PostgreSQL Table Partitioning, in depth: declarative DDL, the lock each command takes, building indexes without downtime, migrating a live table and keeping the planner honest

How a partitioned table works

In PostgreSQL a partitioned table is a virtual parent that holds no rows. On insert, the executor evaluates the partition key and routes the row to the one partition whose bounds contain it. Each partition is an ordinary table with its own heap, indexes, statistics and vacuum schedule, so dropping a month of data is dropping a table, not deleting millions of rows and vacuuming the holes.

Range bounds are inclusive at the lower end and exclusive at the upper end, so FROM ('2026-10-01') TO ('2026-11-01') covers every instant in October and nothing in November, and adjacent partitions never overlap. PostgreSQL rejects overlapping bounds at creation time. If no partition accepts a row and there is no default partition, the insert fails with an error. That error is a feature: it says the partition job did not run.

CREATE TABLE events (
    tenant_id   bigint      NOT NULL,
    event_id    bigint      GENERATED ALWAYS AS IDENTITY,   -- identity on a partitioned table: PostgreSQL 17+
    occurred_at timestamptz NOT NULL,
    kind        text        NOT NULL,
    payload     jsonb,
    PRIMARY KEY (tenant_id, event_id, occurred_at)   -- must include the partition key
) PARTITION BY RANGE (occurred_at);

-- Lower bound inclusive, upper bound exclusive: no gaps, no overlaps.
CREATE TABLE events_2026_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_2026_11 PARTITION OF events
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

CREATE INDEX events_tenant_time ON events (tenant_id, occurred_at);   -- cascades to every partition

Two rules surprise people. A primary key or unique constraint must include every partition key column, because each partition enforces uniqueness only within itself and there is no global index. And an index created on the parent cascades to every partition, which is convenient for small tables and dangerous for large ones.

The lock each command takes

Partition maintenance is safe in production only if you know what each command blocks. The levels below are from the ALTER TABLE and partitioning documentation for current releases. ACCESS EXCLUSIVE blocks everything including reads; SHARE UPDATE EXCLUSIVE blocks other schema changes and vacuum but lets reads and writes continue.

Two habits follow. Set lock_timeout in maintenance sessions, so a command that cannot get its lock fails in seconds instead of queueing behind a long report with every new query piling up behind it. And create partitions well ahead, never at midnight on the day they are needed.

Building indexes without blocking writes

A plain CREATE INDEX on a large partitioned table builds every partition's index while holding a lock that blocks writes, and the concurrent form is not available on the parent. The documented workaround splits the build into pieces. Create the index on the parent only, with ON ONLY, which registers an invalid parent index without touching partitions. Build each partition's index with CREATE INDEX CONCURRENTLY, which takes longer but does not block writes. Then attach each partition index to the parent index. Once every partition has one attached, the parent index becomes valid automatically, and future partitions get it on creation.

-- 1. Create the parent index only. It is marked invalid and locks nothing for long.
CREATE INDEX events_kind_idx ON ONLY events (kind, occurred_at);

-- 2. Build each partition's index without blocking writes (run outside a transaction).
CREATE INDEX CONCURRENTLY events_2026_10_kind_idx ON events_2026_10 (kind, occurred_at);
CREATE INDEX CONCURRENTLY events_2026_11_kind_idx ON events_2026_11 (kind, occurred_at);

-- 3. Attach each one. When every partition has an attached index, the parent becomes valid.
ALTER INDEX events_kind_idx ATTACH PARTITION events_2026_10_kind_idx;
ALTER INDEX events_kind_idx ATTACH PARTITION events_2026_11_kind_idx;

The same pattern works for unique constraints and primary keys. Script it, and check for invalid indexes left behind by a failed concurrent build (pg_index.indisvalid = false) before you attach anything.

Migrating a large live table

Usually the table already exists: a 2 TB events that deletes and vacuum can no longer keep up with. Rewriting it with INSERT ... SELECT doubles storage and takes hours. Instead, attach the existing table unchanged as the first partition, covering everything before a cutover date.

The scan is the obstacle: attaching a regular table makes PostgreSQL check that every row fits the bound, under an ACCESS EXCLUSIVE lock on that table. The documentation's escape is a valid CHECK constraint that implies the partition bound, which lets ATTACH skip the scan. Add the constraint NOT VALID first, which is instant, then VALIDATE it, which scans the table under a lock that allows reads and writes. The constraint must include IS NOT NULL on the key, because a range partition does not accept nulls.

-- Step 1, online: prove the old table only holds rows below the cutover.
-- Explicit UTC offsets: the CHECK must imply the bound whatever the session TimeZone is.
ALTER TABLE events ADD CONSTRAINT events_legacy_bound
    CHECK (occurred_at IS NOT NULL AND occurred_at < '2026-10-01 00:00+00') NOT VALID;
ALTER TABLE events VALIDATE CONSTRAINT events_legacy_bound;  -- SHARE UPDATE EXCLUSIVE: reads and writes continue

-- Step 2, one short transaction: swap names and attach. The CHECK lets ATTACH skip its scan.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE events RENAME TO events_legacy;
-- Columns and defaults only: INCLUDING CONSTRAINTS would copy the bound onto the parent.
CREATE TABLE events (LIKE events_legacy INCLUDING DEFAULTS)
    PARTITION BY RANGE (occurred_at);
ALTER TABLE events ATTACH PARTITION events_legacy
    FOR VALUES FROM (MINVALUE) TO ('2026-10-01 00:00+00');
CREATE TABLE events_2026_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01 00:00+00') TO ('2026-11-01 00:00+00');
CREATE TABLE events_2026_11 PARTITION OF events
    FOR VALUES FROM ('2026-11-01 00:00+00') TO ('2026-12-01 00:00+00');
COMMIT;

The swap transaction then holds its locks only briefly. Rehearse it on a full-size restored copy, and check what LIKE does not carry: ownership, grants, triggers and publication membership. An identity column needs care too: the simplest path is a plain column on the parent whose default draws from the legacy sequence. Once the legacy data ages past retention, detach and drop it like any other partition.

The DEFAULT partition is a trade-off, not a safety net

A DEFAULT partition catches rows no other partition accepts. That sounds like protection against a missed partition job, and it does prevent insert errors. The costs arrive later. When you attach a new partition, PostgreSQL must verify that the default holds no rows belonging to the new range, and it scans the default under an ACCESS EXCLUSIVE lock unless a CHECK constraint on the default excludes that range. If rows for the new range are already there, the attach fails and you have to move them by hand. And as the restriction table shows, DETACH ... CONCURRENTLY is not allowed at all while a default partition exists, so every retention detach takes ACCESS EXCLUSIVE on the parent.

For time-series tables the better choice is usually no default partition, partitions created months ahead, and an alert when the furthest one is less than a month away. A failed insert is loud; a default silently absorbing rows is not. Keep a default only for list partitioning over an open set of values, and monitor its row count.

Automating the lifecycle

The lifecycle job does three things on a schedule: create partitions ahead, detach and drop those past retention, and analyze the parent. Write it as a small program with autocommit rather than as a single PL/pgSQL procedure, because DETACH ... CONCURRENTLY refuses to run inside a transaction block, and a DO block or function call always runs in one. The extension pg_partman packages the same lifecycle and is widely used; read the documentation for your installed version, because its interface changed between major releases.

import datetime as dt
import psycopg

RETAIN_MONTHS, AHEAD_MONTHS = 13, 3

def month_start(d, delta):
    m = d.year * 12 + d.month - 1 + delta
    return dt.date(m // 12, m % 12 + 1, 1)

with psycopg.connect(DSN, autocommit=True) as conn:   # autocommit: DETACH CONCURRENTLY refuses a transaction block
    conn.execute("SET lock_timeout = '5s'")
    today = dt.date.today()
    for k in range(AHEAD_MONTHS + 1):
        lo, hi = month_start(today, k), month_start(today, k + 1)
        conn.execute(f"CREATE TABLE IF NOT EXISTS events_{lo:%Y_%m} PARTITION OF events "
                     f"FOR VALUES FROM ('{lo}') TO ('{hi}')")
    cutoff = month_start(today, -RETAIN_MONTHS)
    for (name,) in conn.execute("""
            SELECT c.relname FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
            WHERE i.inhparent = 'events'::regclass AND c.relname ~ '^events_[0-9]{4}_[0-9]{2}$'"""):
        y, m = map(int, name.split("_")[1:])
        if dt.date(y, m, 1) < cutoff:
            conn.execute(f"ALTER TABLE events DETACH PARTITION {name} CONCURRENTLY")
            # archive here (pg_dump -t, or copy to object storage), then:
            conn.execute(f"DROP TABLE {name}")
    conn.execute("ANALYZE events")   # parent statistics are never refreshed by autovacuum

A cancelled concurrent detach leaves the partition pending, and only one per table may be pending. The job should check pg_inherits.inhdetachpending and complete it with ALTER TABLE events DETACH PARTITION name FINALIZE before starting new work.

Keeping the planner honest

Partitioning pays only when queries prune. PostgreSQL prunes at planning when the key comparison uses constants, and again at execution start for parameters, stable functions such as now() and values from subqueries. Execution-time pruning shows up in EXPLAIN as a Subplans Removed line, so partitions you expected to see may simply be absent. The key must be compared as a bare column; wrapping it in a function or casting it defeats pruning.

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM events
WHERE tenant_id = 42 AND occurred_at >= now() - interval '7 days';

-- Look for:
--   Append
--     Subplans Removed: 12          <- pruned when execution started (now() is not a plan-time constant)
--     ->  Index Only Scan using events_2026_10_tenant_time on events_2026_10

Statistics are the quiet problem. The documentation states that autovacuum does not process partitioned parents, so it never analyzes them. Partitions get their own statistics, but queries that need parent-level estimates plan from stale or missing numbers. Run ANALYZE events after the initial load and on a schedule, as the job above does.

Two planner settings are off by default: enable_partitionwise_join and enable_partitionwise_aggregate. They run joins and aggregates partition by partition, which can help analytic queries at the price of planning time and memory; enable them per session where they help. Planning cost grows with the partitions that survive pruning; the documentation says a few thousand partitions are handled well when queries prune all but a few.

Failure modes

• Missing future partition. The creation job failed, and inserts error at midnight on the first of the month. Create months ahead and alert on the horizon.
• Lock pile-up. A maintenance command waits behind a long query and blocks every new query on the table. Use lock_timeout and retry.
• No pruning. Queries filter on a function of the key, or not on the key at all, and scan every partition. Check EXPLAIN for each hot query before and after.
• Stale parent statistics. Plans degrade over months as data shifts. Schedule ANALYZE on the parent.
• Stuck detach. A cancelled concurrent detach leaves a partition pending; FINALIZE it.
• Key updates. Changing the key moves the row between partitions as a delete plus insert. Treat the key as immutable.

Related reading

Partitioning splits one table on one server; spreading data across servers is sharding. If you are still deciding whether PostgreSQL fits the workload, see when to pick Postgres, and for how partitioned tables are published to subscribers see logical replication use cases.

What to do next

1. Confirm the hot queries filter on the candidate key as a bare column, and that the drop granularity matches retention.
2. Design the primary key and unique constraints to include the partition key before creating anything.
3. Decide against a DEFAULT partition unless the key set is open, and write down why.
4. For an existing table, rehearse the NOT VALID, VALIDATE and ATTACH migration on a full-size restored copy.
5. Build every large index with ON ONLY, CREATE INDEX CONCURRENTLY per partition and ATTACH, and check for invalid leftovers.
6. Ship an autocommit lifecycle job that creates partitions ahead, detaches concurrently, finalizes stuck detaches, drops past retention and analyzes the parent.
7. Alert on the furthest partition's upper bound and on the row count of any default partition, and set lock_timeout in every maintenance session.