Partition Key Selection & Design: Cardinality, Skew, Query Alignment

The partition key is the one schema decision you cannot cheaply undo: it determines which queries prune, how evenly writes spread, and whether maintenance stays bounded as the table grows. This guide — part of Database Partitioning Fundamentals & Architecture — gives you a repeatable framework for picking that key: audit the workload, score candidates on cardinality and skew, match the workload to a key family, and verify immutability before the first CREATE TABLE ... PARTITION BY ships. For mapping whole application archetypes to strategies, see Use-Case Mapping for Partition Strategies; this page focuses on the key itself.

Problem Framing

A payments platform partitions its 2 TB orders table by HASH (order_id) across 32 partitions. Writes distribute perfectly. Six months later the on-call rotation is drowning: the finance dashboard’s “orders last 7 days” query scans all 32 partitions because order_id carries no temporal information; customer-support lookups by customer_id fan out the same way; and month-end archival — which used to be a candidate for DROP PARTITION — now requires a DELETE that touches every partition and bloats them all.

Nothing is wrong with hash partitioning here. The key is wrong. order_id appears in almost no WHERE clause except single-row lookups, so the planner can never prune, and the one operational win partitioning should have bought (cheap time-based archival) is impossible because time is not in the key.

Fixing it means building a new table partitioned by created_at, dual-writing, backfilling 2 TB in batches, and cutting over — weeks of work that a two-day key audit before launch would have avoided. That audit is what the rest of this page describes.

The Four Tests Every Candidate Key Must Pass

Score each candidate column (or column pair) against four tests before writing any DDL:

1. Query alignment. Does every hot query — the top statements by total execution time, not by count of distinct query shapes — carry the key in a prunable predicate (=, IN, or a range on the key)? A key that 95% of read traffic filters on turns partitioning into an automatic performance win; a key that only batch jobs filter on turns it into pure operational overhead.
2. Cardinality and granularity. Does the key’s distinct-value count map to a sane partition count? You want partitions large enough to amortize per-partition planning and metadata cost (roughly 5–50 GB each) and few enough that the planner stays fast — under about 1 000 partitions per table in PostgreSQL.
3. Write distribution vs data locality. These pull in opposite directions. A monotonically increasing timestamp gives perfect locality (all recent rows in one partition, ideal for pruning and archival) but concentrates 100% of insert traffic on that same partition. A hashed key gives perfect write spread but zero locality. Decide which side of the tension your workload lives on before choosing.
4. Immutability. Is the column effectively write-once? An UPDATE that changes a partition key value is executed as a delete from one partition plus an insert into another. That row movement breaks ctid-based optimizations, can raise serialization errors under concurrency, fires delete+insert trigger semantics instead of update semantics, and — in MySQL — is simply slower and lock-heavier. status columns that transition through a lifecycle are the classic trap.

If a candidate fails all four branches, that is a real answer: the table may not benefit from partitioning yet, or the workload needs restructuring first — the use-case mapping guide covers that call.

Step 1 — Audit Query Alignment with pg_stat_statements

Do not trust anyone’s memory of “what the app queries.” Pull the actual hot statements, ranked by total execution time, and check which columns their predicates carry:

-- Requires: CREATE EXTENSION pg_stat_statements;
-- Top 20 statements touching the target table, by total time
SELECT
  queryid,
  calls,
  ROUND(total_exec_time::numeric / 1000, 1)          AS total_sec,
  ROUND(mean_exec_time::numeric, 2)                  AS mean_ms,
  ROUND(100.0 * total_exec_time /
        SUM(total_exec_time) OVER (), 1)             AS pct_of_load,
  LEFT(query, 120)                                   AS query_head
FROM pg_stat_statements
WHERE query ILIKE '%orders%'
  AND query NOT ILIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 20;

Build a small tally from the output: for each candidate key, what percentage of total_exec_time comes from statements whose WHERE clause contains that column in a prunable form? Function-wrapped predicates (date_trunc('day', created_at)) and inequality-only predicates on hash keys do not count — the planner cannot prune on them.

A useful acceptance bar: the winning key should appear prunably in statements covering at least 80% of read-side execution time. Below 60%, partitioning on that key will make the unaligned queries slower (every query now pays partition-append overhead) while helping only a minority.

Repeat the audit for writes. INSERT statements always route by the key, but check UPDATE and DELETE shapes too: an UPDATE ... WHERE order_id = $1 against a table partitioned by created_at must scan every partition unless the application also passes the timestamp.

Step 2 — Measure Cardinality and Skew

A key that aligns with queries can still fail on shape. Measure three numbers for each candidate: distinct-value count, top-value concentration, and skew ratio.

-- Cardinality of each candidate key
SELECT
  COUNT(*)                              AS total_rows,
  COUNT(DISTINCT customer_id)           AS distinct_customers,
  COUNT(DISTINCT created_at::date)      AS distinct_days,
  COUNT(DISTINCT region)                AS distinct_regions
FROM orders;

-- Concentration: how much of the table do the top 10 values own?
SELECT
  customer_id,
  COUNT(*)                                              AS row_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2)    AS pct_of_table
FROM orders
GROUP BY customer_id
ORDER BY row_count DESC
LIMIT 10;

-- Skew ratio: biggest group vs average group
WITH groups AS (
  SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id
)
SELECT MAX(n)                              AS biggest_group,
       AVG(n)::bigint                      AS avg_group,
       ROUND(MAX(n)::numeric / AVG(n), 1)  AS skew_ratio
FROM groups;

Interpret the numbers against the key-family targets:

• Too low (region with 4 values): four partitions cannot spread I/O or keep partition sizes bounded as the table grows. Usable only as the outer level of a composite key.
• Too high (customer_id with 40 million values): one partition per value is impossible; the value must be hashed or bucketed, which discards range pruning on that column.
• Skewed (skew ratio above roughly 5×): the biggest value becomes a hot partition that dominates vacuum time, cache pressure, and backup duration. Skew does not disqualify a key, but it forces a mitigation — sub-partitioning the hot value or isolating it — which must be designed now, not after the pager fires.

Run the concentration query on a recent time slice as well as the full table. Historical distribution lies: a tenant that was 2% of rows last year may be 30% of rows this month.

Step 3 — Match the Workload to a Key Family

With alignment and shape data in hand, pick the key family. The three families trade off differently, and the DDL is not interchangeable later.

Temporal keys

A timestamp or date column, almost always paired with range partitioning strategies. Best when recency defines both the hot query window and the retention policy:

CREATE TABLE metrics (
  metric_id   BIGINT GENERATED ALWAYS AS IDENTITY,
  device_id   UUID        NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL,
  value       DOUBLE PRECISION,
  PRIMARY KEY (metric_id, recorded_at)
) PARTITION BY RANGE (recorded_at);

CREATE TABLE metrics_2026_07 PARTITION OF metrics
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

Strengths: pruning on every time-bounded query, DROP PARTITION retention, and stable partition sizes if ingest volume is steady. Weakness: every insert lands in the newest partition, so single-partition write throughput is the ceiling — acceptable on one node, a real problem in distributed setups where the newest partition pins one shard.

Entity keys

A bounded categorical column — tenant, region, product line — paired with list partitioning techniques. Best when isolation, per-entity archival, or compliance-scoped deletion is the goal:

CREATE TABLE tenant_events (
  event_id   UUID        DEFAULT gen_random_uuid(),
  tenant_id  VARCHAR(32) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  payload    JSONB,
  PRIMARY KEY (event_id, tenant_id)
) PARTITION BY LIST (tenant_id);

Strengths: exact-match locality (dropping a churned tenant is one DDL statement), independent autovacuum per entity. Weakness: entity size skew is the norm, not the exception — plan the hot-entity mitigation up front.

When no natural entity exists and the goal is purely even write spread across many anonymous keys, hash the highest-cardinality stable identifier instead; hash routing algorithms covers modulo vs consistent hashing and the rebalancing consequences. Accept before choosing it that hash keys permanently give up range pruning and bulk archival.

Composite keys

Two dimensions, ordered by query priority: the outer key answers “which entity,” the inner key answers “which time window.” In PostgreSQL the practical construction is list (or hash) at the top level with range sub-partitions:

CREATE TABLE tenant_events (
  event_id   UUID        DEFAULT gen_random_uuid(),
  tenant_id  VARCHAR(32) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  payload    JSONB,
  PRIMARY KEY (event_id, tenant_id, created_at)
) PARTITION BY LIST (tenant_id);

CREATE TABLE events_acme PARTITION OF tenant_events
  FOR VALUES IN ('acme')
  PARTITION BY RANGE (created_at);

CREATE TABLE events_acme_2026_07 PARTITION OF events_acme
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

Composite keys resolve the write-distribution/locality tension by giving each dimension its own level — writes spread across tenants while each tenant’s data stays time-ordered. The cost is partition count multiplication (tenants × months) and heavier automation: partition creation must now iterate two dimensions, which is exactly the kind of workflow covered by the automated-creation guides in the implementation section.

Order matters. Put the dimension that every hot query filters on at the outer level. If dashboards query across all tenants by time but per-tenant queries are rare, range-by-time outer with hash-by-tenant inner is the correct inversion.

Step 4 — Verify Immutability

An UPDATE that changes a row’s partition key is executed as a cross-partition row move: PostgreSQL (11+) deletes the row from the old partition and inserts it into the new one. Concurrent transactions touching the moved row can fail with serialization errors, RETURNING ctid values are invalidated, and per-row triggers fire in delete+insert order. MySQL performs the equivalent move inside the storage engine with similar cost. Either way, a frequently updated column is a poor partition key.

Audit how often each candidate is actually updated:

-- Find UPDATE statements that set the candidate column
SELECT calls,
       ROUND(total_exec_time::numeric / 1000, 1) AS total_sec,
       LEFT(query, 140)                          AS query_head
FROM pg_stat_statements
WHERE query ILIKE 'UPDATE%orders%'
  AND query ILIKE '%created_at =%'
ORDER BY calls DESC;

If the business logic occasionally must rewrite the key (a mis-attributed tenant, a corrected event timestamp), make the operation explicit rather than letting ORMs issue silent row-moving updates:

-- Block accidental partition key updates; force an explicit move procedure
CREATE OR REPLACE FUNCTION forbid_partition_key_update()
RETURNS trigger AS $$
BEGIN
  IF NEW.tenant_id IS DISTINCT FROM OLD.tenant_id THEN
    RAISE EXCEPTION
      'tenant_id is the partition key; use move_tenant_row() instead';
  END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_forbid_key_update
  BEFORE UPDATE ON tenant_events
  FOR EACH ROW EXECUTE FUNCTION forbid_partition_key_update();

Lifecycle columns (status, state, stage) fail this test almost by definition: every row migrates partitions several times during its life, turning the write path into a churn machine. If the dominant query is “give me all pending rows,” a partial index on the unpartitioned dimension is nearly always the better tool.

Worked Examples

Three common workloads, run through the framework:

E-commerce orders. Hot queries: recent-order dashboards (time), customer order history (customer_id), single-order lookup (order_id). Alignment audit typically shows time-bounded queries dominating execution cost. Choose created_at range partitioning by month; serve customer history through a (customer_id, created_at DESC) index inside each partition and accept bounded fan-out for the rare full-history lookup. order_id fails the alignment test as a key despite being the primary identifier — a UUID that appears only in point lookups prunes nothing.

IoT time-series. Hot queries: per-device recent windows, fleet-wide aggregations by time. Both carry recorded_at, so temporal is the outer dimension. At single-node scale, monthly or daily range partitions alone suffice. At multi-node scale, the newest-partition write hotspot forces a composite: hash by device_id across shards, range by recorded_at within each — write spread from the hash level, retention and pruning from the range level.

Multi-tenant SaaS. Hot queries: always scoped by tenant_id; retention and deletion are per tenant (compliance). Entity key on tenant_id wins the alignment test outright, but the skew measurement decides the final layout — a flat list layout, a hashed layout, or list-with-range sub-partitions for the whales. The full evaluation, including the SQL to profile tenant distribution and benchmark pruning across all three layouts, is worked end-to-end in Choosing a Partition Key for Multi-Tenant Workloads.

Decision Reference

Operational Contrast

The key family largely dictates the mechanism, so contrast them by what they can never give you. A temporal key with range partitioning buys time-window pruning and free archival but concentrates writes on the newest partition. An entity key with list partitioning buys per-entity isolation and one-statement tenant deletion but inherits whatever skew the business has. An opaque key with hash routing buys uniform write spread but permanently forfeits range pruning, targeted archival, and any human-readable mapping from value to partition — and rebalancing a hash layout means moving data.

Composite layouts buy back most of the missing property at the cost of partition-count multiplication and two-dimensional automation. When none of the candidates pass the alignment audit, step back to use-case mapping — the right answer may be a different table design, or no partitioning at all.

Failure Modes

Common Mistakes

• Choosing the primary key as the partition key by default. High-cardinality surrogate ids (order_id, UUIDs) appear only in point lookups; they force hashing and prune nothing for analytical or time-window queries. The partition key should come from WHERE clauses, not from the identity column.
• Auditing query shapes instead of query cost. Counting “how many queries filter on X” weights a once-a-day report the same as a 500-per-second lookup. Always weight the alignment audit by total_exec_time.
• Ignoring the write path. A key can align perfectly with reads while every UPDATE ... WHERE id = $1 fans out across all partitions. Audit UPDATE/DELETE shapes with the same rigor as SELECTs.
• Designing for today’s distribution. Cardinality and skew drift: tenants grow 100×, ingest volume triples, retention policies change. Re-measure skew quarterly and keep the hot-value mitigation (sub-partitioning DDL, detach runbook) rehearsed rather than theoretical.

FAQ

Related

• Database Partitioning Fundamentals & Architecture — parent overview of partitioning concepts, tradeoffs, and scaling limits
• Choosing a Partition Key for Multi-Tenant Workloads — end-to-end walkthrough applying this framework to a SaaS events table
• Use-Case Mapping for Partition Strategies — mapping whole application archetypes to partition strategies before key selection
• Range Partitioning Strategies — implementing temporal keys: boundary design, pruning, and lifecycle management