As has been the case every year between September and October, the release of the new version of PostgreSQL 17 has been announced , and as with all previous versions, the priority remains improving performance and optimizing resources, especially for operations such as VACUUM, I/O and queries, while the most important new feature of this release is incremental backup.
For enterprise users, the update brings a host of improvements that simplify database administration and use. While PostgreSQL has historically been a relational database, this release expands its integration with advanced capabilities for working with JSON documents, a feature commonly associated with MongoDB Atlas.
What's new in PostgreSQL 17
In this new version of PostgreSQL 17, a new data structure has been introduced in the VACUUM operation, which reduces memory consumption by up to 20 times, allowing these operations to be faster and more efficient in terms of shared resources.
Regarding performance improvements, write-away register (WAL) management has been optimized, achieving up to twice the write performance on systems with many concurrent queries. A streaming I/O interface has been proposed to improve sequential search and statistics updates after the ANALYZE operation.
In addition, improvements have been implemented in queries with "IN" clauses when using B-tree indexes, as well as the parallel construction of BRIN indexes, the optimization of "NOT NULL" checks, and the acceleration of the processing of common table expressions (CTEs). For intensive calculations, SIMD instructions, such as AVX-512, have been incorporated to accelerate operations such as the `bit_count` function.
Another of the novelties that stands out are the New features for JSON, since the Users can now query JSON data types using standard SQL commands. This is possible thanks to the new functionality of the table json, which transforms JSON data into a tabular structure. From there, users can perform queries using the clause FROM in SQL, allowing data to be returned in a relational format. The `JSON_TABLE` function has been added to convert JSON data into standard PostgreSQL tables.
Also noteworthy is the new incremental backup functionality in PostgreSQL 17, which allows you to back up only the changes made since the last full or incremental backup , optimizing bandwidth usage and reducing storage requirements. While PostgreSQL already offered solutions like WAL Archiving, which replicated the Write-Ahead Log (WAL) to maintain the necessary sections of the database, version 17 introduces, for the first time, a native mechanism for performing incremental backups. This system not only minimizes operational disruptions by having less impact on performance, but also significantly reduces recovery times—a key aspect, as restoring backups is often a slow and frustrating process for users.
A platform-independent engine was implemented to process locale properties, enabling consistent query result sorting. This engine considers factors such as the presence of special characters and case sensitivity when sorting values. The logical replication mechanism now simplifies the database upgrade process, eliminating the need to delete logical replication slots or resynchronize data when migrating to a new version.
Other notable changes include :
- Exporting long strings with the `COPY` command has been sped up to two times.
- Copying has been optimized when the source and destination encodings match.
- Added `ON_ERROR` option to allow import to continue despite errors inserting data.
- The ability to use columns with unique identifiers in partitioned tables has been added.
- A failover feature has been implemented to switch to a backup server in fault-tolerant configurations.
- Added a new pg_createsubscriber utility to convert a physical replica to a logical replica.
- The “sslnegotiation” option has been introduced to negotiate secure TLS connections directly using the ALPN TLS extension.
- Added the predefined role pg_maintain, which grants users the right to perform operations related to DBMS maintenance
Finally, if you are interested in learning more, you can find the details at the following link.