LabHub

Blog

PostgreSQL 17 Partitioning and Parallel Query Guide

2026-03-06 · 15 min read · #database#postgresql#partitioning#parallel-query#2026-03

한국어English日本語

PostgreSQL 17 Partitioning Strategy and Parallel Query Optimization Complete Guide

Why partitioning and parallel query matter in PostgreSQL 17

In a large production environment, once a single table grows past a few hundred million rows, index size, vacuum time and query response all degrade sharply. PostgreSQL 17 takes declarative partitioning and parallel query one step further. Partitioned tables now support identity columns and exclusion constraints directly, and the ALTER TABLE ... MERGE PARTITIONS and SPLIT PARTITION syntax lets you change partition boundaries dynamically. On the parallel query side, parallel processing has been extended to FULL OUTER JOIN and aggregate functions, and parallel create index for GIN indexes and parallelization of correlated subqueries were added.

This article covers design strategies for each partitioning type, how partition pruning works, how to read parallel query plans, the procedure for migrating a large table, and the troubleshooting you actually run into in production — all with code.

Overview of partitioning types

PostgreSQL supports three basic partitioning strategies plus composite partitioning that combines them.

Range partitioning

Suited to time-based data or continuous value ranges. Splitting an orders table by month is the classic example.

-- Range partitioning: monthly orders table
CREATE TABLE orders (
    order_id    BIGSERIAL,
    customer_id BIGINT NOT NULL,
    order_date  DATE NOT NULL,
    total_amount NUMERIC(12,2),
    status      VARCHAR(20) DEFAULT 'pending'
) PARTITION BY RANGE (order_date);

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

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

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

-- Default partition (catches data that falls outside the ranges)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- Local indexes are created automatically on each partition
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status, order_date);

List partitioning

Used when you separate data by discrete category values (region, status code, and so on).

-- List partitioning: users table by region
CREATE TABLE users (
    user_id     BIGSERIAL,
    username    VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL,
    region      VARCHAR(10) NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT now()
) PARTITION BY LIST (region);

CREATE TABLE users_kr PARTITION OF users
    FOR VALUES IN ('KR');

CREATE TABLE users_us PARTITION OF users
    FOR VALUES IN ('US');

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

CREATE TABLE users_apac PARTITION OF users
    FOR VALUES IN ('JP', 'SG', 'AU', 'IN');

CREATE TABLE users_others PARTITION OF users DEFAULT;

Hash partitioning

Distributes rows evenly by the hash value of a particular column. Useful when the data has no natural range or category.

-- Hash partitioning: distribute the sessions table evenly across 4 partitions
CREATE TABLE sessions (
    session_id  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     BIGINT NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ DEFAULT now(),
    expires_at  TIMESTAMPTZ
) PARTITION BY HASH (session_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);

Composite partitioning (sub-partitioning)

You can combine Range and List to build two-level partitioning.

-- Composite partitioning: Range by year -> List by region
CREATE TABLE events (
    event_id    BIGSERIAL,
    event_type  VARCHAR(50),
    region      VARCHAR(10),
    event_date  DATE NOT NULL,
    payload     JSONB
) PARTITION BY RANGE (event_date);

CREATE TABLE events_2026 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')
    PARTITION BY LIST (region);

CREATE TABLE events_2026_kr PARTITION OF events_2026
    FOR VALUES IN ('KR');

CREATE TABLE events_2026_us PARTITION OF events_2026
    FOR VALUES IN ('US');

CREATE TABLE events_2026_eu PARTITION OF events_2026
    FOR VALUES IN ('DE', 'FR', 'GB');

How to choose between Range, List and Hash partitioning

ItemRangeListHash
Data it suitsTime series, continuous rangesDiscrete categoriesNeeds even distribution
Example partition keysorder_date, created_atregion, statususer_id, session_id
Partition pruningEffective on range predicatesEffective on equality predicatesOnly on equality of the hash key
Risk of data skewCan concentrate in one periodCan concentrate on one valueLow (even distribution)
Adding a new partitionEasy (add a future range)Easy (add a new value)Not possible (needs rehash)
Removing a partitionInstant with DROPInstant with DROPCannot drop individually
Retention / archivingExcellent (detach old partitions)ModerateDifficult
Composite partitioningSupportedSupportedNo second level
PostgreSQL 17 improvementsMERGE/SPLIT supportedMERGE/SPLIT supportedLimited

Choosing between them: for time-series logs or order data, pick Range; for data keyed on region or status code, pick List; when even distribution matters most and pruning matters less, pick Hash. In practice, Range + List composite partitioning is the most common.

How partition pruning works, and how to optimize it

Partition pruning is the mechanism that analyzes a query's WHERE clause and excludes unnecessary partitions from the execution plan. In PostgreSQL 17 both compile-time pruning and run-time pruning are in play.

Compile-time pruning

Constant predicates are evaluated during planning and partitions are excluded there.

-- Checking compile-time pruning
EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01';

/*
Result:
  Append
    -> Seq Scan on orders_2026_03
          Filter: ((order_date >= '2026-03-01') AND (order_date < '2026-04-01'))
-- orders_2026_01 and orders_2026_02 are pruned and never scanned
*/

Run-time pruning

For parameterized queries or predicates that depend on a subquery result, pruning happens at execution time.

-- Run-time pruning: works with a prepared statement
PREPARE get_orders(date, date) AS
SELECT * FROM orders WHERE order_date >= $1 AND order_date < $2;

EXPLAIN ANALYZE EXECUTE get_orders('2026-02-01', '2026-03-01');
-- Only orders_2026_02 is scanned at execution time

When pruning does not work

Pruning does not work in the following situations, so take care.

-- Verifying pruning behaviour: the enable_partition_pruning setting
SHOW enable_partition_pruning;  -- make sure it reads 'on'

-- Anti-pattern: a function applied to the partition key (no pruning)
EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026
  AND EXTRACT(MONTH FROM order_date) = 3;
-- Ends up scanning every partition

-- Correct pattern: use a range predicate (pruning works)
EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01';
-- Only orders_2026_03 is scanned

Reading parallel query execution plans

PostgreSQL 17 leans on parallel query execution far more aggressively. On a partitioned table, parallel workers can scan several partitions at once, and parallel processing has also been extended to FULL OUTER JOIN and aggregate operations.

Key parallel query parameters

ParameterDefaultDescription
max_parallel_workers8Maximum parallel workers for the whole instance
max_parallel_workers_per_gather2Maximum parallel workers per Gather node
min_parallel_table_scan_size8MBMinimum table size for a parallel Seq Scan
min_parallel_index_scan_size512kBMinimum index size for a parallel Index Scan
parallel_tuple_cost0.1Cost of passing a tuple from a parallel worker
parallel_setup_cost1000Cost of starting a parallel worker
max_worker_processes8Limit on background worker processes overall
work_mem4MBWorking memory, applied per worker individually

Inspecting a parallel execution plan

-- Setting parallel query parameters (session level)
SET max_parallel_workers_per_gather = 4;
SET work_mem = '256MB';

-- Parallel scan of a large partitioned table
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2026-04-01'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 100;

/*
Example execution plan:
  Limit  (cost=... rows=100)
    -> Sort  (cost=... rows=...)
          Sort Key: (sum(total_amount)) DESC
          -> Finalize GroupAggregate  (cost=... rows=...)
                Group Key: customer_id
                -> Gather Merge  (cost=... rows=...)
                      Workers Planned: 4
                      Workers Launched: 4
                      -> Partial GroupAggregate  (cost=... rows=...)
                            Group Key: customer_id
                            -> Parallel Append  (cost=... rows=...)
                                  -> Parallel Seq Scan on orders_2026_01
                                        Filter: (...)
                                  -> Parallel Seq Scan on orders_2026_02
                                        Filter: (...)
                                  -> Parallel Seq Scan on orders_2026_03
                                        Filter: (...)
  Planning Time: 2.1 ms
  Execution Time: 1,245 ms
*/

Key points:

Analyzing a Parallel Hash Join

-- Parallel Hash Join: optimizing a large join
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, o.order_date, c.username, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-03-01' AND o.order_date < '2026-04-01'
  AND o.total_amount > 10000;

/*
  Gather  (cost=... rows=...)
    Workers Planned: 4
    Workers Launched: 4
    -> Parallel Hash Join  (cost=... rows=...)
          Hash Cond: (o.customer_id = c.customer_id)
          -> Parallel Seq Scan on orders_2026_03 o
                Filter: (total_amount > 10000)
          -> Parallel Hash  (cost=... rows=...)
                Buckets: 65536  Batches: 1  Memory Usage: 12MB
                -> Seq Scan on customers c
*/

In a Parallel Hash Join every worker helps build one shared hash table, so the build finishes faster than a single-threaded Hash Join. Depending on the work_mem setting it runs either in-memory or multi-batch, and note that with N workers as much as (N+1) x work_mem of memory can be used.

New partitioning features in PostgreSQL 17

MERGE PARTITIONS

Several partitions can be merged into one. Useful when consolidating older data by quarter or by year.

-- PostgreSQL 17: merge monthly partitions into a quarterly one
ALTER TABLE orders
    MERGE PARTITIONS (orders_2026_01, orders_2026_02, orders_2026_03)
    INTO orders_2026_q1;

-- Check the new partition after the merge
SELECT
    parent.relname AS parent_table,
    child.relname  AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_bound
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child  ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'orders'
ORDER BY child.relname;

SPLIT PARTITION

Splits one partition into several. Used to subdivide a partition that has grown too large.

-- PostgreSQL 17: split a quarterly partition back into monthly ones
ALTER TABLE orders
    SPLIT PARTITION orders_2026_q1 INTO (
        PARTITION orders_2026_01 FOR VALUES FROM ('2026-01-01') TO ('2026-02-01'),
        PARTITION orders_2026_02 FOR VALUES FROM ('2026-02-01') TO ('2026-03-01'),
        PARTITION orders_2026_03 FOR VALUES FROM ('2026-03-01') TO ('2026-04-01')
    );

Migrating a large table to partitioning

Converting an existing single table with hundreds of millions of rows into a partitioned table is one of the trickiest operations in production. Here is a practical procedure that keeps downtime to a minimum.

Approach 1: online migration with pg_partman

-- Step 1: install the extension
CREATE EXTENSION IF NOT EXISTS pg_partman;

-- Step 2: create the new partitioned table
CREATE TABLE orders_partitioned (LIKE orders INCLUDING ALL)
    PARTITION BY RANGE (order_date);

-- Step 3: let pg_partman create the partitions automatically
SELECT partman.create_parent(
    p_parent_table := 'public.orders_partitioned',
    p_control := 'order_date',
    p_type := 'native',
    p_interval := '1 month',
    p_premake := 3
);

-- Step 4: migrate the data (in batches)
-- INSERTing everything at once makes WAL explode, so split it into batches
DO $$
DECLARE
    batch_start DATE := '2020-01-01';
    batch_end   DATE;
BEGIN
    WHILE batch_start < '2026-04-01' LOOP
        batch_end := batch_start + INTERVAL '1 month';
        INSERT INTO orders_partitioned
        SELECT * FROM orders
        WHERE order_date >= batch_start AND order_date < batch_end;
        RAISE NOTICE 'Migrated: % to %', batch_start, batch_end;
        batch_start := batch_end;
        PERFORM pg_sleep(0.5);  -- spread out the WAL load
    END LOOP;
END $$;

-- Step 5: swap the tables (short lock)
BEGIN;
ALTER TABLE orders RENAME TO orders_old;
ALTER TABLE orders_partitioned RENAME TO orders;
COMMIT;

-- Step 6: drop the old table once verified
-- SELECT count(*) FROM orders;
-- SELECT count(*) FROM orders_old;
-- DROP TABLE orders_old;

Approach 2: using logical replication

Where no downtime is acceptable, use logical replication.

  1. Create the new partitioned table in a separate schema
  2. Synchronize data in real time with a logical replication subscription
  3. Check for differences, then switch the application over during a short maintenance slot
  4. Drop the subscription and clean up the old table

Operational considerations

Index management

On a partitioned table, indexes are created locally on each partition. Creating an index on the parent table applies it automatically to existing and future partitions. A global unique index must include the partition key.

-- Unique constraints on a partitioned table must include the partition key
-- The following raises an error (partition key not included)
-- ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (order_id);

-- Correct approach: include the partition key
ALTER TABLE orders ADD CONSTRAINT pk_orders
    PRIMARY KEY (order_id, order_date);

Vacuum strategy

On a partitioned table, VACUUM runs on each partition individually. PostgreSQL 17 can run the VACUUM command in parallel.

-- Vacuum a single partition
VACUUM (VERBOSE, ANALYZE) orders_2026_03;

-- Vacuum the whole partitioned table (each partition in turn)
VACUUM (VERBOSE, ANALYZE) orders;

-- Tuning autovacuum: per-partition settings
ALTER TABLE orders_2026_03 SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 2
);

Constraints and triggers

Troubleshooting: failures and recovery

Case 1: the query runs with zero parallel workers

Symptom: EXPLAIN ANALYZE shows Workers Planned: 4 but Workers Launched: 0

Cause and fix:

-- Check the current settings
SHOW max_worker_processes;          -- default 8
SHOW max_parallel_workers;          -- default 8
SHOW max_parallel_workers_per_gather;  -- default 2

-- Check how many workers are running concurrently
SELECT count(*) FROM pg_stat_activity
WHERE backend_type = 'parallel worker';

-- Fix: raise max_worker_processes (requires a restart)
-- postgresql.conf
-- max_worker_processes = 16
-- max_parallel_workers = 12
-- max_parallel_workers_per_gather = 4

When the whole worker pool has already been consumed by other queries, a new query can end up with zero workers. Coordinate this with your connection pool settings so that parallel queries do not all pile up during peak hours.

Case 2: a full scan because partition pruning did not kick in

Symptom: the query reads only one month of data, yet every partition is scanned

Cause: a function applied to the partition key, or a type mismatch

-- Problem query: type mismatch
-- The partition key is DATE but the comparison is against a TIMESTAMP
SELECT * FROM orders
WHERE order_date = '2026-03-15 00:00:00'::timestamp;

-- Fix: compare using the exact type
SELECT * FROM orders
WHERE order_date = '2026-03-15'::date;

-- Verify that pruning happens
EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE order_date = '2026-03-15'::date;
-- Append -> Seq Scan on orders_2026_03 (pruning is working)

Case 3: waiting on a LOCK when adding a partition

Symptom: CREATE TABLE ... PARTITION OF hangs for a long time

Cause: it needs an ACCESS EXCLUSIVE LOCK on the parent table, and a concurrent transaction is still using that table

Fix:

-- Set lock_timeout so it fails fast
SET lock_timeout = '5s';

-- Look for idle transactions
SELECT pid, state, query_start, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
   OR state = 'idle in transaction';

-- Run it in a maintenance window, or use the CONCURRENTLY option
-- (adding a partition itself does not support CONCURRENTLY, only indexes do)
CREATE INDEX CONCURRENTLY idx_new_part ON orders_2026_04 (customer_id);

Case 4: OOM from work_mem blowing up

Symptom: the PostgreSQL process is killed by the OOM killer during a parallel query

Cause: work_mem is multiplied by the number of workers. With work_mem = 1GB and 4 workers, including the leader, 5 x 1GB = 5GB can be used

-- Calculating a safe work_mem
-- Total memory: 64GB, max concurrent connections: 200, max parallel workers: 4
-- work_mem = 64GB * 0.25 / 200 / 5 = about 16MB
SET work_mem = '16MB';

-- Raise it temporarily for one query only
SET LOCAL work_mem = '256MB';
SELECT ... ;  -- large aggregation query
RESET work_mem;

Performance optimization checklist

A checklist for running this in production.

Partitioning design:

Parallel query tuning:

What to monitor:

References

  1. PostgreSQL official documentation - Table Partitioning
  2. PostgreSQL official documentation - How Parallel Query Works
  3. PostgreSQL 17 release notes - partitioning and parallel query improvements
  4. Crunchy Data - Postgres Parallel Query Troubleshooting
  5. Mydbops - PostgreSQL 17 Partitioning Best Practices: MERGE/SPLIT Commands
  6. Microsoft Tech Community - Postgres 17 Query Performance Improvements
  7. AWS Blog - Improve query performance with parallel queries in PostgreSQL
  8. pgMustard - Increasing max_parallel_workers_per_gather

Read the original: https://www.youngju.dev/blog/database/2026-03-06-database-postgresql17-partitioning-parallel-query.en

Comments

No comments yet.

Sign in to leave a comment