CollapsingMergeTree and VersionedCollapsingMergeTree

The trade-off

A MergeTree part never changes once written — there's no in-place UPDATE, no in-place DELETE. replacingmergetree handled that constraint for full-row replacement by inserting a whole new version and letting a background merge discard the old one. The collapsing engines handle a narrower, harder problem under the same constraint: retracting one specific prior contribution to a running total, without ever touching the row that contribution came from.

The mechanism is a pair of rows sharing a sorting key, distinguished by a Sign column: a state row (Sign = 1) carries a current valid state, and a cancel row (Sign = -1) cancels a specific prior state. During a background merge, ClickHouse looks for rows sharing a sorting key that are identical in every column except Sign, and removes matched pairs. What you're trading for that: no true in-place update ever happens, which keeps the engine append-only and merge-friendly — but the writer that wants to cancel a row has to be able to reproduce that row's exact prior field values, and (for plain CollapsingMergeTree) insert the pair in the right relative order. That reproduction-and-ordering burden is the real cost, and it's the central judgment call this lesson is about.

The alternatives, steelmanned

ReplacingMergeTree — full-row replacement. The case for it: dead simple. A writer that always has the complete, current state of an object just inserts it, and the newest version wins on merge. This is the right tool whenever a single source of truth already holds the full row — a CDC stream reflecting an upstream table's current state is the canonical fit, per the last lesson. Its weakness here is structural, not a matter of preference: if state accretes from many small, independent updates and no single writer ever holds the authoritative complete total — three separate services each occasionally nudging a shared counter — there's no "latest full row" to insert. You'd need a read-modify-write cycle to construct one, which fights an OLAP engine's write-only ingestion model directly.

Query-time signed-sum on a plain MergeTree — SUM(value * Sign) GROUP BY key. Store every delta as its own row, positive or negative, on an ordinary MergeTree, and let the aggregate resolve the current total fresh on every query. The case for it: zero ordering constraints, of any kind — no pair has to be inserted before, after, or anywhere near its counterpart, because nothing needs to be found and collapsed at merge time at all. It also doesn't depend on the collapsing engines' merge logic existing at all, unlike the option below. The cost: the table keeps every delta row forever unless something else prunes it (a TTL, a periodic partition drop), and a query has to sum the entire delta history for a key rather than reading a handful of already-collapsed rows — real, compounding cost on high-churn keys as their delta count grows without bound.

FINAL on a CollapsingMergeTree or VersionedCollapsingMergeTree table. FINAL isn't unique to ReplacingMergeTree — it applies to both collapsing engines too, forcing ClickHouse to fully merge the relevant data, running the same collapse logic a background merge would, before returning a result. The case for it: you keep the collapsing engine's storage-side benefit (bounded growth from ordinary background merges) and only pay the query-time collapse cost on the specific reads that need a hard guarantee — the same targeted trade replacingmergetree recommended for FINAL there. The cost is the same shape too: real, per-query compute that scales with how much unmerged data that query has to resolve. Between this and the plain-MergeTree signed-sum option above, neither is uniquely "the" same-instant guarantee — both are, which is exactly why the decision framework below treats "does any query need that guarantee" as a separate question from "which engine stores the table."

SummingMergeTree — for when nothing ever needs retracting. The next lesson covers it properly, but its case belongs here too: if a metric only ever accumulates — a raw event count that's never corrected or reversed — SummingMergeTree sums numeric columns sharing a sorting key on merge, with no Sign column, no cancel rows, no pairing logic at all. It's genuinely simpler whenever retraction is never actually needed. Its limitation is exactly that: no way to undo or correct one specific earlier contribution, only ever add more.

CollapsingMergeTree — background-compacted retraction, strict order required. The case for it: real merge-time compaction (bounded growth as pairs cancel, the same benefit ReplacingMergeTree gets), while genuinely supporting "retract this specific prior state," not just "add more." The cost is a real ordering requirement: ClickHouse's own comparison of the two engines states plainly that "CollapsingMergeTree allows only strictly consecutive insertion" of a cancel row after its corresponding state row for the same key, while VersionedCollapsingMergeTree "allows inserting the data in any order with multiple threads." Violate that ordering — a cancel row for a key that gets processed before its state row, easy to do with uncoordinated concurrent writers — and the pair can fail to collapse, which doesn't just leave stale duplicates the way an unmerged ReplacingMergeTree row does; it can leave a query's SUM(Sign) or SUM(value * Sign) silently wrong.

VersionedCollapsingMergeTree — the same idea, order-independent. Adds an explicit Version column, incremented for each new state of an object. On merge, ClickHouse "deletes each pair of rows that have the same primary key and version and different Sign. The order of rows does not matter." That removes the insertion-order constraint entirely — multiple threads, multiple services, no coordination needed. The cost: one more column every writer has to assign correctly, and getting that wrong reintroduces a close cousin of the "version column with the wrong meaning" mistake from replacingmergetree — a Version that doesn't reflect genuine state ordering breaks the pairing just as surely as bad insertion order breaks plain CollapsingMergeTree.

Decision framework

The reasoning a senior engineer runs through, in order:

1. Does the writer always know the complete current state of the row, or only what changed? Complete state, every time → ReplacingMergeTree is probably already enough; "the state changes over time" alone isn't sufficient reason to reach for a collapsing engine.
2. If only deltas are known, does any contribution ever need to be retracted or corrected — or is the metric purely additive? Purely additive, nothing ever reversed → SummingMergeTree (next lesson) is simpler and has no ordering story to worry about at all.
3. If retraction is real, can every writer across every thread and service guarantee relative insert order for a cancel/state pair sharing a key? A single, strictly-ordered writer per key (say, one Kafka consumer per partition, partitioned by the sorting key) can make plain CollapsingMergeTree viable. Multiple uncoordinated writers for the same key almost always means VersionedCollapsingMergeTree, or reconsidering the query-time signed-sum approach if churn is low enough to afford it.
4. Does any query need a guarantee that holds at every instant, independent of whether a merge has run? Same question as the ReplacingMergeTree lesson, and the same two answers: FINAL on the chosen collapsing engine's table, paid only on the specific queries that need it, or a plain MergeTree with query-time SUM(value * Sign) if you'd rather not depend on the collapsing engine's merge logic at all. Both cost real, per-query compute; neither is free just because it isn't a background merge.

Watching a pair collapse

CREATE TABLE UAct
(
    UserID     UInt64,
    PageViews  UInt8,
    Duration   UInt8,
    Sign       Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1);
-- Later: this user's session actually ran longer. Cancel the old state,
-- insert the corrected one — never an UPDATE.
INSERT INTO UAct VALUES
    (4324182021466249494, 5, 146, -1),
    (4324182021466249494, 6, 185, 1);

After a merge, only (4324182021466249494, 6, 185, 1) remains: the cancel row and the old state row it targets are byte-identical except Sign, so they collapse away, leaving the corrected state. When more than two rows share a key, ClickHouse's exact rule matters, not just the intuition of "opposite signs cancel": a merge reduces a run of same-key rows to at most two, keeping the first cancel row and the last state row if state and cancel counts match and the run ends on a state row; the last state row alone if states outnumber cancels; the first cancel row alone if cancels outnumber states; and none of them, unchanged, in every other case. The pairing isn't "does a plus-one exist somewhere for every minus-one" in the abstract — it's a specific, order-sensitive reduction over whatever rows a given merge happens to see together.

Case studies

A good fit, straight from ClickHouse's own reference example. The official CollapsingMergeTree documentation's own worked example — the UAct table used earlier in this lesson — is framed around exactly the workload the engine was built for: "we want to calculate how many pages users checked on some website and how long they visited them for," where a session's stats get corrected in place by canceling the old totals and restating new ones. That's a strong signal for the workload shape this lesson has been building toward: a single logical entity (one user's session) whose contribution to a report needs occasional, in-place correction, not endless raw accumulation.

A bad fit, documented in public. GitHub issue #13930 against the ClickHouse repository is a real, filed report of a user hitting the ordering requirement directly: rows failed to collapse because a Sign = -1 row was inserted before its Sign = 1 counterpart, with the reporter's own expectation — "it shouldn't make any difference... as both rows during a merge should cancel each other" — turning out to be exactly wrong for the plain engine. It's a small, concrete instance of the same mistake this lesson's Misconception and Cold Start scenario are built around: assuming a background process is forgiving about the one thing plain CollapsingMergeTree is specifically not forgiving about.

Anti-pattern gallery

Querying with SUM(value) instead of SUM(value * Sign). This is the collapsing-engine-specific mistake that has no real equivalent in ReplacingMergeTree: on an unmerged table, cancel rows are still physically present, still carrying their old field values, and a plain sum over the value column double-counts (or worse, counts a canceled contribution as if it were still current) unless the query multiplies by Sign — or waits for a merge and trusts the collapse to already be complete, which has the same eventual-consistency risk as every other assumption of instant merging in this module.

Assuming collapse happens synchronously, the same mistake ReplacingMergeTree warned about, with a sharper consequence. An unmerged ReplacingMergeTree table shows a stale duplicate row — visibly odd, but a count(DISTINCT key) is still roughly right. An unmerged pair of CollapsingMergeTree rows, summed without accounting for Sign, can produce a number that's confidently wrong rather than obviously duplicated, which is a harder failure to catch by inspection.

Reconstructing an approximate "old" row instead of remembering the exact one. Collapsing depends on the cancel row matching the state row it targets in every column except Sign — ClickHouse's own guidance is direct: "the program that writes the data should remember the state of an object to be able to cancel it." A cancel row built from a slightly-stale read of "what I think the old state was" rather than the actual previously-inserted row simply won't match, and the pair silently never collapses.

Reaching for CollapsingMergeTree when the real requirement is ReplacingMergeTree's. Not every "this row changes over time" workload needs sign-based retraction — if a writer always has the complete current state, restating the whole row (ReplacingMergeTree) is simpler and has no ordering story at all. The collapsing engines earn their added complexity only when no single writer holds that complete state.