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.

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.