Select Page

A PostgreSQL 13 Feature Deep-dive

Author: Shailesh Rangani | | May 4, 2021


 

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.

Read This Next

You Can’t Put Off a PostgreSQL v9.5 Upgrade Anymore – End of Life is Here

PostgreSQL 13 has an exciting range of new features and performance improvements that make it an excellent choice for an upgrade. Learn more about moving to PostgreSQL 13 and the upgrade process in our white paper.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

Using Nulls in DB2

If a column “value” can be null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known.

Craig Mullins | April 6, 2015

Scripting Out SQL Server Logins, Server Role Assignments, and Server Permissions

This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.

JP Chen | October 1, 2015

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS