Skip to content
The Data EngineeringThe Data EngineeringBeta
About
Main content

Partitions, buckets, row groups and zone maps

Which units can a query prove it does not need?

Micro-partitioning

The design move is to remove the user's ability to get G wrong.

Explicit Hive-style partitioning (one directory per value, for example /date=2026-03-01/) fails in two directions:

  • Too coarse: a partition is far larger than the I/O unit. You prune down to a directory and then scan all of it.
  • Too fine: this happens as soon as someone partitions by a high-cardinality . You get millions of directories, files far below the natural batch size of the storage (the batch-size rule from Data at rest and the one ratio), and metadata operations that cost more than the data.

Both are mismatch-rule failures. The second is also a batch-size-rule failure: the unit fell below the minimum set by the fixed cost.

Micro-partitioning takes the decision away. The system cuts the data into small immutable units of its own choice (Snowflake's are 50-500 uncompressed) and keeps statistics for every column of every unit. Pruning becomes the evaluation of metadata, not the matching of a path.

Naming versus describing

The distinction worth keeping is .

  • Hive partitioning puts the predicate in the path. That is a name. A name gives exact, zero-cost pruning for equality on exactly one expression and nothing else. It forces low cardinality, because the namespace is physical.
  • Micro-partitioning puts the predicate in statistics. That is a description. Descriptions are approximate, apply to every column at once, and cost storage and evaluation time.

Exactness and rigidity on one side; generality and metadata cost on the other. The same trade-off appears far outside storage. It is also the idea behind Iceberg's : the transform (for example "day of scan_time") is recorded in the table metadata and is not written into the path.

Bucketing

has a different purpose from everything above:

Derivation. A shuffle join sends rows across the network so that equal keys meet on the same machine. It moves O(N) bytes at query time. Suppose both tables are hash-partitioned into the same number of buckets by the same hash function on the join key. Then bucket i of table A can only match bucket i of table B, and the shuffle disappears.

, and now it is paid on every write forever. This is the in its most legible form.

Why bucketing is brittle

  • It is a committed . The bucket count is baked into the data. Changing it means a full rewrite.
  • Both sides must agree on the count and the hash function. Any engine that does not know the convention shuffles again anyway.
  • You have coupled the physical layouts of two datasets. Neither can change alone.

Skew is worse than expected

Hashing concentrates skew by design. Every row of a hot key (a key with a huge number of rows) lands in exactly one bucket. So the finishing time of the join becomes the finishing time of one task that holds that key. This is the with real teeth: the average bucket is fine, the variance kills you. And unlike most skew, you cannot fix it by adding parallelism, because the partitioning function forbids splitting that key.

Parcelo's version. Suppose scan_events is bucketed into 256 buckets by parcel_id to join it with a parcels table. If a bad scanner writes a placeholder parcel_id = 'UNKNOWN' on 3% of all rows (60 million rows), all of those rows go to one bucket, which is then about 60 million ÷ (2 billion ÷ 256 ≈ 7.8 million) ≈ 8 times bigger than an average bucket. The whole join waits for that one task.

Hash versus range

Hashing destroys order. So bucketing gives equality pruning and no range pruning. Sorting gives range pruning and, through the same order, equality pruning too. So why ever hash? Because sorting concentrates writes: new data lands at one end of the key space, which makes one unit hot. Hashing spreads writes uniformly.

Hash versus range partitioning of a key space also shows up in record routing between services and in shard assignment in distributed systems, with the same trade-offs and different words each time.

Segment, row group and stripe organization

This is where G is finally pinned down, and the interesting result is that it cannot be.

Four constraints act on the unit size:

  • Two push it up: the fixed cost of the storage must be amortized (batch-size rule), and the per-unit metadata must stay small compared with the data.
  • Two push it down: skip resolution improves as units get smaller, and the rewrite cost under is proportional to the unit size.

Lower bound for object storage. By the natural transfer size, keeping a 50 ms request under 10% of the transfer time at about 80 MB/s needs requests of roughly 40 MB. That is the floor under 's 128 MB and ORC's 64 MB stripe: both sit above it. The floor is not convention. It is arithmetic.

Upper bound. The payoff is capped at min(G, 1/s). So any G larger than 1/s for your typical selective query is waste. If a selective query returns 10⁴ rows from 10⁹ (so 1/s = 10⁵), a useful G is around 10⁴ rows, which at 200 bytes per row is about 2 MB. The cap min(G, 1/s) alone would allow up to 10⁵ rows; a unit much larger than one query's answer is mostly read in vain. Either way the useful unit is a few MB, far below 40 MB.

The answer is a hierarchy

Every mature format answers with a hierarchy and not with a number:

  • Parquet: file → row group → page.
  • ORC: file → stripe → a row entry every 10,000 rows.

The outer level meets the I/O amortization bound. The inner level meets the skip-resolution bound. Metadata exists at both levels. This is not a compromise between the two constraints. It is a refusal to compromise: you pay in complexity instead.

Generalize it, because it appears again and again:

Cache hierarchies, B-tree fanout, LSM levels and "partition, then row group" all have this shape.

Parcelo's version. 128 MB files hold 640,000 rows of 200 bytes. A tracking lookup needs about 12 rows, about 2.4 . The outer unit (the file, fetched with one range request) is sized for the object store. Inside, Parquet pages (typically around 1 MB each, before , in many writers) with their own min/max values let the reader skip most of the file even after it is open.

Data-skipping metadata

This is the proof machinery. A zone map is the weakest useful summary: just [min, max] per column per unit. It supports exactly one inference: if the range of the predicate does not intersect the , skip the unit.

Skipping metadata is one-sided

Proving relevance would mean reading the unit, which is what you wanted to avoid. So every such structure is a conservative over-approximation of the true set of values. A false positive ("maybe there is a match" when there is none) only wastes I/O. A false negative ("no match" when there is one) is a correctness bug: the query would return wrong results. This asymmetry is why Bloom filters are usable here. A (a compact structure that answers "is this value possibly in the set?") can only err toward "maybe present". A structure with errors in both directions would be useless however small it was.

Two properties of zone maps

1. Their quality is clustering quality, restated. [min, max] is tight exactly when the values in the unit are contiguous in value space, which is what clustering arranges. Zone maps do not do the skipping; clustering does. The zone map is how the engine finds out.

2. They are fragile and not free. One outlier (a sentinel date such as 9999-12-31) widens a unit's range to everything, and at a million units the summaries themselves run to gigabytes. Both are worked out with the zone map itself, together with the stronger summaries (Bloom filters, value sets, histograms) and what each costs per unit or per value.

The metadata-growth rule and "metadata is data"

Here the appears: metadata grows linearly with the data, but one read has a fixed capacity. So metadata needs its own hierarchy.

And the principle is now established:

The recursion stops only when the top level fits in one read. That is why Iceberg has : they are the zone maps of the zone maps.

Where independence is assumed (the unverifiable-assumption rule)

The question to ask is: "What does this mechanism assume that it cannot verify, and who pays when the assumption is wrong?" For clustering, the assumption is independence: that columns behave independently of each other. It is assumed everywhere and is true almost nowhere. This section is where it costs the most and also pays the most.

It costs you in estimation. A query planner estimates a multi-predicate query by multiplying the selectivities of single columns, and then estimates skipping from per-column zone maps. Correlated columns break this in both directions. country = 'IN' AND city = 'Pune' has a true far higher than the product of the two, because almost every Pune row is an IN row. The skip estimate is wrong by whatever the correlation is. You see it as a plan that chose a scan it should not have chosen, with no hint why.

It pays you in hidden clustering. If order_id rises together with order_date, then clustering on date gives you almost free skipping on order_id, which nobody designed and nobody documented. Many production tables skip well on columns that were never part of the clustering key, only because the order of ingestion correlated with something. This is also why reclustering sometimes makes a query slower: it destroyed an accidental correlation.

It wastes bits. Z-ordering two strongly correlated columns spends half of the interleaved bits on information that the other half already has. The effective dimensionality is lower than the declared d. That is good for the U^(1−1/d) floor, and bad for the idea that you bought two dimensions of insurance.

The honest summary: clustering quality is a property of the joint distribution of your data and your queries, and every tool you have measures only single columns.

Measuring clustering quality

Every statement in this article is unfalsifiable in your own environment without a measure of how good the current clustering really is: how much the per-unit [min, max] ranges overlap, or how many units could contain a given value. That measure, overlap depth, is also what tells maintenance when the bet has decayed enough to pay for a rewrite.

Parcelo's version. For the file list of scan_events, Kavya can compute for the column depot_id the number of files whose [min, max] contains a given depot. If it is 6 for every depot, clustering is healthy. If it is 3,125 for every depot (every file), clustering is gone. That one number would have shown her the backfill problem on the first day.

When the bet was wrong: clustering evolution

Clustering is a bet on a predicate. What happens when the bet was wrong? Options include changing the clustering key, and table formats such as Iceberg that support partition evolution (the partitioning scheme changes over time). The central question is whether old data is rewritten into the new scheme or left under the old one, with the table then holding files of two schemes at once. This is the escape hatch for clustering's central risk. It is also not free: rewriting the whole table costs about U/d per unit as discussed above, and leaving old data alone means that old and new data skip differently.

Two things that remain open

  • Secondary access paths. Clustering serves one dimension (or a few, weakly). Secondary structures (indexes such as B-trees, hash indexes, bitmap indexes and inverted indexes) serve the rest. They are search structures and are a separate topic from this mechanism, but note that this article's synthesis points at them as the answer when d is 5 or more.
  • Adaptive and learned approaches. Database cracking (the data is partially reorganized as a side effect of the queries that arrive), adaptive indexing and learned index structures (a model predicts where a key sits). These are genuinely contested. The laboratory results are strong. Production adoption is thin. The reasons for the gap (how updates are handled, worst-case guarantees) are exactly the constraints this article names.