Courses › System Design › Key Technologies

ClickHouse

Columnar OLAP storage, MergeTree, materialized rollups and analytics at scale.

ClickHouse is an open-source columnar OLAP database built for fast analytical queries over billions of rows: "clicks per ad per hour this week," "p99 latency by endpoint," "top 100 videos by views today." In interviews, it (or a peer like Apache Druid, Apache Pinot or a cloud warehouse) is the answer when users need to slice and aggregate large event data interactively.

OLTP vs OLAP

Why columnar storage is fast

  • A query reads only the columns it uses. SELECT ad_id, count() ... GROUP BY ad_id on a 50-column table reads 1–2 columns' worth of data.
  • Values in one column are similar, so they compress extremely well (often 10x or more), which means less disk I/O.
  • Processing runs vectorized over blocks of column values, using CPU efficiently.
  • Data is sorted by a primary key / ORDER BY clause, and a sparse index lets queries skip blocks that can't match.

The MergeTree engine

  • Inserts write immutable sorted parts; background merges combine them into larger ones, like an LSM tree.
  • Insert in batches (thousands of rows at a time), not row by row — buffer in Kafka or the client.
  • Partition tables by time (e.g. by day) so old data can be dropped cheaply and queries can skip whole partitions.
  • Choose the ORDER BY key to match your filters, e.g. (ad_id, event_time) for per-ad time-range queries.
  • Variants pre-aggregate during merges: SummingMergeTree and AggregatingMergeTree, often fed by materialized views that roll raw events into per-minute or per-hour tables.
[Producers] -> {Kafka: events} -> [ClickHouse Kafka engine / Flink] -> (raw_events table)
(raw_events table) -> [Materialized view] -> (clicks_per_minute table)

Scaling

  • Shards split data across nodes; a Distributed table fans queries out and merges the results.
  • Replicas copy each shard for availability, coordinated through ClickHouse Keeper (ZooKeeper-compatible).

When to use it

  • Analytics dashboards over event, log or metrics data
  • Real-time aggregation results that users query with flexible filters and groupings
  • Time-series and observability data at high volume

When not to

  • Transactional workloads with frequent updates or deletes of individual rows
  • Point lookups by key at high QPS — a key-value store or Redis is better
  • Small data — PostgreSQL handles analytics on a few million rows just fine

[!INTERVIEW] Serving the advertiser dashboard "Flink writes per-minute click counts to ClickHouse, in a table partitioned by day and ordered by (advertiser_id, ad_id, minute). A materialized view rolls minutes into hours. Dashboard queries filter by advertiser and time range, so they read only a few sorted blocks and return in tens of milliseconds even over months of data."