Why MergeTree Wins

What ClickHouse MergeTree does differently

A B-tree engine updates pages in place. That single fact drives everything you dislike about running OLTP engines under analytical load: random I/O, page locking, write amplification, and indexes that degrade as tables grow. MergeTree refuses the premise. An INSERT never touches existing data — it writes a brand-new part: a self-contained directory in which every column lives in its own compressed file, with rows sorted by the table’s ORDER BY key.

The design borrows from LSM trees, with a deliberate twist: there is no memtable and no leveled compaction contract. Parts land on disk already sorted, and a background scheduler — fully observable in system.merges — consolidates them opportunistically. Each merge re-sorts, recompresses, and rebuilds every index and checksum, so read performance stays flat while data volume grows by orders of magnitude.

Immutability is not an implementation detail; it is the operational contract. Readers never block writers. Caches never invalidate mid-part. Replication ships whole parts rather than replaying row-level logs, and a consistent backup is, at bottom, a file copy. When we audit a struggling cluster, the question is rarely “is MergeTree fast enough” — it is whether the schema lets the engine do its job.

The corollary: almost every ClickHouse MergeTree performance problem we triage in performance audits traces back to one of three schema decisions — the sort key, the partition key, or the ingestion batch shape. The rest of this page covers exactly those decisions.

Inside a Part

Granules, marks, and the sparse primary index

Open a part’s directory and the layout is disarmingly simple: one compressed data file per column, a marks file that maps index entries to compressed offsets, and primary.idx — the sparse primary index. Rows are grouped into granules of 8,192 (the default index_granularity), and the index stores exactly one entry per granule: the primary-key values of its first row.

Contrast that with a dense B-tree index carrying an entry per row. A one-billion-row part needs roughly 122,000 sparse entries — a structure small enough to pin in memory permanently, for every part, on every replica. The primary key in ClickHouse MergeTree therefore does two jobs at once: it defines the physical sort order on disk (a clustered layout, which is also what makes compression work so well), and it feeds the pruning step that discards granules before any I/O happens.

None of this needs to be taken on faith. ClickHouse will show you the pruning arithmetic for any query — parts touched, granules read, and which index eliminated what:

EXPLAIN indexes = 1
SELECT count()
FROM analytics.events_local
WHERE tenant_id = 4271
  AND event_time >= '2026-08-01 00:00:00';

-- Illustrative output (values vary per estate):
--   ReadFromMergeTree (analytics.events_local)
--   Indexes:
--     Partition   Condition: true            Parts: 8/8
--     PrimaryKey  Keys: tenant_id, event_time
--                 Parts: 3/8   Granules: 41/9840

That Granules: 41/9840 line is the single most useful number in MergeTree query tuning. When it reads like 9800/9840, the sort key is not serving the workload — no amount of hardware fixes that. Beyond the primary index, skip indexes extend pruning to non-key columns: minmax indexes are now auto-generated for numeric and temporal columns (settings shipped in 25.1 and 26.2), and the full-text text index — production-ready since ClickHouse 26.2 — replaces the now-deprecated tokenbf_v1 and ngrambf_v1 bloom-filter designs for log and message search.

The MergeTree Engine Family

One storage model, specialized merge semantics

Every member of the family shares the same parts-and-merges architecture; what differs is what happens to rows during a merge. Picking the wrong variant is a one-way door on large tables — the correction is a rebuild — so we treat engine selection as an architecture decision, never a default.

Production DDL

A production-grade ClickHouse MergeTree table, spelled out

House rule at ChistaDATA: customer-facing DDL never relies on defaults. Every engine parameter, codec, TTL clause, and setting is written out, because implicit behavior is exactly what changes between releases. Here is the shape of an events table as we would actually ship it (adapt columns and retention to your workload, and test on staging before production):

CREATE TABLE analytics.events_local
(
    event_date   Date DEFAULT toDate(event_time),
    event_time   DateTime64(3, 'UTC') CODEC(Delta, ZSTD(1)),
    tenant_id    UInt32,
    event_type   LowCardinality(String),
    user_id      UInt64,
    payload      JSON,                -- JSON type is GA since ClickHouse 25.3
    message      String,
    INDEX idx_message message TYPE text(tokenizer = 'splitByNonAlpha') GRANULARITY 1,
                                      -- text index: production since 26.2
    INDEX idx_user user_id TYPE bloom_filter(0.01) GRANULARITY 4
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/analytics/events_local', '{replica}')
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_type, event_time)
TTL toDateTime(event_time) + INTERVAL 90 DAY TO VOLUME 's3_cold',
    toDateTime(event_time) + INTERVAL 13 MONTH DELETE
SETTINGS index_granularity = 8192,
         storage_policy = 'tiered',
         min_bytes_for_wide_part = 10485760;

Sort keys come from query logs, not intuition

The ORDER BY clause above — low-cardinality equality filters first, the time column last — is a starting shape, not a law. The defensible version comes from a predicate census: group system.query_log by normalized_query_hash, extract the WHERE columns your workload actually filters on, and test candidate keys with EXPLAIN indexes = 1 against the top queries. And decide carefully the first time: changing a sort key later means a new table, an INSERT SELECT backfill, and a gated swap — never a mutation.

Partition for lifecycle, not for pruning

The most common self-inflicted MergeTree wound we see is daily — or worse, hourly — partitioning chosen “for performance.” The sort key prunes; partitions exist for retention, archival, and TTL drops. Monthly toYYYYMM() is the default posture for time-series data, and finer granularity needs an operational justification, because every extra partition multiplies part counts and merge scheduling load.

Compression is a per-column decision

Because the sort order clusters similar values together, per-column codecs compound the win: Delta or DoubleDelta with ZSTD for timestamps and counters, Gorilla for float gauges, and LowCardinality(String) for columns under roughly ten thousand distinct values — validated with uniq(), not assumed. Measure outcomes in system.columns on real data; compression ratios quoted without measurement are marketing.

Measurement First

Prove it with system tables, not opinion

MergeTree is unusually honest about its own state. Three queries form the core of the weekly health check we run on every supported estate — part pressure, merge backlog, and pruning efficiency of the live query mix:

-- 1. Part pressure per partition: the "too many parts" early warning
SELECT database, table, partition_id, count() AS active_parts
FROM system.parts
WHERE active AND database = 'analytics'
GROUP BY database, table, partition_id
ORDER BY active_parts DESC
LIMIT 10;

-- 2. Merge backlog and memory, right now
SELECT table, round(elapsed, 1) AS sec, round(progress, 2) AS pct,
       formatReadableSize(memory_usage) AS mem, result_part_name
FROM system.merges;

-- 3. Granule-pruning efficiency of the last 7 days of queries
SELECT normalized_query_hash,
       sum(ProfileEvents['SelectedMarks'])  AS marks_read,
       sum(ProfileEvents['SelectedRanges']) AS ranges,
       count() AS executions
FROM system.query_log
WHERE type = 'QueryFinish' AND event_date >= today() - 7
GROUP BY normalized_query_hash
ORDER BY marks_read DESC
LIMIT 20;

Recent releases pushed measurement even earlier in the design loop: EXPLAIN WHATIF (26.6) evaluates a hypothetical skip index with no build cost, and mergeTreeAnalyzeIndexes() (26.1) returns the exact row ranges each index selects per part. An index proposal in a ChistaDATA architecture review carries a WHATIF result, not an argument — the same measurement-first posture behind our 24×7 support practice.

MergeTree in 2025–2026

What changed recently — version-pinned

MergeTree is moving fast, and a surprising share of “best practice” articles online describe the engine as it was two years ago. Six changes that materially affect design decisions today:

Operating MergeTree

Where MergeTree estates get hurt in production

Too many parts. The classic failure, and almost always an ingestion-shape problem rather than a database problem: thousands of single-row inserts create parts faster than merges retire them. The doctrine is 10,000–500,000 rows per INSERT — enforced by the producer, by a buffer layer, or by async inserts with wait_for_async_insert = 1 as the stated durability contract. Watch system.part_log for part-creation rate; it is the earliest leading indicator we know of.

OPTIMIZE TABLE … FINAL as a habit. Forcing a full rewrite of every active part is an expensive, cluster-visible operation that competes with the merges you actually need. It has legitimate uses — closing out an immutable partition, pre-FINAL consolidation on a Replacing table — but as a scheduled reflex it is an anti-pattern. Let the scheduler work; measure it in system.merges before overriding it.

Mutations treated as DML. ALTER TABLE … UPDATE/DELETE is an asynchronous part rewrite — an operational event to be gated, tracked in system.mutations, and never issued casually against a multi-terabyte table. Application teams deserve the honest framing: MergeTree offers analytical storage with repair tools, not OLTP update semantics.

Keeper as an afterthought. Every ReplicatedMergeTree operation — part registration, merge assignment, insert deduplication — flows through ClickHouse Keeper, and most cluster-wide outages we respond to are coordination outages, not storage outages. Keeper is tier-0 infrastructure: sized, monitored via system.zookeeper_info (since 26.1), and drilled with the same seriousness as the database itself.

Standing advice for everything on this page: rehearse on staging with production-shaped data, keep a verified restore path before any structural change, and let our managed services or remote DBA team carry the operational load when your engineers should be building product instead.

Frequently Asked Questions

ClickHouse MergeTree, asked and answered

Is MergeTree an LSM tree?

Related, not identical. MergeTree shares the LSM core — immutable sorted runs consolidated by background merges — but drops the memtable and the WAL-driven leveled-compaction contract. Inserts land on disk already sorted, and merging is opportunistic rather than level-triggered. The practical consequence is the batching doctrine: since every INSERT creates a part, insert in chunks of thousands of rows, not one row at a time.

How should I choose the ORDER BY key?

From evidence: run a predicate census over system.query_log to see which columns your workload filters on, order low-cardinality equality columns first with time last, then compare candidate keys by granule-pruning ratio under EXPLAIN indexes = 1. Schema intuition alone routinely produces keys that prune nothing.

ReplacingMergeTree or lightweight updates for mutable data?

ReplacingMergeTree with argMax() reads (or a costed FINAL) remains the default for high-churn entities. Patch-part lightweight UPDATEs (25.7, beta) are a real alternative for correction-heavy workloads touching roughly ten percent of rows or less — on a monitored budget, watching patch partitions in system.parts.

Does MergeTree run on S3 and object storage?

Yes, and the pattern that works is tiering: hot data on NVMe, colder partitions moved by TTL to S3-backed volumes, with local headroom reserved for merges. Zero-copy replication over shared storage remains experimental as of 26.7 and is not a supported open-source production path — the honest low-cost options today are tiered volumes or Parquet/Iceberg offload. That assessment is part of every ChistaDATA architecture engagement.