Detach an old partition without blocking live traffic (DETACH CONCURRENTLY)

Command

-- Must run outside a transaction block
ALTER TABLE sales DETACH PARTITION sales_2026_01 CONCURRENTLY;

-- If interrupted (cancel, lost connection) the partition stays 'detach pending'; complete it:
ALTER TABLE sales DETACH PARTITION sales_2026_01 FINALIZE;

-- Any pending detach?
SELECT inhrelid::regclass AS partition_name, inhparent::regclass AS parent
FROM pg_inherits
WHERE inhdetachpending;

-- The detached table is now standalone: archive or drop it
DROP TABLE sales_2026_01;

What it does

Detaches the partition in two transactions; takes only SHARE UPDATE EXCLUSIVE on the parent so running queries are not blocked, and adds a CHECK constraint equivalent to the partition bound to the detached table.

When to use it

When detaching old periods on a live system for retention/archiving (followed by DROP, a move to another tablespace, or a pg_dump archive).

Caution

Cannot run inside a BEGIN ... COMMIT block and cannot be used when the parent has a default partition. It waits for all transactions that use the parent and started before it; long transactions prolong it. If interrupted, the partition stays hidden from queries but not detached ('detach pending') and must be completed with FINALIZE. A plain DETACH without CONCURRENTLY is faster but needs ACCESS EXCLUSIVE on the parent; try it with lock_timeout.