Episode Transcript
[00:00:00] I personally don't intend on trying it anytime soon, but I am super fascinated with the development of pgrust and the first blog post this week talks about how they kind of built the Gravity engine and some of their thinking behind it. But I hope you, your friends, family and co workers continue to do well.
[00:00:22] Our first piece of content is programming at light speed, building the Gravity query engine. This is from Malisper me and he's talking about PGRUST. Their 0.4 beta that was just released has a new query engine they're calling Gravity and they say it is three times faster than Clickhouse on Clickbench and it achieves that speed while supporting the full PostgreSQL dialect and type system. And he's talked about this previously, but they have been using batching operator Fusion and SSMID to make PGRUST hundreds of times faster than Postgres for analytical queries.
[00:01:00] So all of that plus the JIT compiler work basically made Gravity and they're considering this a query compiler that generates optimal machine code for any SQL query. And he says normally when engineers work to make something faster, they work what he's calling from the bottom up. So basically we have this performance. How can we make it faster? Can we make it 20% faster, 40% faster? Whatever the problem is, you don't know how far away the limits of your machine are.
[00:01:30] So when they built Gravity, they flipped that question around.
[00:01:34] They asked how fast could it actually be?
[00:01:37] So basically they started from the physical speed limit of their hardware and file format and worked backwards from there. And that's what he's calling programming at Lightspeed. So to program at Lightspeed he had Claude write a optimize Rust program against their columnar storage crates over a set of analytical queries. So there was no SQL executor. It just ran hand tuned multi threaded Rust loops with SMID directly over there on disk format. And this pedal to the metal as it were executed queries four times faster than existing analytical databases like clickhouse. So they said with this metric they want to write Gravity, their query engine to be within 20% of this rust code execution.
[00:02:24] And they developed Gravity as a multi stage compiler pipeline that progressively lowers a high level Postgres plan down to raw assembly.
[00:02:33] So it does eventually make the Postgres plan, then it goes into a pipeline graph and then a Lisp IR that gets compiled into assembly.
[00:02:43] Now this goes into a lot of detail I can't cover in the show, but definitely check this blog post out if you want all the details.
[00:02:50] They did say that as they were testing different queries, they noticed there were instances where they could not reach, you know, their lightspeed metric, and they basically found algorithms they needed to add to the planner specifically for their columnar format, which is why the stock Postgres planner couldn't support them. So they did add a zone walk, a zonetopk and a two pass.
[00:03:16] And the result is what was basically said in the headlines is that their execution runs as much as three times faster than Clickhouse. So definitely check out this blog post if you want to learn more.
[00:03:28] Next Piece of Content There was another episode of Postgres FM last week. This one was on Uuidv4 versus Uuidv7 and Nick and Kirk this week talked about the performance differences between the 2. So again, UUIDv4 is an entirely randomized identifier.
[00:03:47] UUIDv7 has basically a timestamp in the front, so you get ordered UUIDs.
[00:03:54] Now as they note here, there are various charts and illustrations that happen during the show, so there is an advantage to watching the shows to see the charts.
[00:04:05] Basically they tested UUIDv4UUIDv7 as well as BigInt or an 8 byte integer for a primary key. The integer was always fastest followed by Uuidv7 of course with regard to insert rates. So when they were testing inserts the bigint was the fastest, although not by much, followed by UUIDv7 and then UUIDv4 but it was still in the running with the other two until that index outgrew the memory so it was no longer memory resident.
[00:04:41] Then I kind of dropped like a rock because of all the random IO that's necessary to maintain a UUIDB4 index because it's entirely random. Therefore whenever you have a new entry to add to the index you're randomly writing across it, whereas with a time ordered uuidv7 they just keep landing in the right hand page of the index. And if you have a page split it just adds a new page and then keeps on writing. The other way it hurts you is with regard to wall because at the end of every checkpoint if a page was altered you're going to get a full page image rights into that wall. And if you have random IO all over the place you have to create many of these full page rights compared to using an incremental integer or a time ordered based UUIDB 7. But of course they talked about a lot more in the show. You can feel free to listen to the episode here or watch the YouTube video down here. Next piece of content Postgres logical replication beyond one writer this is from tpoueh.org and this is the fourth part of his series talking about logical replication uses.
[00:05:53] And here he's looking at maintaining essentially a multi master infrastructure as long as you design it right.
[00:06:02] So what do I mean by multimaster as long as you design it right? Well, the design is meant to cover cases where you have different regions, so each region will have one source of truth primary it can fail over, but you still only ever have one primary database in that region. So you have one in Sydney here, one in Mumbai, one in Amsterdam, one in New York, and one in San Francisco.
[00:06:28] So data in a given region gets stored with its region as its flag.
[00:06:34] So the only primary database out of all of these that can write San Francisco data is the primary in San Francisco. The only one that can write data in the Sydney region is the primary in Sydney. So a primary can only write to one region, but it replicates that data across so it can read from any region.
[00:06:55] So if you have that use case, you could use this technique.
[00:06:59] So each table has for its primary key region and then an event. So for example, in this events table here, the primary key is a region, and then the event ID and this event ID could be generated through a sequence of that one primary or even a uuid. And it's not going to conflict with any other region because the region's built into the primary key. And by using row filters for the logical replication, they can explicitly say what data a publication is allowed to publish. And they use replication origin tracking to make sure that you avoid a replication loop. So if you have this use case, this could definitely be beneficial. And again, there's a lot more in this blog post than I can cover, but check that out if you want to learn more.
[00:07:44] Next piece of content. Getting rid of a 4 terabyte table in PostgreSQL this is from boringsql.com and he's talking about a situation where they have a 4 terabyte table and 10 replicas.
[00:07:56] Now they want to delete this table, but they don't want to adversely impact the primary nor these 10 replicas. And what's the best way to do this? So he basically did a bunch of experiments to check out how to do this. Again, another very long blog post. But in terms of his ultimate recommendations, and as to if the table has any foreign keys, go ahead and drop them first, one per migration using a lock timeout. Of course, the second recommendation is rename the table before you delete it. So basically this ensures nothing's touching it, there's no queries running against it, and basically validate that to make sure nothing is contacting this table still. And he does recommend turning on logrecovery conflict wait so you can monitor the replicas and again drop it in the quietest hour with a short locked timeout and a retry loop if you think that's necessary. But if you want to learn more, definitely check out this blog post. Next Piece of Content Multigres Private Alpha Automatic failover for supabase Postgres this is from supabase.com and they announced oraldb entering a public beta last week. This week they're now announcing a private alpha of multigrad. So not as far along but you can start I guess kicking the tires as it were. So again, this is their scale out solution that was spearheaded by the individual who did the test for MySQL so it's a scale out solution. So basically you get a three node Postgres cluster spread across three availability zones in one region, handles automatic failover, has built in connection pooling, one standard Postgres string you can use to connect to the cluster. And of course they say the Supabase infrastructure should work with a multigres solution. So if you're interested in this scale out solution, definitely check it out. Next Piece of Content do we still have to denormalize? Have your schema and search it too. This is from paradedb.com and he's talking about a situation where let's say you want to search across multiple tables in your relational database. Users posts comments and this example is from Stack Overflow. For example, you want to look for a particular string in any of these tables in particular columns. Well the natural way you would do it in SQL is join those tables together, search for the particular string in each table using an or, and then order by particular range and give it a limit. But this is dog slow in Postgres and it can even be slow in Parade db. So they've put some effort into optimizing it and they're calling these search joins. Now I should say there is a way to optimize this by using unions instead. So converting this into three unions and joining the results together. They do show that further below, but they have optimized it even further from that and they discussed some of the modifications they've made with their engine and they show the results here.
[00:11:03] So this is the natural SQL above with the or so that's in gray and you can tell no matter whether the search result was rare, medium or common, it was still pretty much the same speed. If you convert that query into a Union all version, you can get over 100 times faster in a rare situation and then not quite as fast as the occurrence of that search result goes to a median or a common search as high as 14 seconds for the common, so maybe about nine times faster than the natural search. But their engine optimization drops it even tenfold more in the rare instance running in 100 milliseconds compared to 1.2 for the union all optimized or the over 100 seconds for the natural SQL and as slow as 600 milliseconds for a medium frequency search result about 5 times faster or 5 to 6 times faster than the Union all version. And then they looked at joining and grouping into facets to get aggregates and here from the union rewrite, it's only about twice as fast, but infinitely better than just using ors in the where clause. But if you want to learn more, definitely check out this blog post.
[00:12:17] Next piece of content the hidden latency inside your database this is from planetscale.com and this is basically talking about the advantages of bare metal because you know you want your app to talk to your database as fast as possible and if you have it in different availability zones, there's a latency to that.
[00:12:38] If you can bring them into the same availability zone, that interaction is so much faster because it doesn't have to cross availability zones. Well, the same thing holds for your database connecting to your disk. So if you're running an EC2 instance talking to EBs, you have relatively slow network storage when the database needs to talk to it. You can make it so much faster by using local storage in that database. Now if you're planning to do this, you're definitely going to want to have redundancy there. PlanetScale Bare Metal does use three instances across three availability zones, so if you have a primary failure, the entire cluster isn't brought down. So one of the replicas gets promoted to a primary.
[00:13:23] But you do have to be aware that they have it configured so that at least one replica needs to acknowledge a commit before the primary sends the acknowledgement to the client. So that does reintroduce some latency, but it's just at the commit point as opposed to every single write that would happen when you're using network based storage. So that means if you use a solution like this, you want to batch your data changes as much as possible and minimize the number of commits but to get the advantage of this stuff, you also need to be doing frequent disk accesses. If most of your database stays in memory, you're not going to see much of an advantage of using local disks on the instance because it's mostly just reading from memory. But go ahead and check out this blog post if you're interested. Next piece of content the 3D's of software distribution distribution distribution this is from pgdog.devo and they have taken an interesting direction in terms of running their company and supporting their product, where they have it built into their CI that they push new Docker images out to their public repository directly to the users and you get binaries for x86, ARM64 and Apple Silicon. They even have a Debian package. Now their Enterprise Edition is even more interesting because they made it publicly available so you can download it directly from their Docker repository.
[00:14:51] So they have an open source helm chart for you to deploy it into your Kube cluster. He says quote no auth, no contact form, no credit card required, you can just go ahead and use it. And apparently over 5 million downloads have happened for their PGDog Enterprise Edition and 323,000 for their Control plane. So basically I'm assuming they only get paid if you ask for their assistance. So basically he's distributing all of the code as widely as he can.
[00:15:18] I'm sorry I forgot to mention it, but basically pgdog is the sharding pooler that Lev Kokotov is working on. But check this out if you want to learn more. Next piece of content is PostgreSQL really winning? This is from Stoker PostgreSQL and he said the November 2026 DB engine rankings are out and Postgres is the only one that's still growing, although it doesn't seem as sharp. But all the others are declining, so clearly that's advantageous. And he says be aware that the DB Engine's survey measures popularity, not necessarily install base or revenue. And he says, why are people leaving?
[00:15:57] Well of course it is having to pay for the database, so that's one aspect of money. But it can also be licensing pain. All the cloud providers offer managed Postgres.
[00:16:07] You don't have a vendor lock in, it's good enough for most work and often better talent and momentum. So a lot of developers are already familiar with PostgreSQL and AI workloads because PGvector made PostgreSQL a reasonable default for Vector Search. But if you want to learn more about this, definitely check out this blog post and last piece of content. Cloud Native PG Reaches CNCF Incubation Another rung on the ladder. This is from gabrielebartulini.it and becoming an incubation project means there's a lot more resources and opportunity available to the Cloud Native PG operator for Postgres. So definitely seems like this operator is continuing to advance and dare I say, become the default that most people start with. At least for Postgres and kubernetes. You can tell the star history continues to climb all throughout 2026 as well. So if you want to learn more about that, definitely check out this blog post. 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.
