TopolearnThese engines replace UPDATE and pre-compute aggregates, which is how ClickHouse models mutable and summarized data.
The MergeTree family adds logic to merges. When parts with equal sort keys merge, the engine combines those rows.
- ReplacingMergeTree(ver) keeps only the row with the highest
ver(or the last inserted) perORDER BYkey. To "update" a row you insert a new version. An optionalis_deletedcolumn lets a version act as a delete. - SummingMergeTree sums numeric columns of rows with the same key: a cheap counter table.
- AggregatingMergeTree stores
AggregateFunction(uniq, UInt64)state columns and merges them, for any aggregate (unique users, quantiles).SimpleAggregateFunction(sum, UInt64)is the lightweight form for functions like sum, min, max. - CollapsingMergeTree and VersionedCollapsingMergeTree cancel rows with a
signof +1 and -1.
The crucial gotcha: merges happen eventually, at unknown times, and only within a partition. Until then duplicates or unsummed rows are visible. Queries must therefore aggregate anyway:
SELECT ... FROM t FINALmerges on the fly at read time. It is much cheaper in modern versions but still costs extra work.- Or write the query to be correct regardless:
argMax(col, ver) ... GROUP BY keyfor Replacing,sum(...) GROUP BY keyfor Summing,uniqMerge(state)for Aggregating.
OPTIMIZE TABLE ... FINAL forces a merge but is heavy; never rely on it in production queries.
Example
CREATE TABLE user_profile (id UInt64, plan String, ver UInt64)
ENGINE = ReplacingMergeTree(ver) ORDER BY id;
INSERT INTO user_profile VALUES (1, 'free', 1);
INSERT INTO user_profile VALUES (1, 'pro', 2); -- an "update" is a new version
SELECT count() FROM user_profile; -- 2 (not merged yet)
SELECT * FROM user_profile FINAL; -- 1 pro 2
SELECT id, argMax(plan, ver) FROM user_profile GROUP BY id; -- 1 pro
CREATE TABLE daily_users
(day Date, users AggregateFunction(uniq, UInt64))
ENGINE = AggregatingMergeTree ORDER BY day;
INSERT INTO daily_users SELECT today(), uniqState(toUInt64(number % 10)) FROM numbers(100);
INSERT INTO daily_users SELECT today(), uniqState(toUInt64(number % 20)) FROM numbers(100);
SELECT day, uniqMerge(users) FROM daily_users GROUP BY day; -- today 20Remember
- Engines combine rows with equal sort keys during merges
- Merges are eventual: always read with FINAL or an aggregating query
- AggregatingMergeTree stores -State values; read them with -Merge
Watch out for
- Assuming ReplacingMergeTree guarantees uniqueness at query time without FINAL or GROUP BY.