5. The ordering key, partitions, and TTL

This is the chapter that decides whether your ClickHouse is fast.

Chapter 3 showed that a query reads only the granules the sparse index cannot rule out. The ordering key is what decides how many that is. Choose well and a query reads 8,192 rows out of 40 billion. Choose badly and it reads all 40 billion, and no setting, no hardware, and no rewrite of the SQL will save you.

ORDER BY — how to choose

The rule, stated first and then justified:

Put the columns you filter on most often first, in order of increasing cardinality — with the caveat that a column you rarely filter on should not be first no matter how low its cardinality.

Three properties are at stake simultaneously.

1. Granule skipping. The index only helps for a prefix of the ordering key. This is the same prefix rule as MongoDB’s compound indexes and PostgreSQL’s multi-column indexes, and it works for the same reason: the data is sorted by the tuple, so only a leading subset gives you a contiguous range.

ORDER BY (country, device, ts)

   WHERE country = 'UK'                        ✅ contiguous range
   WHERE country = 'UK' AND device = 'mobile'  ✅ narrower range
   WHERE device = 'mobile'                     ❌ scattered through every country
   WHERE ts >= '2026-08-01'                    ❌ scattered everywhere

2. Compression. Chapter 2: the first column becomes long runs of identical values and compresses enormously. Later columns compress progressively less.

3. Merge and deduplication behaviour. For the engines in chapter 4, the ordering key is the identity of a row.

Why low cardinality first

Sorting by (country, ts) produces:

   DE, 00:00 │ DE, 00:01 │ DE, 00:03 │ … │ FR, 00:00 │ FR, 00:02 │ … │ UK, …
   └──────────── all German rows, sorted by time ────────────┘

Within each country, time is sorted — so WHERE country='UK' AND ts BETWEEN … narrows twice.

Sort by (ts, country) instead and every country appears in every granule. Filtering by country skips nothing at all.

The general shape that works: (low-cardinality filter columns…, high-cardinality filter column, timestamp).

The mistakes

A high-cardinality column first. ORDER BY (user_id, ts) with 500 million users means each granule holds a handful of users, the index is effectively random with respect to every other filter, and — worse — every other column becomes incompressible because adjacent rows are unrelated.

Do this only if you genuinely filter by user_id on nearly every query. If you do, it is correct. If you do not, it is the single most damaging choice available.

A timestamp first when you also filter on something else. This is the most common real-world error, because it feels natural for time-series data. If most queries are WHERE tenant = X AND ts > Y, put tenant first. Time-based partitioning already handles the coarse time filter (below) — the ordering key does not need to repeat that job.

Too many columns. Each additional column adds index size and merge cost while contributing less and less skipping. Three to five is a good range. Beyond that, the tail columns are essentially free-riding.

Using a random UUID anywhere near the front. It destroys compression for the whole table.

Working it out from your queries

The method, and it takes twenty minutes:

-- what do people actually filter on? read your own query log
SELECT query FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 7 DAY
  AND query ILIKE '%page_views%'
LIMIT 200;

Then:

1. List the top ten queries by frequency and by rows read.
2. Find the filter columns that appear in most of them. That is your candidate prefix.
3. Order those by cardinality, ascending.
4. Put the timestamp last.
5. Verify with EXPLAIN indexes = 1 on each of the ten, and look at the granule ratio.

If two query patterns want genuinely different orderings, you do not compromise — you create a projection or a second materialised table with the other ordering (chapter 11). Storage is cheap; scanning 40 billion rows is not.

PARTITION BY — a different thing entirely

The most common conceptual mix-up in ClickHouse. Partitioning is not a second index and it is not for query performance in the way the ordering key is.

ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (country, ts)

A partition is a logical group of parts that are never merged with parts from another partition. Physically it becomes a separate set of directories:

   /var/lib/clickhouse/data/default/page_views/
   ├── 202606_1_57_3/
   ├── 202607_58_112_3/
   └── 202608_113_140_2/
        ▲
        └── partition id = 202608

What partitioning is genuinely for:

Dropping data instantly. This is the main reason.

ALTER TABLE page_views DROP PARTITION '202601';

That deletes a directory. It is instant, regardless of size. Compare with DELETE, which is a mutation that rewrites every affected part (chapter 6). If you have a retention policy, partition by time.

Tiered storage. Move old partitions to cheaper disks or object storage (below).

Backup and manipulation. FREEZE PARTITION, ATTACH/DETACH PARTITION, and moving a partition between tables are all directory-level operations.

Coarse pruning. A query with WHERE ts >= '2026-08-01' does skip whole partitions — genuinely useful, and secondary to the ordering key.

The one rule about partitioning

Do not over-partition

Aim for tens to low hundreds of partitions per table, not thousands.

Each partition holds its own parts, its own merge stream, and its own set of open files. Partition by day for three years and you have 1,095 partitions; partition by hour and you have 26,000, each with several parts, each with a file per column. Startup becomes slow, merges fragment, memory use climbs, and TOO_MANY_PARTS becomes routine.

The default good answer is PARTITION BY toYYYYMM(ts) — one per month. Use daily only for genuinely huge tables with short retention. Partitioning by a non-time column with high cardinality (user_id, tenant_id with thousands of tenants) is almost always a mistake.

The two-line summary of the distinction:

   ORDER BY     → decides which granules to read inside a part.
                  This is what makes queries fast.

   PARTITION BY → decides which parts exist at all.
                  This is what makes dropping data instant.

TTL — automatic expiry and tiering

ClickHouse can delete or move data by age, evaluated during merges.

Row-level TTL

CREATE TABLE events (
    ts     DateTime,
    ...
) ENGINE = MergeTree
ORDER BY (type, ts)
TTL ts + INTERVAL 90 DAY;

Rows older than 90 days are removed when a merge touches their part. That means expiry is not prompt — a part that is never merged keeps its expired rows.

Force it if needed:

ALTER TABLE events MODIFY TTL ts + INTERVAL 90 DAY;   -- triggers re-evaluation
OPTIMIZE TABLE events FINAL;                          -- expensive; use sparingly

Or set ttl_only_drop_parts = 1, which makes ClickHouse drop whole parts when every row in them has expired rather than rewriting parts to remove some rows. Combined with time-based partitioning this is dramatically cheaper, and it is what you want for retention on a large table.

For pure retention, DROP PARTITION on a schedule beats TTL — it is instant and costs nothing.

Column-level TTL

Drop expensive columns while keeping the rest:

CREATE TABLE events (
    ts        DateTime,
    user_id   UInt64,
    raw_body  String TTL ts + INTERVAL 7 DAY,   -- keep the big column briefly
    status    UInt16
) ENGINE = MergeTree
ORDER BY (status, ts);

After seven days, raw_body is reset to its default and the storage is reclaimed while the row survives. Excellent for logs where the payload is huge and the metadata is what you query.

TTL with rollup

Aggregate old data instead of deleting it:

TTL ts + INTERVAL 30 DAY GROUP BY country, toStartOfHour(ts)
    SET views = sum(views), duration = avg(duration)

Per-second data becomes hourly after a month. The GROUP BY here must be a prefix of the table’s ORDER BY.

TTL for tiered storage

The most operationally valuable form. Define storage volumes, then move data between them by age:

config.xml

<storage_configuration>
  <disks>
    <fast><path>/mnt/nvme/</path></fast>
    <slow><path>/mnt/hdd/</path></slow>
    <cold><type>s3</type><endpoint>https://…</endpoint></cold>
  </disks>
  <policies>
    <tiered>
      <volumes>
        <hot><disk>fast</disk></hot>
        <warm><disk>slow</disk></warm>
        <archive><disk>cold</disk></archive>
      </volumes>
    </tiered>
  </policies>
</storage_configuration>

CREATE TABLE events (...) ENGINE = MergeTree
ORDER BY (type, ts)
PARTITION BY toYYYYMM(ts)
TTL ts + INTERVAL  7 DAY TO VOLUME 'warm',
    ts + INTERVAL 90 DAY TO VOLUME 'archive',
    ts + INTERVAL  2 YEAR DELETE
SETTINGS storage_policy = 'tiered';

Recent data on NVMe, last quarter on spinning disk, older on S3, gone after two years — automatic, with no operator involvement and no change to queries. This is one of ClickHouse’s genuinely best features and it turns “how much history can we afford?” into a configuration line. The s3 disk can point at any S3-compatible endpoint including a self-hosted MinIO — and when it does, ClickHouse owns the lifecycle of those objects, so MinIO’s own expiry rules should stay off that bucket entirely.

Watch it work:

SELECT partition, disk_name, formatReadableSize(bytes_on_disk), rows
FROM system.parts WHERE table = 'events' AND active
ORDER BY partition;

Putting it together — a good table

CREATE TABLE page_views (
    ts        DateTime          CODEC(DoubleDelta, ZSTD(1)),
    tenant_id UInt32,
    country   LowCardinality(String),
    device    LowCardinality(String),
    user_id   UInt64            CODEC(T64, ZSTD(1)),
    url       String            CODEC(ZSTD(3)),
    duration  UInt32            CODEC(T64, LZ4)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)               -- ~monthly, instant retention drops
ORDER BY (tenant_id, country, ts)       -- the filters, low → high cardinality
TTL ts + INTERVAL 30 DAY  TO VOLUME 'warm',
    ts + INTERVAL 365 DAY DELETE
SETTINGS index_granularity = 8192,
         ttl_only_drop_parts = 1;

Every line of that is a decision from chapters 2 through 5, and it is a reasonable starting template for an events table.

What you would type

-- what the index actually skips
EXPLAIN indexes = 1 SELECT count() FROM page_views WHERE tenant_id = 7;

-- partitions, sizes, and which disk they are on
SELECT partition, count() AS parts, sum(rows), formatReadableSize(sum(bytes_on_disk)), any(disk_name)
FROM system.parts WHERE table = 'page_views' AND active
GROUP BY partition ORDER BY partition;

-- how many partitions do I have? (should be tens, not thousands)
SELECT uniqExact(partition) FROM system.parts WHERE table = 'page_views' AND active;

-- the table definition as ClickHouse sees it
SHOW CREATE TABLE page_views;

-- instant retention
ALTER TABLE page_views DROP PARTITION '202501';

What goes wrong

Timestamp first in ORDER BY when queries also filter on a tenant, country or type. The most common design error.

Over-partitioning. Daily or hourly partitions on a table with years of history. Slow startup, fragmented merges, TOO_MANY_PARTS.

Partitioning by a high-cardinality column. PARTITION BY user_id creates a partition per user. It will not survive contact with production.

Expecting PARTITION BY to make queries fast. It prunes coarsely; the ordering key does the real work.

TTL that never fires because parts are not being merged. Use ttl_only_drop_parts and time partitioning, or drop partitions directly.

Changing ORDER BY later. You largely cannot. ALTER TABLE ... MODIFY ORDER BY can only append columns to the existing key. Changing the leading columns means creating a new table and rewriting the data — an INSERT INTO new SELECT * FROM old, which is fast but requires the disk and the downtime. Get this right at design time.

What to take from this chapter

• ORDER BY is the most consequential decision in ClickHouse. It sets granule skipping, compression, and row identity.
• Choose it as the columns you filter on most, ordered low to high cardinality, timestamp last. Verify with EXPLAIN indexes = 1 and the Granules: read/total ratio.
• Only a prefix of the key helps. Two genuinely different access patterns need a projection or a second table, not a compromise key.
• PARTITION BY is not an index. It exists for instant DROP PARTITION, tiered storage, and coarse pruning.
• Tens to low hundreds of partitions. toYYYYMM(ts) is the default right answer.
• TTL deletes rows, resets columns, rolls up old data, and moves data between storage tiers — hot NVMe to warm disk to S3, automatically.
• ttl_only_drop_parts = 1 plus time partitioning makes retention cheap; DROP PARTITION makes it free.
• You cannot meaningfully change ORDER BY later. Only appending columns is supported.

Next: what actually happens when you insert.