Typorithm · medium

Sparse Primary Index

sparse-primary-indexclickhouseprimary-indexgranulesindex-granularity

Saved on this device

Full lesson · 8 learning steps Open any exercise for focused practice

Step 1 of 8 · intro card

Learn Sparse Primary Index

Indexing & Partitioning

ClickHouse keeps one primary-index mark per granule of the ORDER BY key, so a range filter on the leading key skips whole granules instead of scanning every row.

A MergeTree part is split into granules of index_granularity rows (8192 by default), and the primary index stores just one mark — the first key value — per granule.

Because the data is sorted by the ORDER BY key, a filter on the leading key can binary-search the marks and read only the granules whose ranges can match.

This is why the index is called sparse: it indexes granules, not individual rows, keeping the index tiny and resident in memory.

index_granularity = 8192 rows per granule.

One primary-index mark per granule.

Leading-key filters skip whole granules.

CREATE TABLE hits
(
    user_id  UInt64,
    event_ts DateTime,
    url      String,
    bytes    UInt64
)
ENGINE = MergeTree
ORDER BY (user_id, event_ts)
SETTINGS index_granularity = 8192;

SELECT count()
FROM hits
WHERE user_id = 42;

Read the implementation

Connect each part of the code to the concept before you type.

1. Sort key builds the index

The primary index is built from the ORDER BY key — one mark per granule.

ORDER BY (user_id, event_ts)

2. Rows per granule

index_granularity = 8192 means each granule holds 8192 rows by default.

SETTINGS index_granularity = 8192

3. Leading-key filter

A filter on the leading ORDER BY column lets ClickHouse skip whole granules.

WHERE user_id = 42

A complete learning loop

Understand

Read the concept map and follow the code breakdown.

Reconstruct

Type the implementation, repair mistakes and work through every recall exercise.

Verify

Use the final check without hints; revisit weak concepts in short practice sessions.