Concept Explainer

ClickHouse Concept Explainer

Use this skill when a user asks "what is X", "explain Y", or "how does Z work". Give the right mental model first, add a concrete example, then offer to go deeper. Tailor detail to the user's apparent experience level.

MergeTree Family & How Merges Work

Every insert creates one or more immutable parts on disk (a directory of column files + index). ClickHouse merges these parts in the background — combining small parts into larger ones, re-sorting data, applying deduplication or aggregation depending on the engine variant. Until a merge happens, the same logical row may exist across multiple parts.

Why small inserts are bad: each INSERT of 10 rows creates a tiny part. Parts accumulate faster than the background merger can keep up, hitting the too many parts error (default threshold: 300 parts per partition). Always batch at least 1 000–10 000 rows per insert; use async inserts for high-frequency small writes.

-- Check live part counts per table
SELECT table, count() AS parts, sum(rows) AS total_rows
FROM system.parts
WHERE active AND database = currentDatabase()
GROUP BY table ORDER BY parts DESC;

Cross-reference: clickhouse-best-practices for insert batching settings.

Sparse Primary Index / Primary Key

ClickHouse does not build a per-row B-tree index. Instead it stores one index entry per granule (default 8 192 rows, set by index_granularity). Each entry records the primary key value at the start of that granule.

At query time ClickHouse binary-searches these sparse marks, selects the minimal set of granules that could match, and reads only those blocks from disk. The primary key is not unique — duplicates are allowed and common. Its sole purpose is to sort data on disk so range scans skip irrelevant granules.

-- Effective only when filtering on leading primary-key columns:
SELECT count() FROM events WHERE user_id = 42 AND event_date >= today() - 7;
-- user_id must be the first or second ORDER BY column to benefit from the index.

Cross-reference: query-optimization for EXPLAIN INDEXES and skip indexes.

ORDER BY vs PARTITION BY vs PRIMARY KEY

These three clauses look similar but serve different purposes:

Partitioning rule of thumb: partition on a low-cardinality column (e.g., toYYYYMM(date)). Each partition is merged independently; over-partitioning (millions of partitions) slows queries and maintenance. Most tables need no explicit PARTITION BY or just a monthly/daily date partition.

Cross-reference: schema-design-advisor for partition sizing guidance.

Columnar Storage & Compression

ClickHouse stores each column in its own file. A query reading only 3 out of 100 columns touches ~3% of the data. Within a column file, values are stored consecutively, so they compress extremely well — identical or slowly-changing values in sorted data often achieve 5–20× compression with LZ4 (default) or ZSTD.

Per-column codecs let you tune further:

CREATE TABLE metrics (
    ts    DateTime CODEC(DoubleDelta, ZSTD),  -- timestamps: delta + entropy coding
    value Float64  CODEC(Gorilla, ZSTD)       -- float series: XOR delta
) ENGINE = MergeTree ORDER BY ts;

This is why ClickHouse wins on analytical queries: it reads far less data off disk than row-oriented databases, and what it does read is compact.

Replication

ReplicatedMergeTree keeps two or more replicas in sync through ClickHouse Keeper (or ZooKeeper). The coordination protocol works like this:

1. One replica receives an insert, writes a part locally, and logs the operation to Keeper.
2. Other replicas see the log entry and fetch the part (either from the leader or from each other).
3. Each replica applies merges independently but uses Keeper to agree on which parts to merge so they converge to identical state.

Replication is eventually consistent: a fresh replica may not yet have a just-inserted row. Use SYSTEM SYNC REPLICA to wait for a replica to catch up, or enable insert_quorum to make inserts wait for N replicas before acknowledging.

Cross-reference: replication-guide for quorum settings and Keeper health checks.