6 ms·

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 versi

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 have any questions, I'm happy to answer them.

barrkel 3mo ago
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.
- malisper 3mo ago
  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 https://github.com/Percona-Lab/sysbench-tpcc [1] https://github.com/ClickHouse/ClickBench https://github.com/ClickHouse/ClickBench
jl6 3mo ago
"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?
- malisper 3mo ago
  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
tudorg 3mo ago
> - 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 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&type=-&machine=+ca4e&cluster_size=-&opensource=-&hardware=+c&tuned=+n&metric=combined&queries=- https://benchmark.clickhouse.com/#system=+liH|_etx|gQ|saB&ty...
- malisper 3mo ago
  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.
  - tudorg 3mo ago
    Yeah, spent a lot of time on parallelism, vectorizing, pipelining, filter push-downs, bloom filters, all the tricks out there. It's really fun to make pretty steady progress on this.
  - FreakLegion 3mo ago
    pg_mooncake (now effectively abandoned due to being acquired by Databricks, but still up at https://github.com/Mooncake-Labs/pg_mooncake https://github.com/Mooncake-Labs/pg_mooncake) pulled the DuckDB engine into Postgres wholesale, if I remember right. pg_lake also uses DuckDB but keeps it external, routing through Postgres and managing Iceberg tables (but not the data itself) there (https://github.com/Snowflake-Labs/pg_lake https://github.com/Snowflake-Labs/pg_lake). Both of these were neck and neck with ClickHouse last time I tried them.
gnull 3mo ago
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?
boomskats 3mo ago
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-af5b-ac0d801163f4%40iki.fi https://www.postgresql.org/message-id/31cc6df9-53fe-3cd9-af5... [1]: https://www.youtube.com/watch?v=xLLakMmVtbY https://www.youtube.com/watch?v=xLLakMmVtbY
- malisper 3mo ago
  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 I've started to see meaningful benefits by changing the parallel algorithms to use a shared memory space. For example parallel hash joins have to copy tuples through shared memory to pass them between workers. That's just not something I have to do.
  - Apaec 3mo ago
    [flagged]
dimes 3mo ago
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.
- malisper 3mo ago
  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?
  - ptrwis 3mo ago
    Not the OP, but yes - just imagine a web server talking to DB over one connection without any connection pooler
  - roller 3mo ago
    Microsoft SQL Server has had a similar feature for a while -- Multiple Active Result Sets aka MARS. I don't have a good read on whether it actually helps any workloads. I've seen adapters that don't support it because of the extra complication. https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/using-multiple-active-result-sets-mars https://learn.microsoft.com/en-us/sql/relational-databases/n...
  - dimes 3mo ago
    Multiplexing would have a number of benefits. As you say, each client would only need a single connection regardless of the number of queries being sent. Resulting in: On the client side, there is usually a local connection pool. When a burst of traffic comes in, the client needs to either wait for the pool to free up or establish a new connection, which adds latency. This latency hit wouldn’t occur with multiplexing. With multiplexing, systems like pgbouncer would be unnecessary. Also, even with a thread-per-connection, you can still quickly exhaust the servers resources when you have lots of connections because threads have a lot of overhead. Reducing the number of connections needed would greatly increase the number of clients that a database can serve.
solomatov 3mo ago
Super impressive! Is it possible for you to share your methodology of using LLMs?
- malisper 3mo ago
  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.
  - solomatov 3mo ago
    Thanks for sharing.
  - krashidov 3mo ago
    Did you hand write the skills or did you have an agent audit your work and infer patterns?
    - malisper 3mo ago
      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.
film42 3mo ago
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.
- fpgaminer 3mo ago
  If it's a choice between performance and being able to "safely" run sketchy extensions, I'd rather have performance.
  - jeltz 3mo ago
    Threads does not offer any major performance advantage, performance of processes vs threads is virtually the same. The reason the PostgreSQL project is moving towards threads is to make development easier.
    - codesnik 3mo ago
      unless you're spawning them for new connections.
      - jeltz 3mo ago
        Some, but not that much. Switching PostgreSQL to a threaded model will not magically make spawning connections fast. PostgreSQL connections are quite heavyweight. The reason to use threads is almost entirely about ease of development, not about performance. If you use shrared memory like PostgreSQL does you need to write your own allocators, etc. So much you get for free if you use threads.
    - malisper 3mo ago
      > 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.
quadrature 3mo ago
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 https://x.com/BenjDicken/status/2074326407795417435
- deleted 3mo ago
  [deleted]
- malisper 3mo ago
  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 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.
  - quadrature 3mo ago
    best of luck to you, it will be great to see a rearchitected postgres.
OsrsNeedsf2P 3mo ago
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?
- malisper 3mo ago
  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 https://www.heap.io/blog/testing-database-changes-right-way [1] https://www.heap.io/blog/analyzing-performance-millions-sql-queries-one-special-snowflake https://www.heap.io/blog/analyzing-performance-millions-sql-... [2] https://malisper.me/table-of-contents/ https://malisper.me/table-of-contents/ [3] https://www.youtube.com/watch?v=xKoYIvMFnoQ https://www.youtube.com/watch?v=xKoYIvMFnoQ [4] https://www.youtube.com/watch?v=MPSMH8w7nfw https://www.youtube.com/watch?v=MPSMH8w7nfw
- deleted 3mo ago
  [deleted]
happyPersonR 3mo ago
Do you have anything in the regression test suite like jepsen etc?
- malisper 3mo ago
  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.
levkk 3mo ago
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!
- LtWorf 3mo ago
  Remember when databases were faster to run in virtualbox rather than bare metal? (because virtual box was completely ignoring all the instructions to flush the data on the disks)
- codys 3mo ago
  It'd be very unfortunate if Postgres didn't have regression tests for data loss due to bad io patterns. Should be possible to do some checks against those in an appropriate test harness. Which might mean "have qemu run something we can kill off and examine the results". If those don't exist, I hope folks recognize how useful they are and add them.
- f311a 3mo ago
  Yeah, claims that it can be faster than CH are very suspicious. CH guys are very good at their craft, they spend hundreds of hours optimizing one single small detail.
  - diarrhea 3mo ago
    Yeah. I don't think PG could even come close. Column-oriented is fundamentally different, and pairs well with all the SIMD acceleration ClickHouse is also doing. There's just no comparison. If a Postgres rewrite came close to that, it must've sacrificed something else.
kardianos 3mo ago
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 https://github.com/solidcoredata/pgwire4
- jeltz 3mo ago
  404 Not Found
  - kardianos 3mo ago
    Now public.
mattgreenrocks 3mo ago
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?
- hoppp 3mo ago
  Bun has an interesting blog post about how it was ported. It did cost a lot, much more than hiring people would cost outside USA.
reinitctxoffset 3mo ago
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.
kfsone 3mo ago
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"?
greatony 3mo ago
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.
teravor 3mo ago
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.
- bluGill 3mo ago
  That forces you not only in not the intentional good decisions of the past but also copies too many bad ones.
deleted 3mo ago
[deleted]
jimbokun 3mo ago
That…is really impressive. Well done!
chuliomartinez 3mo ago
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.
drchaim 3mo ago
i highly doubt you can make it faster than clickhouse, but happy to see it.
egorfine 3mo ago
> build a better version of Postgres Faster is quantifiable. How do you measure better?
- dvhh 3mo ago
  [flagged]
up2isomorphism 3mo ago
300x is mostly a marketing term, especially without the test description. BTW, showing no respect to what it is trying to copy looks uncomfortable.
brikym 3mo ago
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.
eu-tech-tak 3mo ago
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.
agumonkey 3mo ago
Is it your first rewrite/migration (with or without llm) ? good luck nonetheless
Keyframe 3mo ago
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.
- ramijames 3mo ago
  I'm curious. Do you attribute this to weak and/or incomplete tests? How granular should tests be to have complete coverage so that an AI won't create a converted codebase that "passes tests" but is still functionally inaccurate?
  - Keyframe 3mo ago
    That's the million dollar question. Do you/we/us have tests that cover everything which covers QA as well? If such a mythical beast exists, maybe from remnants of ye olde TDD past and hasn't been modified as such.. then maybe this would be possible to do as such.
AlexClickHouse 3mo ago
Would you like to submit to ClickBench? I can also do it if you would prefer...
- zX41ZdbW 3mo ago
  https://github.com/ClickHouse/ClickBench/pull/983 https://github.com/ClickHouse/ClickBench/pull/983

barrkel 3mo ago

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.

malisper 3mo ago
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 https://github.com/Percona-Lab/sysbench-tpcc [1] https://github.com/ClickHouse/ClickBench https://github.com/ClickHouse/ClickBench

jl6 3mo ago

"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?

malisper 3mo ago
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

tudorg 3mo ago

> - 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 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&type=-&machine=+ca4e&cluster_size=-&opensource=-&hardware=+c&tuned=+n&metric=combined&queries=- https://benchmark.clickhouse.com/#system=+liH|_etx|gQ|saB&ty...

malisper 3mo ago
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.
- tudorg 3mo ago
  Yeah, spent a lot of time on parallelism, vectorizing, pipelining, filter push-downs, bloom filters, all the tricks out there. It's really fun to make pretty steady progress on this.
- FreakLegion 3mo ago
  pg_mooncake (now effectively abandoned due to being acquired by Databricks, but still up at https://github.com/Mooncake-Labs/pg_mooncake https://github.com/Mooncake-Labs/pg_mooncake) pulled the DuckDB engine into Postgres wholesale, if I remember right. pg_lake also uses DuckDB but keeps it external, routing through Postgres and managing Iceberg tables (but not the data itself) there (https://github.com/Snowflake-Labs/pg_lake https://github.com/Snowflake-Labs/pg_lake). Both of these were neck and neck with ClickHouse last time I tried them.

gnull 3mo ago

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?

boomskats 3mo ago

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-af5b-ac0d801163f4%40iki.fi https://www.postgresql.org/message-id/31cc6df9-53fe-3cd9-af5... [1]: https://www.youtube.com/watch?v=xLLakMmVtbY https://www.youtube.com/watch?v=xLLakMmVtbY

malisper 3mo ago
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 I've started to see meaningful benefits by changing the parallel algorithms to use a shared memory space. For example parallel hash joins have to copy tuples through shared memory to pass them between workers. That's just not something I have to do.
- Apaec 3mo ago
  [flagged]

dimes 3mo ago

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.

malisper 3mo ago
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?
- ptrwis 3mo ago
  Not the OP, but yes - just imagine a web server talking to DB over one connection without any connection pooler
- roller 3mo ago
  Microsoft SQL Server has had a similar feature for a while -- Multiple Active Result Sets aka MARS. I don't have a good read on whether it actually helps any workloads. I've seen adapters that don't support it because of the extra complication. https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/using-multiple-active-result-sets-mars https://learn.microsoft.com/en-us/sql/relational-databases/n...
- dimes 3mo ago
  Multiplexing would have a number of benefits. As you say, each client would only need a single connection regardless of the number of queries being sent. Resulting in: On the client side, there is usually a local connection pool. When a burst of traffic comes in, the client needs to either wait for the pool to free up or establish a new connection, which adds latency. This latency hit wouldn’t occur with multiplexing. With multiplexing, systems like pgbouncer would be unnecessary. Also, even with a thread-per-connection, you can still quickly exhaust the servers resources when you have lots of connections because threads have a lot of overhead. Reducing the number of connections needed would greatly increase the number of clients that a database can serve.

solomatov 3mo ago

Super impressive! Is it possible for you to share your methodology of using LLMs?

malisper 3mo ago
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.
- solomatov 3mo ago
  Thanks for sharing.
- krashidov 3mo ago
  Did you hand write the skills or did you have an agent audit your work and infer patterns?
  - malisper 3mo ago
    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.

film42 3mo ago

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.

fpgaminer 3mo ago
If it's a choice between performance and being able to "safely" run sketchy extensions, I'd rather have performance.
- jeltz 3mo ago
  Threads does not offer any major performance advantage, performance of processes vs threads is virtually the same. The reason the PostgreSQL project is moving towards threads is to make development easier.
  - codesnik 3mo ago
    unless you're spawning them for new connections.
    - jeltz 3mo ago
      Some, but not that much. Switching PostgreSQL to a threaded model will not magically make spawning connections fast. PostgreSQL connections are quite heavyweight. The reason to use threads is almost entirely about ease of development, not about performance. If you use shrared memory like PostgreSQL does you need to write your own allocators, etc. So much you get for free if you use threads.
  - malisper 3mo ago
    > 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.

quadrature 3mo ago

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 https://x.com/BenjDicken/status/2074326407795417435

deleted 3mo ago
[deleted]
malisper 3mo ago
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 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.
- quadrature 3mo ago
  best of luck to you, it will be great to see a rearchitected postgres.

OsrsNeedsf2P 3mo ago

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?

malisper 3mo ago
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 https://www.heap.io/blog/testing-database-changes-right-way [1] https://www.heap.io/blog/analyzing-performance-millions-sql-queries-one-special-snowflake https://www.heap.io/blog/analyzing-performance-millions-sql-... [2] https://malisper.me/table-of-contents/ https://malisper.me/table-of-contents/ [3] https://www.youtube.com/watch?v=xKoYIvMFnoQ https://www.youtube.com/watch?v=xKoYIvMFnoQ [4] https://www.youtube.com/watch?v=MPSMH8w7nfw https://www.youtube.com/watch?v=MPSMH8w7nfw
deleted 3mo ago
[deleted]

happyPersonR 3mo ago

Do you have anything in the regression test suite like jepsen etc?

malisper 3mo ago
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.

levkk 3mo ago

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!

LtWorf 3mo ago
Remember when databases were faster to run in virtualbox rather than bare metal? (because virtual box was completely ignoring all the instructions to flush the data on the disks)
codys 3mo ago
It'd be very unfortunate if Postgres didn't have regression tests for data loss due to bad io patterns. Should be possible to do some checks against those in an appropriate test harness. Which might mean "have qemu run something we can kill off and examine the results". If those don't exist, I hope folks recognize how useful they are and add them.
f311a 3mo ago
Yeah, claims that it can be faster than CH are very suspicious. CH guys are very good at their craft, they spend hundreds of hours optimizing one single small detail.
- diarrhea 3mo ago
  Yeah. I don't think PG could even come close. Column-oriented is fundamentally different, and pairs well with all the SIMD acceleration ClickHouse is also doing. There's just no comparison. If a Postgres rewrite came close to that, it must've sacrificed something else.

kardianos 3mo ago

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 https://github.com/solidcoredata/pgwire4

jeltz 3mo ago
404 Not Found
- kardianos 3mo ago
  Now public.

mattgreenrocks 3mo ago

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?

hoppp 3mo ago
Bun has an interesting blog post about how it was ported. It did cost a lot, much more than hiring people would cost outside USA.

reinitctxoffset 3mo ago

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.

kfsone 3mo ago

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"?

greatony 3mo ago

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.

teravor 3mo ago

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.

bluGill 3mo ago
That forces you not only in not the intentional good decisions of the past but also copies too many bad ones.

deleted 3mo ago

[deleted]

jimbokun 3mo ago

That…is really impressive. Well done!

chuliomartinez 3mo ago

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.

drchaim 3mo ago

i highly doubt you can make it faster than clickhouse, but happy to see it.

egorfine 3mo ago

> build a better version of Postgres Faster is quantifiable. How do you measure better?

dvhh 3mo ago
[flagged]

up2isomorphism 3mo ago

300x is mostly a marketing term, especially without the test description. BTW, showing no respect to what it is trying to copy looks uncomfortable.

brikym 3mo ago

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.

eu-tech-tak 3mo ago

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.

agumonkey 3mo ago

Is it your first rewrite/migration (with or without llm) ? good luck nonetheless

Keyframe 3mo ago

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.

ramijames 3mo ago
I'm curious. Do you attribute this to weak and/or incomplete tests? How granular should tests be to have complete coverage so that an AI won't create a converted codebase that "passes tests" but is still functionally inaccurate?
- Keyframe 3mo ago
  That's the million dollar question. Do you/we/us have tests that cover everything which covers QA as well? If such a mythical beast exists, maybe from remnants of ye olde TDD past and hasn't been modified as such.. then maybe this would be possible to do as such.

AlexClickHouse 3mo ago

Would you like to submit to ClickBench? I can also do it if you would prefer...

zX41ZdbW 3mo ago
https://github.com/ClickHouse/ClickBench/pull/983 https://github.com/ClickHouse/ClickBench/pull/983