The Partitioning Decision
Five questions that decide a partition key: what the filters carry, how many distinct values, how much data per partition, how often data is appended, and whether the distribution is skewed. Answer them from data, not from intuition.
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.
Given this table and the queries that actually run against it, what should the partition key be — and is the honest answer sometimes that there should not be one?
The engineer who has to make this call once, before the data exists, and live with it for years. And every consumer of the table afterwards, whose query cost was decided by a choice they were not part of and cannot see.
The unit of the decision is the candidate key, evaluated against five properties. The output is not a ranking of keys; it is a defensible statement of why one key was chosen, which is what makes the decision revisitable when the workload changes.
Pick the column that appears in the WHERE clause of the query that is currently slow. It is concrete, it is motivated by a real complaint, and it is right often enough — the query is usually filtered by date — that the method survives well past the point where it stops working.
The currently-slow query is not representative. Optimising for it produces a layout that is worse for the ninety queries nobody complained about (Measure Before You Optimize).
- The currently-slow query is not representative. Optimising for it produces a layout that is worse for the ninety queries nobody complained about (Measure Before You Optimize).
- The chosen column has high cardinality, so the table gains a directory per value and every query on it — including the one that motivated the change — gets slower (Partition Cardinality).
- Two columns both appear in filters, so both are chosen, and the partition count becomes their product while the data per partition is divided by it (Partitioning).
- The volume per partition was never computed, so a granularity that looked sensible produces partitions too small to fill a single file (File Size and the Small-Files Problem).
- The distribution was never examined, so one value holds most of the rows and the job's runtime is decided by one task regardless of the partition count (Data Skew).
- The decision was made when the table was created and never revisited, so a layout chosen for a batch reporting workload is still in place after the table became the input to a real-time model (Data Observability).
What is actually happening
- The decision has exactly five inputs and all of them are measurable before any data is rewritten: which columns the filters carry, how many distinct values each candidate has, how much data lands per partition, how often data is appended, and how evenly the rows are distributed.
- The first question decides whether partitioning can help at all. A key that queries do not filter on produces metadata cost and zero pruning, which is the worst possible outcome and is entirely predictable in advance (Partition Pruning).
- The second and third are the two walls from the cardinality lesson, restated as a sizing calculation: partitions must be numerous enough to prune usefully and few enough to enumerate, and each must hold enough rows to justify a file (Partition Cardinality).
- The fourth question — append frequency — is what connects the partition key to file size. A partition receiving continuous small writes accumulates files at the write rate, so the granularity decision and the compaction schedule are the same conversation (File Compaction).
- The fifth converts the decision from an average into a distribution. Cardinality and skew are independent, and a key can pass every count-based test and still concentrate most of the work in one unit (Hot Keys: When Aggregate Metrics Hide a Saturated Node).
- Underneath all five is a single asymmetry that decides most cases: pruning returns are proportional and diminishing, while metadata cost is absolute and linear. That is why the answer usually converges on time — the only common key whose cardinality grows on a calendar rather than with the business.
Five questions, in order
The order matters. Question one can eliminate a candidate outright — a column nobody filters on cannot be a useful partition key regardless of how well it scores on everything else. Questions two and three decide whether the candidate fits between the two walls. Question four connects granularity to file size. Question five converts an average into a distribution and is the one most often skipped.
Notice that there is no winner in the decision below. Each option carries a when and a cost, and the criteria are the lesson: a good decision is one where you can state which question ruled out the alternatives, not one where a rule was followed.
For most tables this converges quickly on a single time column at a granularity set by volume per period. That is not a failure of the method — it is the method confirming that time is usually right, and telling you exactly why, so that the cases where it is wrong are recognisable when they appear.
Which key, at which granularity — and should there be one at all?
when Always first. Extract predicate columns and their frequency from query history rather than from a conversation.
cost A column that queries do not filter on prunes nothing and still costs a directory per value. This question eliminates more candidates than the other four combined, and answering it from memory is the most common way the whole exercise goes wrong (Partition Pruning).
when For every candidate that survives question one, and for the product if more than one column is proposed.
cost Too many and enumeration cost is on the critical path of every query; too few and pruning eliminates almost nothing. Multi-column schemes multiply, and the intuition is additive (Partition Cardinality).
when Immediately after question two, computed as a distribution rather than a mean.
cost Below roughly one file's worth per partition, the scheme creates objects instead of pruning opportunities and the table pays partition overhead and file overhead at once (File Size and the Small-Files Problem).
when Before choosing granularity, because append rate and granularity together determine file accumulation.
cost A high append rate into fine partitions fragments quickly and makes compaction a hard requirement rather than a maintenance nicety; a coarse partition delays the point at which a period can be declared complete (File Compaction).
when Last, and never skipped. Cardinality and distribution are independent properties.
cost A dominant value produces a unit of work no amount of parallelism divides, so the job's runtime is set by one task. A count-based check passes; a percentile check does not (Data Skew).
when Filters carry a date, cardinality grows with the calendar, volume per period justifies a file, and the distribution is roughly even.
cost Queries that filter only on a non-time column read every partition in range. Serve them with sort order inside the partition rather than with a second partition column (Clustering and Sort Order).
when The table is small enough to scan, or is always read in full, or is a dimension joined rather than filtered.
cost Nothing today, and a rewrite later if it grows. Write it down as a decision so that the next person to look at the table does not "fix" it (Scan Cost).
Getting the answers from data you already have
All five answers exist before the decision is made. Question one lives in query history, which every serious engine records in some form. Questions two, three and five live in the data itself and need three aggregate queries against a sample. Question four is a property of the writer and is known to whoever operates it.
The query-history step is the one worth building once and keeping. A standing report of predicate columns by frequency is useful for far more than partitioning: it tells you which columns deserve sort order, which are candidates for a semantic layer, and which columns exist that nobody has ever filtered on.
A note on interpreting the results: predicate frequency is not the same as predicate cost. A column filtered by one enormous nightly model matters more than one filtered by two hundred trivial dashboard refreshes, so weight the frequency by bytes scanned before drawing conclusions (Scan Cost).
| Question | Where the answer comes from | What disqualifies a candidate |
|---|---|---|
| 1. What do filters carry? | Query history over a representative window, weighted by bytes scanned | The column appears in few queries, or in few expensive ones. Nothing else can rescue this. |
| 2. How many distinct values? | count(DISTINCT key) on a sample; the tuple count if multi-column | Cardinality grows with the business rather than the calendar, or the product exceeds what the metadata layer enumerates comfortably. |
| 3. How much data per partition? | Rows per key as a percentile distribution | The low percentile is too small to justify a file, so the scheme creates objects rather than pruning. |
| 4. How often is data appended? | The writer's commit interval and volume per period | The append rate into the proposed granularity fragments faster than compaction can be scheduled to keep up. |
| 5. How skewed is it? | Max-to-median ratio of rows per key | One value dominates, producing an indivisible unit of work that sets the job's runtime. |
1-- The shape is portable; the history table's name and columns are not.2-- Every engine and warehouse exposes something like this; find yours.3WITH recent AS (4 SELECT query_text, bytes_scanned, total_elapsed_time5 FROM query_history6 WHERE start_time >= CURRENT_DATE - INTERVAL '14' DAY7 AND query_text ILIKE '%from%events%'8 AND query_type = 'SELECT'9)10SELECT11 CASE12 WHEN query_text ILIKE '%event_date%' THEN 'event_date'13 WHEN query_text ILIKE '%event_ts%' THEN 'event_ts'14 WHEN query_text ILIKE '%customer_id%' THEN 'customer_id'15 WHEN query_text ILIKE '%country%' THEN 'country'16 ELSE '(no candidate predicate found)'17 END AS predicate_column,18 count(*) AS queries,19 sum(bytes_scanned) AS total_bytes_scanned,20 sum(total_elapsed_time) AS total_elapsed21FROM recent22GROUP BY 123ORDER BY total_bytes_scanned DESC;24 25-- Read two things from this, not one:26-- queries -> how many people would benefit27-- total_bytes_scanned -> how much a prune on that column would save28-- A column filtered rarely by one enormous model can outrank a column29-- filtered constantly by cheap dashboard refreshes. Weight by cost.30 31-- The '(no candidate predicate found)' bucket is the interesting row:32-- those queries will not prune under ANY key, and if they dominate the33-- bytes, the partitioning decision is not where the saving is.Pattern-matching query text is crude and it is enough to rank candidates, which is all question one needs. Where the engine exposes structured predicate information, use it. What is not acceptable is skipping the step, because the team's belief about what the filters carry and the query history's answer are routinely different.
Deciding, then proving it
A partitioning decision is not finished when the data is written. It is finished when there is evidence that queries prune, and a standing check that will notice when they stop. Without that, the decision is a belief, and the failure mode of the whole module is beliefs that are no longer true and nothing that reports it.
The distinction below is what separates a decision from a guess. A guess produces a partitioned table. A decision produces a partitioned table, a written record of which question ruled out each alternative, and a metric that would fire if the answer changed.
The written record matters more than it sounds. Layout decisions are inherited: the person who has to decide whether to change one, two years later, has no way to tell whether the current scheme was carefully reasoned or copied from a tutorial. Writing down the five answers is what makes it possible to revisit the decision instead of being afraid of it (Dataset Documentation).
| Check | Expresses | Catches | Still misses |
|---|---|---|---|
| Pruning ratio for the top recurring query shapes. | The key is being used by the queries it was chosen for. | A predicate-shape regression, a parameter type change, an optimiser change after an upgrade. | Query shapes outside the sample, and any query that has always been a full scan — a stable bad ratio never regresses. |
| Partition count and its growth rate. | The key's cardinality is bounded by something predictable. | A key that turned out to grow with the business; a granularity that is too fine for the volume. | Skew entirely — count says nothing about distribution — and a count that is stable because the writer stopped (Volume Anomalies). |
| Rows per partition at the tenth and ninety-ninth percentiles, and the max-to-median ratio. | Data is distributed across partitions the way the sizing analysis assumed. | Undersized partitions, a dominant value, a catch-all null bucket accumulating quietly. | Whether anyone filters on the key. Perfect sizing on an unqueried column scores perfectly (Partition Pruning). |
| Files per partition and compaction lag. | The granularity is compatible with the append rate. | Fragmentation from a granularity chosen without reference to question four. | Fragmentation that compaction is currently masking while losing the race — the level is fine and the derivative is not (File Compaction). |
| Rows whose data-derived key does not match their partition value. | The writer routes each row to the partition its data says it belongs in. | Time-zone drift, a parsing bug, a backfill that partitioned by processing time. | Rows whose underlying value is itself wrong — this confirms internal consistency, never truth (The Dimensions of Data Quality). |
The first row is the outcome measure and the rest are its diagnostics. Every one of them is blind to correctness: a table can score perfectly on all five and be missing a day, which is the module's closing reminder that layout is a cost discipline and never an evidence of trust (The Pipeline Succeeded. The Data Is Wrong.).
A dashboard is slow and filters on `customer_id`, so partition by `customer_id`. Rewrite the table, confirm the dashboard is faster, close the ticket.
Extract predicate columns from two weeks of query history weighted by bytes scanned. Compute distinct values, rows-per-partition percentiles and skew ratio for the top candidates. Choose a key and granularity against volume per period and append rate. Write the five answers down. Implement, then measure the pruning ratio for the top recurring query shapes and alert on regression.
The first method optimises for one predicate and is blind to what it costs everything else — most damagingly, it cannot see that a high-cardinality key makes every query on the table slower through enumeration cost, including the dashboard it was meant to fix. The second reaches the same answer when the slow query is representative and a different one when it is not, which is exactly the case where the first method is expensive. The verification step matters independently of the choice: without it, a predicate-shape regression six months later reverses the benefit and nothing reports it (Partition Pruning).
How to build it
Most important first.
- Ask the five questions in order and write down the answers. The written answers are the deliverable, because they are what makes the decision reviewable when the workload changes (Dataset Documentation).
- Derive question one from query history, not from a conversation. Every warehouse and engine records the predicates that ran; a week of them is a far better description of the workload than anyone's recollection (Metadata: Technical, Operational and Business).
- Default to a single time column at the coarsest granularity that still prunes usefully, and treat any other answer as needing to justify itself against that baseline. Express the second filter dimension as sort order rather than as a second partition column, unless it is genuinely low cardinality and appears in nearly every query (Partitioning, Clustering and Sort Order).
- Compute the sizing before committing: partitions created, rows at the low percentile, skew ratio, and the product if more than one column is proposed — and decide explicitly what happens to null and unparseable keys, monitoring that partition by name from the first day (Partition Cardinality, Nullability & Defaults).
- Re-ask the questions on a schedule. Cardinality drifts, volume grows and query patterns move, and none of those changes will announce itself (Data Observability).
- Be willing to answer "no partition key". A table that is small enough to scan, or that is always read in full, gains nothing from partitioning and pays for it (Scan Cost).
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.
- The method guarantees a defensible decision, not an optimal one. There is no optimal partition key, because a key is optimal only with respect to a query mix, and the query mix changes.
- It guarantees nothing about the future. Every one of the five answers can drift, and the decision has no mechanism that detects when it has stopped being right (Partition Cardinality).
- Answering the questions from query history guarantees you are optimising for queries that actually ran, and not for the ones the platform team believes are important — which are frequently different sets (The Metrics Layer).
- No partition key guarantees pruning, because pruning depends on predicate shape at query time and not on the key alone (Partition Pruning).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check that closes the loop is the pruning ratio from real queries: partitions read versus partitions available, sampled from query history after the decision is implemented. This is the only evidence that the chosen key is doing what it was chosen for (Partition Pruning).
- A structural companion: partition count, rows per partition at low and high percentiles, and skew ratio, tracked on a schedule so drift is visible before it is expensive (Pipeline Metrics).
- What both miss is the query that was never written because the table was too expensive to query that way. Layout shapes which questions people ask, and the cost of the questions nobody asks appears in no metric (Data Discovery).
- They also miss correctness entirely. A perfectly partitioned table can be missing a day, and every layout metric will be green (Data Tests).
- Granularity is a freshness decision as much as a cost one: a finer partition can be declared complete sooner, so consumers can be told that a period is closed earlier (Atomic Publish).
- It is also a freshness cost, because finer partitions fragment faster and a growing compaction backlog makes recent data the most expensive to read (File Compaction).
- The fourth question — append frequency — is where those two meet, and it is the question most often skipped. A table appended once a day and a table appended once a minute want different granularities even with identical volume and identical queries (Cost vs Freshness).
- The decision has a shelf life, and nothing in the platform tracks its expiry. Volume grows, cardinality drifts, query patterns move to a different filter column, and the layout that was correct at creation quietly stops being.
- Re-deciding is a rewrite, and its cost grows with the history accumulated under the old scheme. Deciding well early is worth more here than in most areas because the correction gets more expensive over time, not less (What Backfills Break).
- Some table formats allow the partition specification to evolve for new data without rewriting history. That removes the rewrite and leaves the table with two schemes, which is a real improvement and not a free one (Open Table Formats).
- Because the partition column is visible to consumers as a predicate target, changing it is a change to something people wrote SQL against, and it deserves the notice a contract change gets (Data Contracts).
- A wrong decision is recoverable by rewriting, with no data at risk. The cost is compute and coordination, and it grows with the volume written under the wrong scheme (File Compaction).
- Rewrite into a new location, verify the pruning ratio against real query shapes, then swap. Verifying after the swap means discovering a second wrong decision in production (Atomic Publish).
- Keep the old layout until the new one has been observed under real traffic for long enough to see the query mix, not just the shapes you tested (Rolling Back Data).
What can go wrong
- Optimising for the loudest query rather than the workload, producing a layout that is better for one dashboard and worse in aggregate.
- Answering question one from memory rather than from query history, and being confidently wrong about what the filters carry.
- Computing an average rows-per-partition and missing a skew that a percentile would have shown immediately (The Average Was Fine and Users Were Not).
- A multi-column scheme whose product cardinality nobody computed.
- A decision made once, documented nowhere, and inherited by people who cannot tell whether it is still right — so nobody dares change it and nobody can defend it (Dataset Documentation).
- A correct decision implemented without a pruning check, so a predicate-shape regression six months later reverses the benefit invisibly (Partition Pruning).
- "Partition by every commonly-queried column." The partition count is the product of the cardinalities and the data per partition is divided by it, so a scheme with four dimensions costs more to enumerate than it saves in scanning, and makes the queries it was built for slower along with everything else. Express the second and later filters as sort order (Partition Cardinality).
- "The right partition key is a property of the table." It is a property of the table *and the workload*. The same table serving batch reporting and serving per-entity lookups wants different layouts, and if it must serve both, one of them will be poorly served (Every Optimization Buys Something and Sells Something).
- "We know what the queries filter on." Query history routinely disagrees with the team's belief, particularly once BI tools are generating predicates nobody reviews (Stale Dashboards).
- "Average rows per partition looks fine." An average is precisely the statistic a skewed distribution defeats. Look at percentiles and at the max-to-median ratio (The Average Was Fine and Users Were Not).
- "Decide once and move on." Every input to the decision drifts, and none of the drift is reported. Re-asking the questions on a schedule is part of the method, not an optional extra.
- "Not partitioning is never the answer." For a table that is always read in full, or is small enough to scan, partitioning adds metadata and buys nothing. "No partition key" is a legitimate outcome and should be written down as a decision rather than left as an omission.
- Question one should include a governance filter: even when a personal identifier is the dominant predicate, partitioning by it publishes that identifier in paths and listings, and that usually disqualifies it independently of the performance argument (PII in Pipelines).
- Question four should include retention: if data is expired on a time boundary, partitioning on that boundary makes expiry a directory drop with an auditable edge, which is often a stronger argument for a time key than the query performance is (Data Retention).
Operating it
- Predicate columns and their frequency, extracted from query history. This is the input to question one and it should be a standing report rather than a one-off investigation (Metadata: Technical, Operational and Business).
- Partition count and its growth rate, per table, which distinguishes a time-like key from a business-like one at a glance.
- Rows per partition at the tenth, fiftieth and ninety-ninth percentiles, plus the max-to-median ratio (Percentiles: Which One, and How Many Users Is That?).
- Pruning ratio for the top recurring query shapes, which is the outcome measure for the whole decision (Scan Cost).
- Files per partition and compaction lag, which tell you whether the granularity chosen is compatible with the append rate (File Size and the Small-Files Problem).
- At 10x volume, the decision usually holds and granularity may need to become finer — a change that is much less disruptive than a change of key, because the axis is unchanged.
- At 100x, the ceiling questions become the binding ones: how many partitions the metadata layer can enumerate, and whether file discovery has to move from listing to a manifest (Open Table Formats).
- At 10x consumers, the answer to question one becomes less stable, because more consumers means more predicate shapes and the dominant filter is less dominant. That is the point where a metrics or semantic layer starts paying for itself by constraining the shapes that reach the table (The Metrics Layer).
- The decision itself costs analyst time and a few queries against a sample — trivially cheap relative to a rewrite, and routinely skipped anyway.
- A good decision moves bytes scanned, which is usually the platform's largest controllable driver (What Actually Drives Data Platform Cost).
- A bad decision costs on two drivers at once: more bytes scanned by queries that cannot prune, and more requests and planning time from excess metadata (Object Storage).
- The cost of correcting the decision grows with the history written under it, which makes early analysis a genuinely time-sensitive investment (Compute Waste).
- A method with five inputs is slower than picking the column in the slow query, and it will sometimes reach the same answer. It earns its cost on the cases where it does not, which are the expensive ones (Every Optimization Buys Something and Sells Something).
- Optimising for the observed query mix means optimising for the present. A layout tuned tightly to today's workload is more fragile to a workload change than a conservative time-based one.
- Choosing the conservative answer — partition by date, sort by the rest — leaves some performance unclaimed on specific queries in exchange for a layout that is hard to get badly wrong. That is usually the right trade and it should be a choice rather than a default.
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.
- GENERALThe five questions apply to any system that partitions physically, including relational databases with declarative partitioning, where the same cardinality and skew analysis governs the choice and only the enumeration cost differs.
- SCALE-SPECIFICBelow the size where a full scan is acceptable, the correct answer to all five questions is "do not partition", and the analysis is wasted effort. The method becomes worth its cost at the point where a full scan stops fitting in the window a consumer will tolerate.
- WAREHOUSE-SPECIFICWarehouses that manage physical layout automatically remove questions two and three from your hands — the system decides the unit — while leaving question one and question five entirely yours, expressed as a clustering key. The decision does not disappear; the vocabulary and the failure modes change.
- TOOL-SPECIFICExtracting predicate columns from query history depends on the engine or warehouse exposing a queryable history of executed statements. Where it does not, the substitute is instrumenting the BI and transformation layers, which is worse coverage and better than asking people what they filter on.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns treating a layout change as a migration with a rollout, a verification step and a rollback position, rather than as a one-off rewrite that happens on a quiet afternoon.