The open source database is becoming more efficient

The open source database project PostgreSQL continues to improve the performance of its latest version and provide users with advanced monitoring capabilities.

Continuation of the article below

PostgreSQL version 13 became generally available on September 24th and is the first major update to the popular open source database since PostgreSQL version 12, which was released in October 2019. A number of optimizations should be made with PostgreSQL 13 to help DBMS administrators improve operations.

The new version also includes features that enable performance improvements over previous versions.

PostgreSQL itself is growing in popularity, and many third-party providers, including public cloud providers, offer interfaces that are compatible with PostgreSQL.

“The adoption of the public and hybrid cloud by large companies is expected to drive the adoption of PostgreSQL further,” said Carl Olofson, IDC vice president of research.

“The improvements in PostgreSQL 13 seem to be primarily due to efficiency. This can be important as wasted resources often affect database performance, ”he says. “The wide range of improvements shows the advantage of open development of the technology.”

A number of performance improvements

One of the performance improvements in PostgreSQL 13 is work on B-tree deduplication, which makes indexes smaller and more efficient, says Jonathan Katz, who was instrumental in the PostgreSQL project. Search speed would then be improved, especially in indexes that contain repeated values.

It also provides a performance improvement for partitioned tables, which should speed up data queries. He adds that PostgreSQL 13 will run faster for users who write a lot of analytical queries and use aggregate functions.

The query planning must also be optimized taking into account extended statistics.

The concept of “sucking up” data has long been at the heart of PostgreSQL and other databases and offers the DBMS the ability to suck up space after a row has been deleted. With PostgreSQL 13, the suction process is accelerated through a new approach to indexes.

“Indexes can now be vacuumed in parallel, which was not an option in previous versions,” says Jonathan Katz, adding that if a table contains multiple indexes, indexes can be vacuumed simultaneously rather than individually. .

Better monitoring in PostgreSQL 13

PostgreSQL has a long history of including code hooks that developers and administrators can use to monitor various aspects of database activity.

In PostgreSQL Update 13, Jonathan Katz reports that a number of new options have been added to these monitoring functions to provide insight into database operations.

Among these new features, the EXPLAIN monitor query can now track write logs. Write Ahead Logs (WAL) are part of the PostgreSQL approach to data integrity and provide 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 one command.

The ability to track usage of the ANALYZE command has also been improved. This enables an administrator to better understand how long it takes to collect statistics on a particular process.

Tracking the backup progress is also part of the update. Administrators can track the progress of pg_basebackup, a base backup of a PostgreSQL cluster.

Those who are primarily responsible continue to point out that various changes may introduce incompatibilities with previous versions of PostgreSQL, including versions 8.0, 7.3, and Pre-9.1.

Microsoft Azure has already announced that it will support higher versions in its Azure Database for PostgreSQL managed service (currently 11.0 and 12.0). Google added support for PostgreSQL 12 on May 21, and also supports versions 9.6 (through 2021), 10, and 11. AWS RDS for PostgreSQL includes versions 9.5, 9.6, 10, 11, and 12.