ClickHouse MergeTree optimization is the difference between a table that answers analytical queries in milliseconds and one that scans hundreds of gigabytes for every request. This guide is written for senior engineers who already understand the MergeTree storage model and want a measurement-driven approach to sort keys, partitioning, index granularity, data-skipping indexes, and projections. Every recommendation for ClickHouse MergeTree optimization here is anchored in a system table you can query and version-pinned so you know when a feature became available.
Rather than trading in folklore, we tie each decision to observable evidence in system.parts, system.data_skipping_indices, and system.query_log. If a change cannot be justified by a column you can read, it does not belong in your schema. That evidence-first stance is what makes ClickHouse MergeTree optimization repeatable.
ORDER BY Design: The Core of ClickHouse MergeTree Optimization
The sorting key is the single most consequential decision in any MergeTree schema. It determines physical row order on disk, which drives both compression ratio and the sparse primary index used for granule pruning. Two principles govern good sort-key design in ClickHouse MergeTree optimization: cardinality ordering and query-pattern alignment.
Cardinality ordering for MergeTree optimization
Place lower-cardinality columns before higher-cardinality columns in the ORDER BY tuple. Runs of repeated values compress far better under the codecs ClickHouse applies per column, and a low-cardinality prefix keeps the primary index marks meaningful across many granules. Leading with a high-cardinality column such as a raw timestamp or a UUID scatters values so finely that neither compression nor pruning gains traction.
CREATE TABLE events_local
(
tenant_id UInt32,
event_type LowCardinality(String),
event_time DateTime,
user_id UInt64,
payload_bytes UInt32
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_type, event_time)
SETTINGS index_granularity = 8192;Query-pattern alignment
Cardinality ordering is a tiebreaker, not the whole story. The sort key must lead with the columns your queries filter on most often, because granule pruning only works when a predicate constrains a left-anchored prefix of the sorting key. A key of (tenant_id, event_type, event_time) prunes well for queries that filter by tenant_id, but a query filtering only by user_id will scan every granule, since user_id is not in the key at all.
Confirm the payoff empirically. After loading data, compare the marks a query reads against the marks available in the parts. This is the heartbeat of ClickHouse MergeTree optimization: the read path should touch a small fraction of total marks.
SELECT
query_duration_ms,
read_rows,
read_bytes,
ProfileEvents['SelectedMarks'] AS selected_marks,
ProfileEvents['SelectedParts'] AS selected_parts
FROM system.query_log
WHERE type = 'QueryFinish'
AND has(tables, 'default.events_local')
ORDER BY event_time DESC
LIMIT 5;Partitioning Anti-Patterns in ClickHouse MergeTree Optimization
Partitioning in ClickHouse is a data-management tool, not a query-acceleration tool. Its legitimate jobs are efficient TTL expiry, cheap DROP PARTITION and DETACH PARTITION operations, and coarse time-based data lifecycle. Using it as a substitute for a good sort key is the most common mistake in MergeTree schema design, and correcting it is often the biggest single win in ClickHouse MergeTree optimization.
Over-partitioning and the parts explosion
Each partition holds its own set of parts, and parts are never merged across partition boundaries. A PARTITION BY expression that yields many distinct values produces a proliferation of small parts, which inflates merge pressure, consumes file descriptors and memory for part metadata, and can trip the parts_to_throw_insert guard that rejects inserts once a partition accumulates too many active parts.
The classic disaster is a per-day-per-tenant scheme such as PARTITION BY (toDate(event_time), tenant_id). With a thousand tenants and a year of data, that is on the order of hundreds of thousands of partitions, each fragmented into small parts. Monthly partitioning with the tenant carried in the sort key is almost always the better choice.
SELECT
partition,
count() AS parts_in_partition,
sum(rows) AS total_rows,
formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active
AND table = 'events_local'
AND database = 'default'
GROUP BY partition
ORDER BY parts_in_partition DESC
LIMIT 20;If a single partition holds dozens of active parts long after inserts have settled, or if the average part is far smaller than the tens-to-hundreds of megabytes a healthy part reaches, over-partitioning is the likely cause. A sound part-count budget is one of the most reliable signals in ClickHouse MergeTree optimization.
index_granularity Trade-offs in ClickHouse MergeTree Optimization
The index_granularity setting, 8192 rows by default, defines how many rows sit between primary index marks — the size of a granule. It is the resolution of the sparse index and the smallest unit of data ClickHouse reads, which makes it a subtle lever in ClickHouse MergeTree optimization.
A smaller granularity produces finer-grained pruning and can help highly selective point-style lookups, but it enlarges the primary index (more marks held in memory) and adds per-granule overhead that hurts large scans. A larger granularity shrinks the index and favors big sequential reads at the cost of pruning precision. Since ClickHouse introduced adaptive granularity, the companion setting index_granularity_bytes (default 10 MiB) caps a granule by byte size so that very wide rows do not create oversized granules; set it to 0 to force fixed row-count granules.
CREATE TABLE metrics_local
(
metric_name LowCardinality(String),
sampled_at DateTime,
host_id UInt32,
metric_value Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(sampled_at)
ORDER BY (metric_name, host_id, sampled_at)
SETTINGS index_granularity = 8192,
index_granularity_bytes = 10485760;Treat the default as correct until measurement says otherwise; premature granularity changes are a common misstep in ClickHouse MergeTree optimization. Change granularity only when system.query_log shows that point lookups read many more marks than the rows they return, and always compare index size and scan throughput before and after.
Data-Skipping Indexes for ClickHouse MergeTree Optimization
Data-skipping indexes let ClickHouse skip whole granules for columns that are not part of the sort key. They store a compact summary per block of granules and are consulted during query planning to eliminate ranges before any data column is read. The three workhorses are minmax, set, and bloom_filter, and choosing the right one is central to ClickHouse MergeTree optimization.
Choosing an index type
A minmax index stores the minimum and maximum of an expression per block and skips blocks whose range cannot match a range or equality predicate. It shines when a column is loosely correlated with the sort order, such as a monotonic secondary timestamp. A set(max_rows) index stores up to a bounded number of distinct values per block and helps equality and IN predicates on columns with modest per-block cardinality. A bloom_filter index stores a probabilistic membership structure and is the right tool for high-cardinality equality lookups, for example a UUID or an email column.
ALTER TABLE events_local
ADD INDEX idx_user_id user_id TYPE bloom_filter(0.01) GRANULARITY 4;
ALTER TABLE events_local
ADD INDEX idx_payload_bytes payload_bytes TYPE minmax GRANULARITY 1;
ALTER TABLE events_local
MATERIALIZE INDEX idx_user_id;
ALTER TABLE events_local
MATERIALIZE INDEX idx_payload_bytes;GRANULARITY tuning in MergeTree optimization
The index GRANULARITY is not measured in rows. It specifies how many primary-index granules each index block summarizes. A GRANULARITY of 1 means one summary per granule, giving the finest skipping at the cost of a larger index; a higher value coarsens the summary and shrinks the index but skips in bigger chunks. Inspect the resulting footprint in system.data_skipping_indices, which exposes each index’s type, type_full, expr, granularity, and the compressed and uncompressed byte sizes.
SELECT
name,
type_full,
expr,
granularity,
formatReadableSize(data_compressed_bytes) AS compressed,
formatReadableSize(data_uncompressed_bytes) AS uncompressed,
formatReadableSize(marks_bytes) AS marks
FROM system.data_skipping_indices
WHERE table = 'events_local'
AND database = 'default';Verifying that a skip index actually fires
Adding an index proves nothing; you must confirm it prunes. Use EXPLAIN indexes = 1 and read the Skip entries, which report the parts and granules that survived each index as a survivors-over-input fraction. Since ClickHouse 25.9, run this with the analysis caches disabled so the plan reflects a fresh evaluation rather than a cached decision.
EXPLAIN indexes = 1
SELECT
count()
FROM events_local
WHERE user_id = 4242
SETTINGS use_query_condition_cache = 0,
use_skip_indexes_on_data_read = 0;If the Skip line shows granules dropping sharply, the index is earning its storage. If the fraction barely moves, the index type or the column choice is wrong, and keeping it only wastes space and slows inserts. Cross-check the real read reduction against ProfileEvents['SelectedMarks'] in system.query_log for the same query. Verification like this is non-negotiable in ClickHouse MergeTree optimization.
Projections vs Materialized Views in ClickHouse MergeTree Optimization
When a single physical order cannot serve every access pattern, you reach for either a projection or a materialized view. They solve overlapping problems in very different ways, and choosing wrongly is a durable source of pain. Getting this call right is a defining skill in ClickHouse MergeTree optimization.
How each one works
A projection lives inside the base table. It stores an alternate sort order or a pre-aggregation as hidden parts alongside the primary data, and the optimizer transparently rewrites a query to read the projection when it helps. Because a projection is part of the table, it is always consistent with the base data and requires no separate insert pipeline. A materialized view, by contrast, is a separate target table fed by an INSERT trigger: each insert into the source runs the view’s SELECT and writes the result into an independent MergeTree table you manage and query explicitly.
ALTER TABLE events_local
ADD PROJECTION proj_by_user
(
SELECT *
ORDER BY user_id
);
ALTER TABLE events_local
MATERIALIZE PROJECTION proj_by_user;CREATE MATERIALIZED VIEW events_daily_mv
TO events_daily
AS
SELECT
tenant_id,
toDate(event_time) AS event_date,
count() AS event_count,
sum(payload_bytes) AS total_bytes
FROM events_local
GROUP BY
tenant_id,
event_date;The target table for that view should itself be explicit about its engine and key, never leaning on defaults.
CREATE TABLE events_daily
(
tenant_id UInt32,
event_date Date,
event_count UInt64,
total_bytes UInt64
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events_daily', '{replica}')
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date)
SETTINGS index_granularity = 8192;When each wins, and the storage and mutation cost
Projections win when you want an alternate ordering or a roll-up of the same rows with zero consistency risk and no extra query surface, since the optimizer picks them automatically.
Their cost is storage — every projection duplicates the columns it covers — and write amplification, because each part must build and merge its projection parts too. A materialized view wins when the transformation is complex, spans multiple sources, or must be queried as a first-class table, and when you want the aggregate to live independently. Its cost is consistency management: the view only sees rows inserted after it was created, a backfill of historical data is a manual job, and an insert that fails after the source write can leave source and view out of step.
Mutations are the sharpest edge. An ALTER TABLE ... UPDATE or DELETE on a table with projections must rewrite the affected projection parts as well, multiplying the I/O of an already heavy operation. Since ClickHouse 23.x, lightweight DELETE offers a cheaper path for row removal, but classic mutations remain expensive; a materialized view is not rewritten by a source mutation at all, which is sometimes an advantage and sometimes a correctness trap. Whichever you choose, confirm the storage cost in system.parts and the columnar footprint in system.parts_columns before committing the design.
Tying It Together With Measurement
A disciplined ClickHouse MergeTree optimization pass follows the evidence. Start in system.query_log to find queries with a poor ratio of read_rows to result_rows or high SelectedMarks. Use EXPLAIN indexes = 1 to see which stage failed to prune, then decide whether the fix is a better sort-key prefix, a data-skipping index, or a projection. Audit part health in system.parts to catch over-partitioning, and size your indexes with system.data_skipping_indices. Let each change be justified by a number you can point to, and re-measure after every single change so you never attribute a win to the wrong lever.
For teams that want expert help turning these signals into a validated schema, ChistaDATA offers 24×7 ClickHouse Support with enterprise SLAs and senior-led ClickHouse Consulting engagements. The official MergeTree engine documentation and the system.data_skipping_indices reference are worth keeping open as you work, since both track behavior changes release by release.
Test in Staging Before Production
Every technique here — reordering a sort key, changing a partition expression, adjusting index_granularity, adding a skip index, or materializing a projection — reshapes data on disk and changes merge, mutation, and compression behavior across the whole table. Validate each change in a staging environment that mirrors your production schema, data distribution, and hardware, and compare the relevant system.query_log and system.parts figures before and after. A change that speeds up one query can quietly regress inserts or a dozen other queries, and only a staging comparison will catch it.
If you would rather not carry that risk alone, ChistaDATA’s ClickHouse Support team can review your MergeTree schema, reproduce your workload in a controlled environment, and hand you a measured plan — so your ClickHouse MergeTree optimization work reaches production with confidence rather than guesswork.
