[00:00:00] So we have another release of PGRUST out this week and this one claims to be 300 times faster for data analytics compared to Postgres. Now, personally I find this fascinating watching the story of how this is done, but I still see some areas that I question. But I think in general having more projects pushing performance as far as possible is a good thing to have. The only question is, is there anything good you're giving up by doing that? But we'll take a look at that this week. But I hope you, your friends, family and co workers continue to do well. Our first piece of content is rebuilding Postgres for 300x faster analytics batching operator Fusion SimID or SIMD. This is from Malispur Me and he said last week we released version 0.2 of PGRust 10 times faster than the previous version. It's 30% faster on OLTB workloads than Postgres. I think he projected a couple of weeks ago it would be roughly 50% faster, so maybe they needed to add some additional stuff back in. But the biggest difference is 300 times faster for analytical workloads. And he says most of this performance difference is due to the query engine. So he's going to be talking about that.
[00:01:22] So basically they optimize PGRUST query engine to use less CPU and less memory bandwidth than Postgres when processing the same queries. Now, he has an example here where he shows a summarization query summarizing 500 million numbers that takes 20 seconds. So you're giving it SQL and it's coming out with the results in 20 seconds. But then he says for comparison, here's how you do it in rust, which is entirely apples to oranges. The apples to apples comparison would be what is the C code performance of this and what is the rust performance of this. Now he does say this example isn't an apples to apples, but is pretty apples to oranges. But he says this rust code takes 358 milliseconds, which is 55 times faster. So now he says that's an indication of all the overhead to process a SQL query and do those summarizations to get back the end result. So essentially that's the overhead they're looking to tackle. Now he goes over how Postgres actually does a sum aggregation.
[00:02:25] And he says Postgres uses a style of executor known as a volcano model. And here's a miniature implementation of it. And the key feature of the volcano model is the next method, which is to return a Single row. So while this executor is very simple, and he says this code is close to what Postgres does, it adds a lot of overhead. So basically his optimization is to add batching to it. So there's no batching in the volcano model. So here's his batching implementation. It looks like a size of a 1024 and that basically makes things three times faster. And after this optimization, the next hotspot is copyfromslice. And to address that, he's using operator fusion so they can create a single node that combines the logic of both the sequential scan and the sum. And he has an example of that here. Now he says this is a very specific implementation. And he says it seems like, you know, we're cheating because we're hard coding an optimization for a specific query, which you can do it if you know it in advance, but you never know what SQL query is going to hit. So his resolution to that is to use JIT compilation so you can generate the ideal code you wish you had and cheat in every query. And the final optimization is SMID or siimd to basically process multiple pieces of data in parallel. And here's the code change for that. Of course, the disadvantage of SMID is that you can't use it when operating on floats because they are not associative. So if the order varies, you're going to get different results with the output. So that's another example of when you throw something out for an optimization, what are you losing by throwing that thing out? So he says all these different optimizations makes the query 10 times faster.
[00:04:16] But these plus many more made up to the 300 times faster for analytical queries. But if you want to learn more, definitely check out this blog post.
[00:04:26] Next piece of content. Your count distinct is too slow. Approximations and sampling in Postgres this is from Snowflake.com and she's using an example of a 10 million row page view table. So it's tracking page views on a website and showing different ways to speed up approximations, like distinct counts. She shows how the rows are generated and says, you know, the problem is exact is expensive. Why? Because they essentially read every row.
[00:04:56] Now a quick way to do sampling, which might work for percentage counts or averages, is to use the table sample clause that returns a sample of the data. So for example, she's taking a 10% sample here and it runs in a third of the time, but it gives you a pretty identical average.
[00:05:15] Now you can take these samples quickly by doing it 10% of every page. Or you could do it a little bit slower, more accurate by doing 10% of every row using a Bernoulli option. Now while this works for averages or percentage counts, it kind of falls down when you're talking about distinct counts. So if you need fast distinct counts, you can use the hyperloglog extension. So it doesn't store every unique value, but it observes patterns in the values to estimate how many distinct values it's seeing. And you typically store it as a column on a roll up table alongside your regular columns.
[00:05:55] Shows an example of its usage here and how you would pre aggregate it in this example in a table, and you can even use them in window functions. The next area she covers is Apache Data Sketches. And again, this is another probabilistic data structure that summarizes a stream of data. So you can ask questions like how many distinct values there were, how many distinct values overlap, or what's the medium P99 for a histogram? Or what's the heaviest items by count?
[00:06:25] And she goes through and looks at all these different capabilities using data sketches. Now she says a lot of time what you're going to want to do is pre aggregate your data and then merge in changes. But having the system definitely allows you to save on hardware because it requires less processing to process a sampled set of data.
[00:06:44] And she covers ways to keep the rollups fresh by using materialized views or using PG incremental as well. So if you want to learn more about this definitely check out this blog post. Next piece of content related to the previous post is the distinct in your count. This is from boringsql.com and if you're trying to do a count of distinct users using this format, count distinct user ID from events for example, this is a slow way to get that data, but there is a faster way. Now the reason it's slow is because parallelism goes out the window when you use distinct he shows an example where you're just getting a count of events and you could see four parallel workers are working. But if you're using a distinct count there's no parallelism anywhere. So it's a single process doing all the work. But you can make it faster by turning it from a distinct into a group by so this form where you do a query select user ID from events group by the user ID and then do a count of that subquery lets it run in parallel because the group by you can do it in parallel and then of course the count can be parallel. So you can see four workers launched four planned and essentially this makes the result about four times faster. It went from 1.2 milliseconds down to 0.36 milliseconds. Oh, he says about 3.4 times faster. So definitely choose this option if you want to leverage parallelism to execute your query faster. And he says order bys can also hit the same restriction of avoiding parallelism because you need things in a particular order. Now he says per group distinct counts were a little bit difficult and the planner may or may not pick the parallel path. It depends, he says, on the cardinality and the costs. And the last thing he does mention is that you don't need to do this for everything. But when you have millions of rows you're trying to aggregate, choosing the group by option will give you a performance benefit over distinct. But check this out if you want to learn more and Next Piece of Content There was another episode of Postgres FM last week. This one was on Zeta. So Nick and Michael were joined by Tutor, the CTO of Zeta, and they talked about their essentially serverless platform, mostly focused on copy on write branching of databases very quickly being able to scale down the database sizes to zero sounds like a lot of what AI workloads are looking for. He also mentioned running CICD pipelines or pull request reviews and they talked about currently what they're doing and some of their history. So if you want to learn more, you can definitely listen to the episode here or watch the YouTube video down here.
[00:09:30] Next piece of content Massively parallel Postgres Backups this is from planetscale.com now I don't think people will be adopting this, but I thought this discussion was fascinating where PlanetScale is describing how they handle the backups of their sharded databases that they provide to customers and essentially every 12 hours they take a backup of every database in their system with essentially no impact to production queries.
[00:09:57] So this is what they do. But I think it's always informative to learn things about this at scale to see how you might implement something similar if you need to. Now they're using an example of their sharded Postgres solution, Nike. So they have a large database that has been sharded and what they do when they want to take a backup is they spin up a brand new set of EC2 instances and then they pull the previous backup from S3 so they reload the these temporary instances with the most recent backup and then they grab the wall files from S3 and I think they even pull some yes directly from the primaries as well to make sure everything's in sync and up to date. Once everything's in sync they stop the replication and then they do the copy down of the database in that state down to S3 and decommission these instances.
[00:10:49] And they are just using PGBasebackup to do the backups and presumably the restores as well and then applying the wall stream. And they did it this way for speed. Particularly when you have a scale out solution like this, the time it takes to copy data from S3 to the instance is actually not that bad.
[00:11:11] So they show an example of if you have one database with I think 40 terabytes, trying to back up just one instance with 40 terabytes given the transfer rate would take I think 40 hours if you break it up into eight shards and now it's 2.8 hours. But if you had 32 shards it would only take 42 minutes to do the backup. So there's a reason why this particular backup solution works so well when you have a sharded database. But if you want to learn more, definitely check out this blog post. Next piece of content Turning Claude into Postgres so I can raise a series A this is from byteofdev.com and he actually wanted to use Claude to actually store his data, just everything being LLM, which I can't fathom it being a non deterministic system, but he did it nonetheless. And of course it was slow as a dog, but it did actually return queries that had some accuracy. But he talks about setting it up here so check this out if you're interested. There was also a blog post at thebuild.com ontogeny recapitulates the rel cache where he discusses this particular initiative, which I thought was interesting. Next piece of content hacking workshop for September 2026 is coming up. This
[email protected] they're going to be discussing David Rowley's talk Optimizing Code in the Hot Path with examples from tuple deformation and if you're interested in attending, you can sign up using this form and the last piece of content. The videos for pgconf.dev 2026 are available at this link, so if you're interested in video content, definitely plenty here to check out.
[00:12:53] I hope you enjoyed this episode. Be sure to check out scalingpostgres.com where you can find links to all the content discussed 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.
