ClickHouse Cheatsheet

How ClickHouse stores data in MergeTree parts, what ORDER BY and PARTITION BY really do, how replication works with Keeper, and how to distribute and shard tables. Each entry gives the problem, how it works, SQL examples, real-world notes and trade-offs.

Updated: 2026-10-07

5 entries

Quick tables

Reference and comparison tables for quick scanning.

MergeTree family at a glance

MergeTree family at a glance
EngineWhat a merge doesUse for
MergeTreeCombines sorted parts, nothing elseAppend-only events, logs, metrics
ReplacingMergeTreeKeeps one row per sorting key (latest version); duplicates remain until a mergeUpserts and CDC where eventual dedup is fine (use FINAL or argMax to read exact)
SummingMergeTreeSums numeric columns of rows with the same keySimple rollup counters
AggregatingMergeTreeMerges aggregate-function statesMaterialized-view rollups (uniq, quantiles)
CollapsingMergeTreeCancels +1/-1 row pairsMutable state modelled as sign rows
Replicated*Same engines, replicated through KeeperAnything that must survive a node loss

Sharding key options

Sharding key options
Key expressionDistributionGood forWatch out
rand()Even, randomLogs and events with no join/aggregation localityEvery query hits every shard; no colocation
cityHash64(user_id)Even if the key isPer-user or per-tenant queries and joinsHot tenants skew a shard; a bad key skews everything
user_id (plain)Only as even as the idsDense integer idsSequential or skewed ids give uneven shards
toYYYYMM(ts)By timeRarely: tiered or time-split clustersToday's shard takes all the writes

Storage

Inserts create immutable sorted parts that background merges combine; a sparse index over granules makes range scans fast.

The problem

Analytical queries scan billions of rows, and writes arrive continuously. Updating sorted files in place is slow. MergeTree makes writes cheap by never modifying data: it appends sorted immutable parts and merges them in the background.

How it works

  • Each INSERT writes a new part: a directory of column files sorted by the table's ORDER BY key. Parts are immutable.
  • Background merges combine small parts into bigger ones, keeping them sorted, so reads see a few large parts instead of thousands of small ones. Parts from different partitions are never merged together.
  • Storage is columnar: each column compresses well and only the columns a query touches are read. Parts are Wide (one file per column) or Compact (all columns in one file) depending on size.
  • The primary key is a sparse index: one entry per granule of 8192 rows (`index_granularity`), small enough to stay in RAM. A query uses it to skip granules, not to find single rows.
  • Insert in large batches (thousands of rows or more, or use `async_insert`): many tiny inserts create many parts faster than merges can combine them and end in a “too many parts” error.
  • Engine variants change what a merge does: Replacing (keep latest row per key), Summing, Aggregating, Collapsing. Their results are only final after merging, so reads that need exactness use FINAL or an aggregate.
  • UPDATE and DELETE are mutations that rewrite parts in the background — heavy, not for per-row traffic. Prefer append plus ReplacingMergeTree, or lightweight deletes for occasional removals.

Examples

An events table: ORDER BY puts the columns you filter on first.

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

Watch parts and merges; many active parts means inserts are too small.

sql
SELECT table, count() AS active_parts, sum(rows) AS rows
FROM system.parts
WHERE active AND database = currentDatabase()
GROUP BY table
ORDER BY active_parts DESC;

In practice

  • Observability stacks — SigNoz, Uptrace and similar store logs, traces and metrics in MergeTree tables partitioned by day and ordered by service and time.
  • Web analytics / product analytics — Event tables ordered by tenant and time, with AggregatingMergeTree materialized views for rollups.
  • CDC pipelines — ReplacingMergeTree with a version column keeps the latest row per key, read with FINAL or argMax.
  • Pitfall — Thousands of single-row inserts per second: batch them (or use async inserts) before they become a parts backlog.

Trade-offs

Pros

  • Very fast ingestion (sequential writes) and scans (columnar, sparse index, compression)
  • Simple recovery model: parts are immutable files
  • Flexible aggregation and dedup via engine variants

Cons

  • Not for point updates or transactional workloads; mutations are heavy
  • Dedup and rollups are eventual until merges run
  • Needs batched inserts and a sensible ORDER BY, or performance collapses

ORDER BY decides sort order and the sparse primary index; PARTITION BY is for data lifecycle, not query speed.

The problem

ClickHouse has three similar-sounding settings — ORDER BY, PRIMARY KEY and PARTITION BY — and mixing up their jobs is the most common cause of slow queries and operational pain.

How it works

  • ORDER BY defines the physical sort order inside every part and, unless PRIMARY KEY is given, the primary key. It is the main performance lever.
  • The sparse primary index lets a query skip granules when its WHERE clause constrains a prefix of the key: equality or range conditions on the first column help most; a filter on a later column works well only if earlier columns are also constrained or have low cardinality.
  • Rule of thumb: put low-cardinality, frequently filtered columns first, then higher-cardinality ones, then time.
  • PRIMARY KEY may be a prefix of ORDER BY (smaller in-memory index) — mostly used by Summing/Aggregating engines.
  • PARTITION BY splits data into independent sets of parts. It exists for data management — DROP PARTITION, TTL and moving data to cheaper storage are cheap metadata operations — not for query speed.
  • Keep partitions coarse (monthly is the usual recommendation, daily for large volumes): too many partitions means too many parts and slow startup and merges. Never partition by a high-cardinality column.
  • TTL expires rows or moves them between volumes automatically; combined with partitions, whole-partition drops are the cheapest way to enforce retention.

Examples

Retention with partitions and TTL; dropping a partition is instant.

sql
CREATE TABLE logs
(
    service LowCardinality(String),
    ts      DateTime,
    level   LowCardinality(String),
    message String
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(ts)
ORDER BY (service, level, ts)
TTL ts + INTERVAL 30 DAY DELETE;

ALTER TABLE logs DROP PARTITION 20260901;

Check how many granules a query really reads.

sql
EXPLAIN indexes = 1
SELECT count()
FROM logs
WHERE service = 'checkout' AND ts >= now() - INTERVAL 1 HOUR;

In practice

  • Logs and traces — ORDER BY (service, level, timestamp) with daily partitions and a TTL matching retention.
  • Multi-tenant analytics — Lead with tenant_id so each tenant's data is contiguous and queries skip other tenants' granules.
  • EXPLAIN indexes = 1 — Shows selected parts and granules; a ratio near 1.0 means the key does not match your filters.
  • Pitfall — Partitioning by user id or by hour for a small table creates tens of thousands of parts and slows everything.

Trade-offs

Pros

  • A good ORDER BY skips most of the data on selective filters
  • Partitions make retention and tiering cheap, metadata-only operations
  • Sparse index stays small and in memory

Cons

  • The sort order is fixed per table: queries that filter on other columns scan more (use projections or materialized views)
  • Choosing partitions badly hurts merges and startup
  • Changing ORDER BY later means rewriting the table

Replication

Asynchronous multi-master replication: Keeper coordinates a replication log, replicas fetch parts from each other.

The problem

A single node loses data when its disk or machine fails and stops serving when it is down. Replication keeps copies on other nodes — and ClickHouse does it asynchronously and multi-master, so any replica can take inserts and all converge.

How it works

  • Use a Replicated* engine (ReplicatedMergeTree, ReplicatedReplacingMergeTree, ...). Each replica registers under a table path in Keeper with its own replica name.
  • Replication is asynchronous and multi-master: an INSERT can be sent to any replica. The replica writes a part locally, then records it in a replication log in Keeper; the other replicas read the log and fetch the part directly from that replica. Keeper holds metadata, not table data.
  • By default an INSERT is acknowledged after one replica has it. `insert_quorum` requires more replicas to confirm, trading latency and availability for durability; reads may still see lag unless you also use quorum reads (`select_sequential_consistency`).
  • Inserted blocks are deduplicated: re-sending the same block (same data) after a timeout is safe and written once, within a bounded deduplication window.
  • Merges are scheduled and replicated too: one replica merges, the others fetch or repeat the merge, so replicas end with the same parts.
  • ClickHouse Keeper is a drop-in ZooKeeper replacement built on Raft. Run three Keeper nodes (odd count, quorum), on dedicated hosts with fast disks; losing Keeper quorum makes replicated tables read-only.
  • Start with replication for fault tolerance before adding shards; ON CLUSTER DDL creates the table on every replica at once; use macros such as `{shard}` and `{replica}` to avoid hand-editing paths.

Examples

A replicated table created on every node of a cluster; macros fill in shard and replica.

sql
CREATE TABLE events ON CLUSTER main
(
    tenant_id UInt32,
    ts        DateTime,
    event     LowCardinality(String)
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/{database}/{table}',
    '{replica}'
)
PARTITION BY toYYYYMM(ts)
ORDER BY (tenant_id, ts);

Look for replication lag and queue backlog.

sql
SELECT database, table, is_readonly, absolute_delay, queue_size
FROM system.replicas
ORDER BY absolute_delay DESC;

In practice

  • ClickHouse Keeper — Raft-based coordination (ZooKeeper compatible protocol); recommended dedicated 3-node ensemble.
  • Altinity clickhouse-operator — Declares shards and replicas and generates macros, cluster config and Keeper wiring on Kubernetes.
  • system.replicas — Per-table view of read-only state, queue size and lag — the first thing to alert on.
  • Pitfall — Writing the same table through different replicas with different ORDER BY or settings: all replicas of a table must have identical definitions.

Trade-offs

Pros

  • Any replica accepts writes; no failover step for ingestion
  • Data moves replica-to-replica, so Keeper stays small
  • Insert deduplication makes retries safe

Cons

  • Asynchronous: an acknowledged insert can be lost if that replica dies before others fetch it (unless insert_quorum)
  • Replicas can lag, so reads may be stale
  • Keeper quorum becomes a critical dependency

Distribution

A Distributed table stores nothing: it fans queries out to shards, merges results and routes inserts by a sharding key.

The problem

When data or compute outgrows one node, you shard it. Applications should not need to know which shard holds which rows. The Distributed engine presents a cluster of shards as one table.

How it works

  • A Distributed table holds no data. It is defined over a cluster (from server config), a database and a local table that exists on every shard.
  • SELECT: the initiator node sends the query to one replica of every shard, each shard runs it on its local table, and the initiator merges the partial results (aggregations are pushed down so only partial states travel).
  • INSERT: each row is routed to a shard by the sharding key expression; without a key you can insert only when there is a single shard (or with `insert_distributed_one_random_shard`).
  • Inserts through a Distributed table are asynchronous by default: data is written to a local queue on the initiator and forwarded in the background. `insert_distributed_sync = 1` waits for the shards. For throughput, many writers insert directly into the local tables.
  • With `internal_replication = true` in the cluster config, a write goes to the first healthy replica of the shard and replication spreads it; with false the Distributed table writes to every replica itself (and can diverge).
  • JOINs: joining two Distributed tables sends the right side to every shard; use GLOBAL JOIN to broadcast it once, or colocate both tables on the same sharding key so each shard joins locally.
  • ON CLUSTER executes DDL on every node, so schemas stay in step.

Examples

The local table lives on every shard; the Distributed table fans out and routes by a hash of tenant_id.

sql
CREATE TABLE events_all ON CLUSTER main AS events
ENGINE = Distributed(main, currentDatabase(), events, cityHash64(tenant_id));

SELECT tenant_id, count() AS n
FROM events_all
WHERE ts >= now() - INTERVAL 1 DAY
GROUP BY tenant_id
ORDER BY n DESC
LIMIT 10;

Broadcast the small side of a join once instead of re-sending it per shard.

sql
SELECT e.event, t.plan, count() AS n
FROM events_all AS e
GLOBAL INNER JOIN tenants AS t ON t.id = e.tenant_id
GROUP BY e.event, t.plan;

In practice

  • Large analytics clusters — Shards for capacity and parallelism, replicas inside each shard for availability; apps query only the Distributed table.
  • clickhouse-operator — Generates the cluster definition (shards, replicas, internal_replication) the Distributed engine reads.
  • chproxy / load balancers — Spread queries across initiator nodes so no single node merges everything.
  • Pitfall — Writing everything through one initiator creates a bottleneck and a backlog in its async queue; check `system.distribution_queue`.

Trade-offs

Pros

  • Transparent horizontal scale for storage and query CPU
  • Partial aggregation pushdown keeps network traffic small
  • One table name for applications

Cons

  • A query is as slow as the slowest shard, and fails if a shard has no healthy replica
  • Async inserts through the Distributed table can queue up and lose data if the initiator dies
  • Cross-shard joins and high-cardinality GROUP BY are expensive

Pick a sharding key (rand, hash of tenant/user, ...) for balance versus colocation; resharding is manual, so replicate first.

The problem

Which shard a row lands on decides whether load is balanced, whether related rows sit together, and how painful growth is. The sharding key is hard to change later, so it deserves a deliberate choice — and most clusters do not need sharding at all yet.

How it works

  • Routing: the sharding expression must be an integer; ClickHouse takes its remainder modulo the sum of the shard weights and sends the row to the shard whose weight range contains it (weights 9 and 10: remainders 0–8 go to shard 1, 9–18 to shard 2).
  • rand(): even spread and no hot shards, but no locality — every query touches every shard, and joins or distinct counts cannot be done locally.
  • A hash of a business key (cityHash64(user_id) or tenant_id): all rows of one tenant live on one shard, so per-tenant queries, joins and aggregations stay local (and `optimize_distributed_group_by_sharding_key` can finish them on the shards).
  • Skew: a few huge tenants overload one shard; check actual row and CPU distribution before trusting a key. Use a hash so sequential ids do not cluster.
  • Changing the number of shards or weights changes the remainder for most rows: ClickHouse does not rebalance automatically and plain modulo is not consistent hashing. Resharding means copying data to a new cluster (for example INSERT … SELECT FROM remote(...)) or planning shard counts ahead.
  • The documentation calls the simple remainder a limited solution that suits medium clusters (dozens of servers); very large deployments use custom schemes.
  • Order of growth: tune the schema and ORDER BY → add replicas for availability and read capacity → scale the node vertically → shard when one node's storage or query compute is truly the limit.

Go sketch

Go
// shardFor mirrors how a Distributed table routes a row: take the
// sharding expression's value modulo the total weight, then find the
// shard whose weight range contains the remainder.
func shardFor(value uint64, weights []uint64) int {
    var total uint64
    for _, w := range weights {
        total += w
    }
    rem := value % total
    var upper uint64
    for i, w := range weights {
        upper += w
        if rem < upper {
            return i
        }
    }
    return len(weights) - 1
}

// shardFor(rem 0..8, {9, 10}) -> shard 0; rem 9..18 -> shard 1.
// Change the weights or add a shard and most rows change shard:
// this is a plain modulo, not consistent hashing.

In practice

  • Log / event pipelines — rand() or a hash of a high-cardinality id: even load matters more than locality.
  • Multi-tenant SaaS analytics — Hash of tenant_id so a tenant's queries stay on one shard; isolate or split a giant tenant separately.
  • Weights — Give a bigger machine a bigger weight in the cluster config so it takes proportionally more rows.
  • Cloud-managed ClickHouse — Separates storage from compute on shared object storage, which removes most manual sharding and resharding work.

Trade-offs

Pros

  • rand(): perfect balance, simplest writes
  • Business-key hash: local joins and aggregations, fewer cross-shard queries
  • Weights allow heterogeneous hardware

Cons

  • Modulo routing makes resharding a data migration
  • Business-key hash risks skew from large tenants; rand() gives up locality
  • More shards mean more failure points: every shard needs replicas