We're excited to share that PostgreSQL 16 is now generally available on Azure Database for PostgreSQL - Flexible Server. This version introduces a variety of new features that are designed to enhance the functionality and performance of your databases.
In the following article, we will outline some key features of PostgreSQL 16, focusing on areas such as replication enhancements, performance improvements, and query optimization. These features aim to support Azure users in managing their databases more efficiently and with greater ease. Whether it’s for scaling your application or improving overall database performance, PostgreSQL 16 offers valuable new tools and improvements. Let’s dive into what this update means for you as an Azure Database for PostgreSQL - Flexible Server user.
Preserving replication slots after failover
If you're using both High Availability (HA) and logical replication in PostgreSQL, there's good news with version 16. Previously, in the event of a failover, the replication slots would be lost. This was a problem because it meant you'd have to recreate the replication slots and resynchronize the data. Now, PostgreSQL 16 has improved this process. The standby server will recognize the replication slots from the primary server, so you don't have to set them up again after a failover. This update is a big help for anyone using HA with logical replication. For those using older versions of PostgreSQL, the pg_failover_slots extension can provide similar functionality for versions up to PostgreSQL 15.
This development is not just an incremental update - it's a game-changer that opens new architectural possibilities. By shifting some of the data replication responsibilities away from the primary server to read replicas, it reduces the load on the primary server.
Furthermore, the combination of physical and logical replication methods enhances the robustness of the database setup. Users of Azure Database for PostgreSQL - Flexible Server can now explore more sophisticated and scalable database architectures with the adoption of this new feature.
Cascading replication, combining physical and logical replication.
Enhanced IO Monitoring with pg_stat_io
PostgreSQL 16 introduces pg_stat_io, a new comprehensive view that offers deeper insights into IO activity within the database. This addition is a leap forward for database monitoring and performance tuning. Previously, statistics in PostgreSQL were not detailed enough to provide the necessary information for fine-tuning performance. With pg_stat_io, the information is broken down by backend type, IO context, and IO operation, delivering a level of detail that was not available before.
Screenshot of pg_stat_io view.
This granular visibility is invaluable for database administrators looking to optimize their systems for better performance and efficiency. For those interested in understanding this new Postgres feature in depth, there's a video available from the author of pg_stat_io and a Postgres teammate of mine here at Microsoft, Melanie Plageman, offering a thorough explanation of how pg_stat_io works and how to leverage it for database optimization.
Load balancing with libpq in PG16
Database adapters based on libpq, which are used across a host of programming languages, including Python, C, C++, and Ruby, now include enhancements that greatly improve scalability and security within database systems.
The load_balance_hosts=random option is an addition that transforms connection distribution by enabling random selection among a list of hosts. This load balancing feature is particularly advantageous for spreading out the workload on multiple replica servers. When utilized alongside target_session_attrs=standby, it directs connections to standby servers, which is key for scalable and resilient architecture.
The require_auth option further strengthens security by allowing the specification of secure authentication methods, like require_auth=scram-sha-256, ensuring the use of stringent authentication protocols.
Subquery in FROM does NOT require an alias
PostgreSQL 16 introduces a more flexible approach to subqueries, making the transition from databases like Oracle smoother.
In prior versions, a subquery in the FROM clause required an alias; otherwise, it would result in an error. However, starting with PostgreSQL 16, you can run a query like SELECT * FROM (SELECT 1); without needing to assign an alias to the subquery. This update reduces the effort needed to rewrite queries, thereby easing the migration path and potentially speeding up the transition to PostgreSQL.
PostgreSQL 16 marks a significant stride in database performance through a series of query planner enhancements. The latest release has empowered the query planner to parallelize FULL and RIGHT joins, which can lead to more efficient query execution. It also delivers more finely tuned plans for queries incorporating aggregate functions alongside DISTINCT or ORDER BY clauses and introduces incremental sorting for SELECT DISTINCT queries. These optimizations extend to window functions as well, which now run more effectively.
Moreover, PostgreSQL 16 has seen advances in bulk data loading with the COPY command. Both single and concurrent loading operations have become faster, with some tests indicating improvements of up to 300%. The release also brings new client load balancing capabilities for those using libpq-based adapters, further optimizing performance.
Vacuuming, the process of reclaiming storage occupied by dead tuples, has also been refined. The enhancements in vacuum strategy minimize the need for full-table vacuums, a boon for database maintenance and uptime.
A notable addition in PostgreSQL 16 is the introduction of CPU acceleration using Single Instruction, Multiple Data (SIMD) on both x86 and ARM architectures. This acceleration boosts performance for ASCII and JSON string processing and enhances the speed of array and subtransaction searches, solidifying PostgreSQL 16 as a top choice for high-performance database solutions.
There's much more to PostgreSQL 16 than we've touched on, with a host of enhancements ready to be explored. To discover all the new capabilities and improvements, the detailed release notes for PostgreSQL 16 are the perfect resource. For Azure Database for PostgreSQL - Flexible Server users, the transition to this new version is straightforward, thanks to the major version upgrade feature. Upgrade to PostgreSQL 16 and take full advantage of the most advanced features yet.
Additionally, for a deeper dive into the capabilities of PostgreSQL 16, we recommend Bruce Momjian's insightful talk, “What’s New in Postgres 16”, presented at the Chicago PUG.
This presentation is a fantastic resource for anyone looking to understand the full scope of PostgreSQL 16's enhancements. Our thanks go to Henrietta (Hettie) Dombrovskaya for organizing this event, and we encourage you to view the talk for a comprehensive overview of what PostgreSQL 16 has to offer.