[malisper]

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.

[film42]

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.

[pilif]

That’s not true for Postgres however: due to its usage of a shared memory pool, whenever a subprocess is terminated unexpectedly, Postgres will kill all other processes and enter recovery mode, replaying the WAL, during which time it will not accept connection requests.

It does this because it can’t possibly know whether the dying process did bad things to the shared memory pool.

[film42]

You are correct! TIL, and thank you. Connection processes get a SIGQUIT, shared buffers cleared, and WAL replayed, but postmaster stays alive. It's effectively an online restart.

[anp]

I’d be very curious to hear what an online restart approach for sketchy extensions crashing a shared thread per connection process might look like. This is more a question for the author of the project but I’m curious if they have plans in that direction.

[funny_falcon]

systems, upstart or even simple primitive script wrapper.

Built-in is also possible: just fork once after start, and you have parent as watchdog and restarter.

[vanviegen]

What's online about this restart? Just that the tcp port keeps listening? I assume all running transaction will have to be aborted, right? And connections dropped?

[film42]

The postmaster is effectively your supervisor. It could see the child segfault and abort the whole DB, but that would be deferring supervision to your init system. Not ideal.

[fpgaminer]

If it's a choice between performance and being able to "safely" run sketchy extensions, I'd rather have performance.

[brianaker]

A mixture of threads and processes that can be used to match processors, disk I/O, and network interfaces.

A very long time ago, there was once a feature called "Data Blades" which tanked a commercial database vendor. A badly behaving blade could bring down the entire database. Most anyone who has been working on databases for a few decades remembers this and makes a point of either not introducing these sorts of features or making use of processes over threads.

I have not looked at the code referenced in the mentioned project, but thus far I haven't seen a model that could craft a complete SQL parser on its own.

There are a number of problems, and design decisions, that a developer decides on when writing a database that I don't see any current models… just because you have the ingredients does not mean that the stew is edible.

[quantified]

Informix. Michael Stonebraker, to his credit, learned a lot along the way and revised his thinking about database technology and capability after believing that a single engine could be good at everything.

[mulmen]

> A very long time ago, there was once a feature called "Data Blades" which tanked a commercial database vendor.

I have no idea what this is and a web search turned up Harbor Freight woodworking tools.

[Izkata]

I think it's this: https://www.ibm.com/docs/en/informix-servers/15.0.x?topic=co...

And what you found seems to be "Dado", not "Data".

[mulmen]

Yes, Kagi was sufficiently confused as to what "Data blades" are that it actually thought I was looking for replacement woodworking blades. "DataBlade" finds the IBM Informix product.

[Verdex]

My first web search (data blades) turned up harbor freight woodworking tools.

Then I made a second web search: data blades database.

That turned up some ibm database software module technology which I assume is what's being discussed.

[mulmen]

I performed both searches on Kagi and didn't see anything about IBM. I do see results for "DataBlade" and "Data blade database" but I didn't try those specific variations after my first two attempts returned nothing of interest. That's too much effort to decipher a HN post.

[rtpg]

While PG's behavior doesn't guarantee a lack of data corruption, "an extension crashed, all bets are off, tear everything down" is going to give you a much better fighting chance against data corruption vs the alternative.

[roughly]

In the age of vibe-generated code, I promise you're gonna want the safety.

[CuriouslyC]

So load them up in read replicas

[echelon]

You have to have a write path. You're gonna want what to be non-sketchy and performant.

[noworriesnate]

Or use webassembly to sandbox them

[wtetzner]

What about just not installing sketchy extensions?

[throwitaway222]

I know right? Postgres is not firefox, we don't operate with 25 extensions on all the time. At most we have 1 and normally we have 0.

[socalgal2]

You could fix probably the sketchy extension issue with WASM.

[worthless-trash]

Limit, not fix.

[tremon]

Do you mean that you don't run sketchy extensions and therefore this doesn't affect you, or that you're ok with data loss due to extension failures?

[hoppp]

What about extensions that are not sketchy? Lots of good ones out there.

[throwitaway222]

People are assuming the extensions can't also be rewritten to be good.

[throwaway27448]

Presumably they'd be fine running in a threaded context.

[thayne]

An extension written for a single threaded host system might not work in a multi-threaded context. For example if has global or shared state that isn't protected with locks or similar (which is unfortunately fairly common in c code)

[hoppp]

Extensions like pgvector, TimescaleDB would probably need to be ported tho, not sure how much but there are footguns.

[SoftTalker]

Not needed in many (most?) cases.

[throwitaway222]

Especially since all those sketchy extensions can be rewritten in rust over a weekend and have their bugs fixed as well.

[xikxp1]

This is just not true. PGRX can't physically support expanded shared memory due to Rust being very strict with custom allocators. A lot of shady extensions rely on dynamically allocated memory and C++ is like the only possible choice there.

[ori_b]

But it isn't.

[jeltz]

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.

[malisper]

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

[mort96]

Passing pointers is not significantly faster than passing offsets into a shared memory pool.

[menaerus]

Sorry, what? Passing a pointer is a matter of wrapping the value into the CPU register. OTOH passing an offset into a shared memory is a write to main memory so several magnitudes slower.

[jeltz]

Ok ... you know PostgreSQL supports hash tables in shared memory, right? PostgreSQL could in theory share those if we wanted to. The issue is just that coding anything which uses shared memory is a lot of work.

Additionally the reasons PostgreSQL does not offer Clickhouse performance has very little to do with parallelism. PostgreSQL plans to move to threading but the efforts around imporving OLAP performance are almost entirely unrelated.

[comex]

> The issue is just that coding anything which uses shared memory is a lot of work.

Doesn’t that kind of prove the parent’s point though? In theory shared memory can do anything that threads can do. But if in practice some feature doesn’t get implemented in the multi-process design (because shared memory is hard), when it likely would have been implemented in a threaded design, then that’s still an advantage for threads.

[gpm]

Apart from being a lot of work are you really gaining much at that point? Memory corruption can still take down both sides...

[dupontcyborg]

i may be missing context, but shared memory across processes, without ipc?

[SigmundA]

MSSQL can handle 32k open connections no need to run a pooler in front of it, can PG do 32k connections and a process for each?

MSSQL shares cached query plans between connections including jitted code, PG cannot do that and the changes needed to make the plans cross process portable would be extensive while sharing between threads is just normal code sharing between threads.

[codesnik]

unless you're spawning them for new connections.

[jeltz]

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.

[TZubiri]

Some see a 30 year old system and think "outdated", I see a 30 year old system and think "time tested."

Clearly a process per connection is more stable and that's what I'm using.

It's unclear what problem such optimizations are solving anyway, with the old way you could only support a million concurrent users with a single server? Are we missing out on supporting ten million concurrent users with 2 servers instead of 10? Ostensibly reducing the minimum db hardware opex for a 10B$ company from 10k$/month to 2k$/month?

[film42]

A million users? Hell, I'd bet 99.999% of live postgres databases in existence serve less than 5 users on average. Even among products that actually make a profit, I bet 99.9% of them serve less than 100 customers a day. We hooligans on hacker news manage the 0.1% of databases, and in my newfound consulting life, I'm hoping to never support one of those again.

[vanviegen]

> Clearly a process per connection is more stable

Even if those processes share most of their memory, and are written in a notoriously memory-unsafe language?

A significant performance improvement can well be the difference between being able to run the entire database on one beefy server, and having to shard. And that has a huge cost in terms of complexity and thus reliability and development time.

[toast0]

Doesn't postgres (rightly) have a cow if a process has a disorderly shutdown (at least while in a write transaction) because there's shared memory between the processes?

[fluffybucktsnek]

I'm not informed of the Postgres's internals, but, maybe, that can be solved by grouping threads into different processes depending on which set of extensions they request.

[win311fwg]

An OS thread per connection can be fine for performance if you don't have to scale your connections, but if you don't need to scale connections why have connections at all? Databases are even more performant when you eliminate connection overhead entirely.

[menaerus]

Thread pools

[andai]

Is that a serious issue? Wouldn't it just restart the split second later? Or does it take a long time to start?

(Or I guess it would get stuck in a doom loop or something?)

[skybrian]

A more modern way to do this might be to support WebAssembly plugins.

The extensions might need to be rewritten, but hey, we have AI for that now, so why not :-)

[Keyframe]

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.

[gulugawa]

I don't code with LLMs, and think you might be right.

However, Postgres is a tool with clearly defined functionality and doesn't have ambiguous requirements that are seen in user facing software. As a result, it is entirely plausible that the author created a working Postgres replacement for certain use cases.

I personally want to see more evidence about the quality of the tool after it is in a finished state.

[ramijames]

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?

[NohatCoder]

There is no such thing as a complete test suite, there will always be some possible bug that it doesn't catch.

In particular, if you put an LLM in an automated loop of "this test fails, please fix it", there is a pretty good chance that it will simply special case all of the tests, possibly in some contrived way that makes it not at all obvious when you read the code.

[baranul]

This is what I have observed as well. Been through many debates about the "perfect plan" and "perfect tests" fallacies.

Maybe a way of looking at it, to understand the nature of the issue. Have a LLM translate a novel from English to Spanish. Of course it can do that translation at speeds that no human could (score a point for AI). But how good is the Spanish translation? Is the quality better than what humans could do? Wouldn't those who are not fluent in Spanish be more easily impressed?

We then can do all kinds of configuration setups and tests, but how do we know the Spanish was translated perfectly, without a massive detail review (and being already truly bilingual in both English and Spanish)?

As is the usual case in the pursuit of perfection (which nothing in nature ever seems to be), there is going to be mistakes, costs (worth it?), and gray areas. It would be foolhardy for us not to suspect or pass it off as otherwise.

[deanishe]

> how do we know the Spanish was translated perfectly, without a massive detail review

You can't. I think that's a large part of why LLMs have caught on much better with programmers: they have ways of making the computer check its own work.

Checking a document is still a laborious manual task. And completely unfulfilling.

[baranul]

And that's the trap. Relying on a "perfectly" crafted test or a LLM to verify what is unknown. Yes, LLMs can be very useful, but perhaps it's best for us to be realistic.

> making the computer check its own work

Kind of like the Spanish teacher telling his students they can grade their own tests, then being surprised that Billy was always giving himself 100%, when he's nowhere near that bright or fluent.

It wouldn't be so bad, if people were more upfront with being unsure or made it clear they were extrapolating from smaller and limited data. But usually, like many of these unusually cocky LLMs, what is too often reported to the public is "perfection" and many inconvenient truths "swept underneath the carpet".

[aeonfox]

This is where fuzzing would be useful. We have an at-least-parity-bug-level oracle with the reference PostgreSQL implementation. Just build a generator of queries (both invalid and valid) and ensure the output matches. The yardstick is how many log10(queries) it can go on average before a discrepancy is found.

[apf6]

It’s 2026, so another basic technique that every team should add to their testing strategy (in addition to proven techniques like fuzzing) is agentic user simulation. Set up an AI agent with access to the product, and prompt it to use the thing in hundreds of realistic use cases, and to report any possible bugs. It will catch a lot of ‘blind spot’ bugs that were previously things that were only caught by humans.

[aeonfox]

Debug yourself of the AI hype^. Besides config, SQL implementations have a very limited textual API surface of accepting queries. Putting whole layers of UI and agents around it is really inefficient way of finding edge cases. I wouldn't call that basic, nor the most comprehensive option. Fuzzing at the query layer is far more computationally efficient and effective.

A truly stochastic method is more likely to hit against edge cases, rather than an agent that tends to towards idiomatic solutions and that is trained against a corpus of existing software, and burns millions of tokens/watts spinning its wheels.

^ There's plenty of business value to be found in agentic AI without reaching for it for every solution. I'd even posit agentic AI is even better when paired with focused old-fashioned squishy-brained software engineering in the loop.

[cookiengineer]

Unit tests don't test for branch coverage.

That's the culprit, because LLMs tend to forget and remove a lot of branch logic in these kinds of tasks. If unit tests don't cover these specific if/elseif/else cases, then they'll just disappear.

They'll also disappear if the LLM is allowed to modify the unit tests, because they sure like to cheat their way around into greenlit test suites. The agentic environment must disallow write access to the unit test files for the agent that writes the code.

If you implement that in your tools, you'll see quickly how the models will try to rewrite the unit tests at all cost, no matter what kind of prompting you've done. Tool policies are the only boundary to successfully guarantee this.

Source: Am building my own agentic environment because of that behavior

[Keyframe]

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.

[ramijames]

I've always played the game that tests are only as useful as you have resources to attribute to them. Mostly all modern development is a compromise between new features and stuff that supports those new features (tests included, but also reviews, code maintenance, docs, etc.).

If LLMs can be utilized to quickly make deep testing possible, I think that's probably a net-positive.

[jakswa]

what model did you try it with? I agree and also push back a bit: How will we know when the LLMs reach the point of handling it if no one takes the leap? I applaud more people sludging through the slop and hauling their slop buckets around.

An example is Fable being released. I felt like the most complex thing I was willing to sludge through was having it clone llama-server's web UI with my own opinions (I really like the original, kudos to them). And the initial skeleton was working so well I felt like I had sunk the tokens and committed to getting it the rest of the way: https://inkcap.click

[Keyframe]

Last time it was Opus 4.8 to no avail. Fable is too expen..precious to try that yet :)

[levkk]

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]

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)

[f311a]

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]

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.

[codys]

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.

[OsrsNeedsf2P]

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]

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

[tudorg]

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

[malisper]

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]

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]

pg_mooncake (now effectively abandoned due to being acquired by Databricks, but still up at 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).

Both of these were neck and neck with ClickHouse last time I tried them.

[tudorg]

Actually δx is faster than the "duckdb embedded in postgres" options: https://benchmark.clickhouse.com/#system=+_etx|_b|_i)|dula|pnc&type=-&machine=-6t|ca2|6ax|g4e|6ale|3al&cluster_size=-&opensource=-&hardware=+c&tuned=+n&metric=combined&queries=-

Plus all the normal Postgres features work as expected: physical/logical replication, crash recovery, pg_dump/pg_restore, etc.

[FreakLegion]

That isn't what the data shows, but we don't need to discuss it further. My reply was to the person interested in learning from other Postgres OLAP designs. This stuff is all pretty immature though and I wouldn't actually build on it outside of some very narrow, well-understood workloads.

[nylonstrung]

Are you using Datafusion or parts of it for query engine

[kfsone]

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

[gnull]

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?

[quadrature]

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

[malisper]

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.

[quadrature]

best of luck to you, it will be great to see a rearchitected postgres.

[jl6]

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

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

[dimes]

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]

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?

[roller]

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

[dimes]

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.

[skybrian]

At that point does it make sense to use http/3 to avoid head-of-line blocking on the network?

[ptrwis]

Not the OP, but yes - just imagine a web server talking to DB over one connection without any connection pooler

[egorfine]

> build a better version of Postgres

Faster is quantifiable. How do you measure better?

[boomskats]

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

[malisper]

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.

[barrkel]

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]

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

[solomatov]

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

[malisper]

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.

[ismailmaj]

I think I would be horrified looking at your Claude API bill.

[baranul]

Yeah, like to see more disclosures about this. Not to mention the whole background debate on the real costs for the AI companies, at what point prices go up to reflect that, or the possible consequences of being overly reliant on this technology.

[krashidov]

Did you hand write the skills or did you have an agent audit your work and infer patterns?

[malisper]

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.

[KolmogorovComp]

Thanks for the insights, are the skills available anywhere?

[solomatov]

Thanks for sharing.

[kardianos]

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

[jeltz]

404 Not Found

[kardianos]

Now public.

[mattgreenrocks]

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]

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.

[chuliomartinez]

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.

[greatony]

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.

[eu-tech-tak]

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.

[happyPersonR]

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

[malisper]

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.

[teravor]

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]

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

[AlexClickHouse]

Would you like to submit to ClickBench?

I can also do it if you would prefer...

[zX41ZdbW]

https://github.com/ClickHouse/ClickBench/pull/983

[jimbokun]

That…is really impressive. Well done!

[up2isomorphism]

300x is mostly a marketing term, especially without the test description.

BTW, showing no respect to what it is trying to copy looks uncomfortable.

[drchaim]

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

[agumonkey]

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

good luck nonetheless

[brikym]

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.

[reinitctxoffset]

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.