PostgreSQL 12 is focused on performance and optimization. This release wasn’t built with brand-new, shiny features in mind; instead, it’s a finely-tuned, well-polished implementation of existing PostgreSQL capabilities. Because new versions of PostgreSQL are released every year, not every new feature is fully capable when it is introduced. After a couple of releases, when the feature has had a chance to grow from its initial implementation, its performance becomes polished, edge cases become supported, and missing capabilities get implemented.
Here are eight of the most significant improvements found in PostgreSQL 12.
1. Partitioning Performance
Partitioning isn’t a new feature — it’s been around for several years — but the partitioning overhead detracted from performance. While PostgreSQL 11 introduced some performance improvements for partitioning, PostgreSQL 12 delivers a polished implementation. For users moving from other databases with thousands of partitions, PostgreSQL 12 now delivers performance benefits by delivering capabilities that can efficiently process thousands of partitions simultaneously. Partitioning performance enhancements can improve query performance, particularly performance with INSERT and COPY statements. In addition, users now have the ability to alter partitioned tables without blocking queries and use foreign keys to reference partitioned tables.
2. B-Tree Enhancements
B-Tree functionality is one of the most complicated feature additions made to PostgreSQL in recent years. The benefit of using B-Trees is to reduce the number of disk blocks accessed. Considering B-Tree technologies date back to the 1970s, it’s difficult to improve upon tried-and-true functions that have existed for decades. But the PostgreSQL 12 team worked to deliver significant performance improvements that are enabled automatically, built to avoid certain edge cases and ‘pathological behaviors’ that once existed in the B-Tree code. Multi-column index sizes are now reduced by up to 40% by using space more efficiently, thereby saving on disk space. Performance improves for indexes with duplicates (non-unique B-Tree indexes) and vacuum runs more efficiently in removing tuples (rows) from indexes. In addition, there is a reduction in locking requirements during index updates.
3. Multi-Column Most-Common-Value (MCV) Statistics
This update, which has been in development for several years, is meant to address an issue that has generated complaints over the years: the edge case of correlated columns in a query. Take the example of Cincinnati, Ohio — you have one field labeled city, and another field called state, with Cincinnati in one column and Ohio in another. Cincinnati, Ohio, is going to be fairly common, but Cincinnati, Arizona, is very rare. PostgreSQL, up until this feature, only recorded a single correlation value for multiple columns. In essence, it would count Cincinnati, Ohio and Cincinnati, Arizona as the same thing. Now you can compare multiple columns and correlate the combinations to optimize indexes for queries.
4. Common table expressions (CTE)
Another feature that’s been overdue for a proper implementation is common table expressions (WITH query inlining). Common table expressions act as an optimization barrier, the query in the common table expression is executed first then PostgreSQL will execute anything after that in the query. Some users adopted common table expressions to improve SQL readability and debugging rather than to optimize the execution of SQL. These users inevitably ran into the optimization behavior. PostgreSQL 12 has implemented a new feature using the keyword— "MATERIALIZE" — which allows the user to turn on the optimization fence. If you don’t use MATERIALIZE you won’t get the optimization fence and could potentially see faster queries.
5. Prepared Plan Control
One important new feature gives users the chance to control the behavior of the PostgreSQL optimizer and potentially improve performance. Previous versions of PostgreSQL would use a custom plan five times, and on the sixth, create a generic plan and use it if it’s as good as the custom ones. This behavior can now be controlled manually through a new variable called “plan_cache_mode”, which allows the user to force a generic plan right away. This adds significant performance benefits to those users who know their parameters are constant and know that the generic plan will work.
6. Just-in-Time Compilation
A feature that was originally introduced in PostgreSQL 11, just-in-time complication is now enabled by default in PostgreSQL 12. Just-in-time compilation allows for data warehouse queries that process lots of data to run the executor more efficiently. Because many users turned this feature on, it’s now become the default for PostgreSQL 12.
7. Checksum Control
Way back in 2013, PostgreSQL introduced a checksum feature used to identify data corruption. This feature had to be turned on when the database was first initialized otherwise users had to dump, turn the feature on and reload the data. This made the feature nearly unusable for some users. With PostgreSQL 12, through a command called “pg checksums”, which used to be called pg verify checksums, users can change a cluster from no checksums to checksums without dumping and reloading data. Currently, the cluster must be offline during this change, but online checksum enablement is being developed.
8. Reindex Concurrently
Index concurrently functionality has existed for years, allowing users to create an index without preventing writes to the index. Reindex doesn’t allow you to create an index while you are writing to the database. With reindex concurrently, an existing index is replaced, by creating a new index in the same place. Reindex concurrently allows you to write to the index and keep the original index name. Obviously, when an index is replaced, minimal locking will occur until the replacement is implemented. This long-requested functionality has been difficult to develop but ultimately was delivered in PostgreSQL 12.
Upgrade to PostgreSQL 12
These eight features are just a few of the many improvements found in PostgreSQL 12. From partitioning improvements to the implementation of common table expressions, PostgreSQL 12 delivers significant usability enhancements that will delight many new and longtime users.