Database migration safety checklist

Database migrations can cause downtime, data loss, or application errors if not handled carefully.

Follow this safety checklist for every migration:

Before writing:

• Can this migration run while the old code is still serving?
• Is it backward compatible with the current application version?

Schema changes:

• ADD COLUMN: safe (use DEFAULT for NOT NULL columns)
• DROP COLUMN: deploy code change first, then drop column
• RENAME COLUMN: never - add new, migrate data, update code, drop old
• ADD INDEX: use CREATE INDEX CONCURRENTLY (PostgreSQL)
• Change type: add new column, backfill, swap in code, drop old

Data migrations:

• Run in batches (1000-10000 rows at a time)
• Use transactions per batch, not one giant transaction
• Add progress logging
• Test on production-sized dataset first

Deployment order:

1. Deploy code that handles both old and new schema
2. Run forward migration
3. Deploy code that only uses new schema
4. Run cleanup migration (drop old columns)

Always have:

• Tested rollback procedure
• Backup taken before migration
• Migration tested on staging with production data volume

Database migrations are one of the riskiest deployment operations. A bad migration can cause extended downtime that code rollbacks alone cannot fix.

Any application with a relational database that needs schema changes