, the latest version of the "best open-source relational database in the world" is set to release in a couple of weeks (if all goes according to plan). This follows the usual schedule ā a new version with a wealth of new features comes out once a year, and, frankly, itās impressive. Thatās why I became an active member of the PostgreSQL community.
In my opinion, unlike previous releases, PostgreSQL 12 doesn't include one or two revolutionary features (like partitioning or query parallelism). I jokingly mentioned that the main highlight of PostgreSQL 12 is its greater stability. And isnāt that what you need when managing your businessās critical data?
But PostgreSQL 12 doesnāt stop there: with new features and enhancements, applications will perform better, and all you need to do is upgrade!
(Well, maybe also rebuild the indexes, but this release makes it less daunting than weāre used to.)
It will be great to upgrade PostgreSQL and immediately enjoy significant improvements without extra hassle. A few years ago, I analyzed the update from PostgreSQL 9.4 to PostgreSQL 10 and saw how much faster the application became thanks to improved query parallelism in PostgreSQL 10. And, most importantly, it required almost nothing from me (just setting a configuration parameter. max_parallel_workers).
Agree that itās convenient when applications work better right after an upgrade. And we really strive to please our users, as the number of PostgreSQL users continues to grow.
And how will a simple upgrade to PostgreSQL 12 make you happy? Let me tell you.
Significant improvements in indexing
Without indexing, a database won't get far. How else can you quickly find information? The foundational indexing system of PostgreSQL is called . This type of index is optimized for storage systems.
We simply use the statement CREATE INDEX ON some_table (some_column), and PostgreSQL does the heavy lifting to keep the index up to date while we continuously insert, update, and delete values. Everything works seamlessly, as if by magic.
But PostgreSQL indexes have one issue ā they and take up unnecessary disk space, while the performance of data extraction and updates decreases. By 'bloat' I mean ineffective maintenance of the index structure. This may or may not be related to dead tuples removed (thanks for the information to Peter Geoghegan ()). Index bloat is especially noticeable in workloads where the index is actively modified.
PostgreSQL 12 significantly improves the performance of B-tree indexes, and experiments with TPC-C type tests have shown that space is now used, on average, 40% less. We now spend less time not only maintaining B-tree indexes (i.e., on write operations) but also on data extraction, as the indexes have become much smaller.
Applications that actively update their tables ā typically OLTP applications () ā will utilize disk more efficiently and handle requests better. The more space available on the disk, the more room the database has for growth without upgrading the infrastructure.
Some upgrade strategies require rebuilding B-tree indexes to take advantage of these benefits (for example, does not automatically rebuild indexes). In previous versions of PostgreSQL, rebuilding large indexes in tables resulted in significant downtime, as changes could not be made during this period. However, PostgreSQL 12 has another great feature: you can now rebuild indexes in parallel with the command , so as to completely avoid downtime.
PostgreSQL 12 also includes other improvements in index infrastructure. Another area where magic was involved is , also known as WAL (write-ahead log). The write-ahead log records every transaction in PostgreSQL in case of failure and replication. Applications use it for archiving and . Of course, the write-ahead log is written to disk, which can affect performance.
In PostgreSQL 12, the costs of WAL records created by GiST, GIN, and SP-GiST indexes during index building have been reduced. This offers several tangible benefits: WAL records take up less disk space, and data is restored faster, for example, during recovery from a failure or point-in-time recovery. If you are using such indexes in your applications (for instance, geospatial applications based on PostGIS heavily utilize the GiST index), this is another feature that significantly enhances performance with no effort on your part.
Partitioning - more, better, faster
PostgreSQL 10 introduced . In PostgreSQL 11, it became much easier to use. In PostgreSQL 12, you can change the scale of partitions.
In PostgreSQL 12, the performance of the partitioning system has significantly improved, especially when there are thousands of partitions in a table. For example, if a query only accesses a few partitions in a table with thousands, it will execute much faster. Performance has improved not just for those types of queries. You will also notice how INSERT operations have sped up in tables with many partitions.
Data entry via ā by the way, this is a great way and here is an example ā has also become more efficient for partitioned tables in PostgreSQL 12. The COPY command was already fast, but in PostgreSQL 12, itās even faster.
Thanks to these advantages, PostgreSQL can store even larger datasets, and retrieving them has become easier. And it requires no effort on your part. If your application has many partitions, for example, if it writes time series data, a simple upgrade will greatly improve its performance.
And although this improvement doesn't quite fall into the category of 'upgraded and rejoice', in PostgreSQL 12 you can create foreign keys that reference partitioned tables, making partitioning work a delight.
WITH queries have improved significantly
Once (CTEs, also known as WITH queries), I couldnāt wait to write an article about how ecstatic application developers using PostgreSQL .
I often notice that newcomers to SQL love to use CTEs: if you write them in a certain way, you can really feel like you're writing an imperative program. Personally, I enjoyed rewriting these queries to make do with without CTEs and improve performance. Now, everything is different.
PostgreSQL 12 allows embedding a certain type of CTE without side effects (SELECT), which is used only once closer to the end of the query. If I kept statistics on the CTE queries I rewrote, most of them would fall into this category. This helps developers write clear code that now also runs quickly.
Moreover, PostgreSQL 12 optimizes SQL execution on its own, you won't have to do anything. And while itās probably not necessary for me to optimize such queries now, itās great that PostgreSQL continues to work on query optimization.
Just-in-Time (JIT) is now enabled by default.
In PostgreSQL 12 systems with support for JIT compilation is enabled by default. First, you get support for for certain internal operations, and secondly, queries with expressions (the simplest example is x + y) in the select lists (which follow your SELECT), aggregates, expressions with WHERE clauses, and others can utilize JIT to boost performance.
Since JIT is enabled by default in PostgreSQL 12, performance will improve on its own, but I recommend testing the application in PostgreSQL 11, where JIT first appeared, to measure query performance and see if any adjustments are needed.
But what about the other new features of PostgreSQL 12?
PostgreSQL 12 has plenty of new cool featuresāfrom the ability to explore JSON data using standard SQL/JSON path expressions to multi-factor authentication with the parameter clientcert=verify-full, generated columns, and much more. Enough for a separate post.
Like PostgreSQL 10, PostgreSQL 12 will enhance overall performance right after the upgrade. Of course, you may have your own pathātest the application under similar conditions in a production system before enabling the improvements, as I did with PostgreSQL 10. Even if PostgreSQL 12 is already more stable than I anticipated, don't neglect quality testing of applications before releasing them into production.
Source: habr.com
