Episode Transcript
[00:00:01] Well, it looks like the reversion train keeps on going.
[00:00:05] We have a number of other features that have been reverted from Postgres 19 and we'll have to see actually when Postgres 19 gets released, but I hope you, your friends, family and co workers continue to do well. Our first piece of content, what is happening with Postgres 19? This is from Snowflake.com and they're talking about the recent feature reversions or reverts and we covered this last week but there are even more this week. So last week we talked about the SQL property graph queries being reverted, the alter table merge split partitions but now we have update delete for portion of which is a feature for temporal range columns group by all was reverted because there were some special handling that needed to be taken care of. I think this is a big blow. The default toast compression change to LZ4 has been reverted because it was missing some support in the build farm non text output formats for PGDUMP all JSON table onerror cascading fast default for domains with non volatile constraints Logical replication database specific snapshots and this appears related to Repack.
[00:01:20] Now she says Repack is still in version 19, but it's limited to a single process that can run concurrently. Nested query tracking that was in PGStats statements is gone and online data checksums is gone as well. So that's about 11 different features that have been dropped. So that's pretty crazy. There's another blog post that also discusses this 19th nervous breakdown. This is from thebuild.com and there is a fourth beta plan for September 24th. Hoped to have general release by the end of October. And this blog post compares to what the 18 development cycle looked like against Postgres 19. So identical feature freeze on April 8th in both years and for both versions beta 1 for 19 was about a month later than 18. They had the same beta 3, but now we're introducing a beta 4 and which there was no beta 4 for Postgres 18 and the general release of Postgres 18 was September 25th. So that's when 19 is having their beta 4 essentially. And it also says in this blog post that RI fast path batching was also removed. And I mentioned last week that a lot of this may be due to all the CVEs that they're having to work on because LLMs are finding all of these bugs. So it sounds like according to this blog post what is basically happening is that people are using LLMs or AI coding assistants to review their own work and saying, oh, we got to fix these things before we release them.
[00:02:55] So it's helping them find bugs in their current code before it actually gets released, which is a good thing. But that also causes delays in these features actually getting into Postgres. So he says here, quote Eisentraut, Langot, Korokatov and Davis pulled their own work. So in light of this different workflow where the LLMs are allowing people to review their own work and correct potential problems in the future, he's wondering if we need to adjust timing of different things like the feature freeze and everything else. So he says, quote, in his opinion, run the scary patch contest in May, not in August to allow more time to judge what's actually going to make it into the next version of Postgres or not. But definitely check out this blog post if you want additional insight into it. And then we also have a Post from command prompt.com, postgreSQL19 September 8th to the 16th Talking about the different reverts again. So they're covering what's going on as well. Next piece of content the unbearable lightness of one more index the this is from boringsql.com and he's interestingly talking about LLMs generating indexes for schemas and how they tend to add a lot more indexes than a DBA typically would. And as a consequence a lot of times based upon the recommendations, there are no more hot updates happening. So basically you have great read performance, but then you've now hurt your write performance. You're generating more wall and also leads to probably autovacuum taking a little bit longer on the index cleanup phase. And he actually simulates trying to create indexes. He created six fictional SAS products for review and looked at the number of indexes that were created by the agents and how that impacts index size, the level of the amount of wall written, and even vacuum times.
[00:04:49] So he's looking at an example of a table with 15 indexes and it has almost twice the amount of wall Double the latency, 1.6 times the on disk footprint, and an extra 65% time on vacuum when doing an update. Heavy workload because it can cause these numerous issues. But check this out if you want to learn more. And next piece of content the real cost of a single insert in Postgres and this blog post walks over how much write amplification happens when you do a single insert. It's basically from wall files and index changes, etc. And he uses an example of 40 bytes in, but it actually generates 340 bytes out and he shows three components of the write amplification. One is the heap tuple, next is index entries and then of course the wall how to measure the write amplification using PG CurrentWall, LSN and PGStatWall as well as ways to bring it down in terms of batching your writes with cloud copy drop indexes that are not necessary.
[00:05:52] Turn on wall compression and raise max wall size and checkpoint timeout so you're not checkpointing as frequently.
[00:05:59] But check this out if you want to learn more. Next piece of content PGRUST version 0.3 the bug smashing release has been released. Apparently this is from PGRUST.com and they have a corpus of 164,000 test cases and the divergences of those cases from how Postgres acts went from 1454 instances in version 0.2 to only 11 in 0.3.
[00:06:27] So that is significant. But they still say, you know, pgrust is not ready for production because there's still bugs, but they're still working on it, but you can go ahead and try it out as I see here quote in a real workload in a non critical environment and if you notice something that behaves counter to how it should, then please file an issue with it. Next piece of content Introducing Nike. This is from planetscale.com and Nikki. Their sharding solution at PlanetScale is now in a platform preview, so they're not saying it's ready for production yet, but you can go ahead and start testing it out. And in terms of what is Nike? Nike is sharded Postgres from PlanetScale and it lets you scale a Postgres database across many machines. So basically your application talks to a Nike router that's compatible with the Postgres wire protocol and it's responsible for routing the query and returning the data and putting it back together to deliver to the client. And they said everything is basically vanilla Postgres Quote There is no custom storage engine, so extensions, SQL support, performance all behave the way Postgres does. You use a shard key to determine how tables are grouped and distributed through the topology, and you can run Nike as a single primary with replicas and once you outgrow a single machine then you can start sharding your database. And they actually have a sidecar included along with every Postgres instance to handle the connection pool component, so it doesn't look like they're actually using PgBouncer for this. So Nike controls both both ends of the connection, the router side and the Postgres side. And of course they have a control plane to track the health of every node. And they also have a data topology to map your logical tables onto the physical shards. They also have another blog post, 118 million queries per second on Nike.
[00:08:24] So they ran 512 shards. So I guess that's over 1500 database servers running that could run at 118 million queries per second with 1.2 petabytes of data. And at that level they had.
[00:08:39] Oh actually 1500 machines is wrong because they were only using one primary for this test.
[00:08:45] So 512 machines, single primary on an 16x large at Amazon and they had 480 nici routers on 8x large instances. And they did say this was a read only workload so there weren't really updates or inserts going on. But still, 118 million queries per second. That's pretty high. So check that out if you're interested. And a third blog post from PlanetScale is the lifecycle of a sharded Postgres query.
[00:09:14] This is a long blog post and they do have these cool animations that show how their sharding system works. So if you want to learn more about how it works under the covers, definitely check out this blog post because this is only halfway through it. I'd have to scroll a lot more for you to see everything. Next piece of Content There was another episode of Postgres FM last week. This one was on Hot Standby Feedback, so if you want to learn more about that, definitely listen to the episode here or watch the YouTube video down here.
[00:09:46] Next piece of content Tom Lane on the architectural decisions that shaped 30 years of Postgres. This is from Snowflake.com and Elizabeth Garrett Christensen actually interviewed him and there's a separate link to the video of that here that I've included as a link as well. So this was super cool. Definitely encourage you to check it out. And I like one of the first comments down here. You know Tom Lane, the legend himself, so definitely check that out if you're interested. I hope you enjoyed this 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.
