Choosing MergeTree Partitioning and Compression for Time-Series Workloads

The Storage Decision That Affects Every Query

You're ingesting 50 million events per day from application logs, IoT sensors, or financial ticks. The raw data lands in ClickHouse, but the table definition you write today determines whether next month's dashboard loads in 200 ms or 20 seconds. MergeTree is the default engine, but its performance hinges on two choices you make at CREATE TABLE time: the partitioning key and the compression codec.

Partition by Time, Order by Cardinality

MergeTree stores data in parts sorted by the primary key (defined via ORDER BY). Partitions are a separate concept—they control which parts get merged and, crucially, which parts get skipped during query execution. For time-series data, the standard pattern is partitioning by a date expression and ordering by the timestamp plus a high-cardinality column.

Consider this table for HTTP request logs:

CREATE TABLE http_requests (
    event_time DateTime64(3),
    service_name LowCardinality(String),
    endpoint String,
    status_code UInt16,
    latency_ms UInt32,
    request_id UUID
) ENGINE = MergeTree()
PARTITION BY toDate(event_time)
ORDER BY (service_name, event_time, request_id)
SETTINGS index_granularity = 8192;

The partition key toDate(event_time) creates one partition per calendar day. The primary key sorts first by service_name (low cardinality, good for filtering), then by event_time for range scans, then by request_id to ensure uniqueness. This ordering lets ClickHouse skip entire granules when you filter WHERE service_name = 'payments' AND event_time >= '2026-10-15'.

Compression: ZSTD for Cold Data, LZ4 for Hot

ClickHouse applies compression per-column. The default LZ4 is fast but achieves ~3× compression on typical logs. ZSTD reaches 5–7× at the cost of 2–3× slower decompression. Since recent partitions are queried far more often, a tiered approach works well:

ALTER TABLE http_requests MODIFY COLUMN
    endpoint CODEC(ZSTD(3)),
    latency_ms CODEC(Delta, ZSTD(3));

-- Keep hot partition on faster codec
ALTER TABLE http_requests MODIFY PARTITION 20261018
    MODIFY COLUMN endpoint CODEC(LZ4);

The Delta codec before ZSTD exploits the fact that latency values change slowly between adjacent rows (thanks to the ORDER BY), improving compression ratio further. Note that ALTER ... MODIFY PARTITION rewrites only that partition's data files—useful for migrating historical data to cheaper storage tiers.

Verifying Partition Pruning Actually Works

Don't assume the optimizer uses your partitions. Run EXPLAIN with the partition and parts columns:

EXPLAIN SELECT count()
FROM http_requests
WHERE service_name = 'payments'
  AND event_time >= '2026-10-15 00:00:00'
  AND event_time <  '2026-10-16 00:00:00';

-- Look for:
-- Partition: 20261015 (only one partition)
-- Parts: 3 (only three data parts scanned)

If you see multiple partitions or a full table scan, check that the WHERE clause uses the partition key's exact expression (toDate(event_time)) or a monotonic function of it. Casting to Date or using toStartOfDay breaks pruning.

The Trade-Off: Insert Throughput vs. Merge Overhead

MergeTree's background merges combine small parts into larger ones to maintain query performance. With daily partitions and high insert frequency (e.g., 1000 inserts/day), you'll accumulate many small parts before merges catch up. This increases memory usage during queries and slows down SELECT because more parts must be opened.

Mitigations:

• Batch inserts: aim for ≥100k rows per INSERT to create fewer, larger parts initially.
• Tune merge_max_block_size and max_bytes_to_merge_at_max_space_in_pool if merges fall behind.
• Consider ReplicatedMergeTree with cleanup_delay_period to control part retention on replicas.

Monitor system.parts for active part count per partition. More than 50–100 active parts signals merge lag.

Checklist Before You Ship

1. Define PARTITION BY on a date or low-cardinality time bucket matching your query patterns.
2. Set ORDER BY with filter columns first, then time, then a unique tiebreaker.
3. Apply CODEC(ZSTD(3)) to high-entropy columns; add Delta for monotonic metrics.
4. Run EXPLAIN on representative queries—verify single-partition scans.
5. Watch system.parts and system.merges for the first 48 hours of production load.

If the EXPLAIN output shows the expected partition and a low part count, your schema will scale. If not, adjust the partition expression or ordering before data volume makes ALTER TABLE expensive.