alt Hacker NewsHey author here. Wasn't expecting to see this up.
To concisely give an overview of the project, I've been experimenting with using LLMs to build a better version of Postgres. Postgres is 30 years old and we've learned a lot about databases since hten. A lot of the techniques that work for doing a rewrite are also useful for doing a rearchitecture.
I'm now working on a new, not yet published version of pgrust that incorporates a lot of techniques. Currently the new version:
- Passes 100% of Postgres regression suite
- Implements a thread per connection model instead of the process per connection model Postgres does
- Is 50% faster than Postgres on transaction workloads
- Is ~300x faster than Postgres on analytical workloads. Right now it's 2x slower than Clickhouse on clickbench and I think it's possible to get faster than Clickhouse
Replies
A thread per connection is a almost always the correct decision for performance, but by choosing a process per connection, postgres is able to let you load whatever sketchy extensions you want. Worst case you crash the process, not the database. It would be nice if you could strike a balance so a segfaul in the extension only crashes a small percentage of connections, not the whole thing.
Ouf. I don't know. I don't want to call you out without evidence -- I myself make benchmark claims all the time -- but 50% improvement in OLTP seems suspicious. I get that you used a standard benchmark, and I don't even know what it entails, but my spidey sense is going off. Perhaps, some trade off somewhere that won't make it to prod because it breaks MVCC -- and yes, I saw that it passes regression tests.
Just checking, is fsync on? :) Regression tests don't catch bad IO patterns afaik.
Anyway... sounds like a fun project to work on!
What's your actual background and expertise with Postgres and databases more broadly? Basically, do you actually know what you're doing, or is there likely a massive footgun you don't know or haven't shared with us?
It doesn't sound like you were trying to launch a product, but doing an experiment and someone threw you under the HN-spotlight-bus :) Is this a "see what I can achieve with LLM coding" or is this "build this and see how much of the coding can be accepted from LLMs"?
> - Is ~300x faster than Postgres on analytical workloads. Right now it's 2x slower than Clickhouse on clickbench and I think it's possible to get faster than Clickhouse
That sounds like you are storing the data in a columnar format? Or do you do both row and columnar?
In a somewhat similar (yet also quite different) effort, I've been working on δx, a Postgres extension that compresses the data in a columnar format stored in normal Postgres tables (so replication, crash recovery, pg_dump, etc. still work normally). https://github.com/xataio/deltax
It is currently about 30-40% slower than ClickHouse (single node, ofc). The PR to add it to clickbench was just accepted, so you can see the comparison here: https://benchmark.clickhouse.com/#system=+liH|_etx|gQ|saB&ty...
What was your methodology and structure in making the prompts for the rewrite? Did you let the LLM roam in all of the codebase and tests from the beginning, or revealed things to it gradually in some way?
> build a better version of Postgres
Faster is quantifiable. How do you measure better?
Are you fixing the heap and table management ?. Postgres does not use an undo log and manages all table updates directly in table storage which slows MVCC.
also have you told Ben Dicken ? https://x.com/BenjDicken/status/2074326407795417435
While a thread-per-connection seems like an improvement, do you have any plans to allow query multiplexing over a single connection? That would be a huge improvement IMO.
"Is 50% faster than Postgres on transaction workloads" - That is a very big claim! 50% faster on everything? Is it a strict improvement across the board or are there tradeoffs that make some workloads slower?
This is great! Those analytical workloads numbers are mad - I'd love to see the benches, and I'm happy to contribute to some of the profiling.
How does your thread-per-connection model compare to Heikki's proposal[0][1] from back in 2023?
[0]: https://www.postgresql.org/message-id/31cc6df9-53fe-3cd9-af5... [1]: https://www.youtube.com/watch?v=xLLakMmVtbY
Super impressive! Is it possible for you to share your methodology of using LLMs?
Is it being used in production anywhere, even if only a toy app?
I know you say it's not production ready and not optimized yet, but in the same breath - in your comment here - you say it's already faster.
How much of the performance gain is from using Rust, compared to using optimizations that are not done in the original PostgreSQL code (like using threads instead of processes, etc.)?
I am simply curious what the benefits of using Rust are in this instance.
I am super curious how you went about the port using LLMs. At $WORK we are looking to port code, preferably with LLMs, and it seems daunting, even with a test suite. Do you have an approach that works well for you?
Just a couple of ideas if you run out of backlog:) - proper versionnumber (64bit) - native json streaming. It would be awesome to get to the point where i could somehow redirect the sql output to the browser directly, but piping will do for now. The idea is to be able to stream rows to client without caching and building json along the way.
PG Wire proto 3 is my largest source of frustrations.
I'm playing with a POC for a better wire protocol here: https://github.com/solidcoredata/pgwire4
Would you like to submit to ClickBench?
I can also do it if you would prefer...
It's a completely new era of software production (I will no longer call it development) LLMs give us unlimited manpower, and the language give us constraints to make more modern and safer softwares. Love to see this rewrite in Rust, and expecting much much more in next few month.
Do you have anything in the regression test suite like jepsen etc?
when doing rewrites like these, why isn't the first step to instrument the original code so that you would get very good automated test suites to point the LLM toward?
use both synthetic and real data to sample the internals of the original software to duplicate.
locate all the data transformation junctures, sample and then replicate the tranforms 1:1 in the rewrite.
i highly doubt you can make it faster than clickhouse, but happy to see it.
That…is really impressive. Well done!
Is it your first rewrite/migration (with or without llm) ?
good luck nonetheless
300x is mostly a marketing term, especially without the test description.
BTW, showing no respect to what it is trying to copy looks uncomfortable.
This is how to LLM. Big ups, I wish the whole front page was stuff like this (and I think it'll happen).
Everyone is so worried about the value of commodity software going to zero. It's like, yeah, going into CS for the money always looked dumb to me, it's just not a good career path for that, you have to love it.
I am way more excited about a whole new class of stuff that obliterates the state of the art at every frontier.
Keep doing it legend.
Awesome work. I'd love to see you add something like kusto query language or pql. The autocomple on kusto, (which can be embedded into web apps) is really amazing.
I don't want to knock you down as most have already did. In-fact it's a useful exercise going forward in exploring how to work with AI. It's here, we're all going to use it one way or the other. Zero issues with that, in-fact kudos to going through the pain of it all.
Now, having gone through several such endeavors originally myself, albeit with internal tools and systems (as an exercise), I've noticed that while all my tests passed with flying colors the rewrite itself was broken even on basic functionality or missed a ton of details. It was in effect useless when I dived into it. Initial tests also showed massive gain in performance, and I know people who were involved aren't really dumb so something smelled funny. Turns out all those things left out and honestly... moments were the key ingredients.
What I did learn from those beginning explorations though was that one-shotting, grand architecture or source up-front, master plans up-front.. all these do not yield good results - YET. Who know what we'll see in few years though. What I did found that works (FOR ME, nota bene) is to keep the design and checklists for myself, written by myself and then do a small piece by piece.. as if you would if you were coding alone or if you would waterfalling a small team of talented juniors. Then, suddenly super happy results come out, but then it's mostly you driving all the way where llm writes code and offers advice (which for the most part you ignore). It's a happy place for myself at least. It's then truly unlocking yourself to the mythical 10x.
Rewriting a large proven system with decades of ultra expertise behind it, which I don't have, is guaranteed not to end up the same 1:1 replacement. If you found a recipe for that - please do share.