This article was published by PostgreSQL Global Development Group on September 24, 2020 with the assistance of the translation volunteer group of China open source software promotion alliance PostgreSQL branch.
PostgreSQL global development team today announced the official release of PostgreSQL 13, the latest version of the world’s most advanced open source database.
PostgreSQL 13 has made significant improvements to the index and lookup system, which benefits large databases, including saving space and improving index performance, using aggregation or partitioning to make query response time faster, and better query plan when using enhanced statistics, etc.
In addition to highly demanding functions (such as parallel cleaning and incremental sorting), PostgreSQL 13 also provides a better data management experience for large and small workload, optimizes daily management, provides more convenience for application developers, and enhances security.
“PostgreSQL core team member Peter eisentraut said:” PostgreSQL 13 demonstrates the collaboration and dedication of our global community in enhancing the capabilities of the world’s most advanced open source relational database. “The innovation of each distribution and its reputation for reliability and stability are the reasons why more and more people choose to use PostgreSQL in their applications.”
PostgreSQL is an innovative data management system, known for its reliability and robustness. Thanks to more than 25 years of open source development by the global developer community, PostgreSQL has become the preferred open source relational database for organizations of all sizes.
Based on the previous PostgreSQL versions, PostgreSQL 13 can effectively handle duplicate data in the standard database index b-tree index. This reduces the overall space usage of B-tree indexes and improves the overall query performance.
PostgreSQL 13 introduces incremental sorting, in which sorted data from earlier steps in the query can speed up the sorting of subsequent steps. In addition, PostgreSQL can now use an extended statistical system (accessible through create Statistics) to create improved plans for queries with or clauses and in / any lookup lists.
In PostgreSQL 13, more types of aggregate queries and group queries can take advantage of PostgreSQL’s efficient hash aggregation function, because queries with large aggregations do not have to be fully contained in memory. The performance of queries with partitioned tables has been improved because there are more cases where partitions can be pruned and joined directly.
Vacuuming is an important part of PostgreSQL management, which enables the database to reclaim storage space after updating and deleting rows. Although previous versions of PostgreSQL have done the job of reducing the cleanup overhead, this process can also pose administrative challenges.
PostgreSQL 13 continues to improve the vacuuming system by introducing parallelizedvacuum for indexing. In addition to the clean-up performance benefits it provides, since administrators can choose the number of parallel workers to run, the use of this new feature can be adjusted for specific workloads. In addition to these performance advantages, data insertion can now trigger the autovacuum process.
The replica slot, which prevents the replica from deleting the wal pre written log before it receives it, can be adjusted in PostgreSQL 13 to specify the maximum number of wal files to keep and helps to avoid out of disk space errors.
PostgreSQL 13 also adds more ways that administrators can monitor database activity, including the progress of explain, streaming basic backup, and analyze command from the reference wal usage statistics. In addition, new PG can be used_ Verify backup command to check PG_ Integrity of basebackup output.
Facilitate application development
PostgreSQL 13 makes it easier to use PostgreSQL data types from different data sources. This release adds the datetime() feature to its SQL / JSON path support, which converts valid time formats (such as ISO 8601 strings) to PostgreSQL local types. In addition, UUID V4 generates the function Gen_ random_ The uuid() function is now available without installing any extensions.
PostgreSQL’s partitioning system is more flexible because partition tables fully support logical replication and before row level triggers.
The fetch first syntax in PostgreSQL 13 has been extended to include the with ties clause. When specified, with ties includes the “tie” based on the order by clause and any row of the last row in the result set.
PostgreSQL’s extended system is a key component of its robustness, because it allows developers to extend its functionality. In previous versions, new extensions could only be installed by the database superuser. To make it easier to take advantage of PostgreSQL’s extensibility, PostgreSQL 13 adds the concept of “trusted extension”, which allows database users to install extensions marked as “trusted” by super users. Some built-in extension tags are trusted by default, including pgcrypto, tablefunc, hsstore, etc.
For applications that require a secure authentication method, PostgreSQL 13 allows clients to require channel binding when using scram authentication, and PostgreSQL external data wrappers (Postgres)_ FDW) can now use certificate based authentication.
PostgreSQL is the most advanced open source database in the world, with thousands of users, contributors, companies and organizations around the world. PostgreSQL has more than 30 years of engineering experience from the University of California, Berkeley, and continues to grow at an unparalleled rate. The mature features of PostgreSQL not only match the top-level proprietary database systems, but also surpass them in advanced database functions, scalability, security and stability.
Download PostgreSQL 13.0
PG Asia Technology Summit: based in China, focusing on Asia and integrating the world| PostgresConf.CN & PGConf.Asia 2020 conference is coming!
For more wonderful content, please pay attention to the following platforms and websites:
Official account of China Postgre SQL chapter (technical articles and technical activities):
PostgreSQL branch of Open Source Software Alliance
China postgre SQL branch technical Q & a community:
Official website of China postgre SQL branch: