malisper

> I am very disappointed to see the direction: It is moving from a "interesting attempt to recreate system software" to "building flashy but useless demo"

What makes you say this is a useless demo? I can't count the number of people who've struggled to do analytics inside of Postgres. Almost always they end up setting up a separate system such as Clickhouse and replicating the data between the two systems. Now they can have one system that's Postgres-compatible, and it's faster than either of the original systems.

> Everyone who knows a bit about databases knows the difference between execution models and what kind of optimization it brings.

In our last post[0], when we mentioned we were getting close to Clickhouse level performance (now faster than Clickhouse), we were met with disbelief. This post is meant to explain part of how we closed the 300x gap between Postgres and Clickhouse. The execution model being 10x of it.

[0] https://news.ycombinator.com/item?id=48841676

One of the new features we recently built is "test mode". This brings cloning a template db from 100ms down to <10ms making it much better for tests.

If you're interested in trying it out, please reach out to me at malis@pgrust.com

Me and Jason, the two people working on the project

We disabled parallelism in the blog post for demonstration purposes. The 300x slower refers to the clickbench numbers[0] where parallelism is enabled

[0] https://benchmark.clickhouse.com/#system=+liH|pgrs|gQ&type=-...

I would probably dig into the reasons for the differences in the benefit on the test machine and in prod

I had an issue like this for optimizing pgrust. I had an optimization that showed no impact on my test machine (c8g.4xl) and showed a 20% improvement when ran on my mac. It turns out the issue was the instruction cache on the c8g.4xl was being saturated on the test machine but not on my laptop, moving the bottleneck to a different place

If you can consistently reproduce the performance difference, you're already half way there

It's a reference to the prompt that found a counterexample to the Dinitz-Garg-Goemans conjecture

> "do a breakthrough and find a structured counterexample"

> A question on 20s postgresql time - It does not look like you are accounting for reading data from disk

I choose the data size so that it would fit in memory on the machine I was testing on. fwiw, there's still a ton of overhead Postgres has that the toy example does not. For example Postgres will serialize the numbers into tuples and need to deserialize them to execute the query. That's why it's not an apples-to-apples comparison

I want to build the best database possible. While Postgres is great, there are a lot of core issues that have been around for over a decade. We're working hard to get pgrust production-ready, and it will definitely be production-ready in the near future. I wouldn't be putting hundreds of thousands of dollars into this project if I didn't think we could build a production-ready database.

I'll need to write up how the scheduler works at some point, but it's heavily based on these papers[0][1]. It solves two different problems. First, it lets us throttle resource-intensive queries. Second, it enables work stealing. If you have idle cores on your machine, we'll assign those cores to running queries to help speed them up. That means if you have an over-provisioned machine, we'll make use of the extra capacity to speed your queries up.

[0] https://15721.courses.cs.cmu.edu/spring2016/papers/p743-leis...

[1] https://db.in.tum.de/~kohn/papers/query-scheduling-sigmod21....

At least in terms of speed, we're much faster on clickbench: https://benchmark.clickhouse.com/#system=+_b|pnc|pgrs|gQ|saB...

pgrcolumnar is not the default storage method. Right now, it's exposed as a table access method. There's lots of design space for how to do this so I want to avoid pre-committing to anything

You can try it. We're happy to help you with it, but expect there to be issues to work through. You would want to do it for something non-critical

Author here. Let me know if you have any questions about the post or about pgrust.

Let me take a shot at answering what I think will be the most common question: how can I trust pgrust? Our #1 priority right now is correctness. Over the past two weeks, I've done a mix of formal verification and differential fuzz testing. We've been able to prove over 1000 user facing functions have the exact same logic in both pgrust and postgres (see the proofs directory if you're curious). For cases where formal verification is not easy, we've taken the c implementation of a function and the rust implementation of a function and ran millions of inputs through each of them and confirmed they gave the same results every time.

We've only covered about 15% of the surface area so far, but in the process, we've discovered ~100 bugs in pgrust and ~20 bugs in Postgres itself. My favorite postgres bug we found is this one[0]. Postgres has a quadtree implementation. Due to floating point rounding, it was possible for a point to be neither above, nor below, nor even with the center point of the quadtree.

We've also entered engagements with Antithesis[1] to do Jepsen style fault testing and Aretta[2] to do more serious formal verification.

If you want to support the project, the easiest way is to give us a star on GitHub[3]

[0] https://www.postgresql.org/message-id/19597-39c532e61d78dff6...

[1] https://antithesis.com/

[2] https://aretta.ai/

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

Author here. At least for databases, AGPL (or stricter) has become standard. The issue is it's so easy for megacorps (Amazon, Google, etc) to take a permissively licensed product and monetize it at the expense of the original standard.

For instance, Mongo, Cockroach, and Materialize have all gone source available. We picked AGPL because it's the best balance between open source and prevents Amazon from just repackaging it and selling it.

If AGPL is an issue for anyone, we would be happy to dual-license under a commercial license.

Having tried all the coding harnesses, I find that using Pi is exactly like using Emacs. For anything you want to build you can ask your agent and it will build it. There's tons of existing code to help you configure it. At the same time half the code is buggy, UI elements will try to overlap one another, and you'll periodically get crashes.

If you're willing to put in the work to master the learning curve and push through the issues, it can be a great tool: https://i.sstatic.net/7Cu9Z.jpg

Done! https://github.com/ClickHouse/ClickBench/pull/1163

> I think the postgresql maintainers don't claim to support moving a database from x86 to arm without a dump-and-reload

I would be very surprised by that because that means replicating a database between the two platforms would lead to corruption.

btw, this bug breaks dump-and-reload too. If you use partitioning, each partition is dumped and restored individually. In this case though, you'll get an error when you try to restore the data because it's trying to put data in the wrong partition. That's better corruption, but still an issue.

Right now I'm only doing very small simple functions. Kani[0] takes care of translating the code to an intermediate representation for me. It converts the Rust code and C code into a GOTO program[1] which verifiers can then run on top of

[0] https://github.com/model-checking/kani

[1] https://model-checking.github.io/cbmc-training/cbmc/overview...

Once it's stable, that's definitely something I'm going to look at doing

At least for ClickBench, the remaining bottleneck is memory bandwidth. I think that's mostly going to be solved by better data representations than SIMD.

One of the biggest wins was our hash table implementation. Depending on the cardinality of the data, it switches between design that is optimized for L2 cache vs something that is outside of L2.

Thank you for the kind words!

My goal is to build the best database possible. I'm trying to imagine what Postgres would be like if it were built today. I've been able to make a bunch of big architectural changes that the Postgres team has been talking about but hasn't yet made. For example, threads instead of processes and a vectorized executor.

I think everyone would agree the types of changes I'm making are good ones. The challenge Postgres faces is there's millions and millions of Postgres databases out there so they are focused on minimizing the risk of breaking any existing functionality over doing a big high risk rearchitecture.

I could see ideas from what I'm doing gradually making their way into Postgres, but I think the odds that pgrust (or any Rust code for that matter) gets merged into Postgres is close to zero.

Yep, I did submit bug reports. This was on Tuesday. I don't see a public copy of the mailing list that has my bug reports yet. The four bugs were:

0) When parsing a macaddr[0], Postgres uses sscanf with %x. %x can wraparound. This means SELECT '10000000aa:bb:cc:dd:ee:ff'::macaddr; will return aa:bb:cc:dd:ee:ff.

1) When parsing a tid[1], Postgres uses strtoul. The return value of strtoul is different across platform for the empty string. This means on some platforms Postgres SELECT '(,5)'::tid; will error and others will accept it.

2) Postgres missed an overflow check in it's cash type[2]. When running SELECT '-92233720368547758.08'::money / (-1)::int8; some platforms will error and other's will return the MIN value. Postgres does check for this for some of the other cash related functions, but it missed it for one of them.

3) When hashing the "char" type Postgres will cast a char to an integer[3]. On some platforms char is signed and on others it's unsigned. This means the hash of a char can be different depending on the platform. If you are using a hash index or hash partitioning on a char and move your DB from x86 to arm, the hashes will differ and your index/partitioned tables become corrupted. Note that this is special char type that you have to refer to by "char" that is separate from the typically used CHAR(n) type which is what you typically use, hence this would never come up under real usage.

The common pattern with all of these is they rely on C behavior that differs across platform (integer overflow, char signedness, strtoul). Rust is better about having more consistent behavior across platforms so these cases get flagged when the Rust code and the C code differ.

[0] https://github.com/postgres/postgres/blob/REL_18_3/src/backe...

[1] https://github.com/postgres/postgres/blob/REL_18_3/src/backe...

[2] https://github.com/postgres/postgres/blob/REL_18_3/src/backe...

[3] https://github.com/postgres/postgres/blob/REL_18_3/src/backe...

I recently came across a use case where formal methods were incredibly helpful. I've been rewriting Postgres in Rust and am currently focusing on correctness. The biggest challenge is that there's so much surface area to cover. Postgres has over 3000 user-facing functions, ranging from regular expression matching to JSON iteration to computing the gamma function. About half of these functions are simple pure functions.

Of the 3000 functions, I've been able to formally verify that the Rust behavior is identical to the Postgres C behavior for over 1000 of them. In the process, I found 4 different Postgres bugs. All of them would not be triggered under ordinary usage, but one, if triggered, would corrupt your database.

I think why formal methods works well for this is I'm testing a large number of small to medium self-contained pieces of code. For each of them the specification is simple: does postgres_fn(args) == pgrust_fn(args). I've been using Kani[0] which works across both Rust and C code so the proofs are based off the actual code and not a translation of the code to another language.

If you want to check out what all the verification look like, you can see them here[1]

[0] https://github.com/model-checking/kani

[1] https://github.com/malisper/pgrust/tree/main/proofs

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!