Partition Pruning in ClickHouse®: How It Really Works
Introduction
Partition pruning is one of the reasons a well-designed ClickHouse® table can answer queries like "Show me last Tuesday's events" without scanning years of unrelated data.
At first glance, the concept seems simple: skip the data you don't need. However, partition pruning is also one of the most misunderstood optimization techniques in ClickHouse®. It's frequently confused with primary key indexing, can be silently disabled by an unsuitable WHERE clause, and is often implemented on the wrong column altogether.
In this article, we'll explore what partition pruning actually does, how ClickHouse® decides whether it can prune partitions, how to verify that it's working, and the situations where it quietly stops helping.
What Is a Partition?
A partition is a logical grouping of data parts on disk.
When creating a MergeTree table, you define a partitioning expression using PARTITION BY. Every inserted row is assigned to a partition based on that expression, and every data part belongs to exactly one partition.
Because each partition is stored separately on disk, ClickHouse® can completely skip partitions that cannot contain matching data.
CREATE TABLE visits
(
VisitDate Date,
Hour UInt8,
ClientID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(VisitDate)
ORDER BY Hour;
In this example:
- Every month's data is stored in its own partition.
- June 2026 data is stored separately from July 2026.
- August 2026 data is stored separately again.
Now consider this query:
SELECT count()
FROM visits
WHERE VisitDate >= '2026-06-01'
AND VisitDate < '2026-07-01';
Since only June data can satisfy the filter, ClickHouse® opens only the June partition and completely ignores every other month's directory.
This is partition pruning.
Partition Pruning vs Primary Key Pruning
Many users assume partition pruning is ClickHouse®'s primary indexing mechanism.
It isn't.
The primary key (ORDER BY) remains the most important optimization for query performance because it determines how rows are ordered inside each data part.
Partitioning serves different purposes:
- Eliminating entire partitions before reading data
- Making retention operations extremely fast
- Simplifying storage tiering
- Reducing unnecessary disk access
Think of it this way:
- Partition pruning decides which folders to open.
- Primary key pruning decides which files inside those folders to read.
Both optimizations work together, but they solve different problems.
Seeing Partition Pruning in Action
The easiest way to verify partition pruning is with EXPLAIN.
EXPLAIN indexes = 1
SELECT count()
FROM visits
WHERE VisitDate >= '2026-06-01'
AND VisitDate < '2026-07-01';
The execution plan includes partition selection details, showing:
- Total partitions
- Selected partitions
- Which pruning rules were applied
If only one partition is selected while dozens exist, pruning is working correctly.
Inspecting Partitions
You can also inspect your table's partitions directly.
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;
This query shows:
- Existing partitions
- Number of active data parts
- Rows stored inside each partition
Suppose EXPLAIN reports that only one partition is scanned, but that partition contains hundreds of small parts and millions of rows.
In that case, partition pruning is working correctly.
The issue is likely excessive fragmentation or an overly coarse partition key—not pruning itself.
When Partition Pruning Works Automatically
Partition pruning succeeds when ClickHouse® can determine which partitions satisfy the filter.
One important reason this works is that ClickHouse® understands certain monotonic functions.
A monotonic function always preserves ordering.
Examples include:
toDate()toYYYYMM()toYear()
Consider this table:
PARTITION BY toYYYYMM(VisitDate)
Even though the query filters on VisitDate rather than toYYYYMM(VisitDate), ClickHouse® can infer which monthly partitions are relevant.
WHERE VisitDate >= '2026-06-01'
AND VisitDate < '2026-07-01'
The optimizer understands that only the June partition can contain matching rows.
When Partition Pruning Fails
Partition pruning quietly stops working when ClickHouse® cannot infer the relationship between the filter and the partition expression.
Consider this partition key:
PARTITION BY cityHash64(user_id) % 16
Now execute:
SELECT *
FROM events
WHERE user_id = 42;
Although the filter clearly identifies one user, ClickHouse® cannot determine which hash bucket contains that user without evaluating the hash.
As a result:
- Every partition must be considered.
- No pruning occurs.
- The query becomes more expensive.
Pruning would only work if the query filtered using the exact same expression:
WHERE cityHash64(user_id) % 16 = 5
Hash functions, modulo operations, and many other derived expressions are non-monotonic, preventing ClickHouse® from working backwards to eliminate partitions.
This behavior is expected and documented—it is not a bug.
Designing a Partition Key That Actually Helps
A good partition key should reflect how users actually query the data.
Some practical guidelines include:
Partition by your most common filter
If nearly every query filters by date, partition by date.
Monthly partitions are usually the best balance between pruning effectiveness and operational overhead.
Daily partitions are generally appropriate only for extremely high-volume workloads such as observability or logging systems.
Avoid high-cardinality partition keys
Never partition by columns such as:
user_idsession_idorder_id
Doing so creates enormous numbers of tiny partitions, increasing merge overhead and reducing overall performance.
These columns usually belong at the beginning of the ORDER BY clause instead.
Keep the partition count reasonable
Thousands of partitions are usually a warning sign.
Each partition introduces additional metadata and background merge work.
Fewer, larger partitions generally perform better than many tiny ones.
Align partitioning with TTL policies
Partitioning also affects data lifecycle management.
If your TTL removes old data by month, partitioning by month allows ClickHouse® to simply drop entire partitions.
Without matching partition boundaries, TTL often falls back to slower row-level mutations.
Final Thoughts
Partition pruning is one of the simplest yet most effective optimizations in ClickHouse®.
Its job is straightforward:
Skip entire partitions that cannot satisfy the query.
However, getting the full benefit requires thoughtful schema design.
Choose a partition key that matches your most common filtering patterns, verify pruning with EXPLAIN indexes = 1, and inspect system.parts whenever performance doesn't match expectations.
When partitioning is designed around real query patterns instead of assumptions, ClickHouse® can eliminate massive amounts of unnecessary I/O before reading a single data granule.
For more ClickHouse® optimization guides and operational best practices, explore the CHOps feature page:
Top comments (0)