Partition pruning
A Socratic walk-through of partition pruning — reasoned out one step at a time, not lectured.
The question we started with
THE QUESTION #Why does how you arrange stored data matter more than how fast the machine reading it is?
Two teams run the same query against the same five-terabyte table: give me yesterday's transactions. One team's query returns in two seconds on modest hardware. The other's takes eleven minutes on a cluster three times the size.
Before reaching for an explanation about tuning or caching, consider what the fast query might have done differently at a more basic level. Not read faster. Read less. And the amount a query is able to skip is not decided when the query runs — it was decided months earlier, by whoever chose how the files on disk would be arranged.
Reasoning it through
REASONING #Start with the crudest possible layout: every row written into files in arrival order, no organisation at all. Now ask for yesterday's rows. What must the engine do?
It has no way to know which files could contain a yesterday row, so it must open all of them and test each row. Five terabytes read to produce perhaps two gigabytes of answer. Notice that this is not a slow implementation of the query — it is the only implementation available, given that layout. The engine is not being stupid. It is being blind.
Now change one thing. Write the rows into directories named by date, so all of yesterday's rows sit in date=2026-07-25/ and nothing else does. Ask the same question. The engine can now reason: the filter is on date, the layout is keyed by date, therefore every file outside that directory is provably irrelevant. It reads one directory and skips the rest without opening a single byte of them.
That inference — eliminating whole regions of storage before reading, on the strength of a filter matching the layout — is partition pruning. And I want to draw attention to the word provably. The engine is not guessing or sampling. If a partition is defined by date = 2026-07-25, then no row inside it can have another date, so exclusion is a logical certainty. That is why the saving is total rather than partial.
Here is the reflective question that makes the whole topic click. What happens if you ask for all rows where the customer's country is France, and the table is partitioned by date?
Nothing helps. The layout carries no information about country, so every partition might contain French customers, so every partition must be read. The five terabytes come back. Which tells us something important: pruning is not a property of the data or of the engine. It is a property of the match between how the data is laid out and how it is asked for. A perfect layout for one access pattern is worthless for another.
Does that mean you should partition by everything? Follow the consequence. Partitioning by date, country, and product creates a directory for every combination, most of which hold a trivial number of rows — and each still costs at least one file, plus a metadata entry. You have traded one problem for the small files problem, and the planning step now spends its time enumerating partitions rather than reading data. So the real design question is not "how finely can I partition?" but "which one or two columns do nearly all my queries filter on?"
There is a second mechanism worth knowing, because people often conflate the two. Columnar and table formats also record min and max statistics per file, and per row group inside a file. A query filtering on amount greater than a thousand can skip any row group whose recorded maximum is nine hundred. This is data skipping, and it works on columns you did not partition by. But it is weaker in an important way: it prunes only when the values happen to be clustered, because a file containing a wide spread of values has a wide min-max range and cannot be excluded. Partitioning guarantees clustering by construction; statistics merely observe whatever clustering exists. That is why sorting data on write matters — it is what makes the statistics sharp enough to be useful.
One honest caveat. Pruning depends on the planner being able to see the relationship between your filter and the partition column. Wrap the column in a function, or compare it against a mismatched type, and many engines lose the connection and fall back to reading everything. Some systems handle this well; some do not, and it varies by engine and version. The practical habit is to check the query plan for the number of partitions or files selected, rather than assuming the pruning happened.
The analogy
THE ANALOGY #Think of a filing room. In one version, every document goes into the next available box in arrival order. To find last March's invoices you open every box. In the other, boxes are labelled by month. You walk the room, read labels, and carry out one box.
The labels contain no documents. They add nothing to what is stored. Yet they are the difference between an afternoon and a minute — and a faster clerk does not close that gap, because the slow version's cost is in the opening, not the reading.
a clerk who reads a label and takes the wrong box has merely erred, whereas a query engine that cannot connect your filter to the labelling scheme silently reads every box and still returns the correct answer — so the failure shows up as cost and latency, never as a wrong result, which is exactly why it can persist unnoticed for years.
Clarifying the model
THE MODEL #Three refinements.
First, on the framing in the question itself. Hardware speed and layout are not really competitors — they compose. But they compose multiplicatively against very different factors: faster hardware improves the constant, while pruning changes how much data enters the calculation at all. Reading 0.1 percent of a table is a hundredfold saving that no reasonable hardware upgrade matches, which is why layout tends to dominate.
Second, a misconception: that partitioning is about organisation for humans, or about parallelism. It is neither. Its purpose is to let the planner prove that regions can be skipped. Any partitioning scheme your queries do not filter on gives you the costs without the benefit.
Third, pruning happens in stages, and knowing which stage failed is diagnostic. Static pruning uses constants in the query text. Dynamic pruning derives the filter at runtime — for instance, from the small side of a join — and prunes the large side with values it discovers mid-query. If a query prunes well alone but poorly inside a join, dynamic pruning is where to look.
A picture of it
THE PICTURE #How to readFollow the width of each ribbon, in gigabytes, from the whole table on the left. The first split is partition pruning and it is the largest by far — that data is never opened. The second split is file and row-group skipping using min-max statistics, which only bites where values are clustered. Only the thin ribbon reaching the far right is data the query actually needed; everything left behind at each stage is work the layout allowed you to avoid.
What became clearer
WHAT CLEARED #A query's cost is set less by how fast bytes can be moved than by how many bytes can be proved unnecessary before anything moves. That proof is only available when the shape of the storage matches the shape of the question — which makes layout a decision about future queries, taken long before those queries are written.
Where to go next
ONWARD #- Partition evolution: changing a table's partitioning without rewriting its history.
- Clustering, Z-ordering, and sort order — making min-max statistics sharp enough to prune.
- Dynamic partition pruning in joins, and how to read it in a query plan.
Key terms
TERMS #| Term | What it means |
|---|---|
| Partition pruning | excluding whole partitions from a scan because the query's filter provably cannot match them. |
| Data skipping | excluding files or row groups using recorded min-max statistics rather than partition structure. |
| Row group | a horizontal block of rows within a columnar file, the smallest unit statistics usually cover. |
Every term the collection defines is gathered in the glossary.