1. What ClickHouse is, and why it exists

Every other guide here describes a database that answers questions about one thing: this user, this order, this key, this document. Find it, return it, change it.

ClickHouse answers a different question: what is true across all of them?

SELECT country, count(), avg(duration)
FROM page_views
WHERE date >= '2026-01-01'
GROUP BY country
ORDER BY count() DESC

Forty billion rows. Answer in under a second, on hardware you can actually afford.

That is the entire proposition, and once you have seen it work, going back to running that query on PostgreSQL is genuinely painful.

Two different jobs

Databases split into two families, and the split is not marketing.

OLTP — Online Transaction Processing. PostgreSQL, MySQL, MongoDB. Many small operations, each touching a few rows, mixing reads and writes, needing transactions and immediate consistency. “Show me order 4471. Update its status.”

OLAP — Online Analytical Processing. ClickHouse, BigQuery, Snowflake, DuckDB. Few large queries, each touching an enormous number of rows but only a handful of columns, almost entirely reads, appended to in bulk. “Revenue by country by month for two years.”

These are not two points on a spectrum where a good database sits in the middle. They demand opposite physical layouts, and you cannot have both at once.

Why a row store cannot win at analytics

Here is the whole argument, and it is worth going through slowly because everything about ClickHouse follows from it.

PostgreSQL stores rows contiguously. A page holds complete rows, one after another:

   ROW STORE — one page
   ┌────────────────────────────────────────────────────────────┐
   │ 4471│ada@ex…│London│UK│2026-08-20│1420│…│ 4472│bob@ex…│ …  │
   └────────────────────────────────────────────────────────────┘
     id   email   city   cc   date       ms

That layout is perfect for SELECT * FROM users WHERE id = 4471 — one page read gives you everything about that user.

Now run the analytics query. You need country and duration from 40 billion rows. But country is glued to email, city, date, and forty other columns you did not ask for. To read one column you must physically read every byte of every row.

If a row is 400 bytes and you need 12 of them, you have just read 97% of that data for nothing. At 40 billion rows that is 16 TB pulled off disk to answer a question that needed 480 GB of actual information.

A column store keeps each column in its own file:

   COLUMN STORE — separate files
   id       │ 4471 │ 4472 │ 4473 │ 4474 │ …
   email    │ ada@ │ bob@ │ cy@  │ dee@ │ …
   country  │ UK   │ UK   │ FR   │ UK   │ …    ← read only this
   duration │ 1420 │  890 │ 2100 │  340 │ …    ← and this

Read two files. Skip the rest entirely. That is a 30× reduction in I/O before any other trick, and it is not an optimisation — it is a different physical shape.

Then the second effect arrives, and it is bigger than the first.

Compression is the real prize

Look at that country column. Millions of consecutive values, drawn from about 200 distinct strings, frequently repeating. Now look at the row store’s page: country interleaved with unique emails, timestamps and IDs.

Similar values sitting next to each other compress extraordinarily well. Random-looking bytes do not.

Typical compression ratios in practice:

   country (low cardinality)         50–200×
   timestamps (sorted, delta-coded)   20–100×
   status codes / enums               50–500×
   counters / metrics (delta+bitpack) 10–30×
   random UUIDs                        1–2×   ← the exception that proves it

A 10:1 overall ratio on real data is ordinary. That means ten times less to read from disk, and ten times more of it fits in page cache. Combined with reading only the columns you need, the difference against a row store on an analytical query is routinely two orders of magnitude.

This is the answer to “why is ClickHouse so much faster than PostgreSQL at this?” It is not clever code — though the code is clever. It is reading a hundredth of the bytes.

The third effect: vectorised execution

Row stores process one row at a time through the query plan. For each row: check the filter, extract the column, add to the aggregate. Per row, that is a function call, a branch, and a cache miss.

ClickHouse processes data in blocks of ~65,536 values from a single column, laid out contiguously in memory as a plain array.

   ROW AT A TIME              VECTORISED
   for each row:              for each block of 65536:
     if (filter(row))           mask = compare_simd(column, value)
       sum += row.value         sum += sum_simd(column, mask)

   1 value per iteration      8–16 values per CPU instruction
   branch per row             no branches in the inner loop
   pointer chasing            sequential memory, prefetcher happy

A tight loop over a contiguous array of UInt32 is exactly what a modern CPU is built for: SIMD instructions process 8 or 16 values at once, the branch predictor is never wrong because there are no branches, and the prefetcher keeps the pipeline fed.

Column storage is what makes vectorisation possible. You cannot fill a SIMD register with duration values if they are 400 bytes apart.

Three effects, multiplying: fewer columns read, better compression, faster processing. That is where 100× comes from.

What ClickHouse gives up

Nothing is free. Be clear about the price before you go further.

No real transactions. No BEGIN/COMMIT across statements, no rollback, no isolation between concurrent writers. An insert of a block of rows is atomic; that is the whole guarantee.

Updates and deletes are expensive and asynchronous. ALTER TABLE ... UPDATE exists and it is a mutation: it rewrites every affected data part in the background. It is not a transactional update; it is a bulk rewrite that eventually completes. If your workload updates individual rows frequently, ClickHouse is the wrong tool and no amount of tuning changes that.

No unique constraints, no foreign keys, no enforced primary keys. The “primary key” in ClickHouse does not identify a row uniquely — it is a sorting and indexing key. Insert the same row twice and you have it twice.

Single-row lookups are mediocre. ClickHouse can fetch one row by key, and it will take milliseconds where PostgreSQL takes microseconds — because it must locate and decompress a whole block to give you one value.

Eventual consistency in a cluster. Replication is asynchronous by default. A write acknowledged by one replica may not be visible on another for a moment.

Joins are limited compared to a relational planner. Chapter 10 covers this honestly; it is the most common source of disappointment.

Small, frequent inserts are actively harmful. Inserting one row at a time creates one data part per insert, and the background merge process cannot keep up. You get the famous TOO_MANY_PARTS error. ClickHouse wants batches of thousands to millions of rows.

Notice what all of these have in common: they are precisely the things an OLTP database is built to do. ClickHouse gave them up on purpose. That is the trade, and it is what buys the speed.

Where it came from

2009 — Yandex.Metrica. Yandex, Russia’s largest search engine, ran a web analytics product — a competitor to Google Analytics. Metrica needed to produce custom reports over billions of page views, on demand, without pre-aggregating everything in advance. Existing systems either could not do it or cost more than the product earned.

Alexey Milovidov started building a column-oriented engine internally. The name is a compression of “Clickstream Data Warehouse.”

2016 — open sourced under Apache 2.0. It spread quickly because it was startlingly fast on ordinary hardware and had few dependencies.

2021 — ClickHouse, Inc. spun out of Yandex as an independent company, with the core team, keeping the Apache 2.0 licence. This matters relative to the licensing stories in the Redis, Elasticsearch and MongoDB guides: ClickHouse is genuinely open source and has stayed that way.

Today. ClickHouse Cloud exists as a managed service; the open-source project releases very frequently — roughly monthly — with LTS releases for people who prefer to move less often.

Version pace

ClickHouse releases fast, and features arrive fast. Version numbers are YY.M — so 25.3 is March 2025. This guide describes behaviour that has been stable for several years, and flags things that are newer. Check the release notes for anything you depend on; a feature that was experimental last year may be default now, and occasionally the reverse.

The promises ClickHouse makes

Linear scan speed measured in billions of rows per second per server. Not a marketing figure — a single modern server routinely scans several billion simple rows per second across cores.

Compression that changes the economics. 10:1 is ordinary. Your entire dataset may fit on hardware you would not have considered.

Real SQL. Not a query DSL. JOIN, GROUP BY, window functions, CTEs, subqueries, plus several hundred functions of ClickHouse’s own. Your BI tool can connect to it.

Linear horizontal scaling. Add shards for more data and throughput; add replicas for availability and read capacity.

Atomic block inserts. A single INSERT of a block either lands completely or not at all.

Ingestion built in. Kafka, S3, HTTP, files, and other databases as native table engines — you can often skip an ETL tool entirely.

The one idea that explains everything else

ClickHouse sorts your data on disk by a key you choose, stores each column separately and compressed, and answers queries by reading the smallest possible number of compressed blocks.

Every design decision follows: why the sorting key matters more than anything else you configure (chapter 5), why the primary index is sparse rather than pointing at rows (chapter 3), why updates are painful (they break the sorted, compressed layout), why small inserts are painful (they create unsorted fragments), and why merges run forever in the background (they restore the sorted layout).

What this guide will do

  YOU ARE HERE
       │
       ▼
  1. What and why ──────────── the OLAP trade
  2. Columns and compression ─ the physical layout and codecs
  3. MergeTree ─────────────── parts, granules, the sparse index
  4. The MergeTree family ──── the engines that do work while merging
  5. Keys, partitions, TTL ─── the decisions that matter most
       │
       ▼
  6. An insert, traced ─────── parts, merges, and TOO_MANY_PARTS
  7. A query, traced ───────── index analysis and vectorised execution
       │
       ▼
  8. Types and the dialect ─── LowCardinality, Nullable, arrays, maps
  9. Aggregation ───────────── combinators and aggregate states
 10. Joins and dictionaries ── the honest chapter
 11. Materialised views ────── insert triggers, not views
 12. Distributed ClickHouse ── shards, replicas, Keeper
       │
       ▼
 13. Tuning and operations ─── system tables and the settings that matter
 14. Failure modes ─────────── what actually goes wrong
 15. When not to use it ───── the honest boundaries
 16. Cheat sheet ──────────── the one-page reference

One structural note. Chapter 5 — the ordering key and partitioning — is the most consequential chapter in this book. In ClickHouse, that one decision determines whether a query reads 4,000 rows or 40 billion. It comes after chapters 3 and 4 because you cannot choose it well until you understand what a granule is and what merges do.

What to take from this chapter

• OLTP and OLAP want opposite physical layouts. ClickHouse chose OLAP completely.
• A row store must read every byte of every row to read one column. A column store reads only the columns you asked for.
• Compression is the bigger win — similar values stored adjacently compress 10–100×, which multiplies with reading fewer columns.
• Vectorised execution over 65,536-value column blocks makes the CPU work the way it is designed to.
• The price: no transactions, expensive asynchronous updates and deletes, no unique constraints, mediocre point lookups, limited joins, and a hard requirement for large batched inserts.
• It is genuinely Apache 2.0 open source, from Yandex.Metrica, now an independent company.
• The one idea: sorted by your key, stored by column, compressed, read as few blocks as possible.

Next: what “stored by column” actually means on disk.