A year after its previous major 16 release, the PostgreSQL Global Development Group announced the release of PostgreSQL 17, the newest iteration of the world’s most advanced open-source database, offering enhancements in performance, scalability, and adaptability to emerging data storage and access patterns.
In the announcement, Jonathan Katz, a member of the PostgreSQL core team, said:
“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. 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.”
PostgreSQL 17 Highlights
System-Wide Performance Gains
One of PostgreSQL 17’s standout features is the overhaul of the vacuum process’s memory management. This critical operation now consumes up to 20 times less memory, resulting in faster vacuum speeds and reduced shared resource use. Consequently, more resources are available for your workload.
Moreover, the new version brings improvements to the I/O layer. Thanks to optimizations in write-ahead log (WAL) processing, high concurrency workloads could experience up to twice the write throughput.
Additionally, a new streaming I/O interface accelerates sequential scans and speeds up the ANALYZE command when updating planner statistics.
Regarding query execution, PostgreSQL 17 enhances the performance of queries using IN clauses with B-tree indexes, the default index method.
BRIN indexes now support parallel builds, and several query planning improvements have been made, including optimizations for NOT NULL constraints and better processing of common table expressions.
The release also introduces more SIMD (Single Instruction/Multiple Data) support, utilizing AVX-512 for functions like bit_count.
Enhanced Developer Experience
PostgreSQL continues to lead in JSON support by adding the SQL/JSON JSON_TABLE command, which allows developers to convert JSON data into standard PostgreSQL tables.
The new version also supports SQL/JSON constructors and query functions, such as JSON_EXISTS, JSON_QUERY, and JSON_VALUE, providing more ways to interact with JSON data. Additional jsonpath expressions have been added to facilitate converting JSON data into native PostgreSQL data types.
The MERGE command, used for conditional updates, now includes a RETURNING clause and the ability to update views. Bulk loading and data exporting have also been optimized; exporting large rows using the COPY command can be up to twice as fast. A new ON_ERROR option in COPY allows imports to continue even if an insert error occurs.
Furthermore, PostgreSQL 17 expands functionality for managing partitioned data and data distributed across remote instances. Identity columns and exclusion constraints are now supported on partitioned tables. The postgres_fdw foreign data wrapper can push EXISTS and IN subqueries to remote servers for more efficient processing.
Additionally, the release includes a built-in, platform-independent, immutable collation provider. This ensures consistent text sorting results across different platforms by providing similar sorting semantics to the C collation but with UTF-8 encoding.
Logical Replication Enhancements
PostgreSQL 17 simplifies major version upgrades for those using logical replication by eliminating the need to drop logical replication slots. This change reduces downtime and the need for data resynchronization after an upgrade.
The release also introduces failover control for logical replication, enhancing resilience in high-availability environments. A new command-line tool, pg_createsubscriber, helps convert a physical replica into a new logical replica.
Security and Operational Improvements
Security and operational management see advancements as well. A new TLS option, sslnegotiation, allows for direct TLS handshakes using Application-Layer Protocol Negotiation (ALPN). The predefined role pg_maintain has been added, permitting users to perform maintenance operations.
Backup utilities receive significant updates. pg_basebackup now supports incremental backups and the new pg_combinebackup utility can reconstruct a full backup from incremental ones. The pg_dump tool includes a new --filter option to select specific objects when generating a dump file.
Monitoring and analysis features have been enhanced. The EXPLAIN command now displays the time spent on local I/O block reads and writes and introduces two new options: SERIALIZE and MEMORY, which are useful for analyzing data conversion times and memory usage.
The new release also reports the progress of vacuuming indexes and adds the pg_wait_events system view for better insight into session waits.
Of course, PostgreSQL 17 includes many other exciting new features and improvements. Visit the official announcement to review them, or read the release notes for a detailed view of all novelties.
Comprehensive documentation for PostgreSQL 17 is available in HTML and man page formats. You can also browse the documentation online in both HTML and PDF formats.
