PostgreSQL 16 is here, and it's packed with exciting new features and improvements! If you're eager to dive in and start using this latest version of one of the world's most powerful open-source databases, you're in the right place.

In this blog, we'll keep things simple and straightforward. Whether you're a PostgreSQL pro or a newcomer, we'll guide you through the essentials of PostgreSQL 16, so you can quickly get up to speed and make the most of what it has to offer. So, let's jump right in and explore what PostgreSQL 16 brings to the table!

Performance improvements in PostgreSQL 16

In PostgreSQL 16, there are significant performance improvements across several key areas:

New optimizations in query planner

In this latest release, the query planner gains the ability to parallelize FULL and RIGHT joins, generate more optimized plans for queries involving aggregate functions with DISTINCT or ORDER BY clauses, utilize incremental sorts for SELECT DISTINCT queries, and optimize the execution of window functions, leading to more efficient operation. Furthermore, PostgreSQL 16 refines RIGHT and OUTER anti-joins, empowering users to pinpoint rows that are absent in a joined table.

Bulk loading

This release also brings significant improvements to bulk loading, applicable to both single and concurrent operations using the COPY command. Tests have shown remarkable performance enhancements of up to 300% in certain scenarios.

Load balancing for libpq

Starting from version 16, tools that utilize libpq, such as psql, now have support for load balancing at the connection level. A new option has been added to the utility i.e. load_balance_hosts which can be used for load balancing. Detailed info and a demo for the utility is provided in our blog: PostgreSQL 16 brings Load Balancing Support in libpq

Improved vacuum

PostgreSQL 16 comes with enhancements to the vacuum strategy that reduce the necessity for full-table freezes.

Performance boosts

This version introduces CPU acceleration through SIMD technology for both x86 and ARM architectures, resulting in notable performance boosts during operations involving ASCII and JSON strings, as well as array and subtransaction searches.

Logical replication in PostgreSQL 16

Bidirectional Replication

This release initiates the process of adding support for bidirectional logical replication, introducing functionality for replicating data between two tables originating from different publishers.

This feature is a game-changer for high availability, Write Scalability, disaster recovery, and collaboration scenarios. While its implementation requires careful consideration, bidirectional replication opens exciting possibilities for seamless data exchange.

Detailed information can be found in our blog: Bidirectional Logical Replication in PostgreSQL 16

Logical replication from standby

In PostgreSQL 16, users gain the capability to engage in logical replication from a standby instance, signifying that a standby server can now publish logical changes to other servers. This offers users, possibilities for distributing workloads, such as opting to use a standby server instead of the more heavily utilized primary server to logically replicate changes to downstream systems.

Performance enhancements for logical replication

Subscribers are now able to apply substantial transactions using parallel workers. In cases where tables lack a primary key, subscribers can utilize B-tree indexes instead of sequential scans to locate rows

Logical replication control enhancements

Introduction of the new predefined role, pg_create_subscription, which grants users the authority to create new logical subscriptions.

Detailed information about predefined roles in PostgreSQL is explained in our blog: Overview of Predefined Roles in PostgreSQL

Monitoring

IO monitoring

PostgreSQL 16 introduces pg_stat_io, a fresh source of crucial I/O metrics, enabling in-depth inspection of I/O access patterns.

Structure of pg_stat_io

‍

pg_stat_all_tables

This view offers comprehensive statistics pertaining to every table within the present database. These statistics include the counts of sequential and index scans, updates, deletes, inserts, and more. Additionally, it furnishes details regarding the quantities of active and obsolete tuples, as well as statistics related to vacuum and analyze operations.

in this release, a new attribute is appended to the pg_stat_all_tables view. This attribute records a new timestamp column i.e. last_seq_scan indicating the time of the last scan table.

Latest structure for pg_stat_all_tables:

‍

pg_stat_all_indexes

This view offers comprehensive statistics for all indexes within the present database. It encompasses details such as the count of index scans, the number of rows fetched by each specific index, etc.

In this release, a new column is appended to the pg_stat_all_indexes view, i.e. last_idx_scan which indicates the time of the last index scan for the particular index.

Latest structure for pg_stat_all_indexes

‍

Improved ‘auto_explain’

The auto_explain extension offers an automated way to log execution plans for slow statements, eliminating the need to execute EXPLAIN manually.

In PostgreSQL 16, the auto_explain feature enhances readability by recording input values for parameterized statements

Improved query tracking

PostgreSQL 16 refines the precision of the query tracking algorithm utilized by pg_stat_statements and pg_stat_activity.

Security in PostgreSQL

In terms of security, PostgreSQL 16 introduces several enhancements and new features:

More Detailed Access Control Options

• This update includes improvements to the management of pg_hba.conf and pg_ident.conf files. It enables regular expression matching for user and database names, providing more flexible and fine-grained access control. Additionally, it includes directives for external configuration files, allowing for better management of access control rules.

Security-Focused Client Connection Parameters

• PostgreSQL 16 introduces the require_auth function, empowering clients to specify the authentication parameters they are willing to accept from a server. This feature adds an extra layer of control over the security of client-server interactions.
• Moreover, the introduction of sslrootcert=system signifies that PostgreSQL should utilize the trusted certificate authority (CA) store provided by the client's operating system, enhancing the security of SSL/TLS connections.

Kerberos Credential Delegation

• This release supports Kerberos credential delegation, allowing extensions like postgres_fdw and dblink to use authenticated credentials to establish connections with trusted services securely. This feature enhances the authentication and security capabilities of PostgreSQL 16.

These security enhancements in PostgreSQL 16 bolster the database system's capabilities to protect sensitive data and ensure secure connections, making it a reliable choice for various applications where security is paramount.

The release of PostgreSQL v16 reflects the PostgreSQL community's unwavering commitment to innovation. This latest version not only underscores the community's dedication but also demonstrates its agility in responding to the ever-evolving needs of developers and database administrators.

With a host of new features and enhanced functionalities, this release stands as a powerful and versatile tool for developers and database administrators, equipping them with the capabilities needed to tackle a wide range of database challenges.

Connect with us for exceptional open-source database management and consulting services. Take the next step in database innovation!

{{cta}}