Postgres 19

Postgres 19

Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in September, the features we like most, and what to check before you upgrade.

Michael [00:00:00]: Hello, and welcome to Postgres.FM, a weekly show about all things PostgreSQL. I am Michael, founder of pgMustard. I'm joined, I was gonna say as usual, but I've had a bit of a break. I am joined by, I guess, the two usual now, guys, Nik from PostgresAI, and Kirk. Hey, Kirk.

Kirk [00:00:17]: Hey. How we doing, Michael?

Michael [00:00:20]: I'm keeping well. I was just telling you actually, I've had the same flu-type thing that the rest of the UK's had for the past week. So you might hear it in my voice a little bit. But feeling much better today and yesterday, so yeah.

Nik [00:00:33]: How are your vacations?

Michael [00:00:37]: Vacations. I haven't been on vacation. I've been working on a new product.

Nik [00:00:41]: I remember when we met a couple of months ago in London, I was surprised. You look like you're on vacations right now. I think people who watch YouTube can see how you look usually in London, right?

Michael [00:00:59]: Yeah, it was great to see you in London and to meet your son as well. That was really fun. And we had a bit of a late, we call it an Indian summer here, like a late heat wave almost, so I was in shorts.

Nik [00:01:16]: So today I guess we have an impossible task, because November 19 is a very well-known release date, but for very different software. Do you know which one? No? Everyone knows.

Kirk [00:01:30]: I don't.

Nik [00:01:31]: Except some few Postgres nerds, right? Because GTA VI is being released on November 19. And we sit here to discuss Postgres 19, right? That's funny, right?

Kirk [00:01:49]: Wow.

Michael [00:01:49]: In the current GTA release cycle, when would GTA 19 come out? Like a couple of hundred years?

Kirk [00:01:57]: Well, I think the first thing we need to mention is Postgres 19 is ready for release. They've committed the changes from release candidate to version 19, and that means it's imminent at this point, we hope. But help me out here, Michael. Did you slow it down by go working on your own project? Did you slow down Postgres or something? Seems a little late.

Michael [00:02:24]: No, nothing to do with me. But I actually did want to thank the people that had slowed it down. For the last few years it's been very, very reliably in September, and the project aims for an annual release cycle, right? Like annual major version upgrades, and then quarterly minor version upgrades, plus some security fixes that have come up off cadence.

And that's been so reliable for quite a few years, and this is quite the shift, right? September, October, November, a couple of months later. I know there's been tons of articles about this. We've probably discussed it before, but I think most of the articles have skewed a little bit negative, like why it's such a bad thing that it's delayed. But I wanted to say I'm really pleased that people have firstly prioritized the security fixes. I think that was the number one reason that this got delayed, because a lot of the key committers were tied up, because they're also part of the security team.

And we've seen in the last two minor versions 70-plus CVEs, so that's a lot of work. I think the reason we've got the CVEs is people using LLMs to find them. And I think it's much better that we've delayed this and got those fixed. And I know the negativity's probably mostly about the reverts that happened. But I've seen several posts. I don't know if you two agree with this analysis. I've seen several posts that have argued the reverts are actually very similar in number, maybe a little bit bigger in size than previous versions.

They just happened later in the cycle, so it seemed worse.

Nik [00:04:22]: When I was reporting some bugs found with AI, of course, I felt like if I keep pushing, I can do more damage and more things will be reverted. For example, at least two threads where I started pushing led to a revert. One was the referential integrity stuff, and the other was the last one, when I decided to give even more power to AI and stopped diving into details completely. It was an experiment. We should talk about it separately.

And it led to a revert. More and more bugs are coming, and then a revert. For example, I just checked it yesterday: the number of messages to the pgsql-bugs mailing list increased significantly, while the number of messages to pgsql-general decreased. And depesz wrote a blog post about that a few days ago, right? And I just felt like, if I keep the pressure, I will probably trigger more reverts. And also, coincidentally, 19 is coming, so I decided to stop pushing.

My hypothesis is some guys also stopped pushing too much, just to let the feature list stabilize. I'm 100% sure we could find more bugs. We just didn't pull more attention to it. So right after the release, I'm going to find more bugs. And others will do.

Michael [00:06:11]: Go on, Kirk.

Kirk [00:06:12]: I just wanna back up the truck. So as we were moving into the date to go live, there was a big revert fest, right? And usually there's a revert fest early on, in February. I think that's when Andrey coined the phrase, or I heard it the first time, so I'll give him credit, but it made me laugh out loud. We have commit fests, and then we had a revert fest where, yeah, these really aren't ready and they're gonna need more time. But they pulled those, and they were at a release-candidate kind of level, and they pulled a handful.

And I just want to give them credit because, look, you don't push bad positions, okay? Never once in my life has pushing a bad position turned out beneficial in the tech world. So I prefer to pull back a bit and regroup and come back later, and that's what they did. And the catch was: we have the same number of committers, we have double the number of ideas and patches coming through, and now we had something like 10 or 20x the number of CVEs in the same year.

That's insane. There's no way the committers can keep up with all of it. And I'm happy their focus is on stability, because if we lose stability, then we're gonna lose the mind share in this industry. If we lose the mind share, we don't become people's first choice on new projects. So I'm just gonna say kudos for their decision. It's late, and so what? It's a Christmas gift now, right? It's late enough in the season. Happy Thanksgiving, everyone. Here's your new version of Postgres. Enjoy it when you have a few days off with the family.

Nik [00:07:53]: So, the revert fest's big wave started with this scary, scary list, right? From Robert Haas.

Kirk [00:08:07]: Yeah, Haas.

Nik [00:08:08]: It was from Tomas Vondra, the blog post about that: it's maybe the same, but problems are found later and reverts happen later. So the question is how to find problems earlier. And this tiny project, pgroast, we started last week, right? To criticize the architecture and the idea earlier, before it gets committed. Maybe some new tools will help. And AI could help to improve things at an earlier stage.

Michael [00:08:42]: There's an argument that LLMs got significantly better at an awful time in the Postgres release cycle. If you think about when feature freeze is, it feels like it times very, very well with things like Opus 5.5, and a lot of people getting access to quite cheap, very, very advanced models that don't make that many mistakes. That feels like awful timing for a project that's just about to release some things, or great timing if you're thinking about it from a startup—

Nik [00:09:15]: It's bad after release at least.

Michael [00:09:17]: Timing-wise.

Nik [00:09:18]: Similar, yeah. But Opus 4.8 was already quite good and could find a lot. The question is whether you can allocate a lot of resources to it and so on. And also there is time for reaction and changing habits, right? And also there are guys who had zero or one. I saw a couple of guys who were mentioned around 100 times in commit messages this year and zero or one times last year. So obviously some people pulled a lot into bug finding. Let's talk about features, right?

What survived. I will start with REPACK. You remember, Michael, how I was extremely surprised at the end of our episode about—

Michael [00:10:09]: Me too.

Nik [00:10:10]: pg_squeeze, to learn the plans to push it to the core, and here we go. It survived.

Michael [00:10:17]: Not only did it survive, but it made it to the next version. When Antonin mentioned that to us, I was thinking, "Okay, we're talking several years."

Nik [00:10:27]: Two to three years. Yeah.

Michael [00:10:29]: And I think they did two big things. Obviously CONCURRENTLY is huge, but also getting rid of the VACUUM confusion. VACUUM FULL being so, I'm gonna say, dangerous, whereas VACUUM is so innocuous and useful. That for me was always quite a big challenge in communication and education, and they fixed both in one go.

Kirk [00:10:59]: Yeah. It's like we got a vacuum well. The vacuum full.

Nik [00:11:04]: So basically we have pg_squeeze in core right now, officially supported. And based on logical decoding, it can shrink your tables and indexes and you can get rid of bloat, which is good, right?

Michael [00:11:22]: Yeah. And you can use an index for the ordering, so you can get a CLUSTER at the same time. The only thing I think you can't do that's in pg_squeeze is the scheduling.

Nik [00:11:36]: Scheduling, you mean, to do it on the weekend?

Michael [00:11:42]: Yeah, exactly. Or at a certain... I'm not sure if it's just time-based.

Nik [00:11:47]: Right. Well, you cannot schedule anything in Postgres without additional means, like the pg_cron extension or an external scheduler. And this is actually sad, because if pg_cron or some alternative was part of core, it would be great, because obviously there is a need to schedule things, right? Right now we have REINDEX INDEX CONCURRENTLY, and we also need some machinery to do it on weekends, for example. Here is a similar thing. There is REPACK and REPACK CONCURRENTLY, and to schedule it, even with pg_cron, purely internally, the question is that with the word CONCURRENTLY it cannot be part of a transaction, so you need a special dance.

It's possible. After Postgres 19, I'm going to revisit this idea. It's a good idea to have some combination of tools which will work on any managed Postgres and allow you to schedule properly and manage schedules without any external means. We did it with REINDEX INDEX CONCURRENTLY. It works. And we can do it with REPACK CONCURRENTLY as well, right? Do you think pg_repack will be over with this release, or the need for pg_repack will still remain?

Michael [00:13:15]: I'll go first. I would love to hear Kirk's answer as well. I think it will massively reduce, not necessarily how much people need it, but who needs it. I think for the vast majority of workloads, REPACK CONCURRENTLY will actually be fine. And there are edge cases, and there's a really great post on boringSQL by a friend of the show, Radim, on exactly where you can hit the limits currently. But what do you think, Kirk?

Kirk [00:13:49]: I was gonna say basically the same thing. Also, it's a new feature in a new version. There's still people out there struggling with 12 and 14. Let's be honest, everyone isn't switching tomorrow when it's released. And you can't get rid of it even for people like me who would want to, because we've already scripted things to use pg_repack. I don't want you breaking it on me. I'll migrate my tools in less of an emergency state after I upgrade, right? So that's my take on it. But I think it's wonderful, the more of that stuff that makes it in.

And then I like Nik's idea. I wish pg_cron was part of the product offering. It should be one of the extensions that comes along for the ride with Postgres, in my opinion. But yeah. Cool.

Nik [00:14:39]: Not only pg_cron, also autonomous transactions. There should be some way to connect to yourself without using—

Kirk [00:14:48]: Passwords.

Nik [00:14:48]: postgres_fdw and dblink. It's passwords and so on. It feels insecure. Probably we should explore it separately. I also agree that pg_repack will probably go down in terms of popularity, how it's used. But I think both pg_squeeze and pg_repack, so REPACK CONCURRENTLY as a successor of pg_squeeze, are both not working in some heavily loaded large table cases. If you have a 10-terabyte table under load, it just doesn't work.

And additionally, dealing with logical replication, some people just don't like it, so there are some edge cases. And honestly, I think the old idea of pgcompacttable might be resurrected. You just update—

Kirk [00:15:45]: Is this the one where you update things—

Nik [00:15:47]: Row by row.

Kirk [00:15:47]: Later in the heap? Yeah.

Nik [00:15:49]: Yeah. Slowly you update, and the dead tuples go to the end, and then vacuum truncates the last pages. But then—

Kirk [00:16:00]: I had questions. Who's on Heroku, and they don't have pg_repack, and that's one of the benefits, right, of REPACK CONCURRENTLY: even on managed services, you'll get it. So I was gonna suggest the compact table approach, but I think a lot of the optimizations we have now around HOT and... I think by default Postgres tries to keep updates on the same page, right? So I wasn't sure how... No?

Nik [00:16:30]: Well, if you touch an indexed column, it cannot be HOT, so—

Kirk [00:16:37]: No.

Nik [00:16:37]: Definitely will be normal.

Kirk [00:16:38]: Yeah. See, that's the trick: you have to update. Okay.

Nik [00:16:41]: And with pgcompacttable, of course, the downside is that indexes will need to be rebuilt afterwards, additionally.

Kirk [00:16:51]: Do you have another favorite for PG19? Moving on.

Nik [00:16:56]: You know my "don't use it" list. Subtransactions, and—

Kirk [00:17:00]: Temp tables.

Nik [00:17:01]: Temporary tables, last week's episode. LISTEN/NOTIFY is almost in that list. Almost, because there are a few more cases where it's okay to use it. But you remember the post from Recall.ai that NOTIFY doesn't scale. There is a big single global lock on commit. And then the DBOS guys also touched that topic. We discussed it on our podcast as well. That lock is still there, unfortunately. But there are improvements for when you have a lot of listeners. It's already great. So one problem is already resolved.

So in some cases, in Postgres 19, LISTEN/NOTIFY will work much better than in Postgres 18. And we have some graphs showing throughput, right? With 1,000 listeners, from 200 per second, we went to 11,000 per second. And it's great. Notification throughput. So drastic improvements. But of course, if you combine NOTIFY with some transactions in a highly concurrent environment, that single global lock is still there, so Postgres 19 doesn't solve it.

So I'm partially satisfied with this feature. It's great to see the progress here, definitely. What about you? What are your—

Kirk [00:18:30]: The first one, and my favorite, is actually NOTIFY, because we actually leverage this in our systems. We have numerous external systems and tools that augment what our system does, and searches in real time behind the scenes to put things back together. So we need that. In fact, we end up using pg_background to do the commit, so it's out of the transaction that we're currently in, so that there's time for the communication to the other servers, who then lift that stuff off. So for me, that was a huge one. We're not currently jammed, but it's flagged in our system: as we grow, this could be a problem, right?

Because the single—

Nik [00:19:16]: Have you considered using logical decoding for asynchronous notification? Like, super bandwidth.

Kirk [00:19:23]: No, we haven't. Again, I'm still relatively green at some of the Postgres stuff, so I'll be honest, I'll always admit where I'm ignorant. I can get into the nitty-gritty on anything if I focus on it, but I haven't looked at that 'cause we haven't had to. The app works, and we use this in three or four separate places where we need notifications and alerts. One of them sends something to a manager if certain people do certain things. So it's really handy for us. I love that they're working on it, and I know it'll get fixed. The other one I like is the autovacuum one, and there's a specific reason I like the changes for prioritizing and letting us queue things up differently: Selma is gonna need this for our bloat removal, because we need four or five passes on a table to clean out the bloat properly, because we have to do it incrementally.

So we have to actually go through a table, and we know we only did step one of three, and then we have to flag it to queue it back up, but we need some time to pass. So this is gonna be interesting for us. So I'm liking that one a lot. Of course, REPACK is great. As long as it doesn't get in the way of our bloat removal—

Nik [00:20:40]: Funny thing: behind the scenes, samograph is connected to this Forge system, and it has some budget and freedom, so it decided to do more tests of NOTIFY to compare with the bottleneck from the Recall.ai and DBOS blog posts. It just decided to bring more data, which we probably will include in the show notes, right?

Kirk [00:21:05]: How about you, Michael? What are your favorites in PG19?

Michael [00:21:10]: Well, I am a big fan of the autovacuum improvements as well, actually. The prioritization feels like, once I've heard about it, it's kind of embarrassing that I hadn't thought: why don't we have it already? But kudos to the people that worked on that. And I think parallel autovacuum will be useful for a lot of people as well. I like these things where you don't have to change anything as a user. You just have to upgrade, and then things will work better.

So those two are great. Probably the biggest one we haven't mentioned yet on my list, which I'm not gonna be a big user of, but I think it's a huge step in a very interesting and good direction for the project, is the plan advice module that's been added to contrib. And I know it's not hints... Well, but it kind of is, right?

Kirk [00:22:03]: It's better.

Michael [00:22:05]: It's a toolkit that can be used by extensions going forwards, and I think the biggest sign of how good it is, is how much code was able to be removed from pg_hint_plan. And Lukas Fittl's got a really good video on this, a 5mins of Postgres episode, that's worth checking out if you haven't seen it already. This doesn't replace pg_hint_plan. It's very much a version one, and, as I said, a toolkit that others can build on. But this has been a discussion and a no-go area for Postgres for years, and I think it is a step in the direction of: let's let people fight the fires of plan flips with a few tools if they want to.

And let people tick a box when they're migrating from Oracle or trying to get a DB2 migration approved or something like that. So I think there's loads of benefits to this, and it's been implemented in quite a mature way.

Kirk [00:23:12]: I'll correct—

Michael [00:23:13]: Again, mostly led by Robert Haas.

Kirk [00:23:15]: I'll correct one thing. You said years, I think you meant decades.

Nik [00:23:20]: Decades. In the session which led to the development of pgroast, we found this page in the wiki: we won't accept these proposals, and one of those proposals is hints.

Kirk [00:23:32]: Hints.

Nik [00:23:33]: And now we have this. I actually think it's a great thing to have, because we saw that Aurora has plan management and the ability to pin plans. I had quite a challenging experience, lasting many months, with an upgrade from 17 to 18 because of many plan flips, which are actually hard to catch. So we needed to improve our methodology for how we analyze future plan flips before upgrading.

Now we have a very comprehensive methodology, without any gaps, but it's quite expensive to use. You need to actually allocate resources to study all the plan flips. And then, what to do with the flips? Now we have a solution for what to do with flips, but plan flip analysis still remains another challenge, right?

Kirk [00:24:33]: Yeah, 'cause what's a good flip versus a bad flip? Sometimes it's a small, subtle change. Sometimes it's a sequential scan when it should never be. But I wanted to point out something, just for the people that are listening who might not be familiar with plan hints. Okay? Plan hints to me is the 1975 solution to having a query where you need to tell the database, "Hey, you're gonna calculate this wrong. So I'm gonna put literally a slash-asterisk hint after the word SELECT, and I'm gonna put some things in there and tell you what tables and indexes you should really be using as your driver table or primary, whatever.

I'm gonna focus you, because you're gonna make mistakes." And I want you to hold it right there. It was a great idea in 1975, okay? The problem is, it's 2026, baby, and the reality is you're hard-coding the hint into the application, usually. The last thing you wanna do is be tweaking your application because of a subtle change in the database, because now your hint is actually a bad idea, and that happens. And this plan stashing works differently. The high-level version is this: you can actually calculate a good plan, and then you can associate it with the SQL ID. Okay?

And then you can give it priority, and this gives you a chance as the DBA to change the behavior of the application without getting the application developer involved. He can ring you up and say, "Hey, I got a query running like crap." You can find that query. You can validate your new idea and then store it in the database as to how it should be handled in the future. Again, it's still the newest version of this, but it is architecturally the right solution. And what I love about it is that long-term there will even be settings in there where you tweak how you want this specific SQL to run based on the environment it's running in.

And that's cool. You could never do that with hints, and for me, that's what actually makes hints bad: they codify an instance of a situation into the application, where you don't wanna go changing it later on just because your environment's different or better or faster. And now you can actually do it at the DBA level, which is where, in my opinion, it belongs. Okay, I'm off my soap.

Nik [00:27:09]: As I see, our testing found some overhead from using advice. Around 10%, right? So it's not free when we talk about very fast queries. And there's a long list of limitations, but it's a great step in an interesting direction, which I think bigger databases actually need. So a good thing to have. What else?

Michael [00:27:43]: I have one that—

Kirk [00:27:44]: You got one more? Go ahead.

Michael [00:27:46]: Quite a lot, Nik. Better defaults. I think a couple have changed in a really good direction. JIT being off by default feels far more sensible to me than it being on by default.

Nik [00:27:59]: It was long overdue. It should have been off by default from the very beginning, yes.

Kirk [00:28:03]: We turned it off.

Nik [00:28:05]: It actually shows a lack of proper testing before a feature is released, and this can be improved with AI. This is my natural reaction to this. For example, I will propose to turn on track_planning in pg_stat_statements, because the benchmark which led to the decision to keep it off was weak. Same with JIT, but vice versa: it was on by default, while it could have been understood earlier that it's the wrong idea to keep it on.

Great thing. Great thing.

Michael [00:28:42]: And another one. JIT, I think, can really bite people. This one not so much. This is much more just a small improvement, which is that TOAST compression by default now uses... well, in fact, a couple of compression things now default to better, more modern compression algorithms. LZ4 in the case of TOAST, if your Postgres was built with it, which a lot of them now are.

And I read about another one, actually: WAL compression. If you turned it on a few years ago, for example, before it had more options, it will now pick Zstandard or LZ4, I think in that order, if they're available, instead of pglz, which it used to use unless you specified.

Nik [00:29:29]: It's one of the things the Forge system flagged: if it's available on the primary but not available on a replica, it can be a problem.

Michael [00:29:39]: Interesting.

Nik [00:29:40]: And there's another default which I like a lot. It takes a little bit of my consulting bread, because we always say "enable it", but now it's enabled by default: log_lock_waits.

Michael [00:30:00]: On by default now.

Nik [00:30:02]: Yeah. And this is definitely a good thing to have enabled by default, 100%.

Michael [00:30:11]: One more dev feature, and then I think we've got some performance improvements as well. The dev feature that I liked the look of was the WAIT command. WAIT FOR LSN. This is for reading your writes on replicas. If you've not got much lag, you can effectively just delay your read by maybe even just a few milliseconds, to let the replica sync, and then do the read query, instead of having to stick to the primary to serve those recently written—

Kirk [00:30:46]: Again, let me just add a comment for the dumber people like me about what this really is about. You start growing your Postgres footprint. You have your primary server, which can handle reads and writes. You get a handful or more of read replicas. They can only do read transactions. So you want to go to your read replicas to do your read-only queries. You write to the main server, and when your application goes to query the results back from the read-only replica, which you're doing for efficiency reasons, this new command actually says, "WAIT FOR LSN", and you give the LSN you just got back from the main, and it'll inject that pause there.

Now, you don't do this if there's a 13-minute wait between the stuff you're doing, obviously, right? But many systems have gotten this down to really fast, to the millisecond level. And this is beautiful, because the wait becomes more transparent to the developer. This is the kind of thing I really enjoy from a development standpoint. You did the right thing, and you're not punished for doing the right thing.

Nik [00:31:56]: That's interesting that you mentioned you're not doing this if the difference is huge. It tells me that it could be improved: saying I want to wait, but only if the difference is shorter. If it's bigger, I want, I don't know, an error immediately.

Kirk [00:32:17]: Well, think about it in a web UI. You just made an update, and the little patch on your screen that's supposed to reflect that update... I believe it works with a timeout. So you can actually have the timeout, from not getting it fast enough, put the spinning hourglass in that little widget on the web, because it hasn't got a time.

Nik [00:32:36]: I don't want a timeout, because a timeout would be a waste of time for me. I want to immediately switch to a different replica, for example. My problem is that this feature is already implemented by many load balancers. For example, if you look at GitLab's load balancer code, many, many years ago it was implemented based on LSNs by very smart people, so it's very smart and advanced.

And with this feature, if I do it inside one specific replica, and you have, for example, five replicas, I have concerns, because I want a better solution: observing all replicas and then deciding where to go. In some cases I see it can be beneficial, but probably this feature belongs not to SQL, but to libpq parameters, like we already have when you can specify multiple hosts in connection strings, right? And then you can say target_session_attrs and so on. Maybe it should be there, saying, "I want to choose a replica, the best one. Also, maybe I need to wait for this LSN."

But then it's a different architectural level for me, right? It's not inside one node. And—

Kirk [00:33:56]: Well, and then—

Nik [00:33:57]: I'm just not going to use it this time.

Kirk [00:33:59]: Well, again, I get more in touch with the application guys, right, and the applications. And what this just did was take away the hardest task of knowing if it's updated yet or not, and the guesswork. What happens is if you make something hard to use, people won't use it. Or worse, they'll come up with constructs that are probably worse, right? Where they remember the ID column coming back, or a change ID or something else, and then they're clearing it that way on the replica until they get it. But I do appreciate the change, and here's the deal. Other people implemented stuff a long time ago, and this can still sit on top of a director.

And when I speak of a director, that's somebody who's kind of like a PgBouncer to many different PgBouncer systems, right? So when you connect, it forwards your connection on, and it load balances across your various read replicas. So you don't know which one you're connecting to, and if one falls too far behind, that's the job of the director in my book, from an architecture standpoint. If one of the read replicas is really far behind and doesn't meet your normal criteria, stop directing traffic to it.

But anyways, I'm sorry I interrupted you pretty heavily, Michael, but I just wanted to make sure that people like me understood the nuance of that LSN and what it was really about.

Michael [00:35:25]: No, you did a great job, and I'm out of practice remembering to explain things first before diving into the detail. But one more on the performance side that we've discussed before, Nik, is the observer effect, or timing overhead, of EXPLAIN ANALYZE. I know that there's always tons of performance improvements in each Postgres version, and this is no different. There are lots of, I would say, smaller tweaks and query rewriting rules, but we've talked in the past about the timing overhead of EXPLAIN ANALYZE and also auto_explain with timing on.

And kudos to the people that worked on this, because I think it actually changes the calculus on having auto_explain on with timing on, either for all your queries, not logging all of them but analyzing all of them, or for some sample, like 10% of your queries, or 5% or 1%, something like that.

Nik [00:36:30]: Well, I also have one more improvement to mention, which was challenging. I remember many times during upgrades, after the upgrade you want to analyze your database. Now, with statistics being transferred from the old server to the new one, probably this feature is less important. But a few weeks ago we had an episode about what you should do after restoring from a dump, for example: you need your statistics to be created, and doing it with a single ANALYZE command.

In a large database it's very slow, so you want to parallelize it, and there is a good CLI tool called vacuumdb. So you can use vacuumdb with the analyze-only option to skip vacuuming. Although after restoring from a backup, as we discussed in detail, you want vacuum, right? But anyway, vacuumdb analyze-only, now with -j jobs, parallel mode, takes care of parent tables and partitions. So plans will be fine. Before that it wasn't the case, and it was not convenient at all.

Kirk [00:37:49]: So just to be clear, it wasn't processing the extra children tables in—

Nik [00:37:58]: No, no. Children were all processed, but the parent table doesn't have data itself, and we didn't have statistics for it, because once you specify the number of jobs, parallel workers go to the children and work, but then nobody updates the parents.

Kirk [00:38:14]: Nobody updated the main partition. Good.

Nik [00:38:18]: By the way, pg_dump now also takes care of restoring extended statistics. Statistics can now be dumped and restored, I guess, right? So this is also good stuff—

Michael [00:38:32]: Yeah. I didn't see that in the release notes. I was looking for it and thought that it happened, but I didn't see it in the release notes, so I was surprised it didn't make it, 'cause I think that now means that all statistics can be dumped out of Postgres.

Was that the only exception?

Nik [00:38:47]: I'm looking at—

Kirk [00:38:47]: You could have extension-based statistics or something that may not get carried. So I would hammer down on saying "all". Okay? I would stick with: regular and extended statistics are now copied over, and leave it at that.

Nik [00:39:01]: "pg_dump: include restorable extended statistics." If you can dump, you should be able to restore it.

Michael [00:39:09]: Makes sense. Nice one.

Nik [00:39:11]: There are some small things which just make your life difficult if you don't have them. Another thing is that in pgbench we have --continue-on-error. I wrote so many shell scripts to simulate this behavior, because sometimes, if there is some error, like max connections reached and so on, you still want to continue your workload, for experimentation purposes. Now it's just a CLI option, which is great to have.

Good.

Kirk [00:39:46]: Now that's the good news. There's some warnings though, right?

Have you seen some of the warnings it tells you about? What's going on with that? I'll throw that over to Michael.

Michael [00:39:58]: Yeah. There are a couple of parameters that have been removed, so that if you've got them specified in your config file, you won't be able to restart without them. Including, and this might be a surprise to some people, I think even a PG18 one. You know the I/O workers? In fact, we didn't talk about these at all. The kind of self-driving feature, Nik, the self-tuning feature of the I/O workers that I think Tomas Vondra was working on, that's had a parameter removed. I haven't checked if that's one of them, but basically there are some parameters that are no longer in Postgres, and if you specify them, it won't start.

Kirk [00:40:40]: And be careful, because you may have that set on, for example, a specific table. One of the great features of Postgres is you can override things on various objects, and if you use that on a specific object, you might not find it as fast. So that's a red flag.

Nik [00:41:00]: And pg_upgrade --check will report success, and then you just cannot start after the upgrade. That just means that, as usual, you need to test it thoroughly before going to production. For example, upgrading a clone separately, checking everything works, and only then proceeding to production, right? Testing on clones remains important. By the way, how we didn't touch any logical replication improvements, specifically replication of sequences?

Michael [00:41:31]: Sequences, yeah.

Nik [00:41:32]: Important, yeah. The only thing left among the big gaps is just DDL, right? Which—

Michael [00:41:39]: The only thing. That small thing.

Nik [00:41:42]: Just DDL in Postgres 20 and that's it. Please.

Kirk [00:41:47]: So just again for the slower listeners like me: you have either DML, data manipulation language, or DDL, data definition language. So there's only two things in there, and one of them's not supported, and it's partially because when you change DDL, it effectively creates DML that affects the tables that PG is running on. So you're actually changing the underlying structure. And it's complicated, right? So this is why it's gonna be a hot second before they get to DDL, but I tell you what, once they do, I think a lot of people are gonna be really happy. That's a nice hurdle to overcome.

Nik [00:42:33]: Good. We covered our favorite features. I think recommendations should change as well, because testing can be done more actively and earlier because of AI, right? So what do you think? How would testing of production systems look now, when we have AI and we can test thoroughly before actually switching production? Or just clone, right? You test all your application in a full clone environment, checking that everything works.

As usual, such environments should be detached from the world: not sending any emails, not making any API calls, right? Because if you test your application on an actual copy, it can be dangerous, but that's outside of the database. The plans will be the same and so on.

Michael [00:43:34]: Synthetic workloads are a bit easier to generate now.

Nik [00:43:38]: Of course. Yeah.

Michael [00:43:39]: I think one of the hardest things with these is that even if you test on a clone of production, if there's no traffic, you're gonna miss a whole category of blocking-related issues. Or sequences—

Nik [00:43:53]: That's a great point.

Michael [00:43:54]: Advancing, and that kind of thing.

Nik [00:43:55]: That's actually the whole idea behind DBLab Forge: now, understanding some telemetry from RCA, root cause analysis of a production problem, we can simulate it purely with synthetic data. We can reproduce this very problem and study it in detail in a non-production environment. This is actually the idea. So you're absolutely right about that. Now it becomes much easier, because a lot of small experiments are done in an automated fashion.

And plan flip analysis is an important thing, as we said, right? So I guess—

Kirk [00:44:33]: We have approaches—

Nik [00:44:35]: My recommendation usually was to wait until the .1, .2 version, but now I think the better recommendation is: don't wait. Start testing earlier, because most people have much more capacity thanks to AI, and why not, right? Start testing earlier and then jump to a new version sooner, with proper testing.

Kirk [00:45:00]: So our approach has been in the past that we pull the full data, we put it in a dev environment first, and then we run the process through a dev environment with a combo of usually a dev person and an IT person, who double-check everything, make sure everything passes, and follow up with some queries. Then that dev environment is actually put into development, and that person is recompiling what's going through Git and pushing it through on their machine, and then that's a predecessor to the IT department doing the automated script stuff that then gets run in staging. So we go from one dev, we automate it a bit, we push it into staging.

The other devs can still be on the old version in our book. And then after it goes through staging and it's there for a day or two, we have the other devs upgrade their environments to match this, and then we move it into the backup system first, because it does 80% of what production does, but more in a backup mode, right? Failure kind of stuff. And then we migrate it through the systems. That's been our approach. I don't see it changing, other than giving a first pass to the AIs to say, "Hey, just fire this up, restore it." Our problem is the restore: if we don't do it the right way, it's 72 hours, and it just takes forever, feeding up the data, feeding up everything else.

And I've yet to solve that problem. I need to work on that. Once I get that worked out, where it can be up and running faster, great.

Nik [00:46:37]: Since you manage Postgres yourself, you should use pg_createsubscriber these days, right? To convert a physical replica to a logical replica and then upgrade it. That's it.

Kirk [00:46:49]: Note to self.

Nik [00:46:50]: Yeah. This is how it should be done. And I'm very curious about plan flip analysis, because this is a big topic. We recently implemented in DBLab Engine the ability to upgrade clones by just pressing a button, and then you can start analyzing. You can have two clones and start analyzing plans.

Kirk [00:47:12]: Just having the dev be able to run it in their environment, Michael, it exercises enough of the code that if we had a plan flip... Our most used table is 200 million rows. Any plan flip on that table is immediately dissatisfied. It never hides, right? Go ahead.

Michael [00:47:34]: Well, I was gonna say, I think I understand why we're calling it plan flips, and I think that's a useful phrase. But it's performance degradation, right? That's the big issue, and I think that actually hints at some easier-to-catch solutions. I know Radim was working on this with his RegreSQL. I think regressions is a more interesting way of thinking about it than flips. 'Cause the plan shape might change, but if the performance hasn't changed, is that a big deal?

Nik [00:48:08]: Of course. Not all flips are bad. It can be an improvement, because the new version has a better plan. It can happen, definitely. "Regression" is a very good word here. A separate question here, Kirk: do you know how many query IDs you actually have?

Kirk [00:48:27]: So yeah, that's a great question. I don't know the exact number. I do know the following truth: we have about 40 queries that are 80 to 90% of our work.

Nik [00:48:39]: In terms of database execution time, or in general?

Kirk [00:48:44]: Number of executions and... Right? Because one of our key ones is a search.

Michael [00:48:50]: That actually does all the heavy work and takes the time.

Nik [00:48:53]: The problem is the tail can be really long, and something in the tail can flip so badly... so you need to analyze everything. And what I'm now thinking about: if you have pg_stat_statements... I know you don't have pg_stat_statements, by the way. It's not common. Most people have it. If you see pg_stat_statements.max at 5,000 by default, and you see all of them are filled, big question: what kind of iceberg hides behind those 5,000?

What is the drift, and so on?

Michael [00:49:29]: You can query for deallocations. I learned that recently. You can see how many times. Yeah, that's really cool.

Nik [00:49:35]: That's cool.

Michael [00:49:35]: But Nik, I had a question. When you said you have to look at everything, I suspect another thing to look at in the tail is calls. So a query in the tail that's very infrequent, even if it flipped, would not cause that bad an issue.

Nik [00:49:52]: It can. It can—

Michael [00:49:52]: But if it was huge... Okay. Yeah.

Nik [00:49:54]: It can be.

Michael [00:49:54]: But I'm thinking the bigger issues might be the ones with lots of calls, even if they're microseconds, and they flipped.

Nik [00:50:01]: Imagine a very simple example. Imagine you have statement_timeout 60, which is quite high in my book. It should be 30, 15 or 10. But let's have 60. You have a big machine, like 96 cores, and then you have a query which is just one QPS, right? And then it suddenly becomes so slow it reaches statement_timeout. So during one minute you will already have 60 active sessions busy with this work.

One QPS of work.

Michael [00:50:37]: Yeah. Good point.

Nik [00:50:38]: What if it was two QPS? You already have more than 100 active sessions, and you have only 96 cores. So something at the very end of the tail might hit you really hard. That's the problem. That's why we need to analyze everything, and if pg_stat_statements doesn't show everything, then your methodology shouldn't be based only on pg_stat_statements. You could look at it for some macro metrics, but to analyze everything... Anyway, that's why you should start earlier and give yourself a month or two to study the behavior of your database and its workload on Postgres 19.

Right? Don't do it in a rush, in one week. It should take longer.

Michael [00:51:28]: Yeah. I like that.

Nik [00:51:29]: Good? I think that's probably it. Good.

Michael [00:51:34]: Nice one.

Nik [00:51:36]: Thank you, Michael, for visiting us.

Michael [00:51:40]: Thank you guys for releasing in my absence. It's fun to be a listener. I get an extra Postgres podcast in my feed now.

Kirk [00:51:49]: And in honor of the fact that you're coming back, I wore the yellow shirt for you.

Nik [00:51:54]: Nice.

Kirk [00:51:54]: I'll wear the red shirt on the last day I'm with you guys. So—

I'm just keeping your seat warm.

Michael [00:52:03]: Yeah. I'll be back in January, so you've got a little while left.

Kirk [00:52:06]: Good.

Nik [00:52:06]: Happy New Year, Michael.

Michael [00:52:09]: Yeah. And Merry Christmas. Happy Thanksgiving.

Nik [00:52:11]: And Merry Christmas. Yes. Okay.

Kirk [00:52:16]: Bye.

Michael [00:52:16]: See you guys.

Some kind things our listeners have said