Partition pruning is the reason a well-designed ClickHouse® table can answer "give me last Tuesday's events" without touching years of unrelated data. It sounds simple - skip what you don't need - but it's also one of the most commonly misunderstood mechanisms in ClickHouse®, mixed up with primary key indexing, silently defeated by the wrong WHERE clause, and often applied to the wrong column entirely. This article walks through what partition pruning actually does, how to verify it's working, and where it quietly fails.
What Is a Partition, and What Is Pruning?
A partition in ClickHouse® is a logical grouping of rows on disk , defined by a PARTITION BY expression when you create a table. Every INSERTlands its rows into a part, and that part belongs to exactly one partition based on the expression's value for those rows. Partitions live as separate directories on disk, which is what makes pruning possible: if a query's filter can only match one partition, ClickHouse® never has to open the directories for the rest.
CREATE TABLE visits
(
VisitDate Date,
Hour UInt8,
ClientID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(VisitDate)
ORDER BY Hour;Here, every row lands in a partition named for its year and month. A query filtering WHERE VisitDate >= '2026-06-01' AND VisitDate < '2026-07-01' only needs to touch the June partition - pruning eliminates every other month before a single granule is read.
It's worth being precise about what pruning does and doesn't replace. Partitioning is not ClickHouse®'s primary indexing mechanism - that's the ORDER BY (primary) key, which controls row order within a part and is usually far more impactful for everyday query speed. Partitioning's real strengths are pruning large, obviously-irrelevant chunks of data up front,and making data lifecycle operations - dropping a month of old logs, moving a partition to cold storage - nearly instant instead of an expensive row-by-row operation.
Seeing Pruning in Action
The easiest way to confirm pruning is actually happening is to ask EXPLAIN directly, using the indexes=1 option to surface index and partition selection details:
EXPLAIN indexes=1
SELECT count()
FROM visits
WHERE VisitDate >= '2026-06-01' AND VisitDate < '2026-07-01';The plan will show how many partitions were selected against how many exist in total. If you're filtering on a genuinely well-chosen partition key, you should see a small selected count regardless of how many months or days of history the table holds - that's the entire point.
You can also cross-check directly against system.parts, which lists every partition and part a table actually has on disk:
SELECT partition, count() AS parts, sum(rows) AS row_count
FROM system.parts
WHERE table = 'visits' AND active = 1
GROUP BY partition
ORDER BY partition;If EXPLAIN claims to have pruned down to one partition, but that partition itself contains millions of rows spread across dozens of small parts, that's a sign the partition key is too coarse, not that pruning failed.
When Pruning Quietly Fails
This is the part that catches people out: pruning works reliably when the query's filter expression lines up with the partition key expression, and it can fail - silently, with no error, just a slower query - when it doesn't.
ClickHouse® can reason about a class of "monotonic" functions, meaning functions where a larger input always produces a larger (or always smaller) output. toYYYYMM() and toDate() fall into this category, so filtering on WHERE VisitDate >= '2026-06-01' still prunes correctly even though the partition key is toYYYYMM(VisitDate), not VisitDate itself - ClickHouse® can infer the relationship.
That reasoning breaks down for non-monotonic expressions. A partition key like PARTITION BY cityHash64(user_id) % 16 - sometimes used to spread writes evenly across a fixed number of buckets - can only be pruned by a query that matches the exact same expression in its WHERE clause. A seemingly equivalent filter on the underlying column, like WHERE user_id = 42, won't prune at all, because there's no way for the optimizer to work backward through a hash function. This is a real, documented limitation, not an edge case - it's worth checking your own partition key against your own typical filters rather than assuming pruning "just works" for any WHERE clause that seems related to the partition.
Designing a Partition Key That Actually Prunes
A few practical rules keep pruning effective instead of accidental:
- Match the partition key to your actual filter pattern. If most queries filter by date range, partition by date (typically by month; daily partitioning is usually only justified for high-volume observability workloads). If queries rarely filter that way, pruning won't help regardless of how the table is partitioned.
- Don't partition by high-cardinality columns. Partitioning by
user_id,session_id, or similar identifiers creates enormous numbers of tiny partitions, which hurts merge performance far more than it helps query speed. Put that column at the front ofORDER BYinstead. - Keep partition counts reasonable. Thousands of partitions per table is a warning sign, not a feature - each one carries its own part-merging overhead in the background.
- Remember TTL depends on partitioning too. If you plan to expire old data, partitioning by the same column your
TTLrule uses lets ClickHouse® drop whole partitions instantly, instead of falling back to slower row-level mutations.
Final Thoughts
Partition pruning is one of the simplest ideas in ClickHouse® - skip what you don't need - but getting real benefit from it means designing the partition key around your actual query patterns, not just picking something that "feels" reasonable. When in doubt, EXPLAIN indexes=1 and system.parts will tell you the truth faster than guessing will.
For more ClickHouse® optimization and operational guidance, explore the CHOps feature page at Features.
