On September 24, the development team announced the release of Postgresql version 13. This new release focuses primarily on performance improvements, faster internal maintenance services, and simplified database monitoring, as well as more reliable access control to the system.
Work has continued on optimizing table indexing in terms of handling duplicates among indexed data in binary index trees, which has not only accelerated query execution but also reduced the disk space consumed by the index.
Additionally, an incremental sorting algorithm has been introduced, whereby re-sorting already sorted data from previous steps is faster, and some queries can be accelerated through the application of new extended statistics (via the CREATE STATISTICS command) when calculating a more efficient step-by-step plan.
Query execution with costly data aggregation has also been optimized through broader use of hash aggregation and offloading some aggregated data to disk if it does not fit in memory. Significant speed gains have been noted for joining tables located on different partitions.
Considerable work has also been done in simplifying the maintenance and administration of Postgresql databases. The built-in vacuuming task, which reclaims disk space after deleting or rewriting rows, can now be performed in parallel threads, allowing the administrator to specify the number of threads. In addition, new monitoring tools for current database activity have been added, and errors during the synchronization of write-ahead logs between the master and replicas have been prevented, which could lead to conflicts when disconnecting replicas or compromise the integrity of a distributed database after their recovery based on log data.
Among the new features for developers, the datetime() function stands out, converting various standard time format representations into the built-in Postgresql type; the out-of-the-box UUID v4 generation function gen_random_uuid(); improved unicode handling; a more flexible system for distributing table data across linked database nodes with full logical-level replication; as well as other changes in queries and new triggers available for replicas.
Access control to the database is touted as one of the key components of the system, and significant progress has been made in this regard in the new version. Now, only a privileged user (superuser) can install extensions to the database. Ordinary users will only be able to install those extensions marked as trustworthy, or a small set of extensions considered trustworthy by default (such as pgcrypto, tablefunc, or hstore). When authenticating users using the SCRAM mechanism (when working through the libpq driver), a 'channel binding' is now required, and the wrapper function for external data postgres_fdw supports certificate-based authorization starting from version 13.
Source: linux.org.ru
