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.

Quitting Postgres

This is a small usability feature, but so long over due. Now you can quit Postgres by simply typing quit or 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.

If you want to dig deeper into all the parallelism support in Postgres this presentation by PostgreSQL committer Thomas Munro at PostgresOpen Silicon Valley from a few weeks ago is a great resource.

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.

And more

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.

If you’re looking for a deeper resource on Postgres I recommend the book The Art of PostgreSQL. It is by a personal friend that has aimed to create the definitive guide to Postgres, from a developer perspective. If you use code CRAIG15 you’ll receive 15% off as well.