← Back to Fixes
Zero-Downtime Postgres Migrations: Problem to Fix Production Guide
Use phased rollout strategy for PostgreSQL schema changes with lock budgets, backfills, and safe cutover.
Tweetable Insight#
Safe migrations are staged product changes, not one SQL command.
One-Sentence Definition#
Zero-downtime migrations are phased schema changes that preserve read/write availability during deploys.
Production Risk Warning#
A single blocking DDL can freeze critical API paths and billing events.
Problem (Search Intent First)#
Migration passes locally but causes lock contention or failures in production.
Why It Happens#
Data volume, live writes, and lock behavior are underestimated.
Production-Grade Fix#
Adopt expand/migrate/contract with explicit rollback points.
Copy-Paste Solution#
sql
alter table public.accounts add column plan_code text;
-- backfill in batches before switching reads/writes
Edge Cases#
- Long-running transactions can hold locks unexpectedly.
- New constraints should be added after data cleanup and validation.