Database Migration Patterns
Safe, repeatable schema evolution for production workloads.
Expand-contract
Add columns and tables in one deploy, remove old structures in a follow-up. Never rename a column in-place — add the new one, dual-write, backfill, switch reads, then drop the old.
Online schema changes
Use tools like gh-ost orpt-online-schema-change to avoid table locks. They create a shadow table, copy rows in chunks, and swap with minimal downtime.
Idempotent migrations
Every migration script must be safe to run multiple times. Guard withIF NOT EXISTS,IF EXISTS, and version checks. Never assume the current state.
Backfill strategy
Batch updates in small transactions (1k–10k rows). UseWHERE id > cursor with an ordered scan. Track progress in a separate table so you can resume after interruption.
Rollback planning
Every migration ships with a tested rollback script. Data-destructive changes require a verified backup. Practice rollbacks in staging before touching production.
Next: read about connection pooling to keep migrations fast under load.