malisper

When I've dealt with this I've generally made sure the transactions are updating rows in a consistent order. You can do that by sorting the rows before you update them

use set seqscan = off when testing your query plans esp when tables are empty or nearly so so you can see if indexes will be used when seq scans become less cheap

How well does this work for you? I thought if you have _any_ index, Postgres will use it if you disabled sequential scans. Diabling sequential scans won't tell if you if you have the right index

This is correct. We first used c2rust to translate the C into unsafe Rust. The generated Rust code had one crate per C compilation unit. We then took the unsafe unidiomatic Rust and one crate at a time converted it to safe Rust.

Actually the inverse. I initially gave claude an outline of what I wanted, had it do some research into how to write idiomatic rust, and then had it draft a series of skills to do the work. I would then try out the skills, audit the results, and then give claude feedback based on what I was seeing. Once I started getting runs where the results were working, I would start to scale things up and audit things with an exponential backoff.

Nothing major yet. Once I wrap up the performance work I'm doing I'll start looking at the best way to go about testing. I suspect there's a lot of novel things you can do with agents.

Thank you! I'm a big fan of your writing. I wanted to make sure there was something people could try out so I could show pgrust was real and not vaporware.

Thank you!

Threads does not offer any major performance advantage

This is very not true. When it comes to parallel queries, a process model adds a ton of overhead. You can't pass pointers between processes because the address space is different. This adds a ton of overhead in a bunch of different places. For example when doing a parallel hash join, Postgres will have each worker build a local hash table. Then it will take all the tuples out of the local hash table and copy them through shared memory to the leader who will then construct a new hash table. This duplicates a lot of work as you have to hash the tuples multiple times.

A lot of getting to Clickhouse level performance was making better use of parallelism.

I spent a couple years managing a Postgres cluster with a petabyte of data. I wrote a couple blog posts from my work then[0][1]. I also wrote dozens of posts on the Postgres internals[2]. I've also given talks on how to generate fractals with SQL[3] and how to write a lisp interpreter in SQL[4].

[0] https://www.heap.io/blog/testing-database-changes-right-way

[1] https://www.heap.io/blog/analyzing-performance-millions-sql-...

[2] https://malisper.me/table-of-contents/

[3] https://www.youtube.com/watch?v=xKoYIvMFnoQ

[4] https://www.youtube.com/watch?v=MPSMH8w7nfw

That's something I eventually want to fix. The challenge is the storage format is so integral to Postgres that it's going to be a huge PITA to come up with a novel design.

Right now OrioleDB is in beta. Once that becomes production ready, I'll evaluate incorporating it into pgrust.

For Ben Dicken, he has seen the project: https://x.com/BenjDicken/status/2074512043462603236. We're still working on all the novel features so I don't think it meets his bar quite yet.

My approach has changed throughout the course of this project. Throughout most of the project, we were working off of a c2rust translation of Postgres to Rust. That gave us a bunch of Rust code that was unsafe but did pass the Postgres test suite and was fast. c2rust had split Postgres into 1000 different crates. We then went through 1 by 1 and rewrote each crate into idiomatic rust.

This naturally lended itself to a suite of skills to describe how to rewrite a crate from unsafe rust to idiomatic rust. The main three skills I had were 1) a skill for identifying the next crates to port 2) a skill for rewriting a crate and 3) a skill for auditing a crate and making sure there weren't any outstanding issues.

My exact approach for managing subagents changed throughout the project. Initially I was doing parallel coding sessions with Conductor. After dynamic workflows came out, I used that as it was really easy to spin up dozens of parallel subagents and manage it from a single orchestrator. Over time I switched from using dynamic workflows to manually spinning up subagents from a central agent. The issue with dynamic workflows is they waterfall. Each step needs to finish before the next one starts. By manually spinning up subagents, I could have claude start porting a new crate as soon as a prior subagent finished.

Can you elaborate on the use case for query multiplexing? Is it so your client would only need to establish one connection with Postgres and then could run as many queries as it wanted?

The rewrite also could have simpler code in some cases

The Rust code is a literal translation of the Postgres code which returns the value at the end instead of an early return.

I see a lot of MemoryContext

MemoryContext in C is used for multiple reasons: 1) performance 2) keeping track of how much memory has been allocated and where and 3) preventing memory leaks.

Reasons 1 and 2 are still relevant for Rust. The challenge is in C memory contexts are stored in a global variable. Global variables don't work well with the rust borrow checker so I opted for passing memory contexts as function arguments instead.

Yep! The new version of pgrust supports batch based execution and a columnar format. I'm curious how you got δx to perform that well? From what I've seen a columnar layout only gets you part of the way and really good parallelism and really fast hash tables seem to make up a significant portion of why Clickhouse is faster.

Rust actually made the change pretty simple. The main changes are:

  - Use thread local variables
  - Move everything from shared memory to process memory
  - Use threads instead of processes

why is the parser unsafe at all?!?

The parser was generated by c2rust. The Postgres parser is generated from yacc/bison itself so I didn't bother making it idiomatic.

Note that most of the unsafes are confined to the parser which was generated by running c2rust over the Postgres parser. The Postgres parser is itself generated from yacc/bison, so I decided to port it over mechanically rather than idiomatically.

If there's particular unsafes that you think are egregious, let me know.

Note that the code I believe you are referring to is from the parser which was generated with c2rust. The Postgres parser is generated from yacc/bison so rather than try to rewrite it idiomatically, I did so mechanically.

The 50% is specifically on percona-tpcc[0]. I got there through a mix of batching (postgres processes a row at a time), prefetching, and several handful of other optimizations.

  [0] https://github.com/Percona-Lab/sysbench-tpcc

It is theoretically possible to have a Rust port of Postgres support extensions. If you make all the relevant functions and structures ABI compatible with Postgres, extensions should work. The issue is the moment you're dealing with C pointers and C strings, pretty much all the code you have to write is unsafe.

The version in the GitHub repo is ~8x slower than Postgres. I have a new unpublished version that is 50% faster than Postgres on transactional workloads and ~300x faster on analytical workloads.

It's not used in production. I've been using different benchmarks to compare the performance vs other systems. Namely sysbench-tpcc[0] and clickbench[1]

[0] https://github.com/Percona-Lab/sysbench-tpcc

[1] https://github.com/ClickHouse/ClickBench

I'm not sure what you mean? The rust code you're showing mimics the Postgres code: https://github.com/postgres/postgres/blob/2e6578292a9184dcaa...

The boolean being returned is the return value of the function. It's not used to return an error.

Hey 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

If you want to follow along, I've been writing about it on my blog (https://malisper.me/) an you can follow the github repo here: https://github.com/malisper/pgrust

I can confirm a naive rewrite won't make things faster. I've been working on rewriting Postgres in Rust. I rewrote things function by function similar to how Jarred did. Even though the new Rust code mapped closely with the previous C code, it was 8x slower. This was due to myriad of reasons. For example naively converting a C union into a Rust enum can be slower because Rust stores a tag with the enum, while C unions do not.

I've been working on a new rewrite that's focused on beating Postgres on performance. As of this morning I got to 100% of the tests passing and have meaningful performance gains over Postgres.

Maybe the takeaway is that 20% is about all the LLM can muster

At this point there's a long list of projects that have used LLMs to rewrite a system in Rust including:

  - Bun (https://github.com/oven-sh/bun/pull/30412)
  - Valkey (https://github.com/ianm199/valdr)
  - Git (https://github.com/gitbutlerapp/grit)
  - Postgres (https://github.com/malisper/pgrust)

But LLMs are great at this

Not really. The latency for an llm to make a single change is on the order of seconds. If in order to change a single function from unsafe Rust to safe rust requires thousands of changes, it will take hours to refactor a single function.

I made a comment about this yesterday[0], but there's been a massive increase in people migrating from C to Rust due to LLMs.

In contrast to what the C to Rust migration book is recommending (using FFI to integrate Rust with C), I've found it much easier to start from scratch. I recently finished a project where I rewrote Postgres in Rust[1]. For context, Postgres is about one million lines of C code.

On one attempt, I tried using c2rust to convert Postgres into unsafe Rust code. That attempt succeeded in terms of getting working "Rust" code, but any attempt to change any piece to safe rust, would require thousands of changes across codebase. Even though I had working Rust code, I found it infeasible to get to working idiomatic Rust code.

Instead what I found to be more effective was starting a new codebase and rewrite each file from the Postgres codebase into Rust one at a time. This allowed me to guarantee that at all times the new codebase was idiomatic and simultaneously I could make one pass over the Postgres codebase to get working idiomatic Rust.

YMMV, but I found it way easier to generate a whole new codebase from scratch rather than incrementally rewrite an existing codebase.

[0] https://news.ycombinator.com/item?id=48738985#48739882

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

A meta-trend I find interesting is there's a lot of projects using AI to rewrite existing systems in new programming languages. Most often in Rust.

  1. Bun rewritten in Rust
  2. Flow rewritten in Rust
  3. The react compiler was rewritten in Rust
  4. Grit is a new implementation of Git in Rust
  5. I've made my own rust rewrite of postgres that passes 100% of the regression and isolation tests[0][1]

One of the many things I did to deal with this was an audit skill that would:

  1. Find a small chunk of code to rewrite
  2. Have a list of things that it was looking for in each piece of code that's being rewritten
  3. Place that next to the code being translated
  4. If that document didn't exist and/or didn't say the code was passing the audit, code wouldn't be merged
  5. As I found problems and anti-patterns I would add those to the skill over time

Complicated old "boring" infra software might actually be the most AI-rewriteable code right now

[0] https://pgrust.com

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