Zero-Downtime Postgres Migrations: Expand-Contract in Practice
Expand-contract patterns, lock budgets, and sequencing patterns for migrating large Postgres tables without freezing writes in production.
Locks are the enemy of uptime
Postgres migrations fail in production when they take AccessExclusiveLock on hot tables. Adding a column with a default on older versions, rebuilding indexes the wrong way, or rewriting a table under traffic will queue writes until customers notice.
We set lock budgets and statement timeouts on migration runners. If a lock cannot be acquired quickly, abort and retry with a safer plan rather than blocking checkout.
Practice the abort path in staging: deliberately fail a lock acquisition and confirm the runner exits cleanly without leaving half-applied state.
Expand-contract sequencing
Expand-contract means adding new structures first, dual-writing or backfilling, switching reads, then removing old columns. It takes more deploys than a big-bang ALTER, and it is how large tables move without a long maintenance window.
Each phase is independently reversible. That property matters more than clever SQL when something unexpected appears mid-migration.
Practice the abort path in staging: deliberately fail a lock acquisition and confirm the runner exits cleanly without leaving half-applied state.
Backfills that respect production
Backfills run in batches with sleeps, throttled concurrency, and progress metrics. We avoid long transactions that hold snapshots and bloat. For very large tables, shadow tables plus atomic rename patterns can outperform in-place rewrites.
Always verify row counts and checksum samples before cutting reads over. Silence is not success — instrumentation is.
Practice the abort path in staging: deliberately fail a lock acquisition and confirm the runner exits cleanly without leaving half-applied state.
Indexes, constraints, and cleanup
Create indexes CONCURRENTLY. Validate constraints in a second step after NOT VALID. Drop unused objects only after the application no longer references them and observability shows zero access.
Document the migration runbook next to the SQL. The next engineer on-call should know which phase you are in without reading git archaeology.
Practice the abort path in staging: deliberately fail a lock acquisition and confirm the runner exits cleanly without leaving half-applied state.
Document the phase you are in
Put the expand-contract phase, lock budget, and rollback step next to the migration SQL. The next on-call engineer should know whether dual-write is active without reading git archaeology.
That runbook habit is what makes zero-downtime Postgres migrations safe under real traffic.
Practice the abort path in staging: deliberately fail a lock acquisition and confirm the runner exits cleanly without leaving half-applied state.
Set lock budgets that abort safely
Migration runners should use short lock timeouts. If AccessExclusiveLock cannot be acquired quickly on a hot table, abort and retry with a safer plan instead of queueing checkout writes.
Adding columns with heavy defaults, rebuilding indexes incorrectly, or rewriting tables under traffic is how “simple” ALTERs become incidents.
Practice the abort path in staging: deliberately fail a lock acquisition and confirm the runner exits cleanly without leaving half-applied state.
Backfill in batches with progress metrics
Backfills need batch sizes, sleeps, and visible progress. Avoid long transactions that hold snapshots and bloat. Verify row counts and checksum samples before cutting reads to new columns.
For very large tables, shadow tables plus atomic rename can outperform in-place rewrites — but only with dual-write discipline during the transition.
Practice the abort path in staging: deliberately fail a lock acquisition and confirm the runner exits cleanly without leaving half-applied state.
Create indexes CONCURRENTLY and validate constraints later
Create indexes CONCURRENTLY. Add constraints as NOT VALID, then validate in a second step. Drop old columns only after the application no longer references them and metrics show zero access.
Expand-contract sequencing takes more deploys than a big-bang migration. It is also how large Postgres estates move without maintenance windows.
Practice the abort path in staging: deliberately fail a lock acquisition and confirm the runner exits cleanly without leaving half-applied state.
“Sustainable delivery comes from making the important trade-offs explicit, measurable, and recoverable.”
Checklist
- User-facing SLO and baseline captured
- Critical path traced end to end
- Data and dependency ownership documented
- Degraded behavior covered by tests
- Rollout and rollback plan reviewed
- Regression signal monitored after release
Umar Khalid
Software Engineering Manager
Manages day-to-day software engineering across APIs, web UI, and AI features — focused on reliable, maintainable production systems clients can own.
Let's build something
remarkable
Whether you need a web or mobile app with AI integrations, blockchain work, or a conversation about our AI products — tell us what you're building and we'll respond fast.
Blog questions
How we write, how often we publish, and how you can contribute or stay in the loop.
Blogs are written by Automative Tech’s engineering leadership — Muhammad Talha Zubair, Bilal Hassan, and Umar Khalid — based on production web, mobile, AI integration, and blockchain work.
We lead with custom web and mobile delivery with AI integrations — Next.js, React, React Native, Flutter, and LLM features. Selected posts also cover blockchain, AI products, and cloud when they support shipping real products.
A few deep pieces per month. We prioritize substance over cadence.
Yes with attribution and a link back to the original. For syndication, contact us for a simple agreement.
Occasionally, when the author has real production experience. Pitch a short outline via the contact form.
Follow the social links in the footer, or contact us to ask about engineering notes updates.