After a year of development, a new stable branch of the PostgreSQL database management system 17 has been released. Updates for this new branch will be provided for five years until November 2029. Support for PostgreSQL 12.x, the oldest supported branch, will end on November 14.
Key innovations:
- When performing the VACUUM operation (garbage collection, cleaning, and packing of disk storage), a new data structure is used that consumes up to 20 times less memory. The transition to this new structure has accelerated the execution of VACUUM operations and reduced the consumption of shared resources.
- Input/output performance optimization has continued. Improvements in the code for handling the Write-Ahead Logging (WAL) have doubled the write operation throughput in systems with a high number of concurrently executing queries. A new streaming I/O interface has been proposed, allowing for faster sequential data scanning in tables and speeding up statistics updates for the query planner after running the ANALYZE operation.
- Query performance has been improved for expressions using "IN" when B-tree based indexes are used. The ability to build BRIN (Block Range Index) indexes in parallel has been implemented. In the query planner, checks for "NOT NULL" have been optimized, and the processing of Common Table Expressions (CTE) defined by the SQL command "WITH" has been accelerated. Vector processor instructions (SIMD) have been leveraged to speed up certain calculations, such as utilizing AVX-512 instructions for the bit_count function.
- Support for new features for working with JSON format, defined in the SQL/JSON standard, has been implemented. A JSON-TABLE function has been added to convert JSON data into standard PostgreSQL tables, along with new jsonpath operators for transforming JSON data into native PostgreSQL data types. New functions for creating (JSON, JSON_SCALAR, and JSON_SERIALIZE) and querying (JSON_EXISTS, JSON_QUERY, and JSON_VALUE) JSON data have been proposed.
- The SQL command "MERGE" has expanded capabilities, allowing for the creation of conditional SQL expressions that combine INSERT, UPDATE, and DELETE operations in one statement. Support for the RETURNING clause has been added, enabling the computation and return of values based on added, updated, or deleted rows. The support for updating views has been implemented.
- The data loading and export capabilities in batch mode have been enhanced. The COPY command's export speed for large rows has been increased by up to two times, copying has been optimized when the source and target encodings match, and the ON_ERROR option has been added, allowing the import to continue after errors occur during data insertion.
- For partitioned tables, the ability to use columns with unique identifiers and support for the query optimization technique "Constraint exclusion" have been added.
- In the Foreign Data Wrapper (postgres_fdw) mechanism for connecting to external tables, the ability to pass on to external server subqueries "EXISTS" and "IN" has been implemented.
- An embedded platform-independent engine for processing collation properties used to define sorting rules and character-sensitive comparison methods has been implemented. For example, when sorting numeric values, it takes into account the presence of a minus sign and decimal point before the number and different writing styles, while case sensitivity and diacritical marks are ignored during comparison. This new engine ensures consistent behavior in sorting query results, regardless of the platform on which PostgreSQL runs.
- In the logical replication mechanism, which allows changes made to the database during the adding, deleting, or updating of records to be streamed to another server, the process of database upgrade has been simplified. Previously, transitioning to a new major release required the deletion of logical replication slots and resynchronization of data, but now deleting slots is no longer necessary. For fault-tolerant configurations, the ability to manage failover to a standby server for logical replication has been implemented. A new utility, pg_createsubscriber, has been added to convert a physical replica into a new logical replica.
- A new option "sslnegotiation" has been added for direct negotiation of secure TLS connections using the TLS extension ALPN.
- A predefined role pg_maintain has been implemented, granting the user permission to perform database maintenance operations.
- Support for incremental backups has been added to the pg_basebackup utility, used for creating database backups. A new utility, pg_combinebackup, has been proposed for reconstructing a full backup from a set of incremental copies. The pg_dump utility has added the "—filter" option to select objects included in the dump.
- When analyzing queries using the SQL command "EXPLAIN," the output now includes the time spent reading and writing local blocks during I/O operations. Two new options, "MEMORY" and "SERIALIZE," have also been added to the "EXPLAIN" command to determine the memory size used and the time spent converting data for network transmission.
- Progress indication for the VACUUM operation with indexes has been provided.
- A system view pg_wait_events has been added, which, in conjunction with the pg_stat_activity view, allows for a more detailed examination of why active sessions are in a waiting state.
Source: opennet.ru
