Choosing the Right MergeTree Engine for ClickHouse Time‑Series Workloads

Decision Context

Time‑series workloads in ClickHouse involve high‑volume writes, occasional duplicates, and a need for efficient aggregation. The MergeTree engine you choose directly affects how data is stored, how merges are performed, and how fast queries can run. The decision hinges on four constraints:

• Do you need to deduplicate rows or keep only the most recent value per key?
• Do you need automatic summation of incremental counters?
• Do you require complex aggregate functions (average, quantile) to be stored in state form?
• Do you work with event streams that include explicit insert/delete pairs?

Based on these constraints, you can narrow the engine selection to one of the five MergeTree variants.

Engine Comparison Table

Engine	Primary Use	Merge Logic	Data Loss Risk	Write Performance	Query Performance	Typical Use Cases
MergeTree	Raw row storage	Sort by primary key	None (unless disk failure)	Fast (no merge side‑effects)	Standard (requires aggregation in SELECT)	General purpose, log ingestion
ReplacingMergeTree	Deduplication (keep latest)	Keeps row with max version per key	Wrong version values can drop data	Similar to MergeTree (extra column)	Metrics that may arrive out of order
SummingMergeTree	Incremental counters	Automatically sums numeric columns per key	Non‑numeric columns ignored; wrong key leads to duplicates	Fast (no user aggregation)	High‑frequency counters, counters per device
AggregatingMergeTree	Storing aggregate state	Merges AggregateFunction states	Requires correct state types; misuse can corrupt	Fast writes (state stored as blob)	Complex metrics (avg, quantile) that need pre‑aggregation
CollapsingMergeTree	Event streams with insert/delete pairs	Collapses rows with same key and opposite sign	Incorrect sign values can leave orphaned rows	Same as MergeTree (extra sign column)	Log streams, inventory changes

Trade‑Offs Explained

MergeTree vs. ReplacingMergeTree

ReplacingMergeTree adds a version column but otherwise behaves like MergeTree. It keeps only the row with the highest version per primary key after a merge. This is ideal when you receive late or duplicate events and want to keep the newest record. The risk is that an incorrect version (e.g., a non‑monotonic timestamp) may cause unintended data loss.

SummingMergeTree vs. AggregatingMergeTree

SummingMergeTree is lightweight: it sums only numeric columns and ignores the rest. It is perfect for simple counters but unsuitable for averages or percentiles. AggregatingMergeTree stores AggregateFunction state objects, enabling complex aggregates. However, you must use aggregateFunctionMergeState (or aggregateFunctionMerge) in SELECTs; otherwise you see raw state blobs.

CollapsingMergeTree

Use this only when your data model includes explicit insert/delete semantics. The sign column (1 or -1) tells the engine how to collapse rows. Incorrect sign values can leave stale rows, so strict data validation is mandatory.

Concrete Implementation: Deduplicated Counters with ReplacingMergeTree

Below is a step‑by‑step example that demonstrates how to create a ReplacingMergeTree table, insert duplicate rows, trigger a merge, and verify that only the latest row per key remains.

1. Create the Table

CREATE TABLE IF NOT EXISTS device_metrics (
    ts DateTime,
    device_id UInt64,
    metric_name String,
    value UInt64,
    version UInt64
) ENGINE = ReplacingMergeTree(version)
PARTITION BY toYYYYMM(ts)
ORDER BY (ts, device_id, metric_name);

Run this on the ClickHouse client (clickhouse-client) as a user with CREATE TABLE privileges. The version column should contain monotonically increasing values for each device_id, metric_name pair.

2. Insert Sample Data (including duplicates)

INSERT INTO device_metrics VALUES
    ('2026-10-15 10:00:00', 1001, 'cpu_usage', 30, 1),
    ('2026-10-15 10:00:00', 1001, 'cpu_usage', 35, 2),  -- duplicate key, newer version
    ('2026-10-15 10:05:00', 1002, 'cpu_usage', 40, 1);

After insertion, the table contains two rows for device_id=1001, metric_name='cpu_usage'. The merge process will later keep only the row with version=2.

3. Trigger a Merge Manually (Optional)

ClickHouse merges asynchronously, but you can force a merge to see the effect immediately:

SYSTEM MERGE device_metrics;

Use SYSTEM SHOW MERGES to confirm that a merge is in progress or has completed.

4. Verify the Result

SELECT * FROM device_metrics
ORDER BY device_id, metric_name;

Expected output (simplified):

ts	device_id	metric_name	value	version
2026-10-15 10:00:00	1001	cpu_usage	35	2
2026-10-15 10:05:00	1002	cpu_usage	40	1

The row with value=30 has been discarded because its version was lower.

5. Check Merge Activity

SYSTEM SHOW MERGES WHERE table = 'device_metrics';

Look for rows with status='completed'. If you see many pending merges, your write throughput may be outpacing merge speed, leading to temporary storage bloat.

Limitations and Practical Checks

• Asynchronous Merges: High write rates can cause merge lag. Monitor system.merges regularly.
• Correct Versioning: Use a strictly increasing sequence (e.g., event timestamp or running counter). A duplicate version will keep the older row.
• Non‑numeric Columns: ReplacingMergeTree retains all columns; if you need to drop columns, use ALTER TABLE after a merge.
• AggregateFunctionState: For AggregatingMergeTree, always call aggregateFunctionMergeState in SELECT to obtain the final value.

How to Validate Merge Logic in Production

1. Insert a controlled batch of rows with known version values.
2. Run SYSTEM SHOW MERGES to confirm a merge occurs.
3. Query the table and compare the result set to the expected state.
4. Use EXPLAIN QUERY PLAN to ensure the query planner is using the MergeTree logic (e.g., MergeTree node appears).

Conclusion

Choosing the right MergeTree variant is a decision that balances write workload characteristics against query requirements. For deduplicated counters, ReplacingMergeTree offers a simple, low‑overhead solution. If you need automatic summation, switch to SummingMergeTree; for complex aggregates, AggregatingMergeTree is preferable. Always validate merge behavior in a staging environment before rolling out to production.