PostgreSQL puts the pedal to the metal with some smart indexing tweaks in version 12

Open-source veteran not above the odd speed increase

Open-source database darling PostgreSQL emitted a new version of its eponymous database last night with more nods to standard SQL and a performance boost.

The performance gains can be attributed to improvements in the indexing system as well as to partitioning. Frequently modified B-tree indexes have been optimised and the team claims a 40 per cent reduction in space utilisation.

The performance of adding data to partitioned tables via INSERT and COPY has also been improved, as well as the critical addition of the ability to attach a new partition to a table without blocking queries.

Other indexing performance bumps from lower overheads in write-ahead log generation and the enabling of Just-in-time compilation by default should speed up the execution of expressions in WHERE. The feature isn't new – it was introduced back in 2018's PostgreSQL 11 – but was left turned off by default.

What is new, however, and will have many administrators jumping for joy, is the arrival of REINDEX CONCURRENTLY, which can rebuild an index without blocking writes to the table. For many that, and the increase in performance, will be worth the price of admission alone.

Compatibility with SQL standards has also received a nod in the release with "generated columns" derived from other columns and a "stored" version, which will stash the computed version on disk. Queries can also be run over JSON documents and WITH queries can now be inlined (so long as they are not recursive or referenced more than once in a later part of the query).

There is also a nod toward those struggling with internationalisation challenges, with nondeterministic collations to allow case or accent-insensitive comparisons.

Finally, authentication has been improved with servers able to require a client to provide a valid SSL certificate, if configured, and client and server-side encryption has been introduced for authentication over GSSAPI. LDAP servers can also be discovered if PostgreSQL is compiled with OpenLDAP.

For those who prefer things a little cloudier, both AWS and Azure continue to linger on PostgreSQL 11. Based on past performance, we'd expect version 12 to crop up on the former in about five months, and on the latter three months later.

PostgreSQL has been around for quite a while, with the first formal release occurring back in 1997. Other than the almost two year gap between 9.4 (2014) and 9.5 (2016), it has managed an emission roughly once a year.

Version 12 packs enough punch to show that the old thing continues to be very much worthy of the attention of DBAs if open source is your thing. Or, worryingly for the likes of Oracle, even if it's not. ®

Broader topics

Other stories you might like

Biting the hand that feeds IT © 1998–2022