Bucketing
Hashing a key into a fixed number of files so that rows with the same key always land in the same bucket. One physical technique, and it earns its keep almost exclusively when it lets a join skip the shuffle.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
When is it worth deciding, at write time, exactly which file every row will live in — and what does that buy that sorting does not?
A recurring join between two large tables that dominates the pipeline's runtime and cost. Bucketing is not a general layout improvement; it is a targeted intervention for a specific join, and if you cannot name the join it is not the right tool.
The unit is the bucket: one file (or a small fixed set of files) holding every row whose key hashes to a given value, out of a fixed number chosen when the table was written. The bucket is a deterministic function of the key, which is the entire property being bought.
Write both tables normally and let the engine work out the join. It will, and for a join that runs occasionally against tables that fit comfortably in memory on the small side, it is the right answer and nothing here applies (Broadcast Joins).
The join is between two large tables. Neither fits on the small side, so the engine redistributes both by the join key across the cluster — a full shuffle, moving essentially all of both tables over the network, every time the job runs (The Shuffle).
- The join is between two large tables. Neither fits on the small side, so the engine redistributes both by the join key across the cluster — a full shuffle, moving essentially all of both tables over the network, every time the job runs (The Shuffle).
- The shuffle is the job. Everything else finishes quickly and the redistribution dominates the runtime, the network cost and the failure surface (Narrow and Wide Transformations).
- The join key is high cardinality, so partitioning on it is not available — the last lesson explained exactly why (Partition Cardinality).
- Sorting helps a filter and does nothing for a join, because a join needs matching keys to be co-located, not merely findable (Clustering and Sort Order).
- The same shuffle repeats on every run, so the cost is a recurring tax on a physical arrangement that could have been decided once at write time.
What is actually happening
- Bucketing applies a hash function to the key, takes it modulo a fixed bucket count, and writes the row into the file for that bucket. Because hashing is deterministic, every row with a given key is in a known bucket — and the same is true in any other table bucketed the same way (Hash Table).
- That is what lets a join avoid redistribution. If both sides are bucketed on the join key with the same function and the same count, bucket *n* of one side can only match bucket *n* of the other, so the join decomposes into independent per-bucket joins with no data movement between workers (Join Algorithms: Nested Loop, Hash, Merge).
- The bucket count is baked into the data, and both sides must agree on more than the count: the hash function, the key columns and their order, and the null handling. Changing any of them re-maps every key, invalidating co-location for every existing file; a mismatch in any of them silently yields a normal shuffle, with a correct answer and no benefit (Hash Index Internals, Query Optimizers).
- Bucketing is a hash partitioning, so it does not support range predicates. A filter for a range of keys must read every bucket, because a hash deliberately destroys ordering. This is the opposite property from sorting and the two are complementary rather than alternative (Radix Sort).
- It also does not fix skew, and can entrench it. A key value that dominates the data hashes to exactly one bucket, so that bucket is as large as the skew is severe and becomes a single task no amount of parallelism divides (Data Skew, Salting a Skewed Key).
- The same primitive appears at every scale in this field: hash partitioning inside a distributed database, hash-partitioned exchange inside a query engine, and consistent hashing across nodes. Bucketing is the version of it that is written down on disk instead of computed each time (Partitioning and Sharding).
A deterministic file for every key
CLUSTERED BY ... INTO n BUCKETS syntax is the Hive-derived form used by several engines; others express the same idea through table properties or a different clause, and a few do not expose it at all. What is portable is the requirement that both sides agree on key, order and count — engines that support it all impose that, and engines that do not will simply shuffle.Bucketing is the least subtle technique in this module. Choose a key, choose a number of buckets, hash the key, take the remainder, and write the row into that bucket's file. There is no statistics, no pruning and no probability involved: a key's bucket is a pure function of the key.
That determinism is the entire product. It means a reader — or, more usefully, a *planner* — can know without looking that rows with a given key can only be in one specific file, and that the same is true of any other table bucketed with the same function and count.
Note what is different from every other technique here. Partitioning and sorting are arrangements that let a reader *exclude*. Bucketing is an arrangement that lets a planner *pair up* two datasets. It is the only technique in this module aimed at joins rather than at scans, and that focus is why it is both powerful and narrow.
The usual shape combines it with partitioning, because they operate on different axes and neither substitutes for the other: the date bound prunes directories, and the bucket structure inside each surviving directory co-locates the join key.
1-- Partition on the time axis (pruning), bucket on the join axis2-- (co-location). The two are orthogonal and both are needed.3CREATE TABLE fct_orders (4 order_id STRING,5 customer_id STRING,6 order_date DATE,7 amount DECIMAL(12,2)8)9PARTITIONED BY (order_date)10CLUSTERED BY (customer_id) SORTED BY (customer_id) INTO 256 BUCKETS;11 12-- The other side must agree on key, order and count, or the13-- optimisation silently does not apply.14CREATE TABLE dim_customer_activity (15 customer_id STRING,16 segment STRING,17 lifetime_val DECIMAL(12,2)18)19CLUSTERED BY (customer_id) SORTED BY (customer_id) INTO 256 BUCKETS;20 21-- bucket_of(customer_id) = hash(customer_id) mod 25622-- 'c-8842014' -> bucket 91, in BOTH tables, always.23-- so bucket 91 of one can only match bucket 91 of the other.24 25SELECT c.segment, sum(o.amount)26FROM fct_orders o27JOIN dim_customer_activity c USING (customer_id)28WHERE o.order_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-25'29GROUP BY c.segment;30 31-- What to verify in the plan: no exchange / shuffle step before the32-- join. If one is present, the bucketing is not being exploited and33-- the write-time cost is buying nothing.The SORTED BY is not decoration. A sorted bucket lets the per-bucket join be a merge rather than a hash build, and the sort costs nothing extra during a write that is already routing rows. Note also that dim_customer_activity is not partitioned — bucketing does not require partitioning, and partitioning does not require bucketing.
The join that does not move
To see why co-location matters, follow what a distributed engine must do without it. Two large tables, a join on customer_id, and rows for any given customer scattered arbitrarily across the files of both. Before any matching can happen, every row of both tables must be moved to a worker chosen by the join key — which is a full redistribution of both datasets across the network.
That redistribution is the shuffle, and it is usually the most expensive single operation in a batch pipeline. It writes intermediate data, moves it across the network, reads it back, and is the stage where skew and stragglers do their damage (The Shuffle).
With both sides bucketed identically, none of it is necessary. Bucket 91 of the orders table and bucket 91 of the customer table are the only two files that can contain matching rows, so a worker can be given both and produce the join output locally. The redistribution was performed once, at write time, and recorded in the physical layout.
The diagram makes the difference structural rather than rhetorical. Count the arrows crossing between workers in each half.
Write both tables in whatever arrangement is convenient. On every run of the join, the engine redistributes both datasets across workers by the hash of the join key, writes the intermediate data, moves it, and reads it back before any matching begins.
Write both tables bucketed on the join key with the same hash and the same bucket count, sorted within each bucket. Each run of the join reads matching bucket pairs locally and merges them, with no exchange step.
The redistribution is the same work in both cases; what differs is how many times it is paid. Shuffling performs it on every run, over the network, as part of the critical path, in a stage where a skewed key produces a straggler that decides the job's completion time. Bucketing performs it once when the data is written and records the result in the physical layout, so subsequent joins read what is already co-located. That trade is favourable exactly when the tables are joined far more often than they are written — and unfavourable, sometimes badly so, when they are not, because every write now carries the routing cost (The Shuffle).
Everything that has to agree, and what happens when it does not
Bucketing is fragile in a specific and instructive way: the optimisation is all-or-nothing, and the failure mode is silence. Everything that must match — hash function, key columns, column order, bucket count, and how nulls are treated — must match on both sides. If any of it does not, the engine shuffles, the answer is correct, and the write-time cost you are paying buys precisely nothing.
That silence is what makes it a poor default. A partitioning mistake shows up as a pathological object count; a sorting mistake shows up as a scan cost. A bucketing mistake shows up as normal behaviour, because a shuffled join is what would have happened anyway.
The second fragility is that the bucket count is baked into the data. Every other parameter in this module can be changed by rewriting one table; bucket count must be changed across every table in the compatible set simultaneously, and there is no intermediate state in which the optimisation still applies.
None of that makes bucketing wrong. It makes it a technique to reach for deliberately, for a named join, with a verification step in the plan — and to leave alone otherwise. The failure table below is the checklist.
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| The two tables were bucketed with different bucket counts. | The exchange step is still in the plan; runtime and shuffle bytes are unchanged from before bucketing. | A key maps to different buckets under different moduli, so co-location does not hold across the pair. | Align the counts and rewrite both. Assert the bucket specification of both tables in the job that depends on it, so a mismatch fails loudly. |
| Only the fact table was bucketed; the dimension was added later. | Write cost went up; read cost did not go down. | Co-location is a property of a pair, not of a table. | Bucket both, or drop bucketing. One-sided bucketing is strictly worse than none. |
| The join key has one dominant value. | The exchange is gone and the job is still slow; one task in the join stage runs far longer than the rest. | Hashing distributes distinct keys, not rows. A dominant key is one bucket. | Salt the dominant key for processing, or handle it as a separate branch of the job (Salting a Skewed Key, Straggler Tasks). |
| A filter or aggregation is introduced between the scan and the join. | A query that used to avoid the exchange starts shuffling again after a model change. | The optimiser no longer recognises the input as bucket-aligned for that shape. | Capture the plan for the target join on a schedule so this is caught by the platform, not by the bill (Reading EXPLAIN ANALYZE). |
| Volume grows for two years against a fixed bucket count. | Bucket files are far above any sensible size; per-bucket joins spill. | Bucket count is fixed at write time and does not adapt to volume. | Plan a coordinated rewrite of the whole bucketed set. There is no incremental path, which is why the count should be chosen generously at the start. |
| A range predicate is applied to the bucket key. | Every bucket is read for a query that touches a narrow range of keys. | Hashing destroys order by design; range selectivity requires sorting, not bucketing. | Use sort order for range predicates and bucketing only for equality joins. They are complementary (Clustering and Sort Order). |
How to build it
Most important first.
- Start from a named join. If you cannot state which recurring join is the problem and show that its shuffle dominates its cost, bucketing is not the answer and file size or sort order probably is (The Partitioning Decision).
- Bucket both sides on the same key columns, in the same order, with the same bucket count. Anything less and the optimisation silently does not apply, which is worse than not doing it because you now pay the write cost for nothing.
- Combine bucketing with partitioning rather than replacing it, and sort within each bucket on the join key. The usual shape is partition by date, bucket by the join key within the partition, sorted — pruning on the time axis, co-location on the join axis, and a per-bucket join that can be a merge rather than a hash build for a sort that is nearly free during the same write (Partitioning, Clustering and Sort Order).
- Choose the bucket count so that each bucket is a sensible file size at the volume you expect *in a year*, because changing it later is a full rewrite. This is the one layout parameter with a genuinely painful migration (File Size and the Small-Files Problem).
- Check the key's distribution before committing, exactly as for a partition key. A dominant value produces a dominant bucket and the join gets a straggler instead of a shuffle (Straggler Tasks).
- Verify from the plan that the shuffle actually disappeared. Bucketing that is not being exploited looks identical to bucketing that is, from every angle except the plan (Reading EXPLAIN ANALYZE).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- Bucketing guarantees co-location: every row with a given key is in a known bucket, and two tables bucketed identically have matching keys in matching buckets. That is a structural guarantee about physical placement and it is the only thing bucketing promises.
- It does not guarantee that any query will exploit it. Whether the engine recognises the arrangement and skips the exchange is an optimiser decision, dependent on version, statistics and the exact query shape (Query Optimizers).
- It guarantees nothing about bucket sizes. Hashing spreads distinct keys evenly and does nothing about a single key that carries a large share of the rows (Data Skew).
- It guarantees no ordering, by construction. Hashing destroys order, so range predicates on the bucket key gain nothing at all.
- Correctness is unaffected in every case. A mismatched bucket count produces a shuffle and the right answer, never a wrong one.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The structural check is bucket size distribution: rows per bucket within a partition, as a distribution. Even buckets mean the hash is doing its job; a dominant bucket means the key is skewed and the join will be decided by one task.
- The behavioural check is on the read side and matters more: confirm from the query plan that the exchange step is absent for the target join. This is the only evidence that the write-time cost is buying anything (Reading EXPLAIN ANALYZE).
- What both miss is correctness entirely, and they miss a bucket count mismatch between the two sides — which produces a perfectly healthy bucket distribution on each table individually and no benefit whatsoever when they are joined (Data Tests).
- Bucketing constrains writes more tightly than any other technique here: every write must route each row to its bucket, which means a distributed writer performs a shuffle at write time. The cost of the join has been moved from read time to write time, not eliminated (The Shuffle).
- That makes it a poor fit for low-latency streaming appends, which would need to either write tiny files per bucket per micro-batch or buffer until a bucket is worth writing. Both undo something you wanted (Streaming Ingestion).
- The workable pattern is to bucket during a scheduled rewrite of closed partitions and leave the recent tail unbucketed, accepting that joins against fresh data still shuffle (File Compaction).
- The bucket count is effectively part of the table definition and changing it is a full rewrite of every bucketed table that must interoperate. This is the least evolvable decision in the module (Breaking Schema Changes).
- Changing the join key columns has the same cost, and so does changing their order, because the hash is computed over the tuple.
- A change to the engine's hash function between versions would break co-location across the boundary. In practice engines pin this deliberately for exactly this reason, which is worth knowing when moving data between engines that both call the feature bucketing and do not necessarily hash identically.
- Because the arrangement is invisible to consumers, none of this is a schema change from their point of view — which means it will not go through whatever review a schema change goes through (Data Contracts).
- Recoverable by rewriting, with no data at risk, and expensive in exactly the way the last few lessons have been: reading and rewriting the whole table.
- The awkward case is interoperability. If several tables are bucketed to match each other, changing one means changing all of them, and there is no partial state in which the optimisation still applies.
- Abandoning bucketing is safe and cheap in the sense that removing it never breaks a query — it only reinstates the shuffle. That is a useful property: an unwanted bucketing scheme can be dropped by rewriting normally, without coordination.
What can go wrong
- Bucket counts that differ between the two sides, so the shuffle happens anyway and the write-time cost is paid for nothing.
- Only one side bucketed, which is the same failure with an even more plausible cause — the second table was added later by someone who did not know.
- A skewed join key producing one enormous bucket, converting a shuffle problem into a straggler problem (Straggler Tasks).
- A bucket count chosen for today's volume, producing buckets that are far too large two years later, with a full rewrite as the only remedy.
- A query shape the optimiser does not recognise as bucket-compatible — an extra filter, a different join order, an aggregation in between — silently reintroducing the exchange.
- Range predicates on the bucket key that read every bucket, from someone who assumed bucketing was a kind of partitioning (Partitioning).
- "Bucketing is partitioning with a hash." They share the word and nothing else operationally. Partitioning creates a directory per value and enables pruning on that value; bucketing creates a fixed number of files and enables co-location. A range filter prunes partitions and reads every bucket (Partitioning).
- "Bucketing makes queries faster." It makes one shape of join cheaper by eliminating an exchange. It does nothing for a filtered scan, a range query, or an aggregation on a different key, and it makes writes more expensive for all of them.
- "Bucketing fixes skew." It relocates skew into a single bucket. A dominant key value is still a dominant unit of work, and the fix for that is salting, which is a different technique with a different cost (Salting a Skewed Key).
- "We bucketed the fact table, so the join is fast." Both sides must be bucketed compatibly. One-sided bucketing is a write-time cost with no read-time benefit, and it is the most common way this technique is misapplied.
- "We can change the bucket count later." Changing it re-maps every key and requires rewriting every table in the compatible set together. It is the least evolvable decision here and deserves the most thought up front.
Operating it
- Presence or absence of the exchange step in the plan for the target join, captured on a schedule rather than checked once (Reading EXPLAIN ANALYZE).
- Bytes shuffled per job run. This is the number bucketing exists to move, and it should step down when bucketing starts being exploited and step back up when it stops (What Actually Drives Data Platform Cost).
- Rows per bucket as a distribution, per partition, to catch key skew before it becomes a straggler (Percentiles: Which One, and How Many Users Is That?).
- Task duration distribution for the join stage, whose right tail is where a dominant bucket appears (Tail Latency: Why p50 Being Fine Does Not Help).
- File size per bucket over time, which drifts as volume grows and is the early warning that the bucket count needs revisiting (File Size and the Small-Files Problem).
- At 10x volume with a fixed bucket count, buckets get ten times larger. The co-location property still holds and file sizes may leave their band, which is the pressure that eventually forces a rewrite.
- At 100x, the bucket count chosen at design time is almost certainly wrong, and the migration is the full rewrite of every table in the bucketed set. Choosing generously at the start is the cheap insurance.
- Cluster size interacts: a bucket count far below the available parallelism leaves workers idle, and far above it makes many small files. The count is a compromise between file size and parallelism, decided once (Worker Pools Beyond Threads).
- The saving is bytes shuffled, recurring on every run of the target join. It is the only cost driver bucketing moves (The Shuffle).
- The cost is a write-time shuffle to route rows into buckets, paid on every write to the table rather than on every read.
- It therefore pays when the table is joined more often than it is written, and does not pay when the reverse is true — a rule that disqualifies it for most tables and clearly justifies it for a few.
- There is a metadata cost too, though a small one: bucket count is fixed, so it does not grow with the data the way partition count can (Partition Cardinality).
- Bucketing trades write flexibility for read cost on one specific join. It is the least flexible technique in this module and the only one that constrains two tables at once (Every Optimization Buys Something and Sells Something).
- It moves cost from read time to write time. That is a good trade when reads outnumber writes and a bad one otherwise, and unlike compaction the ratio has to be quite favourable to justify the migration risk.
- It commits to a bucket count at the moment you know least about future volume, with a full rewrite as the only correction.
- It offers nothing for filters, ranges or skew, so a platform that adopts it still needs partitioning, sorting and compaction — bucketing is an addition, never a substitution.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- ENGINE-SPECIFICWhether an engine can exploit bucketing to skip an exchange, and under which query shapes, differs substantially and changes between versions. Some engines require identical bucket counts, others accept a multiple; some lose the optimisation if a filter or aggregation appears between the scan and the join. Verify from a plan rather than from the DDL.
- FORMAT-SPECIFICBucketing is metadata about which file holds which hash range, so it depends on the table format or metastore recording it. A directory of Parquet files written with the right hash but no recorded bucket specification is physically bucketed and, to the engine, not bucketed at all.
- SCALE-SPECIFICBelow the size where a broadcast join is viable, bucketing is pure cost — shipping the small side to every worker is simpler and cheaper. The technique only becomes relevant once both sides are too large to broadcast and the join runs often enough that the shuffle is a recurring line item.
- GENERALHash partitioning to co-locate matching keys is the same idea as hash-partitioned exchange inside a query engine and as sharding by hash in a distributed database. What is specific here is that the arrangement is materialised on storage once rather than recomputed per query.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns what a shuffle costs across machines — the intermediate write, the network transfer, the failure and retry behaviour of an all-to-all exchange — and why an operation that redistributes everything is the natural place for a distributed job to fail.