Data partitioning for faster queries works by splitting a table into separate chunks based on a column, usually a date, so the engine can skip every chunk that a query's filter rules out. Clustering goes one level finer by sorting data within those chunks so file-level statistics can skip even more. Used together, they reduce the bytes scanned per query, which improves both speed and, on usage-billed warehouses, cost.
Why are warehouse queries slow in the first place?
Analytical engines are fast at scanning, but scanning is still the dominant cost. A query that filters on one day of data from a table holding several years has to read the whole table unless the engine can prove most of it is irrelevant. Indexes in the OLTP sense, as covered in database indexes, are rare in columnar warehouses and lakes. Instead, they rely on data layout: how rows are grouped into files and blocks, and what metadata is kept about each group.
The goal of partitioning and clustering is simple: arrange data so the filters people actually use line up with physical boundaries the engine can skip.
What is partition pruning?
A partitioned table stores each partition value separately. In a data lake that often looks like directories:
s3://lake/events/event_date=2026-09-17/part-0001.parquet
s3://lake/events/event_date=2026-09-18/part-0001.parquet
s3://lake/events/event_date=2026-09-19/part-0001.parquet
Managed warehouses hide the directories but keep the same idea internally. When a query includes a filter on the partition column, the planner reads the table's metadata, eliminates non-matching partitions, and never opens their files. That elimination is partition pruning.
-- Pruned: only the 2026-09-18 partition is read
SELECT user_id, COUNT(*) AS events
FROM analytics.events
WHERE event_date = DATE '2026-09-18'
GROUP BY user_id;
-- Often NOT pruned: wrapping the partition column in a function
-- can hide the filter from the planner, depending on the engine
SELECT user_id, COUNT(*) AS events
FROM analytics.events
WHERE CAST(event_date AS VARCHAR) LIKE '2026-09-18%'
GROUP BY user_id;
The second query asks for the same data but may scan every partition. Filter on the partition column directly, with literal values or simple ranges, and check the query plan or the bytes-scanned metric to confirm pruning happened. The general idea mirrors horizontal partitioning in operational databases, applied to analytics storage.
How do you choose a partition key?
A good partition key has three properties:
- It appears in most query filters. If queries rarely filter on it, partitioning on it buys nothing.
- It has moderate cardinality. Enough distinct values to split data meaningfully, but not so many that each partition is tiny.
- It is stable and known at write time. Events arriving late should still land in a predictable partition.
For most event and fact tables, the answer is a date derived from the event timestamp. Date works because nearly every analytical query has a time window, and one day of data is usually a sensible unit for loading, reprocessing, and retention.
| Candidate key | Typical fit | Risk |
|---|---|---|
| Event date | Excellent for time-windowed queries | Too fine if daily volume is tiny |
| Event hour | High-volume streams with hour-level queries | Explodes partition count quickly |
| Region or country | Queries that always filter by region | Skewed: one region may dwarf others |
| Customer or user ID | Rarely a good partition key | Very high cardinality, many tiny partitions |
| Status or type | Low-cardinality filters | Few partitions, little pruning benefit |
When a column has high cardinality, such as user_id, it is almost always a better candidate for clustering than for partitioning.
What is the small files problem?
Over-partitioning is the most common mistake. Every partition holds at least one file, and each file carries overhead: metadata to track, a request to open it, and a footer to read. When partitions are tiny, a query spends more time listing and opening files than reading data.
Small files come from a few sources:
- Partition keys with too many distinct values, or combinations of keys like date plus customer.
- Streaming jobs that write a file per micro-batch per partition.
- Frequent small appends or updates without maintenance.
The fixes are coarser partitions (daily instead of hourly, or monthly for small tables), buffering writes into larger batches, and running regular compaction that rewrites many small files into fewer large ones. Open table formats such as Apache Iceberg and Delta Lake provide compaction operations for this. As a rough guide, target files in the range of hundreds of megabytes rather than kilobytes; the right size depends on your engine, so treat that as a starting point, not a rule.
If a table is small overall, it may not need partitioning at all. A single well-sorted set of files can be faster than a hundred tiny partitions.
What is clustering?
Partitioning decides which chunk a row belongs to. Clustering decides the order of rows within a chunk. Columnar files keep min and max statistics for each column in each block, as explained in Parquet vs Avro vs ORC vs CSV. If data is sorted by customer_id, each block covers a narrow range of customers, so a filter on one customer can skip almost every block. If the data is unsorted, every block contains a wide spread of IDs and nothing is skipped.
Different platforms name this differently. Some call them clustering keys, some sort keys, and some apply it through an ORDER BY during writes or an explicit optimize command. The mechanism underneath is the same: co-locate similar values so block-level statistics become selective.
Good clustering columns are ones that are frequently filtered or joined on, have higher cardinality than your partition key, and are not already covered by partitioning. A common layout is to partition by event_date and cluster by customer_id or account_id.
How does Z-ordering work conceptually?
Sorting by one column makes that column highly selective, but a second sort column only helps within ties of the first. If queries filter on either customer_id or product_id, a plain sort favors one and neglects the other.
Z-ordering addresses this by interleaving the bits of several column values into a single sort key, following a space-filling curve. Rows that are close in all of the chosen dimensions end up close together on disk. Neither column is perfectly sorted, but both gain useful skipping. It works best with two to four columns; beyond that, each column's benefit gets diluted. Some platforms offer similar multi-dimensional techniques under names like Hilbert curves or automatic clustering.
Partitioning vs clustering: which should you use?
| Aspect | Partitioning | Clustering |
|---|---|---|
| Granularity | Coarse, separate chunks | Fine, order within chunks |
| Best column type | Low to moderate cardinality, usually date | Higher cardinality filters and join keys |
| Skipping mechanism | Metadata eliminates whole partitions | Block min/max statistics skip blocks |
| Also used for | Retention, reloads, overwrite by partition | Faster joins and point lookups |
| Main risk | Small files from over-partitioning | Maintenance cost as new data arrives unsorted |
In most cases, use both: partition by date, cluster by the one or two columns that dominate your filters.
A practical checklist
- Look at real query history and list the most common filter and join columns.
- Partition by the column almost every query filters on, typically a date.
- Cluster by one or two high-cardinality columns that queries filter on next.
- Keep files reasonably large and schedule compaction.
- Verify with query plans and bytes-scanned metrics, before and after.
These choices sit alongside modeling decisions such as star schema vs snowflake schema, since fact tables are usually the ones that benefit most.
Key takeaways
- Partition pruning skips whole chunks of a table when queries filter directly on the partition column.
- Choose a partition key that appears in most filters and has moderate cardinality; a date is usually right.
- Over-partitioning creates the small files problem; prefer coarser partitions and regular compaction.
- Clustering sorts data within partitions so block statistics can skip more data on high-cardinality filters.
- Z-ordering balances skipping across several filter columns at the cost of perfect order on any one.
Frequently asked questions
Does partitioning always make queries faster?
No. It only helps queries that filter on the partition column, and too many small partitions can make every query slower. Small tables often perform better unpartitioned but well sorted.
Can I partition by more than one column?
Yes, but each added column multiplies the number of partitions. Use multi-level partitioning only when both columns are in nearly every filter and the resulting partitions stay reasonably large.
Why is my query still scanning the whole table?
The most common causes are a filter that does not reference the partition column, a function wrapped around the partition column, or a join where the filter is applied only after the scan. Check the query plan to see which partitions were read.
How often should I recluster or compact a table?
It depends on how quickly unsorted or small files accumulate. Tables with frequent streaming writes may need daily maintenance, while tables loaded in large batches may rarely need it. Monitor file counts and scan metrics to decide.