How pgrust was built: four attempts to rewrite Postgres in Rust with AI - malisper.me
Primary Menu
malisper.me
malisper.me
General<br>About Me
Table of Contents for Postgres Posts
RSS
Subscribe to Blog via Email
Enter your email address to subscribe to this blog and receive notifications of new posts by email.
Email Address
Subscribe
Skip to content
Home » Uncategorized » How pgrust was built: four attempts to rewrite Postgres in Rust with AI
tl;dr After four attempts and dropping $100k, we succeeded in using Opus to rewrite Postgres in Rust. We defined a number of skills and were able to build a repeatable process to rewrite Postgres files into Rust. After running up to 40 concurrent subagents for days, the end result is the 1.8M lines of idiomatic rust code that make up pgrust.
Over the past three months, I’ve been working with my friend Jason Seibel on building pgrust. The idea behind pgrust is we want to show that by leveraging AI, it’s possible to can build a much better version of Postgres. Unfortunately Postgres is written in C and AI still makes a lot of mistakes. For this reason, we started by first rewriting Postgres in Rust. We believe that by having Postgres in Rust it will become a lot easier for AI to safely add new features to Postgres. This thesis is starting to be proven true. Currently in development we have a new, 5th version of pgrust that is faster than Postgres for transactional workloads and is as fast as Clickhouse when it comes to analytical style queries. For context, Clickhouse is hundreds of times faster than Postgres when it comes to analytical queries.
Let us walk you through each of our attempts and what we learned from each approach. We figure for those interested in completely rewriting legacy systems, this post will serve as a useful guide as how to go about such a rewrite of your own.
Attempt #1 – pgrust-og
The first version of pgrust, pgrust-og was initially was going well. It got very close to completion, but ended up having fundamental issues that made it unsalvageable. What I consider to be the root issue is we approached pgrust-og by porting one feature at a time (for contrast in later versions I worked on porting one file at a time).
There were roughly three different phases to the pgrust-og lifecycle:
Foundation
Features
Last Mile
Foundation
Initially I worked on laying out the core foundation of the database. Postgres has a couple of key systems that everything else relies on. To name a few:
The query parser – The parser converts your SQL query into a representation Postgres can understand what data you’re asking for
The query planner – The query planner takes the output from the parser and produces a query plan. A specific way for Postgres to retrieve the data you’re looking for
The executor – Once Postgres has a plan for how to fetch your data, the executor is the one that actually fetches it
Along with these three, there are maybe 15 other core subsystems of Postgres.
Over a couple of days I built a new database in Rust that architecturally had the same subsystems as Postgres. This step seemingly went very smooth. For each component, I already had a pretty good sense of how it should work. I gave Codex 5.4 access to the Postgres codebase and would ask it to come up with a plan for rewriting X subsystem of Postgres into pgrust-og. I would then review the plan and make sure it covered everything I wanted and would give Codex feedback on the plan where it fell short. From there I had codex execute the plan.
Given these were the core systems of Postgres, I read the code thoroughly to make sure I understood how they worked. This approach worked pretty well and after a couple of days I had knocked out pretty much all the core pieces.
Features, Features, Features
Once I had the core scaffolding in place, I started working on adding all the features that Postgres supports. The easiest way to do this was to look at the Postgres regression tests and look at all the features they were testing. Now the thing about Postgres is it has a surprising number of features. When it comes to features, Postgres looks almost like a programming language’s standard library. Postgres has:
Over 300 different types (including two different json types)
Nearly 500 different SQL keywords
More than 3000 different builtin functions
That’s a lot to implement!
Initially I was following the same approach I did when working on the foundation phase. The "problem" I was running into was that Codex was too good at implementing these features. For any individual feature, Codex is usually able to one-shot it. If you want to try it yourself, ask Codex to write a JSON parser for you. The problem I was running into was I would ask Codex to come up with a plan and then wait hours for it to implement the plan. I spent most of my time "programming" being idle!
This is when I realized that it might be possible to run multiple agents at a time. I decided it was time...