Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

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...

[1] https://antithesis.com/

[2] https://aretta.ai/

[3] https://github.com/malisper/pgrust

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.

I don’t understand. It’s not like we don’t already have faster alternatives, don’t we have things like k and kdb that are many orders of magnitude faster even than this?

We use Postgres because it’s fantastic at the scale and problems it solve, if you have an extremely critical system where raw speed is at the core of everything and you’re dealing with petascale then maybe you’re bringing a solution you like to a problem it doesn’t fit?

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.

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 ?

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.

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.

Can some of these optimization get back propagated to Postgres?

The project has 2 commits.

2.

Commit #1's message is "hey claude, do a breakthrough" from a week ago and is 1.5M lines. Commit #2 is "blog post" from 4 days ago.

My head is spinning. I don't mind AI stuff or AI enabled stuff but there's gotta be some bar for ending up on HN, and also personal accountability: the lack of humility and honesty sets a new low for me. There is no "we" who "released pgrust 0.2". It's one person cosplaying a serious engineering team doing a mountain of work. The bus factor is 1, and its one you can't trust on the basics.

ex. the first 1/3 of the blog post is bloviating about how a rust for loop is faster at summing 500M numbers on the heap than loading the numbers from a table and summing them.

It leaves me in quite some anguish. This site kept me well-informed and growing for 16 years. It is no longer reliable for that as long as things like this can be the #1 post with 60 comments, with the author here, and no one mentioned any of this.

I am very disappointed to see the direction: It is moving from a "interesting attempt to recreate system software" to "building flashy but useless demo"

Everyone who knows a bit about databases knows the difference between execution models and what kind of optimization it brings.

Question, does having it in pure rust, opens possibility of embedding pgrust directly into binary, making it an alternative to SQLite/turso?

300x faster is nice, but I mostly clicked to see if operator fusion finally explains why my GROUP BY still feels like it is doing the work by hand.

I’m curious to see how this compares to pgColumnar or other OLAP extensions.

I think in addition to making it faster, it would be useful if it could be made "leaner," e.g. can run better on lower-spec hardware than PG.

Thanks to the authors for choosing a license that respect users freedom, on top of being an awesome technical project.

300x if it is true you will be just busy dealing with you customers rather than pitching here. Also since it is a vibe coded project, if you are really that good , you should even need to related yourself with Postgres. Who will want related itself to something that is 300x slower than itself?

Batch mode execution has been in Microsoft SQL Server for a while and just recently gained AVX-512 support.

I’ve done some experiments replacing spatial SQL queries with custom vectorised batch mode code in C# and the speed up was astonishing.

The people dubious about these claims have no idea what their computers are really capable of.

pgrust looks interesting. Could it be used as a library by someone who wants a new DB for each integration test in their Rust test suite?

pgrust seems to have good momentum. AGPL is an odd license for a non web project. Postgres is MIT-like, and that drove it's adoption.

Have pgrust folks reconsidered this? Else, IMO we can have an independant rust port of pgrust, which can be MIT, which will garner more attention.

I'll take the 300x slower non-vibe-coded pg, thanks!

[dead]

[dead]

I'm really happy seeing this project. Not sure if this helps you gain $$$ customers, but stupid thing that turns out very difficult in PG is making this fast:

SELECT COUNT(*) FROM large_text_db WHERE X

Where X is something that must be matched exactly. X can be FTS query on FTS-indexed table, but the way COUNT() works in PG is that it's impossible to make it fast. Over large tables, lets say 1B+ rows, it can be very very slow.

Example use case is: searching through a hospital DB of reports that have "pancreatic cancer" in them. This is trivial in SQLite, but in PG it's hard.