PostgreSQL partitioning: ranges, routing, and pruning

PostgreSQL table partitioning divides one logical table into smaller physical tables according to a partition key. With declarative range partitioning, you define non-overlapping ranges, insert through the parent, and let PostgreSQL route each row. A query with a suitable filter can exclude partitions that cannot contain matching rows.

This walkthrough uses monthly date ranges and PostgreSQL 18.6. Our 35 checks verify exact row placement, date boundaries, missing partitions, a default partition, key constraints, and query plans with pruning enabled and disabled. The six-row fixture demonstrates behavior; it does not establish a production speed improvement.

Choose a partition key around the work you need to do

Range partitioning is a natural starting point when queries and retention rules follow dates. List partitioning groups explicit values, while hash partitioning distributes keys across a fixed set of buckets. The PostgreSQL partitioning guide describes these options and their operational tradeoffs.

For this exercise, event_date is a business date. Using the date type keeps time zones outside the example. If your actual data uses timestamps, define the time zone and boundary policy before creating partitions. Use representative filters and retention requirements to choose the granularity; one partition per day is not automatically better than one per month.

Table partitioning is different from the PARTITION BY clause in a window function, which groups rows for a calculation. This local declarative setup also does not distribute data across independent database servers or configure sharding.

Create the parent and two monthly partitions

Use a disposable database named partition_demo, where partition_lab does not already exist. Your role needs permission to create the exercise schema and tables. Save the numbered SQL blocks as named files and execute them in order in separate psql invocations. For example: psql -X -qAt -v ON_ERROR_STOP=1 -d partition_demo -f 01-create.sql. Add your host and user options.

The output uses psql's unaligned, tuples-only format, with a vertical bar between columns. Files 02, 05, and 06 deliberately fail; continue with the following file in a new invocation. If you need a separate container-based learning environment first, see our PostgreSQL with Docker Compose guide.

Run 01-create.sql:

CREATE SCHEMA partition_lab;
CREATE TABLE partition_lab.events (
    event_date date NOT NULL,
    event_id bigint NOT NULL,
    amount numeric(10,2) NOT NULL,
    PRIMARY KEY (event_date, event_id)
) PARTITION BY RANGE (event_date);
CREATE TABLE partition_lab.events_2026_01
    PARTITION OF partition_lab.events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE partition_lab.events_2026_02
    PARTITION OF partition_lab.events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
INSERT INTO partition_lab.events VALUES
    ('2026-01-01', 1, 10), ('2026-01-31', 2, 20),
    ('2026-02-01', 3, 30), ('2026-02-28', 4, 40);
SELECT event_id, event_date, tableoid::regclass AS stored_in
FROM partition_lab.events ORDER BY event_id;

1|2026-01-01|partition_lab.events_2026_01
2|2026-01-31|partition_lab.events_2026_01
3|2026-02-01|partition_lab.events_2026_02
4|2026-02-28|partition_lab.events_2026_02

The January range includes January 1 and excludes February 1. The February range starts at February 1 and excludes March 1. This lower-inclusive, upper-exclusive convention lets adjacent ranges meet without overlapping.

The INSERT targets events, while tableoid identifies the physical table that stores each returned row. The February 1 row goes into February. Our additional check of ONLY partition_lab.events returned zero rows: the parent itself stores none. Querying the parent normally returned all four rows, totaling 100.00.

The CREATE TABLE reference documents PARTITION BY, PARTITION OF, and range bounds. In our tests, overlapping ranges were rejected, and inserting a February date directly into the January child failed. Naming a child table does not bypass its boundary.

Observe a missing partition

Run 02-missing.sql:

INSERT INTO partition_lab.events VALUES ('2026-03-01', 5, 50);

The statement fails with SQLSTATE 23514 because March 1 is outside both ranges. PostgreSQL does not create next month's partition from this INSERT. A separate test for December 31 also failed.

In a multirow INSERT containing a valid January row followed by an unmatched March row, the entire statement failed. The earlier January row was rolled back too. Handle the failure as a failed statement, rather than assuming that some of its rows were accepted.

For a time-based production table, decide how future partitions will be created and how missing coverage will be detected. The required lead time depends on the dates that applications can legitimately submit, including backdated and future records.

Add a default partition deliberately

Run 03-default.sql:

CREATE TABLE partition_lab.events_default
    PARTITION OF partition_lab.events DEFAULT;
INSERT INTO partition_lab.events VALUES
    ('2026-03-01', 5, 50), ('2025-12-31', 6, 60);
SELECT event_id, event_date, tableoid::regclass AS stored_in
FROM partition_lab.events WHERE event_id IN (5, 6)
ORDER BY event_id;
SELECT count(*), sum(amount) FROM partition_lab.events;

5|2026-03-01|partition_lab.events_default
6|2025-12-31|partition_lab.events_default
6|210.00

Both the future March row and the earlier December row now enter events_default. The parent contains six rows totaling 210.00; the default partition contains two rows totaling 110.00.

A default partition accepts keys outside the defined ranges, but it can hide a missed partition-creation step unless you monitor its contents. It does not relax other constraints: our NULL date insert still failed because event_date is NOT NULL.

Choose whether unmatched dates should be accepted or rejected based on the application. A default partition is useful when you can investigate and relocate unexpected rows, but it introduces maintenance work. The later March example shows why.

Verify partition pruning with EXPLAIN

Run 04-pruning.sql. This compares the same February filter with pruning enabled and disabled:

ANALYZE partition_lab.events;
BEGIN;
SET LOCAL constraint_exclusion = off;
SET LOCAL enable_partition_pruning = on;
EXPLAIN (COSTS OFF)
SELECT event_id, event_date FROM partition_lab.events
WHERE event_date >= DATE '2026-02-01'
  AND event_date < DATE '2026-03-01';
SET LOCAL enable_partition_pruning = off;
EXPLAIN (COSTS OFF)
SELECT event_id, event_date FROM partition_lab.events
WHERE event_date >= DATE '2026-02-01'
  AND event_date < DATE '2026-03-01';
ROLLBACK;
SELECT event_id, event_date FROM partition_lab.events
WHERE event_date >= DATE '2026-02-01'
  AND event_date < DATE '2026-03-01'
ORDER BY event_id;

Seq Scan on events_2026_02 events
  Filter: ((event_date >= '2026-02-01'::date) AND (event_date < '2026-03-01'::date))
Append
  ->  Seq Scan on events_2026_01 events_1
        Filter: ((event_date >= '2026-02-01'::date) AND (event_date < '2026-03-01'::date))
  ->  Seq Scan on events_2026_02 events_2
        Filter: ((event_date >= '2026-02-01'::date) AND (event_date < '2026-03-01'::date))
  ->  Seq Scan on events_default events_3
        Filter: ((event_date >= '2026-02-01'::date) AND (event_date < '2026-03-01'::date))
3|2026-02-01
4|2026-02-28

The first plan contains only events_2026_02. The second contains an Append node and scans for January, February, and the default partition. The final two lines are the matching row values, not part of the plan.

SET LOCAL confines these diagnostic settings to the transaction. Disabling constraint_exclusion in both plans prevents that separate exclusion mechanism from confusing this comparison. ROLLBACK restores the session's earlier settings; neither setting should be disabled globally to reproduce the exercise.

Our harness also inspected JSON plans and checked the actual query results with pruning on and off. Both returned exactly IDs 3 and 4. A query without a date filter retained all three partitions, while a March-only filter selected the default partition.

These results show which partitions the planner can exclude for these constant date bounds. They do not measure elapsed time or demonstrate pruning for every parameterized query. The EXPLAIN guide explains how to read plans. Plain EXPLAIN shows the plan without executing the SELECT; EXPLAIN ANALYZE executes the statement and reports runtime information.

Pruning and indexing solve different problems. Excluding January avoids visiting that partition; choosing an index inside February concerns access to rows that remain. A sequential scan of this tiny February partition is not a failure. Keep useful indexes, collect statistics, and assess plans using data volumes and query patterns that resemble your workload.

Include the partition key in the primary key

Run this deliberately invalid definition as 05-invalid-key.sql:

CREATE TABLE partition_lab.invalid_key (
    event_date date NOT NULL,
    event_id bigint PRIMARY KEY
) PARTITION BY RANGE (event_date);

It fails with SQLSTATE 0A000 because the primary key omits event_date. In the working table, PRIMARY KEY (event_date, event_id) enforces uniqueness of the pair.

That is not a guarantee that event_id alone is globally unique. Our tests accepted ID 1 on a different date in February and on another date in January. Repeating the same date-and-ID pair failed with 23505. Design references and application identifiers around the actual constraint, rather than adding the date just to silence a schema error.

Check the default partition before adding a range

Run 06-add-march.sql after the default partition has received its March row:

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

This fails with SQLSTATE 23514. The default partition already contains a row that belongs in the proposed March range. Our checks found no new March table, all six rows remained, and ID 5 was still in events_default. Creating a partition does not automatically relocate those existing rows.

Plan a separate migration that preserves the affected rows, coordinates concurrent writers, and moves the data into the new range before validating the final layout. Review locking and validation requirements for your chosen CREATE or ATTACH procedure. Do not delete default-partition data simply to make creation succeed.

This walkthrough intentionally stops at the failed creation rather than prescribing a production migration. Before changing a populated partition tree, rehearse the procedure and verify recovery with our PostgreSQL backup and restore guide. Include application behavior, row counts, and recovery time in that rehearsal.

Clean up the disposable exercise

Run 07-cleanup.sql only in the disposable exercise database:

DROP TABLE partition_lab.events;
DROP SCHEMA partition_lab;

Dropping this parent removes its partitions and their test data. Our final check confirmed that the schema was gone. The verification harness also stopped its isolated server and removed its temporary password file.

The downloadable package includes the SQL, captured output, JSON plan evidence, and all 35 checks. Use the examples to verify routing and pruning first, then evaluate whether partitioning addresses a real query or maintenance need in your own system.