After almost a year of development, the release of the new stable branch of PostgreSQL 13, in which a higher performance stands out, as well as the inclusion of new features.
For those who are still unfamiliar with PostgreSQL, you should know that it is also known as Postgres and this is a relational database management system (RDBMS) free, open source, which aims to offer a database based on extensibility and compliance with technical standards.
It is designed to handle a variety of workloads, from simple machines to data warehouses or web services with many concurrent users.
What's new in PostgreSQL 13?
In this new version it is highlighted that record deduplication was implemented in B-tree indexes, enabling increased query performance and reduced disk space consumption when indexing records with duplicate data.
Deduplication done by periodically launching a driver which merges groups of duplicate tuples and replaces duplicates with references to a single stored copy.
Also, also better performance of queries using aggregate functions is highlighted, grouped sets (GROUPING SETS) or partitioned tables (partitsirovannye).
Optimizations are related to the use of hashes in addition instead of the actual data, which avoids putting all the data in memory when processing large queries. Partitioning has expanded the number of situations in which partitions can be deleted or merged.
Also added the ability to use advanced statistics generated by the command «CREATE STATISTICS»To improve the efficiency of query scheduling containing "OR" clauses or list searches that use "IN" or "ANY" clauses.
Index cleaning during operation VACUUM speeds up due to garbage collection parallelization in the indices. With the new parameter «PARALLEL»The administrator can define the number of threads that will be started simultaneously for the VACUUM.
Added support for incremental sorting, which allows you to use the data sorted in the previous stage to speed up sorting in the later stages of request processing.
To enable a new optimization, the query planner provides the settings » enable_incremental_sort ', Which is enabled by default.
Added the ability to limit the size of replication slots, allowing you to automatically guarantee the storage of write-behind log (WAL) segments until they are received by all backup servers that receive replicas. The replication slots also prevent the primary server from deleting rows, which could lead to conflicts even if the secondary server is offline.
De the other changes that stand out of this new version:
- Parameter max_slot_wal_keep_size can now be used to limit the maximum size of WAL files to avoid running out of disk space.
- The possibilities of monitoring the DBMS activity are extended: in the command EXPLAIN, the display of additional statistics on the use of the WAL-log is provided.
- A new command has been added pg_verifybackup to verify the integrity of the backups created by the command pg_basebackup.
- When working with JSON using operators jsonpath, it is allowed to use the function datetime () to convert time formats (ISO 8601 strings and native PostgreSQL time types).
- Built-in function added gen_random_uuid () to generate UUID v4.
The partitioning system provides full support for logical replication and BEFORE row-level triggers. - The concept of trusted extensions has been implemented, which can be installed by ordinary users who do not have DBMS administrator rights.
- The list of these plugins is initially predefined and can be expanded by the super user. Trusted Plugins Include pgcrypto, tablefunc, hstore, etc.
- The external table join mechanism of the external data container (postgres_fdw) supports certificate-based authentication.
Finally, it is mentioned that the updates for the new branch will be published for five years until November 2025.
Source: https://www.postgresql.org/