Episode Transcript
[00:00:00] I had heard about pgrust maybe a couple of months ago and I heard someone was rewriting Postgres in Rust and I probably had a little bit of an eye roll with regards to that and I thought it was maybe just a developer being a developer and everything is better in Rust or whatever language you happen to like at the time. But because I'm talking about this as the lead to the show, it might be something to start to pay attention to because it just crossed passing 100% of the Postgres regression tests and you can start it up on your existing Postgres directory.
[00:00:36] So we'll talk about that this week, but I hope you, your friends, family and co workers continue to do well. Our first piece of content is the Four Horsemen behind thousands of Postgres outages. This is from malicebird Me and and of course this is a clickbaity title, but these are some of the things he sees and suggests he wants to change or fix in pgrust. And again, this is a Rust rewrite of Postgres. So the first thing he addresses is vacuum and transaction ID wraparound. And transaction ID wrap around is a definite concern as your database grows because we're still using 32 bit transaction IDs and I can only assume he's going to be using 64 bit and his intent is to change how rollbacks happen and use an undo log like Oracle does, probably to avoid having to vacuum the heap. But both of these are goals, they are not present yet, presumably because he has to get the regression tests passing. So he's just trying to match Postgres at this point. These are just future looking things as far as I understand it.
[00:01:45] The next is connection limits and query parallelism and he's basically knocking the Postgres process model, although he makes some of these statements that I'm like that doesn't happen because he says quote if more things try to connect to Postgres than the connection limit, they won't be able to. That's true, and your database will go down. I've never seen a database go down due to too many connections. The database stops additional people from connecting, but basically he's going to be using a thread based model and he says this was architected from day one with it. And he says even though the process model is safer, Rust provides safety guarantees for these threads, so it should be fine. The next item he calls out is bad query plans. Now again with pgplannadvice this is not as relevant too much anymore, but again this is all Forward facing. That he wants to do with PGrust is he wants to build an adaptive query planner and that if a Query normally takes 10 milliseconds, suddenly takes 10 seconds, PGRUST would detect the regression, check if the plan changed, or check if the statistics drifted and make a correction. Although in theory you could make this enhancement to Postgres, today it should be tracking sufficient information to detect plan flips. I'm not saying it would be easy, but I could see how that feature could be added. Next he talks about JSON and the fact it does not collect statistics, and that's something he wants to do. But again, Postgres could start tracking some sort of statistics for JSON as well. So this gives you a sense of what he's going for. The next post is PGRust passes 100% of the Postgres regression tests, and that's with Postgres 18.3. And it's disk compatible with Postgres, so you can boot from an existing Postgres data directory, but of course it's not production ready yet, not optimized, not compatible with existing Postgres extensions, but still, this is a significant milestone nonetheless. And then the next blog post, how PGRust was built 4 attempts to rewrite Postgres and Rust with AI now, this is a much longer blog post, but it talks about his technique for using AI to do it. And in terms of the TLDR, after four attempts and dropping $100,000, we succeeded in using Opus to rewrite Postgres and Rust.
[00:04:08] So they had four iterations to try to get it and spend $100,000 at the end. But you'll see they've actually added another hundred thousand dollars near the end of this blog post. Now, of course, the issue with using AI for building these is are you going to, I guess, quote unquote, paint yourself into a corner? Is it going to have too much bloat or slop or whatever you want to call the code to be able to move forward? And it's interesting that he says for this first attempt it got completion, but ended up having fundamental issues that made it unsalvageable, which philosophically any software can be altered and improved. So I think it's a pretty significant statement to call it unsalvageable. And they said this attempt was mostly port one feature at a time, and it goes into details about that.
[00:04:57] The second attempt was to basically transpile the Postgres code from C to Rust, so basically matching it up line by line with the Postgres code. But the problem with doing that, it basically generated 5.3 million lines of unsafe rust code. Now, what's interesting about this attempt, it completely passed the Postgres test suite and passed 100% of the regression suite and had similar performance to Postgres, but it was even ABI compatible with Postgres, which means the extensions would work as well.
[00:05:29] The problem was when they tried to convert the code into idiomatic rust.
[00:05:34] That's where things fell apart.
[00:05:37] And it was taking hours to do this type of conversion. So the idea for the next attempt was to rewrite the generated C2 rust code crate by crate, because there were approximately a thousand rust crates that was initially output. And he goes through over the skills and some of what he prompted to try and do that, but he had a lot of problems with token usage. And he makes an interesting comment here. Now, I may be a little crazy or maybe very crazy, but I thought if I could complete PGrust for under $100,000, it would be worth it. Now, the problem is in C, the circular dependencies looks to be what might have killed him with this, because rust crates forbid the creation of circular dependencies. And then going back to one of the previous iterations being unsalvageable, he here he says, quote, when Fable came out, he said, all right, let me try to get Fable to resolve this. And he says, even Fable couldn't recover this version that he's calling PGRUST Idiomatic.
[00:06:37] Is there any way to save PGRUST Idiomatic? Does it just make sense to start over again? And he had spent a lot on these Claude Dynamic workflows, up to $50,000 at this point. So the fourth attempt, he called PGRUST Fabled, but the spoiler was it was not built by Fable because Fable was released. And then Anthropic did a rug pull, and you could only have Opus. So that's what he did. I kept going with Opus, and he talks about his different skills and how he approached this a little bit differently. And he prioritized implementing seams between the crates and updated his audit skill to give it more things to look for.
[00:07:18] So he regularly ran this audit skill and was consistently making sure all the seams were wired.
[00:07:23] But ultimately he got this version. PGRUST Fabled, although written in Opus, was able to pass 100% of the Postgres regression suite and using idiomatic rust at the cost of another $50,000. So $100,000 in total.
[00:07:39] Now, then he says, okay, what's next? He says quote, the big issue with pgrust fabled is it's really slow, eight times slower than Postgres.
[00:07:49] So they're doing the next version presumably based on this code called PGRUST Fast. And this is crazy to me. So far they've put in $200,000 into PGRUST fast on top of the hundred thousand dollars they put into Rust Idiomatic. So it's using a new Vectorize executor, a new JIT compiler that cuts the latency of jit compilation from 50 milliseconds to 5 microseconds, and a scheduler that dynamically adjusts the prioritization of queries. Now this is still an internal test, but he says this altogether the optimizations we've made have made PGrust fast, over 50% faster than Postgres on transactional workloads and about as fast as Clickhouse for analytical ones.
[00:08:37] If this is true, that's pretty amazing and they plan on releasing this within the next two weeks.
[00:08:44] And this blog post was released today as I'm recording this, so it definitely looks like something to keep an eye on. But what do you think? Feel free to let me know in the comments.
[00:08:56] Next Piece of Content Philosophy behind PG Hard Storage this is from cybertech PostgreSQL and again this is the PG backup tool that Cybertek keeps on talking about now, but basically they just wanted another alternative in the community. They're not saying you should drop your existing tool, but they were looking for particular feature set and that's why they chose to build their own. And I'm sure the PG backrest issues that had happened earlier prompted them to continue doing it. But some of their focus has been they wanted to make it cloud native by default so basically everything can work over a LibPQ connection so it can work with managed Postgres as well as running your own bare metal servers. They wanted to use streaming replication as a first class citizen so no archive command is required, it just uses the replication slot. It's patrony aware from the start so it can follow leader switches with zero gap. So I believe they have customers who are using patrony so this was a requirement for them. They also are offering drop in compatibility shim. So if you're migrating from PG Backrest or Barman they give you a path to move to their solution. And every feature of the backup is Apache 2.0 license and as a reminder they are offering five different key management service backends, five different storage backends you could use as well as some other things we've discussed. Some things it does not have yet. It doesn't have a built in scheduler, although you could easily use CRON or your own scheduler. It doesn't have a web ui, it's all CLI at this point. It doesn't have a snapshot aware fast path yet, but that's on the roadmap for version 1.1. And it looks like they may support plugins with the Tier 2 plugin registry. So feel free to check this out if you want to learn more. The next one architecture behind PG hard storage the replication protocol. And this goes into more detail about how they're just using a single replication slot and the replication protocol to do all of your backups of the wall and even the base backup and they list the consequences from that one. Managed Postgres works automatically. You don't need an archive command, you don't need SSH access to the host, etc. Consequence number two, the privilege model is dead simple. You just need to be able to connect to the database with a replication role and be able to read all data and of course execute the relevant functions.
[00:11:26] Back pressure is the slide. The replication slot handles that if the agent goes down, it starts accumulating wall until the agent comes back up.
[00:11:34] Fourth consequence, you get dual stream wall essentially for free. So you can set up wall streaming from both the primary and a fast following replica where they're calling the lowest lag replica because it's doing chunk de dupes. It's not like you're having to retain everything twice. You can have two or more sources of the wall but only one copy gets ultimately stored.
[00:11:57] And the fifth consequence is failover is a reconnect. So if patrony has a failover, it just reconnects up to the new primary. Now in terms of trade offs, you do have network egress on the source side because all the data needs to get out through the agent. There is one replication slot per agent, so you need to have one free. And if the agent has a long outage you're going to be accumulating wal and there's no archive command. Double archive on managed Postgres. Basically the streaming path is the only path if you're using managed Postgres.
[00:12:30] But if you want to learn more, definitely check this out. Next piece of content. Listen carefully how notify can trip up your database. This is from virus.org and this is Jimmy Angelico's presentation that he gave at Poset talking about lists and notify and how if your traffic ramps up, it does create a global lock on the PG database system view which can slow down everything and as he describes it here, how the internal serialization of notifications triggers access exclusive lock cascades on PG database and they fixed it using unlog queue tables and transaction level advisory locks. So you can watch the YouTube video of that here and look at the slides down here. Next piece of content Also from Jimmy Angelakos is LinkedIn fixing bad SQL and PostgreSQL with Jimmy Angelakos.
[00:13:23] And this is basically from his book PostgreSQL Mistakes and How to Avoid Them. And you can look at the video of it here.
[00:13:31] Next piece of content Our SQL PGQ rewrites to joins on PostgreSQL19 this is from CyProtechKevinPoseQL.com and this goes over the detail about how graph queries get rewritten to SQL in the planner, particularly with regard to indexes. So if you want to learn more about that, you can definitely check out this blog post. Next piece of content how much do you really need to know about databases? This is from Karen Jecks.com and this is from a presentation she gave at EuroPython in 2026. And it's actually not so much about what developers or software engineers need to know, but it looks like if you wanted to become a dba, kind of all the different areas you would have to know and be familiar with. So if you're interested in that, you can check out this presentation and next piece of content. The test passed the plan didn't. This is from Boring SQL.com and he's talking about the tool he released, RegressQL, that does regression testing of your SQL to make sure that the results of your queries still return them what they're supposed to. But there is still a gap with that. If an index gets dropped, well, you still get the right answer, but it's so much slower.
[00:14:46] So they've just released a version 2 that also tests for plan differences.
[00:14:52] So he says test the plan, not just the rows. So if you're interested in that, definitely check out this blog post and the last piece of content making 768 servers look like one. This is from Planetscale.com and what they're talking about is sharding. And I know we've talked a lot about pgdog and multigres recently. Well, there's also netkey, which planetscale has made for Postgres. They do use vitess for MySQL but they have developed Netke or Neki for Postgres, so they have their own sharding solution as well that you can check out and learn more. I hope you enjoyed this episode. Be sure to check out scalingpostgres.com where you can find links to all the content mentioned, as well as sign up to receive weekly notifications of each episode.
[00:15:42] There you can find an audio version of the show as well as a full transcript. Thanks. I'll see you next week.
