PostgreSQL 17 delivers a leap forward for open-source databases

The PostgreSQL Global Development Group has unveiled PostgreSQL 17, the latest iteration of what is widely-regarded as the world’s most advanced open-source database. This release marks a significant milestone in the database’s evolution, bringing substantial performance enhancements and new features that cater to both emerging and established data management needs.

PostgreSQL 17 builds upon its robust foundation, offering improved performance and scalability whilst adapting to contemporary data access and storage patterns. The update introduces a revamped memory management system for vacuum operations, optimisations for storage access, and enhancements for high concurrency workloads. Users can expect speedier bulk loading and exports, alongside query execution improvements for indexes.

“PostgreSQL 17 highlights how the global open-source community, which drives the development of PostgreSQL, builds enhancements that help users at all stages of their database journey,” said Jonathan Katz, a member of the PostgreSQL core team

 “Whether it’s improvements for operating databases at scale or new features that build on a delightful developer experience, PostgreSQL 17 will enhance your data management experience.”

One of the standout features in this release is the significant reduction in memory consumption for vacuum processes. The new internal memory structure uses up to 20 times less memory, leading to faster vacuum operations and freeing up resources for other tasks.

High concurrency workloads stand to benefit from up to twice the write throughput, thanks to improvements in write-ahead log (WAL) processing. The introduction of a streaming I/O interface accelerates sequential scans and ANALYZE operations, further boosting overall performance.

Query execution sees notable enhancements, particularly for queries with IN clauses utilising B-tree indexes. The release also introduces parallel builds for BRIN indexes and incorporates SIMD (Single Instruction/Multiple Data) support to accelerate certain computations.

For developers, PostgreSQL 17 expands its JSON capabilities with the addition of JSON_TABLE, allowing for the conversion of JSON data into standard PostgreSQL tables. The release also introduces SQL/JSON constructors and query functions, providing more flexibility in handling JSON data.

Bulk loading and data exporting receive a performance boost, with the COPY command seeing up to twice the speed for large row exports. A new ON_ERROR option allows imports to continue even when insert errors occur, enhancing data migration processes.

Logical replication, crucial for real-time data streaming, sees significant improvements. Users can now perform major version upgrades without dropping logical replication slots, simplifying the upgrade process. The introduction of failover control for logical replication enhances resilience in high-availability environments.

Security and operational management also receive attention in this release. A new TLS option, sslnegotiation, supports direct TLS handshakes using ALPN. The addition of the pg_maintain predefined role grants users permission to perform maintenance operations without full superuser access.

Backup capabilities are expanded with pg_basebackup now supporting incremental backups, complemented by the new pg_combinebackup utility for reconstructing full backups.

Monitoring and analysis features see enhancements, with EXPLAIN now showing time spent on local I/O operations and new options for examining data conversion time and memory usage. The ability to monitor index vacuuming progress and a new system view for session wait events provide deeper insights into database operations.

PostgreSQL’s continued evolution reflects its status as a preferred open-source relational database for organisations of all sizes. With its 25-year history of open-source development and a dedicated global community, PostgreSQL 17 represents another leap forward in data management technology.

(Photo by Joshua Earle)

See also: Safe Coding: Google’s strategy reduces memory safety vulnerabilities

Looking to revamp your digital transformation strategy? Learn more about Digital Transformation Week taking place in Amsterdam, California, and London. The comprehensive event is co-located with AI & Big Data Expo, Cyber Security & Cloud Expo, and other leading events.

Explore other upcoming enterprise technology events and webinars powered by TechForge here.

Tags: coding, databases, development, open source, open-source, postgresql, postgresql 17, programming