16. Cheat sheet

Everything worth keeping from the previous fifteen chapters, in a form you can scan at 3 a.m.

The numbers

Thing	Value
Granule size (`index_granularity`)	8,192 rows
`index_granularity_bytes`	10 MB
`max_block_size` (query)	65,505 values
`max_insert_block_size`	1,048,576 rows
Insert batch target	10,000 – 1,000,000 rows, every 1–10 s
`parts_to_delay_insert`	150 per partition
`parts_to_throw_insert`	300 per partition
Partitions per table, target	tens to low hundreds
`max_partitions_per_insert_block`	100
Max merge size	~150 GB
`old_parts_lifetime`	8 minutes
`max_memory_usage`	10 GiB per query
`max_server_memory_usage`	0.9 × RAM
`mark_cache_size`	5 GB
`LowCardinality` threshold	under ~10,000 distinct values
Keeper nodes	3 or 5
Typical compression	10:1

The DDL template

CREATE TABLE events
(
    ts        DateTime              CODEC(DoubleDelta, ZSTD(1)),
    tenant_id UInt32,
    service   LowCardinality(String),
    level     LowCardinality(String),
    user_id   UInt64                CODEC(T64, ZSTD(1)),
    duration  UInt32                CODEC(T64, LZ4),
    message   String                CODEC(ZSTD(3)),
    raw       String                CODEC(ZSTD(3)),
    trace_id  String    MATERIALIZED JSONExtractString(raw, 'trace_id')
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)                 -- instant DROP PARTITION retention
ORDER BY (tenant_id, service, ts)         -- E→S→R: filters low→high card., time last
TTL ts + INTERVAL  30 DAY TO VOLUME 'warm',
    ts + INTERVAL 365 DAY DELETE
SETTINGS ttl_only_drop_parts = 1;

Choosing an engine

   append-only events                   MergeTree
   latest version per key / CDC sink    ReplacingMergeTree(version [, is_deleted])
   counters                             SummingMergeTree
   dashboards and rollups               AggregatingMergeTree  ← with a MV
   net out with +1 / -1                 VersionedCollapsingMergeTree
   replicated (any of the above)        Replicated<Engine>(keeper_path, replica)
   routing across shards                Distributed(cluster, db, table, shard_key)

Reading each engine without `FINAL`

-- ReplacingMergeTree
SELECT id, argMax(name, version), argMax(email, version) FROM t GROUP BY id;

-- SummingMergeTree
SELECT k, sum(v) FROM t GROUP BY k;

-- AggregatingMergeTree
SELECT k, uniqMerge(users), avgMerge(dur) FROM t GROUP BY k;

-- VersionedCollapsingMergeTree
SELECT k, sum(v * sign) FROM t GROUP BY k HAVING sum(sign) > 0;

FINAL is a design smell. Use these instead.

Types — the choices that cost money

✅ UInt8/16/32 sized to the data      ❌ UInt64 for everything
✅ DateTime (4 bytes)                 ❌ DateTime64 by default
✅ LowCardinality(String) < 10k       ❌ LowCardinality on a UUID
✅ UInt32 DEFAULT 0                   ❌ Nullable(UInt32) reflexively
✅ Decimal for money                  ❌ Float for money
✅ Enum for closed sets               ❌ Enum for sets that grow
✅ typed MATERIALIZED cols from JSON  ❌ JSONExtract in every query

Codecs

   DateTime, monotonic ids      CODEC(DoubleDelta, ZSTD(1))
   counters, metrics            CODEC(Delta, ZSTD(1))
   slowly-changing floats       CODEC(Gorilla, ZSTD(1))
   narrow-range integers        CODEC(T64, ZSTD(1))
   large text                   CODEC(ZSTD(3))
   UUIDs, hashes, blobs         CODEC(NONE)  or ZSTD(1)

System tables, by question

-- what is slow? (group by shape, sort by TOTAL time)
SELECT normalized_query_hash, count() AS runs, sum(query_duration_ms) AS total_ms,
       formatReadableQuantity(sum(read_rows)) AS rows, any(substring(query,1,90)) AS q
FROM system.query_log WHERE type='QueryFinish' AND event_time > now() - INTERVAL 1 DAY
GROUP BY normalized_query_hash ORDER BY total_ms DESC LIMIT 20;

-- PART COUNT — the number to alert on
SELECT database, table, count() AS parts FROM system.parts WHERE active
GROUP BY database, table ORDER BY parts DESC LIMIT 20;

-- where is the disk going, per column
SELECT name, type, formatReadableSize(sum(data_compressed_bytes)) AS comp,
       round(sum(data_uncompressed_bytes)/sum(data_compressed_bytes),1) AS ratio
FROM system.columns WHERE table='events' GROUP BY name, type ORDER BY sum(data_compressed_bytes) DESC;

-- running now
SELECT query_id, elapsed, formatReadableSize(memory_usage), read_rows, substring(query,1,80)
FROM system.processes ORDER BY elapsed DESC;
KILL QUERY WHERE query_id = '…';

-- merges / mutations
SELECT table, elapsed, progress, num_parts FROM system.merges;
SELECT table, mutation_id, parts_to_do, latest_fail_reason FROM system.mutations WHERE NOT is_done;
KILL MUTATION WHERE mutation_id = '…';

-- replication
SELECT database, table, is_readonly, absolute_delay, queue_size FROM system.replicas;
SELECT * FROM system.replication_queue WHERE num_tries > 5;

-- errors, disks, dictionaries, changed settings
SELECT name, value, last_error_message FROM system.errors WHERE value > 0 ORDER BY value DESC;
SELECT name, formatReadableSize(free_space), formatReadableSize(total_space) FROM system.disks;
SELECT name, status, last_exception FROM system.dictionaries;
SELECT name, value, default FROM system.settings WHERE changed;

Diagnosing a slow query

EXPLAIN indexes = 1 SELECT …;

   Granules: 8 / 4882312          ✅ the index is working
   Granules: 4882312 / 4882312    ❌ full scan — the ORDER BY key is wrong
                                     for this query (chapter 5)

Then, in order:

1. fix the ORDER BY key, or add a projection / materialised view
2. stop using SELECT *
3. PREWHERE the small selective columns
4. add a data skipping index (only if values are clustered)
5. pre-aggregate what repeats
6. only now touch settings

Aggregation quick reference

   uniqCombined(x)             ✅ default distinct count (~0.5% error)
   uniqCombined64(x)              above ~4 billion distinct
   uniqExact(x)                ⚠️  exact, unbounded memory
   quantileTiming(0.95)(ms)    ✅ latency
   quantileTDigest(0.99)(x)    ✅ accurate tails
   quantilesTDigest(.5,.9,.99)(x)  several in one pass
   topK(10)(x)                 approximate most frequent
   argMax(x, y)                x at the row with max y
   groupArray(10)(x)           ✅ ALWAYS bound it
   countIf / sumIf / avgIf / uniqIf   conditional, one pass
   -State / -Merge / -MergeState      partial aggregation

Joins

   smaller table ALWAYS on the right
   join_algorithm = 'grace_hash'      right side too big for RAM
   GLOBAL JOIN                        distributed: evaluate once, broadcast
   join_use_nulls = 1                 or LEFT JOIN gives 0, not NULL
   ASOF JOIN                          nearest preceding row by time
   LEFT SEMI / ANTI JOIN              faster than IN / NOT IN

   ✅ better: a DICTIONARY   dictGet('dict','attr',key)
   ✅ best:   denormalise at insert time

Materialised views

CREATE TABLE hourly (h DateTime, k LowCardinality(String),
                     views AggregateFunction(count),
                     users AggregateFunction(uniq, UInt64))
ENGINE = AggregatingMergeTree ORDER BY (k, h);

CREATE MATERIALIZED VIEW mv TO hourly AS
SELECT toStartOfHour(ts) AS h, k, countState(), uniqState(user_id)
FROM events GROUP BY h, k;

-- backfill history separately, strictly BEFORE the view's creation time
-- read with countMerge() / uniqMerge()

❗ a MV is an INSERT TRIGGER — no history, no updates, no deletes
❗ always use the `TO table` form
❗ a failing MV FAILS THE INSERT into the source table
✅ keep raw data 90 days, rollups 2 years — that is the point

The rules

ORDER BY is the whole game. Filter columns, low→high cardinality, time last. Effectively permanent.

PARTITION BY is for DROP PARTITION, not for query speed. Tens to hundreds, toYYYYMM(ts).

Batch your inserts. 10k–1M rows. Never one row at a time.

Never SELECT *.

Denormalise. Storage is cheap; joins are not.

LowCardinality under 10,000 distinct values.

Avoid Nullable. Use a default.

DROP PARTITION, not DELETE.

Alert on part count.

Profiles per user — memory cap, execution timeout, readonly.

Anti-patterns

❌ one INSERT per row                → batch, or async_insert = 1
❌ raising parts_to_throw_insert     → fix the batching
❌ SELECT *                          → name the columns
❌ WHERE toDate(ts) = …              → WHERE ts >= … AND ts < …
❌ ALTER UPDATE / DELETE routinely   → ReplacingMergeTree or DROP PARTITION
❌ FINAL on every query              → argMax / sum / -Merge
❌ big table on the right of a JOIN  → swap, or use a dictionary
❌ uniqExact on high cardinality     → uniqCombined
❌ groupArray unbounded              → groupArray(N)
❌ daily/hourly partitions for years → toYYYYMM
❌ OPTIMIZE FINAL in cron            → let merges work
❌ no TTL on system.query_log        → disk fills
❌ ClickHouse as the app database    → PostgreSQL alongside it

Two-minute triage

1. system.errors                what is failing
2. system.parts (count/table)   healthy part count?
3. system.processes             what is running
4. system.merges                keeping up?
5. system.replicas              lag, queue, readonly
6. system.mutations             anything stuck?
7. system.query_log             slow by total time
8. system.disks                 space

Where the other guides connect

PostgreSQL — the operational database that should own your state while ClickHouse owns analytics. Its chapter 15 draws the same line from the other side.
MongoDB — same relationship; feed ClickHouse with change streams (its chapter 12).
Kafka — the standard front door for ingestion, and the answer when clients cannot batch. ClickHouse’s Kafka engine consumes it directly.
Elasticsearch — the overlap for log analytics: search there, aggregate here.
Cassandra — the other LSM-tree storage engine in these guides; the same immutable-sorted-files idea, in a row-oriented world.
Redis — where to cache a dashboard result if even ClickHouse is not fast enough.

The one-sentence summary

ClickHouse sorts your data by the key you choose, stores each column separately and compressed, and reads only the granules it cannot rule out — so pick the ordering key for your queries, batch your inserts, denormalise freely, and never update a row.

Written by Sepuri Sai Krishna