PostgreSQL 13 is here, and as always EDB is proud to be among the many global contributors who made this happen. This year there’s not one headline-grabbing feature, but rather a wide variety of improvements along with updates to previously released features. You’ll find changes that include performance, usability, and security improvements, among others.
The continuous improvement of important but lesser-known features is a real testament to the power and productivity of an open source project. Let’s go through some key highlights and give you reasons to upgrade today.
Vacuum up to 3X faster
For those that are new to PostgreSQL, vacuuming is part of the multiversion concurrency system and removes dead tuples or rows that are created whenever a record is changed or deleted. The autovacuum feature cleans up dead hidden rows so you don’t have bloat and is similar to Java’s garbage collector. There are two vacuum-related improvements: parallel vacuuming of indexes and allowing for index-only scans of append only data.
Test results with parallel vacuum
First, with parallel vacuum, Postgres can now use multiple processor threads to perform vacuum operations faster. Our test results indicate that vacuum can be up to 3 times faster when using multiple parallel workers. Second, prior to this release, autovacuum would only trigger vacuum operations on updates and deletes but is now enabled for inserts. As a result, index-only scans will happen more frequently which is especially useful for IoT applications which continuously stream writes to the database.
Avoid man-in-the-middle attacks for passwords
Libpq is the default set of library functions that client applications use to query the database. It’s important for these connections to be secure, otherwise there could be an opportunity to impersonate the server, also called a man-in-middle attack. Many different security protocols can be used to encode the password sent by the client, e.g., SCRAM-SHA-256. The new enhancement enables SCRAM to use channel binding, which is a feature that allows mutual authentication over an SSL connection when you are not using certificates.
Making security easy to use is crucial because hard-to-use features often lead to no security at all. SCRAM with channel binding is a variation of password authentication that is easy to use, but much more secure.
Reduce errors in production
When your database throws an error like "could not open file base/XXXXX/XXXX”, you know that something is seriously wrong. Most of the time, such errors occur while trying to access a table or index. Pg_catcheck is a simple tool developed by EDB for diagnosing system catalog corruption. If you suspect that your system catalogs are corrupted, this tool may help you figure out exactly what problems you have and how serious they are. Pg_catcheck will now verify that the initial files are available for every relation (table or index).
This option won't tell you the reason why you are getting such errors. It also won't provide advice on how to fix them — for this, you should consult an expert. However, this option provides a proactive tool to help in detecting cases early rather than hitting errors in production.
Verify full backups
There’s a nice combination of an improvement and a new tool for creating full physical backups. Pg_basebackup – the tool for taking a backup of a running PostgreSQL cluster – now provides a backup manifest. This manifest is a JSON file containing metadata about the backup: a version number, file list, WAL range, and a checksum.
Pg_verifybackup is a new tool that validates not only the integrity of the files in the backup, but also that the write-ahead log records needed to recover the backup are valid. Pg_verifybackup assures you that your backups are indeed good and can restore properly.
Join partitioned tables easily
Partitioning allows you to create a more responsive database by segmenting large data sets using range, list or hash keys. Partitioning splits a table into multiple tables, and done in a way that is transparent to client applications, making it faster to access the needed data. Previously PostgreSQL allowed you to efficiently join partitioned tables only if they had matching partitioned boundaries. In PostgreSQL 13, partition-wise joins allow you to efficiently join tables even when their partition bounds do not match exactly. The benefit is that joining partitioned tables is faster, which encourages the use of partitioning and therefore a more responsive database.
Logical replication for partitioned tables
One of the major features of PostgreSQL is that it gives you the option to selectively replicate which data to copy to a standby through logical replication. This feature is now even more powerful because it can be used with partitioned tables. Previously, partitions had to be replicated individually to the standbys. Now a partitioned table can be published explicitly, causing all its partitions to be published automatically. Addition/removal of a partition causes it to be likewise added to or removed from the standbys.
The benefit of this feature is that it gives you a tremendous amount of flexibility. You can also now replicate from a partitioned table to a non-partitioned table, from a non-partitioned table to a partitioned table and of course from partitioned table to partitioned table. Logical replication is no longer constrained because of partitioning.
De-duplication of B-tree indexes
B-tree indexes – the default and most common indexing type used in PostgreSQL – are now deduplicated by default. For example, suppose you have an index that is using ‘Estado Unidos Mexicanos’ for Mexico and ‘United States of America’ for USA. If there are thousands of records with the same country name, duplicate index keys are now stored only once on disk. If your workloads use many non-unique indexes (low cardinality), this can mean big savings on disk space and better performance. Early tests have shown 40% smaller indexes, or even 70% smaller in some specific cases.
What’s interesting here is that this is enabled by default in PostgreSQL 13, so you may benefit without even knowing it! Note that if you upgrade using pg_upgrade, you will need to reindex if you want the deduplication of your previous indexes.
Other security enhancements
The PostgreSQL community continues to enhance important security features with each major release. One significant default has changed: the minimum TLS version is 1.2, which is more secure. Outside of that, there are a number of other improvements giving you more choice and flexibility over security settings. Among these, Foreign Data Wrappers have a couple of added conveniences like the ability to use certificates for authentication, and allowing non-superusers to connect to foreign servers without a password. And there are plenty of other additions – including the ability to use passwords to unlock client certificates – giving you more options to fit your security needs.
This is a short rendition of what we think is worthy of highlighting; however, PostgreSQL 13 has over 170 enhancements - there is something for everyone! Read the release notes for the comprehensive list of what’s new in PostgreSQL 13.
Ready to upgrade? Download PostgreSQL 13 from EDB today.