Database migrations without downtime
A migration that takes an exclusive lock at deploy time is a scheduled outage wearing a deploy badge. Zero-downtime Postgres migrations use expand/contract: add schema before removing it, backfill before enforcing constraints, and never block reads on the hot path.
Deploy Friday. Migration runs. ALTER TABLE orders ADD COLUMN status_v2 VARCHAR(20) NOT NULL DEFAULT 'pending' — except the table has forty million rows and Postgres rewrites the heap under an ACCESS EXCLUSIVE lock. Checkout stops for eleven minutes. The team calls it a deploy, not an outage. Customers call it something else. Postgres zero-downtime migration is not a tool feature — it is a discipline: expand schema alongside old, migrate data asynchronously, contract only after proof, and treat lock duration as a release blocker.
Production does not pause for schema changes. Applications roll continuously. Old and new code versions run simultaneously during deploys. Migrations must work while both versions are live — backward compatible on the way up, forward compatible until contract phase completes.
Lock modes decide whether deploy is migration or outage
Postgres DDL acquires lock levels that block concurrent access:
| Lock level | Blocks reads? | Blocks writes? | Typical DDL |
|---|---|---|---|
ACCESS SHARE | No | No | CREATE INDEX CONCURRENTLY start |
ROW EXCLUSIVE | No | No | INSERT, UPDATE, DELETE |
SHARE UPDATE EXCLUSIVE | No | Brief write block | VACUUM, CREATE INDEX CONCURRENTLY |
ACCESS EXCLUSIVE | Yes | Yes | ALTER TABLE most forms, DROP |
Any migration requiring ACCESS EXCLUSIVE on a hot table during peak traffic is an outage plan. Measure lock wait in staging with production-shaped row counts — not empty tables.
A NOT NULL column added in one transaction on a large table is a lock, not a migration strategy.
Expand/contract is the zero-downtime migration pattern
Three phases, often across multiple deploys:
Expand. Add new schema without breaking old code.
- Add nullable column — old code ignores it.
- Add new table alongside old — dual-write or async sync.
- Add new index
CONCURRENTLY— no table lock for reads/writes. - Add trigger or generated column for transition period.
Migrate. Move data and traffic.
- Backfill new column in batches —
UPDATE ... WHERE id BETWEEN ? AND ? LIMIT 1000with sleep between batches. - Dual-write: application writes to both old and new columns/tables.
- Switch reads to new path via feature flag when backfill complete.
- Validate with reconciliation queries — counts, checksums, sample diffs.
Contract. Remove old schema only after proof.
- Drop old column — only when no code reads or writes it.
- Drop old table — only after traffic at zero for sustained period.
- Add
NOT NULLconstraint — only after backfill confirms no nulls.
Deploy 1 (expand): ADD COLUMN email_normalized TEXT NULL
Deploy 2 (migrate): backfill job + app dual-write
Deploy 3 (migrate): app reads email_normalized; flag on
Deploy 4 (contract): DROP COLUMN email_legacy — after N days zero traffic
The rollback strategy for deploys article covers what happens when migrate-phase deploys fail — expand-phase rollback is often app revert while schema stays; contract-phase rollback may be impossible without forward-fix.
High-risk operations and their safe alternatives
| Risky migration | Why it locks or blocks | Safe pattern |
|---|---|---|
ADD COLUMN ... NOT NULL on large table | Rewrites table, exclusive lock | Add nullable → backfill → SET NOT NULL with check constraint in steps |
CREATE INDEX (non-concurrent) | SHARE lock blocks writes | CREATE INDEX CONCURRENTLY |
| Column type change | Rewrite, exclusive lock | New column + backfill + rename swap in contract |
ADD FOREIGN KEY | Validates all rows, strong lock | NOT VALID constraint first → VALIDATE CONSTRAINT separately |
Table rewrite (ALTER ... TYPE) | Full lock | New table + copy + swap views or routing |
| Renaming column | Breaks old code instantly | Add new, dual-write, deprecate old name in code first |
CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Migration tools that wrap everything in transactions need special handling for concurrent index creation.
Backfill without melting the database
Batch backfills protect production:
-- Repeat until zero rows affected
UPDATE orders
SET status_v2 = map_status(status_legacy)
WHERE id IN (
SELECT id FROM orders
WHERE status_v2 IS NULL
LIMIT 5000
FOR UPDATE SKIP LOCKED
);Patterns:
- Batch size tuned to I/O — monitor replication lag on replicas during backfill.
SKIP LOCKED— avoid fighting live writes for same rows.- Off-peak scheduling — backfill is production load.
- Progress tracking — rows remaining, ETA, pause switch for incidents.
- Idempotent backfill — safe to restart;
WHERE new_col IS NULLguard.
Backfill jobs compete with live traffic for I/O and connection pool slots. Coordinate with connection pooling in serverless — long-running backfills through the same pool as request handlers starve application queries.
Application compatibility matrix per deploy
Every migration deploy needs a written matrix:
| Deploy phase | Old app (N) | New app (N+1) | Schema state |
|---|---|---|---|
| Expand | Works | Works | New nullable columns exist |
| Dual-write | Writes old col only | Writes both | Both columns populated over time |
| Read switch | Reads old | Reads new | Backfill complete |
| Contract | Must be gone | Works | Old column dropped |
If N and N+1 must run simultaneously — always true during rolling deploys — schema changes must not break N. Contract phase requires proof that N is no longer running.
How should teams run zero-downtime Postgres migrations?
These practices separate expand/contract discipline from lock-and-pray deploys.
Can all migrations be zero-downtime?
No. Some changes — splitting a heavily contended table, fundamental model rewrites — require maintenance windows or read-only mode. The goal is zero-downtime by default, maintenance window by explicit decision with stakeholder notice — not by accident when a lock hits production.
How many deploys should expand/contract take?
As many as needed for safety. Four-deploy sequences are normal. Rushing contract before backfill completes creates data corruption, not speed.
When is a maintenance window acceptable?
Low-traffic periods for operations that genuinely cannot be made concurrent — rare with Postgres tooling. Document RTO, notify customers, rehearse. Do not use maintenance windows to avoid learning CREATE INDEX CONCURRENTLY.
A common argument runs the other way
The opposing view holds that zero-downtime migrations add complexity disproportionate to outage risk — that small teams should accept brief locks and ship faster.
Brief locks on small tables are fine. Brief locks on million-row hot tables are not brief. Complexity of expand/contract is front-loaded and documented; complexity of unplanned eleven-minute outages is reactive and customer-facing. Teams that measure lock duration in staging rarely choose the lock.
Key takeaways
- Zero-downtime migrations use expand/contract across multiple deploys — never big-bang DDL on hot tables.
ACCESS EXCLUSIVElocks on production tables are outage plans — measure in staging with real row counts.- Add nullable columns first; backfill in batches; enforce NOT NULL only after backfill completes.
CREATE INDEX CONCURRENTLYandNOT VALIDforeign keys avoid blocking validation locks.- Write app compatibility matrix per deploy — old and new code run simultaneously during rolling deploys.
- Backfill is production load — batch, monitor replica lag, coordinate with connection pools.
Conclusion
Database migrations without downtime are a contract with running production: schema changes arrive compatible with the code currently serving traffic, data moves on schedules that respect I/O limits, and destructive contract steps wait for proof.
The next migration should be classified before anyone writes SQL: expand, migrate, or contract? If the answer is "all three in one transaction," send it back for redesign. Production does not pause — migrations should not ask it to.