Does ATTACH PARTITION or DETACH PARTITION lock the parent table?

Partition attach/detach blocking the partition tree

Warning: this works, but blocks traffic or rewrites data at scale.

What happens

DETACH PARTITION without CONCURRENTLY holds ACCESS EXCLUSIVE on the parent and the partition, the whole partition tree stalls behind it. ATTACH PARTITION validates the partition bound by scanning the incoming table while the parent is locked, unless a CHECK constraint matching the bound already exists to skip the scan.

Why it is dangerous on a populated table

It completes, but the time it holds its lock or rewrites rows scales with the table, and on a busy one that is long enough for traffic to queue.

Measured

Both forms ran against the same table (20M rows, 1.7 GB) on Postgres 18.6, with one session inserting a row and another reading one row every 50 ms. Worst wait is the slowest of those probe calls while the statement ran. This run is from Sep 16, 2026; the whole benchmark has every scenario and how to run it.

	Statement	Time	Worst write wait	Worst read wait	Lock
Fires	ATTACH PARTITION without a matching CHECK (scans the partition under lock)	2.5 s	2.4 s	1.8 s	AccessExclusiveLock
Safe	CHECK proving the bound, added NOT VALID and validated, then ATTACH PARTITION	1.1 s	186 ms	242 ms	ShareUpdateExclusiveLock

Fires on

ALTER TABLE measurements DETACH PARTITION measurements_2024;

The safe pattern

Detach with CONCURRENTLY (Postgres 14+, run outside a transaction), it uses SHARE UPDATE EXCLUSIVE instead of locking the whole tree. Before attaching, add a CHECK constraint matching the partition bound to the incoming table and validate it; ATTACH then skips its validation scan and is near-instant.

-- Detach (PG14+, alone in its own migration, outside a transaction):
ALTER TABLE measurements DETACH PARTITION measurements_2024 CONCURRENTLY;
-- Attach without a scan: pre-add a bound-matching CHECK NOT VALID,
-- VALIDATE it, then ATTACH PARTITION, validation is skipped and the
-- lock window is brief

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (2)

attach

ALTER TABLE measurements ATTACH PARTITION measurements_2026 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

detach plain

ALTER TABLE measurements DETACH PARTITION measurements_2024;

Stays silent (1)

detach concurrently

ALTER TABLE measurements DETACH PARTITION measurements_2024 CONCURRENTLY;

How to check locally

Catch this before it ships

This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV019 BV021 →