Hidden Gems of PostgreSQL 13

PostgreSQL 13 is a lucky release! There are some very impactful new features and the release is coming out at its regularly scheduled time in the fall.

One of my favorite parts around the PostgreSQL release process is when I can take a step back, review and discuss the features going into a new release, and learn how they can make a positive impact on our users. For example, in a past hallway track at PGCon, I learned how PostgreSQL 12 introduced the ability to do a form of multi-factor authentication!

Each release has a lot of these "hidden gems" -- features that may not jump off the page, but can have a big impact when you actually need them. Postgres 13 is no exception: some of these features make it easier to write queries, add additional layers of security, or help you to avoid downtime.

So what are the hidden gems of PostgreSQL 13?

Guard Against the Rogue Replication Slot

PostgreSQL replication slots, introduced in 9.4, provide a convenient way to prevent replicas from getting out of sync from a primary, and are an essential component for logical replication. Arguably, this helped to move PostgreSQL replication itself to become closer to a "set and forget" feature: once it was working, you would know that your replica would not get out of sync.

However, this opened up a new kind of problem: "the unacknowledged replication slot." Let me explain this from personal experience.

The way that replication slots work is that they have the primary PostgreSQL instance retain its write-ahead logs (WAL) until all slots have acknowledged that they have received a particular segment. Once that is complete, the WAL file is removed from the primary. Let's note that in a standard PostgreSQL installation, a WAL file is 16MB large.

In a previous life, I wrote an application that used logical replication (at the time, via logical decoding) that would stream in all the changes from my database and take actions based on those changes (aka "change data capture"). On occasion, not only would this process crash, but my monitors for it would fail (yay crash loop!). However, one monitor did not fail: a warning that my database was running out of disk!

What happened was that given the replication slot was not being acknowledged, my database server kept retaining WAL files. Had I not intervened, my PostgreSQL instance could have run out of disk and completely gone down.

This leads to a hidden gem of PostgreSQL 13: max_slot_keep_wal_size. If set, this value indicates the maximum amount of WAL files that should be retained should a replication slot go unacknowledged. If this value is exceeded, PostgreSQL will start removing the oldest WAL files.

The good news is that this feature can help prevent downtime due to a rogue replication slot. However, you risk that a replica goes out of sync and has to be reinitialized.

Regardless if and how you use replication slots, PostgreSQL replication is not completely a "set and forget" feature: you do need to monitor the health of your PostgreSQL replicas.

Greatest Common Divisor / Least Common Multiple

It's not a trick question on a math exam: PostgreSQL 13 adds the gcd (greatest common divisor) and lcm (least common multiple) functions to its math library! These functions are helpful in solving problems that involve alignment and, when you find yourself in that situation, you'll be thankful for this hidden gem!

Certificate Authentication for the PostgreSQL Foreign Data Wrapper

There are a lot of things to consider when deploying an application using the postgres_fdw to production, especially with security. A strong consideration to make: how will one Postgres instance authenticate to another?

PostgreSQL 13 expands the authentication possibilities for the PostgreSQL FDW by introducing the ability to use certificate-based authentication. Authenticating with certificates has its advantages over passwords. While it may take a bit more work to set up certificate authentication, it can be easier to administrate and secure over the longer term.

Extending this authentication mechanism to the Postgres FDW provides another security enhancements to using this PostgreSQL feature in production and another hidden gem for this release!

And speaking of certificates...

Use Encrypted Certificates to Connect From Your Applications

Do you want to use your encrypted certificate to connect to PostgreSQL, but don't want to have to type in the password prompt each time?

PostgreSQL 13 adds a new client connection parameter called sslpassword that lets you specify the certificate's password as part of the connection string. This makes it more convenient for applications to leverage encrypted certificates as no more user interaction is required!

To take advantage of this hidden gem fully from an application, you'll still want to keep your certificate password in a safe place, such as your operating system keychain or a vault.

Only Normal?

Unicode normalization is an important technique for comparing unicode strings. For example, normalization is a key step in preparing a UTF-8 string for SCRAM authentication as it ensures that strings that are canonical equivalent can be compared.

PostgreSQL 13 adds the "normalize" function, which can be used for string normalization so long as the server uses UTF-8. This hidden gem can be helpful for performing lookups on UTF-8 strings: perhaps you can even use it as part of an expression index if you don't want to store normalized strings.

UUID Generation Without Extension

Did you know that you could generate UUIDs in PostgreSQL with the gen_random_uuid() function? Did you know that prior to PostgreSQL 13, you had to install the pgcrypto extension to get this function?

While pgcrypto is awesome for many, many reasons, you no longer need to install it to generate UUIDs of the v4 nature. Certainly a convenience, certainly a hidden gem.

Allow Me To Explain...

Though there is a lot of neat stuff added to EXPLAIN in Postgres 13, I wanted to introduce you to the PostgreSQL Glossary. There are a lot of terms that get thrown around the PostgreSQL ecosystem -- many of them used in this blog -- it can be hard to keep track. And then there are the acronyms, and honestly, I always have to look up ACID because I tend to mess up one of the words.

The glossary is a nice gem added as part of the PostgreSQL 13 documentation, and I encourage you both to view and expand upon it!

But Wait... There's More!

There's a lot more, and I encourage you to go through the PostgreSQL 13 release notes and see if you can uncover your own hidden gems.

Sometimes, we don't know what the most impactful features are in a new Postgres release until we try it out, so I also encourage you to give it a whirl and see for yourself!