PostgreSQL 16: What’re New and Notable features Part 1/2

PostgreSQL 16 release has been announced and there are quite a few major changes that will help developers and administrators in many ways. In this blog, I will cover high-level changes that are coming in this release. Below are different areas that are focused on.

1. Performance Improvements
2. Logical replication
3. Access Control & Security
4. Monitoring

Performance Improvements

• PostgreSQL 16 introduces query planner optimizations that enhance performance by enabling parallelization of FULL and RIGHT joins, generating more optimized plans for queries with aggregate functions and DISTINCT or ORDER BY clauses, implementing incremental sorts for SELECT DISTINCT queries, and optimizing window functions for more efficient execution. It also enhances RIGHT and OUTER “anti-joins” for identifying missing rows in joined tables.
• This release significantly improves bulk loading operations using the COPY command, delivering up to a 300% performance improvement in some scenarios. It also adds support for load balancing in clients using libpq and optimizes the vacuum strategy to reduce the need for full-table freezes, contributing to better overall database performance.
• This release introduces CPU acceleration through SIMD technology in both x86 and ARM architectures, resulting in notable performance gains when processing ASCII and JSON strings, as well as when performing array and subtransaction searches, further enhancing the database’s speed and efficiency.

Logical Replication

• PostgreSQL 16 introduces the ability to perform logical replication from a standby instance, enabling the distribution of workloads by using standby to replicate changes to downstream systems.
• Performance enhancements in this release for logical replication include parallel processing for large transactions, the use of B-tree indexes for tables without primary keys, and faster initial table synchronization in binary format.
• Access control improvements in this release encompass the addition of the predefined role “pg_create_subscription,” granting users the capability to create logical subscriptions, and the beginning of bidirectional logical replication support, enabling data replication between tables from different publishers.

Access Control and Security

• More precise access control options, empowering administrators to finely tune database access are introduced. This includes enhancements in the management of pg_hba.conf and pg_ident.conf files, offering regular expression support for matching user and database names, as well as the ability to incorporate external configuration files using “include” directives.
• PostgreSQL 16 introduces a set of security-focused client connection parameters. Notably, the “require_auth” parameter allows clients to specify their preferred authentication parameters from the server, enhancing security customization. Additionally, the “sslrootcert” parameter enables PostgreSQL to utilize the client’s OS-provided trusted certificate authority (CA) store for secure SSL/TLS connections.
• This release includes support for Kerberos credential delegation, bolstering security when connecting to trusted services through extensions like postgres_fdw and dblink. This feature ensures that authenticated credentials are used, elevating the overall security of connections to external systems and services.

Monitoring

• Enhanced database performance tuning capabilities by introducing pg_stat_io, a valuable resource for in-depth analysis of I/O access patterns.
• This release introduces a timestamp field in the pg_stat_all_tables view, enabling users to track the last scan time of tables and indexes more effectively.
• PostgreSQL 16 enhances query readability by logging parameterized statement values in auto_explain and improves query tracking accuracy in both pg_stat_statements and pg_stat_activity.

In my next blog, we shall cover the updates on a more granular level