Before any internals, it's worth being precise about what kind of database this is — and, just as importantly, what kind of database it deliberately is not. Most confusion and misuse of ClickHouse comes from treating it like a faster Postgres, rather than a different tool built for a different job.
OLTP vs OLAP
Databases like Postgres and MySQL are optimized for OLTP — Online Transactional Processing: many concurrent, small operations, each touching a handful of rows (create an order, update a balance, look up one user), where correctness and low per-operation latency matter most.
ClickHouse is built for OLAP — Online Analytical Processing: fewer, much larger operations, each scanning and aggregating millions or billions of rows (total revenue by country last quarter, p99 latency per endpoint over the last hour), where throughput over huge volumes matters most and a single query touching a lot of data is the normal case, not the exception.
avg(amount) still touches every full row on disk — all four columns come along for free whether you asked for them or not.
avg(amount) reads and decompresses only the amount column — the other three never leave disk.
Where it came from
ClickHouse was built inside Yandex to power Yandex.Metrica, a web analytics product needing to compute arbitrary aggregate reports — on demand, not from a fixed set of pre-built dashboards — over clickstream data arriving at a rate of billions of events a day. It was open-sourced in 2016. That origin still shapes the engine today: everything is built around the assumption that queries are unpredictable in shape but predictable in scale — always "scan a lot, return a little."
When it's the wrong choice
Being honest about this matters more than the feature list. Don't reach for ClickHouse for:
- Single-row lookups and updates at high frequency — the whole architecture (immutable parts, background merges, sparse index) is optimized for scanning ranges, not for "fetch/update exactly one row" at OLTP-style request rates. See Parts & Background Merges and Mutations for why.
- Systems that need real transactions — there is no multi-statement
BEGIN/COMMIT/ROLLBACKwith isolation guarantees across tables the way an OLTP database provides them. - Strong referential integrity — there are no foreign key constraints. Joins work (see Joins), but nothing stops an orphaned reference from being inserted.
- Highly relational, join-heavy schemas — joins are supported and can be fast, but the engine and query planner are not as mature at complex multi-way joins as a decades-old relational database. Denormalizing toward wide tables is often the idiomatic ClickHouse answer instead of normalizing further.
- A queue or a cache — despite the Kafka engine and in-memory engines existing, ClickHouse is not a substitute for a message broker or a key-value cache; those have very different latency and consistency guarantees.
When it's the right choice
- Event/log/metrics analytics at high ingest volume.
- Ad-hoc aggregate queries over huge, mostly-append-only datasets.
- Real-time dashboards that need sub-second answers over billions of rows.
- Time-series data with a natural date/time-based access pattern.
Everything else in this module assumes you're building one of those — and explains, concretely, how ClickHouse makes that fast.