On Thursday, September 24, 2020, Postgres version 13 was officially released. As opposed to a release full of "new features", Postgres 13 largely improves upon existing functionality.

The Release Notes can be found here -> https://www.postgresql.org/docs/release/13.0/

In this blog post, I review three of the more impactful enhancements.

(Click on images to enlarge)

Parallelized Vacuuming of Indexes:


Postgres rows/records are referred to as 'Tuples'. When these records are 'deleted' by end-users, the records/tuples are not actually deleted. Rather, they are marked for deletion. These are referred to as 'Dead Tuples'

This, in turn, creates a Postgres database state called 'Bloat'.

A Postgres 'Vacuum' is a process by which these dead tuples are removed.

To maintain peak performance, it is critical that these vacuum operations run as quickly and efficiently as possible.

Postgres version 13 introduces the Parallel Vacuum of multiple indexes corresponding to a single table.

This speeds up the processing of these expensive operations and improves overall performance.

De-Duplication of B-Tree Index Entries:

Postgres version 13 offers B-Tree Index entry de-duplication.

Indexes are an important component of relational databases. Like an index in a book, they speed up look-up time.

A B-Tree index is the most commonly used Postgres Index. The B-Tree Index is particularly efficient because it essentially "sorts itself" and maintains as a balanced and accessible data structure.

If you really want to geek-out, read about Lehman and Yao's high-concurrency B-tree management algorithm -> https://www.csd.uoc.gr/~hy460/pdf/p650-lehman.pdf (my head hurts)

Postgres takes this powerful index and improves upon it. In short, the de-duplication of B-Tree Index entries provides both space savings and performance gains.

Incremental Sorting:

When a database needs to sort data, it takes a performance hit. It logically follows that sorted data makes for a faster query than non-sorted data.

With Incremental Sorting introduced in Postgres version 13, sorted data from earlier in the query can be leveraged to speed up the latter part of the query.

Again, this blog highlights of only a subset of the many additions and improvements to this latest version of Postgres.

So, climb your way to tip-top performance and be sure not to pass by Postgres version 13.







Related Content