Detach and attach partitions with minimal locking
A plain DETACH PARTITION died on a 2 s lock_timeout because one reader was open. The CONCURRENTLY form went through taking only SHARE UPDATE EXCLUSIVE on the parent, never ACCESS EXCLUSIVE, and a matching CHECK constraint cut the re-attach from 53 ms to 0 ms.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
Rolling a monthly partition out of a table is the routine part of running a partitioned schema, and it is also where partition maintenance most often takes an outage. ALTER TABLE ... DETACH PARTITION wants ACCESS EXCLUSIVE on the parent, which means it queues behind every open transaction and then blocks every new one. ATTACH has the mirror-image problem: it validates the partition bound by scanning the whole table under the same lock.
delivery_logs_p holds three monthly partitions of tens of MB each (about 24 MB apiece in this lab). October needs to move to cold storage while the application keeps writing to November and December.
Simple terms
Rolling a monthly partition out of a table is routine, and it is also where partition maintenance most often causes an outage. A plain DETACH wants the strongest lock on the parent table, so it queues behind every open transaction and then blocks every new one. ATTACH has the mirror problem: it scans the whole incoming partition under that lock to check its rows fit the bound. The CONCURRENTLY form of detach, plus a matching CHECK constraint on attach, lets both happen without taking the table down.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
Card required. Cancel before day 7 and you are not charged.
More in this category
Other Schema migrations runbooks
Neighbouring incidents that share the same diagnostic surface.
Connected
How this connects to the rest of the library
A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.