At Hieda, we’re always keeping an eye on the latest and greatest in open-source database-related software to keep you informed of interesting changes. A few weeks ago, the latest version of PostgreSQL was released with a bunch of significant improvements and new features. In this blog post, we’ll go over some of these improvements together, and see where PostgreSQL 16 makes a difference.

Performance Boosts

First and foremost, those of you that have been working with PostgreSQL for a while will be pleased to learn that PostgreSQL 16 introduces substantial performance enhancements.  

• PostgreSQL now supports parallel execution of FULL and RIGHT JOINS, as well as other optimisations related to executing queries.  
• SELECT DISTINCT queries have also received a significant performance boost when dealing with large datasets, thanks to incremental sorts.
• You can now execute large transactions in parallel for logical replication subscribers. This enhancement can dramatically enhance the performance of replication workloads.  
• Bulk data loading also gets a speed boost, with concurrent bulk loading optimized for up to a 300% improvement.  
• PostgreSQL 16 now caches RANGE and LIST partition lookups, which further turbocharges performance for bulk data loading in partitioned tables.

To put these performance upgrades into perspective, consider the following query:

```SQL

SELECT *

FROM users

JOIN addresses

ON users.id = addresses.user_id

JOIN phones

ON addresses.id = phones.address_id

WHERE users.name = 'John Doe';

```

In PostgreSQL 15, this query executed as a single sequential join. However, PostgreSQL 16 enables parallelization, resulting in a remarkable improvement in query execution time. For example, on a dataset of 1 million rows, the query that took 5 seconds in PostgreSQL 15 now takes just 2 seconds: an improvement of no less than 60%.

Logical Replication Advancements

PostgreSQL 16 takes logical replication to the next level with several enhancements. The standout feature is the ability to replicate data from a standby. This opens up exciting possibilities for multi-region architectures. You can have a primary database in one region and a standby in another, with the standby now capable of sending data to a third system in its region.  

Exciting as this is, it’s far from the only replication enhancement. Other improvements include:

• Initial table synchronization in binary format, which significantly reduces the time it takes to initially synchronize a new subscriber
• Replication without a primary key, which makes it possible to replicate data from tables that do not have a primary key. Thanks to this, subscribers now can use B-tree indexes instead of sequential scans to find rows.
• Improved security: subscription owners now need to have SET ROLE permissions on all tables in the replication set or be a superuser. As we’ll discuss below, there are other security measures included.

Enhanced Security Features

Security is a top priority in PostgreSQL 16. It introduces advanced access control options and robust security management. The handling of configuration files, such as pg_hba.conf and pg_ident.conf, has also been refined, enabling regular expression matching for user and database names. Additionally, external configuration files can now be included via directives.

Rounding off the security improvements, PostgreSQL 16 brings support for Kerberos credential delegation. This enables extensions like postgres_fdw and dblink to utilize authenticated credentials for connecting to trusted services.

Comprehensive Monitoring Capabilities

PostgreSQL 16 definitely doesn't skimp on monitoring features either. The pg_stat_io view offers in-depth insights into I/O activity of your system, while detailed statistics on sequential and index scans on tables enhance performance evaluation. Together, these enhancements make monitoring your PostgreSQL database noticeably easier.

The readability of auto_explain has also been improved by logging values passed into parameterized statements. As a result, the query tracking algorithm utilized by pg_stat_statements and pg_stat_activity has also been refined, leading to more accurate insights.

Additional Features and Improvements

As if all of that wasn’t enough, the PostgreSQL team decided to ship even more features and improvements with the new version. Among many others, PostgreSQL 16 now offers support for ICU collation rules, enhanced JSON support, improved security for connection parameters, more efficient shared buffer usage, and support for parallel execution of certain administrative tasks. An impressive list to say the least.

Conclusion

PostgreSQL 16 signifies a significant step forward for open-source databases, offering a wide range of important improvements and new features. These enhancements make PostgreSQL more performant, secure, and manageable than ever. If you're using PostgreSQL, upgrading to version 16 is a move we highly recommend.