NotesSystem
ClickHouse
Also: Click House · MergeTree
An open-source column-oriented OLAP database, built at Yandex for web analytics and open-sourced in 2016. Its defining choice is the MergeTree engine: sorted, immutable parts merged in the background, with a sparse index over granules of up to 8,192 rows.
Design choices
- Column-Oriented Storage: each part stores one file per column, sorted by the table's ORDER BY key.
- Sparse primary index: one entry per granule (up to 8,192 rows by default), holding the sort-key value of its first row, so about 1,000 entries index 8 million rows; queries skip granules whose key range can't match.
- Parts and merges: each insert writes a new part directly (no WAL); background merges combine parts as equals, without LSM levels. Lightweight deletes write a hidden row-exists mask that merges apply later.
- Columnar Compression: LZ4 by default in self-managed installs, Zstandard in ClickHouse Cloud, per-column codecs (Delta, DoubleDelta, Gorilla, T64) and the LowCardinality dictionary type.
- Skip indexes (min/max, set, Bloom Filter) and projections (extra sort orders) for filters outside the key.
- Vectorized execution over column blocks.
Notable trade-offs
- Small, frequent inserts make too many parts: inserts are delayed at 1,000 active parts per partition and rejected at 3,000 ("Too many parts"). The docs ask for batches of 10,000–100,000 rows, or async inserts.
- Updates and deletes are possible but costly next to append-only workloads.
Used by
- Cloudflare (2018): HTTP analytics for 6M requests/s on 36 nodes; an estimated 36.74 bytes per request versus 1,630 bytes as raw Cap'n Proto logs.
- Uber (2021): moved logging off Elasticsearch, cutting hardware cost by more than half.
Connections
- Mentioned
- Bloom Filter