On this page

ClickHouse Internals

ClickHouse is an open-source column-oriented database designed for online analytical processing (OLAP). Created at Yandex for the Yandex.Metrica web analytics system (processing over 20 billion events per day), it has become the default choice for high-performance analytical workloads — event analytics, log analysis, time-series aggregation, and real-time dashboards.

ClickHouse is fast. Not "fast for an analytics database" — fast in absolute terms. A single server can scan billions of rows per second for aggregation queries. This performance comes from three key architectural decisions: column-oriented storage, vectorized query execution, and aggressive use of hardware capabilities (SIMD instructions, memory bandwidth, disk I/O parallelism).

This page goes from first principles (why a column store exists at all) down to the byte level (what is inside a part directory, what a mark is, how the sparse primary index prunes granules), then up through the engine family, query execution, ingestion, distribution, mutations and operations, and finishes with a full schema-design walkthrough and interview questions. If you are new to the OLTP/OLAP split, read OLTP vs OLAP first; if you want a head-to-head with Druid and Pinot, see ClickHouse vs Druid vs Pinot — this page deliberately does not repeat that comparison in depth.

Why ClickHouse Exists

The Yandex.Metrica Problem

Yandex.Metrica is a web analytics service (think Google Analytics) and, around 2010–2014, was one of the largest in the world. Its original architecture did what most analytics products of that era did: it pre-aggregated incoming page views and clicks into fixed reports (visits per day per site, top referrers per day, and so on) and stored those aggregates. That design has two fatal problems:

  1. The set of reports is frozen. A customer who wants "conversions from mobile Safari users who arrived from a specific campaign and viewed at least three pages" cannot get it, because nobody pre-aggregated that combination. The number of possible combinations of dimensions is effectively infinite.
  2. Aggregates explode in size. Pre-aggregating by many dimensions produces tables that can be larger than the raw data, and every new report means a new pipeline and a backfill.

The alternative is to store non-aggregated events and compute any report on the fly at query time. That requires a database that can scan and aggregate billions of rows in well under a second, on commodity hardware, while ingesting a firehose of new events. Nothing available at the time could do that at Yandex's scale and cost, so Alexey Milovidov and team built one. Development started around 2009, ClickHouse went into production for Metrica in 2012, and it was open-sourced under Apache 2.0 in June 2016. As of 2014 Metrica was tracking roughly 12 billion events per day; later public descriptions cite over 20 billion events per day and a cluster of hundreds of servers holding tens of trillions of rows. ClickHouse, Inc. was spun out of Yandex as an independent company in 2021 and runs ClickHouse Cloud.

Design philosophy in one sentence

ClickHouse is built around a single assumption: reads are huge scans over a few columns, writes are large append-only batches, and updates are rare. Every internal decision — immutable sorted parts, sparse indexes, background merges, vectorized execution — follows from optimizing for that workload and refusing to compromise for OLTP.

Where It Is Used (Well-Documented Deployments)

CompanyWorkloadPublic source
CloudflareHTTP and DNS analytics behind customer dashboards and the GraphQL Analytics API. Their 2018 post "HTTP Analytics for 6M requests per second using ClickHouse" describes replacing a PostgreSQL/Citus pipeline.Cloudflare engineering blog
UberLog analytics platform replacing an ELK stack; schema-agnostic log storage with significantly lower hardware cost than Elasticsearch.Uber engineering blog (2021)
eBayOLAP for monitoring and event data, run on Kubernetes; migrated from Apache Druid.eBay tech blog (2020)
GitLabBackend for observability features (error tracking, tracing) and product analytics.GitLab handbook and engineering docs
YandexMetrica, the original workload.ClickHouse documentation history

A pattern repeats across these: each team first tried a general-purpose store (PostgreSQL, Elasticsearch, Druid) for event-shaped data, hit cost or flexibility limits, and moved to ClickHouse for raw-event storage with on-the-fly aggregation.

When ClickHouse Beats Other OLAP Engines

  • Ad-hoc SQL over raw events. Druid and Pinot shine when queries are known in advance and data is rolled up at ingest. ClickHouse shines when analysts write arbitrary SQL (window functions, subqueries, arrays, joins to small dimensions) over unaggregated data.
  • Cost per TB scanned. Compression of 10x+ plus aggressive data skipping means a modest cluster serves workloads that would cost much more on per-query-billed warehouses.
  • Single-binary simplicity. One server process does storage, query and (with Keeper) coordination. Druid needs five or six process types.
  • Logs and observability. Columnar storage plus token/ngram bloom filters makes it a cheaper Elasticsearch replacement when full relevance-ranked search is not required.

It is weaker when you need hundreds of thousands of concurrent tiny queries (Pinot's star-tree indexes and upsert tables are designed for user-facing analytics at extreme QPS), large multi-way joins between big fact tables (Snowflake, BigQuery and Trino handle those better), or frequent row-level updates (any OLTP database).

Column-Oriented Storage

Row vs. Column Storage

In a row-oriented database like PostgreSQL, data is stored row by row. To compute SELECT AVG(price) FROM events WHERE date = '2026-03-20', the engine reads entire rows — including all columns you do not need — from disk.

In a column-oriented database, each column is stored separately. The same query reads only the price and date columns, ignoring everything else.

Row-oriented (PostgreSQL):
┌──────┬──────────┬───────┬────────┬─────────┐
│ id   │ date     │ price │ user   │ country │
├──────┼──────────┼───────┼────────┼─────────┤
│ 1    │ 2026-03  │ 42.50 │ alice  │ US      │
│ 2    │ 2026-03  │ 89.99 │ bob    │ UK      │
│ ...  │ ...      │ ...   │ ...    │ ...     │
└──────┴──────────┴───────┴────────┴─────────┘
Disk read: ALL columns for matching rows

Column-oriented (ClickHouse):
date:    [2026-03, 2026-03, ...]   ← only these
price:   [42.50, 89.99, ...]       ← two columns
id:      [1, 2, ...]               ← NOT read
user:    [alice, bob, ...]          ← NOT read
country: [US, UK, ...]             ← NOT read

Put numbers on it. An events table with 1 billion rows and 40 columns averaging 8 bytes is ~320 GB uncompressed. A row store answering AVG(price) for one day reads every page holding matching rows — all 40 columns. A column store reads two columns: ~16 GB uncompressed, and after compression (often 5–10x on sorted columns) perhaps 2–3 GB. Add granule pruning on date (below) and the query touches a few hundred MB. That is the entire game: bytes not read are the cheapest bytes. For the B-tree and LSM background this contrasts with, see Storage Engines.

Compression

Column storage enables dramatically better compression because values in the same column have similar data types and distributions. ClickHouse uses column-specific codecs:

CodecBest ForCompression Ratio
LZ4 (default)General purpose, fast decompression4-8x
ZSTDHigher compression, slower decompression8-15x
DeltaMonotonically increasing values (timestamps, IDs)Combined with LZ4: 20-50x
DoubleDeltaSlowly changing integers (counters)Combined with LZ4: 50-100x
T6464-bit integers with limited value range2-10x
GorillaFloat values that change slowly (gauge metrics)Combined with LZ4: 10-30x
CREATE TABLE events (
    timestamp DateTime CODEC(Delta, ZSTD),
    user_id   UInt64   CODEC(T64, LZ4),
    amount    Float64  CODEC(Gorilla, LZ4),
    country   LowCardinality(String)  -- dictionary encoding
) ENGINE = MergeTree()
ORDER BY (timestamp, user_id);

Real-world ClickHouse deployments achieve 10-20x compression over raw data, which translates directly into 10-20x less disk I/O for analytical queries.

Ratios are data-dependent

The ratios in the table above are typical ranges, not guarantees — a Delta codec on a perfectly regular timestamp column can exceed 100x, while on random IDs it may make things worse. Always measure with system.columns (see Compression in Depth below).

Storage Layout in Depth

To reason about ClickHouse performance you need a precise mental model of what is on disk. The hierarchy is:

graph TD
    T["Table: events (MergeTree)"] --> P1["Partition 202610"]
    T --> P2["Partition 202611"]
    P2 --> PA["Part 202611_1_120_3 (merged)"]
    P2 --> PB["Part 202611_121_121_0 (fresh insert)"]
    PA --> C1["ts.bin + ts.mrk2"]
    PA --> C2["user_id.bin + user_id.mrk2"]
    PA --> C3["event_type.bin + event_type.mrk2"]
    PA --> IDX["primary.idx (sparse index)"]
    PA --> META["checksums.txt, columns.txt, count.txt, minmax_ts.idx, partition.dat"]
    C1 --> G["Granules of 8192 rows, addressed via marks"]

Partitions

PARTITION BY splits a table into independent groups of parts. Parts are never merged across partitions, and partitions are the unit for cheap bulk operations: ALTER TABLE ... DROP PARTITION deletes a month of data instantly by removing directories, DETACH/ATTACH/MOVE PARTITION move data between tables or disks, and TTL can act on whole partitions.

CREATE TABLE events (
    ts         DateTime,
    tenant_id  UInt32,
    event_type LowCardinality(String),
    user_id    UInt64,
    duration   UInt32
) ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (tenant_id, event_type, ts);

Partitioning is not primarily a query-speed feature — the primary index and the per-part min/max index do most of the pruning. It is a data-management feature. The most common partitioning mistake is making partitions too fine:

  • Good: toYYYYMM(ts) or toDate(ts) for tables with very high daily volume. Aim for partitions in the tens to hundreds of GB and fewer than ~1,000 partitions per table.
  • Bad: PARTITION BY tenant_id with 50,000 tenants, or toStartOfHour(ts) for years of data. Every insert that spans many partitions creates one part per partition touched, merges cannot consolidate across partitions, and you hit "too many parts" quickly. The default max_partitions_per_insert_block is 100 precisely to stop this.

Parts and Their Names

Every INSERT creates at least one new part per partition it touches: an immutable directory, sorted by the table's sort key. A part name such as 202611_1_120_3 encodes partition_minBlock_maxBlock_level:

  • 202611 — the partition ID
  • 1 and 120 — the range of insert block numbers this part covers
  • 3 — the merge level (0 = straight from an insert; each merge that produces it increases it)

A mutated part gets a fifth component, the mutation version (for example 202611_1_120_3_145). You can watch all of this live:

SELECT partition, name, part_type, rows, marks, level,
       formatReadableSize(bytes_on_disk) AS size
FROM system.parts
WHERE database = 'default' AND table = 'events' AND active
ORDER BY name;
┌─partition─┬─name─────────────────┬─part_type─┬──────rows─┬─marks─┬─level─┬─size───────┐
│ 202611    │ 202611_1_120_3       │ Wide      │ 98304512  │ 12001 │     3 │ 1.21 GiB   │
│ 202611    │ 202611_121_126_1     │ Wide      │  4915200  │   601 │     1 │ 63.80 MiB  │
│ 202611    │ 202611_127_127_0     │ Compact   │    65536  │     9 │     0 │ 1.02 MiB   │
└───────────┴──────────────────────┴───────────┴───────────┴───────┴───────┴────────────┘

Old parts that were merged away stay on disk as inactive for a few minutes (old_parts_lifetime, default 480 seconds) so that a crash mid-merge never loses data, then they are deleted.

Inside a Wide Part

202611_1_120_3/
├── checksums.txt            # checksums of every file, verified on fetch/attach
├── columns.txt              # column names and types in this part
├── count.txt                # row count
├── default_compression_codec.txt
├── partition.dat            # partition key value
├── minmax_ts.idx            # min/max of partition-key columns (part-level pruning)
├── primary.idx              # sparse primary index (primary.cidx when compressed)
├── serialization.json       # per-column serialization (e.g. sparse)
├── ts.bin                   # compressed column data
├── ts.mrk2                  # marks: granule → offsets into ts.bin
├── tenant_id.bin
├── tenant_id.mrk2
├── event_type.bin           # LowCardinality: keys + dictionary streams
├── event_type.mrk2
├── ...
└── skp_idx_idx_trace.idx    # data-skipping index files, if any

.bin files hold the column values, compressed in compressed blocks — each block is a header (codec, compressed size, uncompressed size, checksum) followed by the compressed bytes. Block size is governed by min_compress_block_size (64 KiB) and max_compress_block_size (1 MiB): ClickHouse accumulates at least 64 KiB of uncompressed data before compressing, and never more than 1 MiB.

Granules are the logical unit of reading: by default 8,192 rows (index_granularity). A granule is the smallest set of rows ClickHouse can decide to skip or read. Since version 19.x the default is adaptive granularity: a granule also closes when it reaches index_granularity_bytes (10 MiB), so tables with huge rows (long JSON strings) get smaller granules. That is why the .mrk2 extension exists — the "2" means marks that also record the number of rows in each granule.

Marks (.mrk2) connect the logical granule to the physical bytes. For every granule, the mark stores two numbers per column:

  1. the offset of the compressed block in the .bin file where the granule starts, and
  2. the offset inside that block's decompressed data where the granule's first row begins.

A compressed block can contain several small granules, and a large granule can span several compressed blocks. Marks make both cases addressable: to read granule 731 of user_id, ClickHouse looks up mark 731 in user_id.mrk2, seeks to the block, decompresses it, and skips to the in-block offset. Marks are cached in the mark cache (mark_cache_size, default 5 GiB) so this lookup does not hit disk.

graph LR
    PI["primary.idx entry 731 = (42, 'click', 2026-11-08 10:15)"] --> MK["user_id.mrk2 mark 731 = (block offset 18.2 MB, in-block offset 24576)"]
    MK --> BIN["user_id.bin compressed block at 18.2 MB"]
    BIN --> DEC["decompress, skip 24576 bytes"]
    DEC --> ROWS["8192 user_id values of granule 731"]

The Sparse Primary Index

This is the single most important structure in ClickHouse, and the one most different from B-tree databases.

ClickHouse does not index every row. Instead, it uses a sparse index — one entry per granule (default 8,192 rows). The index stores the first value of the ORDER BY columns for each granule.

Granule 0: rows    0 -  8191  → index entry: (2026-03-01 00:00:00, 1001)
Granule 1: rows 8192 - 16383  → index entry: (2026-03-01 00:05:12, 2847)
Granule 2: rows 16384 - 24575 → index entry: (2026-03-01 00:10:44, 5102)
...

A query on WHERE timestamp > '2026-03-01 00:06:00' uses binary search on the sparse index to skip granules 0, then reads granules 1, 2, ... in parallel. This is why ORDER BY choice is critical — it determines which queries can skip data efficiently.

Why sparse? Consider 10 billion rows. A dense B-tree over (tenant_id, ts) would hold 10 billion entries — hundreds of GB that must be maintained on every insert. A sparse index holds 10,000,000,000 / 8,192 ≈ 1.22 million entries; at ~16 bytes per entry that is ~20 MB, small enough to keep entirely in memory for every part. The trade-off is that the index can only say "the rows you want are somewhere in these granules", so the minimum read unit is a granule (8,192 rows), never a single row. That makes point lookups adequate rather than excellent — and makes range scans over sorted data spectacular.

Note that the primary key in ClickHouse is not unique. Inserting two rows with the same key is perfectly legal; uniqueness is something only the ReplacingMergeTree family approximates, eventually.

Worked Example: How a Query Uses the Primary Key

Take the events table defined above: PARTITION BY toYYYYMM(ts), ORDER BY (tenant_id, event_type, ts), 100 million rows in one month. 100,000,000 / 8,192 = 12,208 granules (the last is partial). Data is physically sorted first by tenant, then event type, then time:

granule   first key in granule (tenant_id, event_type, ts)
  0       (1,  'click',    2026-11-01 00:00:03)
  ...
  4810    (41, 'view',     2026-11-29 22:10:00)
  4811    (42, 'click',    2026-11-01 00:00:11)   ← tenant 42 starts here
  4812    (42, 'click',    2026-11-04 17:31:40)
  ...
  4849    (42, 'purchase', 2026-11-01 02:12:09)
  ...
  4902    (43, 'click',    2026-11-01 00:00:02)   ← tenant 43 starts here

Query: tenant 42's clicks on 8 November.

SELECT count(), avg(duration)
FROM events
WHERE tenant_id = 42
  AND event_type = 'click'
  AND ts >= '2026-11-08' AND ts < '2026-11-09';

Step by step:

  1. Partition pruning. ts bounds map to partition 202611; every other partition's parts are excluded using partition.dat and minmax_ts.idx, without opening any column file.
  2. Primary key analysis. ClickHouse runs a generic exclusion search over primary.idx (binary search when the condition is on a key prefix). Granules whose key range cannot contain (42, 'click', [2026-11-08, 2026-11-09)) are excluded. Because the prefix (tenant_id, event_type) is fully fixed by equality, the third column ts is also usable: inside the tenant-42/click run, rows are sorted by time, so only the granules overlapping 8 November survive.
  3. Skip indexes, if any, prune further (none here).
  4. Read. Only surviving granules are read, and only for the columns the query needs (tenant_id, event_type, ts for the filter, duration for the aggregate). Ranges of adjacent granules are read sequentially by multiple threads.

You can see exactly this with EXPLAIN indexes = 1:

EXPLAIN indexes = 1
SELECT count(), avg(duration) FROM events
WHERE tenant_id = 42 AND event_type = 'click'
  AND ts >= '2026-11-08' AND ts < '2026-11-09';
ReadFromMergeTree (default.events)
Indexes:
  MinMax
    Keys: ts
    Parts: 3/27
    Granules: 12208/36624
  Partition
    Keys: toYYYYMM(ts)
    Parts: 3/27
    Granules: 12208/36624
  PrimaryKey
    Keys: tenant_id, event_type, ts
    Condition: and((ts in [1762560000, 1762646399]),
                   and((event_type in ['click', 'click']), (tenant_id in [42, 42])))
    Parts: 1/3
    Granules: 4/12208

Four granules out of 36,624 — about 32,768 rows read instead of 300 million. In system.query_log this shows up as read_rows = 32768, and the query finishes in single-digit milliseconds.

Now flip the predicate to something the sort key does not help with:

SELECT count() FROM events WHERE user_id = 987654;

user_id is not in the sort key, so PrimaryKey reports Granules: 12208/12208 — a full scan of that column. Still fast in absolute terms (100M UInt64 values ≈ 800 MB uncompressed, often ~0.1 s on a modern server), but it does not scale to tenants with 100 billion rows. Fixes, in order of preference: a projection ordered by user_id, a bloom_filter skip index, or a separate table/materialized view sorted by user_id.

flowchart TD
    Q["Query WHERE predicates"] --> PP{"Partition key matches?"}
    PP -- "no" --> SKIP1["Skip part entirely"]
    PP -- "yes" --> MM{"Part min/max overlaps?"}
    MM -- "no" --> SKIP1
    MM -- "yes" --> PK["Exclusion search on primary.idx"]
    PK --> SI["Skip indexes prune granule ranges"]
    SI --> RD["Read surviving granules via marks, needed columns only"]
    RD --> PW["PREWHERE filters cheap columns first"]
    PW --> EX["Vectorized execution"]

PREWHERE

ClickHouse automatically moves selective filters into PREWHERE (optimize_move_to_prewhere = 1): it first reads only the filter columns for the surviving granules, evaluates the filter, and then reads the remaining (often wide) columns only for the rows that passed. On a logs table where you select a 2 KB message column but filter on a 1-byte level, this is often a 10x or larger I/O saving.

ORDER BY vs PRIMARY KEY

By default PRIMARY KEY equals ORDER BY. You can declare them separately, with one rule: the primary key must be a prefix of the sorting key.

CREATE TABLE daily_stats (
    day        Date,
    tenant_id  UInt32,
    page       String,
    referrer   String,
    views      UInt64
) ENGINE = SummingMergeTree
PRIMARY KEY (tenant_id, day)
ORDER BY (tenant_id, day, page, referrer);

Why do this? In the SummingMergeTree above, the sort key defines what "the same row" means for summing — you want one row per (tenant, day, page, referrer). But you only ever filter by tenant and day, so indexing page and referrer would just bloat primary.idx in memory with long strings. Splitting them keeps the index small while preserving the merge semantics. A second use case: ALTER TABLE ... MODIFY ORDER BY can only append newly added columns to the sort key, which is cheap precisely because the primary key stays the same.

ORDER BY Design Rules

  1. Put the most frequently filtered column first (usually a time column)
  2. Put the next most filtered column second (usually a dimension like user_id or tenant_id)
  3. Do NOT add columns that are only used in aggregations — they add merge overhead without improving data skipping

Refining rule 1

Rule 1 above is the common starting point, but production schemas usually put a low-cardinality, always-filtered column before time — (tenant_id, event_type, ts) rather than (ts, tenant_id). With time first, every row has a distinct leading value, so the index can only prune on time, and filters on tenant_id within a time range barely skip anything. With tenant_id first, both an equality on tenant and a range on time prune well (as the worked example shows). A widely used heuristic: order key columns by ascending cardinality among the columns you filter on, ending with the time column. It also improves compression, because long runs of equal values compress extremely well.

Wide vs Compact Parts

WideCompact
LayoutOne .bin + one .mrk2 per columnAll columns in a single data.bin, marks in data.mrk3
Chosen whenPart ≥ min_bytes_for_wide_part (10 MiB by default) or ≥ min_rows_for_wide_partPart smaller than those thresholds
Good forLarge merged parts, reading few columnsSmall fresh inserts — far fewer files and fsyncs

Small inserts produce compact parts (a 100-column table would otherwise create 200+ files per tiny insert, which is brutal on the filesystem). Once merges grow a part past the threshold, it is written in wide format. You rarely tune this; just know that part_type in system.parts shows it. (Older versions also had experimental in-memory parts; they were removed in 23.x.)

MergeTree Engine Family

The MergeTree is ClickHouse's primary table engine. All production tables use a MergeTree variant. The name reflects its core mechanism: data is written into parts which are periodically merged in the background.

Write Path

graph TD
    INSERT[INSERT batch] --> Part[New Part<br/>sorted by ORDER BY<br/>column files + index]
    Part --> Disk[Disk: parts directory]
    Disk --> Merge[Background Merge<br/>combine small parts<br/>into larger parts]
    Merge --> BigPart[Merged Part<br/>fewer, larger parts]
  1. An INSERT writes a new part — a directory containing one file per column, a primary index, and metadata
  2. Within the part, data is sorted by the ORDER BY columns
  3. Background merges combine parts, maintaining sort order and applying deduplication (for ReplacingMergeTree)

Parts are immutable once written. Updates and deletes are implemented as mutations — a separate asynchronous process that rewrites affected parts.

In more detail, an insert goes through these stages on the server:

  1. Incoming rows are formed into blocks (up to max_insert_block_size, ~1 million rows) and each block is split by partition key.
  2. Each partition's rows are sorted in memory by the sort key.
  3. Each column is encoded, compressed and written to a temporary directory (tmp_insert_...); the primary index, marks, min/max indexes and checksums are written alongside.
  4. The directory is atomically renamed into place and the part becomes visible. A single insert block is therefore atomic per partition: readers see all of it or none of it.

There is no write-ahead log on the default path. Durability comes from the part being fully written (and, for replicated tables, from the part being fetched by other replicas or an insert quorum). fsync_after_insert exists if you need the data on stable storage before acknowledging.

Structurally this is an LSM tree without a memtable: each insert is its own immutable sorted run, and merges play the role of compaction.

Background Merges and Why They Matter

graph LR
    I1["202611_1_1_0"] --> M1["202611_1_3_1"]
    I2["202611_2_2_0"] --> M1
    I3["202611_3_3_0"] --> M1
    I4["202611_4_4_0"] --> M2["202611_4_6_1"]
    I5["202611_5_5_0"] --> M2
    I6["202611_6_6_0"] --> M2
    M1 --> M3["202611_1_6_2"]
    M2 --> M3

Merges are where ClickHouse does almost all of its "real" work besides answering queries:

  • They bound the number of parts. Each query must open every relevant part, look up its index and marks, and run a reading pipeline per part. 10 parts per partition is cheap; 3,000 is not.
  • They restore sort order within a partition. Two parts each sorted by (tenant_id, ts) overlap in key ranges; after merging, one part with one index covers the range and pruning becomes precise.
  • They apply engine semantics. Replacing, Summing, Aggregating and Collapsing logic runs only during merges (and at query time with FINAL).
  • They apply TTL, physically remove lightweight-deleted rows, and build skip indexes for merged data.

A merge is a k-way merge of already-sorted parts: stream each part's columns, pick the next smallest key, write the result as a new part. Two algorithms exist: horizontal (all columns at once, used for small parts) and vertical (sort-key columns first to compute the row permutation, then the remaining columns one at a time — far lower memory for wide tables).

The merge selector picks which adjacent parts to merge, preferring parts of similar size (like size-tiered compaction) to keep write amplification low. Key limits:

SettingDefaultMeaning
background_pool_size16Threads for merges and mutations
background_merges_mutations_concurrency_ratio2Merges allowed per thread, so 32 concurrent by default
max_bytes_to_merge_at_max_space_in_pool150 GiBLargest part a merge will produce
parts_to_delay_insert1000 (150 before 23.6)Inserts are artificially slowed beyond this many active parts in a partition
parts_to_throw_insert3000 (300 before 23.6)Inserts fail with TOO_MANY_PARTS

Merged parts stop growing at ~150 GiB, so a big table settles into a few large parts per partition plus a tail of small recent ones. Write amplification is real: a row may be rewritten 5–10 times over its life as it climbs merge levels. Budget disk bandwidth for it — merges on a table ingesting 1 TB/day can consume more I/O than the queries do.

You can force a merge with OPTIMIZE TABLE events PARTITION 202611 FINAL, which rewrites the partition into one part. It is useful for tests and one-off cleanups of cold partitions, and harmful when run on a schedule against hot data: it rewrites everything, every time.

MergeTree Variants

EngineAdditional Behavior
MergeTreeBase engine — insert-only, no deduplication
ReplacingMergeTreeDeduplicates rows by ORDER BY key during merges. Last version wins.
SummingMergeTreeSums numeric columns for rows with the same ORDER BY key during merges
AggregatingMergeTreeMerges rows using aggregate function states (for pre-aggregated materialized views)
CollapsingMergeTreeUses a sign column (+1/-1) to logically delete rows during merges
VersionedCollapsingMergeTreeCollapsing with version for out-of-order inserts

Every variant also has a Replicated* twin (ReplicatedReplacingMergeTree and so on) — see Replication. In ClickHouse Cloud all of them are transparently backed by SharedMergeTree.

ReplacingMergeTree

-- ReplacingMergeTree: keep only the latest version of each user profile
CREATE TABLE user_profiles (
    user_id   UInt64,
    name      String,
    email     String,
    version   UInt64
) ENGINE = ReplacingMergeTree(version)
ORDER BY user_id;

ReplacingMergeTree Is Not Immediate

Deduplication happens during background merges, not at insert time. Until a merge runs, queries may see duplicate rows. Use FINAL modifier in queries to force deduplication at read time: SELECT * FROM user_profiles FINAL WHERE user_id = 42. But FINAL is slow — it forces a merge of all relevant parts at read time.

Correction (2026-11): an earlier version of this page said FINAL forces a single-threaded merge. That was true of old releases; modern ClickHouse processes FINAL in parallel (max_final_threads), skips the merge step for parts that cannot contain duplicates of each other, and with do_not_merge_across_partitions_select_final = 1 merges each partition independently. FINAL now typically costs a small multiple of a plain read rather than an order of magnitude, but it is still not free.

The precise semantics, which are the source of many production bugs:

  • "Duplicate" means equal sorting key (ORDER BY), not equal primary key and not all columns equal.
  • Which row survives: with a version column, the row with the highest version; on ties, or without a version column, the last inserted row.
  • Dedup is per partition and per shard. Two versions of user_id = 42 in different partitions (for example if you partition by toYYYYMM(updated_at)) are never merged together. Partition by something immutable, or not at all, for Replacing tables. On a sharded cluster, route all versions of a key to the same shard (a sipHash64(user_id) sharding key).
  • Merges may never happen for a given pair of parts — the selector has no obligation to merge everything. Correctness must not depend on background merges.
  • Deletes: since 23.2 you can add an is_deleted column: ReplacingMergeTree(version, is_deleted). A row with is_deleted = 1 and the highest version wins and then disappears from FINAL results.

The three correct ways to read a Replacing table:

-- 1. FINAL: simplest, exact, some read-time cost
SELECT name, email FROM user_profiles FINAL WHERE user_id = 42;

-- 2. argMax: exact, often faster for aggregations over many keys
SELECT user_id, argMax(email, version) AS email
FROM user_profiles
GROUP BY user_id;

-- 3. LIMIT 1 BY: latest row per key
SELECT * FROM user_profiles
ORDER BY user_id, version DESC
LIMIT 1 BY user_id;

SummingMergeTree

When merging, all rows with the same sorting key are collapsed into one, and numeric columns not in the key are summed. If you list columns explicitly — SummingMergeTree((views, revenue)) — only those are summed; other non-key columns take a value from one of the rows.

CREATE TABLE page_views_daily (
    day       Date,
    tenant_id UInt32,
    page      String,
    views     UInt64,
    revenue   Decimal(18, 2)
) ENGINE = SummingMergeTree
ORDER BY (tenant_id, day, page);

INSERT INTO page_views_daily VALUES ('2026-11-08', 1, '/home', 10, 0);
INSERT INTO page_views_daily VALUES ('2026-11-08', 1, '/home', 5, 19.99);

-- WRONG until a merge happens: may return two rows (10 and 5)
SELECT views FROM page_views_daily WHERE page = '/home';

-- RIGHT: always aggregate on read; the merge just makes it cheaper
SELECT page, sum(views), sum(revenue)
FROM page_views_daily
WHERE tenant_id = 1 AND day = '2026-11-08'
GROUP BY page;

Two gotchas: rows whose summed columns all become zero are deleted on merge, and averages cannot be summed — store sum and count and divide at read time, or use AggregatingMergeTree.

AggregatingMergeTree and -State / -Merge Combinators

SummingMergeTree only handles sums. For anything else — distinct counts, quantiles, averages — you store intermediate aggregate states and merge them. ClickHouse exposes this through combinators that work on any aggregate function:

CombinatorWhat it doesExample
-StateReturns the partial state, not the final valueuniqState(user_id) gives AggregateFunction(uniq, UInt64)
-MergeCombines states and finalizesuniqMerge(visitors) gives UInt64
-MergeStateCombines states and returns a state (for rollup chains)uniqMergeState(visitors)
-IfConditional aggregationcountIf(status >= 500), uniqStateIf(user_id, is_new)

A uniq state is an adaptive sketch of at most a few KB; merging two sketches gives the distinct count of the union — something you cannot get from two pre-computed numbers. Likewise quantileState holds a reservoir sample and avgState holds a (sum, count) pair.

CREATE TABLE page_stats_daily (
    day       Date,
    page      LowCardinality(String),
    views     SimpleAggregateFunction(sum, UInt64),
    visitors  AggregateFunction(uniq, UInt64),
    p95_load  AggregateFunction(quantile(0.95), Float32)
) ENGINE = AggregatingMergeTree
ORDER BY (page, day);

CREATE MATERIALIZED VIEW page_stats_daily_mv TO page_stats_daily AS
SELECT
    toDate(ts)                   AS day,
    page,
    count()                      AS views,
    uniqState(user_id)           AS visitors,
    quantileState(0.95)(load_ms) AS p95_load
FROM page_views
GROUP BY day, page;

-- Reading: always -Merge (and plain sum for SimpleAggregateFunction)
SELECT
    page,
    sum(views)                    AS views,
    uniqMerge(visitors)           AS unique_visitors,
    quantileMerge(0.95)(p95_load) AS p95_load_ms
FROM page_stats_daily
WHERE day >= today() - 30
GROUP BY page
ORDER BY views DESC
LIMIT 10;

SimpleAggregateFunction(sum, UInt64) stores a plain value for functions whose state is the result (sum, min, max, any, groupUniqArray), avoiding the overhead of a full state column. Unique visitors over 30 days computed from 30 daily sketches is correct (a union, not the sum of daily uniques) — the canonical reason this engine exists.

CollapsingMergeTree and VersionedCollapsingMergeTree

These engines implement "updates" in an append-only world by cancelling rows. A sign column of +1 means "this state exists", -1 means "cancel the state with the same key". On merge, +1/-1 pairs with the same sorting key annihilate.

CREATE TABLE user_balance (
    user_id UInt64,
    balance Int64,
    sign    Int8
) ENGINE = CollapsingMergeTree(sign)
ORDER BY user_id;

INSERT INTO user_balance VALUES (1, 100, 1);
-- balance changes 100 -> 150: cancel the old state, insert the new one
INSERT INTO user_balance VALUES (1, 100, -1), (1, 150, 1);

-- correct regardless of merge state:
SELECT user_id, sum(balance * sign) AS balance
FROM user_balance
GROUP BY user_id
HAVING sum(sign) > 0;

The catch: the writer must remember the old state in order to emit the cancel row, and CollapsingMergeTree collapses correctly only when the -1 row arrives after the +1 it cancels. With multi-threaded or out-of-order ingestion, use VersionedCollapsingMergeTree(sign, version): it collapses by (key, version) regardless of arrival order.

CREATE TABLE order_state (
    order_id UInt64,
    status   LowCardinality(String),
    amount   Decimal(18, 2),
    sign     Int8,
    version  UInt32
) ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY order_id;

When to choose which: Replacing if you can tolerate FINAL/argMax and the writer does not know prior state (CDC streams give you the full new row, so Replacing is the usual choice). Collapsing when you need sum-style aggregates over the current state to be correct without FINAL, and the producer (for example a stream processor) holds the previous state anyway.

ReplicatedMergeTree (Preview)

ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}') behaves exactly like MergeTree locally but records every insert, merge and mutation in a shared log in ClickHouse Keeper, so all replicas converge on the same set of parts. Details in Replication.

TTL and Data Lifecycle

CREATE TABLE events (
    timestamp DateTime,
    data      String
) ENGINE = MergeTree()
ORDER BY timestamp
TTL timestamp + INTERVAL 90 DAY DELETE,
    timestamp + INTERVAL 30 DAY TO VOLUME 'cold_storage';

TTL automatically moves old data to cheaper storage tiers and eventually deletes it, keeping storage costs predictable.

(The TO VOLUME clause requires the table to use a storage policy that defines a cold_storage volume — SETTINGS storage_policy = '...' — declared in the server's storage configuration.)

TTL comes in several flavors, all applied during merges (plus dedicated TTL merges, governed by merge_with_ttl_timeout, default 4 hours):

CREATE TABLE logs (
    ts       DateTime,
    service  LowCardinality(String),
    level    LowCardinality(String),
    message  String TTL ts + INTERVAL 14 DAY,   -- column TTL: reset the big column early
    trace_id String
) ENGINE = MergeTree
PARTITION BY toDate(ts)
ORDER BY (service, level, ts)
TTL ts + INTERVAL 7 DAY RECOMPRESS CODEC(ZSTD(9)),        -- recompress warm data harder
    ts + INTERVAL 30 DAY TO VOLUME 'cold',                 -- move to S3/HDD volume
    ts + INTERVAL 180 DAY DELETE WHERE level = 'DEBUG',    -- conditional delete
    ts + INTERVAL 365 DAY DELETE                           -- final delete
SETTINGS storage_policy = 'hot_cold';
  • DELETE removes expired rows (optionally with WHERE).
  • TO DISK / TO VOLUME moves whole parts to another tier (local NVMe → HDD → S3).
  • RECOMPRESS rewrites old parts with a heavier codec.
  • GROUP BY rolls expired rows up into aggregates (TTL ts + INTERVAL 30 DAY GROUP BY service, level SET ...), a built-in downsampling.
  • Column TTL resets a single column to its default after expiry — handy for dropping large payloads early while keeping the row.

Two performance tips: align TTL with the partition key and set ttl_only_drop_parts = 1 so expired data is removed by dropping whole parts instead of rewriting them; and remember that row-level TTL deletes are rewrites, so on huge tables they cost as much as a mutation.

Data-Skipping Indexes

The primary index only helps predicates on the sort-key prefix. Data-skipping indexes (secondary indexes) give extra pruning on other columns. They are not B-trees pointing at rows; each one stores a small summary per index block of GRANULARITY N granules, and lets ClickHouse skip blocks whose summary proves no row can match.

TypeSummary stored per blockGood forExample
minmaxMin and max of the expressionColumns correlated with the sort order (e.g. duration that loosely tracks time, an auto-increment ID)TYPE minmax
set(N)Up to N distinct values (empty = too many)Low-cardinality columns clustered in blocksTYPE set(100)
bloom_filter(fpr)Bloom filter of valuesEquality / IN on high-cardinality IDs: trace_id, request_idTYPE bloom_filter(0.01)
tokenbf_v1(bytes, hashes, seed)Bloom filter of tokens (split on non-alphanumerics)hasToken(message, 'timeout'), LIKE '%timeout%' on whole wordsTYPE tokenbf_v1(32768, 3, 0)
ngrambf_v1(n, bytes, hashes, seed)Bloom filter of n-gramsSubstring search: LIKE '%/api/v2/%'TYPE ngrambf_v1(4, 65536, 3, 0)
ALTER TABLE logs ADD INDEX idx_trace  trace_id    TYPE bloom_filter(0.01)          GRANULARITY 4;
ALTER TABLE logs ADD INDEX idx_msg    message     TYPE tokenbf_v1(32768, 3, 0)     GRANULARITY 4;
ALTER TABLE logs ADD INDEX idx_url    url         TYPE ngrambf_v1(4, 65536, 3, 0)  GRANULARITY 4;
ALTER TABLE logs ADD INDEX idx_status status      TYPE set(100)                    GRANULARITY 4;
ALTER TABLE logs ADD INDEX idx_dur    duration_ms TYPE minmax                      GRANULARITY 4;

-- New indexes only apply to newly written parts until you build them for existing data:
ALTER TABLE logs MATERIALIZE INDEX idx_trace;

The rule that decides whether a skip index helps: the value must be clustered. A bloom filter on trace_id works because each trace's spans are written close together in time and the sort key includes time — a given trace ID appears in a handful of blocks. A bloom filter on user_id in a table sorted by time is usually useless: an active user appears in nearly every block, so nothing is skipped and you pay the cost of checking the index for every block. Verify with EXPLAIN indexes = 1 (look for the Skip section and its Granules: x/y) and with send_logs_level = 'trace', which logs "Index idx_trace has dropped N/M granules".

For the theory behind false-positive rates and sizing, see Bloom Filters. Recent releases also add an inverted text index for full-text search (TYPE text); check your version's status before relying on it.

Materialized Views

Materialized views in ClickHouse are not snapshots of a query result (as in PostgreSQL). They are insert triggers — when data is inserted into the source table, the materialized view's query runs on the new data and inserts the result into a destination table.

-- Source table: raw events
CREATE TABLE events (
    timestamp DateTime,
    user_id   UInt64,
    event     String,
    amount    Float64
) ENGINE = MergeTree()
ORDER BY (timestamp, user_id);

-- Materialized view: pre-aggregate daily revenue per user
CREATE MATERIALIZED VIEW daily_revenue
ENGINE = SummingMergeTree()
ORDER BY (date, user_id)
AS SELECT
    toDate(timestamp) AS date,
    user_id,
    sum(amount) AS total_amount,
    count() AS event_count
FROM events
GROUP BY date, user_id;

When you INSERT INTO events, the daily_revenue view automatically aggregates the new batch and inserts the result. Queries on daily_revenue scan a table that is orders of magnitude smaller than events.

Because the destination is a SummingMergeTree that merges eventually, queries against daily_revenue must still aggregate: SELECT date, sum(total_amount) FROM daily_revenue GROUP BY date. The GROUP BY inside the view only aggregates within one insert block; two inserts for the same (date, user_id) produce two rows until a merge.

Chain Materialized Views for Multi-Level Aggregation

Raw events → Per-minute aggregation → Per-hour aggregation → Per-day aggregation

Each level reduces data volume by 60x. Dashboard queries hit the most aggregated level, achieving sub-second response times over months of data.

(The 60x figure assumes one row per key per minute at the bottom level; real reduction depends on how many distinct dimension combinations exist per bucket.)

The Incremental Aggregation Pattern

flowchart LR
    APP["Producers"] --> RAW["events (MergeTree), raw, 90-day TTL"]
    RAW -- "MV trigger per insert block" --> MV1["events_hourly_mv"]
    MV1 --> AGG1["events_hourly (AggregatingMergeTree), 2-year TTL"]
    AGG1 -- "MV on the target table" --> MV2["events_daily_mv"]
    MV2 --> AGG2["events_daily (AggregatingMergeTree), forever"]
    DASH["Dashboards"] --> AGG1
    DASH --> AGG2
    ADHOC["Ad-hoc analysts"] --> RAW

The production form of the pattern uses the TO syntax with an explicit target table — never the implicit .inner table form — because you can then alter, back-fill and drop the view independently of the data:

CREATE TABLE events_hourly (
    hour       DateTime,
    tenant_id  UInt32,
    event_type LowCardinality(String),
    events     SimpleAggregateFunction(sum, UInt64),
    users      AggregateFunction(uniq, UInt64),
    p99_ms     AggregateFunction(quantileTDigest(0.99), UInt32)
) ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (tenant_id, event_type, hour)
TTL hour + INTERVAL 2 YEAR;

CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly AS
SELECT
    toStartOfHour(ts)                  AS hour,
    tenant_id,
    event_type,
    count()                            AS events,
    uniqState(user_id)                 AS users,
    quantileTDigestState(0.99)(duration) AS p99_ms
FROM events
GROUP BY hour, tenant_id, event_type;

Back-filling historical data after creating the view is a plain INSERT ... SELECT into the target, filtered to the time range before the view existed:

INSERT INTO events_hourly
SELECT toStartOfHour(ts), tenant_id, event_type, count(),
       uniqState(user_id), quantileTDigestState(0.99)(duration)
FROM events
WHERE ts < '2026-11-08 12:00:00'
GROUP BY 1, 2, 3;

Semantics to remember:

  • The view sees only the inserted block, not the table. A view that joins to the source table, or uses window functions across blocks, will produce surprising results.
  • The view is triggered by inserts into the leftmost table in its FROM. Inserts into a joined table do not trigger it.
  • Views run synchronously in the insert path. Ten views on one table make every insert do eleven writes; a failing view fails the insert (unless materialized_views_ignore_errors). With parallel_view_processing = 1 views execute concurrently.
  • Mutations, DROP PARTITION and TTL on the source table are not propagated to the target.

Refreshable Materialized Views

Since 24.x, ClickHouse also supports the classic batch semantic: re-run the whole query on a schedule and atomically swap the result. Use it for things that cannot be computed incrementally — top-N over a sliding window, joins between two large tables, denormalized snapshots.

CREATE MATERIALIZED VIEW top_pages_24h
REFRESH EVERY 10 MINUTE
ENGINE = MergeTree ORDER BY views
AS SELECT page, count() AS views, uniq(user_id) AS users
FROM page_views
WHERE ts >= now() - INTERVAL 1 DAY
GROUP BY page
ORDER BY views DESC
LIMIT 1000;

Options include APPEND (insert instead of replace), DEPENDS ON other_view to build DAGs of refreshes, and RANDOMIZE FOR to spread load. Status is visible in system.view_refreshes. Rule of thumb: incremental MVs for append-only aggregations that must be fresh within seconds; refreshable MVs for expensive transformations where minutes of staleness are fine.

Projections

Projections are an alternative to materialized views. They are stored inside the same table as the source data, maintaining a different sort order or pre-aggregation. ClickHouse automatically uses the optimal projection for each query.

ALTER TABLE events ADD PROJECTION events_by_user (
    SELECT * ORDER BY (user_id, timestamp)
);

ALTER TABLE events ADD PROJECTION hourly_counts (
    SELECT toStartOfHour(timestamp) AS hour, event, count()
    GROUP BY hour, event
);

-- build for existing parts (otherwise only new parts get the projection)
ALTER TABLE events MATERIALIZE PROJECTION events_by_user;

Physically, a projection is a hidden sub-part stored inside each part's directory (events_by_user.proj/), written in the same insert and merged in the same merge. That gives projections their key advantages over MVs: they are always consistent with the base data (mutations and partition drops apply to them), and the optimizer picks them transparently — users keep querying events. The costs: every insert writes the data again (a SELECT * projection doubles storage and insert I/O), projections cannot use a different engine or partition key, and historically they did not mix with lightweight deletes (check lightweight_mutation_projection_mode in your version).

ProjectionMaterialized view
StorageInside the source table's partsSeparate target table
Query rewriteAutomaticYou query the target explicitly
Consistency with source deletes/mutationsYesNo
Different engine / TTL / partitioningNoYes
Can join or transform across tablesNoYes (at insert time)
Typical useSecond sort order, simple rollupsMulti-level rollups, Kafka pipelines, reshaping

Compression in Depth

The Compression table earlier gives the overview. Here is how codecs actually compose and how to choose.

General-Purpose Codecs: LZ4 vs ZSTD

Every column ends in a general-purpose compressor. The trade-off is CPU at read time versus bytes on disk:

LZ4ZSTD(1)ZSTD(3–9)
Decompression speed (per core)~3–5 GB/s~1–1.5 GB/s~1 GB/s (decompression barely slows with level)
Compression speedvery fastfastslower — insert and merge cost rise
Ratio vs LZ41xtypically 1.3–1.6x betterslightly better again
Choose whenHot data on NVMe, CPU-bound queriesThe default for most workloads where disk or network (S3) is the bottleneckCold data, via TTL ... RECOMPRESS

ClickHouse Cloud and many large self-hosted deployments default to ZSTD because their data is on object storage or network disks, where fewer bytes read beats faster decompression. On local NVMe with CPU-heavy queries, LZ4 often wins. Measure: system.query_log exposes ProfileEvents['CompressedReadBufferBytes'] and decompression time.

Specialized Codecs (Transform, Then Compress)

Specialized codecs are pre-processing transforms that make data more compressible; they are always chained before a general codec: CODEC(DoubleDelta, ZSTD(1)).

CodecWhat it doesUse forAvoid for
Delta(N)Stores difference from the previous valueMonotonic or slowly drifting values: sorted timestamps, counters, sorted IDsRandom values (deltas are as large as the values)
DoubleDeltaStores delta of deltas, with variable-length bit packingTimestamps at near-constant intervals (metrics scraped every 10 s): delta-of-delta is ~0Irregular event times
GorillaXOR with previous float, encode leading/trailing zeros (from Facebook's Gorilla paper)Float gauges that change slowly: CPU %, temperatureIntegers, noisy floats
FPCPredictive floating-point compressionFloat64 scientific or sensor data; often beats Gorilla on noisy seriesIntegers
T64Transposes a block of 64 integers into bit planes and drops unused high bitsIntegers with a small actual range in a wide type (values < 65,536 stored as UInt64)Already-tight types
GCDDivides by the greatest common divisor of a blockValues that are multiples of a constant (prices in cents stored as multiples of 100, timestamps rounded to seconds in DateTime64(3))Arbitrary values

How to pick, as a decision procedure:

flowchart TD
    S["Column type?"] --> I{"Integer or DateTime?"}
    S --> F{"Float?"}
    S --> STR{"String?"}
    I -- "monotonic, regular interval" --> DD["DoubleDelta + ZSTD"]
    I -- "monotonic, irregular" --> DL["Delta + ZSTD"]
    I -- "small range in wide type" --> T6["T64 + ZSTD"]
    I -- "random IDs" --> Z1["ZSTD only"]
    F -- "slowly changing gauge" --> GO["Gorilla or FPC + ZSTD"]
    F -- "noisy" --> Z2["ZSTD only"]
    STR -- "fewer than ~10k distinct values" --> LC["LowCardinality + ZSTD"]
    STR -- "high cardinality" --> Z3["ZSTD, consider ZSTD(3+)"]

One important interaction: sort order drives compression more than codecs do. A Delta codec on ts is excellent when the table is ordered by ts, and much weaker when ordered by (tenant_id, ts) because the timestamp resets at every tenant boundary (it is still sorted within each tenant run, so it usually remains a good choice). Low-cardinality columns early in the sort key compress to almost nothing because they become long runs.

Measure everything:

SELECT
    name,
    type,
    compression_codec,
    formatReadableSize(data_compressed_bytes)   AS compressed,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed,
    round(data_uncompressed_bytes / data_compressed_bytes, 1) AS ratio
FROM system.columns
WHERE database = 'default' AND table = 'events'
ORDER BY data_compressed_bytes DESC;

LowCardinality

LowCardinality(T) is dictionary encoding as a data type: each part stores a dictionary of distinct values and the column itself holds small integer keys (UInt8/UInt16/...). Benefits go beyond storage — GROUP BY, WHERE event_type = 'click' and joins on the column operate on the integer keys, which is often 2–5x faster than on plain String.

  • Use it for strings with up to roughly 10,000 distinct values (it keeps working beyond that, and can be fine up to ~100k, but the advantage shrinks and dictionaries get big).
  • Do not use it for IDs, URLs or free text — the dictionary just duplicates the column.
  • It also works on numbers and dates (LowCardinality(UInt32)) but there is rarely a reason; fixed-width numbers are already cheap.
  • It is preferable to Enum when the value set can change, because adding a value does not require an ALTER.

The Cost of Nullable

Nullable(T) stores an extra UInt8 null map per value in a separate stream (col.null.bin), so every read of the column reads two streams, every function must check the mask, and several optimizations are disabled. Nullable columns also cannot be part of the sort key unless allow_nullable_key = 1. Measured overhead is typically 10–30% on scans of the column, more for narrow numeric types where the null map is a large fraction of the data.

The idiom: use a sentinel default instead ('', 0, '1970-01-01') and declare Nullable only when "missing" is semantically different from "zero" and you need to tell them apart in queries. DEFAULT expressions and input_format_null_as_default = 1 (on by default) turn incoming NULLs into defaults automatically.

Sparse Serialization

For columns that are mostly default values (a error_code UInt16 that is 0 in 99% of rows), ClickHouse automatically switches to sparse serialization per part when the ratio of defaults exceeds ratio_of_defaults_for_sparse_serialization (0.9375): it stores only the positions and values of non-default rows. This is why serialization.json exists in each part.

Query Execution

Vectorized Query Execution

ClickHouse processes data in blocks (columns of ~65,536 rows) using vectorized operations. Instead of processing one row at a time (like a traditional Volcano-model executor), each operator processes an entire column vector at once.

(Note: the default max_block_size is 65,409 in recent versions — 65,536 minus a small allocator overhead — and blocks read from MergeTree are often smaller, aligned to granules. The order of magnitude is what matters.)

Traditional (row-at-a-time):
for each row:
    if row.date == '2026-03-20':
        sum += row.amount

Vectorized (ClickHouse):
date_column = read_column_block("date", 65536 rows)
mask = SIMD_compare_eq(date_column, '2026-03-20')
amount_column = read_column_block("amount", 65536 rows)
sum = SIMD_masked_sum(amount_column, mask)

Vectorized execution:

  • Eliminates per-row function call overhead — one function call processes thousands of values
  • Enables SIMD instructions — ClickHouse uses SSE4.2 and AVX2/AVX-512 to process 4-16 values per CPU instruction
  • Maximizes CPU cache utilization — processing a column vector keeps data in L1/L2 cache
  • Enables branch-free processing — conditional operations are converted to masks, avoiding CPU branch misprediction penalties

This is why ClickHouse can aggregate 1-2 billion rows per second per core on modern hardware.

Correction (2026-11): that figure is the best case — a trivial aggregate such as sum over a narrow, already-cached numeric column. Realistic queries with filters, decompression and hash-based GROUP BY run at roughly 100–500 million rows per second per core, which on a 32–64 core server is still the "billions of rows per second per server" in the introduction.

Pipeline and Processors

A query is parsed into an AST, analyzed (the newer analyzer, default since 24.3, resolves names and types), turned into a query plan (logical steps: read, filter, aggregate, sort, limit), optimized (filter push-down, PREWHERE, projection selection, reading in order), and finally expanded into a pipeline of processors — a DAG of small operators connected by ports, through which blocks ("chunks") flow.

graph BT
    R1["MergeTreeSelect thread 1"] --> F1["Filter + Expression"]
    R2["MergeTreeSelect thread 2"] --> F2["Filter + Expression"]
    R3["MergeTreeSelect thread N"] --> F3["Filter + Expression"]
    F1 --> A1["AggregatingTransform 1"]
    F2 --> A2["AggregatingTransform 2"]
    F3 --> A3["AggregatingTransform N"]
    A1 --> M["Merge partial aggregates (Resize N to 1)"]
    A2 --> M
    A3 --> M
    M --> S["Sort + Limit"]
    S --> O["Output to client"]

A scheduler with a pool of max_threads (default: number of physical cores) runs whichever processors have input ready. Parallelism comes from splitting the granule ranges of all selected parts across reading streams, so each thread reads, filters and partially aggregates its own slice, and a final step merges partial results. You can see the expansion:

EXPLAIN PIPELINE
SELECT event_type, count() FROM events
WHERE ts >= now() - INTERVAL 1 DAY GROUP BY event_type;
(Expression)
ExpressionTransform
  (Aggregating)
  Resize 16 → 1
    AggregatingTransform × 16
      (Expression)
      ExpressionTransform × 16
        (ReadFromMergeTree)
        MergeTreeSelect(pool: ReadPool, algorithm: Thread) × 16 0 → 1

JIT Compilation

Vectorization removes per-row interpretation overhead, but a complex expression like (a * 2 + b) / c > 10 still makes several passes over memory, materializing an intermediate column for each operator. ClickHouse uses LLVM to JIT-compile such expression trees into a single fused loop (compile_expressions = 1, triggered once an expression has been seen min_count_to_compile_expression = 3 times). The same applies to aggregate functions in GROUP BY (compile_aggregate_expressions) and to multi-column sort comparators (compile_sort_description). Compiled code is cached. Gains are typically 10–50% on expression-heavy queries and negligible on simple scans.

How GROUP BY Works

GROUP BY is ClickHouse's crown jewel, and most of its benchmark leadership comes from here.

  1. Specialized hash tables per key type. The aggregator picks a method based on key types: a direct-addressed array for UInt8/UInt16 keys (no hashing at all), a hash table keyed by UInt64 for single numeric keys, a "keys128/keys256" packed key when several fixed-width keys fit into 16 or 32 bytes, a string-specialized table for single String keys, and a serialized-key fallback for everything else. The hash tables are open-addressing with carefully chosen hash functions and inline aggregate states.
  2. Per-thread partial aggregation. Each thread aggregates its slice of data into its own hash table — no locks.
  3. Two-level hash tables. When a thread's table exceeds group_by_two_level_threshold (100,000 keys) or group_by_two_level_threshold_bytes (50 MB), it converts into a two-level table: 256 sub-tables selected by bits of the hash. The payoff is in the final merge: instead of one thread merging N huge tables, 256 buckets are merged in parallel, bucket i of every thread's table together.
  4. External aggregation. If memory runs out, max_bytes_before_external_group_by spills two-level buckets to disk and merges them bucket by bucket — slower, but it finishes instead of failing with MEMORY_LIMIT_EXCEEDED.
  5. Aggregation in order. If the GROUP BY keys are a prefix of the sort key, optimize_aggregation_in_order can stream through sorted data and emit groups as soon as the key changes, using near-zero memory.
flowchart LR
    T1["Thread 1 hash table"] --> C1{"over 100k keys?"}
    C1 -- "yes" --> L1["256 buckets"]
    T2["Thread 2 hash table"] --> C2{"over 100k keys?"}
    C2 -- "yes" --> L2["256 buckets"]
    L1 --> B0["Merge bucket 0 (thread A)"]
    L2 --> B0
    L1 --> B1["Merge bucket 1 (thread B)"]
    L2 --> B1
    L1 --> B255["Merge bucket 255 (thread X)"]
    L2 --> B255

Practical consequences: high-cardinality GROUP BY (per user_id across billions of rows) is memory-bound — budget roughly key size + state size + ~50% overhead per distinct key. uniq is approximate and tiny; uniqExact keeps every value in a hash set and can use gigabytes. count(DISTINCT x) maps to uniqExact by default (count_distinct_implementation).

JOINs: Algorithms and Why They Are the Weak Spot

ClickHouse began life with almost no join support, and joins remain the area where it is least competitive with MPP warehouses. The reasons are structural: there is no cost-based optimizer with full statistics for join ordering (it is improving — automatic swapping of join sides arrived in 24.12 and join reordering in 25.x), shuffles between nodes are limited, and the classic algorithm builds an in-memory hash table of the right-hand table.

Algorithm (join_algorithm)How it worksMemoryWhen to use
hashBuild a hash table from the right table, stream the left table through itRight side must fit in RAMRight side small (dimension tables, < a few GB)
parallel_hashRight side split into N buckets built by N threads in parallelSame, built fasterDefault in recent versions; medium right sides
grace_hashBoth sides hashed into buckets, buckets spilled to disk and joined one at a timeBoundedRight side too big for RAM, still want hashing
full_sorting_mergeSort both sides by join key (or reuse the sort order of MergeTree tables), then mergeBounded, external sortBoth sides large, especially if already sorted by the key
partial_mergeSort right side, stream left in blocksLowMemory-constrained, slow
directKey-value lookups into a dictionary or EmbeddedRocksDB/Join tableNone at query timeRight side is a dictionary
autoStart with hash, switch to a merge-based join when memory limits are exceededAdaptiveSafe default for unpredictable sizes
-- Put the smaller table on the RIGHT (older versions do not swap automatically)
SELECT u.plan, count() AS events
FROM events AS e
INNER JOIN users AS u ON e.user_id = u.user_id
WHERE e.ts >= today() - 7
GROUP BY u.plan
SETTINGS join_algorithm = 'parallel_hash';

Rules that keep joins fast:

  1. Filter before you join. Push predicates into subqueries on both sides; older versions did not always push them through joins.
  2. Small table on the right. Or rely on automatic side swapping in 24.12+.
  3. Prefer IN to JOIN for pure filtering: WHERE user_id IN (SELECT user_id FROM users WHERE plan = 'enterprise') builds a set, not a full hash table of columns.
  4. Denormalize at insert time (a materialized view or the ingestion pipeline adds dimension attributes to events) when the same join runs thousands of times a day. Analytical storage is cheap; repeated joins are not.
  5. On a cluster, a join against a Distributed table either runs per shard against local data (correct only if both tables are co-sharded on the join key) or needs GLOBAL JOIN, which ships the whole right side to every shard.
  6. Use dictionaries for dimension lookups — next section.

Dictionaries

A dictionary is an in-memory key-value structure loaded from a source (a ClickHouse table, PostgreSQL, MySQL, HTTP, a file) and refreshed periodically. Lookups are function calls (dictGet) evaluated vectorized, inside the scan, with no join planning at all.

CREATE DICTIONARY tenants_dict (
    tenant_id UInt64,
    name      String,
    plan      String,
    region    String
)
PRIMARY KEY tenant_id
SOURCE(POSTGRESQL(host 'pg.internal' port 5432 user 'ch_reader' password 'secret'
                  db 'app' table 'tenants'))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 600);   -- reload every 5-10 minutes, randomized

SELECT dictGet('tenants_dict', 'plan', tenant_id) AS plan, count() AS events
FROM events
WHERE ts >= today()
GROUP BY plan;

Layouts trade memory for lookup speed: FLAT (array indexed by key, fastest, keys must be small integers), HASHED / SPARSE_HASHED, COMPLEX_KEY_HASHED (composite keys), RANGE_HASHED (value valid for a date range — slowly changing dimensions), IP_TRIE (CIDR lookups, used for geo-IP), CACHE / SSD_CACHE (partial, for sources too large to load). A dictionary turns a dimension join into an O(1) lookup per row and is the idiomatic way to enrich events in ClickHouse.

Ingestion

Insert Patterns

ClickHouse performs best with batch inserts of 10,000-1,000,000 rows. Each insert creates a new part on disk. Too many small inserts create too many parts, which overwhelms the merge process.

Bad:  1,000 inserts/second × 1 row each = 1,000 parts/second (will crash)
Good: 1 insert/second × 1,000 rows each = 1 part/second (healthy)
Best: 1 insert/10 seconds × 100,000 rows each = efficient

Use a buffer table or an external buffer (Kafka, a message queue) to batch inserts before writing to ClickHouse.

(More precisely, "will crash" means: merges fall behind, inserts are first delayed at parts_to_delay_insert and then rejected with TOO_MANY_PARTS at parts_to_throw_insert. The server stays up; your writes fail.)

Why Many Small Inserts Hurt

Every insert, no matter how small, pays fixed costs: forming and sorting a block, creating a part directory with files per column (or a compact part), fsync, registering the part, and — for replicated tables — several round trips to Keeper and a fetch by every other replica. A 1-row insert into a 50-column replicated table can easily cost as much as a 100,000-row insert. Then the merge pool has to merge all those tiny parts. The official guidance is at most about one insert per second per table, with at least 1,000 rows and ideally 10,000–100,000+ per insert.

flowchart LR
    A["1000 tiny inserts per second"] --> B["1000 new parts per second"]
    B --> C["Merge pool saturated"]
    C --> D["Active parts climb past 1000"]
    D --> E["Inserts delayed"]
    E --> F["Past 3000 parts: TOO_MANY_PARTS error"]

The four ways to get batching, roughly in order of preference:

  1. Client-side batching — buffer in the producer (or a collector like Vector, Fluent Bit, OpenTelemetry Collector) and flush every N rows or T seconds.
  2. Asynchronous inserts — let the server batch for you.
  3. A queue (Kafka) with a consumer that writes big batches — the Kafka engine or ClickPipes/Kafka Connect.
  4. Buffer tables — legacy, mostly superseded by async inserts.

Asynchronous Inserts

With async_insert = 1, the server accepts small inserts into an in-memory buffer per (table, settings, query shape) and flushes the buffer as one part when it reaches async_insert_max_data_size (10 MiB by default in recent versions) or async_insert_busy_timeout_ms elapses (an adaptive timeout up to ~200 ms in recent releases), or after async_insert_max_query_number queries.

INSERT INTO events
SETTINGS async_insert = 1, wait_for_async_insert = 1
FORMAT JSONEachRow
{"ts":"2026-11-08 10:00:00","tenant_id":42,"event_type":"click","user_id":7,"duration":120}
sequenceDiagram
    participant C1 as Client 1
    participant C2 as Client 2
    participant S as ClickHouse server
    participant D as Disk
    C1->>S: INSERT 5 rows async
    C2->>S: INSERT 3 rows async
    Note over S: rows wait in buffer until size or timeout
    S->>D: flush 8 rows as ONE part
    S-->>C1: OK after flush
    S-->>C2: OK after flush

wait_for_async_insert = 1 (the default) acknowledges only after the flush is written — safe, at the cost of up to one timeout of latency. wait_for_async_insert = 0 acknowledges immediately; it is faster but you can lose acknowledged data on a crash and you will not see errors. Use = 1 unless the data is truly disposable.

Buffer Tables

CREATE TABLE events_buffer AS events
ENGINE = Buffer(default, events, 16,      -- target db, table, num_layers
                10, 100,                  -- min_time, max_time (seconds)
                10000, 1000000,           -- min_rows, max_rows
                10000000, 100000000);     -- min_bytes, max_bytes

Clients insert into events_buffer; it flushes to events when all minimums or any maximum is reached. Data in the buffer is in RAM only (lost on crash), it is not replicated, and ALTERs on the target are awkward. Prefer async inserts for new systems.

The Kafka Engine

For streaming ingestion, the Kafka table engine acts as a consumer: a Kafka table is a cursor over a topic, and a materialized view moves rows from it into a MergeTree table in batches.

CREATE TABLE events_queue (
    ts         DateTime64(3),
    tenant_id  UInt32,
    event_type String,
    user_id    UInt64,
    duration   UInt32
) ENGINE = Kafka
SETTINGS kafka_broker_list  = 'kafka-1:9092,kafka-2:9092',
         kafka_topic_list   = 'events',
         kafka_group_name   = 'clickhouse-events',
         kafka_format       = 'JSONEachRow',
         kafka_num_consumers = 4,
         kafka_max_block_size = 1048576;

CREATE MATERIALIZED VIEW events_queue_mv TO events AS
SELECT toDateTime(ts) AS ts, tenant_id, event_type, user_id, duration
FROM events_queue;
flowchart LR
    K["Kafka topic, 12 partitions"] --> KT["events_queue (Kafka engine)"]
    KT -- "poll batch" --> MV["events_queue_mv"]
    MV --> T["events (ReplicatedMergeTree)"]
    T -- "commit offsets after write" --> K

Notes from production: kafka_num_consumers should not exceed the topic's partition count (extra consumers sit idle) or the core count; offsets are committed after the block is written, which gives at-least-once delivery — a crash between write and commit re-delivers the batch, so pair it with a Replacing engine or insert deduplication if duplicates matter. Malformed messages can be routed aside with kafka_handle_error_mode = 'stream'. For the Kafka side of the story see Kafka Internals, and the ClickHouse vs Druid vs Pinot page for how Druid's and Pinot's native consumers differ.

Idempotent Inserts and Deduplication

Networks fail mid-insert. If a client gets a timeout, it cannot know whether the part was written, so it retries — and you get duplicates. ClickHouse solves this for replicated tables by storing a hash of every inserted block in Keeper for the last replicated_deduplication_window blocks (1,000 in recent versions, 100 in older ones); a retried block with the same hash is silently dropped.

  • The retry must send exactly the same rows in the same order with the same settings, so the block hash matches. Most official clients do this when retrying.
  • For explicit control, set a token: INSERT INTO events SETTINGS insert_deduplication_token = 'batch-2026-11-08-000042' VALUES .... Same token, same result — even if the batch is split into several blocks.
  • Non-replicated MergeTree needs non_replicated_deduplication_window set (default 0, i.e. off).
  • Async inserts deduplicate only with async_insert_deduplicate = 1.
  • Materialized views dedup their own target blocks with deduplicate_blocks_in_dependent_materialized_views = 1; without it a retried insert can be dropped at the source but duplicated in the MV target (or vice versa).

This is block-level idempotency, not row-level uniqueness. For row-level "exactly once" you still want a natural key and ReplacingMergeTree. See Idempotent Pipelines and Exactly-Once Semantics for the general patterns.

Distributed ClickHouse

Sharding Architecture

graph TB
    Client[Client Query] --> Dist[Distributed Table<br/>events_distributed]
    Dist --> S1[Shard 1<br/>Node A + Node B replica]
    Dist --> S2[Shard 2<br/>Node C + Node D replica]
    Dist --> S3[Shard 3<br/>Node E + Node F replica]

    S1 --> Local1[Local MergeTree<br/>events_local]
    S2 --> Local2[Local MergeTree<br/>events_local]
    S3 --> Local3[Local MergeTree<br/>events_local]

ClickHouse uses a two-table pattern for distributed queries:

-- Local table on each shard
CREATE TABLE events_local ON CLUSTER my_cluster (
    timestamp DateTime,
    user_id   UInt64,
    event     String
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
ORDER BY (timestamp, user_id);

-- Distributed table (virtual — routes queries to shards)
CREATE TABLE events_distributed ON CLUSTER my_cluster AS events_local
ENGINE = Distributed(my_cluster, default, events_local, rand());

The Distributed engine:

  • Writes: routes each insert batch to a shard based on the sharding expression (rand(), sipHash64(user_id), etc.)
  • Reads: sends the query to all shards in parallel, gathers partial results, and merges them on the coordinator node

A distributed GROUP BY is a two-phase aggregation: each shard runs the query up to the intermediate state of aggregation (the same -State idea as AggregatingMergeTree) and sends states, not final values, to the initiator, which merges them. This is why uniq and quantile are correct across shards. When the sharding key is a prefix of the GROUP BY keys, optimize_distributed_group_by_sharding_key = 1 lets each shard finalize locally because no group spans shards.

sequenceDiagram
    participant C as Client
    participant I as Initiator node
    participant S1 as Shard 1 replica
    participant S2 as Shard 2 replica
    participant S3 as Shard 3 replica
    C->>I: SELECT tenant_id, uniq(user_id) FROM events_distributed GROUP BY tenant_id
    I->>S1: rewritten query on events_local
    I->>S2: rewritten query on events_local
    I->>S3: rewritten query on events_local
    S1-->>I: partial aggregate states
    S2-->>I: partial aggregate states
    S3-->>I: partial aggregate states
    Note over I: merge states, finalize, ORDER BY, LIMIT
    I-->>C: result

Choosing the sharding key:

  • rand() gives perfect balance but scatters every key, so per-user dedup (Replacing) and co-located joins are impossible.
  • sipHash64(user_id) or cityHash64(tenant_id) keeps an entity on one shard — required for ReplacingMergeTree correctness and local joins — but a single huge tenant becomes a hot shard.
  • Inserting into the Distributed table is convenient but by default asynchronous (data is queued on the initiator's disk and forwarded in the background, distributed_foreground_insert = 0). At high volume, it is better for the ingestion layer to compute the shard and insert directly into events_local on the right shard.
  • Resharding is manual (create a new cluster layout, copy data). Over-provision shards early, or use ClickHouse Cloud, which avoids sharding entirely. See Sharding for the general trade-offs.

Replication

ClickHouse uses ZooKeeper (or ClickHouse Keeper, a built-in Raft-based alternative) for replication coordination. ReplicatedMergeTree ensures all replicas eventually converge to the same set of parts through a replication log stored in ZooKeeper.

Replication is asynchronous and part-level — entire parts are replicated, not individual rows. This makes replication bandwidth-efficient (compressed parts are transferred) but means replicas may lag behind the primary by one or more insert batches.

Clarification (2026-11): there is no single primary. ClickHouse replication is multi-master — every replica accepts inserts and queries. "Lag behind the primary" should read "lag behind the replica that received the insert". Merges are coordinated: one replica is chosen to perform a given merge and records it in the log, and other replicas either perform the same merge locally (deterministic, so the result is byte-identical) or fetch the merged part, whichever is cheaper.

sequenceDiagram
    participant C as Client
    participant R1 as Replica 1
    participant K as ClickHouse Keeper
    participant R2 as Replica 2
    C->>R1: INSERT block
    R1->>R1: write part 202611_7_7_0
    R1->>K: check dedup hash, add log entry GET_PART
    K-->>R1: committed
    R1-->>C: OK
    K-->>R2: log entry visible in queue
    R2->>R1: fetch part over HTTP port 9009
    R1-->>R2: compressed part files
    R2->>K: mark part present

Consistency knobs:

  • insert_quorum = 2 acknowledges an insert only after 2 replicas have it; select_sequential_consistency = 1 then lets reads see only quorum-written data.
  • max_replica_delay_for_distributed_queries (300 s) makes Distributed queries avoid stale replicas.
  • system.replicas shows absolute_delay and queue_size; system.replication_queue shows each pending action and its last error.

For background on replication models see Replication.

ClickHouse Keeper vs ZooKeeper

Replicated tables, ON CLUSTER DDL and some features (insert dedup hashes, replicated databases) need a strongly consistent coordination service. Historically that was Apache ZooKeeper. ClickHouse Keeper is a drop-in replacement written in C++ on the NuRaft implementation of Raft; it speaks the ZooKeeper client protocol, so the server does not care which one it talks to.

ZooKeeperClickHouse Keeper
ImplementationJava, ZAB protocolC++, Raft (NuRaft)
GC pausesYes — a classic cause of session expiry and read-only replicasNone
Memory and disk useHigher; snapshots and logs uncompressedLower; compressed logs and snapshots
Large znode countsStruggles beyond tens of millionsHandles more, with faster recovery
DeploymentSeparate JVM clusterStandalone binary or embedded in clickhouse-server
Extras—Multi-read requests, optional linearizable reads, system.zookeeper works unchanged

Run 3 Keeper nodes (tolerates 1 failure) or 5 (tolerates 2), on separate hosts with fast disks for the Raft log, ideally not embedded in heavily loaded ClickHouse servers. Keeper is a metadata store; keep it small by avoiding very high part churn (another reason for big inserts).

Parallel Replicas

Classic ClickHouse uses one replica per shard per query; the other replicas only add failover and concurrency. Parallel replicas let a single query use all replicas of a shard: the initiator splits the granule ranges of the selected parts into tasks and replicas pull tasks dynamically (so a slow replica just takes fewer).

SET enable_parallel_replicas = 1,
    max_parallel_replicas = 3,
    cluster_for_parallel_replicas = 'my_cluster';

This turns "add replicas" into a way to speed up big scans, not just to add concurrency, and it is the main way ClickHouse Cloud scales a single heavy query horizontally. Caveats: older versions named the setting allow_experimental_parallel_reading_from_replicas, FINAL and some join shapes have limited support, and small queries get slower from coordination overhead (parallel_replicas_min_number_of_rows_per_replica guards this).

ClickHouse Cloud and SharedMergeTree

ClickHouse Cloud separates storage from compute. SharedMergeTree is a MergeTree variant whose parts live in object storage (S3, GCS, Azure Blob) and whose metadata (which parts exist) lives in Keeper:

graph TB
    Q["Queries and inserts"] --> C1["Compute replica 1"]
    Q --> C2["Compute replica 2"]
    Q --> C3["Compute replica N"]
    C1 --> KP["Keeper: part metadata"]
    C2 --> KP
    C3 --> KP
    C1 --> OS["Object storage: all parts, single copy"]
    C2 --> OS
    C3 --> OS
    C1 -.-> CACHE1["Local SSD cache"]
    C2 -.-> CACHE2["Local SSD cache"]

Differences from self-managed ReplicatedMergeTree:

  • No data replication between replicas — durability comes from object storage, so adding a replica copies no data and takes seconds.
  • No sharding — every replica sees all data; parallel replicas distribute a big query across them.
  • Replicas do not fetch each other's parts; they read part lists from Keeper and data from S3 (with a local filesystem cache), so insert throughput scales with the number of replicas.
  • Compute can scale to zero when idle.

The trade-off is latency of cold reads from object storage, mitigated by caches and prefetching. Open-source users can approximate the architecture with MergeTree on S3 disks plus zero-copy replication, but that path has been fragile and is not recommended for new deployments.

Mutations, Deletes and Updates

Mutations (ALTER UPDATE / DELETE)

ALTER TABLE events DELETE WHERE user_id = 123;
ALTER TABLE events UPDATE country = 'DE' WHERE user_id = 123;

-- watch progress
SELECT mutation_id, command, parts_to_do, is_done, latest_fail_reason
FROM system.mutations
WHERE table = 'events' AND NOT is_done;

-- cancel a runaway mutation
KILL MUTATION WHERE mutation_id = 'mutation_145.txt';

A mutation is an asynchronous background job that rewrites every part containing at least one matching row (for UPDATE, only the affected columns are rewritten and the rest are hard-linked; for DELETE, the whole part). Updating one row in a 150 GB part rewrites that part. Mutations are ordered: each part is processed at a given mutation version, and new inserts after the mutation was submitted are not affected. mutations_sync = 1 (or 2 for all replicas) makes the ALTER wait.

That is why updates are expensive: immutability of parts is what makes reads and compression fast, and a mutation pays for it by rewriting. A table with a steady stream of point updates via mutations will spend all its I/O rewriting itself and fall behind.

flowchart LR
    M["ALTER TABLE ... UPDATE WHERE user_id = 123"] --> P1["Part A contains user 123"]
    M --> P2["Part B no match, skipped"]
    M --> P3["Part C contains user 123"]
    P1 --> R1["Rewrite affected columns of part A"]
    P3 --> R3["Rewrite affected columns of part C"]
    R1 --> SW["Swap in new parts, old parts become inactive"]
    R3 --> SW

Lightweight Deletes

Since 23.3 (GA), standard SQL DELETE is a lightweight delete:

DELETE FROM events WHERE user_id = 123;

Instead of rewriting parts, it writes a hidden _row_exists mask column for affected parts (cheap — one column), and all subsequent queries filter out masked rows automatically. Rows are physically removed at the next merge of those parts. Lightweight deletes are typically orders of magnitude faster to execute than ALTER DELETE, but every query on affected parts pays a small filtering cost until merges clean up, and huge deletes still trigger rewrites of the mask. For deleting large time ranges, DROP PARTITION remains the best tool.

Lightweight Updates

Recent releases (25.7+) add a standard UPDATE ... SET ... WHERE statement implemented with patch parts: small parts that contain only the updated columns for the affected rows, applied on read and materialized during merges. This makes occasional row updates practical; it does not make ClickHouse an OLTP database. Check the status of the feature in your version before depending on it.

ClickHouse Is Not a Primary Database

ClickHouse does not support UPDATE or DELETE in the traditional sense. Mutations are asynchronous background operations that rewrite entire parts. If you need to delete a user's data for GDPR compliance, the delete may not take effect for minutes or hours. Always pair ClickHouse with a transactional primary database.

(Update: with lightweight DELETE, deleted rows disappear from query results immediately; physical removal still waits for merges, which matters for compliance — schedule OPTIMIZE ... FINAL or rely on min_age_to_force_merge_seconds for partitions that must be purged on disk within a deadline.)

Upsert Patterns

PatternHowRead costBest for
ReplacingMergeTree + versionInsert the full new row with a higher versionFINAL or argMaxCDC from PostgreSQL/MySQL (Debezium), entity snapshots
ReplacingMergeTree + is_deletedInsert a tombstone rowSameCDC including deletes
CollapsingMergeTreeInsert cancel row + new rowsum(x * sign)Stream processors that hold prior state
AggregatingMergeTree with argMaxStateStore argMaxState(value, ts) per keyargMaxMergeLatest value per key inside rollups
Lightweight UPDATEUPDATE t SET ... WHERE id = ...Patch parts on readOccasional corrections
ALTER UPDATEMutationNone after completionRare bulk backfills

The CDC pipeline is the canonical one: CDC from the OLTP database emits row images with an LSN or commit timestamp; that becomes the version column of a ReplacingMergeTree(version, is_deleted) table ordered by the source primary key.

Operational Best Practices

Key Metrics

MetricHealthy RangeConcern
Parts per partition< 300> 300: "too many parts" error
Background merges< 50 concurrent> 50: merge backlog
Query concurrency< 100ClickHouse is designed for few, heavy queries
Replica lag< 60 seconds> 60s: replication falling behind
Memory usage< 80%OOM kills are the top ClickHouse failure mode

Correction (2026-11): the 300-parts threshold was the default parts_to_throw_insert before version 23.6; it is now 3,000 (with insert throttling starting at 1,000). Staying under ~300 active parts per partition is still a sensible health target. The merge pool is bounded by background_pool_size × background_merges_mutations_concurrency_ratio (32 by default), so "merge backlog" shows up as system.merges being consistently full plus a growing part count, not as a count over 50. max_concurrent_queries defaults to 100 (higher in newer releases); short queries can comfortably run at hundreds of QPS per server.

System Tables for Observability

ClickHouse documents itself through system.* tables. The ones you will use weekly:

TableWhat it tells you
system.partsEvery part: rows, bytes, level, active flag, partition — part-count health
system.columnsPer-column compressed and uncompressed size — codec decisions
system.mergesMerges and mutations running right now, progress, memory
system.mutationsMutation queue, stuck mutations and failure reasons
system.query_logEvery query: duration, rows/bytes read, memory, exception, ProfileEvents
system.part_logPart lifecycle events: NewPart, MergeParts, MutatePart, RemovePart — insert rate per table
system.replicas / system.replication_queueReplication delay, read-only replicas, stuck fetches
system.processesCurrently running queries (kill with KILL QUERY)
system.metrics, system.events, system.asynchronous_metricsGauges, counters and periodically computed values (memory, CPU, disk)
system.errorsError codes with counts since start
system.text_log, system.trace_logServer log as a table; sampling profiler stack traces

Queries worth saving:

-- 1. Slowest queries today, grouped by normalized shape
SELECT
    normalized_query_hash,
    any(substring(query, 1, 100))          AS sample,
    count()                                AS runs,
    round(avg(query_duration_ms))          AS avg_ms,
    max(query_duration_ms)                 AS max_ms,
    formatReadableQuantity(sum(read_rows)) AS rows_read,
    formatReadableSize(max(memory_usage))  AS peak_mem
FROM system.query_log
WHERE type = 'QueryFinish' AND event_date = today()
GROUP BY normalized_query_hash
ORDER BY sum(query_duration_ms) DESC
LIMIT 10;

-- 2. Partitions with too many parts
SELECT database, table, partition_id, count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table, partition_id
HAVING parts > 100
ORDER BY parts DESC;

-- 3. What merges are doing right now
SELECT database, table, round(elapsed) AS secs, round(progress * 100, 1) AS pct,
       num_parts, formatReadableSize(total_size_bytes_compressed) AS size,
       is_mutation, formatReadableSize(memory_usage) AS mem
FROM system.merges;

-- 4. Inserts per minute per table (are clients batching?)
SELECT toStartOfMinute(event_time) AS minute, table, count() AS new_parts,
       round(avg(rows)) AS avg_rows_per_part
FROM system.part_log
WHERE event_type = 'NewPart' AND event_time > now() - INTERVAL 1 HOUR
GROUP BY minute, table
ORDER BY minute DESC, new_parts DESC;

-- 5. Unhealthy replicas
SELECT database, table, is_readonly, absolute_delay, queue_size,
       inserts_in_queue, merges_in_queue
FROM system.replicas
WHERE is_readonly OR absolute_delay > 30 OR queue_size > 50;

-- 6. Failed queries in the last hour
SELECT event_time, exception_code, substring(exception, 1, 120) AS err,
       substring(query, 1, 80) AS q
FROM system.query_log
WHERE type IN ('ExceptionBeforeStart', 'ExceptionWhileProcessing')
  AND event_time > now() - INTERVAL 1 HOUR
ORDER BY event_time DESC
LIMIT 20;

Query 4 is the single best early-warning signal: if avg_rows_per_part is in the tens, some client is not batching and you will meet TOO_MANY_PARTS soon.

Common Production Problems and Fixes

SymptomUsual causeFix
TOO_MANY_PARTS (code 252)Small, frequent inserts; inserts touching many partitions; partition key too granularBatch on client or async_insert = 1; coarser PARTITION BY; check system.part_log for the offending table
MEMORY_LIMIT_EXCEEDED (code 241)High-cardinality GROUP BY, big right side in a hash join, uniqExact, huge ORDER BY without LIMITmax_bytes_before_external_group_by / _sort; grace_hash or full_sorting_merge; uniq instead of uniqExact; dictionaries instead of joins
Queries read the whole tableFilter not on sort-key prefixEXPLAIN indexes = 1; reorder ORDER BY; projection or skip index
Replica read-onlyLost Keeper session (GC pause in ZooKeeper, network), Keeper disk fullFix Keeper (move to ClickHouse Keeper, fast disk); SYSTEM RESTART REPLICA
Mutations never finishMutation on huge table, failing expression, lack of disk space for rewritesystem.mutations.latest_fail_reason; KILL MUTATION; use lightweight delete or DROP PARTITION
Duplicates in Replacing tableReading without FINAL; versions in different partitions or shardsFINAL/argMax; immutable partition key; shard by entity key
MV target missing rows after retriesDedup applied at source but not dependent viewsdeduplicate_blocks_in_dependent_materialized_views = 1; dedup tokens
Disk fills unexpectedlyMerges need free space roughly equal to the parts being merged; detached or inactive parts; system.*_log tables without TTLKeep 20–30% free; add TTL on system log tables; clean detached/
Slow ON CLUSTER DDLDistributed DDL queue blocked by an unreachable hostsystem.distributed_ddl_queue; fix or remove the dead host

Hardware Sizing Rules of Thumb

These are starting points from ClickHouse's own guidance and common practice — benchmark before committing.

  • CPU: more cores beat higher clocks for scans; 16–64 cores per server is typical. ClickHouse uses every core for a single query. x86 with AVX2 or ARM (Graviton) both work well.
  • Memory: minimum 32 GB for anything real, 64–256 GB typical. Memory-to-storage ratio around 1:50–1:100 for interactive analytics on hot data; for archival or log workloads with rare queries, 1:100–1:130 or more is fine. Memory feeds the page cache, mark cache and uncompressed cache as well as GROUP BY and joins.
  • Storage: NVMe SSD for hot data; HDD or object storage for cold data via TTL moves. Plan disk throughput for merges (sustained write roughly 2–3x your compressed ingest rate). Keep 20–30% free space for merges and mutations.
  • Network: 10 Gbit/s minimum between replicas; 25 Gbit/s+ when using S3 disks or parallel replicas.
  • Throughput expectations (per server, well-batched): inserts of a few hundred thousand to a few million rows per second; simple aggregations over a few billion rows per second; compression 5–20x depending on schema.
  • Topology: start with one shard, two replicas and grow vertically first. A single modern server handles tens of TB compressed. Shard only when one machine's disk, ingest or scan speed is no longer enough.

Schema Design Walkthrough: An Events Table

Scenario: a multi-tenant product-analytics SaaS. ~2 billion events/day, 90 days retained hot, queries are almost always "for tenant X, over time range T, broken down by event type / page / country". Occasionally support looks up a single user's events.

Version 1: the naive schema

CREATE TABLE events_v1 (
    event_id   String,                -- UUID as text
    ts         DateTime64(3),
    tenant_id  String,                -- numeric, stored as text
    user_id    Nullable(String),
    event_type String,
    page       String,
    country    Nullable(String),
    device     String,
    duration   Nullable(Float64),
    properties String                 -- JSON blob
) ENGINE = MergeTree
PARTITION BY toYYYYMMDD(ts)
ORDER BY event_id;                     -- "it's the primary key, right?"

Problems, one by one: ORDER BY event_id sorts by a random UUID, so no filter on tenant or time can skip anything and nothing compresses (neighbouring rows share nothing); toYYYYMMDD partitions are fine at this volume but the sort order makes per-partition pruning the only pruning; String IDs are 36 bytes where 16 or 4 would do; Nullable everywhere adds null maps; low-cardinality strings (event_type, country, device) are stored in full; Float64 for a millisecond duration wastes bytes.

Version 2: the designed schema

CREATE TABLE events_v2 (
    tenant_id  UInt32,
    event_type LowCardinality(String),
    ts         DateTime64(3)           CODEC(Delta, ZSTD(1)),
    user_id    UInt64                  CODEC(ZSTD(1)),
    event_id   UUID                    CODEC(ZSTD(1)),
    page       LowCardinality(String)  CODEC(ZSTD(1)),
    country    LowCardinality(String),
    device     LowCardinality(String),
    duration   UInt32                  CODEC(T64, ZSTD(1)),   -- ms, 0 = unknown
    properties String                  CODEC(ZSTD(3)),
    INDEX idx_user user_id TYPE bloom_filter(0.01) GRANULARITY 4
) ENGINE = MergeTree
PARTITION BY toDate(ts)
ORDER BY (tenant_id, event_type, toStartOfHour(ts), user_id, ts)
TTL toDate(ts) + INTERVAL 90 DAY
SETTINGS ttl_only_drop_parts = 1;

ALTER TABLE events_v2 ADD PROJECTION by_user (
    SELECT * ORDER BY (tenant_id, user_id, ts)
);

The reasoning for each decision:

  1. ORDER BY starts with tenant_id — every query filters on it, and it has moderate cardinality (thousands). Then event_type (tens of values, frequently filtered). Then toStartOfHour(ts) — a coarse time bucket so that time-range filters prune within a tenant/type run, while user_id after it groups a user's events within each hour (better compression of user-level columns and faster per-user funnels). Finally ts for full ordering.
  2. PARTITION BY toDate(ts) — ~2 billion rows/day is big enough for daily partitions (hundreds of GB uncompressed per day); with ttl_only_drop_parts retention becomes dropping one directory per day.
  3. Types: UInt32 tenant, UUID (16 bytes) instead of 36-byte strings, UInt32 milliseconds, no Nullable.
  4. LowCardinality on every string dimension with a bounded set of values. page is borderline (thousands to tens of thousands of paths per tenant) — measure; if the dictionary per part grows beyond ~100k entries, revert to String with ZSTD.
  5. Codecs: Delta on the sorted timestamp, T64 on small-range integers, heavier ZSTD on the JSON blob that is rarely read.
  6. Per-user lookups get a projection sorted by (tenant_id, user_id, ts) — support queries become indexed reads — and a cheap bloom filter as a fallback for parts not yet rebuilt.

Before and after

Representative numbers for one day (~2 billion rows) on a single 32-core NVMe server; your exact figures will differ, but the ratios are typical of this kind of redesign:

Metricv1v2Change
Compressed size per day~310 GB~48 GB (+ ~40 GB projection)6.5x smaller (3.5x incl. projection)
count() by event type for one tenant, last 7 days4.8 s, 14 B rows read0.06 s, 41 M rows read~80x faster
Hourly time series for one tenant and event type, 30 days9.1 s0.09 s~100x faster
Single user's events for one tenant, last 30 days22 s (full scan)0.04 s (projection)~500x faster
Top countries across all tenants, 1 day (full scan in both)3.2 s0.7 s~4.5x faster (fewer bytes, LowCardinality GROUP BY)

The cross-tenant query shows the limit: when nothing in the sort key matches, the only gains are from fewer bytes and cheaper types. That is the job of rollups — add an events_hourly AggregatingMergeTree (see The Incremental Aggregation Pattern) for dashboards that span all tenants.

Validate your own version with:

SELECT query_duration_ms, read_rows, formatReadableSize(read_bytes) AS read
FROM system.query_log
WHERE type = 'QueryFinish' AND query LIKE '%events_v2%'
ORDER BY event_time DESC
LIMIT 5;

When to Use ClickHouse

WorkloadClickHousePostgreSQLElasticsearchTime-Series DBs
Analytics (GROUP BY, COUNT, SUM)ExcellentSlow at scaleLimitedGood for metrics
Point lookups by primary keyAdequateExcellentAdequateAdequate
Full-text searchBasicpg_trgm, tsvectorExcellentNot designed for
Time-series metricsExcellentTimescaleDB extensionAdequatePurpose-built
ACID transactionsNot supportedExcellentNot supportedVaries
Real-time inserts (< 1ms latency)Batch inserts onlyExcellentNear-real-timeVaries
JoinsLimited (small dimensions)ExcellentNot supportedLimited

Notes (2026-11): "ACID transactions — not supported" means no multi-statement transactions in production; each insert block is atomic and isolated per partition, and experimental transaction support exists. "Joins — limited" is less true than it used to be: grace hash, full sorting merge and automatic side swapping make large joins workable, but they are still the area where ClickHouse trails MPP warehouses.

When ClickHouse Is the Right Choice

  • Analytical queries over billions of rows (event analytics, product analytics, log analysis)
  • Aggregations at sub-second latency over terabytes of data
  • Time-series analysis with complex GROUP BY and window functions
  • Real-time dashboards fed by streaming inserts

When ClickHouse Is the Wrong Choice

  • OLTP workloads (frequent single-row updates, deletes, point lookups)
  • Applications requiring ACID transactions
  • Small datasets (< 1 million rows) — PostgreSQL is simpler and sufficient
  • Primary data store — ClickHouse should be a secondary analytics store fed by CDC from your primary database

Comparison With Other Analytical Engines

ClickHouseApache DruidApache PinotBigQuerySnowflakeDuckDB
DeploymentSelf-hosted or ClickHouse CloudSelf-hosted or ImplySelf-hosted or StarTreeServerless (GCP)Managed SaaSIn-process library
ArchitectureShared-nothing MPP; shared storage in CloudSeparate ingest, historical, broker, coordinator processes; deep storageSimilar to Druid (controller, broker, server, minion)Separated storage (Colossus) and Dremel computeSeparated storage (S3) and virtual warehousesSingle process, vectorized
IngestionBatch inserts, async inserts, Kafka engineNative Kafka/Kinesis supervisors, batchNative real-time consumers, upsertsBatch loads, streaming APIBatch COPY, SnowpipeReads files and in-memory data
FreshnessSecondsSub-second to secondsSub-second to secondsSeconds to minutes~1 minute (Snowpipe)Immediate (local)
Query latency sweet spot10 ms – seconds10–500 ms on pre-indexed dims10–100 ms at very high QPSSecondsSecondsms – seconds on one machine
Ad-hoc SQL breadthVery broad, with dialect quirksNarrowerNarrower (improving with multi-stage engine)Full ANSIFull ANSIFull, PostgreSQL-like
Large joinsWeak to moderateWeakWeak to moderateStrongStrongStrong (single node)
UpdatesMutations, lightweight delete, ReplacingReindex segmentsNative upsert tablesDML (costly)DMLFull DML
Pricing modelHardware or Cloud compute + storageHardwareHardwarePer TB scanned or slotsPer second of warehouseFree
Best atRaw-event analytics, logs, cost efficiencyTime-sliced dashboards with rollupsUser-facing analytics at high QPSHuge ad-hoc warehouse queries without opsEnterprise warehousing, mixed workloadsLocal and embedded analytics, notebooks

For a deeper three-way comparison including benchmarks and operations see ClickHouse vs Druid vs Pinot; for lakehouse engines (Trino, Spark) see Query Engines; and for the conceptual split between transactional and analytical systems see OLTP vs OLAP and the Database Selection Guide.

Interview Questions

1. Why does ClickHouse use a sparse primary index instead of a B-tree?

A B-tree indexes every row: billions of entries, too large for memory, and expensive to maintain on high-volume inserts. Because MergeTree parts are sorted and immutable, one entry per granule (8,192 rows) is enough to binary-search to the right ranges. The index for 10 billion rows is ~1.2 million entries — megabytes, fully in memory. The trade-off is that the smallest read is a granule, so point lookups read thousands of rows; analytical scans, which read millions, do not notice.

2. Explain granules, marks and how a query finds its data.

A granule is a block of ~8,192 rows (adaptive by bytes) — the unit of skipping. primary.idx stores the sort key of the first row of each granule. Each column has a marks file with, per granule, the offset of the compressed block in the .bin file and the offset inside the decompressed block. A query evaluates its WHERE clause against partition and min/max indexes, then the primary index and skip indexes, producing granule ranges; it then uses marks to seek into only the needed columns' .bin files and decompress only those blocks.

3. How would you choose ORDER BY for a multi-tenant events table?

Start with the columns that are almost always filtered with equality, ordered by ascending cardinality (tenant_id, then event_type), then a time column (possibly a coarse bucket followed by finer columns). Avoid putting a high-cardinality random value (UUID) first. Verify with EXPLAIN indexes = 1 that typical queries read a small fraction of granules. Secondary access paths get projections or skip indexes rather than a compromised main order.

4. What is the "too many parts" error and how do you fix it?

Every insert creates at least one part per partition touched; merges consolidate them. If inserts are small and frequent, or each insert spans many partitions, part creation outpaces merging. Past parts_to_delay_insert inserts are slowed and past parts_to_throw_insert they fail. Fix the producers (batch to ≥ 1,000 rows, ideally 10k–100k, about once per second), enable async inserts, coarsen the partition key, and check system.part_log to find the offender.

5. ReplacingMergeTree returned duplicates. Why, and what do you do?

Dedup happens only at merge time, only within a partition, only within a shard, and merges are not guaranteed to happen. Reads must use FINAL, argMax(col, version) with GROUP BY key, or LIMIT 1 BY key. Structurally, make the partition key immutable for a given entity and shard by the entity key.

6. How do materialized views differ from PostgreSQL's, and what are the pitfalls?

They are insert triggers: the view's SELECT runs on each inserted block and writes to a target table; they never re-read the source. Pitfalls: aggregation is per block so the target must use Summing/AggregatingMergeTree and reads must re-aggregate; only inserts into the leftmost FROM table trigger; mutations and partition drops on the source are not propagated; failing views fail inserts; back-fills are manual INSERT ... SELECT. Refreshable MVs provide the PostgreSQL-style periodic recompute.

7. Why are JOINs a weak spot, and how do you work around it?

The default hash join builds the right side in memory, there is limited cost-based join ordering, and distributed joins either require co-sharding or broadcast the right side (GLOBAL JOIN). Workarounds: filter first, keep the small table on the right, use IN for filtering, dictionaries for dimension lookups, denormalize at insert time, and choose grace_hash or full_sorting_merge for large-large joins.

8. How does GROUP BY scale to hundreds of millions of keys?

Specialized hash tables per key type, per-thread partial aggregation without locks, conversion to two-level (256-bucket) hash tables beyond 100k keys so the final merge runs in parallel by bucket, spilling to disk with external aggregation when memory is exceeded, and in-order aggregation when grouping by a sort-key prefix.

9. Describe how replication works and the role of Keeper.

ReplicatedMergeTree replicas are multi-master. A replica that receives an insert writes the part, records a log entry (and dedup hash) in Keeper; other replicas read the log and fetch the part over HTTP. Merges are assigned via the log and either re-executed deterministically or fetched. Keeper (Raft, ZooKeeper protocol) stores only metadata. Replication is asynchronous by default; insert_quorum makes it synchronous for a number of replicas.

10. A GDPR request requires deleting a user's data. How do you do it in ClickHouse?

Use lightweight DELETE FROM t WHERE user_id = ..., which hides rows immediately and removes them physically at the next merge; then force physical removal within the compliance deadline (OPTIMIZE ... FINAL on affected partitions, or min_age_to_force_merge_seconds). Remember MV targets and projections: deletes are not propagated to MV targets, so delete there too. For very large deletes by time range, use DROP PARTITION. Long-term, keep PII in a separate table keyed by user so it can be dropped without rewriting events.

11. When would you pick Pinot or Druid over ClickHouse?

When you serve user-facing analytics at tens of thousands of QPS with strict p99 latency on known query shapes (Pinot's star-tree index, native upserts), or when ingestion-time rollup and a Kafka-native, managed segment lifecycle matter more than ad-hoc SQL (Druid). ClickHouse wins on ad-hoc SQL breadth, raw-event storage, operational simplicity and cost per TB.

12. What does SharedMergeTree change compared with ReplicatedMergeTree?

Data lives once in object storage, metadata in Keeper; replicas are stateless compute that do not copy data to each other, so scaling replicas is fast and sharding is unnecessary. Parallel replicas spread single queries across compute. The cost is object-storage latency, mitigated by local caches.

Further Reading