PostgreSQL 17 release

PostgreSQL 17 was released in September 2024, and updates to the current version will be delivered until November 2029. The main additions and changes to the project are as follows:

• New features for working with JSON have been added. If we talk directly about functionality, there is a new jsonpath operator so that you can convert data from native format to PostgreSQL, in addition, JSON-TABLE function became available, which is necessary for converting data to PostgreSQL. New functions for querying and creating JSON format data have also been introduced;
• The approach in the data structure of the VACUUM operation has been changed. Now during the execution of the operation less resources will be used and thus the execution speed will be increased;
• If a B-tree-based index is used in a query with IN in such a variant, the efficiency will be significantly improved. Parallel construction will be available in BRIN indexes. CTE processing when using WITH command is slightly accelerated, and NOT NULL queries are also optimized. SIMD will be used to speed up certain calculations;
• The MERGE command has been slightly modified to create conditional expressions. RETURNING support is now available, the expression outputs a value based on changes or deletions made;
• Improved throughput in systems with a large number of simultaneous requests. The WAL log code has been modified to achieve optimization. In the ANALYZE operation, the speed of data processing in tables to update statistical data has been increased;
• In the batch mode, some changes were made regarding data loading/exporting. Also in COPY command, it became possible to increase the speed of export, copying was optimized a bit and ON_ERROR command was added - it ensures the continuation of import even if there was an error in loading some data;
• DBMS update has been simplified; the logical replication mechanism has been considerably simplified in case of any changes in the database. Previously, the replication process included deleting the replication slots, but now this stage has been canceled. Now you just need to switch to a failover server for logical replication. Also, the pg_createsubscriber utility, which is needed for conversion to logical replication, has been released;
• Support for Constraint exclusion optimization has been added to sectioned tables and it is now possible to set unique identifiers for columns;
• EXPLAIN command added information about memory and time consumption during input and output. With the help of SERIALIZE and MEMORY options, it is possible to retrieve exact information about the time and memory used to change data for further transfer;
• It is now possible to transfer IN/EXISTS queries in external postgres_fdw tables to an external server;
• Added support for incremental backups in the pg_basebackup utility. In order to restore a backup from multiple copies you can now apply pg_combinebackup. And with the filter option in pg_dump it is now possible to select necessary objects;
• The pg_maintain role has been added, giving the right to manage and maintain the DBMS;
• It is now possible to use pg_wait_events together with pg_stat_activity to detect the reason for data being in the waiting state.