PostgreSQL Partitioning Complete Guide: Range, List, Hash Strategies and Performance Optimization

• When Partitioning Is Needed
• Scope of This Post and Reference Version
• How to Choose a Partition Key
  • Step 1: count what your queries actually filter on
  • Step 2: check whether there is a retention policy
  • Step 3: check the distribution of the candidate column
  • Step 4: now choose the strategy
  • Step 5: check it does not collide with a uniqueness requirement
• Range Partitioning: Time-Series Data
  • Creating Monthly Partitions
  • Per-Partition Indexes
  • Verifying Partition Pruning
• List Partitioning: Category-Based Division
• Hash Partitioning: Even Distribution
• Multi-Level Partitioning
• Automated Partition Management
  • Using pg_partman Extension
  • Shell Script for Auto-Creation
  • Deleting/Archiving Old Partitions
• Performance Comparison: Before vs After Partitioning
  • Test Environment
  • Query Performance Comparison
• Caveats and Constraints
  • PRIMARY KEY Constraint
  • UNIQUE Constraint
  • Cross-Partition Join Performance
• Monitoring
• The Conditions Under Which Pruning Actually Happens
• Failure Modes Per Strategy, and the Order to Diagnose Them
  • Symptom: we partitioned by Range but one particular month is overwhelmingly slow
  • Symptom: we partitioned by List and one partition holds 80% of everything
  • Symptom: we partitioned by Hash and every date-range query is a full scan
  • Symptom: we added partitions and planning time now exceeds execution time
  • Symptom: attaching each new partition takes tens of minutes
• When Not to Partition
• References
• Quiz

When Partitioning Is Needed

As tables grow larger, performance issues arise:

• INSERT performance degrades due to increasing index size
• Full table scan costs increase
• VACUUM operation time increases
• Data retention/deletion costs increase

Generally, partitioning should be considered when the table size exceeds tens of GBs or when time-series data needs to be deleted after a certain period.

Scope of This Post and Reference Version

This post is about which partitioning strategy to choose and what to use as the partition key. The operational mechanics — what locks the DDL for attaching and detaching partitions actually takes, and in what order to build an index without downtime — are covered separately in PostgreSQL Partitioning Practical Guide. The syntax examples here exist to show what each strategy looks like; the page space goes to the reasoning behind the decision.

The reference engine is PostgreSQL 18. Every setting whose name and default is stated in this post was read from the PostgreSQL 18 documentation, and the URLs are collected in the references section at the end. Planner behaviour around partitioning has changed across major versions, so if you are on a different version, re-check the defaults in that version's documentation.

The reason strategy selection gets this much space is simple. The partition key is the hardest decision in partitioning to reverse. A bad index can be dropped. A single partition can be detached. But get the partition key wrong and you have to rebuild the entire table — and you usually discover you got it wrong only after enough data has accumulated, which is precisely the point at which rebuilding is most expensive.

How to Choose a Partition Key

Choosing the strategy (Range, List, Hash) first and the key second is almost always wrong. The order is the other way around.

Step 1: count what your queries actually filter on

Nearly all of partitioning's benefit comes from pruning, and pruning only happens when the partition key appears in the WHERE clause. So the partition key candidate is "the column that appears most often, and most selectively, in our queries' WHERE clauses". Do not decide by intuition. Count.

-- If pg_stat_statements is enabled, counting from real query text is the
-- most accurate approach. Column names vary by version, so check the
-- documentation for the version you are running.
SELECT calls, total_exec_time, left(query, 120) AS query
FROM pg_stat_statements
WHERE query ILIKE '%from events%'
ORDER BY calls DESC
LIMIT 20;

There are two things to look for. Which column appears in WHERE most often, and how much data that condition actually eliminates. A condition that appears constantly but leaves half the table is useless as a partition key.

Step 2: check whether there is a retention policy

If "we delete anything older than 90 days" exists, time-based Range is effectively the answer. Partitioning's second benefit, DROP TABLE, is only usable when the data is cut along time. If the retention policy runs along a different axis — deleting the data of churned customers, say — that axis becomes the partition key candidate.

Step 3: check the distribution of the candidate column

SELECT region,
       count(*) AS rows,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM orders
GROUP BY region
ORDER BY rows DESC;

Example output

 region |   rows    |  pct
--------+-----------+-------
 KR     | 412000000 |  82.4
 US     |  51000000 |  10.2
 JP     |  28000000 |   5.6
 DE     |   9000000 |   1.8

This single query decides the fate of List partitioning. If 82% sits in one value, the orders_kr partition is nearly the size of the original table and partitioning has bought you almost nothing. The common wrong answer at this point is "let's switch to Hash". Hash does even out the sizes, but the price is giving up range-query pruning entirely. Even partition sizes and faster queries are different things.

Step 4: now choose the strategy

Once the three things above are settled, the strategy is nearly determined for you.

That last row is, in practice, the most common answer.

Step 5: check it does not collide with a uniqueness requirement

Leaving this step for last is too late. As the documentation puts it, to create a unique or primary key constraint on a partitioned table, the partition keys must not include any expressions or function calls, and the constraint's columns must include all of the partition key columns. The documentation also explains why: the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions, so the partition structure itself must guarantee that there are no duplicates across different partitions.

This is a domain constraint, not a syntax constraint. A users table where email must be globally unique cannot be partitioned by created_at. You can work around it by moving the uniqueness guarantee into the application or a separate table, but that cost usually exceeds whatever partitioning was going to buy you. So run this check alongside step 1, not after step 4.

Range Partitioning: Time-Series Data

The most commonly used strategy, dividing data by date or ID range.

Creating Monthly Partitions

-- Create parent table
CREATE TABLE events (
    id          BIGSERIAL,
    event_type  VARCHAR(50) NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- Default partition (when no matching partition range exists)
CREATE TABLE events_default PARTITION OF events DEFAULT;

Per-Partition Indexes

-- Global indexes automatically created on each partition
CREATE INDEX idx_events_type ON events (event_type);
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Local index for a specific partition
CREATE INDEX idx_events_2026_03_type
    ON events_2026_03 (event_type, created_at DESC);

Verifying Partition Pruning

-- Check if partition pruning works with EXPLAIN
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE created_at >= '2026-03-01'
  AND created_at < '2026-03-15'
  AND event_type = 'purchase';

-- Result: only the events_2026_03 partition is scanned
-- Append
--   -> Index Scan using events_2026_03_type on events_2026_03
--        Index Cond: (event_type = 'purchase')
--        Filter: (created_at >= '2026-03-01' AND created_at < '2026-03-15')

List Partitioning: Category-Based Division

Divides data by specific value lists:

-- Partitioning by region
CREATE TABLE orders (
    id          BIGSERIAL,
    customer_id BIGINT NOT NULL,
    amount      DECIMAL(12,2) NOT NULL,
    region      VARCHAR(10) NOT NULL,
    status      VARCHAR(20) NOT NULL,
    ordered_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE orders_kr PARTITION OF orders
    FOR VALUES IN ('KR');

CREATE TABLE orders_jp PARTITION OF orders
    FOR VALUES IN ('JP');

CREATE TABLE orders_us PARTITION OF orders
    FOR VALUES IN ('US');

CREATE TABLE orders_eu PARTITION OF orders
    FOR VALUES IN ('DE', 'FR', 'GB', 'IT', 'ES');

CREATE TABLE orders_other PARTITION OF orders DEFAULT;

Hash Partitioning: Even Distribution

Distributes data evenly using hash values of a specific column:

-- Hash partitioning based on user ID (4 partitions)
CREATE TABLE user_activities (
    id          BIGSERIAL,
    user_id     BIGINT NOT NULL,
    activity    VARCHAR(100) NOT NULL,
    metadata    JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id);

CREATE TABLE user_activities_0 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE user_activities_1 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE user_activities_2 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE user_activities_3 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Multi-Level Partitioning

Combining Range and List for multi-level partitioning:

-- Level 1: Date (Range), Level 2: Region (List)
CREATE TABLE sales (
    id          BIGSERIAL,
    product_id  BIGINT NOT NULL,
    region      VARCHAR(10) NOT NULL,
    amount      DECIMAL(12,2) NOT NULL,
    sold_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, sold_at, region)
) PARTITION BY RANGE (sold_at);

-- Monthly sub-partitions
CREATE TABLE sales_2026_03 PARTITION OF sales
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01')
    PARTITION BY LIST (region);

CREATE TABLE sales_2026_03_kr PARTITION OF sales_2026_03
    FOR VALUES IN ('KR');

CREATE TABLE sales_2026_03_jp PARTITION OF sales_2026_03
    FOR VALUES IN ('JP');

CREATE TABLE sales_2026_03_other PARTITION OF sales_2026_03 DEFAULT;

Automated Partition Management

Using pg_partman Extension

-- Install pg_partman
CREATE EXTENSION pg_partman;

-- Configure automated partition management
SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'created_at',
    p_type := 'native',
    p_interval := '1 month',
    p_premake := 3,           -- Pre-create 3 months ahead
    p_start_partition := '2026-01-01'
);

-- Automated maintenance (run via cron)
-- Creates new partitions + manages old partitions
SELECT partman.run_maintenance();

Shell Script for Auto-Creation

#!/bin/bash
# create_monthly_partitions.sh

PGHOST="localhost"
PGDB="mydb"
PGUSER="admin"

# Create partitions for the next 3 months
for i in 0 1 2 3; do
    MONTH=$(date -d "+${i} months" +%Y-%m-01)
    NEXT_MONTH=$(date -d "+$((i+1)) months" +%Y-%m-01)
    TABLE_NAME="events_$(date -d "+${i} months" +%Y_%m)"

    psql -h $PGHOST -d $PGDB -U $PGUSER -c "
        CREATE TABLE IF NOT EXISTS ${TABLE_NAME}
        PARTITION OF events
        FOR VALUES FROM ('${MONTH}') TO ('${NEXT_MONTH}');
    " 2>/dev/null

    echo "Created partition: ${TABLE_NAME}"
done

Deleting/Archiving Old Partitions

-- Detach partition (preserve data, exclude from queries)
ALTER TABLE events DETACH PARTITION events_2025_01;

-- Move the detached partition to a compressed tablespace
ALTER TABLE events_2025_01 SET TABLESPACE archive_tablespace;

-- Or delete completely (DROP is much faster than DELETE!)
DROP TABLE events_2025_01;
-- vs.
-- DELETE FROM events WHERE created_at < '2025-02-01';
-- The above approach takes tens of minutes for millions of rows

Performance Comparison: Before vs After Partitioning

Test Environment

-- Create a 100 million row table (no partitioning)
CREATE TABLE events_no_part (
    id BIGSERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create a partitioned table with the same data (monthly)
-- ... (using the events table above)

Query Performance Comparison

-- Querying 1 month of data
-- Without partitioning: 15.2 seconds (Full Table Scan)
-- With partitioning: 0.8 seconds (Partition Pruning -> single partition scan)

-- Index size
-- Without partitioning: 2.1 GB (single index)
-- With partitioning: 175 MB/partition x 12 = 2.1 GB (same total, but individual indexes are more efficient)

-- Data deletion (1 month)
-- Without partitioning: DELETE -> 45 min + VACUUM 30 min
-- With partitioning: DROP TABLE -> 0.01 seconds

Caveats and Constraints

PRIMARY KEY Constraint

The partition key must be included in the PRIMARY KEY:

-- Error! Partition key (created_at) is not in PK
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,  -- ERROR
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Correct approach: composite PK
CREATE TABLE events (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

UNIQUE Constraint

-- UNIQUE constraints must also include the partition key
CREATE UNIQUE INDEX idx_events_unique
    ON events (event_type, created_at);  -- OK

-- UNIQUE without partition key is not allowed
-- CREATE UNIQUE INDEX ON events (event_type);  -- ERROR

Cross-Partition Join Performance

-- Without filtering by partition key, all partitions are scanned
-- Always include the partition key in the WHERE clause!
SELECT * FROM events
WHERE created_at >= '2026-03-01'  -- Partition pruning works
  AND event_type = 'purchase';

-- Check the enable_partition_pruning setting
SHOW enable_partition_pruning;  -- must be 'on'

Monitoring

-- Check size per partition
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) as table_size
FROM pg_tables
WHERE tablename LIKE 'events_%'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- Check row count per partition
SELECT
    relname as partition_name,
    n_live_tup as row_count
FROM pg_stat_user_tables
WHERE relname LIKE 'events_%'
ORDER BY relname;

-- Verify partition pruning effectiveness
EXPLAIN (ANALYZE, COSTS, BUFFERS, FORMAT TEXT)
SELECT count(*) FROM events
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';

The Conditions Under Which Pruning Actually Happens

However well you choose the strategy, partitioning is a net loss if pruning does not happen. So "how do I know it happened" and "what do I check when it did not" are the final validation of your strategy choice.

Pruning happens at two different times, and the documentation distinguishes them.

Planning-time pruning is the planner examining partition definitions and proving that a given partition need not be scanned. Pruned partitions do not appear in the EXPLAIN output at all.

Execution-time pruning happens for parameter values that are only known during actual execution — values coming out of subqueries, or execution-time parameters such as those from parameterized nested loop joins. The documentation notes that determining whether partitions were pruned during this phase requires careful inspection of the loops property in the EXPLAIN ANALYZE output.

The distinction matters because the diagnosis differs. Failing to prune at planning time means the query has to change; a plan that relies on execution-time pruning cannot be judged from plain EXPLAIN at all.

Example output — pruning happened

 Aggregate  (actual time=88.412..88.413 rows=1 loops=1)
   ->  Seq Scan on events_2026_03 events  (actual time=0.019..61.203 rows=1204411 loops=1)
         Filter: ((created_at >= '2026-03-01') AND (created_at < '2026-04-01'))
 Planning Time: 0.412 ms
 Execution Time: 88.501 ms

Example output — pruning did not happen

 Aggregate  (actual time=2140.882..2140.883 rows=1 loops=1)
   ->  Append  (actual time=0.021..2004.114 rows=1204411 loops=1)
         ->  Seq Scan on events_2026_01 events_1  (actual rows=0 loops=1)
         ->  Seq Scan on events_2026_02 events_2  (actual rows=0 loops=1)
         ->  Seq Scan on events_2026_03 events_3  (actual rows=1204411 loops=1)
         ->  Seq Scan on events_default events_4  (actual rows=0 loops=1)
 Planning Time: 1.882 ms
 Execution Time: 2140.994 ms

Reading it is simple. If partitions are listed one after another under Append and most of them show actual rows=0, pruning did not happen. Those scanned-but-empty partitions are the evidence. Pruned partitions, by contrast, never appear in the list at all — so the test is not "I do not see it, so it must have been pruned" but "among the ones still listed, is anything showing rows=0". For prepared statements, the number removed is reported as Subplans Removed.

There are really only four reasons pruning fails. Check them in this order.

1. The partition key is not in the WHERE clause at all. The most common and most anticlimactic cause.
2. A function or cast was applied to the partition key. WHERE date_trunc('month', created_at) = '2026-03-01' cannot use pruning. Rewrite it as created_at >= '2026-03-01' AND created_at < '2026-04-01' and it will. Timezone casts are the same trap.
3. enable_partition_pruning is off. The documented default is on, so if it is off somebody turned it off deliberately.
4. A prepared statement is using a generic plan. If the parameter values are unknown at planning time, planning-time pruning cannot happen. The documented default for plan_cache_mode is auto; if switching it to force_custom_plan changes the plan, this is your cause.

There is one more thing worth knowing, about joins and aggregates across partitions. Per the documentation, the defaults for both enable_partitionwise_join and enable_partitionwise_aggregate are off. Turning them on lets PostgreSQL pair up matching partitions for joins and aggregation, which is sometimes a large win — but the documentation states the price alongside it. With these settings enabled, the number of nodes whose memory usage is restricted by work_mem appearing in the final plan can increase linearly with the number of partitions being scanned, which can result in a large increase in overall memory consumption during execution, and query planning also becomes significantly more expensive in memory and CPU. Turn them on casually against a table with hundreds of partitions and it comes back as an OOM. Count your partitions first.

Failure Modes Per Strategy, and the Order to Diagnose Them

Symptom: we partitioned by Range but one particular month is overwhelmingly slow

Three things to check, in order.

SELECT relname, n_live_tup, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname LIKE 'events_%'
ORDER BY relname;

1. Compare partition sizes. If a promotion or an incident made one month's traffic several times higher, that partition is several times larger. The premise of time-based Range — equal periods produce comparable sizes — has broken.
2. Look at last_analyze and last_autoanalyze for that partition. When you bulk-load into a freshly created partition, queries often arrive before the statistics do.
3. Confirm the parent's index really was attached to that partition too. If you operate by building per-partition indexes and attaching them, a single missing partition genuinely happens.

Symptom: we partitioned by List and one partition holds 80% of everything

This is the result of skipping the distribution check in step 3. Switching to Hash evens out the sizes but loses range pruning. The better practical answer is to sub-divide only the large value. Following the multi-level partitioning section above, sub-partitioning just the dominant value's partition — splitting KR again by month, say — gives you a split that matches the real query pattern rather than merely equal sizes. The small values do not need splitting, so leave them alone.

Symptom: we partitioned by Hash and every date-range query is a full scan

It is working as designed. Hash partitioning divides by the hash of the key, so a range condition cannot exclude any partition — hashing does not preserve order. Hash is the right strategy only when "we only do equality lookups on this key" is true, and that check has to finish at step 4. Discover it after the table exists and there is no way out but to rebuild the table.

There is a second consequence: Hash partition counts are hard to change later. Changing MODULUS changes which partition every key belongs to, which is effectively a full redistribution. When you pick the initial count, think about how many times larger the table will get.

Symptom: we added partitions and planning time now exceeds execution time

You have too many partitions. The documentation says the planner is generally able to handle partition hierarchies with up to a few thousand partitions fairly well — but it attaches a condition: provided that typical queries allow the planner to prune all but a small number of them. So the problem is not the partition count itself, it is the count remaining after pruning.

Diagnosis is just comparing Planning Time and Execution Time in the EXPLAIN output. When planning time approaches or exceeds execution time, your partition granularity is too fine. Moving from daily to monthly alone divides the partition count by about thirty.

The documentation also makes clear that the answer depends on the workload: with data warehouse type workloads it can make sense to use a larger number of partitions than with an OLTP type workload. And it adds that you should never just assume that more partitions are better than fewer partitions, nor vice versa.

Symptom: attaching each new partition takes tens of minutes

That is an operational DDL problem, not a strategy problem. The cause is a DEFAULT partition scan or a missing CHECK constraint, and the diagnosis and fix — including the lock levels involved — are written up in the practical guide.

When Not to Partition

Work through the decision procedure above and, in a good number of cases, the answer comes out "do not". That is normal.

Do not partition if your queries do not filter on the partition key. Partitioning without pruning turns one large table scan into N smaller table scans and adds planning cost on top. Every dramatic number in the performance comparison section above rests on the assumption that pruning happens.

Do not partition while the table is still small. Partitioning is a schema change that is hard to reverse. "It will get big someday, so let's do it now" is usually a losing trade: the operational cost and the constraints you pay until then generally exceed the cost of migrating when you actually need to.

Do not partition if a column requiring global uniqueness is unrelated to the partition key. The step-5 constraint can be worked around, but in most cases the workaround costs more than partitioning gains.

Without a retention policy you forfeit half the benefit. In a table where data is never deleted you will never use DROP TABLE, and what remains is pruning alone. If pruning is all you need, an index usually gets you there. Look at your indexes and queries before you consider partitioning.

And to be honest about it: the performance comparison table in this post may not reproduce on your data. Those numbers came from one particular schema, one particular distribution and one particular set of queries. Measure your own real queries with EXPLAIN ANALYZE before deciding to partition. That is the single most important sentence in this post.

References

• PostgreSQL 18 — Table Partitioning — the planning-time versus execution-time pruning distinction, Subplans Removed, why unique constraints must include the partition key, guidance on partition counts and workload differences. Checked 2026-08-16
• PostgreSQL 18 — Planner Method Configuration — enable_partition_pruning defaults to on; enable_partitionwise_join and enable_partitionwise_aggregate default to off along with their memory cost; plan_cache_mode defaults to auto. Checked 2026-08-16
• PostgreSQL 18 — CREATE TABLE — inclusive/exclusive RANGE bounds, MINVALUE and MAXVALUE, the definition and restrictions of the DEFAULT partition. Checked 2026-08-16
• PostgreSQL 18 — ALTER TABLE — the locks ATTACH and DETACH take, and the DEFAULT partition caveat. Checked 2026-08-16
• pg_partman documentation — create_parent signature and changes since 5.0. Checked 2026-08-16

One note on the automated management section above: the pg_partman example uses p_type := 'native', but as of the pg_partman 5.0 documentation the values p_type accepts are range and list, with range as the default. From 5.x onward, trigger-based partitioning was removed and all partitioning is handled by declarative partitioning. The documented default for p_premake is 4. Check the version of the extension you have installed and write the arguments to match that version's documentation.

Quiz