LayoutSCALE-SPECIFICENGINE-SPECIFICWAREHOUSE-SPECIFICGENERAL

Partition Cardinality

A partition key with too many distinct values produces more metadata than data. The target is enough rows per partition to justify a file, and few enough partitions to list.

What actually happens How to build it Can I trust it?

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.

The question

How many partitions is too many — and how would you know before you have written four years of them?

Who needs this

Everyone who queries the table, including the people the bad key was supposed to help. This is the rare layout mistake that makes *every* query slower, including the one it was introduced to optimise, which is what makes it worth a lesson of its own.

What one row is

The unit is the partition, priced as an object. Every distinct value of the partition key costs at minimum one directory, one file and one metadata entry, forever, regardless of how many rows are inside it. Cardinality is the question of how many of those you are creating.

The obvious build

Partition by whatever appears in the WHERE clause. Queries filter by user_id, so partition by user_id; they also filter by country and event_type, so partition by those too. Each choice is locally justified by a real query, and the reasoning is the same reasoning that works for indexes.

Why it breaks

The table is partitioned by user_id with millions of users. The write job produces a directory per user, each holding a handful of rows in a file far below any sensible size, and the object count now scales with the user base rather than with the data (File Size and the Small-Files Problem).

How it breaks with real data

The table is partitioned by user_id with millions of users. The write job produces a directory per user, each holding a handful of rows in a file far below any sensible size, and the object count now scales with the user base rather than with the data (File Size and the Small-Files Problem).
Query planning gets slower for every query, including the per-user one, because the planner must consider a partition list with millions of entries before it can eliminate any of them (Partition Pruning).
A directory-based table has to be listed to discover partitions. Listing millions of prefixes is a paginated sequence of API calls that happens before a single byte of data is read (Direct Uploads and Signed Authorization).
The write job itself becomes the bottleneck: routing rows to millions of destinations means holding millions of open writers or performing an enormous shuffle, and either way the job that used to finish comfortably now does not (The Shuffle).
Partitioning on two or three dimensions multiplies rather than adds. Date times country times event type produces a partition count that is the product of the cardinalities, and the data per partition is divided by that same product.
A key with one dominant value — a country where most customers live, a NULL bucket, a default tenant — produces one enormous partition among many tiny ones, so the job's runtime is decided by a single task (Data Skew, Straggler Tasks).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

Pruning is a proportion and metadata is an absolute. Doubling partitions can at best halve what a selective query reads; it always doubles what the planner must consider and what the metadata layer must hold. Past some point the second effect dominates and additional partitions are strictly harmful (Cardinality: The Label That Took Down Monitoring).
The floor is set by file size. A partition holding fewer rows than a reasonable file cannot produce a reasonable file, so at that point partitioning and fragmentation are the same decision viewed from two directions (File Size and the Small-Files Problem).
The ceiling is set by whatever must enumerate partitions. A directory-based table pays a listing proportional to partition count; a manifest-based one pays a metadata read proportional to it; a relational database pays planning time proportional to it. Every implementation has this cost and they differ only in the constant (Partitioning Internals: Key → Partition Function → Node).
Multi-column partitioning is a Cartesian product, and this is the part people consistently underestimate. Adding a column with fifty distinct values does not add fifty partitions; it multiplies the existing count by up to fifty, and divides the data per partition by the same factor.
Skew is a third axis, orthogonal to count. A key can have a perfectly acceptable number of distinct values and still be a bad key because the distribution is concentrated — the partition count says nothing about whether the rows are spread across it (Hot Keys: When Aggregate Metrics Hide a Saturated Node).
Time is the key that works because its cardinality grows with the calendar rather than with the business, and its distribution is roughly uniform. That is not a coincidence or a convention; it is the reason it is the default answer (Partitioning).

Two walls, and the gap between them

Partition cardinality has a floor and a ceiling, and the useful range is between them. The floor is file size: a partition must hold enough rows to produce at least one file worth writing, or the table pays partition overhead and file overhead simultaneously for data that would have fit in a corner of a larger file.

The ceiling is enumeration: something has to know which partitions exist, and that something pays a cost proportional to how many there are — a paginated listing, a metadata read, a planning pass. That cost is paid by every query, including the ones that end up reading one partition.

Everything else in this lesson follows from those two walls. Time works as a key because its cardinality grows on a calendar, which is slow and predictable, and because its distribution across periods is roughly even. A business identifier fails because its cardinality grows with the business, which is unbounded, and because its distribution is usually concentrated.

The comparison below is the canonical mistake and the canonical fix. Read the because: the fix does not abandon the per-customer query, it serves it through a different mechanism whose cost does not scale with the number of customers.

The same query under two partition schemes

SELECT sum(amount) FROM events WHERE customer_id = 'c-8842' AND event_date BETWEEN '2026-08-01' AND '2026-08-25'

A) events/customer_id=c-8842/every event for one customer, across all history · 1 file · read
A) events/customer_id=c-0001/ … c-9999999/a handful of events each · 1 file · skipped
B) events/event_date=2026-08-01/ … 2026-08-25/one day of events per partition, sorted by customer_id · 3 files · read
B) events/event_date=2023-01-01/ … 2026-07-31/one day of events per partition · 3 files · skipped

2 of 4 shown paths are read.

Scheme A reads less data and costs more, because the enumeration it forces on every query is unbounded in the number of customers. Scheme B reads more data and its metadata cost is fixed by the number of days. The trade is a bounded read cost against an unbounded metadata cost, and that is why it is not close.

Serving a per-customer query two ways

Partition by customer_id

Partition `events` by `customer_id` because that is what the important query filters on. Every customer gets a directory; a query for one customer touches one directory and reads a very small amount of data.

Partition by date, sort by customer_id

Partition `events` by `event_date`, and sort each partition by `customer_id` during the write or during compaction. A per-customer query bounded by a date range prunes to those days, then skips files and row groups whose recorded `customer_id` range excludes the value.

The first arrangement costs one directory, one file and one metadata entry per customer, so object count scales with the customer base while data volume does not. Planning, listing and write-time routing all grow with that count and are paid by every query on the table — including the per-customer one, which now waits for a partition list with millions of entries to be enumerated before it can eliminate anything. The second arrangement holds partition count at one per day forever and buys customer selectivity through file statistics, which cost no extra objects and no extra metadata. The per-customer query does read more than the directory-per-customer version would have, and it reads a small fraction of a few partitions rather than a full scan — a bounded cost traded for an unbounded one (Clustering and Sort Order).

Measure the key before you commit to it

SIMPLIFIEDThe SQL uses a generic approx_percentile; engines differ in the exact function name and in whether an exact percentile is affordable at this size. The method — distribution rather than mean, product rather than sum — is what transfers, and the function name is the part to look up for your engine.

Every one of these mistakes is preventable with three queries run against a sample before the table is rewritten. Distinct values of the candidate key; rows per distinct value as a distribution; and the product, if more than one column is being considered. None of them is difficult and none of them is normally run.

Read the results against the two walls. If rows per partition at the low percentile is too small to fill a file, the key is too fine. If distinct values is large enough that enumerating them is a visible cost on your engine, the key is too fine in the other direction. If the ratio of the largest to the median is large, the key is skewed and its count was never the problem.

Note the last query especially. Multi-column partitioning is the one place where the arithmetic surprises people, because the intuition is additive and the reality is multiplicative — and the query below makes that concrete before a single directory is created.

Candidate key	Cardinality growth	Distribution	Verdict
`event_date`	With the calendar — one per day, forever	Roughly even, with weekly and seasonal variation	The default. Cardinality is predictable and bounded by time rather than by the business.
`event_month`	With the calendar, twelve times slower	Even, and partitions are twelve times larger	Correct when daily partitions would be too small to fill a file. Coarser pruning, fewer objects.
`event_hour`	With the calendar, twenty-four times faster	Even, and partitions are much smaller	Only when the volume per hour justifies a file. Otherwise it converts a scan problem into a metadata problem (File Size and the Small-Files Problem).
`country`	Bounded, low	Heavily concentrated in a few values	A plausible second dimension, and it multiplies the count and introduces skew. Usually better as a sort column.
`event_type`	Bounded, low, and grows when the product does	Usually very concentrated in one or two types	Rarely worth a partition. Low cardinality is necessary and not sufficient — the distribution disqualifies it.
`customer_id`	With the customer base — unbounded	Long-tailed: most customers have very few events	The canonical mistake. Serve it with sort order inside a time partition (Clustering and Sort Order).
`session_id`	Faster than the data does	One session per partition, by definition	Never. The partition count exceeds any plausible row count per partition.
`tenant_id`, small fixed set	Bounded by contracts, changes rarely	Depends entirely on tenant size, often extremely skewed	Legitimate when partition boundaries are also the access and deletion boundary — an explicit governance decision, not a performance one (Data Access Control).

Three queries to run before choosing a partition key

1-- 1. How many partitions would this key create?
2SELECT count(DISTINCT candidate_key) AS partitions_created
3FROM   events_sample;
4 
5-- 2. How much data lands in each? Read the distribution, never the mean:
6--    a mean is exactly the statistic a skewed key defeats.
7WITH per_partition AS (
8  SELECT candidate_key, count(*) AS rows_in_partition
9  FROM   events_sample
10  GROUP BY candidate_key
11)
12SELECT count(*)                                              AS partitions,
13       min(rows_in_partition)                                AS smallest,
14       approx_percentile(rows_in_partition, 0.10)            AS p10,
15       approx_percentile(rows_in_partition, 0.50)            AS median,
16       approx_percentile(rows_in_partition, 0.99)            AS p99,
17       max(rows_in_partition)                                AS largest,
18       max(rows_in_partition)
19         / nullif(approx_percentile(rows_in_partition, 0.50), 0) AS skew_ratio
20FROM   per_partition;
21 
22-- 3. Multi-column schemes multiply. Compute the product before writing it,
23--    not after the write job stops finishing.
24SELECT count(DISTINCT event_date)                       AS dates,
25       count(DISTINCT country)                          AS countries,
26       count(DISTINCT event_type)                       AS types,
27       count(DISTINCT (event_date, country, event_type)) AS actual_partitions
28FROM   events_sample;
29 
30-- 4. And the one everybody forgets: where do the unusable keys go?
31SELECT count(*) AS rows_with_no_usable_partition_key
32FROM   events_sample
33WHERE  candidate_key IS NULL OR candidate_key = '';

Query 3 returns two numbers worth comparing: the product of the individual counts is the worst case, and actual_partitions is what the data really produces — usually smaller, because the dimensions correlate, and usually still far larger than anyone guessed. Query 4 sizes the catch-all partition before it exists.

Skew: the failure that a healthy partition count hides

Cardinality and skew are independent properties, and a platform that monitors only the first will miss the second entirely. A key can produce a completely reasonable number of partitions and still put most of the rows in one of them.

This matters because a partition is usually the unit of work distribution. If one partition holds a large share of the data, one task holds a large share of the work, and the job finishes when that task finishes regardless of how many workers are idle waiting for it. Adding capacity does not help; the work cannot be divided (Why Eight Cores Give You Four and a Half).

The usual sources are boringly predictable. A default value that most rows carry. A NULL bucket collecting everything that failed to parse. A single dominant tenant, country or product. A backfill that wrote a year of history into one partition because the partition value was derived from processing time rather than event time.

The remedies split cleanly. If the skew is in the data and the query pattern tolerates it, sub-partition or salt the key so the heavy value is spread across several units (Salting a Skewed Key). If the skew is an artefact — nulls, defaults, a mis-derived value — fix the writer, because spreading a bug across more partitions is not a fix. And if one partition is genuinely and permanently larger, accept it and size the work by bytes rather than by partition count.

Cardinality and skew failures, and what each looks like from outside

Trigger	Symptom	Cause	Response
A high-cardinality business key is chosen as the partition column.	Every query on the table gets slower, including the one the key was chosen for. Data volume is unchanged.	Object and metadata count now scale with the business rather than with the calendar; enumeration is on the critical path of every plan.	Rewrite to a time key with the business key as sort order. Measure distinct values before committing to any replacement.
A third partition dimension is added to help one dashboard.	The write job stops finishing inside its window; file count multiplies; total size is flat.	Partition count is the product of the dimensions, and data per partition is divided by that product.	Remove the dimension and express it as sort order. Compute the product on a sample before any future addition (The Partitioning Decision).
Rows arrive with a null or empty partition key.	A catch-all partition grows steadily and appears in no dashboard that groups by valid values.	Most engines route unparseable keys to a single default partition, which is both a skew source and a quality signal nobody reads.	Alert on the catch-all partition's size explicitly, by name, and treat growth as an upstream parsing incident (Data Tests).
One tenant, country or product dominates the dataset.	Job runtime is decided by one task; adding workers changes nothing.	The heavy partition is a single unit of work and cannot be divided by adding capacity.	Salt the heavy key into sub-partitions for processing, or split the work by bytes rather than by partition (Salting a Skewed Key, Straggler Tasks).
A backfill derives the partition value from processing time.	One partition holds a year of history; every per-day metric for that year is wrong.	The partition value no longer describes the data inside it.	Rewrite the affected range deriving the key from event time, and add the partition-value-matches-data check permanently (Validating a Backfill Before You Publish).

How to build it

Most important first.

Compute the numbers before writing the data. Distinct values of the candidate key, rows per distinct value, and the distribution of those rows — three queries against a sample, run before a rewrite rather than after (The Partitioning Decision).
Target enough data per partition to justify at least one file at your chosen size, and few enough partitions that listing or enumerating them is not on the critical path. State both walls explicitly for your platform; do not adopt a number from an article (File Size and the Small-Files Problem).
Use one partition column, and express every further axis of selectivity through sort order rather than through a second dimension. Sorting by customer_id inside a date partition gives most of the skipping with none of the object explosion; a second partition column is justified only when it is genuinely low cardinality and appears in nearly every query (Clustering and Sort Order).
Coarsen rather than abandon when a key is nearly right. Partitioning by month instead of day, or by a bucketed range instead of a raw value, keeps most of the pruning and divides the partition count (Bucketing).
Handle nulls and defaults explicitly, because a catch-all partition is a skew generator that nobody chose and nobody monitors.
Alert on partition count growth rate, not on an absolute number. The absolute threshold depends on the engine; the shape of the growth curve tells you whether the key is time-like or business-like regardless of engine.

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.

Cardinality guarantees nothing about correctness. A table partitioned by user_id returns exactly the same rows as one partitioned by date; the bad key costs money and time and never costs accuracy.
A low partition count does not guarantee good pruning — a table partitioned by a column nobody filters on has few partitions and prunes nothing.
A high partition count does not guarantee bad performance in every engine. Systems that keep partition metadata in a compact manifest tolerate counts that would make a directory-listing engine unusable, which is why this threshold is not portable (Open Table Formats).
Nothing guarantees an even distribution across partitions. The key's cardinality and the key's skew are independent properties and must be measured separately (Data Skew).

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 would catch this

The check is a distribution audit: partition count per table, rows per partition as a distribution rather than an average, and the ratio of the largest partition to the median. Run it on a schedule and alert on the growth rate of the first and the value of the third.
A specific check for the pathology: count partitions whose row count is below a stated floor. A table where most partitions hold a handful of rows is over-partitioned regardless of what the total looks like (Pipeline Metrics).
What both miss is whether the key is *useful*. A perfectly sized, perfectly even partition scheme on a column no query filters on scores well on every structural metric and delivers nothing — only the pruning ratio from real queries shows that (Partition Pruning).

Freshness

Finer partitioning can improve the freshness a consumer perceives, because a smaller unit can be published sooner — an hourly partition is complete an hour after its period ends, a daily one a day after (Atomic Publish).
It also worsens it in the aggregate, because more partitions means more files means more compaction backlog, and a compactor that cannot keep up makes recent data the slowest to read (File Compaction).
The honest framing is that granularity is a freshness-versus-metadata trade, and it should be argued with the volume per period in hand rather than in the abstract (Cost vs Freshness).

When the schema or meaning changes

Cardinality is not static. A key that had a few hundred distinct values at launch can have a few hundred thousand two years later, and nothing in the platform will announce that the partition scheme has drifted out of its band (Data Observability).
A schema change that makes a partition column more granular — adding a sub-type, splitting a category — multiplies partition count without changing a byte of data.
Correcting cardinality means rewriting history, and until it is rewritten the table is physically two schemes at once, with queries behaving differently depending on the period they touch (What Backfills Break).

How to re-run this safely

Recoverable by rewriting into a coarser scheme, and the rewrite is the expensive part rather than the risky part — no data is lost and the operation is byte-preserving at the row level (File Compaction).
Rewrite into a new location and swap, and keep the old layout until the new one has been verified. A rewrite of millions of tiny objects is itself a heavy read operation and deserves to be run once (Atomic Publish).
The reads that hurt during recovery are the listings. If the table has become large enough that enumerating it is the problem, the recovery job hits the same wall as the queries do, and it may need to proceed range by range rather than as one pass.

What can go wrong

Failure modes

Partition count that grows with the business rather than with the calendar, which is the signature of a key that will eventually be wrong regardless of how it looks today.
A multi-column scheme whose product cardinality nobody computed, discovered when the write job stops finishing.
A catch-all partition for nulls and defaults that becomes the largest partition in the table and is absent from every dashboard that groups by valid values.
A "fix" that coarsens the key without rewriting history, leaving the table permanently in two schemes.
A monitor with an absolute partition-count threshold, ported from a different engine, that either never fires or fires constantly (Alert Fatigue: The Page Nobody Reads).
Skew inside an acceptable partition count — the metric everyone tracks looks fine and one task still decides the runtime (Straggler Tasks).

Misreads

"Partition by every commonly-queried column." The product of the cardinalities is the partition count and the data per partition is divided by that same product. Four dimensions produce a directory tree that costs more to enumerate than the data costs to read, and the query that motivated the fourth dimension gets slower along with everything else (The Partitioning Decision).
"High cardinality means better pruning." It means finer pruning and more metadata. Selectivity beyond the point where a query already reads a small fraction buys very little and costs linearly (Cardinality: The Label That Took Down Monitoring).
"Our partition count is fine, so the key is fine." Count and skew are independent. A well-sized scheme with one dominant value has a healthy count and a single task that decides every job's runtime (Hot Keys: When Aggregate Metrics Hide a Saturated Node).
"We will just coarsen it later." Coarsening means rewriting history. Later means rewriting more history, on a table that has by then become expensive to enumerate.
"This threshold worked at my last company." Partition-count limits depend on how the engine stores and reads partition metadata, and differ by orders of magnitude between a directory-listing engine and a manifest-based table format. Port the method, not the number (Benchmark Fallacies: Confident Numbers That Are Wrong).

Privacy, retention and access

A high-cardinality partition key is usually an identifier, and identifiers in paths are the leakiest place to put them: listing a prefix enumerates them without granting access to any object (PII in Pipelines).
The one legitimate case for identifier partitioning is tenancy, where partition boundaries are used as an access-control and deletion boundary — and it should be an explicit decision made for that reason, with the metadata cost accepted knowingly (Data Access Control).

Operating it

How you see it in production

Partition count per table and its growth rate. The growth rate is the diagnostic: partitions per day should be a small constant for a time-partitioned table and is not for a business-keyed one.
Rows per partition as a percentile distribution — median, low percentile, high percentile — never as a mean, because a mean is exactly the statistic a skewed distribution defeats (Percentiles: Which One, and How Many Users Is That?).
The ratio of the largest partition to the median partition, tracked over time. This is the skew signal and it moves independently of everything else (Data Skew).
Write-job task durations, whose right tail is where a skewed partition key first becomes visible to an engineer (Tail Latency: Why p50 Being Fine Does Not Help).
Query planning time as a share of total query time, which is where an excessive partition count shows up on the read side (Pipeline Observability).

What changes at 10x and 100x

At 10x data volume with a time key, partition count is unchanged and partitions get bigger. That is the whole argument for time as a key, stated as a scaling property.
At 10x *users* with a user key, partition count grows 10x and data per partition is unchanged — the metadata grows and nothing about the reads improves.
At 100x, the ceiling stops being about performance and becomes about feasibility: some metadata layers have practical limits on partition count, and a table above them is not slow, it is unusable (Partitioning Internals: Key → Partition Function → Node).
Multi-column schemes hit these walls sooner than anyone expects, because the growth is multiplicative and the intuition is additive.

What drives cost here

Metadata cost scales with partition count and is completely independent of data volume — the defining property of this failure and the reason it is invisible in every byte-based view (What Actually Drives Data Platform Cost).
Request cost scales with object count, and object count has a floor of one per partition, so an over-partitioned table pays per-request charges proportional to its cardinality (Object Storage).
Write cost rises because routing to many destinations means a wider shuffle and more open writers (The Shuffle).
The saving side — bytes not scanned — has diminishing returns: once a query reads a small enough fraction, halving it again saves little, while the metadata cost keeps growing linearly (Scan Cost).

What this approach costs

Coarser partitions mean each query reads more. That is a real cost, paid to avoid a metadata cost that grows without bound, and it is the right trade because the first is bounded and the second is not.
Expressing selectivity through sort order instead of partitioning costs a write-time sort and gives probabilistic rather than guaranteed skipping — statistics-based skipping helps in proportion to how well the data clusters, which is a weaker promise than a directory that provably cannot match (Clustering and Sort Order).
Measuring cardinality before committing costs an analysis step that feels unnecessary at the point where the table is small and every option looks fine.

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.

SCALE-SPECIFICBelow a few thousand partitions almost any key works and this lesson is theoretical. Above a few hundred thousand, listing and planning costs dominate on directory-based tables and the same key that was fine becomes the platform's main problem — the advice inverts entirely across that range.
ENGINE-SPECIFICA manifest-based table format reads partition metadata from a compact file and tolerates counts that make a directory-listing engine unusable, so the practical ceiling differs by orders of magnitude between them. The floor set by file size is the same everywhere.
WAREHOUSE-SPECIFICWarehouses that manage layout internally often cap the number of partitions a table may have, or hide partitioning behind clustering entirely. Where the cap exists it converts this lesson from a judgement call into a hard error at write time, which is arguably better.
GENERALThe shape — pruning returns are proportional and diminishing while metadata cost is absolute and linear — holds in every system that partitions, including relational databases with declarative partitioning, where the equivalent symptom is planning time rather than object listing.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasepartitioning-and-sharding partitioning-internals cost-based-optimization

Observabilitycardinality hot-keys percentiles tail-latency alert-fatigue

Cloudobject-storage object-storage-architecture

Concurrencyparallel-scaling-is-not-linear work-stealing

DSAhash-table

Domains that do not exist yet

— Distributed Systems owns why an indivisible unit of work sets a floor on job completion time regardless of cluster size, and why that floor is a property of the data's distribution rather than of the scheduler.