Episode Transcript
[00:00:00] We seem to be on this kick where one company posts a benchmark of performance and then another company posts another benchmark that shows they're not as fast as their solution. Or yes, they're fast, but they're not as efficient as our solution. And as I said last week, I think this is great for us as consumers and users of Postgres, so we'll see where things go. But I hope you, your friends, family and co workers continue to do well. Our first piece of content is open Source. Citus matches PlanetScale's Nikki at 10 million queries per second on half the data nodes. This is from statgres IO and looking at the graph here, they achieved 10 million queries per second, which is what Planetscale's Nikki did on 50 nodes. And statgres open source implementation of Citus where they're using a query router pattern that they developed to achieve 10 million queries per second on 24 data nodes. So less machines.
[00:01:02] And the blog post starts off, you know, last month PlanetScale published a blog post on how they achieved 118 million queries per second on Nike, but they included Data points at 5 data nodes, 50 data nodes and 512 shards.
[00:01:17] So basically they attempted to match their 50 node test and you'll see why in a second. And this is a follow on blog post from the one we covered last week where they talked about their query routers they developed to work with Citus. And as opposed to sending all traffic through the Citus coordinator node, you send them through query routers and you get close to linear scalability of your query Traffic. And with three query nodes and three data nodes, I believe they got up to 1.4 million queries per second. And so they went ahead and they matched the 1 million and 10 million data points. And I'm assuming that is the 5 and 50 data nodes that they used.
[00:01:58] Now this wasn't doing writes, it was just doing reads. And there were a single row primary key reads to match what Nikki was doing. Presumably there were primaries only, which I remember that's what Nikki did, no replicas.
[00:02:11] So they basically tried to match that.
[00:02:14] But here's the interesting thing. The hardware they used R8GD 16x large, whereas Nike used R8G 16x large. You notice the D is missing. That means the one that Strakgres used was using local NVMe disks instead of network storage. But I thought PlanetScale was known for their bare metals, so it's interesting that they used these. So does the disk subsystem have anything to do with it?
[00:02:41] I don't know. So for this test, it looks like they have three worker nodes, which is basically where the data is stored sharded. And their query routers, they used six. And with that they were able to achieve 1.4 million transaction per second.
[00:02:58] And then they just scaled that up, they doubled it, quadrupled it, and then 8 folded it. And basically they were able to get up to 10 million transactions per second from almost 20,000 clients sending traffic to it.
[00:03:13] And their scaling efficiency was around an 87%. So it's not fully linear. That's this light purple line. So it does drop as you're scaling, but it's pretty good. It's also interesting how the P50 was still at 1 millisecond for these queries in spite of doing 10 million transactions per second. So that's pretty impressive, which is what they say here, which is really impressive. And they say the latency that they're showing is end to end, measured from the client's perspective. So that's great.
[00:03:44] And at the 1.4 million mark, while they were using about half the database CPUs, they actually exceeded the transactions per second that Nikki did. So in terms of a ratio metric of TPS per data node, they were more than twice as efficient. And with the 50 data nodes or 50 shards at the 10 million transactions per second, they were still a little bit more than twice as efficient as Nike. And they charted some of the performance numbers down here as well. Now, they tried to do a price calculation. It was a little hard to do because Nike doesn't publish a cost right now, but they projected around five times cheaper to run their stackgres solution using Citus than using Nikki, at least right now. Now, the reason they only went to 10 million queries per second is because it costs a lot of money to do these tests and just to do essentially the data. In this blog post it said they spent around $6,000 to achieve these results.
[00:04:45] But could they achieve 118 million queries per second? Well, they said PlanetScale CEO said on Hacker News that their cost for running that test was a quarter of a million dollars. So $250,000.
[00:04:59] So that's pretty crazy. Now the question is, will Planetscale now up the ante, make improvements to Nike and then show them performing better than this Citus Stack Res solution, just like they did for paradedb.
[00:05:14] We'll have to see next piece of content related to this is it happens to be that Alvaro Hernandez from Statgres joined Nick and Michael on Postgres FM last week where they talked about their Situs and Stack Grez solution. They talked about both blog posts, the one I covered last week and the one I covered this week, comparing them to Nike. And he said the reason he started investigating working with Situs is because it is a pretty stable product at this point. It's been around for 10 years. Most recently in the last few years it's gone open source. So it is an open source product that people can use, although I think there's still some licensing restrictions, so you definitely have to check on that. And he's saying on their Stackgres and Citus solution they're running a 2.2 petabyte database using only 24 nodes.
[00:06:06] Now during the call, Nick did mention something that all of these benchmarks that are coming out, they're just queries, they're not showing write traffic. And really the reason why you basically want to shard something is that you need more write performance. Basically a single writer is insufficient, so you need to scale out to multiple writers. That's one of the main reasons to do sharding, because if it's just a query load you could just add more read replicas. So all the benchmarks thus far for Nike and the Stack Res Citus combo are all covering read only queries. So it would be great to get some write based benchmarks as well. But of course a lot of this discussion does cover those two blog posts, but they definitely add some nuance to the discussion. So if you want to, you can definitely listen to the episode up here or watch the YouTube video down here.
[00:06:54] Next piece of content is PostgreSQL. Just got a GPU. Introducing CyberTech PGEE GPU. This is from CyberTech PostgreSQL and if you don't want to shard out for performance, maybe you want to evaluate this solution. Now my understanding is that this is not open source, but Cybertech has engineered the Query Planner to use a GPU instead of a CPU to do query processing. And my understanding is that the Planner decides do I send it to the GPU or do I send it to the regular CPU to bring back the results? And he shows an example of a 500 million retail order database he worked with on one CPU core. The query took 30 seconds. If that query was done across a 20 core machine, it took 3 seconds. And they said they even tried more cores but it basically didn't improve performance at all. But sending it to one GPU got the results in 14.7 milliseconds. And he says 31 seconds compared to the blink of an eye. And then they said they used eight GPUs against a billion row order table and, and a Query that took 159 milliseconds on the CPU took 2.8 milliseconds on the GPUs, which is a factor of 57,000 times faster. And apparently they tested something where they got 2.1 trillion rows every second. I'm assuming that means transactions or queries, but they don't specify.
[00:08:26] So they're basically leveraging the massive parallelism that's possible with GPUs or the CUDA cores. But he adds this quote. You know, a GPU isn't simply more cores, it brings its own memory and vastly more of it per second.
[00:08:42] A single current generation card carries around 26 times the memory bandwidth of a mainstream server processor and roughly 20,000 parallel lanes to consume it when you Compare it to 128 CPU cores. And they said they've done a lot of tests to validate that the same answer is being returned whether you send it to the GPU versus the cpu. So basically something like this might turn analytics on its head. And they do have an additional product page here that you can learn more about it. But I thought this was fascinating. I can't wait to hear more. Next piece of content.
[00:09:20] PGRUST version 0.4 Beta Core Gravity columnar indexes Automatic memory and developing in the open this is from Malaspur Me. This is the Rust rewrite of Postgres. They've just released 0.4 beta and they basically have said they're starting to push the ceiling on what a modern database engine can do.
[00:09:43] So they introduced the Gravity query engine, which they state is as much as three times faster than Clickhouse. So basically they rewrote their JIT compiler from the ground up and it supports x86, arm and wasm. And it says this new engine supports all Postgres queries and types natively. And it works on top of the traditional PostgreSQL row store as well as presumably they're a column store. So basically this PGR columnar adds full MCC support, so you get full transactions on columnar tables. It says it also introduces a new columnar index. So this is a columnar index you can apply directly on top of a row store table.
[00:10:27] So basically that index now gets columnar level analytics speed without forcing you to convert your table. You can do fast insertions, deletions, and updates from the columnar store and it supports all the traditional indexes. So he says, quote every standard Postgres index type btreegen just now works out of the box on PGR columnar table, so it is a separate table type. They're also adding automatic memory management in terms of dynamically adjusting workman and as well as automatically doing buffer cache sizing. And the last thing is adding native JSON B statistics as well. So statistics being tracked deep within the JSON B structure. And they also say they're developing out in the open now, unlike previously, but they still say, you know, PGrest is still experimental and not ready to be a primary database. But if you want to use as a replica and try to send queries to it, you can try it out. Next piece of content I don't trust on deletecascade this is from planetscale.com and this is a case where you specify foreign key and on a delete you want to cascade those deletes, but you can run into a delete chain that can basically make your performance really suffer and that you can have a cascading delete. Then you have a migration going on that can cause an outage. It shows what visually happens here because of the locks that have to happen as those deletes get cascaded. Now, of course you should always use a lock timeout on your migrations, but that would have mitigated this issue, so that migration would have been halted and you could try it again. But it can cause terrible performance if you happen to have an unindexed foreign key. And it also causes a lot of performance problems if you're dealing with logical replication as well. So he's basically arguing that, you know, using soft deletes is the better alternative than doing on delete cascade from a performance perspective, because you really avoid a lot of the long transactions. But check this out if you want to learn more. Next piece of content how Save Points quietly throttled our Postgres queue and whenever I look at this save points, I'm remembering historical blog posts how save points have caused a lot of problems. And if you're dealing with a Postgres queue, okay, you've probably got a lot of inserts, deletes, or updates going on at the same time. Yeah, I can imagine there are problems. Now, they didn't hit a particular problem, but they were actually testing performance. So they wanted to see how far they could push their system.
[00:12:55] And once they started scaling things up, they noticed an increase of lightweight locks due to multiple transactions locking the same row and basically it seemed to be the major cause were the save points because they were holding this transaction open for so long.
[00:13:10] So they decided to get rid of the save points, although looking at this, they didn't get rid of the save points.
[00:13:17] So basically as opposed to having a parent transaction that then used save points for each of the child transactions that were happening, they actually just processed one child transaction at a time, although it looks like they were still using save points. But because it wasn't a very long transaction they broke up the size, it was able to make it more efficient. So they removed sub transactions from one part of the ticker that they were working with. So they say the multi transaction weights didn't disappear completely as a weight class, but they never came back to dominating the time in the database even under the heaviest load. So basically they reduced the number of sub transactions with this solution that they came up with. But if you want to learn more, definitely check this out. Next piece of content Postgres Recovery Prefetch Considered harmful and causing outages this is from ardentperf.com so this particular issue seems to appear when the primary instant does a large sequential IO. So it's something that happens on the replica, and recovery prefetch converts those operations into 8 kilobyte random reads. But this can amplify IO per second as much as 16 times on the replicas, which exhausts the IOPS and causes replicas to significantly lag after writer activity. And this makes it worse if you're using synchronous replication because that essentially brings the whole system down because it has to wait for those changes to be applied and they're not being applied due to all the iops that's happening on the replicas. And he shows an example of this in action.
[00:14:49] So basically, if you have a large sequential I O and you're using synchronous replication, you may want to consider disabling recovery prefetch. But if you want to learn more, definitely check out this blog post and the last piece of content. There was another episode of the Talking Postgres podcast. This one is from Oracle DBA to Postgres contributor with Karen Jecks. So if you want to learn more about her work, you can listen to the podcast or look at the YouTube video down here. 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.
