ClickHouse MergeTree: ORDER BY, Partitioning, and the Sparse Index

By /

ClickHouse’s workhorse table engine is MergeTree. Almost every table uses it or one of its variants, and three things determine whether queries are fast or slow: ORDER BY, PARTITION BY, and the sparse primary index they produce.

The previous post covers what a MySQL developer needs to know before using ClickHouse; this one covers how its main table engine stores and reads data.

Why it’s called MergeTree

Every insert creates a new immutable part — a self-contained directory of sorted, compressed column files on disk. ClickHouse never edits existing parts in place.

Left unchecked, inserts would produce thousands of tiny parts. A background process continuously merges small parts into larger ones, keeping the part counts low and the data well-sorted. That merge process is the “merge” in MergeTree, and it follows the same log-structured design used in RocksDB and Cassandra.

Two consequences fall straight out of this design:

  • Batched inserts — One insert creates one part. Row-by-row inserts cause part explosion and eventually the Too many parts error.
  • Timing matters — Deduplication and aggregation in special engines happen during merges, not at insert time.

ORDER BY: The sorting key

ORDER BY defines the physical sort order of rows within every part. It is not a secondary structure like a MySQL index — it is the on-disk layout.

CREATE TABLE events
(
    tenant_id   UInt32,
    event_type  LowCardinality(String),
    event_time  DateTime,
    user_id     UInt64,
    amount      Decimal(10, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_type, event_time);Code language: JavaScript (javascript)

Because the data is physically sorted by (tenant_id, event_type, event_time), a query filtered on tenant_id — or tenant_id and event_type — can skip straight to the relevant region of disk. A query filtered only on user_id (which appears nowhere in the sorting key) must scan everything.

Three rules for choosing a good ORDER BY:

  1. Lead with the most frequently filtered columns. The sorting key works left-to-right, just like a composite index in MySQL.
  2. Generally go from lower cardinality to higher cardinality. Putting tenant_id (few values) before event_time (many values) tends to keep similar rows adjacent, improving both index effectiveness and compression. However, actual query patterns matter more than cardinality alone, so optimize first for the columns that appear most often in the WHERE clause.
  3. Compression depends on sort order. Sorted, similar data compresses dramatically better — often 5-20x.

Choosing ORDER BY in practice

The best ORDER BY is usually neither the unique key nor the lowest-cardinality column, but the set of columns that most closely matches how the table is actually filtered. The useful question is not “what uniquely identifies a row?” but “what appears most often in the WHERE clause?” In each pattern below, the sorting key simply follows the filters:

-- User activity: one tenant, then a time range
-- WHERE tenant_id = ? AND event_time BETWEEN ...
ORDER BY (tenant_id, event_time)

-- Event tracking: adds an event-type filter
-- WHERE tenant_id = ? AND event_type = ? AND event_time BETWEEN ...
ORDER BY (tenant_id, event_type, event_time)

-- User-centric access: one user, then a time range
-- WHERE user_id = ? AND event_time BETWEEN ...
ORDER BY (user_id, event_time)

The point is not uniqueness; it is letting the sparse index eliminate as much data as possible before anything is read from disk.

PRIMARY KEY vs ORDER BY

Coming from MySQL, this is the part that trips people up. In ClickHouse:

  • ORDER BY controls how data is sorted on disk.
  • PRIMARY KEY controls what goes into the sparse index — and when it is not specified, it defaults to the ORDER BY. Most tables never declare PRIMARY KEY at all.
  • Neither one enforces uniqueness. There is no duplicate-key error.

The two are separated only to get a shorter index key while still sorting on more columns for compression:

PRIMARY KEY (tenant_id, event_type)
ORDER BY (tenant_id, event_type, event_time)

The PRIMARY KEY must always be a prefix of ORDER BY.

The sparse index: Not a B-tree — a table of contents

A MySQL B-tree points to individual rows. ClickHouse’s primary index is coarser and far cheaper.

Each part is divided into granules — blocks of rows, 8,192 by default (index_granularity). ClickHouse stores one index entry (a mark) for the first row of each granule. That means roughly one mark per 8,192 rows. On a billion-row table that’s about 120,000 marks, small enough to keep in memory.

It works like the table of contents in a book: it does not give the exact page a sentence is on, but it points to the right chapter.

Take a query that filters on the sorting key:

SELECT * FROM events WHERE tenant_id = 42;

ClickHouse resolves it in three steps:

  1. Binary-searches the marks to find granules that might contain tenant_id = 42.
  2. Reads only those granules off disk — and only the columns the query needs.
  3. Scans a few thousand rows linearly to find exact matches.

This is why ClickHouse is well suited to “give me everything for tenant 42 in March” and poorly suited to “give me the one row where user_id = 999” if user_id isn’t in the sorting key — the latter forces a full scan because the table of contents is useless for it.

When a filter column isn’t in ORDER BY

A natural question for anyone coming from MySQL is what happens to a query like SELECT * FROM events WHERE user_id = 12345 when user_id is not part of the sorting key. The sparse index cannot help: the marks describe only the sorting-key columns, so ClickHouse has to scan every candidate granule.

That is not automatically slow — ClickHouse is built for large analytical scans — but the engine loses its most effective skipping mechanism. When a frequently filtered column cannot reasonably go into ORDER BY, data-skipping indexes (minmax, set, and bloom-filter) can prune granules on that column instead. They are a secondary optimization, worth adding only after the sorting key itself is right.

Granularity tuning

  • Smaller granules means a bigger, more precise index (better for point-ish lookups).
  • Larger granules means a smaller index and less overhead (better for big scans).

The default of 8,192 is right for almost every table; it is best left alone without a measured reason to change it. Modern ClickHouse also uses adaptive index granularity by default: rather than fixing every granule at 8,192 rows, it creates marks more frequently when rows are physically large, keeping the amount of data behind each mark, and therefore memory usage, predictable.

How the pieces fit together

The whole layout nests: a table splits into partitions, each partition into immutable parts, and each part holds rows sorted by ORDER BY and grouped into granules that the sparse index marks:

Table
|
+-- Partition (202607)                -- one dir per PARTITION BY value
|   +-- Part (immutable; rows sorted by ORDER BY)
|   |   +-- Granule: rows 1-8192      -- 1 mark: ORDER BY value of row 1
|   |   +-- Granule: rows 8193-16384  -- 1 mark: ORDER BY value of row 8193
|   |   +-- ...
|   +-- Part
|
+-- Partition (202608)
    +-- Part

Thinking in terms of partitions, parts, granules, and marks makes most MergeTree behavior easier to reason about.

PARTITION BY: For management, not speed

PARTITION BY splits the table into separate directories on disk — one per partition value. With PARTITION BY toYYYYMM(event_time), all of August 2026’s data lives in its own partition, separate from July’s.

The biggest misconception is that partitioning is a query-speed feature. It’s mostly a data-management feature:

  • Instant bulk deletes. ALTER TABLE events DROP PARTITION '202601' drops a whole month in milliseconds by deleting directories — no row-by-row mutation.
  • TTL and tiered storage. Expire old partitions automatically, or move them to cheaper disks.
  • Partition pruning. A query filtered on the partition key does skip irrelevant partitions — but this is a bonus, not the main event. The ORDER BY does the heavy lifting for query speed.

The pitfall: over-partitioning

Merges only happen within a partition, and each partition needs its own parts. Partitioning by day on a high-traffic table produces thousands of tiny partitions, part explosion, and slow everything.

A workable guideline is a handful to a few hundred partitions, each holding a meaningful amount of data. Monthly is the safe default, and a high-cardinality column like user_id should never be the partition key.

The MergeTree family: Engines that transform data during merges

Plain MergeTree just stores rows. Its siblings use the merge process to transform data, deduplicating or pre-aggregating by the ORDER BY key:

  • ReplacingMergeTree — keeps only the latest row per ORDER BY key. The closest thing to an upsert, but deduplication happens during merges, so a query needs FINAL to guarantee it at read time.
  • SummingMergeTree — collapses rows with the same key, summing the numeric columns. Great for counters.
  • AggregatingMergeTree — stores partial aggregate states; used for incremental materialized-view rollups.
  • CollapsingMergeTree / VersionedCollapsingMergeTree — model updates and deletes via sign rows that cancel each other out.

For most tables, plain MergeTree is the right choice; the variants earn their place only when a table needs deduplication (Replacing), rollups (Aggregating), running counters (Summing), or sign-based collapsing (Collapsing).

Inspecting the layout with system tables

Everything above is observable. system.parts shows exactly how a table is laid out — partitions, parts, row counts, and compressed vs. uncompressed size:

SELECT
    partition,
    count() AS parts,
    sum(rows) AS rows,
    formatReadableSize(sum(data_compressed_bytes))   AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed
FROM system.parts
WHERE table = 'events' AND active = 1
GROUP BY partition
ORDER BY partition;Code language: PHP (php)

The parts column is the one to watch: if a single partition has hundreds of active parts, inserts are too small or too frequent. Comparing compressed to uncompressed shows the ORDER BY compression at work — a good sorting key routinely gives 5-20x.

To see how well a specific query uses the index, prefixing it with EXPLAIN indexes = 1 makes ClickHouse report how many granules it read versus the total — the truest measure of whether the ORDER BY fits the query.

A MergeTree design checklist

Before a table goes to production, a few questions catch most design mistakes:

  • Does ORDER BY match the most common WHERE clauses?
  • Are the low-cardinality filter columns placed early in the key?
  • Is PARTITION BY chosen for lifecycle management rather than query speed?
  • Will inserts arrive in reasonably sized batches?
  • Will the number of partitions stay manageable over time?
  • Do any frequent queries filter on columns absent from ORDER BY?

Clear answers to those questions usually mean the table design is on the right track.

Putting it all together

A well-designed MergeTree table comes down to three decisions — ORDER BY, PARTITION BY, and insert batching — and together they decide how it lays out data on disk, how well it compresses, and how much work a query has to do. With those three right, the sparse index, compression, and background merges do the rest. The common failure modes are the mirror image: a sorting key that ignores the query patterns, partitioning by day, and single-row inserts.

Leave a Reply