Expand-Contract Database Migration
Ship a schema change in backward-compatible phases so old and new application versions can run simultaneously during a rolling deployment.
Objective
Change a database schema without breaking in-flight requests or forcing a coordinated downtime window.
Environment
- PostgreSQL or another relational database
- A migration tool with versioned, ordered migrations
- Application instances that can run old and new code concurrently
- Backups and a tested restore procedure
Steps
1. Expand the schema
Add nullable columns, new tables, or new indexes without removing or renaming existing structures. Use concurrent index creation where supported.
Expected state: Old application versions continue to work unchanged.
2. Deploy dual-write or dual-read compatibility
Update the application to write both old and new fields and read with a fallback, then roll out gradually.
Expected state: New and old instances produce consistent data during the transition.
3. Backfill and verify
Backfill existing rows in bounded batches, monitor replication lag and lock waits, and verify counts and constraints.
Expected state: Every row has valid values in the new structure with no long-held locks.
4. Switch reads to the new structure
Change read paths and remove the fallback only after backfill verification and a soak period.
Expected state: Application correctness no longer depends on the old field.
5. Contract
In a later release, drop the old column or table and remove compatibility code after confirming no instance uses it.
Expected state: The schema is clean and the previous version is no longer deployable.
Decision points
Can the migration run online or does it require a maintenance window?
- Online expand/contract
- Maintenance window
Prefer expand/contract for tables with active traffic; use a window only when the operation genuinely cannot be made online.
How should backfill be throttled?
- Small batches with sleeps between them
- Single large transaction
Use bounded batches and monitor lag and locks; a single large transaction increases lock time and rollback cost.
Expected outcome
A schema change that is safe for mixed-version deployments, with backfilled data, verified constraints, and no unplanned downtime.
Verification
Run the migration on a production-like copy, measure lock waits and duration, verify row counts and constraints, and exercise both old and new application versions against the migrated schema.
Cleanup
Remove temporary triggers, dual-write code, backfill scripts, and feature flags after the contract phase.
Why one-step migrations break deployments
A rolling deployment runs old and new code at the same time. If a migration drops a column that the old version still reads, requests fail during the overlap. Expand/contract makes every phase compatible with both versions.
-- Expand: nullable, backward compatible
ALTER TABLE accounts ADD COLUMN display_name text;
-- Backfill in batches
UPDATE accounts
SET display_name = name
WHERE display_name IS NULL
AND id IN (SELECT id FROM accounts WHERE display_name IS NULL LIMIT 1000);Steps and gates
- Expand and deploy compatibility code.
- Backfill in bounded batches, watching lock waits and replication lag.
- Switch reads after verification.
- Contract only in a later release, after the old version is fully retired.
Verification
- Old and new application versions both pass integration tests against the migrated schema.
- Row counts and constraints match expectations.
- Lock waits and query latency stay within budget during backfill.
- A restore from backup succeeds on a production-like copy.
Rollback
Before the contract phase, rollback is a code change; the old schema remains. After the contract phase, rollback requires restoring the dropped structure from a backup or a reversible migration, so delay destructive steps until the new version is proven.