Data-skipping indexes: minmax, set and bloom filters

If you come from a B-tree database, forget what “secondary index” means. A ClickHouse data-skipping index does not point at rows. It stores a small summary for each block of data, and at query time the summary answers one question: “can this block possibly contain a match?” If not, the block is skipped without reading it. If the answer is “maybe”, ClickHouse reads the block and filters the rows as usual.

The primary key (ORDER BY) works the same way at a coarser level and is almost always the best tool. Skip indexes are for filters on columns that are not the leading part of the key. They only exist on the MergeTree family.

Granules and what an index covers

MergeTree splits each part into granules of index_granularity rows (8192 by default). The primary index has one entry per granule. A skip index entry covers GRANULARITY n granules, so GRANULARITY 1 summarises 8192 rows per entry and GRANULARITY 4 summarises 32768.

INDEX index_name <column or expression> TYPE <type>(<params>) GRANULARITY <n>

• A finer granularity skips more precisely but makes the index bigger and slower to evaluate.
• A coarser one is smaller, but a single matching row keeps the whole group of granules alive.

The one condition that decides if it works

A skip index helps only if matching values are clustered into few blocks. The data is physically ordered by the primary key, so a column that correlates with that order (a timestamp, an increasing id, a status that occurs in bursts), or a value that is rare, gives blocks where the summary says “no”. A column whose values are spread evenly over the whole table gives blocks that all say “maybe”, and the index only costs you disk and insert time.

The demo

A table with several indexes, each matched to a different column shape. The data is built so that each filter has a realistic distribution: a few huge outlier durations, a burst of 503 statuses, one rare URL, one rare message.

CREATE TABLE events (
    event_time DateTime, user_id UInt64, event_type LowCardinality(String),
    url String, duration_ms UInt32, country LowCardinality(String),
    status UInt16,
    message String,
    INDEX idx_duration duration_ms TYPE minmax GRANULARITY 1,
    INDEX idx_status   status      TYPE set(10) GRANULARITY 1,
    INDEX idx_url      url         TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_msg_tok  message     TYPE tokenbf_v1(8192, 3, 0) GRANULARITY 1,
    INDEX idx_msg_ngram message    TYPE ngrambf_v1(3, 8192, 3, 0) GRANULARITY 1
) ENGINE = MergeTree ORDER BY (event_type, event_time);

INSERT INTO events
SELECT toDateTime('2026-03-01 00:00:00') + number, number % 5000, 'view',
       if(number % 400000 = 7, 'https://example.com/rare-page',
          concat('https://example.com/p/', toString(number % 7))),
       if(number % 200000 = 5, 60000, 10 + (number * 7) % 500),
       ['DE','TR','US'][1 + number % 3],
       if(number >= 300000 AND number < 304000, 503, 200),
       if(number % 250000 = 11, 'upstream timeout while reading response', 'ok')
FROM numbers(1000000);

The table has 123 granules. EXPLAIN indexes = 1 reports, per index, how many granules survive.

minmax

Stores the minimum and maximum of the column per block. Best for numeric or date columns where values drift slowly or outliers are rare.

EXPLAIN indexes = 1 SELECT count() FROM events WHERE duration_ms > 50000;

Skip
  Name: idx_duration
  Description: minmax GRANULARITY 1
  Condition: (duration_ms in [50001, +Inf))
  Parts: 1/1
  Granules: 5/123

The query that read 123 granules without the index reads 5. (The query itself returns 5 rows.) The primary key line above it still says 123/123, because duration_ms is not in the key.

set

Stores up to N distinct values per block (set(N)); set(0) means unlimited. Good for low-cardinality columns that cluster, such as a status code that is 503 only in some periods.

EXPLAIN indexes = 1 SELECT count() FROM events WHERE status = 503;

Skip
  Name: idx_status
  Condition: (status in [503, 503])
  Granules: 2/123

Mind the cap. If a block contains more than N distinct values, the index stores nothing useful for that block and cannot exclude it.

An index that does nothing

Here is the same kind of index on a column that does not correlate with the order of the data. user_id cycles through 5000 values, so every block contains all of them:

ALTER TABLE events ADD INDEX idx_user user_id TYPE set(100) GRANULARITY 1;
ALTER TABLE events MATERIALIZE INDEX idx_user SETTINGS mutations_sync = 2;
EXPLAIN indexes = 1 SELECT count() FROM events WHERE user_id = 4242;

Skip
  Name: idx_user
  Granules: 123/123

123 of 123 granules survive. The index is built, stored and maintained on every insert, and skips nothing. If your filter looks like this, the answer is not a better index type but a projection, a different ORDER BY, or a different table design.

Bloom filters

A bloom filter is a probabilistic set: it answers either “definitely not in this block” or “maybe in this block”. It never gives a false negative; the false-positive rate is what you tune.

Block contents	Looking for 3	Answer
1, 2, 3, 4, 5		maybe
4, 5, 6, 7, 8		definitely not
7, 8, 9, 10, 11		maybe (false positive)

Three variants:

• bloom_filter(false_positive_rate): stores values as they are. For equality (=, IN) on strings or numbers, and has() on arrays. The default rate is 0.025.
• tokenbf_v1(size_in_bytes, hash_functions, seed): splits strings into alphanumeric tokens and stores the tokens. For searching whole words with hasToken(), or LIKE patterns on token boundaries.
• ngrambf_v1(n, size_in_bytes, hash_functions, seed): stores n-grams (substrings of length n, usually 3 or 4). For substring search with LIKE '%foo%' or position().

EXPLAIN indexes = 1 SELECT count() FROM events WHERE url = 'https://example.com/rare-page';
-- idx_url: Granules: 3/123

EXPLAIN indexes = 1 SELECT count() FROM events WHERE hasToken(message, 'timeout');
-- idx_msg_tok: Granules: 4/123

EXPLAIN indexes = 1 SELECT count() FROM events WHERE message LIKE '%upstream tim%';
-- idx_msg_ngram: Granules: 4/123 (the token index cannot help with a substring that is not a whole token)

The tokenbf_v1 and ngrambf_v1 indexes need the filter size tuned to your data. The size in bytes is per block; too small and everything becomes a “maybe”. Larger blocks of text need larger filters. Measure with EXPLAIN on realistic data rather than copying numbers from a blog post.

For serious text search, newer releases also have a native full-text index, TYPE text(tokenizer = ...), which builds an inverted index instead of bloom filters. It exists in 26.4 and is worth trying for token-based search; see the full-text index docs for its current status and tokenizers.

Adding an index to an existing table

ADD INDEX affects only new parts. Build it for existing data explicitly:

ALTER TABLE events ADD INDEX idx_country country TYPE set(10) GRANULARITY 1;
ALTER TABLE events MATERIALIZE INDEX idx_country;           -- a mutation; can be heavy
ALTER TABLE events DROP INDEX idx_country;

MATERIALIZE INDEX rewrites index files for every existing part, so schedule it like any big mutation. Check what exists and how large it is:

SELECT name, type, granularity, data_compressed_bytes
FROM system.data_skipping_indices WHERE table = 'events';

How to decide

1. Can the access pattern be served by the primary key? Reorder ORDER BY first (leading with low-cardinality columns that you filter by).
2. Does the filter column correlate with the sort order, or is the wanted value rare? Then a skip index may help. Test it with EXPLAIN indexes = 1 on production-like data.
3. Compare cost: each index adds work to every insert and every merge, and storage.
4. If the column is unrelated to the order and values are common, use a projection or a different table instead.

Settings worth knowing: use_skip_indexes (on by default; turn off to A/B test a query), and materialize_skip_indexes_on_insert.

Reference: data-skipping indexes in the docs.