kentoh - Fotolia

PostgreSQL 13 advances popular open source database

The open source database project has its first major milestone release of 2020, bringing users accelerated data query performance and optimizations to improve overall operations.

The open source PostgreSQL database project is continuing to improve performance in its latest release as well provide users with enhanced monitoring capabilities.

The PostgreSQL 13 release became generally available on Sept. 24 and is the first major update of the popular open source database since the PostgreSQL 12 release that debuted in Oct. 2019. With PostgreSQL 13, a series of administrative optimizations help database administrators to improve operations.

The new release also includes features that provide performance gains over previous releases.

PostgreSQL itself is growing in popularity and many third-party database management vendors, including public cloud providers, offer PostgreSQL-compatible interfaces.

Hybrid and public cloud adoption by larger enterprises should drive even more for PostgreSQL adoption, said Carl Olofson, research vice president at IDC.

"PostgreSQL 13 improvements seem to be mainly about efficiency, which can be huge because resource waste is a key source of poor database performance," he said. "The wide range of improvements points to the benefit of developing technology in an open source manner."

PostgreSQL 13 improvements seem to be mainly about efficiency, which can be huge because resource waste is a key source of poor database performance.
Carl OlofsonResearch vice-president, IDC

Series of performance boosts

Among the performance improvements in PostgreSQL 13 is work on B-tree index deduplication which will make the indexes smaller and more performant, said Jonathan Katz, a major contributor to the PostgreSQL Project.

Katz noted that PostgreSQL 13 also provides a performance boost to partitioned tables, which will make data queries faster. He added that for people who write a lot of analytical queries that use aggregate functions, PostgreSQL 13 accelerates operations.

Parallelized vacuuming optimizes PostgreSQL 13 databases

The concept of data "vacuuming" has long been core to PostgreSQL, as well as other databases, providing a way for the database to suck up or vacuum space after a row is deleted. With PostgreSQL 13, the vacuuming process is accelerated with a new approach for database indexes.

Indexes can now be vacuumed in parallel, which was not an option in previous releases, Katz noted, adding that if a table has multiple indexes, the indexes can be vacuumed concurrently instead of one at a time.

PostgreSQL data vacuuming functions.
PostgreSQL 13 expands on the data vacuuming capabilities of the open source database with a new parallelized vacuum command that enables administrators to optimize multiple indexes at the same time.

Better database monitoring in PostgreSQL 13

PostgreSQL has long included code hooks that enable developers and administrators to monitor various aspects of a database's activity.

In the PostgreSQL 13 update, Katz noted that those monitoring capabilities have been expanded with a series of new ways to gain visibility into database operations.

Among those new capabilities is that the EXPLAIN monitoring query can now track write-ahead logs. Write-ahead logging (WAL) is part of PostgreSQL's data integrity approach, providing a log of changed data. The EXPLAIN query can now be used to track WAL usage statistics so an administrator can understand how much WAL data is generated from a command.

The ability to track the progress of the ANALYZE command has also been improved, which can help an administrator to understand how long it will take to collect statistics about a given process.

Tracking progress for backups is also part of the update with the ability for administrators to track the progress of pg_basebackup, which takes a base backup of a PostgreSQL cluster.

Dig Deeper on Database management

Business Analytics
Content Management