Episode Transcript
[00:00:00] Part of me wishes that Postgres 19 was released already so we wouldn't have all these blog posts and discussions about why Postgres 19 is late and is AI to blame or not to blame. But unfortunately I found the most interesting post talking about the state of 19 and actually focusing on some features that are coming as well. But hopefully in a couple of weeks the show starts will be a little different.
[00:00:27] But I hope you, your friends, family and coworkers continue to do well. Our first piece of content Are we reverting patches because of bugs found by AI? This is from Vondra Me and the first thing he mentions is that, you know, there was this blog post by Elizabeth Christensen that was saying basically AI is helping find bugs that's causing these features to get reverted. Although he presents some data that shows, of course, a more nuanced view than that. So he talks about the different patches reverted from Postgres 19 and he talks about the reasons why they were reverted, as well as a conclusion on whether it was AI or not. And he goes through a lot of detail with regard to each one. A lot of them are not AI, although I'm sure AI still probably had some role in the work given the state of things nowadays.
[00:01:22] And ultimately I don't think it matters whether AI was the reason or not necessarily, but I think he represents a pretty good interpretation of what happened. So we did look at reverts in the past. So we looked at PG14 through PG19 releases and when things tended to be reverted. So this is a revert count on the left here. Now what you can see is the slope of the PG19 quite sharp. So there were, it looks like up to 10 reverts that happened in a matter of 10 to 15 days.
[00:01:56] There are some sharp slopes in some other releases, but they were earlier. So the significance of this is that it is a pretty steep slope and how late it happened. So basically people had already assumed features would probably get in at this time.
[00:02:12] And in this early phase, when other releases had sharp increases in the number of reverts, pg 19 was relatively stable during this time. And they said day 275, which is around here, is when the feature freeze happens for each of the versions. So not much happened after feature freeze. And then he looks at this other chart of the cumulative revert changes with regard to insertions and deletions, and you can see PG19 is definitely off the chart.
[00:02:41] So my interpretation is this is lines of code. So at least the magnitude of PG 19 has been significant. It seems more than twice as much as any previous release, as well as a lot of the increase happened rather late. Now he ends with a question, is it related to AI? But from that same post from Elizabeth Christensen, he points out the August patch for Postgres had 28 CVEs, so this is probably the primary reason that there was relatively low activity at the feature freeze mark compared to other releases, because people were probably busy reviewing and implementing security patches that the work that normally got completed here got shifted late.
[00:03:32] So this data suggests that AI may have had a hand in in the feature development for Postgres 19, but it may actually be the CVEs that caused this delay as to when all of the reversions happened and potentially their magnitude, because maybe more features would have been able to reach a stable state and not be reverted if committers didn't have to focus on so many CVEs that were happening.
[00:03:57] But if you want to learn more, definitely check out this blog post.
[00:04:00] Next Piece of Content why Open Source Always Wins this is from pgdog.dev and pgdog is a sharding pooler. It is an open source project.
[00:04:12] So this blog post basically is advocating for open source and why it's a good thing. I thought it was a pretty good read. But the ultimate question is okay, if you're doing open source, how do you make money? And he says free in terms of free and open source means free as in freedom to use it and to make your own choices. But it's not that they're giving everything away for free because he says, quote, it costs a lot of money to build and even more money to operate the code. Because he says if you deploy it, you own it. So yes, it's an open source project, you can choose to take it and install it, but at that point of installation you now own the responsibility of whatever that code is doing.
[00:04:52] And he says, quote, you can create a GitHub issue on an open source project and hope it gets resolved by a generous stranger, but at the end of the day you are on the hook to make sure that it does. So for that reason, you know they offer the Enterprise Edition licenses and they have a 15 minute SLA basically to make sure this software works in your environment for you. But if you want to learn more, definitely encourage you to check this out.
[00:05:18] Next piece of content what repack concurrently costs while it runs this is from boringsql.com so this is a feature coming in 19 repack and repack concurrently assuming it does not get reverted. And he put it through its paces, comparing it to vacuum full, which entirely blocks the table for reads and writes, as well as the current PGrepack extension and PG Squeeze. And PG Squeeze, its implementation is basically what the new repack and repack concurrently follow. So the implementation does use logical decoding to keep track of changes. And it's using a replication slot to basically have a snapshot for when it started.
[00:06:01] It copies the whole table over and then it replays all the wall that's been maintained to get the table in sync with the old table.
[00:06:11] And at the very end when it's swapping things back, it needs an access exclusive lock on that table. It's very brief, but of course this can impact reads and writes during that final phase. And out of this comparison between the three other methods of resizing the table, repack concurrently came out pretty far ahead. Shockingly even faster than vacuum full in terms of speed. The wall generated was in line with PG squeeze because they have the same type of implementation. But the peek extra disk was kept rather small too compared to PGfreeze because apparently it releases the wall files earlier.
[00:06:48] And it looks like this was done on a roughly 18 gigabyte table. And he also did a repack operation when 3,000 single row updates were hitting per second. And this extended the duration of the repack by about 20 seconds or so and also increase the amount of wall generated. So he says something to be aware of is that vacuum is held back during these repack operations, so you're going to have dead tuples accumulate. But it looks like because it is using a logical replication slot, it can impact other databases and other tables when holding vacuum. So basically you want to keep activity to a minimum on the database as a whole while you're running a repack operation on a particular table.
[00:07:31] He talks about the swap that happens at the end in terms of cutting over to the new table. And that access exclusive lock and repack only tries once.
[00:07:41] So if you want to protect yourself and implement a lock timeout, that's going to just stop the whole repack process. It doesn't do any retries that apparently PGrepack does. So that's something to keep in mind. And this I don't think has been mentioned enough. But repack concurrently is not in vcc.
[00:08:02] So if a repeatable read transaction is started and then you start the repack, if you try to access that table in that repeatable read transaction snapshot, you'll basically see zero records after the repack swap has happened. So again, just need to be cautious about long running transactions when doing these repack operations.
[00:08:23] Apparently there's also a memory and a hard ceiling on concurrent number of changes.
[00:08:28] So this is updates and deletes that are happening during the repack operation. And he was looking at an overall memory limit of the database I suppose here and at different millions of changes they would hit that limit and have the back end kill by out of memory errors. Now he said he didn't hit a limit on an 8 gigabytes of memory, but it has a hard fixed limit at around 100 million changes. So he says, quote repack concurrently can't finish if more than 105 million rows are updated or deleted during the run. So that's definitely something that should be emphasized. And he talks about long BG repack runs as well and he gives some general guidance before you run it what you should keep track of before you're running a repack operation.
[00:09:18] But overall he thinks it is the best option to use for, you know, most of us. So definitely if you start using repack concurrently when Postgres 19 is released, definitely reference this blog post so you can make sure to handle these different considerations that can come up. And I did include a link to the Postgres docs where it has a big warning here with regard to SQL repack where repack with the concurrently option is not MVCC safe and they have a link to talk about some caveats with regard to that. So again need to be careful of what transactions are running when you're running repack concurrently.
[00:09:55] Next piece of content Query Plan hints in PostgreSQL 19, a new Postgres feature you will probably never need. This is from Snowflake.com and they are talking about basically PGPlan advice coming to 19 and it's a way to do query hints. But she says, you know, quote you will probably never need hints because the planner is usually right. It's pretty good and it keeps getting better with each version and this should continue to happen in the future we hope. But she says, you know, before you're going to use pgplannadvice definitely run an analyze because maybe your statistics are out of date. Or use create statistics because maybe you have some column correlations that need to be specified so the planner can make the best plan with regard to it.
[00:10:41] Maybe you need to add or adjust indexes or even check your work memory and memory settings which can constrain different planner options. But she goes through and shows an example of using pgplannadvice this where it outputs a plan that you can then suggest a particular plan for a query ID and the configuration of the database. So if you want to learn more about check this out.
[00:11:06] Next piece of content A hint of dependence this is from thegresqlpost.org and actually I like the subtitle a little better why Execution Instructions do not belong in SQL and what the Planner Needs Instead so as you can tell, this is again about hints and whether hints should be in SQL and he's showing a post where someone said basically the SQL standard should have advanced hinting and Vic fearing, which I've seen his name somewhere else, but he actually sits on the SQL Standard committee.
[00:11:41] And I would say this is a paper that basically expresses his belief that the SQL standard should not contain hints and it's a matter of configuration or implementation and shouldn't be in the query syntax because what you're doing is you're leaking the implementation into the SQL, which is not where it's supposed to live. That query language is supposed to be independent of the implementation and a lot of times you get a bad plan because because of missing statistics. For example, he does review different Hint systems in MySQL and Oracle and other database systems, as well as why Postgres has resisted implementing them for so long. I don't like the different quotes from Robert Haas and Tom Lane here. Robert Haas says with regard to hints, we just want them to actually work and not suck, with Tom Lane saying I haven't seen a hinting scheme that didn't suck and that even includes aspects of Postgres behavior that's hint like. But I don't say that there can't be one, so maybe there's an alternative.
[00:12:44] So again, They've settled on PGPlanNadvice, which is basically a configuration that looks for particular query types and allows you to essentially lock a plan or change a different plan for how that query is going to run.
[00:12:58] And he says this is very similar to Oracle's SQL Plan Management and SQL Server's Query Store.
[00:13:05] Now he covers a lot more information in this post. I don't have time to cover everything, but he also talks about giving the Planner as much information as you can with regard to statistics, keeping them up to date and configuring your database so it collects those statistics of the data frequently, as well as give it additional information like correlation between columns as well. But if you want to learn more, definitely check out this blog post.
[00:13:30] Next Piece of Content There was another episode of Postgres FM last week. This one was on Google Summer of Code B Tree Merge and in this episode Nick and Michael were joined by Salma El Said and Kirk Wolak and they discussed their project to add B Tree Merge to Postgres. And this is basically trying to do space optimization for indexes because as data gets updated and deleted particularly the indexes can become quite fractured, fragmented and they went over the process of working on this particular feature. So if you want to learn more you can listen to the episode here or watch the YouTube video down here. Next Piece of Content alter function set workmem Fixing PostgreSQL disk spills without a global change this is from cortexridge.com they are talking about an issue where they hadn't deployed their app in a while, but there was a sudden change in Postgres that was spilling 150 gigabytes of of data a day to a disk. So apparently this was a plan change that happened. Either that or their number of products increased such that the previous workman was insufficient. So the queries were spelling to disk and using temp files. Now they could have increased workman globally and frequently. I have increased workman based upon a particular user.
[00:14:56] You can also set it on a particular session, but this particular one they set up for a specific function, so they altered a function and set it to use a specific amount of workmem. So I haven't actually seen that before. But after doing that now suddenly they essentially got no temporary file rights as a result of this change.
[00:15:16] So definitely something to keep in mind you can do if that comes up. And the last piece of content PGQ and PGQ workflow engines you might need in PostgreSQL this is from percona.com and we talked about these previously. But these are Q engines you can use in Postgres. They don't use your typical 4 update skip locked. It doesn't cause massive vacuum problems. It uses a different implementation by tracking different snapshots to see when transactions completed during a particular point in time. And there's a single append log but it can have multiple consumers of it. And we did talk about this in a previous episode of Scaling Postgres but this blog post goes over these different queuing systems and how they work as well as of course showing you the chart of how you can avoid bloat issues like for example when your X Men horizon block all these other implementations definitely bloat, whereas PGQ and PGQ did not.
[00:16:18] And PGQ is the original implementation I think from Skype from years ago, whereas PGQ is the more recent one done by Nick at Postgres FM and Postgres AI, but check this out if you want to learn more. I hope you enjoyed this episode. Be sure to check out scalingpostgrows.com where you can find links to all the content mentioned, as well as sign up to receive weekly notifications of each episode.
[00:16:43] There. You can also find an audio version of the show as well as a full transcript. Thanks. I'll see you next week.
