A log table with billions of rows, dashboards filtering on status, users searching error messages with LIKE - and queries guzzling terabytes. ClickHouse is fast only when it can skip work, and that's exactly what data-skipping indexes do: they let the engine avoid reading blocks that can't match a predicate. Used well, they cut scanned bytes by orders of magnitude; used blindly, they add overhead. The trick is knowing which flavor fits which query.

What a data-skipping index is

Skip indexes work on MergeTree tables and store lightweight metadata per granule (default ~8192 rows), so the engine can skip blocks during reads. You define a name, an expression, a TYPE, and a GRANULARITY. Crucially, they don't return row locations the way a B-tree does - they only answer one question: "is it even worth reading this block?"

The three Bloom-based options

The rule of thumb: tokenbf_v1 for word tokens (logs, tags), ngrambf_v1 for free-form substrings, bloom_filter for non-text membership (IDs, codes) - and always test on your own data shape.

Copy-paste examples

bloom_filter on a status code - helps when you filter on rare statuses like WHERE status IN (500, 503):

CREATE TABLE logs_raw (ts DateTime, status UInt16, user_id UInt64, msg String)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (ts, user_id);

ALTER TABLE logs_raw ADD INDEX ix_status status TYPE bloom_filter(0.01) GRANULARITY 2;
ALTER TABLE logs_raw MATERIALIZE INDEX ix_status;   -- if the table already has data

tokenbf_v1 for word search, indexed case-folded so casing can't defeat it:

ALTER TABLE logs_raw ADD INDEX ix_token
  lowerUTF8(msg) TYPE tokenbf_v1(65536, 3, 42) GRANULARITY 2;

SELECT * FROM logs_raw WHERE hasToken(lowerUTF8(msg), 'timeout');
SELECT * FROM logs_raw WHERE msg ILIKE '% connection %';

ngrambf_v1 for substring matches - pick n for the shortest substring you search (n=3 matches 3-char windows; smaller n grows the index and false positives):

ALTER TABLE logs_raw ADD INDEX ix_ngram
  msg TYPE ngrambf_v1(3, 1048576, 3, 123) GRANULARITY 2;

SELECT count() FROM logs_raw WHERE msg LIKE '%conn err%';

Proving it with a micro-benchmark

Don't trust intuition - measure read_rows and read_bytes, and how many granules get dropped. Create a few million sample rows, run the query without the index and capture metrics, add the index plus MATERIALIZE INDEX, then rerun and compare:

-- which indexes would fire, and parts/granules before/after
EXPLAIN indexes=1
SELECT * FROM logs_raw WHERE hasToken(lowerUTF8(msg), 'timeout');

-- canonical impact from the query log
SYSTEM FLUSH LOGS;
SELECT query_duration_ms, read_rows, read_bytes
FROM system.query_log
WHERE query ILIKE '%hasToken%' AND type = 'QueryFinished'
ORDER BY event_time DESC LIMIT 1;

The official docs' canonical example drops a query from 100M rows / 800 MB scanned to 32,768 rows / 360 KB because most granules were skipped. Your numbers will differ - the pattern is what matters.

Choosing between them

Think in blocks, not rows. If a value appears at least once in a block, ClickHouse must read that block even if only one row matches, so skipping pays off only when your predicate is sparse within blocks. That makes correlation with ORDER BY the deciding factor: skip indexes work best when the non-key column loosely correlates with the primary key (certain error codes cluster in certain time ranges, say). If almost every block contains a match, nothing gets skipped. Then pick the flavor: rare IDs or codes → bloom_filter; word search → tokenbf_v1 on lowerUTF8(msg); substrings or space-less languages → ngrambf_v1 with a sensible n.

Tuning checklist

• Start simple: bloom_filter(0.01) on the column you filter most, then validate with EXPLAIN indexes=1.
• Materialize after adding to an existing table, or old parts stay unindexed.
• Granularity: begin at 2 (index block ≈ 2 × 8192 rows); raise it if lookup overhead dominates, lower it if skipping is too coarse.
• Token/n-gram sizing: more bytes means fewer false positives but more disk - estimate before committing.
• Verify impact: track read_rows, read_bytes, and granules dropped. If the numbers don't move, the index isn't helping.
• Know when to bail: if almost every block matches, fix the data layout (ORDER BY) or reach for materialized views or projections instead.

Common pitfalls

Quick reference

• bloom_filter: WHERE error_code IN (502,503), WHERE country='IS' - rare values on non-key columns.
• tokenbf_v1: hasToken(lowerUTF8(msg),'timeout'), ILIKE '% timeout %' - word boundaries in log text.
• ngrambf_v1: LIKE '%conn err%' - free text, partial matches, languages without spaces.

Skip indexes are pruning hints, not magic, and they belong after you've chosen a good ORDER BY. Start with a simple bloom_filter for rare values, add tokenbf_v1 or ngrambf_v1 where text search is hot, and prove the value with EXPLAIN and system.query_log. Pick one painful text filter, add a single index, materialize, and measure read_rows before and after - keep what pays rent and drop what doesn't.