ClickHouse MergeTree: ORDER BY Is the Index, Tiny Inserts Are the Outage

The product dashboard went from 80 ms to 14 seconds on a Tuesday morning. Nothing had shipped. system.parts showed 48,200 active parts on analytics.events. A new Lambda was inserting one row per click, and ClickHouse was doing exactly what MergeTree does: one INSERT, one part, then a merge queue that never caught up.

I treated ClickHouse like a faster Postgres for six months before that. The bill was fine. The parts were not. MergeTree is a columnar engine that indexes by sort order and wins by scanning less. It loses the moment you feed it OLTP-shaped writes.

ORDER BY is the index

There is no secondary B-tree you add later. The ORDER BY key is the sparse primary index. Granules of 8,192 rows store the min and max of that key. A query that filters on the leading columns skips granules. A query that filters on a column that is not in the key reads the table.

CREATE TABLE analytics.events
(
    event_date Date,
    event_ts   DateTime,
    user_id    UInt64,
    event_name LowCardinality(String),
    props      String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_name, user_id, event_ts)
SETTINGS index_granularity = 8192;

-- Prunes. Leading key matches the dashboard filter.
SELECT count()
FROM analytics.events
WHERE event_name = 'checkout_failed'
  AND event_date >= today() - 7;

-- Does not prune on user_id first. You pay the full scan.
SELECT count()
FROM analytics.events
WHERE user_id = 90211
  AND event_date >= today() - 7;

I put event_name first because every panel filtered on it. User lookup went to a second table keyed (user_id, event_ts). One wide table with a compromise ORDER BY made both query shapes mediocre. Warehouse clustering keys are a hint. This key is the layout.

Tiny inserts create parts

A MergeTree INSERT writes a sorted part and returns. Background merges combine parts later. That is the whole write path. A part is cheap. Forty thousand of them are not: the query planner opens every part, merges fall behind, and SelectedParts in the query log becomes the number you page on.

flowchart LR
  app["App / Lambda"] --> ins["INSERT"]
  ins --> part["New part"]
  part --> merge["Background merge"]
  merge --> fewer["Fewer, larger parts"]
  part -->|"thousands of tiny INSERTs"| queue["Merge queue grows"]
  queue --> slow["Queries open every part"]

One INSERT, one part. Merges are eventual. Tiny writes make “eventual” a dashboard outage.

SELECT
    table,
    count() AS parts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS on_disk,
    uniqExact(partition) AS partitions
FROM system.parts
WHERE active
  AND database = 'analytics'
  AND table = 'events'
GROUP BY table;

-- I page above 300 parts per partition, or merge lag over 20 minutes.

The Lambda wrote 12 million rows a day as 12 million inserts. After we batched to ~50,000 rows or 2 seconds, whichever came first, part count dropped to 180 and p95 came back under 200 ms. Same cluster. Same ORDER BY. The insert shape was the incident.

# Buffer in the process. Flush on size or time. Not per event.
BATCH = 50_000
buf: list[tuple] = []

def flush(client):
    global buf
    if not buf:
        return
    client.execute(
        "INSERT INTO analytics.events "
        "(event_date, event_ts, user_id, event_name, props) VALUES",
        buf,
    )
    buf = []

def on_event(client, row):
    buf.append(row)
    if len(buf) >= BATCH:
        flush(client)

async_insert=1 on the server is a safety net for bursty clients, not a license to keep one-row HTTP posts. I still own the batch in the producer. Buffer tables help when many writers cannot coordinate; they add another moving part I only add when I have to.

Mutations are not updates

ALTER TABLE ... UPDATE and DELETE are mutations. ClickHouse rewrites the affected parts. A delete of 2 percent of a 4 TB table is a rewrite of those parts, not a row flag. I have watched a “quick GDPR delete” pin merges for eleven hours and stall ingest.

Prefer ReplacingMergeTree or a collapsing engine when the business key mutates, and accept that the final row is correct after merges. For a hard delete that must be gone now, copy the keepers to a new table and swap. Do not pretend ALTER DELETE is DELETE FROM postgres.

ClickHouse is also not a warehouse replacement. There is no governance catalog, no cheap clone for a finance close, and no analyst-friendly cost attribution. I run it next to a warehouse: ClickHouse for the product loop that needs sub-second scans, warehouse for contracts and month-end. The format debate on the lake — Iceberg vs Delta — is a different layer. MergeTree is the serving engine, not the lake.

FAQ

Why is a “simple” SELECT slow? Usually too many parts from tiny inserts, or an ORDER BY that does not match the filter. Check system.parts before you add RAM.

Is ClickHouse my warehouse? No. It is the sub-second serving scan. Contracts and month-end stay in Snowflake / BigQuery / the lakehouse. Hub: ClickHouse.

ALTER DELETE for GDPR? It is a mutation. It can pin merges for hours. Copy keepers and swap if it must be gone now.

Batch size? Thousands to tens of thousands of rows per insert, not one HTTP POST per event. Buffer in the app or a queue.

What I watch besides system.parts

DelayedInserts means the insert path is waiting on merges. MergedUncompressedBytes climbing while parts stay high means merges are losing. I also watch the count of parts older than a few minutes in the hot partition — that is the Lambda-per-click smell before the dashboard hits 14 seconds.

SELECT
    table,
    count() AS parts,
    sum(rows) AS rows,
    max(modification_time) AS last_mod
FROM system.parts
WHERE active AND database = 'analytics'
GROUP BY table
HAVING parts > 200
ORDER BY parts DESC;

Buffer in the app or a queue (Kafka, then a batch insert every few seconds). Do not put a sidecar that POSTs one HTTP insert per event and call it “real time.” Related: ClickHouse vs Snowflake.

What I run in production

Partition by month, not by hour, unless the volume is huge. Hourly partitions on a quiet table are tiny parts by another name. Keep SELECT * off wide rows; props as a String is cheap to skip when the query names columns. Watch DelayedInserts and MergedUncompressedBytes the way you watch disk.

The mental model that stuck: ORDER BY decides what you skip. Insert batching decides whether the engine can skip anything at all. Get those two right and MergeTree is the fastest scan I have run. Get the insert path wrong and you will debug a “ClickHouse is slow” ticket that is really 48,000 files.