pgrust makes Postgres 300x faster for analytics with batching, operator fusion, and SIMD
Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD
The release of pgrust 0.2 brings a 10x performance boost over its previous version, making it 30% faster than Postgres on OLTP workloads and 300x faster on ClickBench, even surpassing ClickHouse. The query engine alone accounts for a 10x improvement. This article explains the key optimizations—batching, operator fusion, and SIMD—that transform a simple sum query from 20 seconds in Postgres to 135ms in pgrust, using a miniature query engine to illustrate each step.
The query engine is the main user of CPU in a database. We optimized the pgrust query engine to use less CPU and less memory bandwidth than Postgres when processing the same queries.
- malisper
Author here. Let me know if you have any questions about the post or about pgrust.
Let me take a shot at answering what I think will be the most common question: how can I trust pgrust? Our #1 priority right now is correctness. Over the past two weeks, I've done a mix of formal verification and differential fuzz testing. We've been able to prove over 1000 user facing functions have the exact same logic in both pgrust and postgres (see the proofs directory if you're curious). For cases where formal verification is not easy, we've taken the c implementation of a function and the rust implementation of a function and ran millions of inputs through each of them and confirmed they gave the same results every time.
We've only covered about 15% of the surface area so far, but in the process, we've discovered ~100 bugs in pgrust and ~20 bugs in Postgres itself. My favorite postgres bug we found is this one[0]. Postgres has a quadtree implementation. Due to floating point rounding, it was possible for a point to be neither above, nor below, nor even with the center point of the quadtree.
We've also entered engagements with Antithesis[1] to do Jepsen style fault testing and Aretta[2] to do more serious formal verification.
If you want to support the project, the easiest way is to give us a star on GitHub[3]
[0] https://www.postgresql.org/message-id/19597-39c532e61d78dff6...
- sgt
Cool project but .. reality is that people will generally not choose pgrust over Postgres, even 5-10 years from now. The problem is not that it may be technically superior and faster by then, it's that it's not built by the trusted Postgres team. There's a lot more to trust than development velocity or performance. It's also about the longevity and continuity of a critical piece of technology.
- AsyncBanana
You have no idea how long I have been waiting for adaptive planning. One of my biggest annoyances with the Postgres core team has been their reluctance to implement any sort of adaptive planning despite it, at this point, being a well-established technique that has been implemented in multiple production databases. I hope this, at the very least, proves the viability of this model outside of academic/niche contexts.
- rastignack
I would be interested about a more detailed architecture overview of the io scheduler (like this: https://www.scylladb.com/2021/04/06/scyllas-new-io-scheduler...) and the thread scheduler.
PostgreSQL has historically been bad at managing the noisy neighbor problem, but with thread pools, and io priorities, it can be solved.
Has this been tackled here ?
- kopirgan
Can some of these optimization get back propagated to Postgres?
- 3dedb728-3f77
So one trick you can do is make a ramfs/tmpfs and start Postgres on it.
You need a server with enough ram to fit it all.
But it kind of make the database fly.
- ZiiS
Surly AI could also write a clearer headline. For the millions running it in production for decades, using a great echosystem of help support, books, consultants, and managed hosting providers; the is a noteworthy difference between the official release and a partially compatable rewrite.
- patkepa
Question, does having it in pure rust, opens possibility of embedding pgrust directly into binary, making it an alternative to SQLite/turso?