Zero-downtime database migrations require a multi-phase approach where you expand the schema first, migrate data in the background, switch application code, and contract the old schema after validation. This guide covers the expand-contract pattern, online DDL tools, backfill strategies, and rollback procedures with production-tested examples for PostgreSQL and MySQL.
A 2025 Percona survey found that 34% of production outages in database-backed applications are caused by schema migrations. The primary failure modes are table locks during ALTER TABLE, application errors from schema-code mismatches, and failed rollbacks that leave the database in an inconsistent state. The expand-contract pattern eliminates all three.
---
The Expand-Contract Pattern
The expand-contract pattern splits every migration into three deployable phases that can each be rolled back independently.
| Phase | Action | Rollback |
| Expand | Add new columns/tables, keep old ones | Drop new columns |
| Migrate | Backfill data, dual-write in application | Stop dual-write |
| Contract | Drop old columns/tables | Not needed (data preserved) |
Phase 1: Expand (Add New Schema)
-- Example: Rename column "username" to "display_name"
-- Phase 1: Add new column alongside old one
ALTER TABLE users ADD COLUMN display_name VARCHAR(255);
-- This is a non-blocking operation in PostgreSQL
-- The old "username" column remains active
Phase 2: Migrate (Backfill + Dual-Write)
-- Backfill existing data
UPDATE users SET display_name = username
WHERE display_name IS NULL;
-- For large tables, batch the update to avoid locks
-- COMMIT inside a procedure requires PostgreSQL 11+, and the CALL must run
-- outside an explicit transaction block (not inside BEGIN ... or a
-- migration tool's wrapping transaction)
CREATE OR REPLACE PROCEDURE backfill_display_name(batch_size INT DEFAULT 10000)
LANGUAGE plpgsql
AS $$
DECLARE
rows_updated INT;
BEGIN
LOOP
UPDATE users SET display_name = username
WHERE id IN (
SELECT id FROM users
WHERE display_name IS NULL AND username IS NOT NULL
LIMIT batch_size
);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
EXIT WHEN rows_updated = 0;
COMMIT; -- Release locks before sleeping
PERFORM pg_sleep(0.1); -- Throttle to reduce load
END LOOP;
END;
$$;
CALL backfill_display_name();
<h1>Application code: dual-write during migration</h1>
async def update_user_name(user_id: int, name: str):
await conn.execute(
"""UPDATE users
SET username = $1, display_name = $1
WHERE id = $2""",
name, user_id
)
Phase 3: Contract (Remove Old Schema)
-- Only after all application code uses display_name
-- and backfill is 100% complete
ALTER TABLE users DROP COLUMN username;
-- Add NOT NULL constraint after verification
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;
---
Online DDL Tools
For large tables (millions of rows), even adding a column can lock the table in MySQL. Online DDL tools perform migrations without locking.
PostgreSQL: Lock-Free Operations
PostgreSQL handles most DDL operations without full table locks. Key exceptions and workarounds:
-- Adding a column with a default value (PostgreSQL 11+)
-- This is instant, no table rewrite
ALTER TABLE orders ADD COLUMN priority INTEGER DEFAULT 0;
-- Adding an index concurrently (no lock)
CREATE INDEX CONCURRENTLY idx_orders_priority
ON orders (priority);
-- Adding a NOT NULL constraint with validation
-- Step 1: Add constraint as NOT VALID (instant)
ALTER TABLE orders ADD CONSTRAINT orders_priority_nn
CHECK (priority IS NOT NULL) NOT VALID;
-- Step 2: Validate in background (no lock)
ALTER TABLE orders VALIDATE CONSTRAINT orders_priority_nn;
MySQL: pt-online-schema-change
<h1>Percona Toolkit for MySQL schema changes</h1>
pt-online-schema-change \
--alter "ADD COLUMN priority INT DEFAULT 0" \
--execute \
--chunk-size=1000 \
--max-lag=1s \
D=myapp,t=orders
| Operation | PostgreSQL | MySQL (InnoDB) |
| ADD COLUMN (no default) | Instant, no lock | Instant (8.0+) |
| ADD COLUMN (with default) | Instant (11+) | Table copy (use pt-osc) |
| DROP COLUMN | Full rewrite | Table copy |
| ADD INDEX | CONCURRENTLY option | ALGORITHM=INPLACE |
| RENAME COLUMN | Instant | Instant (8.0+) |
| Change column type | Full rewrite | Table copy |
---
Rollback Procedures
Every migration must have a tested rollback script. The expand-contract pattern makes rollback straightforward because each phase is independently reversible.
-- Rollback script template
-- migration_014_rollback.sql
-- Verify current state before rollback
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;
-- Rollback Phase 1 (if Phase 2 not started)
ALTER TABLE users DROP COLUMN IF EXISTS display_name;
-- Rollback Phase 2 (if Phase 3 not started)
-- Stop dual-write in application, revert to reading username only
-- Rollback Phase 3: NOT POSSIBLE
-- Old column is gone - this is why Phase 3 requires
-- 100% confidence that the migration is complete
---
Migration Checklist
| Step | Action | Verification |
| 1 | Write expand migration SQL | Review with DBA |
| 2 | Write rollback SQL | Test in staging |
| 3 | Run expand in production | Verify schema change |
| 4 | Deploy dual-write code | Monitor error rates |
| 5 | Start backfill | Track completion percentage |
| 6 | Verify backfill complete | COUNT WHERE new_col IS NULL = 0 |
| 7 | Deploy code reading new column | Monitor query performance |
| 8 | Remove dual-write code | Verify old column unused |
| 9 | Run contract migration | Final schema verification |
| 10 | Archive rollback scripts | Keep for 30 days |
For connection management during migrations, see our database connection pooling guide. Teams choosing between PostgreSQL and MySQL should read our MySQL vs PostgreSQL comparison for migration-specific considerations. If you are running PostgreSQL with point-in-time recovery, our PostgreSQL backup and PITR guide covers how to create restore points before running migrations.
---
Frequently Asked Questions
How long should each migration phase last?
Phase 1 (expand) should be deployed and verified within one release cycle, typically 1-2 days. Phase 2 (migrate and dual-write) runs until backfill is 100% complete, which depends on table size. For tables under 10 million rows, this is usually 1-4 hours. For tables over 100 million rows, plan for 1-7 days of background migration. Phase 3 (contract) should happen within 2 weeks of Phase 2 completion to prevent schema drift.
What happens if the backfill fails midway?
Batched backfills are idempotent by design. If the process fails at row 500,000 of 1,000,000, simply restart it. The WHERE clause (display_name IS NULL) ensures already-migrated rows are skipped. Log the last processed batch ID so you can resume from the exact point of failure.
Can I run migrations in a transaction?
For DDL operations, PostgreSQL wraps each statement in an implicit transaction. However, wrapping large backfills in a single transaction is dangerous because it holds locks for the entire duration. Always use batched updates with explicit commits between batches.
How do I handle foreign key migrations?
Foreign key changes are the hardest zero-downtime migration. The expand-contract approach: (1) Add the new foreign key column without a constraint. (2) Backfill data. (3) Add the foreign key constraint as NOT VALID. (4) Validate the constraint in the background. (5) Drop the old column. Each step is independently deployable and reversible.
Should I use an ORM migration tool or raw SQL?
For simple column additions and index creation, ORM tools (Alembic, Django migrations, Prisma) work fine. For complex multi-phase migrations (expand-contract), write raw SQL. ORM tools generate single-phase migrations that cannot be split into expand, migrate, and contract phases. Use the ORM for schema tracking but customize the generated SQL.