Choosing a ClickHouse MergeTree Engine Variant for Time‑Series Analytics

Decision and constraints

When designing a time‑series analytics schema in ClickHouse you must decide which MergeTree engine variant best matches your operational requirements. The decision hinges on three constraints: whether you need fault‑tolerant replication, whether you want the server to pre‑aggregate data for roll‑up queries, and how you handle updates or deletes (including out‑of‑order arrivals).

Engine options comparison

d>Raw event storage when replication and aggregation are handled elsewhere

Engine	Replication	Built‑in aggregation	Update/delete handling	Out‑of‑order tolerance	Typical use case
MergeTree	None	No	Not supported (requires manual replace)	None
ReplicatedMergeTree	Yes (via ZooKeeper)	No	Not supported	None	Fault‑tolerant ingest clusters
AggregatingMergeTree	None (can be combined with Replicated)	Yes (during merges)	Not supported	None	Pre‑aggregated roll‑up tables for fast GROUP BY
CollapsingMergeTree	None (can be combined with Replicated)	No	Yes – removes consecutive rows with opposite sign	Low – requires strict order by key	Stream of updates/deletes where each change is encoded as +1/−1 rows
VersionedCollapsingMergeTree	None (can be combined with Replicated)	No	Yes – uses a version column to resolve out‑of‑order updates	High – tolerates out‑of‑order arrivals	Update streams where events may arrive late or out of sequence

Trade‑off explanation

• Replication adds fault tolerance but introduces a ZooKeeper dependency and the risk of split‑brain if the shard/replica topology is mis‑configured.
• AggregatingMergeTree reduces storage and query cost for aggregate workloads, yet it only works with aggregate functions that are both commutative and associative (e.g., sum, count, uniqCombined). Custom aggregates need a combinator or must be avoided.
• Collapsing family engines are ideal for update/delete streams but rely on a strict ordering key. Out‑of‑order inserts leave "stale" rows that persist until a background merge removes them, which can temporarily inflate storage and affect query accuracy.
• VersionedCollapsingMergeTree mitigates the ordering limitation by storing a version column; the merge logic keeps the row with the highest version. This adds a modest storage overhead for the version field and requires the application to supply monotonically increasing versions.
• TTL policies on any MergeTree variant are evaluated during merges; a high merge rate can increase CPU usage, so monitor system.metric for Merge events when enabling aggressive TTL.
• Changing the engine of an existing table requires a full rewrite (ALTER TABLE ... MODIFY ENGINE), which can be costly on large datasets; plan such changes during maintenance windows or use CREATE TABLE ... AS SELECT to migrate gradually.

Implementation and validation example

The following steps illustrate how to create a test table for each engine, load a deterministic dataset, force a merge, and verify the expected behavior. Replace placeholders with your actual values.

1. Prepare the environment – ensure you have CREATE, INSERT, SELECT, and ALTER privileges on the target database.
2. Create a base table definition (shared schema):

   CREATE TABLE IF NOT EXISTS ts_test (
       ts DateTime,
       metric Float64,
       sign Int8,          -- used by collapsing engines
       version UInt64      -- used by VersionedCollapsingMergeTree
   ) ENGINE = MergeTree()
   ORDER BY ts;
   

3. Create variants – copy the schema and change the engine:
   • ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/ts_test', '{replica}')
   • ENGINE = AggregatingMergeTree() (replace metric with AggregateFunction(sum, Float64) if you want pre‑aggregation)
   • ENGINE = CollapsingMergeTree(sign)
   • ENGINE = VersionedCollapsingMergeTree(sign, version)
4. Insert deterministic data – 100 000 rows with sequential timestamps and a constant metric:

   INSERT INTO ts_test
   SELECT
       toDateTime('2026-01-01') + number AS ts,
       12.5 AS metric,
       1 AS sign,
       number AS version
   FROM numbers(100000);
   
   

5. Force a merge to trigger background consolidation:

   OPTIMIZE TABLE ts_test FINAL;
   
   

6. Verify results – query system.parts to see part counts and row counts:

   SELECT
       engine,
       count() AS part_count,
       sum(rows) AS total_rows
   FROM system.parts
   WHERE database = 'your_db' AND table = 'ts_test'
   GROUP BY engine
   ORDER BY engine;
   
   

7. Interpretation
   • For MergeTree and ReplicatedMergeTree you should see roughly the same row count as inserted (≈100 000) and a small number of parts after the final merge.
   • For AggregatingMergeTree (if you defined metric as an aggregate function) the row count will be lower because rows with the same ts are collapsed during the merge.
   • For CollapsingMergeTree the row count will depend on the sign column; with all signs set to +1 you will see no collapsing, but inserting pairs of +1/−1 for the same key will reduce the count after merge.
   • For VersionedCollapsingMergeTree the highest version per key survives, so with monotonically increasing version you will also see a reduced row count after merge.
8. Check merge activity – look in the server log for lines containing Merging parts to confirm that background merges ran as expected.
9. Risks to monitor
   • High merge CPU usage – watch system.metric for Merge spikes.
   • ZooKeeper misconfiguration – verify replica synchronization by inserting on one replica and checking SELECT * FROM ts_test on the other after a short lag.
   • Stale rows in collapsing engines – if you suspect out‑of‑order inserts, query the table for rows with unexpected signs or versions.

By following this pattern you can empirically confirm which MergeTree variant satisfies your replication, aggregation, and update‑handling requirements before committing to a production schema.