Jan 18, 2024
Enhanced performance with PostgreSQL® 16
Aiven for PostgreSQL® adds support for major version 16. Find out what the key improvements are and how you can get the new version.
Many Aiven for PostgreSQL® customers want to be at the forefront of innovation and ensure their developers have access to the latest database features that enhance their productivity and streamline their work. In support of this, Aiven now makes PostgreSQL® version 16 available. The new version comes with a set of enhancements that optimize performance and provide improved monitoring and security. These new features make workload management more efficient and secure and also contribute to an improved developer experience.
Release Highlights
Improved performance with better query planning and optimized bulk loading
This release incorporates query planner optimizations, introducing parallelization for FULL
and RIGHT
joins, optimized plans for aggregate functions with a DISTINCT
or ORDER BY
clause, incremental sorts for SELECT DISTINCT
queries, and more efficient window function execution. RIGHT
and OUTER
“anti-joins” have been refined to facilitate the identification of missing rows. Additionally, bulk loading is optimized, achieving up to 300% performance gains with COPY
in both single and concurrent operations. The update introduces load balancing for libpq
clients, optimizes the vacuum strategy, and utilizes CPU acceleration via SIMD
for notable improvements in string processing, array operations, and subtransaction searches.
Enhanced replication
PostgreSQL 16 introduces enhancements in replication for seamless data exchange between cluster nodes, improving high availability, scalability, and disaster recovery. Performance improvements include parallel transactions for subscribers and enhanced efficiency with B-tree indexes.
Enriched developer experience
This release enriches developer experience with SQL/JSON syntax additions like JSON_ARRAY()
, JSON_ARRAYAGG()
, and IS JSON
. Noteworthy features include using underscores for thousands separators and non-decimal integer literals. The update introduces \bind
in psql
for parameterized queries and improves text collations with default ICU support.
Improved monitoring
PostgreSQL 16 enhances performance tuning with key additions to monitoring capabilities. The introduction of pg_stat_io
provides crucial I/O metrics for detailed analysis of access patterns. In the pg_stat_all_tables
view, a new timestamp attribute, last_seq_scan
, records the time of the last table scan. Similarly, pg_stat_all_indexes
gains the last_idx_scan
column, indicating the time of the last index scan. The auto_explain
readability is improved by logging input values for parameterized statements automatically. Additionally, PostgreSQL 16 refines the query tracking algorithm in pg_stat_statements
and pg_stat_activity
for enhanced precision.
Strengthened security and access control
This release strengthens security with refined access control. The release adds require_auth
for precise client-server authentication and sslrootcert="system"
for enhanced SSL/TLS security. Supporting Kerberos credential delegation ensures secure connections for extensions like postgres_fdw
and dblink
.
Get started with PostgreSQL® 16
Upgrade today to PostgreSQL 16 to take advantage of the new features.
If you already use Aiven for PostgreSQL, you can run an in-place upgrade to migrate to the newest version. You can also easily test the upgrade first on a fork of the database to be upgraded.
If you are not yet using Aiven for PostgreSQL, sign up here for a free trial.
Stay updated with Aiven
Subscribe for the latest news and insights on open source, Aiven offerings, and more.
Related resources
Jun 9, 2022
Join us for a browse about the ins and outs of PostgreSQL® on this page of information and resources.
Feb 3, 2023
Major version updates for your PostgreSQL in seconds? Yes, Aiven can do it! Come and find out more.
Jan 13, 2023
No more worries about losing your logical replication slots during maintenance or failover - Aiven now recreates them for you! Find out more here.