Partitions
ALTER TABLE ... ATTACH PARTITION
PostgreSQL 12+
Also takes a ACCESS EXCLUSIVE lock on the partition being attached (blocks reads, writes, other DDL).
ALTER TABLE orders ATTACH PARTITION orders_2026_01 FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');What this does
On PostgreSQL 12 and later, attaching a partition takes only SHARE UPDATE EXCLUSIVE on the parent (reads and writes on the parent continue), but ACCESS EXCLUSIVE on the partition itself while its constraint is validated against existing rows. Risk stays HIGH because that validation scan can be long if the partition already has data and no matching CHECK constraint exists to skip it.
Safe rewrite
Add a matching CHECK constraint on the partition first so the ATTACH can skip the validation scan
-- 1. Add a matching CHECK constraint on the partition (skips scan during ATTACH):
ALTER TABLE orders_2026_01 ADD CONSTRAINT orders_2026_01_check CHECK (created_at >= '2026-01-01' AND created_at < '2026-02-01') NOT VALID;
ALTER TABLE orders_2026_01 VALIDATE CONSTRAINT orders_2026_01_check;
-- 2. Attach the partition (skips the scan since the CHECK already matches):
ALTER TABLE orders ATTACH PARTITION orders_2026_01 FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
-- 3. Drop the helper constraint (now redundant):
ALTER TABLE orders_2026_01 DROP CONSTRAINT orders_2026_01_check;