Partitions

ALTER TABLE ... DETACH PARTITION

Lock mode ACCESS EXCLUSIVE
Risk HIGH
Blocks reads, writes, other DDL
pgfence rule detach-partition 
ALTER TABLE orders DETACH PARTITION orders_2025_01;

What this does

Without CONCURRENTLY, detaching a partition takes ACCESS EXCLUSIVE on the parent table.

Safe rewrite

Use DETACH PARTITION CONCURRENTLY (PostgreSQL 14+) to avoid the ACCESS EXCLUSIVE lock

ALTER TABLE orders DETACH PARTITION orders_2025_01 CONCURRENTLY;
-- Note: DETACH CONCURRENTLY requires PG14+ and must run outside a transaction block

Read more

More in Partitions

Run pgfence on your migrations ← All lock mode patterns