Treat the sorting key as the index

In a MergeTree table, the ORDER BY clause is the single most consequential performance decision you will make. It defines the order in which rows are physically stored on disk, and that physical order is what the sparse primary index is built on. There is no separate “create index” step that rescues a poor sorting key after the fact; the sorting key is the primary index, so it must reflect how the table is actually queried.

ClickHouse stores data in parts, and within each part rows are sorted by the ORDER BY expression and divided into granules — by default, blocks of 8,192 rows. The sparse primary index keeps one entry per granule rather than one per row, which is why it is small enough to stay in memory even for enormous tables. When a query filters on a prefix of the sorting key, the engine performs binary search over these marks and reads only the granules that can possibly match. The mechanics are documented in detail in the ClickHouse guide to the sparse primary index.

The practical consequence is that you cannot bolt performance on later. Because the sorting key dictates the physical layout of the data on disk, changing it means rewriting the table, so the decision deserves real analysis up front. Treat it the way you would treat the primary-key design of a transactional schema: study the queries that matter, model the access patterns, and choose the ordering that serves the most valuable ones — because in ClickHouse this one clause determines the ceiling on how fast the table can ever be.

-- The ORDER BY clause is your primary index
CREATE TABLE events
(
    tenant_id   UInt32,
    event_date  Date,
    event_type  LowCardinality(String),
    user_id     UInt64,
    payload     String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_type, event_date);

Order primary-key columns correctly

Once you accept that the sorting key is the index, the order of its columns becomes the next decision — and it is governed by a strict rule. ClickHouse can use the primary index efficiently only for a left-to-right prefix of the sorting key. A filter on the first column is fast; a filter on the second column is fast only when the first is also constrained; a filter on the third column alone cannot use the index at all. Designing the sorting key therefore means ranking columns by how your queries filter, not by intuition.

Two principles guide the ordering. First, place the columns that appear most often as equality filters earliest, because they enable the index for the widest range of queries. Second, among those, lead with lower-cardinality columns, because they group many rows under each index mark and produce long runs that compress well and prune cleanly. A common, effective pattern is tenant or category first, then a moderate-cardinality dimension, then time. Validate the choice against the MergeTree documentation and, more importantly, against your real query log.

Tune index_granularity to the workload

The sparse index keeps one mark per granule, and the granule size — index_granularity, 8,192 rows by default — sets the trade-off between index precision and index size. Smaller granules mean the engine can skip to a more precise location and read fewer surrounding rows, at the cost of a larger index and more marks to scan. Larger granules shrink the index but force the engine to read more rows around each match. For most workloads the default is well chosen, and changing it should be evidence-driven rather than reflexive.

Two scenarios justify revisiting it. Highly selective point-lookup workloads on very large tables can benefit from a smaller granularity or from adaptive granularity, which sizes granules by byte volume rather than a fixed row count. Conversely, wide-scan analytical workloads rarely benefit from shrinking it and may pay for the larger index in memory. As with every decision in this guide, measure the granules read before and after; an index change that does not reduce data read is not an optimization.

-- Set granularity explicitly when the workload justifies it
CREATE TABLE metrics
(
    metric_id   UInt32,
    ts          DateTime,
    value       Float64
)
ENGINE = MergeTree
ORDER BY (metric_id, ts)
SETTINGS index_granularity = 4096;

Add data-skipping indexes deliberately

When a query filters on a column that is not part of the sorting-key prefix, the primary index cannot help — but a data-skipping index often can. These secondary structures store a small summary per block of granules and let the engine skip blocks that cannot contain a match. They are powerful when applied to the right column with the right type, and pure overhead when scattered across columns indiscriminately. The decisive factor is whether the indexed values are correlated with the physical row order; an index on a column whose values are randomly distributed across granules will skip nothing.

Choosing the index type matters as much as choosing the column. The table below summarizes the practical choices; the full set is described in the ClickHouse documentation on data-skipping indexes.

Two cautions govern their use. First, a skip index has its own GRANULARITY — expressed as a number of primary-index granules per index block — and tuning it controls how coarse or fine the skipping is; too coarse and it rarely skips, too fine and the index grows large. Second, more is not better. Each skip index adds write-time cost and storage, and an index that never prunes a block is pure overhead. Add them one at a time, in response to a specific slow query, and confirm with the query plan that the index actually eliminates blocks before keeping it.

-- Add a skip index on a non-prefix column
ALTER TABLE events
  ADD INDEX idx_user user_id TYPE bloom_filter(0.01) GRANULARITY 4;

-- Materialize it across existing parts
ALTER TABLE events MATERIALIZE INDEX idx_user;

Use projections for alternate orderings

A single sorting key cannot be optimal for every query against a table. When two access patterns demand contradictory orderings — say, by tenant for one report and by time for another — projections resolve the conflict. A projection is an additional, automatically maintained physical copy of the data within the same table, stored in a different order or pre-aggregated. ClickHouse chooses the best projection for each query transparently, so the application does not change.

Projections come in two flavors. A normal projection reorders the data to serve queries that filter or sort differently from the base table, effectively giving you a second primary index. An aggregating projection pre-computes group-by results, so a dashboard query reads a small summary rather than scanning the raw rows. The cost is additional storage and write-time maintenance, so add projections to serve specific, high-value query patterns rather than speculatively. The feature is documented under ClickHouse projections.

-- A projection that serves a different access pattern
ALTER TABLE events ADD PROJECTION p_by_user
(
    SELECT * ORDER BY (user_id, event_date)
);
ALTER TABLE events MATERIALIZE PROJECTION p_by_user;

Pre-aggregate with materialized views

The fastest scan is the one that reads a pre-computed answer. ClickHouse materialized views are not stored query results that go stale; they are insert triggers that transform incoming rows and write them into a target table as the data arrives. Paired with an AggregatingMergeTree target, a materialized view maintains rolling aggregates — counts, sums, uniques, quantiles — continuously and incrementally, so a dashboard reads thousands of summary rows instead of scanning billions of raw events.

This is indexing in the broad sense: you are trading a small amount of write-time work and storage for an enormous reduction in read-time data volume. The pattern is especially powerful for the high-frequency aggregate queries that dominate analytical dashboards, where the same group-by runs thousands of times an hour. Design the view around those known queries, and keep the raw table for ad-hoc exploration. The mechanics are covered in the ClickHouse documentation on materialized views.

Two design notes keep this technique reliable. Because the view runs on insert, it sees only the newly inserted block, not the whole table — so it suits incremental aggregates and not calculations that need to look across all historical rows at write time. And because the target uses aggregate states, queries against it must finalize those states with the matching -Merge combinator, reading the rolled-up data through a small wrapper view or query. Done correctly, a dashboard that once scanned a billion-row table answers from a few thousand pre-aggregated rows, and the difference in latency is not incremental but categorical.

-- Incremental rollup into an aggregating target table
CREATE MATERIALIZED VIEW mv_daily_counts
TO daily_counts AS
SELECT tenant_id, event_date,
       countState() AS events,
       uniqState(user_id) AS users
FROM events
GROUP BY tenant_id, event_date;

Read less with types, codecs & LowCardinality

Indexing decides which granules are read; data types and codecs decide how much each granule costs to read. Because ClickHouse is columnar and I/O-bound on large scans, shrinking the on-disk footprint of a column is functionally an optimization of every query that touches it. Choosing the narrowest correct integer type, storing timestamps as DateTime rather than strings, and wrapping repetitive string columns in LowCardinality can cut both storage and scan time dramatically.

Compression codecs add another lever. The Delta and DoubleDelta codecs excel on monotonic sequences such as timestamps and counters, Gorilla suits slowly changing floating-point metrics, and these can be layered before the general-purpose compressor. The effect compounds with good ordering: a well-chosen sorting key clusters similar values together, which makes the codecs far more effective. Together, type discipline and codecs frequently halve scan time without any change to the queries themselves.

-- Narrow types and codecs reduce I/O on every scan
CREATE TABLE readings
(
    sensor_id  UInt32      CODEC(Delta, ZSTD),
    ts         DateTime    CODEC(DoubleDelta, ZSTD),
    region     LowCardinality(String),
    value      Float32     CODEC(Gorilla, ZSTD)
)
ENGINE = MergeTree
ORDER BY (sensor_id, ts);

Partition for pruning, not for indexing

Partitioning is frequently confused with indexing, and conflating the two is a reliable way to slow a cluster down. The PARTITION BY clause divides a table into independent parts on disk, which enables partition pruning — skipping entire partitions whose values cannot match a filter — and makes data lifecycle operations such as dropping old data instantaneous. It is a coarse, complementary mechanism to the fine-grained sparse index, not a substitute for it.

The cardinal rule is to keep partitions large and few. A monthly partition on a date column is almost always right; a partition per day per tenant, producing tens of thousands of tiny partitions, overwhelms the engine with parts and degrades both inserts and queries. Partition by the dimension you use to expire or archive data, usually a coarse time bucket, and let the sorting key and skip indexes do the within-partition work. The trade-offs are detailed in the documentation on custom partitioning keys.