postgres-risk-checklisted

# PostgreSQL Change Risk Check PostgreSQL `MVCC`/`Explicit Locking`/`ALTER TABLE` official docs + DML/lock/concurrency insights. For the underlying principles, see `reference/principles.md`. ## When to block (CRITICAL) The following carry **production-incident risk**, so block them until they are switched to a safe pattern. - A plain `CREATE INDEX` on a large table (= write-blocking) — use `CONCURRENTLY`. - `ALTER TABLE ... ADD COLUMN ... NOT NULL` on a large table (scan/rewrite), or adding a column with a volatile `DEFAULT` (table rewrite). - `ALTER COLUMN TYPE` on a large table (full table rewrite + long-held `ACCESS EXCLUSIVE` lock). - Adding a validated constraint (FK/CHECK) in one step — do it in two steps, `NOT VALID` → `VALIDATE`. - `UPDATE`/`DELETE` of millions of rows in a single transaction (lock/WAL/rollback/bloat explosion) — chunk it. - Production DDL without `lock_timeout` — the lock queue blocks every transaction behind it (a headline-incident pattern). ## Check procedure 1. **Classify the change type**: index / column / constraint / type / data backfill / DROP. 2. **Confirm the lock level** — the table in `reference/kb/migration-safety.md`. 3. **Rewrite into a safe pattern** (core patterns below). 4. **Review concurrency/deadlocks** — `reference/kb/locking-concurrency.md`. 5. **State the rollback strategy + whether there is downtime + post-change ANALYZE/VACUUM**. ## Core safe patterns (copy-paste) ```sql -- 0) Always set timeouts first (prevents a l