Choosing a Partition Key for Migration-Friendly Tables

Six months after partitioning orders by customer_id hash into 32 partitions, the team discovers that their two most frequent queries — “orders in the last hour” and “orders for this merchant today” — scan all 32 partitions, that retention still requires deleting rows because every partition contains every month, and that the primary key had to become (id, customer_id), which broke two foreign keys. Nothing is wrong with hash partitioning; it was the wrong key for this table. The partition key is the one decision in partitioning that is effectively permanent: PostgreSQL cannot change it in place (changing it means building a new partitioned table and moving the data), and it shapes query performance, uniqueness, retention and every future migration. This guide gives a practical method for choosing it. It is the design step that precedes everything in Partitioning Live Tables Without Downtime.

Choosing the Partitioning Strategy Decision tree. If old data expires by age, partition by RANGE on the timestamp most queries filter on. If not, but data belongs to a small set of tenants or regions that are queried separately, partition by LIST on that column. Otherwise, if the goal is spreading write load or keeping per-partition size bounded, use HASH on a high-cardinality key. Choosing the Partitioning Strategy Does data expire by age? yes no RANGE on the time column queries filter by Queried per tenant / region (few values)? yes no LIST on tenant or region HASH on a high-cardinality key
Retention by age is the strongest signal; most operational tables should be range-partitioned by time.

Symptom / Error Signatures

A poorly chosen key shows up after the fact:

  • EXPLAIN for common queries lists every partition (Append over all children), because the queries do not filter on the key.
  • Retention still requires DELETE, because each partition contains data of every age.
  • One partition receives most writes (a “hot” partition), for example list-partitioning by tenant when one tenant dominates.
  • Unique constraints you need cannot be expressed:
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "orders" lacks column "customer_id" which is part of the partition key.
  • Updates that change the key move rows between partitions (supported since PostgreSQL 11, but slower, and a source of surprising behaviour in triggers).

Root Cause Analysis

The partition key determines four things at once, and a good key satisfies all four:

Property Determined by the key What goes wrong if ignored
Pruning queries prune only when they filter on the key every query scans every partition
Uniqueness unique keys must include the partition key global uniqueness on id alone impossible
Retention a partition can be dropped when all its rows expire retention remains row-by-row DELETE
Write distribution writes go to the partition for their key hot partitions, uneven sizes

For most operational tables — events, logs, orders, messages — a timestamp that records when the row was created satisfies all four: queries usually filter by time, uniqueness as (id, created_at) is acceptable, retention is by age, and writes concentrate in the current partition, which is usually fine (and good for cache locality). The trap is choosing a time column that changes (such as updated_at) or that queries do not use.

Future migrations are the less obvious factor. Every schema change on a partitioned table is applied to every partition; index builds with CONCURRENTLY cannot be run on the partitioned parent directly and must be built per partition and then attached; and the key column itself is essentially frozen — PostgreSQL refuses to change its type. Fewer, well-sized partitions make each of those operations cheaper, and a key that will never need to change avoids the most expensive migration of all.

Candidate Keys for an orders Table Matrix scoring four candidate partition keys for an orders table against pruning, uniqueness, retention and write distribution. created_at scores well on all four. customer_id hash prunes only per-customer queries and does not help retention. region list prunes regional queries but risks hot partitions. updated_at changes over time and moves rows. Candidate Keys for an orders Table Candidate Pruning Uniqueness Retention Writes created_at (RANGE) time-filtered queries (id, created_at) drop by month current partition hot (fine) customer_id (HASH) per-customer only (id, customer_id) no help even region (LIST) regional queries (id, region) no help one region dominates updated_at (RANGE) rarely filtered (id, updated_at) rows move on update rows move
created_at wins for this table because it serves pruning, retention and uniqueness at once.

Immediate Mitigation

If you are designing now, measure before choosing.

1. Find the predicates your queries actually use. Pull the top queries on the table and note which columns appear in WHERE clauses.

-- PostgreSQL · read-only · requires pg_stat_statements · top queries touching orders
SELECT calls, round(total_exec_time) AS total_ms, left(query, 140) AS query
FROM pg_stat_statements
WHERE query ILIKE '%from orders%'
ORDER BY total_exec_time DESC
LIMIT 20;

2. Check how data ages. If rows are retained by age, the distribution of created_at tells you a sensible interval.

-- PostgreSQL · read-only · rows per month, to size partitions
SELECT date_trunc('month', created_at) AS month, count(*) AS rows
FROM orders GROUP BY 1 ORDER BY 1 DESC LIMIT 24;

3. Check uniqueness requirements. List every unique constraint and every foreign key that references the table; decide whether each can include the candidate key.

4. Prototype on a copy. Create the partitioned table on a restored snapshot, load it, and run EXPLAIN for the top queries to confirm pruning.

If you already partitioned by the wrong key, the fix is a new partitioned table with the right key and a batched data move with dual-writes during the transition, using the patterns in Dual-Write Synchronization and Backfill Optimization.

Permanent Fix / Long-Term Pattern

Adopt a short rule set. Partition operational tables by RANGE on an immutable creation timestamp that queries filter on, with an interval that keeps partitions in the tens of millions of rows and matches retention granularity. Make primary keys (id, created_at) from the start for tables likely to be partitioned, so adoption later does not require a key change. Use LIST only when queries are naturally per-tenant or per-region and the values are few and balanced; use HASH only to spread load when there is no natural range. Avoid sub-partitioning unless you have measured a need — the number of partitions multiplies, and planning time and per-partition maintenance grow with it.

Document the key choice and its reasoning next to the schema, because every future migration on the table depends on it — index builds per partition as in Online Index Management, type changes that must avoid the key, and constraint changes that apply across partitions. On MySQL the same principles apply, with the additional rule that every unique key, including the primary key, must include all partitioning columns, and that RANGE COLUMNS on a date or datetime column is the usual choice for time-based retention with DROP PARTITION.

Partitions Scanned per Query, by Key Choice Bar chart of the average number of partitions a typical "last 24 hours" query scans on a 36-partition table. Range on created_at: 1. Hash on customer_id: 36. List on region: 36. Range on updated_at: 36. Partitions Scanned per Query, by Key Choice RANGE created_at 1 HASH customer_id 36 LIST region 36 RANGE updated_at 36 partitions scanned for a last-24-hours query (36-partition table)
A key the queries do not filter on turns every query into a scan of every partition.

Verification Checklist

Frequently Asked Questions

Can I change the partition key later? Not in place. Changing it means creating a new partitioned table with the new key and moving the data, usually with dual-writes during the transition. Choose carefully the first time.

Is time-based partitioning bad because all writes go to one partition? Usually not. Concentrating writes in the current partition keeps its indexes hot in memory, and PostgreSQL handles high insert rates into one table well. Hash partitioning is for cases where one table genuinely cannot absorb the write rate.

How many partitions is too many? There is no fixed limit, but planning time and maintenance grow with the partition count, particularly for queries that cannot prune. Hundreds are routine; tens of thousands usually indicate the interval is too small.

Should the partition key be part of the primary key? It must be, for PostgreSQL and MySQL to enforce uniqueness on a partitioned table. Designing tables with (id, created_at) keys from the start makes later partitioning straightforward.

Related Articles