What's so great about PostgreSQL v17?

© Laurenz Albe 2024

Every year in fall, there is a new PostgreSQL release. After looking at the highlights of PostgreSQL v17, you may think, “what's the big deal?” Quite a few people might even be unhappy about the reminder that they should really upgrade some time soon. Time to explain how wonderful PostgreSQL v17 is!

Why are there no spectacular new features in PostgreSQL v17?

Well, there are — I'm going to rant about them later. But there is certainly no eye-catcher like “automatic sharding for friction-less horizontal scaling” or “built-in automatic fail-over for high availability”. That's not because PostgreSQL has lost its momentum: in fact, there are more contributors today than ever before. There are several explanations for this seeming lack of innovation.

PostgreSQL is already pretty feature-complete

Over the decades, PostgreSQL has grown a lot. If I think back to release 8.1, the first release I worked with: autovacuum was still something new and somewhat experimental, replication was something scary you did with Slony, and so on. The average DBA had never heard of PostgreSQL. It is amazing to think of all the new features that have come since. How could we ever live without them?

Many smart people have contributed many great things over the years. Most of the easy, obvious improvements (and some difficult ones!) have already been made. The remaining missing features are the really hard ones.

Contributing to PostgreSQL has become harder

Over the years, as the number of contributors and the world-wide importance of PostgreSQL have grown, so have the the demands on new contributions. Today, each code contribution has to go through a peer review process. An ever-increasing number of patches vie for the interest of reviewers and committers. Spending time to improve and merge somebody else's work is much less attractive than working on your own cool feature. This narrow bottleneck means that you need a lot of time and determination if you want to get your contribution committed.

The unhappy consequence is that contributors get discouraged, and a lot of interesting contributions don't make it across the finishing line. Somebody will always find a fly in the ointment of your patch, and people are quick with a wish list of desirable additions. And even if somebody commits your patch, that is no guarantee that it stays: during the development cycle for PostgreSQL v17, a bunch of features got reverted, because people found problems with them that were not easy to fix. My co-worker Ants commented in his laconic way, “PostgreSQL development happens in five commitfests and one revertfest.” The PostgreSQL community is aware that they should improve the development process. There is just no consensus over how to do that.

But I believe that the problem contributors face also has some positive aspects. The development process may be frustrating, but the committed features usually are stable and mature. There is also little danger of PostgreSQL succumbing to “creeping featuritis”: successful old software that grows so many features (warts?) that eventually a new, slim piece of software takes the lead and leaves it in the dust. In a way, PostgreSQL owes its decade-long success story and its well-earned reputation of stability and robustness to its sluggish development process.

Great new features in PostgreSQL v17

But let's turn to the new developments in PostgreSQL. I won't list them all (you can find them in the release notes), but I'll try to whet your appetite for PostgreSQL v17.

The unobtrusive performance improvements in PostgreSQL v17

Every PostgreSQL release comes with performance improvements. Often, these are improvements to the optimizer. In many small ways, the optimizer keeps becoming smarter from release to release. People often ask me, “will my application become faster if I upgrade?” Most of the time, I cannot point out concrete ways in which their workload will benefit. But the combined effect of the improvements if you — say — upgrade from v13 to v17 is enough that I feel safe to predict that most database workloads will run faster after the upgrade.

To pick one of these gems at random, isn't it nice that from v17 on, PostgreSQL will consider fast startup plans in a query with UNION ALL and LIMIT? Another gem is that PostgreSQL will process IN-lists more efficiently with b-tree index scans.

The v17 performance feature that made it to the short list is an improvement to VACUUM. In v17, VACUUM can process more rows in a single pass. It will also freeze old rows more efficiently, reducing the amount of WAL written. This is typical of many performance improvements: you won't see a notable effect on any single SQL statement, but autovacuum will consume fewer resources and reduce the load on your machine.

The new built-in collation provider in PostgreSQL v17

PostgreSQL v17 has a new built-in collation provider. This is the beginning of a development that might one day get rid of one of the most annoying problems in PostgreSQL: the dependency of PostgreSQL on external collation providers like the C library or the ICU library. That dependency requires you to rebuild indexes on strings whenever

• a database uses a natural language collation and
• you upgrade the library that provides the collations

So far, the built-in collation provider only has support for binary collations. That is typical for new features in PostgreSQL: a year is usually too short for writing a complete feature, so the first release offers only a partial implementation. Later releases can add more features. My hope is that somebody will add natural language collations, and that these collation stay stable across major versions. That would obsolete the need to rebuild indexes after an upgrade. There is currently a discussion on the hackers list about how stable the new collations should be; chime in if you have an opinion.

Logical decoding can survive a failover on the publisher in PostgreSQL v17

Up to now, it was difficult to use logical replication with a high-availability failover cluster as source. When the publisher died and a streaming replication standby took over, you usually had to rebuild logical replication from scratch. Now, if you

• define your subscription with failover = true and
• you put the streaming replication replication slot into the new parameter synchronized_standby_slots

logical decoding will wait until the streaming replication standby server has received the WAL. That way, logical decoding can continue after the failover.

If you needed such a setup before v17, you could resort to the third-party extension pg_failover_slots, but having this capability built into PostgreSQL is a great step ahead.

Improved EXPLAIN support in PostgreSQL v17

If you have to tune queries like I do, you will know that EXPLAIN is the indispensable tool to analyze query performance. There is one notable new EXPLAIN option in v17: SERIALIZE to add statistics about the time the executor spent on converting the statement output to the output format. Without that, you cannot see the time it takes to detoast large columns and convert the result to a string.

Support for incremental backups in PostgreSQL v17

No feature list for PostgreSQL v17 can omit this important improvement. Backing up a large database can take a very long time. If you cannot afford a daily pg_basebackup, your options were limited before v17:

• you could perform a base backup less often and accept a longer recovery time
• you could use the low-level backup API with storage snapshot techniques
• you could use the third-party product pgBackRest, which has support for incremental backup

From v17 on, you can also perform incremental backups using pg_basebackup. For that, you have to turn on the new WAL summarization feature that extracts the information which blocks have changed since the previous base backup. To restore an incremental backup, you have to use pg_combinebackup to merge it into the previous base backup.

Wrapping up

We had a look at some of the new features in PostgreSQL v17. I hope that I could get you interested. An upgrade is not only an onerous task you have to perform to stay supported, but also an opportunity to use new features and benefit from performance improvements.