Change the schema without downtime, locks on hot tables, or irreversible steps. Treat the migration and the code deploy as coordinated steps, not one big switch. Pair with financial-invariants for data correctness and scale-readiness-review for sizing impact.
Workflow
1. Classify the change
- Additive (nullable column, new table/index): lowest risk.
- Backfill: risk is runtime and lock duration.
- Constraint/type change (NOT NULL, FK, widen/narrow): risk is table locks and rewrites.
- Rename/move: never in place under a running app — expand/contract instead.
- Destructive (drop): only after no code references it for a full deploy cycle.
2. Expand / contract
Each phase ships independently and is reversible; the app is never broken between steps:
- Expand: add the new structure without removing the old.
- Migrate code: write both, read old (or read new behind a flag).
- Backfill: populate existing rows in batches.
- Switch reads: read new; keep dual-write briefly.
- Contract: stop writing old; add constraints; drop old structure last.
3. Keep DDL online
- Create indexes concurrently/online where supported.
- NOT NULL via checked constraint validated separately, or default-then-enforce.
- FKs as NOT VALID, then VALIDATE separately.
- Keep DDL transactions short; never hold locks during heavy work.
4. Backfill safely
- Bounded, ordered batches (by id/date) with a small sleep between; resumable and idempotent.
- Run as a job/script, never inside the schema migration transaction.
- Monitor lock contention, replication lag, batch duration; throttle if they grow.
5. Rollback plan
- Every step has a down path or documented forward-fix.
- Destructive steps live in a separate, later migration so rolling back the latest deploy loses no data.
6. Verify
- Test on production-scale row counts; measure lock time and duration.
- Run the test suite against the migrated schema at each phase; apply down-then-up where a down path exists.
- Financial tables: reconcile row counts and key sums before and after backfill.
Guardrails
- Never rename or drop a column in the same deploy that stops using it.
- Never backfill a large table in one transaction.
- Never add NOT NULL or a validating FK that full-table-locks a hot table.
- Never run destructive DDL while running code references the old structure.