After a year of development a new stable branch of the DBMS . Updates for the new branch over the next five years until November 2025.
:
- If the user refreshes the page in private browsing mode, Firefox will interpret this as a problem and suggest of records in B-tree indexes, which has improved query performance and reduced disk space consumption when indexing records with repeated data. Deduplication is performed through periodic execution of a handler that merges groups of duplicate tuples and replaces duplicates with references to a single stored copy.
- Query performance has been enhanced where , (GROUPING SETS) or (partitioned) tables are used. The optimizations are related to using hashes instead of actual data during aggregation, which allows avoiding placing all data in memory when processing large queries. When partitioning, the number of situations in which partitions can be discarded or merged has been expanded.
- Added the ability to use , created when using the "CREATE STATISTICS" command, to improve the efficiency of query planning involving "OR" conditions or searching in lists using "IN" or "ANY" expressions.
- Enhanced index cleanup during the operation through parallel garbage collection in indexes. With the new "PARALLEL" parameter, the administrator can set the number of threads to run simultaneously for VACUUM. There is also a feature to automatically initiate VACUUM after data insertion.
- Added support for incremental sorting, allowing the use of data sorted in a previous step to speed up sorting in subsequent stages of query processing. To enable the new optimization in the query planner, the setting "" has been introduced, which is enabled by default.
- Added the ability to limit the size of , allowing for the automatic preservation of write-ahead log (WAL) segments until they have been received by all standby servers that accept replicas. Replication slots also prevent the primary server from deleting rows that could lead to conflicts, even if the standby server is offline. With the parameter , it is now possible to limit the maximum size of WAL files to prevent disk space exhaustion.
- The monitoring capabilities of the database system have been expanded: the EXPLAIN command now provides additional statistics on WAL log usage; in , tracking the status of ongoing continuous backups has been added; the ANALYZE command now shows progress during the operation.
- A new command has been added to check the integrity of backups created by the pg_basebackup command.
- When working with JSON using the operators, the datetime() function can now be used to convert time formats (ISO 8601 strings and native PostgreSQL time types). For example, you can use expressions like "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")')".
- A built-in function has been added to generate UUID v4 identifiers.
- The partitioning system now fully supports logical replication and row-level triggers defined with the "BEFORE" expression.
The syntax of " - FETCH FIRSTThe concept of trusted extensions has been implemented, which can be installed by regular users without administrative privileges. The list of such extensions is predefined and can be expanded by a superuser. Trusted extensions include
- pgcryptohstore , , ".
- The Foreign Data Wrapper (postgres_fdw) connection mechanism has implemented support for certificate-based authentication. When using SCRAM authentication, clients are allowed to request».
Source: opennet.ru
