ClickHouse Append-Only Hinders Upserts
Product manager at Firebolt on on scaling challenges and ACID compliance in OLAP databases
This is less about a missing feature than about where ClickHouse was built to win. ClickHouse is strongest when data arrives as a steady stream of new events, like logs, clicks, and dashboard metrics, because it stores new data in parts and cleans things up later in background merges. Once a workload starts behaving like an app database, where rows must be changed in place and committed reliably, that storage model becomes heavier and less natural.
-
In practice, ClickHouse can do updates and upserts, but they are implemented through mutations, ReplacingMergeTree style engines, or newer lightweight update paths. The tradeoff is that updates are still tied to background part rewrites or merge logic, which adds I/O, disk cleanup work, and operational complexity compared with a system built around row changes from the start.
-
That is why ClickHouse has spread first in observability and embedded analytics. Those workloads are mostly append heavy, need very fast scans, and can tolerate eventual cleanup. The same interview describes customers using ClickHouse for logs and keeping 30 days of retention at lower cost than OpenSearch, which fits the append first design very well.
-
The competitive opening is for databases that try to combine app style writes with OLAP speed. Firebolt positions itself there with ACID compliant storage and upserts as a native primitive, while SingleStore is another nearby comparable built around mixed transactional and analytical workloads. ClickHouse is still winning a different lane, real time analytics on very large event data.
Going forward, ClickHouse will keep expanding its update machinery, but its center of gravity remains high speed analytics over append heavy data. The market is splitting more clearly between engines optimized for event streams and engines optimized for mixed HTAP style workloads, and that makes this architectural difference more important as customers try to collapse multiple databases into one system.