A PostgreSQL 13 Feature Deep-dive

PostgreSQL is an open-source relational database solution known for delivering powerful functionality and continually improving. The latest version, 13, continues to build on this foundation. Here are the newest additions and improvements to PostgreSQL.

Improved Indexing and Lookup Systems

If you’re working with largest databases, you’ll find that PostgreSQL 13 offers a much better experience with indexing and lookups. Some ways that this version has improved these features for big databases includes more efficient space usage, better performing indexes, and a stronger query planning functionality. You’ll also notice that queries that have aggregates or partitions involved are faster.

Newly Added Parallelized Vacuuming

The vacuuming feature in PostgreSQL, which reclaims storage space whenever you update and delete rows, has a much-requested upgrade. The latest version offers parallelized vacuuming for your database indexes. The performance for these operations is greatly improved due to this change.

You’re able to set the number of parallel workers on a workload-by-workload basis, so you can focus your resources on the indexes that need vacuumed the most. Another change to this feature is that you can now use autovacuum with data inserts.

Newly Added Incremental Sorting

Another popular user requested feature that made it into PostgreSQL 13 is incremental sorting. This function allows you to speed up your query sorting when you work with data that has been sorted earlier on. These query optimizations can make a significant impact on your database’s performance.

Another query-related improvement is the ability to use extended statistics for your query plans. This feature covers queries constructed with OR and IN/ANY.

Handle Your Duplicate Data in B-Tree Indexes

B-tree indexes in PostgreSQL received a boost through better handling of duplicate data. When you’re working with large amounts of duplication in your system, you can run into a range of performance issues. This feature decreases the space that your database indexes use and also provide a noticeable improvement in query speeds.

Expanded PostgreSQL Hash Aggregation Capabilities

PostgreSQL has an excellent hash aggregation function, but previous versions were limited in the query types that could use it. With PostgreSQL 13, you have an expanded number of grouping set and aggregate queries that benefit from this feature.

Large aggregate queries are freed from being fully in-memory with this change. Another performance boost is found in partitioned table queries. PostgreSQL 13 accomplishes this by directly joining the partitions.

Greater Visibility into PostgreSQL Activity

Databases have a lot of moving parts, and being able to effectively manage them requires as much visibility into system activity is possible. When you have greater access to this information, your organization can make better decisions about how to keep the database safe, ways to optimize performance, and methods for allocating the right number of resources.

PostgreSQL 13 delivers more monitoring capabilities. You can use EXPLAIN to look up WAL usage, keep an eye on streaming base backups, and learn more about the progress of ANALYZE operations. This version also adds a new backup feature called pg_verifybackup. As the name implies, you’re able to check the data integrity of your PostgreSQL backups.

Trusted Extensions Reduce Administrative Overhead

How much time do your database administrators spend on installing PostgreSQL extensions? They probably have a set list of extensions that they know and trust to be used on your business network, and installing it is a routine procedure that takes them away from more pressing matters.

In previous versions of PostgreSQL, only superusers could add new extensions. This limitation changed in 13, as you’re now able to designate trusted extensions. Your database superusers can set up the most commonly used extensions as trusted, allowing users to add those in on their own. A number of the built-in PostgreSQL extensions have this turned on by default, including hstore, pgcrypto, and tablefunc.