Declarative partitioning lets PostgreSQL expose one logical table while storing its rows in separate partitions. The parent routes writes and provides a common query target; the physical partitions hold the data. Its strongest benefits often concern lifecycle management and selective access, but the choice also affects keys, query shape, and operational locking.

A logical parent with physical children

A partitioned parent has no row storage of its own. Each attached partition holds rows permitted by its bounds and can have its own storage parameters, indexes, and maintenance activity. A partitioned index similarly represents a collection of indexes on the physical partitions. Splitting a table does not copy every row into both a parent store and a child store.

Range, list, and hash partitioning divide data according to different rules. Choose the key from real access and retention requirements: monthly event data may fit range partitioning, while other workloads need a different distribution. Declarative partitioning does not by itself distribute those partitions across independent database servers. Local partitions still share the resources and transactional environment of their database.

Keeping the parent name stable can simplify application access, but it does not prove that application code needs no changes. Queries must expose useful partition-key restrictions to benefit from pruning. Keys and foreign-key references may need redesign, and operations that previously addressed a globally unique identifier must retain their intended identity semantics.

The key affects more than routing

A unique or primary-key constraint on a partitioned table must include all partition-key columns, and the partition key must not contain expressions for these constraints. PostgreSQL’s individual partition indexes cannot independently prove arbitrary uniqueness across the other partitions. A composite key on day and id therefore does not promise that id alone is unique across all days.

That distinction matters during a migration from a single table with a global id key. Merely adding the date to the primary key can weaken the original identity contract. Review callers, references, and duplicate-prevention requirements before changing it. The partition key is not just a physical tuning option that can be changed without examining the public data model.

Updating a partition key can move a row to another partition. The destination must exist and accept the row, and indexes and constraints still apply. Such updates are real data changes, not free metadata edits. If retention has already removed the destination range, a late correction may fail even though the logical parent table still exists.

When it breaks

DETACH removes a table from the partition hierarchy while retaining that table and its rows. Queries through the parent stop seeing it, but the detached relation remains available for separate inspection or export. DROP removes the partition’s data as well; detaching is not deletion, and neither operation automatically creates a verified archive or backup.

Partition maintenance takes locks. A plain detach needs a strong lock on the parent, so it can wait behind long-running work and affect concurrent access. PostgreSQL provides a concurrent detach form with different locking and additional restrictions, including that it cannot run inside an explicit transaction block. Choose the operation using its actual contract rather than assuming that “metadata operation” means “no blocking.”

Too many partitions can also increase planning and memory costs. There is no universal row-count threshold that makes partitioning worthwhile. Measure representative reads and maintenance operations, create future partitions before writes need them, and define how late-arriving data interacts with retention. A small well-indexed table may remain simpler than a large collection of tiny partitions.

Check it quickly

Save this as partition-lifecycle.sql and run psql -X -v ON_ERROR_STOP=1 -f partition-lifecycle.sql in a disposable PostgreSQL 18 database. The experiment uses temporary partitions and ordinary transactional detach; final rollback removes the entire fixture.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE events (day date,id integer,PRIMARY KEY(day,id)) PARTITION BY RANGE(day);
CREATE TEMP TABLE events_sep PARTITION OF events FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TEMP TABLE events_oct PARTITION OF events FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
INSERT INTO events VALUES ('2026-09-15',1),('2026-09-30',2),('2026-10-01',3);
SELECT id,day,tableoid::regclass AS physical_table FROM events ORDER BY id;
DO $$ BEGIN
  CREATE UNIQUE INDEX events_id_only ON events(id);
  RAISE EXCEPTION 'global id uniqueness accepted without partition key';
EXCEPTION WHEN feature_not_supported THEN RAISE NOTICE 'unique key must include partition key';
END $$;
SAVEPOINT id_scope;
INSERT INTO events VALUES ('2026-10-03',1);
SELECT id,day FROM events WHERE id=1 ORDER BY day;
ROLLBACK TO id_scope;
RELEASE id_scope;
UPDATE events SET day='2026-10-02' WHERE id=2;
SELECT id,day,tableoid::regclass AS physical_table FROM events ORDER BY id;
ALTER TABLE events DETACH PARTITION events_sep;
SELECT (SELECT count(*) FROM events) AS attached_rows,(SELECT count(*) FROM events_sep) AS detached_rows;
DO $$ BEGIN
  IF (SELECT array_agg(id ORDER BY id) FROM events) IS DISTINCT FROM ARRAY[2,3] OR
     (SELECT array_agg(id ORDER BY id) FROM events_sep) IS DISTINCT FROM ARRAY[1] THEN
    RAISE EXCEPTION 'detach changed stored rows';
  END IF;
END $$;
ROLLBACK;

PostgreSQL 18.6 routed ids one and two to September and id three to October. It rejected a parent unique index on id alone. Within a savepoint, a second id one on another day was accepted under the composite primary key; rolling back that savepoint restores the original fixture.

Updating id two’s day moved it to October, as tableoid showed. Detaching September then left two rows visible through the parent and one row in the detached table. Assertions verify the exact identifiers on both sides, proving that detach changed membership without discarding stored data. This validates the lifecycle and key semantics, not production lock duration or a complete archival procedure.

Sources