PostgreSQL 13 release

After a year of development published new stable branch of DBMS PostgreSQL 13. Updates for the new branch will go out for five years until November 2025.

All innovations:

• Implemented deduplication records in B-tree indexes, which allowed to improve query performance and reduce disk space consumption when indexing records with duplicate data. Deduplication is performed by periodically launching a handler that merges groups of repeating tuples and replaces duplicates with links to a single stored copy.
• Improved performance of queries that use aggregate functions, grouped sets (GROUPING SETS) or sectioned (partitioned) tables. The optimizations are related to using hashes instead of actual data when aggregating, which avoids putting all the data in memory when processing large queries. Partitioning expands the number of situations in which partitions can be dropped or merged.
• Added the ability to use extended statistics, created when using the "CREATE STATISTICS" command, to improve the efficiency of planning queries that contain "OR" conditions or searches in lists using "IN" or "ANY" expressions.
• Speed ​​up cleaning of indexes when performing an operation VACUUM by parallelizing garbage collection in indexes. With the new "PARALLEL" parameter, the administrator can define the number of threads that will run concurrently for VACUUM. Added the ability to initiate automatic execution of VACUUM after inserting data.
• Added support for incremental sorting, which allows you to use the data sorted in the previous stage to speed up sorting in subsequent stages of query processing. To enable the new optimization in the query planner, there is a setting "enable_incremental_sort", which is enabled by default.
• Added the ability to limit the size replication slots, allowing you to automatically guarantee the preservation of the lazy log (WAL) segments until they are received by all backup servers that accept replicas. Replication slots also prevent the primary server from deleting rows that could lead to conflicts, even if the secondary server is down. Using the parameter max_slot_wal_keep_size you can now limit the maximum size of WAL files to prevent running out of disk space.
• Possibilities for monitoring DBMS activity have been expanded: the EXPLAIN command provides additional statistics on the use of the WAL log; V pg_basebackup the ability to track the status of continuous backups; the ANALYZE command has an indication of the progress of the operation.
• Added new command pg_verifybackup to check the integrity of backups created by the pg_basebackup command.
• When working with JSON using operators jsonpath allowed to use the datetime() function to convert time formats (ISO 8601 strings and native PostgreSQL time types). For example, you can use the constructions "jsonb_path_query('["2015-8-1", "2015-08-12"]', '$[*] ? (@.datetime() < "2015-08-2".datetime ())')" and "jsonb_path_query_array('["12:30", "18:40"]', '$[*].datetime("HH24:MI")')".
• Added built-in function gen_random_uuid () to generate v4 UUIDs.
• The partitioning system has full support for logical replication and BEFORE-specified
  row-level triggers.

• Syntax "FETCH FIRST' now accepts 'WITH TIES' to return additional rows that are at the tail of the result set after applying 'ORDER BY'.
• Implemented the concept of trustworthy add-ons ("trusted extension"), which can be installed by ordinary users who do not have DBMS administrator rights. The list of such additions is initially predefined and can be extended by the superuser. Trustworthy add-ons include pgcrypto, tablefunc, hstore etc.
• Support for certificate-based authentication has been implemented in the Foreign Data Wrapper (postgres_fdw) external table connection mechanism. When using SCRAM authentication, clients are allowed to request "channel binding"(channel binding).

Source: opennet.ru