We’ve all had it happen. One day, we run a query or function and we wait for the result. And we wait. And we wait. Eventually, we realize something is wrong and find a DBA and yell at them.
“Hey Postgres dude!” we scream. “The database is slow!”
Or maybe we can’t even get a connection. Postgres just keeps saying something about too many clients. This application isn’t launching and there are ten managers breathing down our neck and we panic.
“Hey Postgres dude!” we cry. “The database is down or something!”
Is it, though?
Maybe. Sometimes. It happens, after all. Other times, the situation isn’t so obvious and we have to dig a little deeper. All too often, the application stack is its own worst enemy and actually caused the problem to begin with. Let’s explore how that happens, starting with the usual setup:
CREATE TABLE sensor_log ( id SERIAL PRIMARY KEY NOT NULL, location VARCHAR NOT NULL, reading BIGINT NOT NULL, reading_date TIMESTAMP NOT NULL ); INSERT INTO sensor_log (location, reading, reading_date) SELECT s.id % 1000, s.id % 100, CURRENT_DATE - (s.id || 's')::INTERVAL FROM generate_series(1, 100) s(id); ANALYZE sensor_log;
We now have a hilariously simple table with a mere 100 rows. Surely, nothing can go wrong here!
That’s a bold statement though, and entirely wrong. To explain why, we first need to explore a bit deeper into how Postgres handles locking. We’ve discussed MVCC in the past, so a quick summary is that it provides us with natural guarantees:
Just so we’re complete, let’s test each of these assertions. Just run this on two separate connections:
BEGIN TRANSACTION; SELECT * FROM sensor_log WHERE id = 10; -- Then type this to clear the transaction ROLLBACK;
Before running the
ROLLBACK, we should get no
delays from either connection. Each should produce the same
results, and do so extremely quickly. This is no surpris
A few days ago Uber published the article “Why Uber Engineering Switched from Postgres to MySQL”. I didn’t read the article right away because my inner nerd told me to do some home improvements instead. While doing so my mailbox was filling up with questions like “Is PostgreSQL really that lousy?”. Knowing that PostgreSQL is not generally lousy, these messages made me wonder what the heck is written in this article. This post is an attempt to make sense out of Uber’s article.
In my opinion Uber’s article basically says that they found MySQL to be a better fit for their environment as PostgreSQL. However, the article does a lousy job to transport this message. Instead of writing “PostgreSQL has some limitations for update-heavy use-cases” the article just says “Inefficient architecture for writes,” for example. In case you don’t have an update-heavy use-case, don’t worry about the problems described in Uber’s article.
In this post I’ll explain why I think Uber’s article must not be taken as general advice about the choice of databases, why MySQL might still be a good fit for Uber, and why success might cause more problems than just scaling the data store.
The first problem Uber’s article describes in great, yet incomplete detail is that PostgreSQL always needs to update all indexes on a table when updating rows in the table. MySQL with InnoDB, on the other hand, needs to update only those indexes that contain updated columns. The PostgreSQL approach causes more disk IOs for updates that change non-indexed columns (“Write Amplification” in the article). If this is such a big problem to Uber, these updates might be a big part of their overall workload.
However, there is a little bit more speculation possible based upon something that is not written in Uber’s article: The article doesn’t mention PostgreSQL Heap-Only-Tuples (HOT). From the PostgreSQL source, HOT is useful for the special case “where a tuple is repeatedly updated in ways that do not change its indexed columns.” In that case, PostgreSQL is ab
Today the following commit has landed on the master branch of PostgreSQL, meaning that it will be included in the upcoming 9.6:
commit: 976b24fb477464907737d28cdf18e202fa3b1a5b author: Robert Haas <email@example.com> date: Tue, 26 Jul 2016 16:07:02 -0400 Change various deparsing functions to return NULL for invalid input. Previously, some functions returned various fixed strings and others failed with a cache lookup error. Per discussion, standardize on returning NULL. Although user-exposed "cache lookup failed" error messages might normally qualify for bug-fix treatment, no back-patch; the risk of breaking user code which is accustomed to the current behavior seems too high.
First note that the following functions are impacted by this change:
And that those functions could behave in quite strange ways when used with an invalid object. Before this change, some of those functions complained about a cache lookup error or similar which means that an internal error happened, and users continuously ask for the meaning of such errors:
=# SELECT pg_get_constraintdef(0); ERROR: XX000: cache lookup failed for constraint 0 LOCATION: pg_get_constraintdef_worker, ruleutils.c:1377 =# SELECT pg_get_functiondef(0); ERROR: XX000: cache lookup failed for function 0 LOCATION: pg_get_functiondef, ruleutils.c:1958 =# SELECT pg_get_indexdef(0); ERROR: XX000: cache lookup failed for index 0 LOCATION: pg_get_indexdef_worker, ruleutils.c:1054 =# SELECT pg_get_triggerdef(0); ERROR: XX000: could not find tuple for trigger 0 LOCATION: pg_get_triggerdef_worker, ruleutils.c:762
And the other functions returned some inconsistent output:
=# SELECT pg_get_ruledef(0); pg_get_ruledef ---------------- - (1 row) =# SELECT pg_get_viewdef(0); pg_get_viewdef ---------------- Not a view (1 row)
When used on catalog indexes things can get funny as mentioned here:
=# SELECT indexdef FROM pg_catalog.pg_indexes WHERE in
pgBackRest is one of the most powerful backup solutions
available for PostgreSQL. It has enterprise level features
like compression, multiple channels (threads) of backup execution,
incremental and differential backups etc.
The official documentation is Debian-centric in its focus. I wanted to test it out with the PostgreSQL sandbox from the BigSQL project.
The BigSQL Project makes it easy to install and setup PostgreSQL and its associated components across different operating systems. In this document, we are going to look at how to set it up on Ubuntu 14.04. Linux binaries of the sandbox can be downloaded from the BigSQL download page
The sandbox installation requires only the unpacking of the downloaded file.
tar -xvf bigsql-9.5.3-5-linux64.tar.bz2 cd bigsql/
Using the command line utility (pgc) supplied with the sandbox, its very easy to initialize and start a PostgreSQL instance.
./pgc init pg95 ./pgc start pg95
A PostgreSQL instance should now be up and running.
The same pgc utility can be used to install pgBackRest.
./pgc install backrest
An important aspect to keep in mind is that pgBackrest
is written in Perl and has many dependencies on different
perl libraries and modules.
An easy way to install all the dependencies in one shot is to instruct the apt-get utility to install one of the leaf components in the dependency chain.
sudo apt-get install libdbd-pg-perl
This command should fetch all the perl dependencies of pgBackRest.
Set up a backup repository directory for pgBackRest with the following commands.
sudo mkdir /var/log/pgbackrest sudo chmod 750 /var/log/pgbackrest
IMPORTANT for this test:
Change the ownership of the repository directory to the user under which the postgres process is running. If the user is “postgres” and t[...]
It’s now possible to specify a restore_command in
repmgr.conf, which will be included in the
recovery.conf file generated by
clone, making it easier to configure a more robust
replication setup by enabling PostgreSQL to fall back to a WAL
archive source if streaming replication is interrupted. See
Gabriele Bartolini’s recent blog post “Speed
up getting WAL files from Barman” for an example on how to do
repmgr cluster show
repmgr cluster show command now accepts the
--csv, which outputs the status of
the replication cluster in CSV format, suitable for parsing by
scripts. Current “human-readble” output will look something like
$ repmgr -f /path/to/repmgr.conf cluster show Role | Name | Upstream | Connection String ----------+-------|----------|--------------------------------------------------- FAILED | node1 | | host=localhost dbname=repmgr user=repmgr port=5501 * master | node2 | | host=localhost dbname=repmgr user=repmgr port=5502 standby | node3 | node2 | host=localhost dbname=repmgr user=repmgr port=5503
The equivalent CSV output is this:
$ repmgr -f /path/to/repmgr.conf cluster show 1,-1 2,0 3,1
with node ID as the first column, and status (
0 = master,
1 = standby, -1 = failed) as
conninfostrings now accepted
repmgr operations which require the provision of
database connection information, such as
clone, now accept
conninfo-style strings. This brings
repmgr into line with other PostgreSQL utilities and
is more than a convenient syntax – it provides much more granular
control over the
primary_conninfo string in the
recovery.conf file generated by
This is particularly relevant when setting up SSL-based
The pure, unadulterated, presumptuous impudence of NoSQL. Engines like MongoDB recklessly discard concepts like ACID in some futile quest to achieve “web scale”, and end up accomplishing neither. RDBMS systems have literally decades of history to draw upon, and have long since conquered the pitfalls NoSQL platforms are just now encountering. There may be something to a couple of them, but by and large, they’re nothing we really need.
At least, that’s something I might have said a couple of weeks ago.
Now, I’ve never really ascribed to the supposition that NoSQL is the strict antithesis of traditional SQL-driven systems. The intro paragraph is something of a hyperbolic exaggeration of the general disdain that seems to permeate the NoSQL vs SQL debate. Most DBAs I’ve met basically say “meh” and move on with their day, optimizing queries, tweaking storage metrics, and what not. Usually NoSQL is on our RADAR, but we have other stuff to worry about.
Last week, I was forced to reconsider due to an existing internal project that needed my input. That analysis really showed me why devs and data guys from all walks of life are seduced to the Dark Side of data. After another week slogging through documentation, experimenting with self-healing replication sets, self-balancing sharding, and taking copious notes on everything, something finally clicked. The Dark Side, it seems, has some merit beyond simplifying data structure.
So what can Postgres potentially learn from its eternal adversaries? Beyond stealing their encoding mechanisms via JSON and JSONB datatypes, that is. Though my analysis thus far has focused mostly on MongoDB, it alone incorporates several fundamental concepts that I’ve always felt Postgres lacked. For now, I’ll focus on the two that have—in my opinion, anyway—really prevented Postgres from reaching its full potential.
Postgres has replication, but no meaningful application beyond having a hot standby or a few read slaves. Why no master election consensus system like RAFT? Last year, I came ac[...]
Once upon a time FOSS was about Freedom. It was about exposing equality within source code. It allowed everyone equal rights and equal access to the technology they were using. An idea that if you were capable, you could fix code or pay someone to fix code. An ideology that there was something greater than yourself and that there was an inherent right built into what it is to be human with software.
I sat in a bar slowly nursing beers with other community members over a period of hours. We spoke of many things. We spoke of the never-done new PostgreSQL website. We spoke of my distaste for Amazon Web Services since reformed, with the exception of S3. We spoke of life. We argued, we had honest discourse and that is excellent. There was nobody complaining of political correctness. There was nobody claiming to be “offended”. There was nobody leaving because their feelings were hurt. There was a community member who passed out in his chair and dropped his phone. We walked him to his room to make sure he was safe. All was good.
This retrospective has been digging around in my grey matter since that night six months ago. Originally this was going to just be the stuff of legendary and exaggerated stories among community members that are only getting older and a few who are young but will get there someday. That is, until it began to itch, and as with any good community member, I am scratching that itch.
“My time is precious to me”
It seems like a harmless thing to say. Of course your time is precious to you. I would say that is probably true of most people. I know that my time is precious to me. I make it a point of working part time from May - September so I can take time for my family. (Don’t worry, I more than make up for it the rest of the year).
The problem with the statement is the context. The statement came from a well known contributor and a very smart guy. The reference was in relation to why someone would use software as a service and the general idea was: Software as a Service is awesome b[...]
We’re having our 10th Anniversary meeting tonight!
As in previous years, I generated speaker & topic tag clouds:
PostgreSQL is an awesome project and it evolves at an amazing rate. We’ll focus on evolution of fault tolerance capabilities in PostgreSQL throughout its versions with a series of blog posts. This is the third post of the series and we’ll talk about timeline issues and their effects on fault tolerance and dependability of PostgreSQL.
If you would like to witness the evolution progress from the beginning, please check the first two blog posts of the series:
The ability to restore the database to a previous point in time creates some complexities which we’ll cover some of the cases by explaining failover (Fig. 1), switchover (Fig. 2) and pg_rewind (Fig. 3) cases later in this topic.
For example, in the original history of the database, suppose you dropped a critical table at 5:15PM on Tuesday evening, but didn’t realise your mistake until Wednesday noon. Unfazed, you get out your backup, restore to the point-in-time 5:14PM Tuesday evening, and are up and running. In this history of the database universe, you never dropped the table. But suppose you later realize this wasn’t such a great idea, and would like to return to sometime Wednesday morning in the original history. You won’t be able to if, while your database was up-and-running, it overwrote some of the WAL segment files that led up to the time you now wish you could get back to.
Thus, to avoid this, you need to distinguish the series of WAL records generated after you’ve done a point-in-time recovery from those that were generated in the original database history.
To deal with this problem, PostgreSQL has a notion of timelines. Whenever an archive recovery completes, a new timeline is created to identify the series of WAL records generated after that recovery. The timeline ID number is part of WAL segment file names so a new timeline does not overwrite the WAL data generated by previous timelines. It is in fact possible to archive many diff[...]
When you are coding in a source tree as big as PostgreSQL’s, you
will at some point want to look into some kind of source code
indexing. It’s often convenient not to bother, since
grep is actually superfast. But when you want to find where
a function is defined among all the call sites, some more
intelligence is useful.
The traditional tools for this are
etags, which create index files intended for use by vi
and Emacs, respectively. The PostgreSQL source tree has some
customized support for these in the tools
src/tools/make_etags. Because these tools operate on a
directory level, those wrapper scripts create a single tag file
TAGS respectively) in the
top-level directory and symlink it to all the other directories.
This allows you to easily look for entries across the entire source
tree. But it’s clearly a hack, and at least Emacs is often somewhat
confused by this setup.
But there is something much better that works very similarly: GNU GLOBAL. A main difference is that GNU GLOBAL works on a project basis not on a directory basis, so you don’t need to do contortions to create and manage tags files all over your source tree. Also, GLOBAL can be used from the command line, so you don’t need to be an editor wizard to get started with it. Plus, it appears to be much faster.
The whole thing is very simple. Install the package, which is
global and available in most operating
system distributions. To start, run
in the top-level directory. This creates the files
Then you can use
global to search for stuff,
$ global elog src/include/utils/elog.h
Or you can look for places a function is called:
$ global -r write_stderr
You can run
global in any directory.
Or how about you want to look at the code where something is defined:
$ less -t elog
Note no file name is required. (See the manual for the required
setup to make this work with
Or of course use editor integration. For Emacs, there is
Starting from Barman 1.6.1, PostgreSQL standby servers can rely on an “infinite” basin of WAL files and finally pre-fetch batches of WAL files in parallel from Barman, speeding up the restoration process as well as making the disaster recovery solution more resilient as a whole.
Before we start, let’s define our playground. We have our
PostgreSQL primary server, called
angus. A server with
barman and a third server with a
reliable PostgreSQL standby, called
chris – for
different reasons, I had to rule out the following names
cliff and obviously
angus is a high workload server and is continuously
backed up on
chris is a hot
standby server with streaming replication from
angus enabled. This is a very simple, robust and cheap
business continuity cluster that you can easily create with pure
open source PostgreSQL, yet capable of reaching over 99.99% uptime
in a year (according to our experience with several customers at
What we are going to do is to instruct
standby) to fetch WAL files from
streaming replication with
angus is not working, as a
fallback method, making the entire system more resilient and
robust. Most typical examples of these problems are:
chriswhich causes the standby to go out of sync with
For further information, please refer to the Getting WAL files from Barman with ‘get-wal’ blog article that I wrote some time ago.
Technically, we will be configuring the standby server
chris to remotely fetch WAL files from
barman as part of the
option in the
recovery.conf file. Since the release of
Barman 1.6.1 we can take advantage of parallel pre-fetching of WAL
files, which exploits network bandwidth and reduces recovery time
of the standby.
This scenario has been tested on Linux systems only, and requires:
Just got back from extended trip to Database.Camp – Sunday, July 10, 2016 – at the UN in NYC – Totally Free!
Wide range of presentations from CTOs/CEOs from across the database software world, with about 200 attendees, slightly more people earlier in the day.
Very happy to report we had 3 presentations covering different
aspects of PostgreSQL
* EnterpriseDB – Ed Boyajian – Building Postgres Ecosystems
* 2ndQuadrant – Simon Riggs – PostgreSQL: The Next 10 Years
* CartoDB – Javier de la Torre – Celebrating PostgreSQL and demonstrating execellent data visualizations
I don’t think any attendee would have missed the fact that 3 companies spoke about Postgres, whereas all other technologies were discussed only once. I noted that when asked “Do you use database X?”, more attendees hands went up for Postgres than any other. Also very happy to report that our talks interlocked nicely with one another, showing attendees that we can work together as a community.
Best talk of the day was Minerva Tantoco, CTO for the City of New York. Engaging, entertaining and greatly encouraging to see technology being adopted for the public good. Free Wifi in JFK would be nice… but looks like its coming across the whole of NYC, which will really change what is possible.
Well worth attending if they hold it again next year. Congrats to Eric for arranging, and making it for a good cause: Techie Youth.
TL;DR : With the kind support of PostgreSQL Europe, I am launching a new edition of the PostgreSQL Magazine in an innovative paper format. The primary goal is to publish it and send copies to PostgreSQL Conference Europe in Tallinn in November. If we’re sucessful, additional copies will be sent to other events.
I’m here to talk to you about the new direction for PG Magazine. So far, we’ve produced 2 issues of the magazine and we’ve received an entousiastic feedback from long-time community members as well as complete newcomers. I still believe that paper is a great medium to promote PostgreSQL and there’s a need for a regular printed media in the community.
However we’ve been struggling to find a stable economic model to ensure this media remains a community-driven effort. So far the balance we’ve found is that the content is produced, edited and controlled by community members, while the graphic editing, printing and shipping is done by professionnals and paid by non-profit associations (PG Europe, SPI, and others) or commercial sponsors (EDB for instance)
This model works with the current format of the magazine (36 pages) but it’s very slow. Producing a new issue requires a lot of time to gather the articles, edit the content and proof-read everything. It’s also very hard to translate. So far only the Chinese community has been able to fully translate the magazine.
Based on this considerations, here’s an attempt to make the whole process more dynamic while switching to a more appealing format.
Over the last month, I’ve worked on what I called a “folded magazine”. The basic idea is that the magazine is presented as an A5 newspaper. Then you unfold it and you get an A4 newspaper. Then you unfold it and you get an A3 newspaper. Then you unfold it and you get an A2 poster that you can take back at the office…
I used this concept for the 2016 PG Day France booklet. Here’s a video :
This format has drawbacks
a- The graphical editing needs to be done by professionnals. A P[...]
[postgres@db1 tmp]$ ./plbacktrace.py 13239fn_oid=105672 lineno=5 func="life_get_cell_pl(life_board,integer,integer)"
I’ve been almost exclusively a Postgres DBA for a seemingly interminable length of time. While this is good for specializing, nobody wants to be a One-Trick Pony. And aside from the occasional bit of Python to write more advanced tools when Bash isn’t up to the job, it’s All Postgres All The Time. While few things would make me happier, it pays to branch out occasionally.
There’s only so much time in a day though, and being as we use MongoDB for a couple of projects, it only makes sense to investigate how it really works. What can a Postgres user, who eats, sleeps, and breathes Postgres, hope to learn from a NoSQL database? I guess we can start with some simple and basic operations. Can I make a table, fill it with 1-million rows, update a few, delete a few, add an extra index for future searches, and perform a query or two?
Let’s start with the Postgres schema I use for practically everything:
CREATE TABLE sensor_log ( id SERIAL NOT NULL PRIMARY KEY, location VARCHAR NOT NULL, reading BIGINT NOT NULL, reading_date TIMESTAMP NOT NULL ); \timing INSERT INTO sensor_log (id, location, reading, reading_date) SELECT s.id, s.id % 1000, s.id % 100, CURRENT_DATE - ((s.id * 10) || 's')::INTERVAL FROM generate_series(1, 1000000) s(id); TIME: 7236.906 ms CREATE INDEX idx_sensor_log_date ON sensor_log (reading_dat
If your PostgreSQL instance is running on an Ubuntu LTS system that you need to upgrade to the most recent release, say from precise to trusty – because, well, sooner or later you must – you need to consider what is going to happen to your database.
The upgrade process described in this article is similar to what you would have to do if you were upgrading from Trusty to Xenial, the newest Ubuntu LTS release.
Ubuntu attempts to make the process of upgrading to the newest distribution release easy and hassle-free. In fact, it is the case in many situations but not when there is PostgreSQL running in the system. If you just go ahead and try to run do-release-upgrade command, which is the officially recommended way to upgrade your Ubuntu LTS distribution to the newest release, you will end up seeing this error message:
Get:72 http://us.archive.ubuntu.com trusty-backports/universe Translation-en [34.6 kB]
Fetched 23.3 MB in 6s (0 B/s)
Checking package manager
Reading package lists... Done
Building dependency tree
Reading state information... Done
Building data structures... Done
Calculating the changes
Calculating the changes
Could not calculate the upgrade
An unresolvable problem occurred while calculating the upgrade.
This can be caused by:
* Upgrading to a pre-release version of Ubuntu
* Running the current pre-release version of Ubuntu
* Unofficial software packages not provided by Ubuntu
If none of this applies, then please report this bug using the
command 'ubuntu-bug ubuntu-release-upgrader-core' in a terminal.
Not very helpful, is it? Well, clearly you need to troubleshoot. Where do you start?
Examine /var/log/dist-upgrade/main.log and look for ERROR messages. This is what you will see:
2016-02-09 07:19:01,392 DEBUG blacklist expr '^postgresql-.*[0-9]\.[0-9].*' matches 'postgresql-plperl-9.3'
2016-02-09 07:19:01,393 DEBUG The package 'postgresql-plperl-9.3' is marked for removal but it's in the removal blacklist
2016-02-09 07:19:01,462 ERROR Dist-upgrade failed: 'The package 'postgresql-plperl-9.3' is marked fo
Most web applications make use of databases. When deploying your app in production, you will need, among other things, monitoring and analysis/reports of log data generated by your application.
This comes in handy when troubleshooting problems, in situations involving bottlenecks in large applications, where we need to find low-performing SQL queries.
This is a simplified diagram that shows the interactions between the main components of a typical web application production environment.
The users make requests to the web application. In order to serve the requests, the web application needs information from a database, in this case, the databased being used is PostgreSQL, so it makes queries to it.
The PostgreSQL database server computes the queries, returns back data, but at the same time, it also writes to log files on disk.
The log files are being rotated on a daily basis. So for each day, there is one log file. The log files start out empty. Each time they are being used again, they're truncated and new log data will be written to them.
Most of the settings described here are documented in the 18.8 Error reporting and logging section of the PostgreSQL manual.
We're turning on the logging collector, we ensure that we're
%w in the daily log filenames (this is the
0-based weekday represented as a decimal number, with 0 being
sunday, 1 is monday and so on).
Once the logging collector is enabled, the logs are no longer
/var/log/postgresql/ , but instead, they
will be located at
We're also aiming to truncate the file on rotation.
logging_collector = on log_filename = 'postgresql-%w.log' log_file_m
PostgreSQL’s manual is generally pretty clear about the locks taken by various operations – but nothing’s perfect. If you need to, you can also ask PostgreSQL directly.
You can check lock levels trivially with psql or PgAdmin.
For example, to see what lock alter table some_table disable trigger some_trigger; takes:
test=> BEGIN; BEGIN test=> ALTER TABLE some_table DISABLE TRIGGER some_trigger; ALTER TABLE test=> SELECT locktype, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation = 'some_table'::regclass; locktype | mode ----------+----------------------- relation | ShareRowExclusiveLock (1 row)
That’s for a lock on a table. It’s easy to see from this that we take a SHARE ROW EXCLUSIVE lock, which according to the manual:
… protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it at a time.
You can also filter on pg_locks in other ways to see other locks held by a transaction. You’ll have to do some joins on pg_class etc to decode the relation OIDs to names – which is why we really need a pg_stat_locks view in PostgreSQL to make this easier.
People use a variety of canned queries of varying quality for looking to see which processes block others at the moment. With the addition of more detailed lock wait information and pg_blocking_pids() in 9.6, this will get a lot easier, though 9.6 doesn’t add a helper view yet.
One important and possibly confusing thing you’ll see in pg_locks is that every transaction holds a special lock on its self, called the virtualxid lock:
test=> BEGIN; BEGIN test=> SELECT * FROM pg_locks WHERE pid = pg_backend_pid(); locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath ------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+--[...]
After doing full-time PostgreSQL consulting for over 16 years now, I actually don’t remember a time without inheritance anymore. Of course things were improved over time, but in my head it has always been there and it has always just worked as expected. After so many years I still love the feature because it offers […]
Constraints in Postgres are very powerful and versatile: not only are foreign keys, primary keys, and column uniqueness done internally via constraints, but you may create your own quite easily (at both the column and table level). Most of the time constraints are simply set and forget, but there is one time constraints may become a problem: copying the database using the pg_dump program.
The issue is that constraints are usually added *before* the data is copied to the new table via the COPY command. This means the constraint fires for each added row, to make sure that the row passes the conditions of the constraint. If the data is not valid, however, the COPY will fail, and you will not be able to load the output of your pg_dump into a new database. Further, there may be a non-trivial performance hit doing all that validation. Preventing the constraint from firing may provide a significant speed boost, especially for very large tables with non-trivial constraints.
Let's explore one way to work around the problem of pg_dump failing to work because some of the data is not valid according to the logic of the constraints. While it would be quicker to make some of these changes on the production system itself, corporate inertia, red tape, and the usual DBA paranoia means a better way is to modify a copy of the database instead.
For this example, we will first create a sample "production" database and give it a simple constraint. This constraint is based on a function, to both emulate a specific real-world example we came across for a client recently, and to allow us to easily create a database in which the data is invalid with regards to the constraint:
dropdb test_prod; createdb test_prod pgbench test_prod -i -n creating tables... 100000 of 100000 tuples (100%) done (elapsed 0.82 s, remaining 0.00 s) set primary keys... done. psql test_prod -c 'create function valid_account(int) returns bool language sql immutable as $$ SELECT $1 > 0$$;' CREATE FUNCTION psql test_prod -c 'alter table pg[...]
Since Postgres started supporting NoSQL (via hstore, json, and jsonb), the question of when to use Postgres in relational mode vs NoSQL mode has come up a lot. Do you entirely abandon traditional table structures, and go with documents all the way? Or do you intermingle both? The answer unsurprisingly is: it depends. Each newer model including hstore, JSON, and JSONB has their ideal use cases. Here we’ll dig deeper into each and see when you should consider using them.
If you exclude XML, this was the first truly unstructured datatype to arrive in Postgres. Hstore arrived way back in Postgres 8.3, before upsert, before streaming replication, and before window functions. Hstore is essentially a key/value store directly in Postgres. With hstore you’re a little more limited in terms of the datatypes you have: you essentially just get strings. You also don’t get any nesting; in short it’s a flat key/value datatype.
The upside of hstore is you don’t have to define any of your keys ahead of time. You can simply insert the record and it’ll save everything. Let’s say you’ve got an example table:
CREATE TABLE products ( id serial PRIMARY KEY, name varchar, attributes hstore );
From here you can insert whatever you want into the
attributes column. And then query based on those
various keys or values.
INSERT INTO products (name, attributes) VALUES ( 'Geek Love: A Novel', 'author => "Katherine Dunn", pages => 368, category => fiction' ); SELECT name, attributes->'author' as author FROM products WHERE attributes->'category' = 'fiction'
The obvious benefit here is flexibility, but where it really shines is being able to leverage various index types. In particular, a GIN or GiST index will index every key and value within the hstore. This way when you filter on something it’ll use the index if it makes sense to the planner within Postgres.
As hstore isn’t a full document equivalent, it’s a stretch to consider using it as such. If you have relational data as well as some data that may not alway[...]
As the PostgreSQL community continues down its path of world domination I can't help but wonder whether the various PostgreSQL companies are going to survive the changes. Once upon a time there was an undercurrent of understanding that what was good for the community was good for the company. Whatever company that may be. However, over the last few years it seems that has changed. It seems there is more prevalance toward: What is good for the company is good for the community, or in other words, "The goal is profit."
That is a flawed discipline to follow in the Open Source world. A truly beneficial, strong and diverse community has to eliminate that thought entirely. The goal is not profit; profit is the reward.
That isn't to say that profit is bad. That would be stupid. It is profit that allows Command Prompt to sponsor my activities with United States PostgreSQL and Software in the Public Interest. It is to say that my contributions to the community as a whole drive Command Prompt's profit. It is symbiotic; a constant ebb and flow of the relationship between community and commerce.
I would invite other PostgreSQL companies to consider this. I would challenge them to upend their profiteering motive and focus on community building with profit being the reward. The profit will follow. How do you do this? How do you continue to derive profit from community without sacrificing the community or your standing within the community? Here are some practical ideas:
There is nothing wrong with that. I would argue that it is a sign of a healthy project. However, it does come with downsides. The most important one being that no matter how autonomous your employer says that your work is, at any point your priorities are driven by what is best for the company. That means, it is driven by profit. If the work being done is not going to lend itself to the bottom line of the sponsoring company, those priorities may change.
I had the pleasure to attend PGDay UK last week – a very nice event, hopefully I’ll have the chance to come back next year. There was plenty of interesting talks, but the one that caught my attention in particular was Performace for queries with grouping by .
I have given a fair number of similar performance-oriented talks in the past, so I know how difficult it is to present benchmark results in a comprehensible and interesting way, and Alexey did a pretty good job, I think. So if you deal with data aggregation (i.e. BI, analytics, or similar workloads) I recommend going through the slides and if you get a chance to attend the talk on some other conference, I highly recommend doing so.
But there’s one point where I disagree with the talk, though. On a number of places the talk suggested that you should generally prefer HashAggregate, because sorts are slow.
I consider this a bit misleading, because an alternative to HashAggregate is GroupAggregate, not Sort. So the recommendation assumes that each GroupAggregate has a nested Sort, but that’s not quite true. GroupAggregate requires sorted input, and an explicit Sort is not the only way to do that – we also have IndexScan and IndexOnlyScan nodes, that eliminate the sort costs and keep the other benefits associated with sorted paths (especially IndexOnlyScan).
Let me demonstrate how (IndexOnlyScan+GroupAggregate) performs compared to both HashAggregate and (Sort+GroupAggregate) – the script I’ve used for the measurements is here. It builds four simple tables, each with 100M rows and different number of groups in the “branch_id” column (determining the size of the hash table). The smallest one has 10k groups
-- table with 10k groups create table t_10000 (branch_id bigint, amount numeric); insert into t_10000 select mod(i, 10000), random() from generate_series(1,100000000) s(i);
and three additional tables have 100k, 1M and 5M groups. Let’s run this simple query aggregating the data:
SELECT branch_id, SUM(amount) FROM t_10000 GROUP BY 1
and then convince t[...]