Database Migration Safety

# Database Migration Safety During a deploy, old code and new code run against the same database at the same time. Every migration must be compatible with **both**, or someone gets paged. ## The golden rule Never ship a migration and the code that requires it in a step where either could break the other. Split into deploys where each step is safe with the code before and after it. ## Expand-contract (the only safe way to rename/restructure) Renaming `users.fullname` → `users.display_name`: 1. **Expand:** add `display_name` as nullable. Deploy code that writes both columns, reads old. 2. **Migrate:** backfill `display_name` from `fullname` in batches (1k–10k rows per transaction, sleep between batches — one giant `UPDATE` locks the table and bloats the WAL). 3. **Switch reads:** deploy code reading `display_name`, still writing both. Verify with a count of rows where the columns disagree: must be zero and stay zero. 4. **Stop writing old:** deploy code that ignores `fullname`. 5. **Contract:** after a full release cycle with no rollback, drop `fullname` in its own migration. The same shape covers type changes (new column of new type), table splits, and moving data between services. ## Operation safety table (Postgres-flavored) - **Safe:** add nullable column; add column with default (PG 11+ — metadata only); create index `CONCURRENTLY` (must run outside a transaction); drop index `CONCURRENTLY`. - **Dangerous, has safe recipe:** - NOT NULL on existing column → add `CHECK (col IS NOT NULL) NOT VALID`, then `VALIDATE CONSTRAINT` (takes only a light lock), then set NOT NULL. - Foreign key on big tables → add `NOT VALID`, `VALIDATE` separately. - Plain `CREATE INDEX` → blocks writes for the whole build; always `CONCURRENTLY` on non-empty tables. - **Never in one step on a live table:** rename column/table (breaks running old code instantly); change column type requiring a rewrite; drop a column code still reads. All of these go through expand-contract. - Set a `lock_timeout` (e.g. 5s) at the top of every migration so a blocked DDL fails fast instead of queueing behind a long transaction and stalling all traffic. ## Rollback plan — written before running For each migration, write down: - **Down path:** the reverse migration, tested against a copy. If the change is destructive (drop column, lossy type change), there is no down path — which is exactly why destructive steps come last, after the code rollback window has passed. - **Code rollback compatibility:** "If we roll the app back one release, does this schema still work?" Steps 1–4 above: yes. Step 5: only the previous release, which no longer reads the column. - **Data restore story for destructive steps:** snapshot/backup point-in-time confirmed *before* running, and how long restoring takes. ## Checklist before merge - [ ] Migration runs cleanly on a copy with production-scale row counts; duration recorded. - [ ] Old app version works against the new schema (the actual deploy-window condition). - [ ] Long operations are batched; DDL has `lock_timeout`; indexes are `CONCURRENTLY`. - [ ] Destructive steps are in a separate, later migration — never bundled with the expand step. - [ ] Rollback plan written in the PR description, including the no-down-path cases. - [ ] Backfill is idempotent (safe to re-run after a crash mid-way).