Zero-Downtime Database Migrations: Patterns and Pitfalls

Every team I’ve worked with has a migration story. Usually it starts with a change that looked harmless in the pull request, a single ALTER TABLE that ran instantly in staging. Then it hit production, queued up behind a long-running report query, and every request that touched that table queued up behind it. Five minutes of silence, one very loud incident channel.

The reflex after an incident like that is to schedule migrations for 2am on a Sunday, announce a maintenance window, and treat schema changes as rare, dangerous events. That instinct is understandable and mostly wrong. Maintenance windows don’t make migrations safe. They make failures less visible, and they train the team to batch changes into bigger, riskier releases.

My narrower point is this: zero-downtime migrations aren’t a tool you buy or a flag you enable. They’re a small set of patterns, expand and contract, batched backfills, and staged cutovers, applied with discipline. Most schema changes can run at noon on a Tuesday if you sequence them properly. This post covers the patterns that make that true, and the specific places where each one stops being enough.

Key takeaways

• Downtime during migrations almost never comes from the data volume. It comes from lock contention: one blocked ALTER TABLE queues every query behind it.
• The expand and contract pattern is the backbone of zero-downtime changes: add the new shape, migrate readers and writers gradually, remove the old shape last.
• Backfills belong in application-level batches with pauses, never in one giant UPDATE, and always separated from the schema change that enables them.
• Dual writes are for engine or datastore migrations, not routine schema changes. They add real consistency risk and need verification tooling to be worth it.
• Set lock_timeout on every migration. A migration that fails fast and retries is an inconvenience. A migration that waits on a lock is an outage.

Why do “safe” schema changes still take your app down?

Because the danger isn’t the change, it’s the lock the change needs, and the queue that forms behind that lock. In Postgres, most ALTER TABLE variants take an ACCESS EXCLUSIVE lock on the table. The lock itself is often held for milliseconds. The problem is acquiring it. If any transaction is still reading the table, even a slow analytics query, your migration waits. And here’s the part that causes outages: while your migration waits, every new query on that table waits behind the migration. A one-second schema change parked behind a ninety-second report means ninety seconds where the table is effectively down.

 [PERSONAL EXPERIENCE] The lock-queue pileup behind a long-running read is the single most common migration outage pattern I’ve been paged for.

The reflex is to reach for a bigger maintenance window. The better move is to make the migration refuse to wait:

SET lock_timeout = '2s';

ALTER TABLE orders ADD COLUMN fulfilled_at timestamptz;
-- If the lock isn't acquired in 2s, this fails cleanly.
-- Retry in a loop instead of queueing behind slow reads.

With a short lock_timeout, the migration either grabs the lock in a quiet moment or fails fast and retries. Nothing piles up. Pair that with knowing which operations are actually cheap. Since Postgres 11, adding a column with a constant default is a metadata-only change; it doesn’t rewrite the table. Dropping a column is metadata-only too. What’s expensive is anything that rewrites or scans every row while holding a strong lock: changing a column’s type, adding NOT NULL the naive way, or adding an index without CONCURRENTLY.

Isn’t there a tool that just handles this? Partly. Linters like squawk and frameworks’ safe-migration plugins catch the obvious hazards, and I’d use them. But they can’t know that your orders table gets hit by a nightly batch job, or that your ORM wraps migrations in a transaction that makes CREATE INDEX CONCURRENTLY impossible. The lock model is the thing to actually understand.

Where’s the line? Lock hygiene gets you through additive changes on a single database. Once the change rewrites a huge table in place (a type change on a billion-row column, say), no timeout saves you, and you’ve left the world of clever ALTER statements. That’s when you build the new shape alongside the old one, which is the next pattern.

What is the expand and contract pattern?

Expand and contract means you never change the database and the application in one step. You expand the schema so old and new code both work, migrate traffic gradually, then contract by removing the old shape once nothing depends on it. It’s the backbone of every zero-downtime migration, because it removes the requirement that deploys and schema changes happen at the same instant.

Say you’re renaming users.name to users.full_name. A literal RENAME COLUMN is instant, but the moment it commits, every running instance of the old code starts throwing errors. Instead:

-- Expand: add the new column, nothing depends on it yet
ALTER TABLE users ADD COLUMN full_name text;

-- Application step 1: write to both columns, read from the old one
-- Application step 2: backfill old rows (see the next section)
-- Application step 3: read from the new column
-- Application step 4: stop writing to the old column

-- Contract: only after every reader and writer has moved
ALTER TABLE users DROP COLUMN name;

Each step is independently deployable and independently reversible. If reading from full_name surfaces a bug, you roll back one application deploy. The database never has to move in lockstep with the code, which is exactly what makes the whole thing boring, and boring is the goal.

The pitfall is skipping the contract phase. Teams do the expand, ship the new reads, and move on. Six months later there are two name columns, half the codebase writes to one, an old cron job writes to the other, and nobody remembers which is canonical. The contract step isn’t cleanup. It’s the point where the migration actually finishes. Put it in the same ticket.

Where’s the line? Expand and contract assumes both shapes can coexist in one database. When they can’t (you’re changing engines, splitting a service out with its own datastore, or re-sharding), coexistence has to happen across two systems, and that’s dual-write territory.

How do you backfill millions of rows without locking the table?

In small batches, from the application side, with pauses, and never in the same transaction as the schema change. The single-statement version is the trap:

-- The trap: one transaction, long row locks, WAL spike,
-- bloat, and replicas falling behind
UPDATE users SET full_name = name WHERE full_name IS NULL;

On a table with tens of millions of rows, that statement holds row locks for the duration, generates a wall of WAL that lags your replicas, and bloats the table badly enough that you’ll be thinking about it for weeks. The batched version does the same work without the drama:

-- Batched: short transactions, bounded impact
UPDATE users SET full_name = name
WHERE id IN (
  SELECT id FROM users
  WHERE full_name IS NULL
  ORDER BY id
  LIMIT 5000
);
-- Sleep 100ms, repeat until 0 rows updated.
-- Run it from a job you can pause, resume, and monitor.

 [UNIQUE INSIGHT] Treating the backfill as a product-grade job with pause/resume/metrics, rather than a script someone babysits, is what separates teams that migrate weekly from teams that migrate yearly.

Batch size is a dial, not a constant. Start small, watch replication lag and lock waits, and tune upward. The same thinking applies to indexes and constraints, where Postgres gives you non-blocking variants for exactly this reason:

-- Doesn't block writes; can't run inside a transaction.
-- If it fails, drop the INVALID index and rerun.
CREATE INDEX CONCURRENTLY idx_users_full_name ON users (full_name);

-- Add the constraint without scanning, validate later
-- with a weaker lock that doesn't block writes
ALTER TABLE users
  ADD CONSTRAINT users_full_name_not_null
  CHECK (full_name IS NOT NULL) NOT VALID;

ALTER TABLE users VALIDATE CONSTRAINT users_full_name_not_null;

Two pitfalls worth naming. CREATE INDEX CONCURRENTLY can’t run inside a transaction block, so if your migration framework wraps everything in one, you need its escape hatch. And when it fails, it leaves behind an INVALID index that still costs write overhead; you have to notice and drop it. Neither is a reason to avoid the tool. Both are reasons to check pg_indexes after it runs.

Where’s the line? Batched backfills work while the transformation is expressible in SQL and the table stays in one database. When every row needs enrichment from an external service, or the destination is a different system entirely, the backfill becomes a data pipeline problem with its own retry and idempotency design.

When do you actually need dual writes?

Only when the old and new datastores can’t share a schema: engine migrations, extracting a service with its own database, or a re-sharding that moves rows between clusters. For routine schema changes inside one database, dual writes are overkill, and I’ve watched teams reach for them out of caution and buy themselves a harder problem than the one they had.

The reason to be reluctant is consistency. The moment the application writes to two places, you own every failure mode between them. The second write can fail after the first commits. A retry can apply writes out of order. A crash between the two leaves silent drift. None of this shows up in the happy path, which is exactly why it’s dangerous.

If you genuinely are moving between systems, the sequence that works looks like this:

1. Dual write, old system canonical. New-store failures log loudly but never fail the request.
2. Backfill history into the new system, batched as above.
3. Verify continuously. A comparison job diffs the two stores and reports drift. This is the step teams skip, and it’s the one that makes the cutover safe rather than hopeful.
4. Move reads behind a flag, gradually, comparing results where the traffic matters.
5. Flip canonical, then stop writing to the old store once you’d bet the business on the new one. Because of the verification job, you’re not betting. You’re checking.

Notice how much of that is verification rather than writing. If you’re not prepared to build the comparison job, you’re not really prepared for dual writes. Change data capture (Debezium and friends, or logical replication for Postgres-to-Postgres moves) can replace application-level dual writes entirely, and it removes the ordering problem at the source, at the cost of running and understanding the CDC pipeline itself.

Where’s the line? Dual writes earn their complexity when the destination is a different system and the migration runs for weeks. If both shapes fit in one database, expand and contract does the same job with none of the consistency risk. And if your platform offers logical replication between the endpoints, prefer it: replaying one ordered log beats coordinating two writers.

How do you cut traffic over without a big bang?

By making the cutover a sequence of small, reversible steps behind flags, with reads moving before writes and canary traffic moving before everything. The big-bang cutover, where a deploy simultaneously switches all reads and writes, is where otherwise careful migrations fail, because it recreates the exact coupling the earlier patterns removed.

 [PERSONAL EXPERIENCE] The migrations that went smoothly for me shared one habit: reads moved first, one consumer at a time, days before any write moved. The read path told us everything the verification job missed.

The order matters. Move reads first because they’re safe to roll back; flipping a read flag back has no data consequences. Start with internal or low-stakes consumers, compare results against the old path where you can, and widen gradually. Writes move last, after the read path has been quietly correct for long enough that flipping feels boring rather than brave.

Two details decide whether this goes well. First, rollback must be a flag flip, not a redeploy. If reverting takes fifteen minutes of CI, you don’t have a rollback, you have a second incident. Second, define what “correct” means before the cutover: which comparison job, which error rate, which lag metric. Deciding mid-incident whether a discrepancy matters is how small problems become long nights.

What about the database-level cutover, pointing the application at a new primary? Same principles, sharper edges. Connection poolers like PgBouncer can pause and resume traffic to absorb the switch, and managed offerings have their own switchover tooling. The application-side requirement doesn’t change: retries around the pause window, and idempotent writes so a retried request doesn’t double-apply.

Where’s the line? Staged cutovers assume the two paths can serve overlapping traffic while you compare them. Where the switch is genuinely atomic (a DNS-level primary swap, a single writer that can’t be split), your lever isn’t gradualism, it’s shrinking the pause window and rehearsing the switch until the runbook is muscle memory.

So how do you actually decide?

Four questions sort almost every migration into the right pattern.

1. Does the change rewrite or scan the whole table under a strong lock? If no (additive columns, metadata-only changes), it’s a lock-hygiene problem: lock_timeout, retries, done. If yes, you’re building the new shape alongside the old, which means expand and contract.

2. Can old and new code both work against the intermediate schema? If yes, expand and contract with a batched backfill covers it. If no, redesign the intermediate steps until the answer is yes. This is almost always possible within one database, and it’s cheaper than any alternative.

3. Is the destination a different system? Only then do dual writes or CDC enter the picture, and the verification job is part of the estimate, not an optional extra.

4. What does rollback look like at each step? If the answer at any step is “restore from backup,” the plan isn’t finished. Every step should roll back with a flag flip or a single reversible statement.

Read the answers together and a shape emerges: most migrations are question one or two, and they’re routine. The expensive patterns exist for question three, and the discipline in question four is what keeps all of them honest.

Migrations are deployments, not events

The practical default: treat every schema change like a code deploy. Small, frequent, reversible, shipped through the same pipeline with the same review. lock_timeout on everything, expand before contract, backfills in batches, reads before writes. Teams that do this migrate on Tuesday afternoons and nobody notices, which is the entire point.

The cautionary version is the team that batches schema changes into quarterly maintenance windows. Each window carries more change than the last, each one is riskier, and the fear that caused the batching gets reconfirmed every time something slips. The 2am window doesn’t reduce risk. It concentrates it.

Zero downtime isn’t a property of your database. It’s a property of how you sequence changes to it. Learn the patterns, respect the pitfalls, and the migration stops being the story anyone tells afterwards.

Frequently asked questions

• What causes downtime during database migrations?

Lock contention, far more often than data volume. A schema change waiting for an exclusive lock queues every subsequent query behind it, so one slow read plus one blocked ALTER TABLE can freeze a table for minutes. Setting a short lock_timeout and retrying prevents the pileup that turns a fast change into an outage.

• What is the expand and contract migration pattern?

It’s a sequence where you add the new schema shape first, run old and new code side by side, migrate reads and writes gradually, and remove the old shape only when nothing depends on it. Each step deploys and rolls back independently, so the database never has to change in lockstep with application code.

• How do you backfill a large table without downtime?

Run the backfill as an application-level job in small batches, a few thousand rows per transaction, with short pauses between batches. Monitor replication lag and lock waits, and tune batch size accordingly. Avoid one giant UPDATE: it holds locks for the duration, lags replicas, and bloats the table.

• Are dual writes necessary for zero-downtime migrations?

No, not for schema changes within one database; expand and contract handles those with less risk. Dual writes only make sense when moving between different systems or engines, and they require a continuous verification job to detect drift. Where logical replication or CDC is available, prefer it over application-level dual writes.

• Should database migrations run during a maintenance window?

Usually no. Well-sequenced migrations using lock timeouts, expand and contract, and batched backfills run safely during normal traffic. Maintenance windows encourage batching many changes into one risky event. Reserve them for the rare genuinely atomic switch, like a primary failover that can’t be staged gradually.