Comment by sdairs | Hacker News Reader

Comment by sdairs | Hacker News Reader

One of the post authors here; I just want to stress that the goal of the post was not at all to be "haha we're better than postgres" - we explicitly call out the caveats behind the results.

Prior to July 2025 (ClickHouse v25.7), ClickHouse did not support UPDATE statements. At all. Like most columnar, analytics databases. We spent a lot of effort designing and implementing a way to support high-performance, SQL-standard UPDATE statements. This test was pretty much just trying to see how good of a job we had done, by comparing ourselves to the gold standard, Postgres. (If you're curious, we also wrote about how we built the UPDATE support in depth https://clickhouse.com/blog/updates-in-clickhouse-2-sql-styl...)

We have some updates to the post in progress; we originally deliberately used cold runs for both ClickHouse & Postgres, because we wanted to look at the "raw" update speed of the engine, vs. the variability of cache hits. But TL;DR when you run a more "real world" test where caches are warm and Postgres is getting very high cache-hit ratio, its point updates are consistently ~2ms, while ClickHouse is somewhere ~6ms (bulk updates are still many multiples faster in ClickHouse even with the cache in play).

Another thought: this is not meant as criticism or anything, just an aspect of performance that I thought was interesting but not covered by the blog.

A test that would show PG's strengths over ClickHouse for OLTP would be a stress test with a long-running set of updates.

ClickHouse maintains updates as uncompacted patches merged in the background, which is how you would do it with a columnar store. But if you have an update-heavy workload, these patches would accumulate and your query performance would start to suffer. PG on the other hand completes all update work inline, and wouldn't get degrading performance under update-heavy regimes.

This is just a fundamental artifact of OLAP vs OLTP, maybe OLAP can be optimized to the point where it doesn't really matter for most workloads, but a theoretical edge remains with row-based stores and updates.

I think we do allude this in the post, if my understanding is correct. AFAIK both ClickHouse and Postgres are using MVCC, so when an UPDATE is run, a new version of the data is written and the original data is not changed. In ClickHouse, we write a patch part, it's merged automatically by a background process or by FINAL. In Postgres, you write a new tuple and mark the old one as dead, which will be cleared up by a VACUUM.

I'd wager the overhead in Postgres is probably lighter (though it's also light in ClickHouse), so you're right, this would be an interesting test. We actually had something like that planned, to run concurrent UPDATEs and SELECTs at different volumes for a period of time, to see how they each cope. Will definitely do it!

Really interesting post, and well done on setting up the Apples to Oranges nature of the benchmark, you're very clear. It's really interesting to see the deference the district architectures make.

Did you run any tests with the new transaction system in ClickHouse? It would be super interesting to see how it effected the batch updates.

If you're refering to the experimental ACID transactions, we didn't test them; I'll check what state they're in and see if its worth including yet, if not, we'll for sure come back to them later and do a similar test!

I haven’t read that post yet. I assume you merge the updates into the main data files when you merge the segments of the log-structured merge tree?

Yeah that's right. We create "patch parts" that contain the updates, and merge them along with the usual merge process.

I see the Jepsen for Keeper (and, my sincere gratitude for it!); have you considered Jepsen for CH itself?

Reply on news.ycombinator.com