Migrations

Schema and data migrations change production structure safely across deploys. Strong practice: versioned migration files, backwards-compatible expand steps (add nullable column → dual-write/backfill → switch reads → drop old), tested on copy of prod data, and rollback plan when the app and DB versions can diverge.

					deploy 1: ADD COLUMN nullable
deploy 2: backfill job + app writes both columns
deploy 3: read new column, stop writing old
deploy 4: DROP old column
				

Long-running migrations use batch updates with key ranges, throttling, and online index creation to avoid locking tables for minutes.

On interviews: walk through a zero-downtime column rename or type change; mention migration tools (Flyway, Liquibase, Prisma, Alembic) as mechanics, not substitutes for compatibility design.

Common pitfalls: blocking `ALTER` on huge tables; deploy order violations; irreversible data transforms without backup; migrations that assume empty tables; running untested down migrations in panic.

The trade-off is deployment safety and reversibility versus slower, multi-phase rollout and more application branching during transitions.

Checklist:

• Describe expand/contract migration phases.
• Plan backfill idempotency and batch size.
• Coordinate app deploy with schema version.
• Test on realistic volume and locks.
• Document rollback and data repair steps.