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.<\/p>\n
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. \nAdditionally, 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. \nQuery 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.<\/p>\n
Significant work has been done in the area of simplifying the maintenance and administration of PostgreSQL databases. The built-in \"vacuuming\" task, which uses freed disk space after deleting or rewriting rows, can now be executed in parallel threads, allowing the administrator to specify their number. In addition, new monitoring tools for current database activity have been added, along with measures to prevent errors during the synchronization of write-ahead logs between the master and replicas, which could lead to conflicts when disconnecting replicas or compromise the integrity of the distributed database after it is restored based on log data.<\/p>\n
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.<\/p>\n
Access control to the database is proclaimed as one of the key components of the system, and significant progress has been made in this area in the new version. Now, only a privileged user (superuser) can install extensions to the database. Meanwhile, regular users will only be able to install those extensions that have been marked as trusted, or a small set of extensions considered trustworthy by default (for example, 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 from version 13.<\/p>\n