The PostgreSQL Global Development Group released PostgreSQL 16 Beta 1 on May 25, 2023. PostgreSQL 16 improves logical replication by enabling replication from standbys as well as the ability to apply large transactions in parallel for improved performance.
PostgreSQL 16 improves privilege administration, enhances VACUUM and ANALYZE commands and adds pg_stat_io for improved monitoring.
For a complete list of changes and improvements, see the PostgreSQL 16 Beta 1 release notes from the PostgreSQL Global Development Group.
Key changes and enhancements
Privilege Administration
There are several improvements to privilege administration in PostgreSQL 16. Previous versions of PostgreSQL required a superuser account to handle many of the roles of database administration. That worked fine for very small teams and 1-user environments, but is less practical for enterprises where the administration of PostgreSQL is handled by a number of different users. PostgreSQL 16 will alleviate the need for superusers to handle a wider range of tasks specific to their role.
PostgreSQL 16 changes the CREATEROLE function so that users can only grant privileges in roles for which they possess ADMIN OPTION. This allows admins to define more specific roles and grant privileges accordingly. For more on this topic, read “Surviving Without A Superuser - Coming to v16” by EDB Chief Database Scientist, Rober Haas.
Logical Replication
PostgreSQL 16 incorporates multiple performance-improving enhancements to logical replication. Among these is the introduction of logical decoding on stand-by. This enables users to capture a snapshot of an ongoing transaction and store it in WAL files, eliminating the need for a checkpoint. This is possible with the new pg_log_standby_snapshot() function.
Another advancement to logical replication is the ability to apply large transactions in parallel for improved performance. This allows parallel processing while the transaction is in progress on the primary node. Benchmark testing showed that bulk inserts were completed as much as 40% faster than in previous versions of PostgreSQL.
Other changes to logical replication include allowing logical replication initial table synchronization to copy rows in binary format, allowing logical replication apply without a primary key, avoiding replication loops by allowing logical replication subscribers to process only changes without an origin and introduces performing logical replication SELECT and DML actions as the table owner which improves security by requiring subscription owners to have SET ROLE permissions on all tables in the replication set or to be a superuser.
Performance Improvements
PostgreSQL 16 introduces enhanced query execution capabilities through increased query parallelism. This enables parallel execution of FULL and RIGHT joins, as well as parallel execution of the string_agg and array_agg aggregate functions. SELECT DISTINCT queries can now utilize incremental sorts, leading to improved performance. Additionally, concurrent bulk loading of data using COPY has seen substantial performance enhancements, with the PostgreSQL Development Group reporting improvements of up to 300%.
Other performance enhancements include caching RANGE and LIST partition lookups and allowing control of the shared buffer usage by VACUUM and ANALYZE.
Monitoring Enhancements
Several monitoring features have been incorporated in the latest release, including the addition of a new view called pg_stat_io, which provides comprehensive IO statistics. The release adds recording statistics on the last sequential and index scans on tables, the occurrence of updated rows moving to new page, speculative lock information in the pg_locks system view, stand-alone backends with a special backend type, several new wait events and other data that make the monitoring of PostgreSQL more comprehensive than ever before.
Look for more details on the PostgreSQL 16 release as we get closer to the first GA release this Fall.