0Pricing
PostgreSQL Performance & Query Optimization · Lesson

Choosing a Partition Key and Strategy

Evaluate range, list, and hash partitioning against real access patterns to pick a key that prunes effectively.

Choosing a Partition Key and Strategy is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why the Partition Key Decides Everything

Partitioning splits one logical table into many physical child tables. The single most important choice is the partition key — the column (or expression) PostgreSQL uses to route each row to a partition.

A good key lets the planner do partition pruning: it skips partitions that cannot match your WHERE clause, so a query touches a few partitions instead of all of them. A bad key forces scans across every partition and you lose most of the benefit.

  • Range partitioning — buckets by ordered ranges (dates, IDs).
  • List partitioning — buckets by a discrete set of values (region, tenant).
  • Hash partitioning — buckets by a hash of the key for even spread.

The right strategy is the one that matches how your queries actually filter the data.

Start From the Access Pattern, Not the Schema

Never pick a partition key by looking at the table alone. Look at the queries. Ask three questions:

  • Which columns appear in WHERE clauses on almost every read?
  • Do queries filter by ranges (last 7 days) or exact values (tenant_id = 42)?
  • How do you delete or archive old data — by time, by customer, or never?

If 90% of your queries filter created_at >= now() - interval '7 days', then created_at is your candidate key. The partition key must appear in the query's filter for pruning to fire.

-- Inspect real filters before deciding: which columns are filtered most?
SELECT query, calls, total_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%events%'
ORDER BY total_exec_time DESC
LIMIT 10;

Range Partitioning: The Time-Series Default

Range partitioning assigns each row to a partition based on whether the key falls inside a half-open interval [from, to). It is the natural fit for time-series and append-mostly data, where new rows have increasing timestamps and old data is queried less.

Each partition covers one period (a month here). Inserts land in the newest partition; queries that filter by date prune to just the months they need.

CREATE TABLE events (
    id          bigint        NOT NULL,
    created_at  timestamptz   NOT NULL,
    user_id     bigint        NOT NULL,
    payload     jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_05 PARTITION OF events
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE events_2026_06 PARTITION OF events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

Watch Pruning Work on a Range Key

The payoff is visible in EXPLAIN. When your filter overlaps only one partition's range, the plan scans just that partition. The others never appear in the plan — they were pruned at planning time.

Because the filter on created_at matches the partition key, PostgreSQL knows June rows can only live in events_2026_06. The May partition is skipped entirely.

EXPLAIN (COSTS OFF)
SELECT count(*)
FROM events
WHERE created_at >= '2026-06-01'
  AND created_at <  '2026-06-15';

-- Plan shows only:
--   Seq Scan on events_2026_06
-- The May partition is pruned and never scanned.

List Partitioning: Discrete, Bounded Categories

List partitioning routes rows by matching the key against an explicit set of values. Reach for it when the key is a small, stable set of discrete categories that queries filter on exactly — region, country, status, or a fixed tenant tier.

It shines when each category has different retention, locality, or compliance needs (for example, keeping EU data physically separate). Use a DEFAULT partition to catch values you did not enumerate.

CREATE TABLE customers (
    id      bigint NOT NULL,
    region  text   NOT NULL,
    name    text
) PARTITION BY LIST (region);

CREATE TABLE customers_eu PARTITION OF customers
    FOR VALUES IN ('DE', 'FR', 'NL', 'ES');

CREATE TABLE customers_us PARTITION OF customers
    FOR VALUES IN ('US', 'CA');

CREATE TABLE customers_other PARTITION OF customers DEFAULT;

When List Partitioning Backfires

List partitioning assumes the value set is small and slow-changing. It breaks down when:

  • The category has thousands of values (one partition per user_id) — too many partitions hurt planning time and memory.
  • Values are highly skewed — one giant partition holds 80% of rows, so pruning gives little benefit.
  • Queries rarely filter on the key — then no partition is ever pruned.

If the values are many but you still want even distribution and pruning on equality, that is the signal to consider hash partitioning instead.

Hash Partitioning: Even Spread for Point Lookups

Hash partitioning computes a hash of the key and assigns rows by hash MOD modulus. Its purpose is even data distribution when there is no natural range or list, and when queries look up rows by exact equality on a high-cardinality key like user_id.

You define a fixed number of partitions (the modulus). Each gets a remainder. Distribution is uniform, which spreads write load and keeps every partition a similar size.

CREATE TABLE sessions (
    id       bigint NOT NULL,
    user_id  bigint NOT NULL,
    token    text
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_p0 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Hash Prunes on Equality, Not Ranges

The critical limitation: hash partitioning can only prune for equality (and IN) on the key. A range filter is useless because the hash scatters consecutive values across all partitions.

  • WHERE user_id = 1001 → prunes to exactly one partition.
  • WHERE user_id BETWEEN 1000 AND 2000 → no pruning, scans all partitions.

So hash fits point lookups and even write spread, but it is the wrong choice if your dominant queries scan ranges of the key or need time-based retention.

-- Prunes to ONE partition (equality matches the hash key):
EXPLAIN (COSTS OFF)
SELECT * FROM sessions WHERE user_id = 1001;

-- Does NOT prune (range over a hashed key scans every partition):
EXPLAIN (COSTS OFF)
SELECT * FROM sessions WHERE user_id BETWEEN 1000 AND 2000;

Match Retention and Deletes to the Key

Deletion strategy often decides the key more than reads do. Dropping a whole partition is instantaneous and reclaims space immediately; deleting millions of rows with DELETE is slow and bloats the table.

If you must purge data older than 90 days, a range key on the date lets you DROP or DETACH an old partition in milliseconds. Hash and list keys cannot do this for time-based retention because age is not aligned to any single partition.

-- Range-by-date makes retention a metadata operation:
ALTER TABLE events DETACH PARTITION events_2026_05;
DROP TABLE events_2026_05;

-- vs. the slow, bloating alternative on a non-range layout:
-- DELETE FROM events WHERE created_at < '2026-06-01';

Composite Keys and Sub-Partitioning

Sometimes one dimension is not enough. You can partition by multiple columns, or sub-partition: range by month at the top level, then hash by user_id inside each month. This combines time-based retention with even spread for point lookups.

Keep it disciplined — every extra level multiplies the partition count. Too many partitions slow down planning and consume locks. Only sub-partition when a single key genuinely cannot serve both your retention and your lookup patterns.

CREATE TABLE events_2026_06 PARTITION OF events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01')
    PARTITION BY HASH (user_id);

CREATE TABLE events_2026_06_h0 PARTITION OF events_2026_06
    FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE events_2026_06_h1 PARTITION OF events_2026_06
    FOR VALUES WITH (MODULUS 2, REMAINDER 1);

A Decision Checklist

Put the strategies side by side against your real workload:

  • Range — key is ordered (time/sequence), queries filter by ranges, you archive or drop old data by period. The default for logs, events, metrics.
  • List — key is a small fixed set of categories, queries filter by exact category, categories need separate handling (locality, retention).
  • Hash — high-cardinality key, queries are equality point lookups, you want uniform size and write spread, and you do NOT need range pruning or time-based drops.

Pick the strategy whose pruning model matches the filter your queries actually use. If no key is filtered on most queries, partitioning will not help reads — reconsider before adding the complexity.

Quick Check

Apply the decision rules to a concrete workload.

Recap

The partition key and strategy must be driven by how queries filter and how data is retired:

  • Range for ordered keys, range filters, and period-based archival — the time-series default that makes purges a metadata operation.
  • List for a small, stable set of discrete categories filtered by equality, especially when categories need distinct handling.
  • Hash for high-cardinality keys with equality point lookups and a need for even distribution — but it cannot prune ranges or drop by age.

Always verify pruning with EXPLAIN: the right key shows only the partitions your filter can match. If most queries do not filter the key at all, partitioning adds cost without speeding up reads.

Frequently asked questions

Is the “Choosing a Partition Key and Strategy” lesson free?

Yes — the full text of “Choosing a Partition Key and Strategy” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “Choosing a Partition Key and Strategy”?

Evaluate range, list, and hash partitioning against real access patterns to pick a key that prunes effectively. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Choosing a Partition Key and Strategy” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Choosing a Partition Key and Strategy
  2. Partition Pruning at Plan and Execution Time
  3. Automating Partition Creation and Retention
  4. Migrating a Huge Table to Partitions Online
← Back to PostgreSQL Performance & Query Optimization