Database schema migrations are one of the riskiest operations in production deployments. A failed or slow migration can cause outages lasting hours. Here are the patterns that make migrations safe and reversible.
Expand-Contract Pattern
The safest way to rename a column or change a type without downtime:
text
Phase 1 (Expand): Add new column, write to both old and new
Phase 2 (Migrate): Backfill new column from old column
Phase 3 (Contract): Deploy code reading only new column, drop old column
sql
-- Phase 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
-- App writes to both 'name' and 'full_name'
-- Phase 2: Backfill
UPDATE users SET full_name = name WHERE full_name IS NULL LIMIT 1000;
-- Repeat in batches until complete
-- Phase 3: Drop old column (after code no longer reads it)
ALTER TABLE users DROP COLUMN name;Backward-Compatible Migrations Only
- Never add a NOT NULL column without a DEFAULT in the same deploy
- Never rename columns — add new, migrate, drop old
- Never remove columns until all code references are gone
- Never change column types in place — use expand-contract
Batch Large Data Migrations
sql
-- Backfill in small batches to avoid long-running transactions
SET @batch_size = 1000;
SET @last_id = 0;
REPEAT
UPDATE users
SET new_col = derive_value(old_col)
WHERE id > @last_id AND new_col IS NULL
ORDER BY id
LIMIT 1000;
SELECT MAX(id) INTO @last_id
FROM users WHERE new_col IS NOT NULL;
DO SLEEP(0.05);
UNTIL ROW_COUNT() = 0 END REPEAT;Migration Tooling
- Flyway: Java-based, SQL-first, good for teams with DBAs
- Liquibase: XML/YAML/SQL, supports rollback scripts
- gh-ost / pt-osc: for ALTER TABLE on large tables
- Sqitch: Git-based change management with deploy/revert/verify
Always Test Rollback
bash
# Flyway rollback
flyway undo
# Liquibase rollback
liquibase rollbackCount 1
# Test your rollback in staging before running migration in productionKey Takeaways
- Use expand-contract for column renames and type changes — never in-place
- Always backfill in small batches with
SLEEPbetween iterations to avoid replication lag - Write rollback scripts before you run migrations — you will need them eventually
- Test migrations in a production-clone environment with production-size data
JusDB Can Help
Schema migration failures are a leading cause of production incidents. JusDB can review your migration strategy and help you implement a safe deployment process.