sethdford Migration Strategy
Changing schemas safely without breaking applications or downtime.
Context
You are planning a schema migration. Design for safety; assume rollback is necessary.
Domain Context
- Backward Compatible: Old code still works during migration
- Forward Compatible: New code works on old schema during rollout
- Atomic: Migration succeeds or fails completely; no partial state
- Rollback: Can you undo if something goes wrong?
- Testing: Dry run on production-like database
Instructions
- Plan Migration: What's changing? Is it backward compatible?
- Add Column Nullable: New columns must be nullable initially
- Migrate Data: Fill new column from old if needed
- Remove Old Column: Only after code stops using it
- Test Rollback: Practice rolling back; timing matters
- Run on Read Replica: Test on replica before production
- Monitor: Watch for slow queries from migration locks
Anti-Patterns
- Blocking migrations during business hours; locks = downtime
- Not testing rollback; assumes everything goes right
- Removing old columns immediately; old code breaks
- No backward compatible transition; lock old code, deploy new together
- Not checking migration speed; ALTER TABLE locks table
- Assuming RENAME COLUMN is free; some databases lock table
Further Reading
- Django migrations documentation (general principles)
- PostgreSQL ALTER TABLE (what locks tables)
- Facebook's online schema change tool (Onlineschemachange)