Some Major Improvements 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.

Credits:https://www.enterprisedb.com/blog/8-major-improvements-postgresql-12