On September 24, the development team announced the release of the next Postgresql release number 13. The new release focused, among other things, on improving performance, speeding up internal maintenance services and simplifying database monitoring, as well as more reliable system access control.
Work continued on optimizing table indexing in terms of handling duplicates among indexed data in binary index trees, which made it possible not only to speed up query execution, but also to reduce the disk space occupied by the index.
In addition, an incremental sorting algorithm has been added, in which re-sorting of data already sorted in previous steps is faster, and for some queries, acceleration can be achieved by using new extended statistics (via the CREATE STATISTICS command) when calculating a more efficient step-by-step plan.
Execution of queries with expensive data aggregation has also been optimized by using hashed aggregation more widely and flushing some of the aggregated data to disk if it does not fit in RAM. There is a significant increase in the speed of joining tables located on different partitions.
A lot of work has also been done in the field of simplifying the maintenance and administration of Postgresql databases. The built-in "vacuuming" task, that is, using the freed disk space after deleting or overwriting rows, can now be executed in parallel threads, while the administrator has the opportunity to specify their number. In addition, new tools have been added to monitor the current activity of the database and errors have been prevented when synchronizing pre-record logs between the master and replicas, which could lead to conflicts when replicas are disabled or violate the integrity of the distributed database after they are restored based on log data.
Among the innovations for developers, it is worth highlighting the datetime () function, which converts various standard time formats into a built-in Postgresql type; the out-of-the-box UUID v4 generation function gen_random_uuid(); normalization of work with unicode; a more flexible system for distributing table data on connected base network nodes with full-fledged replication at the logical level, as well as other changes in queries and new triggers available for replicas.
Access control to the database is announced as one of the key components of the system, and the new version has made great strides forward in this regard. Now only a privileged user (superuser) can install extensions to the database. At the same time, ordinary users themselves will be able to install only those extensions that they mark as trusted, or a small set of extensions that are considered trusted by default (for example, pgcrypto, tablefunc or hstore). When authenticating users using the SCRAM mechanism (when working through the libpq driver), βchannel bindingβ is now required, and the wrapper function for third-party data postgres_fdw from version 13 supports authorization by certificate.
Source: linux.org.ru