Postgres 16 is released!

By Lukas Fittl

September 14, 2023

In E82 of “5mins of Postgres” we're talking about Postgres 16, which was released today!

There are a lot of exciting features in Postgres 16. I want to give you a quick rundown of a few things that I find exciting, as well as a few things that you might have not known about yet that people in the community pointed out in the last couple of weeks.

We had talked about some of them previously in 5mins of Postgres episodes. We will link them at the end of this article!

Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel.

Postgres 16 highlights
PostgreSQL 16 and its new features
What we have discussed in this episode of 5mins of Postgres

Transcript

Let's have a look!

Postgres 16 highlights

First of all, there's always two places you can look for what's new with Postgres.

There's the official release announcement, which gets posted on the website. And then there are the release notes, which are usually more detailed, but you'll need a little more time to parse for all the little benefits that are new in this release.

pg_stat_io

In the release notes, we can see a few of the highlights in Postgres 16. We previously talked about pg_stat_io. It is something that I'm very excited about and I think will give you a lot more clarity when you have bottlenecks on your server. You can also watch 5mins of Postgres E57 on how Postgres DBAs can use pg_stat_io.

Parallelizing joins

But there are a couple of other benefits here. For example, parallelizing joins with Postgres 16, so you will essentially see improved query plans based on that.

Logical replication

You'll also see benefits from logical replication. Now you can logically replicate from a standby server and you can have logical replication subscribers apply large transactions in parallel. These are further improvements to logical replication from previous releases in Postgres 15 like logical replication column filters and others.

SQL/JSON and JSON_TABLE

If you recall, Postgres 15 introduced some of the SQL/JSON changes, but then late in the release cycle, those were removed because of performance considerations. Luckily, for Postgres 16 the community had another go at this, resulting in a good portion of the SQL/JSON work now being in Postgres 16.

VACUUM Freezing

And last but not least, there are a couple of improvements around VACUUM freezing that I think will also benefit a lot of folks.

There are a lot more things that are in Postgres 16. So how can we find some of the nuggets?

PostgreSQL 16 and its new features

Ryan from the PGSQL Phriday community asked himself the same question. And so the prompt for PGSQL Friday 12, the 12th incarnation of this monthly occurrence, was to ask the community what they thought were non-obvious Postgres 16 improvements.

Postgres 16 allows +infinity in datetime input

And I just want to surface one of these here, which is this article by Ryan Lambert, where Ryan talks about how Postgres 16 improves infinity. What Ryan speaks about here is the change that says accept the spelling +infinity in datetime input. Generally speaking, when you think of infinity in Postgres, it means either infinitely positive or infinitely negative value. In the context of timestamps, it can be beneficial to think about a value that's infinitely in the future.

And so what's now supported in Postgres 16 is that you can cast positive infinity to TIMESTAMPTZ, and you will actually get a positive infinity timestamp.

Testing for performance regressions

Now, one of the things that is important when you're considering whether to upgrade to a new Postgres release is whether there are going to be any performance regressions. Of course, nothing will replace testing it with your own workloads, and oftentimes for serious production systems, it does make sense to wait for a patch release just to make sure that if there are any obvious regressions that other people catch them first before you do.

However, what I do like is that the community has different tries of seeing how performance regressions might've occurred. In this blog post, for example, Mark does a lot of testing between different databases. He tests MySQL a lot, he tests Postgres, and such. You can read about him running a test between Postgres 15 and Postgres 16 using Sysbench and just checking if there are any CPU regressions or anything obvious.

He runs a quite basic test, but I think it's good to see that people are doing these types of tests and they're essentially confirming that, at least right now, there's nothing obviously making the new Postgres releases slower.

Bidirectional replication using origin filters in logical replication

Lastly, I want to point out bidirectional replication using origin filters in logical replication. Oftentimes people are asking: how do I do active-active replication in Postgres? There are third party solutions that don't rely on the built-in logical replication to do this, but there's nothing today in Postgres yet that does that active-active type replication. I would argue that Postgres 16 doesn't really give you that yet, but it does introduce an important aspect to this that will let you build your own solution more easily using core components.

In this above linked article by Vignesh on the Fujitsu blog, he talks about that new logical replication origin filtering. The general idea here is that you are annotating changes that get replicated into a node with having a particular origin. Then you can avoid the logical replication output plugin giving you those same changes again.

If you imagine two nodes replicating to each other, the problem on Postgres 15 would be that you couldn't differentiate between a local change and a remote change. The improvement in Postgres 16 is that we now have that way of saying: “if this data comes from a remote node, because it was replayed using logical replication from that remote node, then we're able to ignore it.”

This is a very important building block for an active-active setup using logical replication. There are a lot of caveats around this so I would definitely not recommend you just going ahead and saying this is the only way I run my production databases, but I do think that this is worth exploring.

Vignesh does go into a lot of details in his blog post and I want to point out the limitations that he lists at the end of his article. A lot of the issues have to do with when you're trying to introduce new nodes into such a setup, and where you're trying to synchronize the initial logical replication setup. This requires a lot of fine tuning, a lot of handholding, but I do think it's worth looking into this new origin filtering as a way to build some tooling around this.

I'm really excited about Postgres 16. I think it's the best Postgres release yet, and I'm sure you'll see a lot of benefit when you're upgrading your databases.

Thanks for joining us for E82 of 5mins of Postgres. Subscribe to our YouTube channel, sign up for our newsletter and follow us on LinkedIn and Twitter to get updates about new episodes!