ClickHouse
A columnar OLAP engine: sparse primary keys, granule skipping, vectorized scans.
ClickHouse stores table data in parts, column by column, in blocks called granules. A sparse primary index records the key value at each granule boundary — it routes key ranges rather than looking up rows. Queries read only the referenced columns, skip whole granules by key range, evaluate predicates block-wise, and merge partial aggregate states. It is built for analytics over append-heavy workloads, not for row-level updates.
MergeTree parts
The MergeTree family writes each insert batch as an immutable part; background merges combine parts and apply TTLs. Granules (~8192 rows by default) are the unit the index and the engine reason about.
Sparse primary key
The primary index stores the key of the first row of each granule — one entry per ~8192 rows, small enough to stay in memory. It finds candidate granule ranges; it is not a B-tree over rows and cannot serve point lookups the way an OLTP index can.
Data skipping indexes
Beyond the primary key, min/max, set, bloom-filter, and n-gram skip indexes let the engine reject granules of non-key columns during the scan.
Vectorized execution
Operators process whole blocks of column values with CPU-vectorized code, and mergeable aggregates (sum, count, min) combine partial states without re-reading rows.
The physical model: columns, parts, granules
A table is a set of parts; each part stores every column as a contiguous compressed file cut into granules. Reading three columns of one granule is three sequential block reads — the layout, not clever caching, is what makes wide-table analytics fast.
The primary key orders data within a part and marks granule boundaries. Because the index is sparse, a key-range predicate prunes whole granules before any data is read; predicates on non-key columns need skip indexes or the block loop.
CREATE TABLE events (
day Date, user_id UInt32, region LowCardinality(String), bytes UInt64
) ENGINE = MergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, region)
SETTINGS index_granularity = 8192;
SELECT region, sum(bytes) FROM events
WHERE day >= '2026-10-07' AND bytes > 1000 GROUP BY region;
Execution: prune, scan blocks, merge
A query pipeline selects the referenced columns, applies primary-key analysis to choose granule ranges, reads those granules column-by-column, evaluates remaining predicates on blocks, and pushes partial aggregate states up to be merged. The walkthrough below traces one such query end to end with honest counters: rows examined, granules skipped, cells read.
Writes are batch inserts that create new parts; mutations (ALTER UPDATE/DELETE) rewrite affected parts asynchronously. Point updates and heavy concurrent row-writes are the wrong workload — this is a columnar OLAP engine, not a general-purpose OLTP database, and comparing its mutation story to PostgreSQL’s is a category error.
Reading a query plan
EXPLAIN PIPELINE prints the operator chain with per-stage parallelism; EXPLAIN indexes = 1 shows the granule ranges key analysis selected. Treat cost numbers as planner estimates of work, not measured latency — the pipeline view plus counters like rows read is the honest way to reason about performance here.
The walkthrough below renders one such pipeline as a step trace, with the counters that matter: granules skipped by the key range, rows examined, cells read per referenced column, and rows surviving the block predicate.
Diagrams
[caption] MergeTree model; PARTITION pruning happens above key analysis in real plans.
The pipeline prunes granules by key range, reads only referenced columns, evaluates predicates block-wise, and merges partial aggregate states.
| table | 32 rows × 4 columns |
| granules skipped | 5/8 (primary key range) |
| cells read | 36 of 128 (user_id never read) |
| rows examined | 12/32 |
| rows after predicate | 6 |
| output rows | 3 |
All steps
- Parse the query — SELECT region, sum(bytes) … WHERE day ≥ 21 AND bytes > 1000 GROUP BY region — only day, region, and bytes are referenced. user_id is never read: a row store would carry every column through the operator; here it costs zero bytes.
- Skip granules by primary key range — The sparse primary index stores min/max of day per granule. Granules covering days 1-20 cannot contain day ≥ 21: 5 of 8 granules are skipped without reading a single row — data skipping.
- Read 3 granules × 3 columns — Only referenced columns of surviving granules are read: 12 rows × day/region/bytes = 36 cells (of 128 in the table). Columnar layout makes each read a contiguous scan of one column’s block.
- Evaluate the predicate per block — bytes > 1000 is evaluated block-by-block (vectorized, not row-at-a-time): 6 of the 12 loaded rows pass into the aggregate. Note this filter is independent of the key — key ranges cannot prune it, only the block loop can.
- Aggregate by region — Each block produces partial sums per region; partial states merge up the pipeline. sum() is a mergeable aggregate, so blocks combine without re-touching rows.
- Output — One row per region with its byte total. Rows examined 12/32, granules skipped 5/8, cells read 36, rows out: 3.
Columnar execution: column pruning, key-range granule skipping, block predicates, and mergeable aggregation — ClickHouse-flavored. Teaching model: 8 granules × 4 rows, one part; real engines run many parts with merges in the background and much larger granules.
Teaching model: 8 granules × 4 rows for legibility; production parts carry ~8192-row granules and run key analysis per part.
[caption] Teaching model: 8 granules × 4 rows for legibility; production parts carry ~8192-row granules and run key analysis per part.
One query, step by step: column pruning, primary-key granule skipping, block-wise predicate evaluation, mergeable aggregation, output — with honest counters for rows and cells read.
Block-level pipeline for the walkthrough query. Units are illustrative relative weights of work, not measured latency.
- 1ReadFromMergeTree (day, region, bytes)12 rows · cost 30
- 2Filter (bytes > 1000)6 rows · cost 8
- 3PartialAggregating (sum by region)9 rows · cost 6
- 4MergingAggregated3 rows · cost 2
[caption] Illustrative pipeline weights for teaching shape — not a measured profile. Use EXPLAIN PIPELINE on a real server for actual stages.
Block-level pipeline for the walkthrough query. Units are illustrative relative weights of work, not measured latency.