PostgreSQL 17 released its first beta version. The main changes include improving query and operation performance, enhancing partitions and distributed workloads, improving the developer experience, and adding security features.
Query and write performance improvements
The latest version and build of PostgreSQL 17 continue to focus on overall system performance optimization. The PostgreSQLVacuum process responsible for reclaiming storage space uses a new internal data structure, which reduces the memory usage of the garbage collection process by up to 20 times, and reduces the time required for execution. In addition, the vacuum process is no longer subject to 1GB Memory usage is limited by maintenance_work_mem This means that you can allocate more resources to the vacuum process.
This version introduces the streaming I/O interface, enabling sequential scanning and running ANALYZE The performance of is improved. PostgreSQL 17 also adds configuration parameters to control the size of transactions, sub transactions, and multixact buffers.
PostgreSQL 17 can now use Planner's statistics and the common table expression CTE (that is WITH Query) the sorting order in the results to further optimize the speed of these queries. In addition, this version significantly improves the IN Clause, the query execution time when using the B-tree index.
Starting from this version, for those with NOT NULL Constrained columns. If the query contains redundant IS NOT NULL Statement. PostgreSQL will directly optimize it. Similarly, those with IS NULL PostgreSQL 17 also supports parallel building of BRIN indexes.
Workloads with highly concurrent write classes can significantly benefit from the improvement of pre write log (WAL) lock management in PostgreSQL 17. Tests show that the performance is improved Up to twice 。
Finally, PostgreSQL 17 adds more explicit SIMD instructions, such as bit_count Function enables AVX-512 instruction support.
Partitioning and distributed workload enhancements
The partition management of PostgreSQL 17 is more flexible split And merge The ability to partition and allow partition tables to use Identity Column And Exclusive constraint （Exclude Constraints）。 In addition, PostgreSQL external data wrapper（ postgres_fdw ）Now you can EXISTS And IN Sub queries are pushed down to the remote server to improve performance.
PostgreSQL 17 adds new functions to logical replication, making it easier to use in high availability architectures and large version upgrades. Using from PostgreSQL 17 pg_upgrade When upgrading to a higher version, it is no longer necessary to delete logical replication slots, thus avoiding the need to resynchronize data after the upgrade.
In addition, you can also control the failover process of logical replication, providing better controllability for PostgreSQL management in a high availability architecture. PostgreSQL 17 also allows subscribers of logical replication to use hash Index, and introduced pg_createsubscriber Command line tool to create a logical copy from a library on a copy that uses a physical copy.
Developer experience
PostgreSQL 17 continues to deepen its support for SQL/JSON standards, adding JSON_TABLE Function to convert JSON into standard PostgreSQL tables and SQL/JSON constructors（ JSON 、 JSON_SCALAR 、 JSON_SERIALIZE ）And Query Functions（ JSON_EXISTS 、 JSON_QUERY 、 JSON_VALUE ）。 It is worth noting that these functions were originally planned to be released in PostgreSQL 15, but were withdrawn during beta due to design considerations - which is one of the reasons why we hope you can help test new functions during beta! In addition, PostgreSQL 17 is jsonpath The implementation of has added more functions, including the ability to convert JSON type values to various specific data types.
MERGE Commands are now supported RETURNING Clause, allowing you to further process the modified lines in the same command. You can also use the new merge_action Function View MERGE Which part of the command is modified. PostgreSQL 17 also allows you to use MERGE Command to update the view and add WHEN NOT MATCHED BY SOURCE Clause allows the user to specify what operation should be performed when there is no match between the rows in the source.
COPY The command is used to efficiently batch load and export data from PostgreSQL. In PostgreSQL 17, The performance of exporting large rows can be improved by up to twice 。 In addition, when the source code matches the target code, COPY The performance of is also improved. A new COPY ON_ERROR Option to continue even if an error occurs when inserting a row. In addition, in PostgreSQL 17, the driver can use the libpq API to use asynchronous and more secure query cancellation methods.
PostgreSQL 17 introduces a built-in collation provider that provides C The sorting rules are similar to the sorting semantics, but the code is UTF-8 Instead of SQL_ASCII 。 This new collation provides an immutability guarantee to ensure that your sorting results will not change on different systems.
Safety function
PostgreSQL 17 adds a new connection parameter sslnegotation Allows PostgreSQL to directly perform TLS handshake when using ALPN, reducing one network round trip. PostgreSQL will be registered in the ALPN directory as postgresql 。
This version introduces a new EventTrigger event, which is triggered when the user authenticates. In libpq, a new file named PQchangePassword API, which can automatically hash passwords on the client side to prevent accidental recording of plaintext passwords in the server.
PostgreSQL 17 adds a new predefined role named pg_maintain , enabling users to execute VACUUM 、 ANALYZE 、 CLUSTER 、 REFRESH MATERIALIZED VIEW 、 REINDEX And LOCK TABLE And ensure that search_path For VACUUM 、 ANALYZE 、 CLUSTER 、 REFRESH MATERIALIZED VIEW And INDEX And other maintenance operations are safe. Finally, users can now use the ALTER SYSTEM To set an undefined configuration parameter that is not recognized by the system.
Backup and export management
PostgreSQL 17 can be used pg_basebackup Incremental backup with a new utility pg_combinebackup , used to merge backups during backup recovery. This version is pg_dump A parameter item has been added --filter , allowing you to specify a file to further specify which objects to include or exclude during the dump process.
monitor
EXPLAIN The command can provide information about the query plan and execution details. Now it adds two options: SERIALIZE It will display the time consumption when serializing data into network transmission form; MEMORY The optimizer memory usage is reported. In addition, EXPLAIN You can now also display the time spent reading and writing I/O packs.
PostgreSQL 17 is standardized pg_stat_statements Medium CALL The number of records generated by frequently called stored procedures is reduced. In addition, VACUUM The progress report now shows the progress of index garbage collection. PostgreSQL 17 also introduces a new view, pg_wait_events , provide a description of the wait event, which can be connected with pg_stat_activity Use together to gain insight into the causes of waiting in active sessions.
In addition, pg_stat_bgwriter Some information in the view is now split into new pg_stat_checkpointer In the view.
Other functions
PostgreSQL 17 has many other new functions and improvements, which may be helpful to your use cases. See the release notes for a complete list of new features and changes:
https://www.postgresql.org/docs/17/release-17.html
Beta Schedule
This is the first beta version of PostgreSQL 17. The PostgreSQL project will release more beta versions according to the test needs, followed by one or more RC versions. The final version will be released in September or October 2024.
Source: https://mp.weixin.qq.com/s/3EBoAHWEI6zZ-T0nNQsk4Q
