Partitioning#
Partitioning splits a table's files into groups by the value of a key, such as the day, so that a query filtering on that key reads only the matching groups.
Before reading
Why it matters#
File statistics let a reader skip parts of a file, but only after finding the file. Partitioning works one step earlier: the engine knows which files belong to which key value and never opens the others. Partitions are also the unit of maintenance. Reloading one day, compacting yesterday or deleting data older than two years each touch a set of partitions, not the whole table.
How it works#
In the classic layout, from Hive, the partition value is written into the file path:
shop/orders/order_date=2026-10-27/part-0001.parquet
shop/orders/order_date=2026-10-28/part-0001.parquet
shop/orders/order_date=2026-10-28/part-0002.parquet
A query with WHERE order_date = DATE '2026-10-28' can be answered from one path. The engine drops every other partition before reading anything. This is partition pruning.
flowchart LR
Q["WHERE order_date = '2026-10-28'"] --> P{Partition<br/>pruning}
subgraph T[shop.orders]
D1[order_date=2026-10-26<br/>3 files]
D2[order_date=2026-10-27<br/>3 files]
D3[order_date=2026-10-28<br/>3 files]
end
P -.->|skipped| D1
P -.->|skipped| D2
P ==>|read| D3Pruning works only when the query filters on the partition key in a form the engine can match. In the Hive layout, order_date is a separate column derived from ordered_at when the data is written. A query that filters on ordered_at alone does not prune, because the engine does not know how the two relate. Table formats such as Iceberg fix this with hidden partitioning (see Apache Iceberg: table format basics): the table stores the rule day(ordered_at) and prunes on filters against ordered_at directly.
A good partition key has three properties. Most queries filter on it. It has a moderate number of values, so each partition holds enough data for files of a good size, a few hundred megabytes or more. And it matches how data arrives and is maintained. A date is the most common choice because data arrives by time, reports filter by time and retention deletes by time.
Over-partitioning is the more common mistake. Partitioning by customer, or by hour on a small table, creates thousands of partitions with tiny files: every write produces more small files, planning has to track all of them, and queries open many files for little data. Under-partitioning means one or a few huge partitions, where every query and every maintenance job touches everything. Partitioning on several keys, such as date and then country, multiplies the number of partitions.
Bucketing handles keys with many values. Rows are assigned to a fixed number of buckets by hashing the key, for example 16 buckets by customer_id. A lookup for one customer reads one bucket out of 16, and the bucket count stays fixed however many customers there are. Two tables bucketed the same way on a join key can also be joined bucket by bucket.
Sorting rows inside each partition by the next most filtered column complements partitioning; see Query performance on lake tables.
Key terms#
- Partition key The column, or derived value, that decides which partition a row belongs to.
- Partition pruning Skipping partitions that cannot match a query's filter.
- Hive-style partitioning Partition values written into file paths as key=value.
- Hidden partitioning The table derives partition values from a column by a stored rule.
- Bucketing Assigning rows to a fixed number of groups by a hash of a key.
- Cardinality The number of distinct values of a column.
Example#
The shop stores about 250 GB of orders a year from 200,000 customers. Two choices of partition key:
| By order date | By customer | |
|---|---|---|
| Partitions after one year | 365 | 200,000 |
| Data per partition | about 700 MB | about 1.25 MB |
| Daily sales query | reads 1 partition | reads all 200,000 |
| One customer's order history | reads all 365 | reads 1 partition |
By customer is wrong: 1.25 MB partitions mean tiny files, and the most common query reads them all. By day gives partitions of about 700 MB, files of a good size, and one partition per daily report. Customer lookups are handled by sorting each day's files by customer_id.
Pitfalls#
- Partitioning on a column with many values. The result is many tiny files and slow planning.
- Filtering on a different column than the partition key. In the Hive layout, filtering on ordered_at does not prune order_date partitions.
- Time zones. A partition date in UTC and a report date in local time disagree near midnight; decide which one the partition uses and document it.
- Changing the scheme later. In the Hive layout this means rewriting the whole table; Iceberg can change the rule for new data only.