Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

malisper.me

Post reply on HN
Print view View original

101–110 of 168 posts

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#101

Post by malisper »

sunzhousz wrote: post #98

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" Everyone who knows a bit about databases knows the difference between execution models and what kind of optimization it brings.

> 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

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#102

Post by malisper »

patkepa wrote: post #94

Question, does having it in pure rust, opens possibility of embedding pgrust directly into binary, making it an alternative to SQLite/turso?

It absolutely can be embedded. The bigger enabler is replacing the process-per-connection model with a thread-per-connection model. Projects like pglite[0] had to give up concurrency because of it. We also support compiling to wasm so you can embed it in the browser too, which is what powers pgrust.com

[0] https://github.com/electric-sql/pglite

[1] https://pgrust.com/

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#103

Post by lossolo »

3dedb728-3f77 wrote: post #99

So one trick you can do is make a ramfs/tmpfs and start Postgres on it. You need a server with enough ram to fit it all. But it kind of make the database fly.

Or you can also just place one or more tables on tmpfs, we are doing that in production.

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#104

Post by malisper »

nz wrote: post #96
jjice wrote: post #79

Earlier quoted context omitted.

While I do like pgrust's license, I do feel like it's kind of wrong to port in such a direct way and change the license. I guess this isn't a fork, but it kind of is? It looks like according to this post [0], they did a Claude Fable + Opus re-write. I know that legally this is seemingly a valid way to do things and avoid copyright, but it feels wrong to me. I don't even necessarily think that my feelings are correct,…

My understanding is that they used c2rust, and then told the LLM/Agents to make the code more idiomatic rust, while also using the PG test suite as a feedback mechanism. This is almost certainly a derived work (and thus a fork, and should thus have the original license and copyright preserved). For example, if I compiled PG into x86-64 assembly, and then decompiled it into C (via, say, IDA), and then polished that de…

> My understanding is that they used c2rust, and then told the LLM/Agents to make the code more idiomatic rust, while also using the PG test suite as a feedback mechanism.

This is correct

> and thus a fork, and should thus have the original license and copyright preserved)

This is not correct. The Postgres license is permissive. We need to include a copy of the license (which we do in the NOTICE file[0]) but we CAN relicense the Postgres code however we want as long as we meet the requirements of the license. pgrust is a derived work of Postgres, but Postgres allows derived works to be under a different license.

[0] https://github.com/malisper/pgrust/blob/main/NOTICE

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#105

Post by jiggawatts »

Batch mode execution has been in Microsoft SQL Server for a while and just recently gained AVX-512 support.

I’ve done some experiments replacing spatial SQL queries with custom vectorised batch mode code in C# and the speed up was astonishing.

The people dubious about these claims have no idea what their computers are really capable of.

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#106

Post by malisper »

postgresperf wrote: post #95
refulgentis wrote: post #76

Earlier quoted context omitted.

Who is "we"?

The pgrust team asked me to look at their results on a review system, and I confirmed the ClickBench speedup there. Regular PostgreSQL is really terrible at some of these queries. Unfortunately fixing that is hard to do in core itself because columnar storage lives outside of the main tree, and some optimization problems only show up when layered on columnar.

^For context, this is Greg Smith, the author of Postgres 9.0 High Performance[0]. That book was my first introduction to Postgres

[0] https://www.amazon.com/dp/184951030X

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#107

Post by shdnx »

3dedb728-3f77 wrote: post #99

So one trick you can do is make a ramfs/tmpfs and start Postgres on it. You need a server with enough ram to fit it all. But it kind of make the database fly.

You can certainly do that, but it'll perform much worse than properly tuning your Postgresql to take advantage of requiring no durability and having lots of RAM. Source: I do this kind of thing for a living.

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#108

Post by up2isomorphism »

300x if it is true you will be just busy dealing with you customers rather than pitching here. Also since it is a vibe coded project, if you are really that good , you should even need to related yourself with Postgres. Who will want related itself to something that is 300x slower than itself?

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#109

Post by whateveracct »

59nadir wrote: post #100
sgt wrote: post #53

Cool project but .. reality is that people will generally not choose pgrust over Postgres, even 5-10 years from now. The problem is not that it may be technically superior and faster by then, it's that it's not built by the trusted Postgres team. There's a lot more to trust than development velocity or performance. It's also about the longevity and continuity of a critical piece of technology.

There's also the fact that it's basically vibecoded.

potemkin industry. jeez.

Re: Making Postgres 300x faster for analytics: batching, operator fusion, and SIMD

#110

Post by theplumber »

malisper wrote: post #35
andriy_koval wrote: post #32

Earlier quoted context omitted.

what is your vision of this project? Do you think pgrust will eventually be prod ready?

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.

If you are at that, add a native TTL feature as well. I think it’s been requested for decades.
Post reply on HN