Data Systems

Partitioning and Bucketing

Partitioning and bucketing physically organize a dataset so queries read less data and joins avoid shuffling, at the cost of careful key choice.

Physical layout controls query cost

How a dataset is laid out on storage decides how much a query must read. Partitioning and bucketing are two complementary techniques that arrange data so common queries touch a fraction of it. Both trade some write-time organization for large read-time savings, and both depend on choosing keys that match how the data is actually queried.

Partitioning

Kronos motion — cta read papers

Partitioning splits a dataset into separate directories or files by the value of a column, most often a date. A query that filters on the partition column reads only the matching partitions and skips the rest, a technique called partition pruning. Partitioning by day means a query for one week reads seven partitions instead of the whole history. The partition column should be one that queries frequently filter on and that has moderate cardinality.

The small-files and skew problems

Too fine a partition key creates many tiny files, which are slow to read
Too coarse a key defeats pruning, since each partition is huge
Skewed keys make some partitions far larger than others
A high-cardinality key (like a user id) is usually a poor partition column

Bucketing

Bucketing distributes rows into a fixed number of buckets by hashing a column. Unlike partitioning, the bucket count is fixed and buckets do not become directories you prune. Its benefit is for joins and aggregations: if two tables are bucketed on the same key into the same number of buckets, they can be joined bucket-to-bucket without a full shuffle of data across the network, because matching keys are already colocated. Bucketing suits high-cardinality join keys that make poor partition columns.

Combining the two

Partitioning and bucketing are often used together: partition by date to prune by time, and bucket by a join key within each partition to make joins efficient. Sorting data within each file by a common filter column adds a third layer, letting columnar formats skip row groups. The unifying principle is to make the physical layout mirror the query pattern, so the system reads and moves as little data as possible. See columnar storage, DAG scheduling, and warehouse vs lake.