Typorithm · medium

Partition Pruning

partition-pruningclickhousepartitionpruningpartition-by

Saved on this device

Full lesson · 8 learning steps Open any exercise for focused practice

Step 1 of 8 · intro card

Learn Partition Pruning

Indexing & Partitioning

PARTITION BY toYYYYMM(event_date) groups rows into monthly partitions, and a WHERE on the partition expression prunes whole partitions before any data is read.

PARTITION BY splits a table's parts by an expression, commonly toYYYYMM(date) for one partition per month.

When a query filters on the partition expression, ClickHouse compares the filter to each partition's min/max and skips partitions that cannot match — before reading any granules.

Pruning happens above the sparse index: first whole partitions are dropped, then the primary index skips granules inside the partitions that remain.

PARTITION BY groups parts by an expression.

Filters on it drop whole partitions.

Pruning happens before granule reads.

CREATE TABLE events
(
    event_date Date,
    user_id    UInt64,
    payload    String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (user_id, event_date);

SELECT count()
FROM events
WHERE event_date >= '2026-06-01';

Read the implementation

Connect each part of the code to the concept before you type.

1. Monthly partitions

toYYYYMM groups rows into one partition per month.

PARTITION BY toYYYYMM(event_date)

2. Prune by partition expr

A filter on the partition expression lets ClickHouse drop whole partitions before reading.

WHERE event_date >= '2026-06-01'

3. In-partition sort

ORDER BY still sorts and indexes rows within each surviving partition.

ORDER BY (user_id, event_date)

A complete learning loop

Understand

Read the concept map and follow the code breakdown.

Reconstruct

Type the implementation, repair mistakes and work through every recall exercise.

Verify

Use the final check without hints; revisit weak concepts in short practice sessions.