How do I achieve zero-downtime migrations?

Use the expand-contract pattern: add new schema alongside old, migrate data in background, switch reads, then clean up. Tools like gh-ost help with MySQL.

Should I migrate data in the same transaction as schema changes?

For small tables, yes. For large tables, separate schema changes from data backfills to avoid long-running transactions and locks.

How long should I keep backup before migration?

Keep pre-migration backups for at least two weeks after the migration is verified. Longer for regulated industries.

Database Migration Checklist

Migrate databases safely with a structured approach to schema changes, data validation, rollback planning, and zero-downtime execution.

Checklist: Database Migration (engineering)

Database migrations are among the riskiest operations in software development. A failed migration can cause data loss, extended downtime, and corrupted application state. This checklist covers the full lifecycle from planning through validation to ensure safe, reversible migrations.

Checklist Items

Create a complete backup before migration [critical]: Take a full database backup and verify it can be restored. Test restore on a separate instance.
Write and test rollback scripts [critical]: Create reverse migration scripts and test them against a copy of production data.
Validate data integrity after migration [critical]: Run row counts, checksum comparisons, and business logic validation queries to verify data accuracy.
Test migration on production-size dataset [important]: Run the migration against a dataset matching production volume to verify timing and resource usage.
Plan for zero-downtime if required [important]: Use expand-contract pattern, shadow tables, or dual-write strategies to avoid application downtime.
Coordinate with application deployment [important]: Ensure application code handles both old and new schema during the migration window.
Monitor database performance during migration [important]: Watch CPU, memory, disk I/O, and lock contention during migration execution.
Update ORM models and queries [recommended]: Ensure all application code, ORM mappings, and raw queries reflect the new schema.
Document the migration [recommended]: Record what changed, why, timing, and any issues encountered for future reference.
Schedule during low-traffic windows [recommended]: Run migrations during off-peak hours to minimize user impact and allow time for troubleshooting.

Common Mistakes

No rollback plan: Always write and test reverse migration scripts before running the forward migration.
Testing on small datasets only: A migration that runs in seconds on dev data may take hours on production. Test with production-scale data.
Dropping columns immediately: Use expand-contract: add new columns, migrate data, update application, then remove old columns in a later release.

Frequently Asked Questions

How do I achieve zero-downtime migrations?: Use the expand-contract pattern: add new schema alongside old, migrate data in background, switch reads, then clean up. Tools like gh-ost help with MySQL.
Should I migrate data in the same transaction as schema changes?: For small tables, yes. For large tables, separate schema changes from data backfills to avoid long-running transactions and locks.
How long should I keep backup before migration?: Keep pre-migration backups for at least two weeks after the migration is verified. Longer for regulated industries.