What’s New in PostgreSQL 12? Performance Boosts and Advanced Features

Performance and Space Efficiency

PostgreSQL 12 delivers a substantial performance uplift, especially for large data sets, achieving roughly a 40% increase in both query speed and space utilization when benchmarked with the TPC‑C workload.

Indexing Improvements

The B‑tree index implementation has been optimized to handle high‑frequency update workloads more efficiently. New options such as INCLUDE allow inclusion indexes, and GiST/GIN/SP‑GiST indexes now support K‑NN (nearest‑neighbor) queries via the <-> distance operator. The CREATE STATISTICS command can now collect most‑common‑value (MCV) statistics to improve query planning for non‑uniform data distributions.

Partitioning Enhancements

Queries on partitioned tables with thousands of partitions have been accelerated, particularly when only a few partitions need to be scanned. The COPY command can now add data to partitioned tables without blocking queries, and new partitions can be created concurrently.

SQL/JSON Path Queries and Generated Columns

PostgreSQL 12 adds support for SQL/JSON path expressions, enabling efficient querying of JSONB documents using existing index mechanisms. It also introduces “generated columns” that compute values from other columns, with the option to store these computed values on disk.

Common Table Expressions (CTEs)

Non‑materialized execution of common table expressions ( WITH queries) is now possible for non‑recursive, single‑use CTEs, reducing overhead for many existing queries.

Just‑In‑Time (JIT) Compilation

JIT compilation, introduced in PostgreSQL 11 via LLVM, is enabled by default in version 12, providing performance gains for queries with WHERE clauses, aggregates, and certain internal operations. Users must have the LLVM module available when building PostgreSQL.

Localization Support

The release expands ICU collation support, allowing custom, case‑insensitive, or accent‑insensitive sorting rules.

Enhanced Authorization and Security

PostgreSQL 12 strengthens authentication with GSSAPI‑based bidirectional encryption and optional OpenLDAP integration for directory searches. It also supports multi‑constraint authentication methods such as scram-sha-256 combined with clientcert=verify-full, requiring valid SSL certificates.

System Management Commands

The REINDEX CONCURRENTLY command enables index rebuilding without blocking new writes, facilitating zero‑downtime maintenance. The pg_checksums utility can now enable or disable page checksums on a running cluster, a task previously limited to the initdb stage.