How ClickHouse Works: Columnar Storage, MergeTree, and OLAP

← Back to the section

ClickHouse is a database built for a single job: to compute analytics over huge volumes of data very fast. An aggregate query over a billion rows takes seconds on a single server. To understand where that speed comes from and where ClickHouse has its limits, you need to understand how it works inside.

Why regular databases are slow at analytics

Imagine an orders table with thirty columns: id, date, customer, status, amount, address, promo code, and so on. In PostgreSQL every row is stored as a whole — all thirty columns sit next to each other on disk.

When you need one specific order, this is convenient: a single read and you are done. But when you need to compute the average order amount by month over two years, PostgreSQL still reads all thirty columns of every row — even though only two are actually needed: date and amount. The extra twenty-eight columns travel from disk to memory for nothing.

On a table of a million rows this is tolerable. On a table of a billion rows it is a disaster.

Columnar storage

ClickHouse stores data differently: all values of one column sit together, in a separate file. All the amount values are in one place, all the status values in another, all the dates in a third.

That same analytical query over two years now reads exactly two files out of thirty. The other twenty-eight are never even opened.

The second effect is compression. The file with the status column holds a million values drawn from five options ("new", "paid", "delivered", "cancelled", "refund"). Such uniform data compresses dozens of times better than motley rows with heterogeneous fields. A typical compression ratio in ClickHouse is 5–20x, versus 2–3x in row-based databases. Fewer bytes read from disk means a faster query.

ClickHouse and PostgreSQL: different jobs

They are not competitors but tools for different situations.

ClickHouse complements PostgreSQL well: the primary data and all changes live in PG, while the event stream and historical analytics go to ClickHouse. Over tables of tens of millions of rows, ClickHouse builds reports in seconds where PostgreSQL would take minutes.

When you need ClickHouse: analytical GROUP BY queries over large tables slow down your operational database; dashboards take a long time to build; a PG replica dedicated to reporting still can't keep up.

When you don't need it yet: you have fewer than ten million rows (PostgreSQL with proper indexes will handle it on its own), you need frequent updates of the current state, or there is no one to take care of yet another storage system.

MergeTree: how ClickHouse stores data

MergeTree is the core storage mechanism in ClickHouse, and understanding it matters: it determines how to create tables correctly and why some approaches to inserting data break everything.

CREATE TABLE order_events (
    event_time   DateTime,
    order_id     UUID,
    customer_id  UInt64,
    event_type   LowCardinality(String),
    amount       Decimal(18, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time);

Every data insert creates a part on disk — an immutable sorted chunk. A part cannot be changed. Instead, ClickHouse gradually merges small parts into large ones in the background — hence the name MergeTree ("merge tree").

Three important rules follow from this mechanism.

You must insert in large batches. A thousand single-row inserts means a thousand parts. Background merges can't keep up, and the table starts responding with the TOO_MANY_PARTS error. The normal mode is batches of tens or hundreds of thousands of rows every few seconds.

Data is immutable by nature. ALTER TABLE ... UPDATE or DELETE is a mutation: a background rewrite of whole parts. For rare operations — for example, deleting a user's data on request — this is acceptable. As an everyday practice it is destructive.

Fresh data is not immediately "tidied up". Engines that deduplicate or aggregate rows during a merge do not do it right after the insert, but when the merge happens. Until that moment several versions of the same record can coexist in the table.

ORDER BY and how ClickHouse finds data

ORDER BY in MergeTree is not about sorting the query result. It is the physical order of data inside the parts, and it is the main architectural decision when creating a table.

Along this order ClickHouse builds a sparse primary index: one mark not per row, but per block of 8192 rows (this is called a granule). When a query arrives with the filter WHERE event_type = 'order_paid', ClickHouse looks in the index and reads only those granules where such values might occur — the rest is skipped.

The query WHERE order_id = '...' over the table above will read the whole table: order_id is not part of ORDER BY, the index doesn't help, and ClickHouse is forced to scan everything.

Two important consequences:

The primary key in ClickHouse is not about uniqueness. It is a navigator over granules. Duplicates by key are perfectly legal; uniqueness is the application's concern.

Point lookups are not ClickHouse's strong side. "Find one record by id" will read at least a granule of 8192 rows, and without hitting the index — the whole table. Go to PostgreSQL for point reads.

PARTITION BY is the second level of pruning. Partitions by month let a query for May avoid touching data from other months entirely. Deleting old data is also simple: DROP PARTITION. A common mistake is making partitions too fine, for example by day over several years: you end up with thousands of partitions and performance drops. A month is a sensible standard.

Specialized table engines

All the "special" engines are the same MergeTree with additional logic that fires at the moment parts are merged.

ReplacingMergeTree — at merge time keeps only the latest version of a row with the same ORDER BY key. Handy when you need to store the "current state" of an entity: a new version is inserted, and the old one disappears at the next merge. Until the merge, both versions exist at once — you can read "cleanly" via the FINAL modifier or the argMax function.

SummingMergeTree — at merge time sums the numeric columns of rows with the same key. Suitable for counters and pre-aggregated metrics.

AggregatingMergeTree — the same, but for any aggregate states (uniqState, quantileState). The foundation of materialized views.

CollapsingMergeTree — "cancels" a row with a paired record carrying a −1 sign. Used in change streams where subtraction is needed.

Replicated*MergeTree — any of the above plus replication.

Choosing an engine is part of schema design. Events that are only appended — plain MergeTree. Entity snapshots with updates — ReplacingMergeTree. Ready-made aggregates for dashboards — SummingMergeTree or AggregatingMergeTree under a materialized view.

What ClickHouse doesn't have

This is worth knowing in advance, so you don't discover it on a production system.

Transactions. The insert of a single batch into a single partition is atomic. "Transferring money between accounts" is not something you implement here.

Cheap UPDATE and DELETE. Only mutations (rewriting parts) or special engines.

Unique constraints and foreign keys. Data integrity is the responsibility of whoever supplies the data.

Fast lookup by an arbitrary key. The granule is the minimal unit of reading; key–value scenarios are not for ClickHouse.

Frequent small inserts. You need batches of data, otherwise TOO_MANY_PARTS.

Each of these points is a deliberate choice: precisely by giving up these capabilities, ClickHouse aggregates billions of rows where PostgreSQL would take minutes.

In short

• ClickHouse stores data by columns, not by rows — analytical queries read only the needed columns and skip the rest.
• Compression of uniform data in columns is 5–20x; less I/O means faster queries.
• Every insert creates a part; ClickHouse merges parts in the background. You must insert in large batches — a thousand single INSERTs kills performance.
• ORDER BY is the physical order of data and the basis of the sparse index. Queries filtering on ORDER BY fields are fast; on other fields it's a table scan.
• PARTITION BY lets you prune whole partitions by time; a month is a sensible standard.
• The primary key in ClickHouse is a navigator, not a uniqueness constraint.
• ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree are specializations of MergeTree with aggregation logic applied at merge time.
• No transactions, no cheap UPDATE/DELETE, no fast point lookups — these are conscious trade-offs for the sake of analytical speed.

What to read next

• Modeling and queries — ORDER BY in practice, types, materialized views, anti-patterns.
• Integration from the application — driver, data batches, pipeline from PostgreSQL and Kafka.
• Operations — replication, sharding, TTL, monitoring.