What's (not) in Postgres 17 beta1, and how to test it

By Lukas Fittl

May 25, 2024

In today’s E116 of “5mins of Postgres” we discuss the Postgres 17 beta1 release that came out earlier this week, some highlights of the release, some changes that unfortunately got reverted since the feature freeze, and how to help the community during the beta testing process.

Share this episode: Click here to share this episode on LinkedIn. Feel free to sign up for our newsletter and subscribe to our YouTube channel.

A summary of the Postgres release schedule
What's in Postgres 17 beta1?
What didn't make it - Reverts since 17 feature freeze
How to test the beta release and report bugs
In conclusion
What we have discussed in this episode of 5mins of Postgres

Transcript

A summary of the Postgres release schedule

A quick reminder on the Postgres release schedule. Postgres major releases come out once a year, typically in September or October. So that means we have 4 more months ahead of us, where this will turn from a beta into a release candidate, into the final release. Now is the time to help test and find bugs so that when we get to the final release, we know what's actually working well versus which things might need some more revisions.

Today, I want to mention a few more things in this release that we didn't talk about previously in 5mins of Postgres. I also want to highlight to you a few of the commits that were reverted because sometimes, we get excited and we think that something is going to be in the final release when unfortunately it gets reverted during the beta period or even before the beta1.

And so there were five changes that I want to call out today that were initially planned for 17, but that didn't end up making it in the end.

Now let's take a quick look at a few of the improvements.

What's in Postgres 17 beta1?

Performance improvements: VACUUM, I/O, CTE planning, WAL locking and more

In the release announcement there are quite a few performance improvements mentioned. We talked previously about things like the new radix tree implementation that makes VACUUM use a lot less memory, thanks to more efficient dead tuple storage. We also talked about the streaming I/O interface, that improves performance, and is the building block for asynchronous I/O.

And we talked about some of the new configurability for transaction, subtransaction and multixact buffers (aka SLRU caches) that will help Postgres scale better for very large workloads. One thing we didn't mention yet previously was the fact that common table expressions can now use planner statistics from the CTE. So if you have complex queries, you might see that Postgres now comes up with better query plans.

Also, Postgres 17 has a noticeable improvement in how the write ahead locks are managed, so if you saw in your wait events, that the WAL locks are a problem on your workload, there's a good chance that this will be less of an issue in Postgres 17.

Also generally, what's really nice about all of this is that for these performance improvements, you usually do not need to change your queries, Postgres just gets better by upgrading.

Operational improvements: Logical replication

Now there are also a few more operational improvements. One thing that I think will make a lot of people's lives easier around logical replication, is the fact that now after "pg_upgrade", you no longer need to drop logical replication slots. And this is a big deal because otherwise you would have to resynchronize your slot from scratch after an upgrade.

Also, if you have more complex setups where you're using logical replication from your replicas, you can now use the new "pg_createsubscriber" command to correctly initialize logical replication from a physical replication snapshot. There were ways to do this in previous releases, but now this is a lot more straightforward to do.

Now, if you're a developer, you might ask yourself what's in it for me.

Developer experience improvements: SQL/JSON is back, MERGE RETURNING and COPY ON_ERROR

So from the application side, it's worth calling out JSON_TABLE and the other SQL/JSON improvements that are coming to 17. So this was something that was previously in Postgres 15, but it ended up being reverted due to the executor overhead this had not just on the JSON queries, but on all queries. The good news is that the community found a way to implement this more efficiently, and so now in Postgres 17 we can have features such as JSON_TABLE or the SQL/JSON constructors as well as the query functions to work with JSON more effectively in Postgres. Also, there are other improvements, for example, to the "jsonpath" functionality.

If you're using MERGE, which got added in Postgres 15, you can now also use the RETURNING clause in MERGE. So this lets you work with the modified rows after the MERGE command succeeded.

And then last but not least, if you're using COPY to bulk load data into Postgres, previously if even one of the rows failed, your whole COPY process essentially had to fail. Versus in 17, we can now specify that we are okay with individual rows erroring out and these being reported back, but the rest of the data being imported successfully.

There's obviously a lot more things that were improved, but I think those just one of the few key things that did make it into the release.

What didn't make it - Reverts since 17 feature freeze

Now let's take a look at the things that unfortunately did not end up making this release.

So I'm sure many of you were excited about the new temporal primary key and foreign key feature, that Peter Eisentraut and other folks worked on. Unfortunately, there were some issues found such as the handling of empty ranges, and so sometimes it's possible to fix these issues after the feature freeze, but unfortunately there was still quite some discussion necessary to find the right approach, and so it was decided to try again for Postgres 18 and revert this feature.

Now, one thing that in a sense sounds like a cosmetic issue, but actually it was something I've gotten confused by a few times is the cataloging of NOT NULL constraints. So when you look at which columns have NOT NULL constraints in Postgres, you essentially have to think a little bit more about how exactly to look at the data that's in the catalog tables. And so the idea was to streamline this, to always catalog these NOT NULL constraints, but unfortunately that did not end up working out because there were a few problems that were found. And again, they were too invasive to be doing them late in the release cycle.

And then there are 3 more reverts here:

Removing useless self joins unfortunately got reverted, so if you're joining your table unnecessarily, maybe your ORM generates a bad query, you still should fix your queries, 17 will not do that for you unfortunately. Maybe we'll get this functionality in Postgres 18.
Custom reloptions for table AMs and other changes that Alexander Korotkov worked on for table access methods. Table access methods initially got added in Postgres 12, but there were quite a few improvements outstanding that Alexander had been working on. Unfortunately, these changes were also quite invasive, and that's part of, why, further review, it was decided that it's best to revise how these modifications are made.
Transform OR clauses to ANY expression, if you had a query like X equals 1 OR equals 2, Postgres would have transformed this into an "= ANY" expression. That unfortunately did not make it in either because there were some implementation and design issues.

And this is to be expected, and so really what our job now is as users of Postgres in this community, is try out the new functionality (not in production!).

How to test the beta release and report bugs

Now you might be wondering how can I test the new release? So first of all, the community publishes packages for the new release, you can install those RPM and DEB packages now. And I expect that there will also be a release shortly of the Docker container for beta 1.

There is a "How to beta test" entry in the Postgres Wiki, and this one is a little bit outdated in terms of version references, but generally I would say it's still pretty accurate.

Most important thing is if you do find a bug, or what you think is a bug, you can either report this to the pgsql-bugs mailing list. This is a good mailing list to be subscribed to if you want to know, maybe somebody's already reported the bug. And so that way you don't report the same bug twice. But if you're in doubt, feel free to make a post to the mailing list.

Do try to include a reproducer. Don't just say "my application crashes", make it self-contained so that if somebody installs Postgres 17 beta1 on their own system, give them something that they can actually run, versus them having to ask you for a lot of information. Also, if you don't feel comfortable subscribing to the mailing list, you can also use the bug reporting form on the website.

In conclusion

Release testing is a community effort with Postgres. Try to test things and thank you to everyone in the community who contributed to this release.

I hope you learned something new from E116 of 5mins of Postgres. Feel free to subscribe to our YouTube channel, sign up for our newsletter or follow us on LinkedIn and X/Twitter to get updates about new episodes!