Episode Transcript
[00:00:00] There was a saying coined that software is eating the world, but is it the case that it's actually open source software eating the world? The first article this week talks about Postgres, kind of from that perspective, but I hope you, your friends, family and co workers continue to do well.
[00:00:20] The first piece of content is the MySQL shadow. This is from momgm us.
[00:00:26] This is very short, but I felt it was very insightful and had me questioning a lot of things about what companies are going to be here tomorrow and what the future looks like. But he says for much of Postgres history it has lived in the shadow of other relational systems or maybe even in the shadow of NoSQL systems, but those shadows have faded. So in particularly he's talking about MySQL because when Postgres started coming up, there was Oracle, Microsoft SQL Server, MySQL but you know, Postgres keeps rising, whereas these others are either stagnant or declining.
[00:01:05] And one quote, he says here few people would choose Oracle's database today, so it is effectively in legacy mode. And is Microsoft in the same position maybe with their SQL server product? But he says the MySQL shadow is a bit more complex and it's not proprietary, it is open source.
[00:01:24] But he says, quote, users are pleading for Oracle to improve MySQL that is, Oracle has promised improvement, but even then they had some layoffs recently at MySQL run by Oracle. And he says even MariaDB has struggled. And he believes the reason for this decline is because one single company was controlling MySQL early on. So that gave them a lot of forward momentum early on.
[00:01:50] But he says, however, this system couldn't last because the fundamental impediments of company controlled development would eventually slow it down. Now, some would argue that an individual company can move much faster than the open source ecosystem, and that is true, but it does require a lot of capital to do that. And part of me thinks, has Postgres reached a tipping point where it would be so much harder to try to catch up to it? But he follows on, saying things like company controlled development just can't compete with open source communities. And I will say once those open source communities have reached a certain threshold, like Postgres is clearly there and of course Linux is clearly there and I would foresee that taking over the operating system area as well. And he says most companies can't sustain the spending, which is probably why Microsoft ab, the original company that started, was eventually sold to sun and then even Oracle. Now of course Once companies can't compete, they try to control it. And he says, quote for Postgres, many companies have tried to control it either via a fork and run or fork and follow.
[00:03:01] And he says, quote, the community is just too large, too strong and too dynamic. But I thought this was a very insightful post. Highly encourage you to check it out. It is quite short as you can see here, but it also gets me thinking about what about the other players in the Postgres ecosystem? How are they surviving and what's their long term outlook? I don't have a particular opinion, it's just something I think about. You know, I'm thinking about supabase, timescale, neon, etc. All these other companies. But definitely encourage you to check this out.
[00:03:34] Next piece of content There was another episode of Postgres FM last week that this one was on Plan Flips and it's unfortunate that they release on Friday and I tend to record on Thursday because this would have gone well with the PGPlanNadvice that was mentioned by Robert Haas and his blog that might be coming to Postgres 19.
[00:03:55] They did this show about plan flips. The Postgres Planner, as a result of a statistics update from Analyze running, actually changes a plan to a worse condition. So maybe the statistics you're collecting is not a sufficient resolution and you actually get a bad plan.
[00:04:15] So the plan flips to a worse one. And they were talking about it because a company called Clerk had a system outage due to it. So they discussed that. They also discussed, hey, are planned flips good or bad? Because the Postgres Planner is designed to be able to change its plan based upon new information.
[00:04:35] So if the data suddenly changes, most of the time it's advantageous to change the plan based upon the new layout of the data or you add a new index that can now allow a different plan to be created.
[00:04:48] So most of the time these are good things. But unfortunately there are some cases I would say more on the rare side where you get a bad flip to something that was working and then now it's not. And Nick was mentioning things about where people might have random page cost set to 4 and then based upon some new data coming into the system, you're now doing a sequential scan as opposed to an index scan.
[00:05:12] That would be a bad plan flip. But they talked a little bit about the outage and they said there's so much more information they would want to know. Like do they have strict timeouts on statement? Timeout, transaction timeout? What is their auto vacuum settings, how often are the stats stats being updated and then what's the resolution at which they're collecting those statistics? What's their default statistics target? Do they have certain ones and certain tables? And then they talked about actually having express hints. So they did talk about pghint plan.
[00:05:42] I don't think they were aware of the pgplant advice that Robert Haas his post that he released. So I don't remember explicitly mentioning that. They do cover it by way of the pganalyze blog post down here, but they mentioned how Aurora does pin its plans, so I think that's kind of how pgplannadvice is looking to approach it as well. But if you want to learn more, definitely encourage you to listen to the episode up here or watch the YouTube video down here.
[00:06:08] Next piece of content Migrating Argos From Heroku to AWS this is from Argoci.com and they actually did this move in 2024 for a variety of reasons. They had limited flexibility and upgrade options and the performance and cost. They felt they could do better on RDs.
[00:06:29] They also mentioned declining product and support that happened in 2026. So this really wasn't a reason why they moved. But the reason I thought I'd mention this is that because Heroku has basically stopped sales, it seems like it's on the brink of becoming end of life and will there be more people trying to migrate off of Heroku on onto some other platform or hosted solutions? But they have a plan of how they approached it, basically using logical replication to transfer the database from Heroku to rds. So if you're interested in that you can check out this blog post.
[00:07:06] Next piece of content workmem it's a trap. This is from mydbanotebook.org and this blog post was interesting. However, I have a lot more questions than what was revealed here. It basically created more questions than answers for me. But the story is there was a production cluster that had just been killed by the out of memory killer after eating 2 terabytes of RAM. So apparently this instance had 2 terabytes of RAM and the postgres processes used it all up even while workmem was set to 2 megabytes.
[00:07:39] So something was using that ton of memory. Now to help diagnose it. They did use PGLOG backend memory contexts. So I guess you give it the backend ID here and it shows you basically all the memory contexts for it. So they looked at one back end and they were seeing 557 megabytes for a single back end on a reproduction server. So, of course, the question I had is how many connections were running and how many connections were running the problem query that caused this. But half a gigabyte for memory for a single back end is quite large, especially when the WorkMem is only 2 megabytes. And under the area here, every DBA knows about WorkMem, and that is, workmem is not per query, but it's the amount of memory that can be used for each hash or sort operation. So one query can have many of them. And if you add parallel workers, the number, as she says here, multiplies fast.
[00:08:38] And the thing to keep in mind is that with memory, it is only released at the end of the whole operation, not during.
[00:08:46] So even though it's using this workmem for the hash operations or sorts, any memory used during that whole query execution does not get released until the query is completely finished. So what the problem query was is a select that called appeal PGSQL function that internally performed a copy operation and then joined the result with something else. So I immediately read that, and I think what, I understand what it's doing, but maybe it's a bit too much to do in a single statement or unit of work. Now, she did say that the 524,000 chunks is a problem.
[00:09:26] I mean, to my mind, I think that mostly means there's probably a lot of fragmentation going on. But that wasn't mentioned. But she says, quote, each of those 524,000 chunks used up to work mem worth of memory. But I thought chunks were a subset of a page, a memory page, which is in a kilobyte range. So I don't quite know how it could use up to megabytes worth of memory. So I don't really understand this quote. Anyway, so basically that prompts more questions from me. And then she ends like, how can you prevent this? And she says, fix your statistics. I was like, okay, I didn't say anything about the statistics. So was part of the problem with statistics being off with the query they were trying to do? Another thing she mentions is fix the query. A query that eats 2 terabytes of RAM is a bad query. I'm assuming she means in aggregate, it eats 2 terabytes of memory because the backend was half a gigabyte.
[00:10:24] Next, use query timeouts. And she says, basically, since you can't cap the memory, at least you can kill things that run for too long. So doing statement timeout transaction timeouts, absolutely. That's Definitely important. And then she says don't forget pglog backend memory context when you're dealing with memory problems. But if you want to learn more, definitely check out this blog post. Next piece of content Validating the shape of your JSON data this is from enterprisedb.com and they're talking about a new extension called PG Schema Validate. So this basically lets you define what a particular schema structure should work for a given column. So when you're creating your table you can define say a JSON B column and say do a check that the JSON schema is valid and here is the structure that valid JSON object. So this is super interesting. So now you could do JSON validation in the database as opposed to doing it in the application. And he says it does string validation like min length, max length, particular pattern and format. You can also do numeric validation with mins, max exclusive min exclusive max and multiple love.
[00:11:41] It does array validation of items with a min and a max unique items contains, min contains, max contains, and even object validation as well as schema composition.
[00:11:55] So this is super interesting and he did say there is another schema validation tool called pgjson Schema that is rust based done by Supabase and he did some benchmarks between both of them and it looks like his version is at least 44 times faster, up to as much as 73 times faster. But you know there are caveats with any performance testing study so he does list some of that here. But if you want to learn more, definitely check this out. Next piece of content Using Patrony to build a highly available Postgres cluster part 1 etcd this is from pgedge.com and Sean did a post last week I really liked about Patrony and this blog post is actually the first in the series where he's going to set up a patrony cluster.
[00:12:41] The first series here is going to be on Etcd. Then he'll do one on Postgres and Patrony and then one on Haproxy.
[00:12:48] So I'm definitely going to be watching out for these and if you're interested you can check them out as well.
[00:12:54] Next piece of content this is actually a new extension I hadn't heard of, but I saw one of the blog posts so I'm linking to the blog here. There is a new extension called pgducklake for creating a lakehouse in postgres. Now it's not actually creating data within the postgres heap, it's storing essentially the schema or data definitions in the heap Tables, but the actual data is stored in parquet files. Because when you're talking about ducks, you're talking about DuckDB, and that is analytics and that's column storage, and you're probably doing it in parquet files. So the way that this tool is used is that you can access and query your parquet files from within postgres, but the actual data files are stored in parquet files, typically in S3.
[00:13:42] So PGDuck Lake works hand in hand with PGDuckDB, which is the engine to do the querying. And they've just released a new extension called pgduckpipe to be able to do real time CDC of the data into the lakehouse. But check out these blog posts if you want to learn more. Next piece of content Production query plans without production data. This is from boringsql.com and he's talking about the new functions in Postgres 18 that allows you to restore statistics into a database after an upgrade using PGRestorerelation stats and PGRestore attributestats. Well, you can actually use these as long as the means to export them using PGDUMP statistics only to test queries of a production nature in a smaller test environment. So basically you're taking the statistics that exist in your production database, moving them over to a smaller environment so that you can test out the queries, hence running production query plans without the production data.
[00:14:44] So he goes over the process to set this up and describes it. So check this out if you want to learn more.
[00:14:51] Next piece of content how moving one word can speed up a query 10 to 50 times. This is from Postgres AI. They have an example query here where you have post tags that are being searched. You want to make sure not to include any posts that have been deleted. So this is one way to write the query where it's looking for where the posts aren't deleted. This is another way to write the query where instead you're finding the posts that have been deleted and just look for the tags where that post does not exist. In this set of deleted posts and the added information, there is a partial index on the post table where it's not deleted and a partial index where it is deleted. Now, because of that, most of the data is not deleted. So the smaller index would be the one that is checking the post where it's deleted.
[00:15:38] So this will run super fast because it's a smaller index and because you're looking for data where it doesn't exist, it also doesn't have to go to the heap to find data.
[00:15:48] So therefore this is the faster version, but they go over this in detail, show the example and show the different performance runs and the different improvement. So you can check this out if you want to learn more.
[00:16:01] And the last piece of content from Proposal to PR how to contribute to the new Cloud Native PG Extensions project. This is from gabrielebartolini.it and he's talking about utilizing the new Kubernetes image volume feature and the new extension control path parameter in Postgres 18 to create trusted immutable extension images. Now he says they have already converted pgaudit and pgvector as well as postgis. They wanted to actually document how to do this for another extension. For migrating it over they used pgcrash, which is basically a chaos monkey, and he goes through the process of setting that up. So if you're interested in that, definitely check out this blog post. I hope you enjoyed that episode. Be sure to check out scalingpostgrads.com where you can find links to all the content mentioned, as well as sign up to receive weekly notifications of each episode There. You can also find an audio version of the show as well as a full transcript. Thanks. I'll see you next week.
