PostgreSQL 16 advances query parallelism

PostgreSQL 16, the next major update of the open source relational database, has arrived in a beta release, highlighted by enhancements in query execution, logical replication, developer experience, and security.

PostgreSQL 16 Beta 1 was published on May 25. The new release improves query execution with more query parallelism, allowing parallel execution of FULL and RIGHT joins and parallel execution of the string_agg and array_agg aggregate functions. PostgreSQL 16 can use incremental sorts in SELECT DISTINCT queries, and improves performance of concurrent bulk loading of data using COPY by as much as 300%, the PostgreSQL Development Group said.

The PostgreSQL 16 release debuts support for CPU acceleration using SIMD for both x86 and Arm architectures, including optimizations for processing ASCII and JSON strings and array and subtransaction searches. Load balancing is introduced for libpq, the PostgreSQL client library.

With logical replication, PostgreSQL 16 can perform logical decoding on a standby instance, providing more options to distribute workloads. Logical replication lets PostgreSQL users stream data in real time to other PostgreSQL instances or other external systems that implement the logical protocol. Performance of logical replication also has been improved. Logical decoding now can be done on a standby instance, providing more options to distribute workloads.

For developers, PostgreSQL 16 continues to implement the SQL/JSON standard for manipulating JSON data, including support for SQL/JSON constructors. The release adds the SQL standard ANY_VALUE aggregate function, which returns any arbitrary value from the aggregate set. Developers can specify non-decimal integers such as 0xff and 0o777. And support has been added for the extended query protocol to the psql client.

PostgreSQL can be downloaded from the project web page for the Linux, Windows, macOS, BSD, and Solaris operating platforms. Additional betas are expected as required for testing, with the final release of PostgreSQL 16 due in late-2023.

Also in PostgreSQL 16:

• Support has been added for Kerberos credential delegation, allowing extensions such as postgres_fdw and dblink to use the authenticated credentials to connect to other services. New security-oriented connection parameters are added for clients. And regular expressions now can be used in the pg_hba.conf and pg_ident.conf files for matching user and database names. PostgreSQL 16 supports the SQL standard SYSTEM_USER keyword, which returns the username and authentication for establishing a session.
• PostgreSQL 16 introduces the Meson build system, which will ultimately replace Autoconf.
• Monitoring features have been added including a pg_stat_io view to provide IO statistics. The page freezing strategy has been improved to help the performance of vacuuming and other maintenance operations. General support for text collations has been improved as well.