How ClickHouse Works: Columnar Storage, Vectorized Execution, and the MergeTree Engine Behind Real-Time Analytics

You have a Postgres database. It runs your application fine. Then your product team asks for a dashboard: daily active users over the last 90 days, broken down by country, plan tier, and feature flag cohort. Your query takes 12 seconds. You add indexes. It takes 8 seconds. You add a read replica. It takes 8 seconds on a faster machine. The problem is not your database configuration; it is that the workload is analytically shaped, and row-oriented storage is structurally wrong for it.

ClickHouse is the answer many teams reach for at this point. Understanding why it works requires going below the SQL surface into the storage layout, the execution model, and the engine mechanics. That is what this article covers.

Why Columnar Storage Changes the Math

A row-oriented database stores each row contiguously: all columns for row 1, then all columns for row 2, and so on. This is optimal for OLTP: fetching a single user record reads one contiguous block. Analytical queries, though, touch a small number of columns across millions of rows. If you want the sum of revenue across 50 million events, a row-oriented store reads every column of every row and discards everything except revenue. You pay the I/O cost for data you throw away.

ClickHouse stores each column in its own file on disk. A query that touches only revenue and country reads only those two column files. On a wide table with 40 columns, a two-column aggregation reads roughly 5% of the data a row-oriented store would read. That is the first reason ClickHouse is fast: it simply reads less.

Compression as a Force Multiplier

Column files contain values of the same type and often similar magnitude. This makes them compress dramatically better than row-oriented files. ClickHouse applies LZ4 by default (and optionally ZSTD) and also supports codec pipelines that work at the column level before general compression.

CREATE TABLE events
(
    event_time  DateTime CODEC(DoubleDelta, LZ4),
    user_id     UInt64   CODEC(Delta, LZ4),
    revenue     Float64  CODEC(Gorilla, LZ4),
    country     LowCardinality(String)
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);

DoubleDelta encodes the difference of differences in timestamps, which collapses monotonically increasing time series to near-zero entropy. Gorilla does the same for floating-point sequences. LowCardinality wraps string columns with low distinct counts (country, status, plan) into dictionary encoding automatically. Real-world analytics tables frequently compress 5:1 to 20:1 depending on data distribution, and compressed data fits in page cache more densely, so cache efficiency improves along with disk I/O.

The MergeTree Engine Family

MergeTree is the core storage engine in ClickHouse. Almost everything in production runs on it or one of its variants: ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, CollapsingMergeTree, and the replicated counterparts of each.

Parts and Merges

When ClickHouse writes data, each INSERT creates one or more immutable on-disk structures called parts. A part is a directory containing one file per column, a primary index file, a marks file, and metadata. Parts are never mutated after creation. Instead, ClickHouse merges parts in the background into larger parts and deletes the originals. This is the same LSM-tree-inspired pattern that RocksDB and Cassandra use.

data/events/
  20240101_1_1_0/     <- part name: partition_min_block_max_block_level
    event_time.bin
    user_id.bin
    revenue.bin
    country.bin
    primary.idx
    checksums.txt
  20240101_2_2_0/
    ...
  20240101_1_2_1/     <- merged part covering blocks 1-2, level 1
    ...

The merge process is not just housekeeping. It is the mechanism by which ReplacingMergeTree deduplicates rows (keeping the row with the highest ver value per key), SummingMergeTree pre-aggregates numeric columns, and CollapsingMergeTree cancels sign-paired rows. If you rely on these semantics, be aware that the guarantee is eventual: a query may see duplicates or unsummed rows in recently written parts. FINAL forces a merge at query time, which has a cost.

The Primary Index: Sparse, Not B-Tree

ClickHouse’s primary index is not a B-tree. It is a sparse index: it stores the first key value of every index_granularity rows (default: 8192 rows per granule). The entire primary index for a multi-billion-row table typically fits in memory (a few hundred MB) and stays there.

primary.idx layout (simplified):
  granule 0:  key at row 0
  granule 1:  key at row 8192
  granule 2:  key at row 16384
  ...

When you query WHERE event_time BETWEEN '2024-01-01' AND '2024-01-02', ClickHouse binary-searches the primary index to find the range of granules whose key range overlaps the filter, then reads only those granules from the column files (guided by the marks file, which records the byte offset of each granule in each column file). All other granules are skipped entirely.

This is why the ORDER BY in a MergeTree definition matters so much: it determines the sort order on disk, which determines how effective the sparse index is at skipping data. A table ordered by (tenant_id, event_time) will skip very efficiently on tenant_id = X AND event_time BETWEEN ..., but not on user_id = Y alone.

Partition Pruning

Partitioning is a coarser-grained mechanism that operates before the primary index. The common pattern is partitioning by month or day:

ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_time, user_id);

When a query includes a filter that maps to the partition key, ClickHouse skips entire partition directories before looking at any index. A 3-year table with monthly partitions means a single-month query skips 35 out of 36 partition directories. Combined with primary index granule skipping within the partition, the actual data read can be a small fraction of the total table size.

Avoid over-partitioning. Partitioning by day on a high-insert workload creates thousands of small parts, each with fixed overhead. Monthly or weekly partitions are usually the right granularity unless you have a hard data retention requirement that maps to partition drops.

Vectorized Query Execution

The classical query execution model is the Volcano iterator: each operator calls next() on its child, which returns one row, which propagates up the tree. This is elegant and composable but miserable for modern CPUs. One row at a time means constant function call overhead, poor branch prediction, and essentially no opportunity for SIMD.

ClickHouse uses vectorized execution: operators process blocks of rows (typically 65,536 rows per block) at a time. This has two compounding benefits.

SIMD Utilization

When you filter 65,536 values of revenue > 100.0, the CPU can apply the comparison to 4 or 8 Float64 values per instruction using AVX2 or AVX-512. The branch predictor sees a tight, predictable loop rather than random row dispatch. The result fits neatly in L1/L2 cache. ClickHouse explicitly uses SIMD intrinsics in its filter kernels, hash aggregation, and string matching functions.

Batch Aggregation

Hash aggregation over a column of 65,536 values inserts them into a hash table in batch, amortizing the hash computation cost. For COUNT(*) and SUM(revenue) this is nearly a memory bandwidth problem, not a compute problem, and ClickHouse is deliberately designed to stay close to memory bandwidth limits.

The difference in practice: a Volcano-model database running an aggregation over 500 million rows might execute 500 million next() calls. ClickHouse executes roughly 7,600 block-level calls (500M / 65,536). The overhead difference alone accounts for a significant fraction of the performance gap.

Materialized Views and Projections

Materialized Views as Continuous Transforms

A ClickHouse materialized view is not a stored query result refreshed on a schedule. It is a trigger: when data is inserted into the source table, the view query runs on the inserted block and writes the result to a target table. The target table is a real table with its own engine, typically AggregatingMergeTree for incrementally-maintained aggregates.

-- Target table for the materialized view
CREATE TABLE events_by_country_daily
(
    date        Date,
    country     LowCardinality(String),
    event_count AggregateFunction(count),
    revenue_sum AggregateFunction(sum, Float64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (date, country);

-- The materialized view
CREATE MATERIALIZED VIEW events_by_country_mv
TO events_by_country_daily
AS
SELECT
    toDate(event_time)              AS date,
    country,
    countState()                    AS event_count,
    sumState(revenue)               AS revenue_sum
FROM events
GROUP BY date, country;

Queries against events_by_country_daily use countMerge() and sumMerge() to finalize the partial aggregates. This pattern pre-computes aggregates at insert time, so dashboard queries that previously scanned billions of rows now scan a few thousand rows in the pre-aggregated table.

The tradeoff is consistency under concurrent writes and the insert amplification cost. Every insert into events triggers an insert into the target table. On very high-throughput writes, multiple materialized views compound this amplification.

Projections

Projections are a newer feature that embeds an alternative sort order (or pre-aggregation) directly inside the table’s parts. Unlike a materialized view, a projection does not require a separate table and is automatically maintained during merges and mutations.

ALTER TABLE events
    ADD PROJECTION revenue_by_user
    (
        SELECT user_id, sum(revenue), count()
        GROUP BY user_id
    );

ALTER TABLE events MATERIALIZE PROJECTION revenue_by_user;

ClickHouse’s query planner will automatically use this projection if the query fits (same columns, compatible filter). The benefit is zero join overhead and transparent usage. The cost is additional storage per part and longer merge times.

Replication and Sharding

ClickHouse Keeper

ClickHouse historically used Apache ZooKeeper for distributed coordination. ClickHouse Keeper is its own Raft-based replacement, shipped as part of the ClickHouse binary. You run it on three or five nodes for quorum. It serves the same API as ZooKeeper, so the migration from ZK to Keeper is a configuration change.

Keeper tracks replica state: which parts each replica has, which mutations are pending, and which log entries have been committed. Replicated table engines (ReplicatedMergeTree) register themselves with Keeper and exchange replication logs.

ReplicatedMergeTree

CREATE TABLE events ON CLUSTER my_cluster
(
    event_time DateTime,
    user_id    UInt64,
    revenue    Float64
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

The macro substitutions {shard} and {replica} come from config.xml. Each replica independently writes parts it receives via insert and fetches parts from other replicas when it falls behind. Replication is asynchronous by default. A write completes when the local replica writes the part; Keeper propagates it to other replicas in the background. You can increase the insert_quorum setting to require acknowledgment from N replicas before the insert returns.

Distributed Tables

A Distributed table is a proxy layer that routes queries across shards:

CREATE TABLE events_distributed ON CLUSTER my_cluster
AS events
ENGINE = Distributed(my_cluster, default, events, rand());

The last argument (rand()) is the sharding key expression. Writes to events_distributed hash the key and route each row to a shard. Queries fan out to all shards and aggregate results on the initiator node. This is where ORDER BY semantics get complicated: the global sort order is not preserved across shards unless you read from a specific shard or use ORDER BY ... LIMIT with care.

Tradeoffs and When to Use ClickHouse

Dimension	ClickHouse	PostgreSQL	TimescaleDB	DuckDB
Query pattern	Analytical, aggregate-heavy	Mixed OLTP/OLAP	Time-series OLAP	Local analytical
Write model	Batch inserts (bulk)	Row-level, transactional	Row-level, transactional	Bulk or file import
Transactions	None (no multi-row ACID)	Full ACID	Full ACID (via PG)	None
Joins	Limited; avoid large joins	Excellent	Excellent (via PG)	Excellent
Deployment	Cluster or single node	Single node or replica set	Single node or replica set	In-process library
Operational complexity	High (Keeper, shards)	Low	Low-medium	Near zero
Compression	Excellent (5:1 to 20:1)	Moderate	Moderate-good	Excellent
Mutation support	Eventually consistent (merges)	Immediate	Immediate	N/A
Best fit	Append-heavy event/log data	Application database	Sensor/metric time-series	Local analytics, notebooks

ClickHouse is the right choice when: you have billions of rows that are primarily appended (events, logs, metrics, audit trails), queries aggregate across most of those rows on a small column subset, and you can tolerate eventual consistency semantics on updates and deletes.

ClickHouse is the wrong choice when: you need row-level transactions, your query patterns are highly selective on primary keys (OLTP), you need complex joins across normalized schemas, or your data volume is under 50-100 million rows (where Postgres with proper indexing and TimescaleDB are simpler and sufficient).

DuckDB deserves specific mention: if your analytics workload runs on a single machine and data fits in local storage, DuckDB delivers ClickHouse-class columnar performance with zero infrastructure. It reads Parquet, CSV, and JSON directly, and runs as an in-process library. For analytics in a data pipeline, notebook, or small-scale product, DuckDB is often the right answer before adding ClickHouse’s operational overhead.

Production Considerations

Schema design is the highest-leverage decision. The ORDER BY key determines data locality on disk. Choose it based on your most common query filter pattern, not your logical primary key. If 80% of queries filter on tenant_id first, that goes first in ORDER BY. Accept that other query shapes will do full scans.

Control insert batch size. ClickHouse creates one part per insert statement. Thousands of tiny inserts create thousands of parts and trigger merge pressure. Insert in batches of at least 100,000 rows (ClickHouse’s own guideline is 1,000-100,000 rows per insert, with larger batches preferred). Use a buffer table or an async insert queue in your application layer to batch writes.

Monitor merge backlogs. Query system.merges to see active merges and system.parts to count parts per table. A part count above 300 per partition is a warning sign. Tune max_bytes_to_merge_at_max_space_in_pool and number_of_free_entries_in_pool_to_lower_max_size_of_merge if merges are falling behind.

Plan for deduplication before you need it. ReplacingMergeTree is not a real-time deduplication guarantee. If your upstream producer can replay events, either use insert_deduplicate (which ClickHouse tracks by block checksum for 100 recent inserts) or architect your pipeline to handle idempotency at the application level. Do not use ReplacingMergeTree FINAL as a correctness guarantee in a high-write production system; the FINAL modifier triggers an expensive merge at read time.

Size your primary index granularity to your working set. The default index_granularity = 8192 rows per granule works well for most workloads. If your rows are very wide, consider lowering it to 1024-2048 to improve skip precision. If rows are narrow and writes are extremely high, a larger granularity reduces index memory pressure.

Use ClickHouse Keeper, not ZooKeeper, for new deployments. Keeper is operationally simpler, ships with the binary, and has better latency characteristics for ClickHouse’s replication protocol.

Profile queries with EXPLAIN and system.query_log. EXPLAIN indexes=1 shows which granules are read after index pruning. system.query_log gives per-query read bytes, memory usage, and execution time. These two sources tell you whether your schema design is working.

The core insight is that ClickHouse’s performance is not magic: it is the consequence of a coherent set of design decisions. Columnar layout reduces I/O. Compression increases effective cache capacity. The sparse index enables granule-level skipping without B-tree overhead. Vectorized execution saturates CPU pipelines. Materialized views push aggregation cost to write time. Each of these decisions makes the same tradeoff: optimize for append-heavy analytical reads, at the cost of update flexibility and operational simplicity. Know the tradeoff, and ClickHouse becomes a precise tool rather than a vague “fast database.”