Describe what a database does when you attach an already-populated table as a new partition of a partitioned table, and when you remove an existing partition from it. Which locks are taken, how can you make the attach avoid a full validation scan, and what does a concurrent detach (PostgreSQL's DETACH PARTITION CONCURRENTLY) buy you?

## Why attach and detach are interesting at all Both operations look like metadata edits, and in the best case they are. What makes them an interview topic is the correctness invariant behind them and the lock levels that enforcing it requires. The invariant: every row stored in a partition must satisfy that partition's bound, because the planner relies on it to skip partitions. ## ATTACH: the validation scan When you attach a populated standalone table, the engine cannot take your word that its rows fit the bound, so by default it reads the whole table and checks each row. On a large table that is minutes of I/O with locks held. Locks taken in PostgreSQL 12 and later: - **share-update-exclusive on the parent** - deliberately weak, so concurrent reads and writes against the partitioned table continue during the attach; - **access-exclusive on the table being attached** - it is about to change identity, so nothing else may touch it; - **access-exclusive on the default partition, if one exists**, which is also scanned to prove no row in it belongs in the newly covered range. The scan can be skipped. If the incoming table already has a validated CHECK constraint whose condition implies the partition bound, the engine proves the invariant from the catalog instead of from the data. That yields the standard low-impact loading recipe: 1. create the table standalone with the same column layout; 2. add `CHECK (occurred_at >= '2026-09-01' AND occurred_at < '2026-10-01')` and a NOT NULL on the key; 3. bulk-load it, with indexes built afterwards for speed; 4. create indexes matching each of the parent's partitioned indexes, with equivalent definitions; 5. attach; the bound check is proved from the constraint and the index attach is a catalog operation; 6. optionally drop the now-redundant CHECK afterwards. Step 4 matters as much as the constraint. If the parent carries partitioned indexes and the incoming table lacks equivalents, the attach builds them right there while holding its locks, which can dwarf the validation scan. Matching them beforehand turns index creation into a catalog attach. Constraints such as foreign keys on the parent are similarly re-checked or re-established during the attach. ## DETACH: the lock queue problem A plain `DETACH PARTITION` needs an access-exclusive lock on the parent because plans referencing the partitioned table must be invalidated. Access-exclusive conflicts with everything, so it waits behind the longest-running transaction that touched the parent, and while it waits every new query queues behind it. A five-minute analytic query can therefore turn a millisecond DDL into a five-minute outage for that table. The standard defence is `SET lock_timeout` plus retry with backoff: fail fast rather than build a queue. ## DETACH PARTITION CONCURRENTLY PostgreSQL 14 added a concurrent variant that avoids the long exclusive lock by splitting the work into two transactions: - the first marks the partition as detach-pending under a share-update-exclusive lock, so ongoing queries keep seeing it; - it then waits for transactions with older snapshots to finish, so that no running query still expects the partition to be part of the parent; - the second transaction completes the detach. Restrictions worth naming: it cannot run inside an explicit transaction block; it is not allowed when the partitioned table has a default partition; and because it waits, a long-running transaction still delays it, though without blocking others. If it is interrupted, the partition is left in the detach-pending state and you finish with `ALTER TABLE ... DETACH PARTITION ... FINALIZE`. Note also that a detached partition keeps the data: it becomes an ordinary table, which is exactly what you want for archive-then-drop. ## Other engines MySQL offers `EXCHANGE PARTITION` to swap a standalone table with a partition, with an explicit `WITHOUT VALIDATION` option that shifts responsibility to you, and `REORGANIZE PARTITION` which copies data. Oracle has `EXCHANGE PARTITION ... WITHOUT VALIDATION` plus `UPDATE INDEXES` to keep global indexes usable, and online variants. The shape of the tradeoff is identical everywhere: prove the bound cheaply, or pay for a scan under a lock. ## What to say in the interview Name the invariant, the two lock levels, the CHECK-constraint trick that skips the scan, matching indexes beforehand, the lock-queue hazard of access-exclusive DDL, lock_timeout with retries, and the concurrent detach with its restrictions.