Partition attach/detach blocking the partition tree

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

What it catches

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.

Fires on

ALTER TABLE measurements DETACH PARTITION measurements_2024;

Do this instead

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

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: npx bolvrk check migration.sql

Browse all 69 rules

← BV019 BV021 →