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.