Learning Objectives
- Explain what problem a data skipping index solves that the primary index cannot, without changing the table's physical sort order
- Distinguish minmax, set, and bloom filter skip indexes by the column shape and query pattern each is suited to
- Explain the one-directional correctness guarantee of a skip index — false positives are possible, false negatives are not
- Predict, given a column's data locality and cardinality, whether adding a specific skip index type will meaningfully reduce granules read or do nothing at all
Why This Matters
order-by-is-the-primary-key just drew the boundary as sharply as it gets: only a left-anchored prefix of ORDER BY ever narrows a granule scan, and every column past the first gap in that prefix — region in that lesson's WHERE region = 'eu-west' example, user_id in this one — gets nothing from the primary index, no matter how selective the filter looks. That's still not catastrophic, per the-wrong-index-is-fine back in Module 2: it's a columnar, vectorized read of just the needed columns, not a full row scan. But "not catastrophic" and "as fast as it could be" aren't the same thing, and ORDER BY has room for exactly one prefix — you can't just add every column you ever filter on to it. Data skipping indexes are the tool for the columns that lose that fight: a second, narrower way to let ClickHouse rule out granules for a column outside the primary key, without touching ORDER BY at all. They don't work the way indexes work everywhere else you've used them, though — adding one is not a guaranteed, if-modest, win. Sometimes it does almost nothing. Knowing which case you're in before you add one is the actual skill this lesson builds.
A summary attached to a block of granules, not a new sort order
Everything in this module so far — ORDER BY, the sparse primary index, granules — changes or reads from the table's one physical sort order. A data skipping index does neither. It doesn't reorder anything on disk. It attaches a small, separate summary structure to the table, computed over groups of granules, that a query can consult before deciding whether those granules are even worth reading.
CREATE TABLE http_requests
(
request_id UInt64,
user_id UInt64,
api_version String,
method String,
status_code UInt16,
event_time DateTime
)
ENGINE = MergeTree
ORDER BY (method, event_time);
ALTER TABLE http_requests
ADD INDEX idx_user_minmax user_id TYPE minmax GRANULARITY 4;
ALTER TABLE http_requests MATERIALIZE INDEX idx_user_minmax;
Two things worth noticing before the mechanism itself: user_id is nowhere in ORDER BY (method, event_time), so by the previous lesson's logic, a query filtering on it reads every granule. And ADD INDEX alone only affects parts written after it runs — existing parts don't retroactively get the index until you MATERIALIZE it. Skip this step on a table with years of history already loaded, and the index silently does nothing for most of your data — not because the index type is wrong, but because it was never built for those parts.
The GRANULARITY 4 here doesn't mean four rows or four granules read — it means the index groups every 4 primary-index granules into one summarized block. (This is a third, unrelated sense of "block" in this course's vocabulary — distinct from both the execution block from Module 1's vectorized-execution lessons and the insert block from earlier in this module. Here it just means "however many granules GRANULARITY groups together.") With the default 8,192-row granule, that's one summary per 32,768 rows. Query time works from that block, not the granule: ClickHouse checks the summary for a block and either skips all four granules in it or reads all four — there's no partial credit within a block.
Why minmax on user_id barely helps
minmax records exactly two numbers per block: the smallest and largest value of the expression among that block's rows. A query can rule a block out only if the value it's looking for falls entirely outside that block's [min, max] span.
request_id is a plausible candidate — in a system where inserts happen close to when requests occur. It's assigned roughly in order as requests happen, and if the pipeline feeding this table inserts each batch shortly after that batch's requests occurred, a given block's request_id values tend to already sit in a narrow, mostly-increasing range even without being in ORDER BY. A query like WHERE request_id BETWEEN 900000 AND 901000 can then skip almost every block whose recorded range doesn't overlap that window. That locality isn't guaranteed by the column itself, though — it's a property of how the data actually arrives. A pipeline that batches, backfills, or replays old data out of order would break the assumption entirely, and the same minmax index would degrade toward the user_id case below.
user_id doesn't have that property. IDs are handed out independently of when a request happens — user 4 and user 4,000,000 are equally likely to show up in the same insert batch. So every block's recorded [min, max] ends up spanning close to the table's entire ID range, and a filter like WHERE user_id = 4821 almost never falls outside any block's span. The index isn't broken — it's doing exactly what a minmax index does. It's just summarizing data that has no range structure to summarize.
EXPLAIN indexes = 1
SELECT count() FROM http_requests WHERE user_id = 4821;
"Indexes": [
{
"Type": "Skip",
"Name": "idx_user_minmax",
"Description": "minmax GRANULARITY 4",
"Parts": "12/12",
"Granules": "4960/5000"
}
]
Bloom filter: rarity, not range, is what it needs
A bloom filter index stores a compact, probabilistic summary of which exact values might appear anywhere in a block. It never has to record a range, so it doesn't need the column's values to cluster together the way minmax does. It only needs the value you're filtering for to actually be absent from most blocks — which is a question of how rare that value is across the table, not where its occurrences happen to sit physically.
ALTER TABLE http_requests DROP INDEX idx_user_minmax;
ALTER TABLE http_requests
ADD INDEX idx_user_bloom user_id TYPE bloom_filter GRANULARITY 4;
ALTER TABLE http_requests MATERIALIZE INDEX idx_user_bloom;
"Indexes": [
{
"Type": "Skip",
"Name": "idx_user_bloom",
"Description": "bloom_filter GRANULARITY 4",
"Parts": "5/12",
"Granules": "310/5000"
}
]
Nothing about user_id's physical layout changed between the two examples — it's still handed out with no relationship to insertion order. What changed is which question the index asks. Minmax asks "could this value be inside this block's range?" and gets "yes, almost always" because the range is nearly the whole table. Bloom filter asks "have I ever seen this exact value in this block?" and gets "no" for the vast majority of blocks, because any single user_id genuinely only appears in a small fraction of them. The trade is a real one, not free: a bloom filter can occasionally answer "maybe present" for a block that turns out not to contain the value at all (a false positive, wasting a read) — but it can never answer "definitely absent" for a block that does contain it. That asymmetry is the whole reason it's safe to use for filtering at all.
set(): small vocabularies, if they cluster
A set index records the distinct values seen in a block, up to a configured cap. api_version is a good fit: across the table's whole history there might be only four or five versions ever deployed, and because a version rollout happens over a contiguous stretch of time, any one block tends to contain requests from only one or two versions — not all of them.
ALTER TABLE http_requests
ADD INDEX idx_version_set api_version TYPE set(3) GRANULARITY 4;
ALTER TABLE http_requests MATERIALIZE INDEX idx_version_set;
The 3 is a cap on how many distinct values the index will bother recording per block. If a block genuinely contains only one or two api_version values, the recorded set is small and precise, and a filter for a version not in that set skips the block cleanly. If a block happens to straddle a rollout boundary and contains four or five distinct versions, the cap is exceeded and that block can no longer be ruled out by this index at all — it falls back to "must read," same as if no index existed for that block. set() doesn't degrade gracefully past its cap; it stops helping entirely for the blocks that exceed it.
Neither type is free, and neither is a substitute for a good ORDER BY
Every skip index costs something even when it never rules out a single block: it has to be computed and stored for every part, including the merged parts this module has already covered, which means more work on every insert and every background merge, plus the disk space the summaries themselves occupy. None of that is worth paying if the column doesn't have the property its index type depends on — locality for minmax and set, rarity for bloom_filter. And none of it replaces ORDER BY: a well-chosen sort key still rules out granules more precisely, for free, on every query whose filter matches its prefix. Skip indexes are for the columns you can't put in ORDER BY — there's only one sort order per table — not a way to avoid choosing one carefully.
Common Misconception
"I'll add a data skipping index on every column I filter on — worst case it just doesn't help, like an unused index in Postgres just sitting there."
In Postgres, an index on a column always works the same way regardless of what the data looks like — a B-tree over any set of values still gives you a precise lookup. A ClickHouse skip index isn't like that. Its usefulness is entirely a property of the data's shape relative to the index type, not something the index type guarantees on its own. minmax on a column with no locality (like user_id in this lesson) can end up ruling out almost nothing, ever, no matter how selective your filters are — while still costing storage and merge overhead on every part. The fix isn't "add fewer indexes," it's "check the fit before adding one": does this column's values cluster within blocks (candidate for minmax or set), or is the value you filter on simply rare across the table as a whole (candidate for bloom_filter)? If neither is true, no skip index on that column is going to do much — and EXPLAIN indexes = 1, run before and after, is how you find out instead of assuming.
Predict, Then Verify
A sensor_readings table is ORDER BY (region, recorded_at) and has two skip indexes: minmax on reading_value GRANULARITY 4, and bloom_filter on device_id GRANULARITY 4. reading_value fluctuates independently from minute to minute with no relationship to region or time. device_id is a high-cardinality identifier — tens of thousands of distinct devices — and each device contributes only a small fraction of the table's total rows.
Two queries run:
- Query A:
WHERE reading_value > 95.0 - Query B:
WHERE device_id = 55190
For each query, predict whether its skip index meaningfully reduces the granules read, or does close to nothing — and say which property (locality or rarity) is driving your answer in each case.
Reveal the answer →
Query A gets close to no help from the minmax index. reading_value has no locality — it fluctuates independently of insertion order — so every block's recorded [min, max] ends up spanning nearly the full range of possible readings, the same way user_id's range did earlier in this lesson. A filter for values above 95.0 falls inside almost every block's span, so almost nothing gets ruled out.
Query B gets real help from the bloom_filter index, and it doesn't need device_id to have any locality at all. What it needs is rarity: since any single device contributes only a small slice of the table's total rows, the value 55190 is genuinely absent from most blocks — and the bloom filter can say so correctly for each of them. The two indexes are answering different questions: minmax needed reading_value to cluster physically and it doesn't; bloom_filter needed device_id to be rare per block, and high cardinality with low per-device row counts gets it there regardless of physical layout.
Summary
A data skipping index attaches a small summary to groups of granules — not a new sort order — so ClickHouse can rule out blocks for columns that aren't, and shouldn't be, part of ORDER BY. minmax records a block's value range and only helps when the column has locality: its values genuinely cluster within blocks, the way a roughly-monotonic ID assigned near insertion time does. set() records a block's distinct values up to a cap and needs the same kind of clustering at a small vocabulary size, falling back to "must read" entirely for any block that exceeds the cap. bloom_filter needs neither range nor clustering — it tests literal presence per block and helps whenever the filtered value is rare enough that it's genuinely absent from most blocks, which is a property of selectivity, not physical layout. Every skip index type shares one correctness guarantee — it can produce false positives (reading a block that turns out not to match) but never a false negative (skipping a block that would have matched) — and every skip index type shares one real cost: storage and merge overhead paid on every part, whether or not the column's data actually has the property that type depends on.
Quiz
1. What problem does a data skipping index solve that ClickHouse's primary index cannot?
2. A skip index can be wrong in one specific, safe direction. Which one?
3. Why does a minmax index on user_id barely reduce the granules read, given user_id is assigned independently of insertion order?
4. Swapping the same user_id column from a minmax index to a bloom_filter index made the same WHERE user_id = 4821 query skip far more blocks. What changed to make that possible?
5. A set(3) index is added on api_version. One block happens to straddle a rollout boundary and actually contains 5 distinct api_version values in that block. What happens for that specific block?
6. A team adds a bloom_filter index on a status_code column that only has 3 possible values, each appearing in nearly every block. What's the most likely outcome?