ClickHouse
An open-source columnar OLAP database built for very high-speed aggregations over massive datasets. It scans billions of rows per second per node. Optimized for analytical reads, not OLTP updates.
- Log/event analytics, dashboards over billions of rows, OLAP.
- Real-time analytics ingestion.
- OLTP with frequent updates.
- Point lookups as the primary access pattern.
Definition
ClickHouse stores data columnar, compressed, and sorted by primary key, enabling vectorized execution over billions of rows. It's a SQL engine optimized for scans and aggregations; updates/deletes are expensive by design.
Why it exists
Row stores are too slow for analytical scans over petabytes. Columnar storage + compression + vectorized execution make interactive analytics on huge data feasible and cheap.
How it works
Data is in columnar parts sorted by key; queries read only needed columns, decompress in cache, and run vectorized. MergeTree engines handle ingestion and merging. Replication and sharding scale reads/writes. Materialized views pre-aggregate common queries.
Scaling limits
Bad at point updates/deletes and high-cardinality JOINs. Small frequent inserts cause many parts (batch instead). Single large queries can starve others; needs concurrency controls.
Cost shape
Very cost-effective analytics: high compression and CPU efficiency. Blow-ups: too many small parts, unbounded materialized views, and over-sharding.
Alternatives
- BigQuery — Serverless, pay-per-query, no ops, but cloud cost and less control.
- DuckDB — Embedded analytics on smaller/local data.
Failure modes
- Too many small inserts creating part churn.
- Expensive JOINs on large tables.
- Memory blow-ups on huge GROUP BY.