writing-safe-migrationslisted

# Writing Safe Migrations ## Overview Before running any migration, answer one question: **does this block all my writes, or not?** Many DDL statements take a lock that stalls every insert/update on the table until they finish — fatal on a large live table. The safe variants exist to avoid exactly that. ## When to Use - Adding an index to an existing large table. - `ALTER TABLE`, adding columns, or adding check constraints. - Any migration on a table with meaningful traffic. - Writing update-heavy queries inside transactions. ## Quick Reference | Operation | Danger | Safe way | |---|---|---| | `CREATE INDEX` | Locks table against writes | `CREATE INDEX CONCURRENTLY` | | Add check/FK constraint | Full-table scan blocks writes | Add `NOT VALID`, then `VALIDATE CONSTRAINT` later | | Dropping columns | Hard to roll back | Keep migrations additive; expand-and-contract | | Long transaction | Holds locks, blocks autovacuum | Keep transactions short | ## Patterns ```sql -- Build the index without locking writes (cannot run inside a transaction block) CREATE INDEX CONCURRENTLY idx_tasks_tenant ON tasks (tenant_id); -- Add a constraint without a write-blocking full-table scan, validate afterwards ALTER TABLE tasks ADD CONSTRAINT chk_status CHECK (status IN ('pending','running','done')) NOT VALID; ALTER TABLE tasks VALIDATE CONSTRAINT chk_status; -- takes only a lighter lock ``` ## Rules - **Additive migrations.** Prefer adding over removing. Avoid dropping/renaming colu