I appreciate that they call this out. This means that if you're using Postgres primarily for storing data where it's ok if you lose a few (like time series data) then Clickhouse is the clear and obvious choice.
But if the transactionality is key for you (like a financial application or the data for an application) then Postgres still makes more sense.
ClickHouse defaults to: "fsync_after_insert = false".
A more fair comparison might at least do "SET synchronous_commit TO OFF" on the PostgreSQL side. (But for this UPDATE benchmark, I think the results would still largely be the same.)
Personally, I architect systems to use Postgres for the write-heavy parts of a workload, and ClickHouse for the write-once parts (time series, analytics, logs, etc). ClickHouse is also the best tool I've ever found for compressing enormous datasets, and is very useful even as a simple data warehouse.
If it needs to be there, use a a fully transactional database.
As long as you can get away with Postgres, stay with Postgres. I'm sure this update here is a step forward just like version-merging is much better than cancelling rows but it's always got a ton of downsides.
Unrelated to updating data, the CH defaults drive me insane, the null join behavior alone made me reconsider trying to rip CH out of our infrastructure (after wasting too long trying to figure out why my query "wasn't working").
Lastly I'll say, if CH does what you need and you are comfortable learning all the ends and outs, then it can do some really cool things. But it's important to remember it's NOT a normal RDMS nor can you use it like one. I almost wish they didn't use SQL as the query language, then people would think about it differently, myself included.
We’ve been doing our best to address and clarify these differences, whether through product features like this one or by publishing content to educate users. For example: https://clickhouse.com/blog/postgres-to-clickhouse-data-mode... https://www.youtube.com/watch?v=9ipwqfuBEbc.
From what we’ve observed, the learning curve typically ranges from a few weeks for smaller to medium migrations to 1–2 months for larger ones moving real-time OLAP workloads from Postgres to ClickHouse. Still, customers are making the switch and finding value — hundreds (or more) are using both technologies together to scale their real-time applications: Postgres for low-latency, high-throughput transactions and ClickHouse for blazing-fast (100x faster) analytics.
We’re actively working to bridge the gap between the two systems, with features like faster UPDATEs, enhanced JOINs and more. That’s why I’m not sure your comment is fully generalizable — the differences largely stem from the distinct workloads they support, and we’re making steady progress in narrowing that gap.
- Sai from the ClickHouse team here.
For general mutable data, ClickHouse is trying super hard to get much better & doing amazing engineering. But it feels like it'll be a long time before the fortress of Postgres for OLTP is breached. https://about.gitlab.com/blog/two-sizes-fit-most-postgresql-... https://news.ycombinator.com/item?id=44895954
The top submission is the end of a 4 part series. Part two is really nice on the details of how ClickHouse has focused on speeding updates: recommend a read! https://clickhouse.com/blog/updates-in-clickhouse-2-sql-styl...
Either way, CH shouldn't be the store of truth when you need record level fidelity.
If you are needing updates then perhaps ClickHouse isn't the perfect choice. Something like ScyllaDB might be a better compromise if you want performant updates with (some sort of) consistency guarantees. If you need stronger guarantees you will need a "proper" database but then you're unlikely to get the performance. AKA tradeoffs or no free lunch.
CH has been my favorite database since I discovered PostgreSQL 20 years ago. My view point is don't use postgres unless you can't use CH.
Basically, every proposed use case for CH is based on event sourcing some data, from Postgres or logs or whatever. The implication is that the data either already exist as a "source of truth" in some primary ACID database, or at least there is an archive of raw data files, or maybe (as with logs and metrics) the risk of data loss isn't that big of a deal.
But what if you actually want to store the data in a single place? CH doesn't really offer peace of mind here. Its entire architecture is based on best-effort management of data. One of ClickHouse's best features is that it can store the data in cloud storage, to allow separation of data and compute at an incredible price point. But it can lose data.
So if you have, say, 30TB of data that is very columnar and cannot be effficiently queried in Postgres, you cannot simply store it in CH alone. You'd have to pay quite a lot of $ to have it safely guarded by (let's say) Postgres, even if it's not being used as the main source of queries. If you have heavy ingest rates, you're going to have to pay for more expensive SSD storage, too.
There are columnar databases that are ACID and focus on consistency, like TimescaleDB. But they tend to be cloud databases. For example, you can self-host Timescale, but you don't get access to the tiered cloud storage layer. So when self-hosting, you need to run expensive SSDs again, no separating of compute and data.
If CH has a better consistency story, or maybe a clustering story that ensures redundancy, I would be really inclined to use it as a primary store.
The omission feels… odd.
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).
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'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!
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.
