Aug 1, 2026

ORDER BY is the one ClickHouse decision you can't take back

Our inventory dashboard asked ClickHouse a simple question: what does stock look like today?

One day of rows mattered. ClickHouse read every day we had ever recorded.

The frustrating part is that the table was sorted by date. The date column was right there in the ORDER BY. It just wasn’t in the right place — and in ClickHouse, position is everything.

The table

Simplified, it looked like this:

CREATE TABLE inventory_snapshots
(
    variant_id    UInt32,
    branch_id     UInt16,
    snapshot_date Date,
    closing_stock Int32,
    stock_value   Decimal(18, 2)
)
ENGINE = MergeTree
ORDER BY (variant_id, branch_id, snapshot_date);   -- 👈 date last

And the query that ran on every dashboard load:

SELECT variant_id, branch_id, closing_stock, stock_value
FROM inventory_snapshots
WHERE snapshot_date = today()
  AND closing_stock <> 0

Read those two together. The query filters on exactly one column — snapshot_date — and that column is the last of the three in the sorting key.

That combination is the worst case, and it’s an easy one to walk into.

Why position decides everything

ClickHouse doesn’t build an index entry per row. It splits the table into granules of 8,192 rows and stores one mark per granule: the sorting-key values of that granule’s first row.

That tiny index is what lets ClickHouse skip data. But it can only skip when your WHERE matches a prefix of the sorting key — the first column, or the first two, and so on.

With ORDER BY (variant_id, branch_id, snapshot_date), the rows are laid out like a phone book sorted by last name, then first name, then birthday. Ask for everyone born on 3 May and the ordering buys you nothing. Those people are scattered across every page. You read the whole book.

That was our query. snapshot_date = today() matched no prefix, so the primary index couldn’t rule out a single granule. Every dashboard load scanned the entire table to find one day’s rows.

The fix, and what it bought

We moved the date to the front:

ORDER BY (snapshot_date, variant_id, branch_id)   -- date first

Now snapshot_date = today() is a prefix. Today’s rows sit together in a handful of granules, and ClickHouse skips the rest without reading them.

I can’t replay the migration itself — it happened long enough ago that the query log no longer reaches back. But the effect is easy to show on the table as it stands today. Two queries with comparable selectivity: one filtering on the first column of the sorting key, one on the last.

	Filter on first column	Filter on last column
Rows returned	35,938	36,064	—
Rows read	64,070	26,120,000	408× more
Bytes read	2.07 MiB	124.55 MiB	60× more
Peak memory	5.17 MiB	12.90 MiB	2.5× more
Query time	6 ms	22 ms	3.7× slower

Look at the first two rows together, because that’s the whole article.

Both queries returned about 36,000 rows. One read 64,000 rows to find them. The other read 26 million — seven hundred times more data than it handed back. Same table, same answer size, one column of difference in where the filter landed.

Now look at the bottom row, and be a little disappointed with it. Four hundred times the data read, and only 3.7× on the clock.

That mismatch is exactly why I trust read_rows over a stopwatch. I ran this twice: the rows-read ratio came out at 407× and 408×, while wall time said 2× on the first run and 3.7× on the second. The rows don’t move. The milliseconds do.

On an idle cluster with warm cache, ClickHouse chews through 26 million rows fast enough that the waste barely shows. The clock says fine. The rows read say this query is doing four hundred times the work it needs to.

You find out which one was telling the truth on a busy afternoon — when the dashboard is open on ten screens at once, when the table is ten times bigger, or when the cache is cold after a restart. Scan work you can’t skip doesn’t go away. It waits.

The trade-off we made

I want to be honest about what we gave up, because “put the date first” is not a universal rule.

The old key was good at something. WHERE variant_id = 123 AND branch_id = 7 — the stock history of one product at one store — was a prefix lookup under the old ordering, and it stopped being one under the new one.

We made that trade knowingly. The dashboard ran hundreds of times a day; the single-product history query ran rarely, and stayed fast enough by other means.

Your sorting key encodes a bet on which question you’ll ask most often. Make the bet on purpose.

The part nobody warns you about

Here’s where ClickHouse differs from every row store you’ve used.

In Postgres, a bad index is a cheap mistake. DROP INDEX, CREATE INDEX, move on. The table itself never changes.

In ClickHouse, the sorting key is the physical layout of the data on disk. There’s no ALTER TABLE ... ORDER BY that reshuffles an existing table. To change it, you rebuild it:

-- 1. new table, new sorting key
CREATE TABLE inventory_snapshots_new (...)
ENGINE = MergeTree
ORDER BY (snapshot_date, variant_id, branch_id);

-- 2. copy everything across
INSERT INTO inventory_snapshots_new SELECT * FROM inventory_snapshots;

-- 3. swap
RENAME TABLE inventory_snapshots     TO inventory_snapshots_old,
             inventory_snapshots_new TO inventory_snapshots;

Three statements, and two costs the statements don’t show:

You need room for two copies. During step 2 the old and new tables both exist in full. On a table that is a meaningful share of your disk, that alone can stop the migration.

Something has to give on writes. Rows arriving during the copy land in the old table and never reach the new one. Your options are to dual-write, to copy and then backfill the difference, or to pause ingestion.

We paused. The ETL stopped for a few minutes, the copy ran, we swapped the tables, and ingestion resumed against the new one. Not elegant, but it’s honest about what it is — and far simpler to reason about at 11pm than a dual-write that has to be exactly right.

Choosing a sorting key

A short checklist, learned the expensive way:

Start from your WHERE clauses, not your schema. Which columns does your hottest query filter on? Those belong at the front.
Prefix or nothing. A column in the sorting key that isn’t reachable through a prefix does nothing for filtering. Being “in the ORDER BY” is not the same as being indexed.
Low cardinality first, usually. Columns with fewer distinct values group rows into bigger, skippable blocks. A date, a branch, a status.
The sorting key is not a wish list. Every extra column makes the index bigger and the sort slower on insert. Three or four columns is plenty for most tables.
If two query patterns genuinely conflict, one table can’t serve both well. Either accept that one is slower, or keep a second table with a different sorting key.

And the one that costs the most to learn late: decide this before the table has a billion rows in it.

How I measured this

Numbers here come from system.query_log, not from a stopwatch — the client’s own timing includes network and formatting:

SELECT
    query_duration_ms,
    formatReadableQuantity(read_rows)  AS rows_read,
    formatReadableSize(read_bytes)     AS bytes_read
FROM system.query_log
WHERE query_id = 'my-tagged-query' AND type = 'QueryFinish'

For sorting-key work, read_rows tells you more than the clock does. It measures how much data the index let you skip, and unlike wall time it doesn’t drift with cache state or with whatever else the cluster is busy doing.

To check your own table, run two queries that return roughly the same number of rows — one filtering on the first column of your sorting key, one on the last — and compare read_rows against the row count each one actually returns. That second number is the one that matters: it tells you how much of the table you paid for and threw away.

One caveat on my numbers: they come from a development cluster, which holds less data than production and was otherwise idle. Treat the ratios as the signal and the milliseconds as noise.

These are the patterns I reach for constantly while tuning ClickHouse for retail reporting. I’m collecting the ones that keep coming up into a short, practical cookbook — the newsletter below is where it lands first, and replies come straight to me. If you’ve had to rebuild a table to fix a sorting key, I’d like to hear how you handled the writes.