The Dilemma
The database is the heart of every enterprise application. It must evolve — new columns, changed constraints, new tables. But it can’t stop while doing so. In a world that expects 24/7 availability, “briefly offline for an update” is no longer an option.
Why Naive Migrations Fail
The Lock Problem
A simple ALTER TABLE users ADD COLUMN phone VARCHAR(50) sounds harmless. In PostgreSQL, it is — a lightweight operation. But ALTER TABLE orders ADD COLUMN total DECIMAL NOT NULL DEFAULT 0 on a table with 50 million rows? That can take minutes and holds an exclusive table lock in older MySQL versions.
The Compatibility Problem
Imagine: you rename a column (ALTER TABLE users RENAME COLUMN name TO full_name). The old application code looks for name. The new servers look for full_name. During deployment, both versions run simultaneously. Result: errors.
The Expand-Contract Method
The most proven pattern for zero-downtime migrations consists of three phases:
Phase 1: Expand
Add new things without removing old ones:
- Create new column (nullable, without NOT NULL)
- Create new table
- Create new index (CONCURRENTLY in PostgreSQL)
The old application continues running unchanged.
Phase 2: Migrate
Transfer data to the new structure:
- Backfill in batches (not everything at once)
- Dual-write: new application version writes to old AND new structure
- Validation: do old and new data match?
Phase 3: Contract
Only when all application instances use the new structure:
- Remove old columns/tables
- Add NOT NULL constraints retroactively
- Remove old indexes
Concrete Patterns
Adding a Column (Safe)
-- Phase 1: Add nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(50);
-- Phase 2: Backfill in batches
UPDATE users SET phone = '' WHERE phone IS NULL AND id BETWEEN 1 AND 10000;
-- ... more batches
-- Phase 3: Set constraint (after complete backfill)
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;Renaming a Column (Safe)
-- Phase 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(100);
-- Phase 2: Dual-write + backfill
-- Application writes to name AND full_name
UPDATE users SET full_name = name WHERE full_name IS NULL;
-- Phase 3: Drop old column (after complete deployment)
ALTER TABLE users DROP COLUMN name;Creating an Index (Safe)
-- CONCURRENTLY prevents table locks
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);Anti-Patterns
| Anti-Pattern | Risk | Alternative |
|---|---|---|
NOT NULL on new column immediately | Lock on large tables | Add nullable, then constrain later |
| Rename column directly | Incompatibility with old code | Expand-Contract |
Backfill large table in one UPDATE | Long transaction, lock, replication lag | Batch updates with pauses |
| Coupling migration and deployment | Rollback becomes impossible | Migration independent of deployment |
Index without CONCURRENTLY | Table lock during index creation | CREATE INDEX CONCURRENTLY |
Tooling
Proven tools for safe migrations:
- gh-ost (GitHub): Online schema changes for MySQL without locks
- pg-osc: Equivalent for PostgreSQL
- Flyway / Liquibase: Versioned migration scripts with rollback support
- pgroll: New tool from Xata for reversible PostgreSQL migrations
- strong_migrations (Rails): Linter that blocks unsafe migrations
Conclusion
Zero-downtime migrations are not a luxury but a necessity for any application that promises availability. The Expand-Contract pattern is simple to understand, but it requires discipline: every schema change is split into at least two steps, with a full deployment between them. Slower? Yes. Safer? Significantly.