ClickHouse MergeTree and Table Design

MergeTree is the engine behind almost every analytical table in ClickHouse. Its design decisions, especially the sort key, determine how fast later queries run. This article explains what those decisions do so you can make them on purpose.

Mental model#

MergeTree table├── columns        stored separately, compressed├── ORDER BY       rows physically sorted by this key├── primary index  sparse: one entry per block of rows├── data parts     each insert writes a new part└── merges         background process combines parts over time

An insert does not update one big file. It writes a new part: a self-contained, sorted chunk of the table. A background process continuously merges parts into larger ones. Queries read across whatever parts currently exist.

What ORDER BY does#

ORDER BY in a CREATE TABLE is the sort key. It has two effects:

1. Within every part, rows are stored physically sorted by the key.
2. ClickHouse builds a sparse primary index on the key: it records the key value at the start of each block (granule) of rows, not for every row.

When a query filters on a prefix of the sort key, ClickHouse uses that index to skip whole blocks that cannot contain matching rows, and reads only the blocks that might. When a query filters on a column that is not near the front of the sort key, the index cannot help and ClickHouse scans more data.

ORDER BY (created_at, customer_id)
WHERE created_at >= '2026-03-01'     → index skips most blocksWHERE customer_id = 42               → index cannot skip; near-full scan

Choosing a sort key#

Lead the sort key with the columns your queries filter and group on most often. A practical order:

1. The column almost every query filters by (often a time column).
2. Then columns used in GROUP BY or as secondary filters.
3. Higher-cardinality columns later, or left out.

ORDER BY (a, b) is not the same as ORDER BY b. The first stores data sorted by a then b, and its index helps queries that filter by a, or by a and b. It does not help a query that filters only by b.

A sort key that omits the column queries filter on is the most common table design mistake. If reports always constrain a time range, the time column belongs in the key.

PRIMARY KEY and ORDER BY#

By default PRIMARY KEY equals ORDER BY. You can set a shorter PRIMARY KEY that is a prefix of ORDER BY when you want a smaller index than the full sort order:

CREATE TABLE analytics.orders(    created_at  DateTime,    customer_id UInt64,    order_id    UInt64,    amount      Float64)ENGINE = MergeTreePRIMARY KEY (created_at, customer_id)ORDER BY (created_at, customer_id, order_id);

Here rows are stored sorted by all three columns, but the index only indexes the first two. Only set both when you have a specific reason; otherwise let PRIMARY KEY default to ORDER BY.

Partitioning with PARTITION BY#

PARTITION BY splits a table into independent groups of parts, usually by time:

CREATE TABLE analytics.orders(    created_at DateTime,    order_id   UInt64,    amount     Float64)ENGINE = MergeTreePARTITION BY toYYYYMM(created_at)ORDER BY (created_at, order_id);

A partition is a data-management unit, not a general query accelerator:

• You can drop or replace a whole partition in one cheap operation (ALTER TABLE ... DROP PARTITION), which makes reloading one month idempotent.
• TTL rules and backups can work per partition.
• A query with a matching filter can skip non-matching partitions, but the sort key already does most of that work.

Partition coarsely. Monthly (toYYYYMM) or daily (toYYYYMMDD) partitions on a large table are normal. Partitioning a small table, or partitioning by a high-cardinality column, creates many tiny partitions and parts and usually makes things slower.

Data parts and merges#

Every insert creates a part. ClickHouse merges parts in the background, but until it does, a table with thousands of tiny parts is slow to query and wastes resources.

The practical rules:

• Insert in large batches, not row by row.
• Avoid one insert per record from a stream; buffer and insert periodically.
• Let background merges do their work; forcing OPTIMIZE ... FINAL routinely is a sign the ingest pattern is wrong.

The MergeTree family#

Variants of MergeTree collapse rows with the same sort key during merges:

Two cautions:

• These engines collapse rows eventually, during merges, and only on an exact full sort-key match. A query that needs the collapsed result now must use FINAL or aggregate again at read time.
• SummingMergeTree only makes sense for additive measures. An average is not additive: storing avg_amount in a SummingMergeTree and reloading overlapping data double-counts it. Store sum and count, and compute the average at read time as sum / count.

A design walk-through#

Same data, two tables, two query patterns.

Reports that always scan a time range and group by month:

CREATE TABLE analytics.orders_by_time(    created_at  DateTime,    customer_id UInt64,    order_id    UInt64,    amount      Float64)ENGINE = MergeTreePARTITION BY toYYYYMM(created_at)ORDER BY (created_at, customer_id);

A lookup that always starts from a customer:

CREATE TABLE analytics.orders_by_customer(    customer_id UInt64,    created_at  DateTime,    order_id    UInt64,    amount      Float64)ENGINE = MergeTreeORDER BY (customer_id, created_at);

Neither is "correct" in the abstract. Each sort key matches one access pattern. Design the table for the queries it will actually serve.

Common mistakes#

A sort key that omits the filtered column#

If queries always constrain a time range, created_at belongs at the front of ORDER BY.

Partitioning to speed up queries#

Partitioning is for managing and reloading data. Fix slow queries with the sort key.

Over-partitioning#

Daily partitions on a small table, or partitioning by a high-cardinality column, creates many small parts. Use month or day, and only at volume.

Additive-only engines holding an average#

SummingMergeTree with an avg column produces wrong totals after reloads. Keep sum and count; derive the average when reading.

Expecting collapse to be immediate#

ReplacingMergeTree and SummingMergeTree reconcile during merges. Use FINAL or re-aggregate at read time when you need the collapsed result now.

Quick reference#

See also#