Postgres 11 - a First Look
Postgres 11 is almost here, in fact the latest beta shipped today, and it features a lot of exciting improvements. If you want to get the full list of features it is definitely worth checking out the release notes, but for those who don’t read the release notes I put together a run down of some what I consider the highlight features.
This is a small usability feature, but so long over due. Now you can quit Postgres by simply typing
exit. Previously you had to use Ctrl + D or
\q. As a begginer it’s one thing to jump into a psql terminal, but once in if you can’t figure out how to quit it’s a frustrating experience. Small usability features, such as this and
watch in an earlier release, are often lost in the highlighted features which talk about performance or new data types. Improvements like this really go a long way for making Postgres a better database for everyone.
Fear column addition no more
Brandur had a great in depth write-up on this feature already, but it falls somewhere into the category of the above as well as a performance improvement. Previously when you added a new column that was
NOT NULL with a default value Postgres would have to take a lock and re-write the entire table. In a production environment on any sizable table for all practical purposes the result was an outage. The work around was to break your migrations apart to be a several step process.
With Postgres 11 you can add a new column to a table that is not null with a default value. The new row will get materialized on your database without requiring a full re-write. Here is to having to think less about your migrations.
Of course performance is a highlight
No Postgres release would be complete without some performance improvements. This release there are really two areas that feature key improvements around performance.
Parallelism continuing to mature
We first saw parallelism support back in PostgreSQL 9.6. At the time it was primarily for sequential scans, which if you used parallelism for your sequential scans was great, but overall that was a narrow focus. PostgreSQL 10 parallelism because much more useful, and with PostgreSQL 11 it just keeps getting better. Some of the highlights for parallelism include:
- Parallel hash joins
- Parallel append
- Parallel index creation – We’ve talked about how great this can be over on the Citus blog. With it natively in Postgres it makes it even easier for people to leverage.
Postgres gets a JIT
Just in time compilation is going to be a big deal for Postgres for the coming years. We have the initial support for it now in PostgreSQL 11. Even in this initial implementation of JIT support you can see a nearly 30% speedup on certain queries, such as highlighted here by the TPC-H benchmark
It is still early days for the just in time query compilation support, so expect the improvements here to be even better in PostgreSQL 12 and 13.
Statistics keep getting better
In Postgres 10 we saw a feature that few have probably used
CREATE STATISTICS. You see under the covers Postgres keeps a lot of information about your database which it uses to determine the query plan it will use when executing a query. Most statistics were single column ones previously, now with
CREATE STATISTICS you could define a correlation between two separate columns. With Postgres 11 now you can create statistics based on expression indexes giving you even more cases where they can help the performance of your app.
Keeping standbys warm
pg_prewarm has been great for warming up a replica’s cache so that should you have a failover you’re not failing over to a cold cache. However up until PostgreSQL 11 you’d have to manually run it yourself or setup some scheduler such as pg_cron, now you can configure
pg_prewarm to run all on it’s own at a regular interval.
PostgreSQL 11 is packed with more features than I’ve seen in a release before, though I think I’ve also said that before. It will be exciting to see several of these features such as the JIT support, statistics, and others as it is still in the early days for them. Meanwhile we have a great set of new features to improve user experience as well as help performance with parallelism. If you’re curious to get your hands on these give the beta a try and send your feedback to the PostgreSQL community.
Find this article helpful? I welcome your support on Patreon to be able to devote more time to articles like this that others find useful.