PostgreSQL turns performance up for v12 release

PostgreSQL turns performance up for v12 release

Relational database management system PostgreSQL is now available in version 12, promising enhanced performance, space utilisation, and SQL conformance.

It is the first version to offer the ability to use JSON path expressions to run queries over JSON documents and automatically inline WITH queries, if the latter aren’t recursive, don’t have side effects, and are “only referenced once in a later part of a query”. The release also adds an implementation for using so called generated columns as defined in the SQL standard. They can compute their own values from other columns in the same table and are therefore comparable with a table view.

To keep systems safe, version 12 supports “client and server-side encryption for authentication over GSSAPI interfaces” and can require authentication clients to provide an SSL certificate along their username.

Admins can look forward to avoiding downtime for complex rebuilds, since PostgreSQL now offers REINDEX CONCURRENTLY to realise those without blocking writes to an index. It also includes pg_checksums to enable checksums in an offline cluster if necessary.

In terms of performance, the PostgreSQL now by default uses just in time compilation and should be able to better handle workloads with frequently changing indexes. Data retrievals in tables with especially large numbers of partitions have been improved as well, while INSERT and COPY processes on those supposedly have become quicker. 

The new version comes with the ability to attach additional partitions to a table without blocking events. A full list of changes can be found in the official announcement.

PostgreSQL was inspired by open source SQL relational database management system Ingres and  was first released in 1996. The project has been designed to handle a large variety of workloads. It supposedly also helps to protect data integrity and build fault-tolerant environments. 

Though dubbed a NoSQL project, PostgreSQL tries to stay conform with the SQL standard, supporting at least 160 of the 179 mandatory features for SQL:2016 Core conformance in its current edition. Main contributors are EnterpriseDB, Crunchy Data, and 2ndQuadrant.