Skip Indexes

The primary key only helps if your query filters on a column that is part of ORDER BY. Skip indexes give ClickHouse a way to avoid reading granules based on other columns too — without physically reordering anything.

What a skip index actually stores

Unlike a Postgres index, a skip index doesn't point at rows. It stores a small summary — per group of granules — and uses that summary to decide whether a granule could possibly contain a match. If it can't, the granule is skipped entirely: never decompressed, never scanned.

ALTER TABLE requests
    ADD INDEX status_idx status_code TYPE minmax GRANULARITY 4;

The first two granules have a min/max range of 200–304 — the index proves 500 cannot be in there, so they're skipped. The third granule's range includes 500, so it gets scanned. No sorting was required for this to work; the index just needed the data to already be somewhat clustered — which is usually true if status_code correlates at all with time, or with a column earlier in ORDER BY.

The index types

• minmax — stores min and max per block of granules. Cheap, effective for numeric/date columns with any locality.
• set(N) — stores up to N distinct values per block. Good for low-cardinality columns where exact-match filtering is common.
• bloom_filter — probabilistic membership test; can have false positives (scans a granule unnecessarily) but never false negatives (never skips a granule that has a match).
• tokenbf_v1 / ngrambf_v1 — bloom filters over tokens or n-grams, for accelerating LIKE / substring search inside text columns.