PostgreSQL 17 Beta 1 has been released. The update offers enhancements to query and operational performance, such as a 20x memory reduction in vacuum, a stream I/O interface, and configurable parameters for transaction, subtransaction, and multixact buffer scalability. It also optimizes queries utilizing planner statistics and the sort order of frequent table expressions (WITH queries), speeds up query execution with the IN clause and a B-tree index, eliminates superfluous IS NOT NULL statements, and supports concurrent index construction for BRIN indexes.

PostgreSQL 17 Beta 1 Released!

The PostgreSQL Global Development Group announces that the first beta release of PostgreSQL 17 is now  available for download. This release contains previews of all features that will be available when PostgreSQL 17 is made generally available, though some details of the release can change during the beta period.

You can find information about all of the PostgreSQL 17 features and changes in the  release notes:

https://www.postgresql.org/docs/17/release-17.html

In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 17 on your systems to help us eliminate bugs or other issues that may exist. While we do not advise you to run PostgreSQL 17 Beta 1 in production environments, we encourage you to find ways to run your typical application workloads against this beta release.

Your testing and feedback will help the community ensure that the PostgreSQL 17 release upholds our standards of delivering a stable, reliable release of the world's most advanced open source relational database. Please read more about our  beta testing process and how you can contribute:

https://www.postgresql.org/developer/beta/

PostgreSQL 17 Feature Highlights

Query and Operational Performance Improvements

PostgreSQL 17 builds on recent releases and continues to improve performance across the entire system.  Vacuum, the PostgreSQL process responsible for reclaiming storage, has a new internal data structure that has shown up to a 20x memory reduction, along with improvements in overall time to complete its work. Additionally, the vacuum process no longer has a 1GB limit on the memory it can use (controlled by  maintenance_work_mem), giving you the option to apply more resources to vacuuming.

This release introduces an interface to stream I/O, and can show performance improvements when performing sequential scans and running  ANALYZE. PostgreSQL 17 also includes configuration parameters that can control scalability of  transaction, subtransaction and multixact buffers.

PostgreSQL 17 can now use both planner statistics and the sort order of  common table expressions ( WITH queries) to further optimize these queries and help them to execute more quickly. Additionally, this release significantly improves execution time of queries that use the IN clause with a  B-tree index. Starting with this release, PostgreSQL can remove redundant IS NOT NULL statements from execution on columns that have a NOT NULL constraint, and no longer needs to do work on queries that contain an IS NULL clause on an IS NOT NULL column. Now as of PostgreSQL 17, you can use parallel index builds for  BRIN indexes.

Workloads with highly concurrent changes can benefit from PostgreSQL 17 with an improvement to how  write-ahead log ( WAL) locks are managed, with some tests showing up to a 2x performance improvement.

Finally, PostgreSQL 17 adds more explicit SIMD instructions, including AVX-512 support for the  bit_count function.

Partitioned and distributed workloads enhancements

PostgreSQL 17 brings more flexibility to partition management, adding the ability to both split and merge partitions, and adds support to partitioned tables for identity columns and exclusion constraints. Additionally, the  PostgreSQL foreign data wrapper ( postgres_fdw) can realize performance benefits from queries with EXISTS and IN subqueries as these can now be pushed down to the remote server.

PostgreSQL 17 adds new capabilities to logical replication that make it simpler to use it in high availability workloads and on upgrades. Starting with upgrades from PostgreSQL 17 to newer versions, you no longer need to drop  logical replication slots when using  pg_upgrade, allowing you to avoid resynchronizing data after an upgrade. Additionally, this release introduces failover control for logical replication, providing more control for managing PostgreSQL databases in high availability environments. PostgreSQL 17 also lets logical replication subscribers use hash indexes for lookups, and introduces the pg_createsubscriber command-line tool for adding logical replication on a replica using physical replication.

Developer Experience

PostgreSQL 17 continues to build on the SQL/JSON standard, adding support for the JSON_TABLE features that can convert JSON to a standard PostgreSQL table, and SQL/JSON constructor (JSON, JSON_SCALAR, JSON_SERIALIZE) and query functions (JSON_EXISTS, JSON_QUERY, JSON_VALUE). Notably, these features were originally planned for the PostgreSQL 15 release but were reverted during the beta period due to design considerations, which is one reason we ask for you to help us test features during beta! Additionally, PostgreSQL 17 adds more functionality to its jsonpath implementation, and the ability to convert JSON values to different data types.

The  MERGE command now supports the RETURNING clause, letting you further work with modified rows. You can also see what part of the MERGE command modified the row using the new merge_action function. PostgreSQL 17 also lets you update views using the MERGE command, and adds a WHEN NOT MATCHED BY SOURCE clause to let you provide behavior if a source row doesn't meet the conditions.

COPY is used to efficiently bulk load and export data from PostgreSQL, and now with PostgreSQL 17 you may see up to a 2x performance improvement when exporting large rows. Additionally, COPY has improved performance when the source encoding matches the destination encoding, and has a new option, ON_ERROR, that lets the copy proceed even if there is an error inserting a row. PostgreSQL 17 also provides better support for  asynchronous and more secure query cancellation routines, which drivers can adopt using the libpq API.

PostgreSQL 17 includes a built-in collation provider that provides similar sorting semantics to the C collation except with UTF-8 encoding rather than SQL_ASCII. This new collation is guaranteed to be immutable, ensuring that the return values of your sorts won't change regardless of what system your PostgreSQL installation runs on.

Security Features

PostgreSQL 17 adds a new connection parameter, sslnegotiation, which allows PostgreSQL to perform direct TLS handshakes when using  ALPN, eliminating a network roundtrip. PostgreSQL is registered as postgresql in the ALPN directory.

This release introduces event triggers that execute during authentication, and in libpq includes a new API called PQchangePassword that automatically hashes passwords on the client-side to prevent accidental plaintext logging in the server.

PostgreSQL 17 adds a new  predefined role called pg_maintain that gives users permission to execute VACUUM, ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW, REINDEX, and LOCK TABLE on all relations. This release also ensures the search_path is safe for maintenance operations like VACUUM, ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW and INDEX. Finally, users can now use ALTER SYSTEM to set the values for unrecognized custom server variables.

Backup and Export Management

PostgreSQL 17 introduces the ability to perform incremental backups using  pg_basebackup, and adds a new utility called  pg_combinebackup that's used as part of the backup restoration process. This release adds a new flag to  pg_dump called --filter, letting you specify a file that contains instructions on what objects to include/exclude from a dump.

Monitoring

The  EXPLAIN command, which provides information about query plans and execution, adds two new options: SERIALIZE, which shows the amount of time it takes to convert data for network transmission, and MEMORY, which reports optimizer memory usage. Additionally, EXPLAIN can now show how much time is spent for I/O block reads and writes.

PostgreSQL 17 normalizes the parameters for CALL in  pg_stat_statements, reducing the number of entries for frequently called stored procedures. Additionally,  VACUUM progress reporting now shows the progress of vacuuming indexes. PostgreSQL 17 also introduces a new view,  pg_wait_events, which provides descriptions about wait events and can be combined with pg_stat_activity to give more insight into why an active session is waiting. Additionally, some information in the  pg_stat_bgwriter view is now split out into the new  pg_stat_checkpointer view.

Additional Features

Many other new features and improvements have been added to PostgreSQL 17. Many of these may also be helpful for your use cases. Please see the  release notes for a complete list of new and changed features:

https://www.postgresql.org/docs/17/release-17.html

Testing for Bugs & Compatibility

The stability of each PostgreSQL release greatly depends on you, the community, to test the upcoming version with your workloads and testing tools in order to find bugs and regressions before the general availability of PostgreSQL 17. As this is a Beta, minor changes to database behaviors, feature details, and APIs are still possible. Your feedback and testing will help determine the final tweaks on the new features, so please test in the near future. The quality of user testing helps determine when we can make a final release.

A list of  open issues is publicly available in the PostgreSQL wiki. You can  report bugs using this form on the PostgreSQL website:

https://www.postgresql.org/account/submitbug/

Beta Schedule

This is the first beta release of version 17. The PostgreSQL Project will release additional betas as required for testing, followed by one or more release candidates, until the final release around September/October 2024. For further information please see the  Beta Testing page.

PostgreSQL 17 Beta 1 Released!