Database Migration Checklist
A database migration checklist for safe schema changes: lock checks, backups, expand and contract steps, backfills and verification. Use it for every migration.
- Typical schedule per schema migration
- The checklist 5 sections, 20 items
- Variables
{{database}}{{migration}} - Start from CI
Use this checklist for any schema change on a production database: new tables or columns, changed types, new indexes, renamed or dropped columns, and data backfills. It also works for a database engine upgrade, with the optional items. It assumes a relational database such as PostgreSQL or MySQL and a migration tool such as Flyway, Liquibase, Alembic, Rails or Prisma migrations, but the steps are tool neutral.
Who owns it
The engineer who wrote the migration owns the run. A second engineer who knows the database reviews the migration before it runs in production. For large tables, the person who runs the migration should stay until the verification steps are done.
Common pitfalls
- Locks you did not expect. Some changes rewrite the table or take an exclusive lock, and every query queues behind them. Check the lock behaviour of each statement for your database version, and set a lock timeout so a blocked migration fails fast instead of stalling the app.
- Renames and drops in one step. Old code still running during the deploy will break. Use expand and contract: add the new column, deploy code that writes both, backfill, switch reads, then drop the old column in a later release.
- Backfills in one transaction. Updating millions of rows at once causes long locks and replication lag. Backfill in small batches.
- Testing on an empty database. A migration that takes a second locally can take an hour on production data. Test on a recent, anonymised copy.
Runbook can create this run when a pull request adds a migration, so the review and backup steps are on record before the migration ever reaches production.
The checklist: Migrate {{database}}: {{migration}}
Review
-
Migration
{{migration}}is reviewed by a second engineer who knows{{database}} -
Each statement is checked for table rewrites and exclusive locks on large tables
Use online options where your database offers them, such as concurrent index builds in PostgreSQL or online DDL tools for MySQL.
-
The change is backwards compatible with the application version currently running
Old code must keep working against the new schema until the deploy is finished.
-
Destructive steps (drop, rename, type change) are split into a later release
-
A down migration or a written manual rollback exists, or it is documented why none is possible
Test
-
Migration runs cleanly on a recent, anonymised copy of production data
-
Run time on that copy is measured and noted in the run
-
Application tests pass against the migrated schema
-
Database engine upgrade is tested for changed defaults and deprecated features optional
Prepare production
-
Fresh backup or snapshot of
{{database}}is taken and its timestamp is noted -
Lock timeout and statement timeout are set for the migration session
-
Migration is scheduled outside peak traffic if it touches large tables
-
Disk space is sufficient for index builds or table copies
Run
-
Migration
{{migration}}is applied in production and its output is checked -
Replication lag on replicas stays acceptable during and after the migration optional
-
Data backfill runs in batches and its progress is tracked optional
Verify
-
Schema in production matches the expected state, such as columns, indexes and constraints
-
Application error rate and slow query log show no new problems
-
Row counts or checksums for changed tables match expectations
-
Follow-up ticket exists for the contract step that removes old columns or code optional
Checklist as Markdown
Start this checklist from your pipeline
When a pull request adds a migration file, POST /api/v1/runs with template "Migrate {{database}}: {{migration}}" and the file name as migration.
curl -X POST https://runbook.metastic.ch/api/v1/runs \
-H "Authorization: Bearer $RUNBOOK_TOKEN" \
-H "Content-Type: application/json" \
-d '{"template": "Migrate {{database}}: {{migration}}", "variables": {"database": "…", "migration": "…"}}'Frequently asked questions
How do I run a database migration without downtime?
Make each migration backwards compatible with the code that is running, and avoid statements that lock large tables. Use the expand and contract pattern for renames and drops, build indexes online and backfill data in batches. Then you can deploy code and schema separately, and either can roll back.
Should every migration have a down migration?
A down migration is useful, but it is not always possible, for example after you drop data. What matters is that you know how to recover before you run the migration. That can be a down script, a restore from the backup you just took, or a forward fix you have already written.
What is the difference between a schema migration and a database upgrade?
A schema migration changes the structure of your own tables. A database upgrade changes the engine version, such as a new PostgreSQL or MySQL major version. Upgrades bring changed defaults, deprecated features and extension updates, so test them on a copy first, and plan the cutover like a small go-live.
Updated