Number One: Vacuum speed increased by 3 times

For starters at PostgreSQL, cleanup is part of a multi-version concurrent system that removes invalid tuples or rows that are generated every time a record is changed or deleted. Autovacuum eliminates table bloat by clearing dead (deleted) hidden rows, similar to the Java garbage collector. There are two improvements related to VACUUM: parallel cleaning of indexes and the ability to perform index-only scans of appended data only.First, with parallel VACUUM, Postgres can now use multiple processor threads to perform vacuum operations faster. Our test results show that vacuum can be improved by a factor of three when working with multiple devices in parallel. Second, before this version, automatic VACUUM would only be triggered on updates and deletes, but now inserts will be triggered as well. As a result, index-only scans will occur more frequently, which is especially useful for Internet of Things (IoT) applications that continuously stream data to a database.

Number two: specifies the password to avoid man-in-the-middle attacks

Libpq is the default set of library functions used by client applications to query databases. It is important to secure these connections, otherwise there may be an opportunity to impersonate the server, also known as a man-in-the-middle attack. Passwords sent by clients can be encoded using a number of different security protocols, such as SCram-SHA-256. New enhancements enable SCRAM to use channel binding, which enables mutual authentication over SSL connections when certificates are not used. Easy-to-use security is critical, because hard-to-use features often don’t provide security at all. SCRAM with channel binding is a variant of password authentication that is easier to use but more secure.

Number three: Reduce errors in production

When the database throws an error such as “could not open file base/XXXXX/XXXX “, you realize that something is seriously wrong. In most cases, such errors occur when trying to access a table or index. Pg_catcheck is a simple tool developed by EDB to diagnose system directory corruption. If you suspect that your system directory is corrupted, this tool can help you figure out exactly what the problem is and how serious it is. Pg_catcheck will now verify that the initial file for each relationship is available (table or index). This option does not tell you why the error occurred. Nor does it offer advice on how to fix them — for that, you should consult an expert. However, this option provides a proactive tool to help detect use cases early, rather than finding bugs in production.

Number four: verifies the full backup

This is the perfect combination of improvements and new tools for creating full physical backups. Pg_basebackup — the utility for backing up running PostgreSQL clusters — now provides a backup manifest. This listing is a JSON file that contains metadata about the backup: version number, file list, WAL scope, and checksum. Pg_verifybackup is a new tool that verifies not only the integrity of the files in the backup, but also the validity of WAL logging required to restore the backup. Pg_verifybackup ensures that your backup is really good and can be restored correctly.

Number five: Easily join partition tables

Partitioning allows you to segment large datasets using ranges, lists, or hash keys to create more responsive databases. Partitioning splits a table into multiple tables and is done transparently to the client application, allowing faster access to the required data. Previously, PostgreSQL allowed you to join partitioned tables effectively only if they had matching partition boundaries. In PostgreSQL 13, partitioned joins allow you to join tables efficiently, even if their partition boundaries don’t exactly match. The benefit of this is that it is faster to join partitioned tables, which encourages the use of partitions and thus improves database responsiveness.

Number six: Logical replication of partitioned tables

One of the main features of PostgreSQL is that it allows you to selectively copy what data to the standby database through logical replication. This capability is now even more powerful because it can be used with partitioned tables. Previously, partitions had to be copied separately to the standby database. You can now explicitly publish a partitioned table so that all its partitions are published automatically. Adding/deleting partitions also adds or removes partitions from the standby database. The beauty of this feature is that it gives you a great deal of flexibility. You can now also copy from partitioned tables to non-partitioned tables, from non-partitioned tables to partitioned tables, and of course from partitioned tables to partitioned tables. Logical replication is no longer restricted by partitions.

Number seven: deduplication of the B-tree index

B-tree indexes (the default and most common type of index used in PostgreSQL) now remove duplicates by default. For example, suppose your index uses “Estado Unidos Mexicanos” in Mexico and “United States of America” in the United States. If there are thousands of records with the same country/region name, duplicate index keys are now stored only once on disk. If your workload uses many non-unique indexes (low cardinality), this can mean significant disk space savings and better performance. Early tests showed index reductions of up to 40%, and even 70% in some specific cases. Interestingly, PostgreSQL 13 has this enabled by default, so you might benefit without even knowing it! Note that if you upgrade using PG_upgrade, the previous indexes need to be re-indexed during deduplication.

Number Eight: Other security enhancements

The PostgreSQL community continues to enhance important security features in each major release. An important default value has been changed: the minimum TLS version is 1.2, which is more secure. In addition, there are many other improvements that give you more choice and flexibility in your security Settings. The external data wrapper has other benefits, such as the ability to authenticate with certificates and allow non-superusers to connect to an external server without a password. In addition, there are many other features – including the ability to unlock client certificates using passwords – that give you more options to meet your security needs. This is just a few of the things we think are worth highlighting. But PostgreSQL 13 has over 170 enhancements – some for everyone! Read the release notes for a complete list of new PostgreSQL 13 features. The PostgreSQL community continues to enhance important security features in each major release. One important default value has changed: the minimum version of TLS is 1.2, which is more secure. In addition, there are many other improvements that give you more choices and flexibility in your security Settings. Of these, Foreign Data Wrappers (FDW) have two additional conveniences, such as the ability to use certificates for authentication and allowing non-superusers to connect to an external server without a password. In addition, there are many other additional features — including the ability to unlock customer certificates using a password — that give you more options to meet your security needs. Here’s a brief description of what we think is worth highlighting, PostgreSQL 13 has over 170 enhancements — there’s something for everyone! Read the community release notes to learn about the new PostgreSQL 13 features.

Ready to upgrade? Download PostgreSQL 13 now

For more exciting content, please pay attention to the following platforms and websites:

PostgreSQL Branch official official number (technical articles, technical activities) : open source software Alliance PostgreSQL branch

China Postgre SQL Branch technical q&A community: www.pgfans.cn

Official website of China Postgre SQL Branch: www.postgresqlchina.com