ClickHouse primary index

ClickHouse's primary index is sparse — unlike Postgres B-tree indexes which have one entry per row, ClickHouse stores one index entry per "granule" (8192 rows by default). The index fits easily in memory even for billion-row tables, and lookups locate the relevant granules, which are then scanned sequentially. The primary key is also the sort order on disk (ORDER BY in the MergeTree definition), which is why you pick it carefully: queries that filter by the leading columns of the primary key are blazing fast; queries that filter by columns not in the primary key do a full scan of the partition. Typical pattern: ORDER BY (tenant_id, date, user_id) — filters on tenant_id alone or tenant_id + date hit index; filter on just user_id doesn't. Secondary "data-skipping" indexes (minmax, set, bloom filter) exist for non-primary-key filters, but they're hints that let ClickHouse skip granules, not full indexes.