Enhanced performance with PostgreSQL^® 16

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.