How ClickHouse Works: Columnar Storage, MergeTree, and OLAP

← Back to the section

ClickHouse is a database built for one 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, that is convenient: one 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 million rows this is tolerable. On a billion 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 query over two years 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 far better than motley rows with heterogeneous fields. A typical ratio is 5–20x versus 2–3x in row-based databases, and that is a direct saving on disk reads.

ClickHouse and PostgreSQL: different jobs

Not competitors: each covers its own class of queries.

ClickHouse complements PostgreSQL well: the primary data and all changes live in PG, while the event stream and historical analytics go to ClickHouse.

It is time for ClickHouse when GROUP BY over large tables slows down the operational database and a separate replica for reporting no longer saves you. It is too early while you have fewer than ten million rows: PostgreSQL with proper indexes will handle that on its own, and a second storage system has to be maintained by someone.

MergeTree: how ClickHouse stores data

MergeTree is the core storage mechanism: 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 do it at merge time. Until then several versions of the same record happily 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 — 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.

The same mechanic fits into a small program: a column of forty rows, a mark every eight of them (in ClickHouse — every 8192) and a lookup over the marks instead of a scan of every row:

live example

import java.util.ArrayList;
import java.util.List;

public class SparseIndex {
    static final int GRANULE = 8;

    public static void main(String[] args) {
        List<String> column = new ArrayList<>();
        for (int row = 0; row < 40; row++) {
            column.add(row < 12 ? "order_created" : row < 28 ? "order_paid" : "order_shipped");
        }
        List<String> marks = new ArrayList<>();
        for (int row = 0; row < column.size(); row += GRANULE) {
            marks.add(column.get(row));
        }
        System.out.println("marks: " + marks);

        String wanted = "order_paid";
        int scanned = 0;
        int found = 0;
        for (int g = 0; g < marks.size(); g++) {
            boolean last = g == marks.size() - 1;
            boolean mayContain = marks.get(g).compareTo(wanted) <= 0
                    && (last || marks.get(g + 1).compareTo(wanted) >= 0);
            if (!mayContain) {
                System.out.println("granule " + g + ": skipped without reading");
                continue;
            }
            System.out.println("granule " + g + ": reading " + GRANULE + " rows");
            for (int row = g * GRANULE; row < (g + 1) * GRANULE; row++) {
                scanned++;
                if (column.get(row).equals(wanted)) {
                    found++;
                }
            }
        }
        System.out.println("rows read: " + scanned + " of " + column.size());
        System.out.println(wanted + " found: " + found);
    }
}

Twenty-four rows instead of forty — and only because equal values lay next to each other. Shuffle them, and the marks prune nothing: the wanted value could be in any granule. That is why ORDER BY decides 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 sensible.

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 the schema. Events that are only appended — plain MergeTree; entity snapshots with updates — ReplacingMergeTree; ready-made aggregates for reports — SummingMergeTree or AggregatingMergeTree under a materialized view.

What ClickHouse doesn't have

Worth knowing in advance, not discovering on a production system.

Transactions. An insert of one batch into one partition is atomic as long as it fits into a single block — by default max_insert_block_size, a little over a million rows. "Transferring money between accounts" is not something you implement here.

Cheap UPDATE and DELETE. DELETE FROM only marks rows as deleted — physically they go away at the next merge. To change values you are left with mutations, that is a rewrite of whole parts, or special engines.

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

Each point is a deliberate choice: giving them up is what produces a report over tens of millions of rows in seconds where PostgreSQL would take minutes.

In short

• ClickHouse stores data by columns: a query opens the files of only the columns it named, and uniform values inside a column compress 5–20x against 2–3x in row-based databases.
• Every insert creates a part; ClickHouse merges parts in the background. You must insert in large batches — a thousand single INSERTs ends in TOO_MANY_PARTS.
• ORDER BY is the physical order of data and the basis of the sparse index: a filter on its fields reads a few granules, a filter on anything else reads the whole table. PARTITION BY prunes at a second level, and a month is a sensible partition size.
• The primary key in ClickHouse is a navigator over granules, not a uniqueness constraint.
• ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree are the same MergeTree with logic that fires at merge time, not right after the insert.
• 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, insert batches, the event flow from PostgreSQL and Kafka.
• Operations — replication, sharding, TTL, monitoring.
• PostgreSQL or ClickHouse — the criteria for deciding whether it is time for a second database.