PostgreSQL 16 Released!

September 14, 2023 - The PostgreSQL Global Development Group today announced the release of PostgreSQL 16, the latest version of the world's most advanced open source database.

PostgreSQL 16 raises its performance, with notable improvements to query parallelism, bulk data loading, and logical replication. There are many features in this release for developers and administrators alike, including more SQL/JSON syntax, new monitoring stats for your workloads, and greater flexibility in defining access control rules for management of policies across large fleets.

"As relational database patterns evolve, PostgreSQL continues to make performance gains in searching and managing data at scale," said Dave Page, a PostgreSQL Core Team member. "PostgreSQL 16 gives users more methods to scale-up and scale-out their workloads, while giving them new ways to gain insights and optimize how they manage their data."

PostgreSQL, an innovative data management system known for its reliability and robustness, benefits from over 25 years of open source development from a global developer community and has become the preferred open source relational database for organizations of all sizes.

Performance Improvements

PostgreSQL 16 improves the performance of existing PostgreSQL functionality through new query planner optimizations. In this latest release, the  query planner can parallelize FULL and RIGHT  joins, generate better optimized plans for queries that use  aggregate functions with a DISTINCT or ORDER BY clause, utilize incremental sorts for  SELECT DISTINCT queries, and optimize  window functions so they execute more efficiently. It also improves RIGHT and OUTER "anti-joins", which enables users to identify rows not present in a joined table.

This release includes improvements for bulk loading using  COPY in both single and concurrent operations, with tests showing up to a 300% performance improvement in some cases. PostgreSQL 16 adds support for  load balancing in clients that use libpq, and improvements to vacuum strategy that reduce the necessity of full-table freezes. Additionally, PostgreSQL 16 introduces CPU acceleration using SIMD in both x86 and ARM architectures, resulting in performance gains when processing ASCII and JSON strings, and performing array and subtransaction searches.

Logical replication

Logical replication lets users stream data to other PostgreSQL instances or subscribers that can interpret the PostgreSQL logical replication protocol. In PostgreSQL 16, users can perform logical replication from a standby instance, meaning a standby can publish logical changes to other servers. This provides developers with new workload distribution options, for example, using a standby rather than the busier primary to logically replicate changes to downstream systems.

Additionally, there are several performance improvements in PostgreSQL 16 to logical replication. Subscribers can now apply large transactions using parallel workers. For tables that do not have a  primary key, subscribers can use B-tree indexes instead of sequential scans to find rows. Under certain conditions, users can also speed up initial table synchronization using the binary format.

There are several access control improvements to logical replication in PostgreSQL 16, including the new  predefined role pg_create_subscription, which grants users the ability to create new logical subscriptions. Finally, this release begins adding support for bidirectional logical replication, introducing functionality to replicate data between two tables from different publishers.

Developer Experience

PostgreSQL 16 adds more syntax from the  SQL/JSON standard, including constructors and predicates such as JSON_ARRAY(), JSON_ARRAYAGG(), and IS JSON. This release also introduces the ability to use underscores for thousands separators (e.g. 5_432_000) and non-decimal integer literals, such as 0x1538, 0o12470, and 0b1010100111000.

Developers using PostgreSQL 16 also benefit from new commands in psql. This includes  \bind, which allows users to prepare parameterized queries and use \bind to substitute the variables (e.g SELECT $1::int + $2::int \bind 1 2 \g).

PostgreSQL 16 improves general support for  text collations, which provide rules for how text is sorted. PostgreSQL 16 builds with ICU support by default, determines the default ICU locale from the environment, and allows users to define custom ICU collation rules.

Monitoring

A key aspect of tuning the performance of database workloads is understanding the impact of your I/O operations on your system. PostgreSQL 16 introduces  pg_stat_io, a new source of key I/O metrics for granular analysis of I/O access patterns.

Additionally, this release adds a new field to the  pg_stat_all_tables view that records a timestamp representing when a table or index was last scanned. PostgreSQL 16 also makes  auto_explain more readable by logging values passed into parameterized statements, and improves the accuracy of the query tracking algorithm used by  pg_stat_statements and  pg_stat_activity.

Access Control & Security

PostgreSQL 16 provides finer-grained options for access control and enhances other security features. The release improves management of  pg_hba.conf and  pg_ident.conf files, including allowing regular expression matching for user and database names and include directives for external configuration files.

This release adds several security-oriented client connection parameters, including require_auth, which allows clients to specify which authentication parameters they are willing to accept from a server, and  sslrootcert="system", which indicates that PostgreSQL should use the trusted certificate authority (CA) store provided by the client's operating system. Additionally, the release adds support for Kerberos credential delegation, allowing extensions such as  postgres_fdw and  dblink to use authenticated credentials to connect to trusted services.

About PostgreSQL

PostgreSQL is the world's most advanced open source database, with a global community of thousands of users, contributors, companies and organizations. Built on over 35 years of engineering, starting at the University of California, Berkeley, PostgreSQL has continued with an unmatched pace of development. PostgreSQL's mature feature set not only matches top proprietary database systems, but exceeds them in advanced database features, extensibility, security, and stability.

PostgreSQL 16 Released!