PostgreSQL 17 came out in September. Let’s see what’s new.
Query And Operational Performance Improvements
PostgreSQL 17 builds on recent releases and continues to enhance performance throughout the system. The vacuum process, responsible for reclaiming storage, now uses a new internal data structure that reduces memory usage by up to 20x, while also improving the time it takes to complete its tasks. Additionally, the previous 1GB memory limit for the vacuum process (controlled by `maintenance_work_mem`) has been removed, allowing you to allocate more resources to vacuuming.
This release introduces a new interface for stream I/O, leading to performance improvements in sequential scans and when running ANALYZE. PostgreSQL 17 also offers new configuration parameters that manage the scalability of transaction, subtransaction, and multixact buffers.
The optimizer in PostgreSQL 17 can now use planner statistics and the sort order of common table expressions (WITH queries) to speed up query execution. The release also improves the performance of queries using the IN clause with a B-tree index. Additionally, PostgreSQL 17 can now eliminate redundant IS NOT NULL checks for columns with a NOT NULL constraint, and it skips processing queries with an IS NULL clause on NOT NULL columns. Finally, parallel index builds are now supported for BRIN indexes in this release.
Partitioned And Distributed Workloads Enhancements
PostgreSQL 17 introduces greater flexibility in partition management by enabling both partition splitting and merging. It also adds support for identity columns and exclusion constraints in partitioned tables. Additionally, the PostgreSQL foreign data wrapper (postgres_fdw) now delivers performance improvements for queries with EXISTS and IN subqueries, as these can be pushed down to the remote server for execution.
The release enhances logical replication with features that simplify its use in high-availability workloads and upgrades. Starting with PostgreSQL 17, logical replication slots no longer need to be dropped during upgrades with pg_upgrade, eliminating the need to resynchronize data afterward. It also introduces failover control for logical replication, offering more management options in high-availability setups. Furthermore, logical replication subscribers can now utilize hash indexes for lookups, and the new pg_createsubscriber command-line tool simplifies adding logical replication to replicas using physical replication.
Updates In JSON
PostgreSQL 17 further advances its support for the SQL/JSON standard by introducing JSON_TABLE, which converts JSON data into a standard PostgreSQL table. It also adds SQL/JSON constructors (JSON, JSON_SCALAR, JSON_SERIALIZE) and query functions (JSON_EXISTS, JSON_QUERY, JSON_VALUE). These features were initially planned for the PostgreSQL 15 release but were delayed during the beta phase due to design considerations, highlighting the importance of beta testing! Additionally, PostgreSQL 17 expands its jsonpath functionality and introduces the ability to convert JSON values into different data types.
MERGE improvements
The MERGE command now includes support for the RETURNING clause, allowing you to work with the modified rows directly. You can also use the new merge_action function to determine which part of the MERGE command made changes to a row. PostgreSQL 17 extends the functionality of the MERGE command to allow updates on views and introduces a WHEN NOT MATCHED BY SOURCE clause, enabling you to define actions for cases where a source row does not meet the specified conditions.
Performance Changes In COPY
COPY, used for efficiently bulk loading and exporting data in PostgreSQL, now offers up to a 2x performance boost when exporting large rows in PostgreSQL 17. Performance has also improved when the source and destination encodings match. A new ON_ERROR option allows the COPY operation to continue even if an error occurs while inserting a row. Additionally, PostgreSQL 17 enhances support for asynchronous operations and provides more secure query cancellation routines, which can be utilized by drivers through the libpq API.
ALPN Support
PostgreSQL 17 introduces a new connection parameter, sslnegotiation, which enables PostgreSQL to perform direct TLS handshakes when using ALPN (Application-Layer Protocol Negotiation), reducing the need for a network roundtrip. PostgreSQL is registered under the name "postgresql" in the ALPN directory.
New Role pg_maintain
PostgreSQL 17 introduces a new predefined role, pg_maintain, which grants users the ability to perform maintenance operations such as VACUUM, ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW, REINDEX, and LOCK TABLE on all relations. Additionally, this release ensures that the search_path is secure for maintenance tasks like VACUUM, ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX. Finally, users can now use the ALTER SYSTEM command to set values for unrecognized custom server variables.
Improvements In Backups
PostgreSQL 17 introduces incremental backup capabilities with pg_basebackup and includes a new tool called pg_combinebackup, which is used during the backup restoration process. Additionally, a new --filter flag has been added to pg_dump, allowing you to specify a file with instructions on which objects to include or exclude from the dump.
EXPLAIN and pg_stat_statements
The EXPLAIN command, which provides insights into query plans and execution, introduces two new options in PostgreSQL 17: SERIALIZE, which measures the time spent converting data for network transmission, and MEMORY, which reports on the memory usage of the optimizer. EXPLAIN can also now display the time spent on I/O block reads and writes.
PostgreSQL 17 enhances pg_stat_statements by normalizing parameters for CALL, reducing the number of entries for frequently called stored procedures. VACUUM progress reporting now includes the progress of index vacuuming. Additionally, PostgreSQL 17 introduces a new view, pg_wait_events, which provides descriptions of wait events and can be used alongside pg_stat_activity for deeper insight into why an active session is waiting. Furthermore, some information from the pg_stat_bgwriter view has been moved to the new pg_stat_checkpointer view.
Summary
PostgreSQL remains the best choice for cloud-native applications. No surprise Postgres is everywhere.
