PostgreSQL Table Partitioning: Range, List & Hash Strategies

2026-04-12

PostgreSQL Table Partitioning: Range, List & Hash Strategies

PostgreSQL table partitioning splits a large table into smaller physical pieces (partitions) while presenting them as a single logical table. The query planner uses partition pruning to scan only the relevant partitions, making queries on multi-hundred-million-row tables dramatically faster.

The Problem

Tables grow. An events table starts at a million rows and works fine. A year later it has 500 million rows. Sequential scans that took milliseconds now take minutes. VACUUM takes hours because it processes the entire table. Index builds lock the table for extended periods. Archiving old data means running expensive DELETE statements that generate massive WAL and leave behind bloat.

The fundamental issue is that PostgreSQL stores all rows in a single table file (or set of files for TOAST). Every maintenance operation -- VACUUM, ANALYZE, REINDEX -- operates on the entire table. Every query that cannot use an index reads every page. The table's size determines the cost of every operation against it, regardless of how much data the query actually needs.

Partitioning addresses this by splitting the table into independent physical tables (partitions), each containing a subset of the data based on a partition key. A time-series events table partitioned by month stores January's data in one partition, February's in another, and so on. A query for events in March only scans the March partition -- the planner excludes all other partitions before execution begins (partition pruning). VACUUM can process each partition independently. Dropping an old month's data is an instant DROP TABLE instead of a multi-hour DELETE.

The problems arise when partitioning is done wrong. Choosing the wrong partition key means queries cannot benefit from pruning and actually get slower (the planner must check every partition). Creating too many partitions -- thousands or more -- increases planning time and memory usage because the planner must evaluate each partition during query planning. Partitioning an existing table requires a data migration, which is disruptive and needs careful planning. And certain constraints that work on regular tables -- like global unique indexes that do not include the partition key -- are not supported on partitioned tables.

How to Detect It

Identify tables that are candidates for partitioning based on size, query patterns, and maintenance burden:

-- Find large tables that might benefit from partitioning
SELECT
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    n_live_tup AS estimated_rows,
    n_dead_tup AS dead_tuples,
    last_autovacuum,
    CASE WHEN n_live_tup > 0
        THEN round(100.0 * n_dead_tup / n_live_tup, 1)
        ELSE 0
    END AS dead_tuple_pct
FROM pg_stat_user_tables
WHERE pg_relation_size(relid) > 1073741824  -- > 1 GB
ORDER BY pg_total_relation_size(relid) DESC;

-- Check if existing partitioned tables have proper pruning
SELECT
    inhparent::regclass AS parent_table,
    count(*) AS partition_count,
    pg_size_pretty(sum(pg_relation_size(inhrelid))) AS total_partition_size
FROM pg_inherits
GROUP BY inhparent
ORDER BY sum(pg_relation_size(inhrelid)) DESC;

Verify that partition pruning is working for your queries:

-- Check if partition pruning eliminates partitions
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM events
WHERE event_timestamp >= '2025-06-01' AND event_timestamp < '2025-07-01';

-- In the output, look for "Partitions removed: N" or count the scanned partitions
-- If all partitions are scanned, pruning is not working

In the EXPLAIN output, look for Append nodes listing which partitions are scanned. If you have 12 monthly partitions and a query for one month still scans all 12, partition pruning is failing -- usually because the WHERE clause does not directly reference the partition key column, or uses a type or expression that prevents compile-time pruning.

Check for too many partitions causing planning overhead:

-- Tables with excessive partition counts
SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname AS parent_table,
    count(*) AS partition_count,
    pg_size_pretty(sum(pg_total_relation_size(child.oid))) AS total_size
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent ON parent.relnamespace = nmsp_parent.oid
GROUP BY nmsp_parent.nspname, parent.relname
HAVING count(*) > 100
ORDER BY count(*) DESC;

Tables with more than a few hundred partitions should be examined. Planning time increases with partition count -- a table with 10,000 daily partitions (27 years of data) will have noticeably slower query planning than one with 324 monthly partitions covering the same period.

How myDBA.dev Shows It

myDBA.dev's Storage & I/O page visualizes table and partition sizes, making it immediately clear which tables dominate storage and how their partitions are distributed. The treemap view sizes each table proportionally to disk usage, and for partitioned tables, you can drill into individual partition sizes to verify they are roughly balanced and that old partitions are being dropped according to your retention policy.

Storage & I/O page showing partition sizes in the treemap visualization with size distribution

The Queries page reveals whether partition pruning is working effectively. Queries against partitioned tables show their EXPLAIN plans with the number of partitions scanned versus excluded. A query that should scan one monthly partition but is reading all twelve stands out immediately when you see the high buffer count and execution time. The execution time trend chart shows whether performance is degrading as new partitions are added -- a sign that planning overhead is increasing or pruning is not working.

Queries page showing partition pruning in EXPLAIN plans with partition scan counts

myDBA.dev also tracks vacuum completion per partition, showing which partitions have been vacuumed recently and which are falling behind. This is critical for partitioned tables because autovacuum treats each partition as an independent table -- a high-churn partition might need more aggressive vacuum settings than a rarely-updated historical partition.

How to Fix It

Range partitioning (most common)

Range partitioning is the standard choice for time-series data, logs, events, and any table where queries filter on a date or timestamp:

-- Create a partitioned table with range partitioning
CREATE TABLE events (
    event_id BIGINT GENERATED ALWAYS AS IDENTITY,
    event_timestamp TIMESTAMPTZ NOT NULL,
    event_type TEXT NOT NULL,
    user_id BIGINT,
    payload JSONB,
    PRIMARY KEY (event_id, event_timestamp)  -- must include partition key
) PARTITION BY RANGE (event_timestamp);

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

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

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

-- Create a default partition for data that doesn't match any range
CREATE TABLE events_default PARTITION OF events DEFAULT;

The partition key must be part of any primary key or unique constraint. This is a fundamental PostgreSQL limitation: uniqueness cannot be enforced across partitions, so the partition key must be included.

List partitioning

List partitioning is ideal when data naturally falls into discrete categories:

-- Partition by region or status
CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY,
    region TEXT NOT NULL,
    order_date DATE NOT NULL,
    customer_id BIGINT,
    order_total NUMERIC(12, 2),
    PRIMARY KEY (order_id, region)
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us-east', 'us-west');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('eu-west', 'eu-central');
CREATE TABLE orders_apac PARTITION OF orders FOR VALUES IN ('ap-southeast', 'ap-northeast');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

Hash partitioning

Hash partitioning distributes rows evenly across partitions when there is no natural range or list key. It is useful for reducing contention on high-insert tables:

-- Hash partition by user_id to spread writes evenly
CREATE TABLE user_sessions (
    session_id UUID DEFAULT gen_random_uuid(),
    user_id BIGINT NOT NULL,
    started_at TIMESTAMPTZ DEFAULT now(),
    session_data JSONB,
    PRIMARY KEY (session_id, user_id)
) PARTITION BY HASH (user_id);

CREATE TABLE user_sessions_0 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_sessions_2 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_sessions_3 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Hash partitioning does not support partition pruning for range queries -- only equality conditions on the partition key. Use it when you need to distribute I/O and lock contention, not for query performance.

Partitioning an existing table

You cannot convert an existing table to a partitioned table in place. The standard approach is to create a new partitioned table, migrate the data, and swap:

-- Step 1: Create the new partitioned table
CREATE TABLE events_partitioned (
    LIKE events INCLUDING ALL
) PARTITION BY RANGE (event_timestamp);

-- Step 2: Create partitions covering the data range
CREATE TABLE events_p_2025_01 PARTITION OF events_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
-- ... create partition for each month

-- Step 3: Copy data in batches to avoid long locks and WAL bloat
INSERT INTO events_partitioned
SELECT * FROM events
WHERE event_timestamp >= '2025-01-01' AND event_timestamp < '2025-02-01';
-- ... repeat for each partition's range

-- Step 4: In a transaction, swap the tables
BEGIN;
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_partitioned RENAME TO events;
COMMIT;

-- Step 5: Verify and drop the old table when confident
-- DROP TABLE events_old;

For zero-downtime migrations, use a trigger or logical replication to capture changes to the old table while the migration is running, then apply them to the new table before the swap.

Automating partition creation

Manually creating partitions is error-prone. Use pg_partman or a scheduled function to create partitions ahead of time:

-- Install pg_partman for automatic partition management
CREATE EXTENSION pg_partman;

-- Configure automatic partitioning
SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'event_timestamp',
    p_interval := '1 month',
    p_premake := 3  -- create 3 future partitions in advance
);

-- Schedule maintenance to create new partitions and drop old ones
-- Run this daily via pg_cron or system cron
SELECT partman.run_maintenance();

Dropping old partitions (retention)

The biggest operational advantage of partitioning is instant data removal:

-- Detach a partition (keeps the data but removes it from the parent table)
ALTER TABLE events DETACH PARTITION events_2024_01;

-- Or drop it entirely (instant, no VACUUM needed, no WAL for individual row deletes)
DROP TABLE events_2024_01;

Compare this to DELETE FROM events WHERE event_timestamp < '2024-02-01', which generates WAL for every deleted row, leaves dead tuples that VACUUM must clean up, and holds locks for the duration of the delete. On a 100-million-row partition, the DELETE might take an hour; the DROP takes milliseconds.

How to Prevent It

Choose the right partition key

The partition key must appear in your most common WHERE clauses. If 90% of queries filter on event_timestamp, partition by timestamp. If queries also filter on tenant_id, consider multi-level partitioning (partition by tenant, sub-partition by time) or composite range partitioning. A partition key that queries do not filter on provides no pruning benefit and only adds overhead.

Verify your choice with EXPLAIN before committing to a partitioning scheme. If partition pruning does not activate for your most important queries, the partitioning is not helping.

Control partition count

Keep the total number of partitions manageable. Monthly partitions for 5 years produce 60 partitions -- reasonable. Daily partitions for 5 years produce 1,825 -- the planner will slow down noticeably. If you need fine-grained data retention (daily drops), consider monthly partitions with daily sub-partitions, or use TimescaleDB which is specifically optimized for large partition counts.

Automate partition lifecycle

Never rely on manual partition creation. Use pg_partman or a cron job to create future partitions in advance and drop old ones according to your retention policy. A missing partition causes INSERT failures; a forgotten old partition wastes storage. myDBA.dev monitors partition sizes and counts over time, alerting you to anomalies like a missing future partition or a default partition that is receiving data (which means the range partitions do not cover all incoming data).

Index each partition appropriately

Indexes defined on the parent table are automatically created on each partition. However, you may want different index strategies for different partitions -- a hot current-month partition might need more indexes for query performance, while a cold historical partition might drop unused indexes to save storage and speed up VACUUM. Review index usage per partition periodically.

FAQ

How do I partition an existing table in PostgreSQL?

Create a new partitioned table with the same schema, create the required partitions, copy data from the old table in batches (one partition's range at a time to control memory and WAL usage), then rename the old and new tables in a single transaction. For zero-downtime migrations, use a trigger on the old table to capture concurrent writes and replay them after the copy. Always test the migration on a staging environment with production-sized data first.

What is range partitioning in PostgreSQL?

Range partitioning splits a table into partitions based on non-overlapping value ranges of a column. The most common use is time-based: monthly or daily partitions on a timestamp column. Each partition stores rows where the partition key falls within its defined range. The query planner eliminates irrelevant partitions during planning (partition pruning), so a query for one month's data only scans that month's partition.

What is hash partitioning in PostgreSQL?

Hash partitioning distributes rows across partitions using a hash function on the partition key. It guarantees even distribution regardless of data skew, making it useful for spreading I/O and lock contention across partitions. However, it only supports partition pruning on equality conditions (WHERE user_id = 123), not range queries. Use hash partitioning when you need to reduce contention, not when you need to speed up range-based queries.

Does PostgreSQL support automatic partitioning?

PostgreSQL does not create partitions automatically. You must create them in advance using CREATE TABLE ... PARTITION OF. The pg_partman extension automates this by creating future partitions on a schedule and optionally dropping old ones. Without automation, an INSERT that does not match any existing partition fails (unless you have a DEFAULT partition). TimescaleDB handles automatic chunk creation natively for time-series use cases.