Partitioning splits one logical table into many physical segments by the value of a partition key - most often a date. Queries that filter on that key then touch only the relevant segments instead of the whole table, a process called partition pruning. For analytical workloads, where filtering by time is nearly universal, partitioning is one of the most reliable ways to make large tables fast.

Pruning is the payoff

If a fact table is partitioned by month and a query asks for last week's data, the engine inspects partition metadata, discards every month that can't match, and scans only the relevant one - turning a full-table scan into a fraction of the work. The win scales with selectivity: the more partitions a query can eliminate, the bigger the speedup. This only works when queries filter on the partition key, which is why the key should be a column that appears in almost every query's WHERE clause - typically the event date.

-- partition by month; a date-filtered query reads one partition
CREATE TABLE events (...)
PARTITION BY toYYYYMM(event_date)
ORDER BY (customer_id, event_date);

SELECT count() FROM events
WHERE event_date >= '2026-06-01';   -- prunes all but June partitions

Choosing the key and grain

Granularity is a balance. Too coarse (yearly) and pruning barely helps; too fine (hourly on years of data) and you drown in tiny partitions, each carrying metadata and file overhead - the "too many small partitions" failure mode that slows planning and loading. Daily or monthly date partitions suit most fact tables. The partition key should be low-to-moderate cardinality and align with how data is loaded (so each load writes whole partitions) and queried (so filters prune).

Partitioning vs. clustering

Partitioning is coarse physical separation; clustering or sort order is fine ordering within a partition. They're complementary: partition by date to eliminate whole time ranges, then sort within each partition by the columns you filter and group on so that zone-map pruning and compression work inside the partition. Used together they shrink a query's scan twice - first to the right partitions, then to the right blocks within them.

Frequently asked questions

What is partition pruning? The engine skipping entire partitions that can't satisfy a query's filter on the partition key, reading only the matching segments instead of the whole table.

What should I partition by? A column present in almost every query's filter and aligned with how data loads - usually the event date, at daily or monthly grain.

Can I have too many partitions? Yes. Very fine partitioning creates many small segments whose per-partition metadata and files slow planning, loading, and even queries. Keep grain coarse enough to avoid thousands of tiny partitions.

How is partitioning different from clustering? Partitioning separates data into coarse physical segments by key; clustering/sort order arranges rows within each segment. Combine them: prune to partitions, then skip blocks within.