DEV Community

Cover image for How zero-downtime database migrations work
binadit
binadit

Posted on • Originally published at binadit.com

How zero-downtime database migrations work

Zero-downtime database migrations: a practical breakdown

You ship a schema change on a Friday afternoon. Thirty seconds later, error rates spike across every pod running the old deploy. Nothing was technically wrong with your SQL; the problem was timing, not syntax. This is the scenario zero-downtime migration practices exist to prevent.

The real problem isn't the migration

Zero-downtime doesn't mean the migration completes instantly. It means your app keeps serving correct reads and writes while the schema shifts from old to new. Most migration failures trace back to one of three things:

  • Lock contention on the table you're altering
  • Application code assuming a schema state that no longer exists
  • A cutover step that briefly knocks the primary offline

The fix starts with a mental shift: treat migrations as a sequence of small, reversible steps, not one big-bang deployment.

What's happening under the hood

Your app servers carry a schema assumption baked into ORM models and prepared statements. The database holds the actual schema. During a migration, you're moving both in sync, and there has to be no moment where they disagree in a way that breaks a query.

Schema changes generally fall into three buckets:

  • Additive (new column, table, index): usually safe, but can still lock depending on the engine and default value behavior
  • Destructive (dropping a column, renaming a table): dangerous if any live code still references the old structure
  • Transformative (changing a column type, splitting a table): requires both old and new structures to coexist temporarily

The pattern: expand-contract

Also called parallel change. Four phases:

  1. Expand: add the new element alongside the old one. Nothing removed yet.
  2. Migrate: backfill data into the new structure, dual-write from the app, or read-with-fallback.
  3. Verify: confirm consistency under real production traffic, not staging.
  4. Contract: once every app instance is confirmed on the new structure, drop the old one.

This works because at every step, both the old and new app deploy can run against the schema without erroring. That matters because deploys are rolling, not instant; you might have two code versions hitting the same DB for 30-90 seconds. If your migration doesn't tolerate that overlap, you'll see error spikes that look random but are completely deterministic.

Locking details worth knowing

In PostgreSQL (11+), adding a column with a constant default is metadata-only; milliseconds regardless of table size. Adding a column with a non-constant default, or a NOT NULL constraint without a default, can force a full table rewrite under an ACCESS EXCLUSIVE lock, blocking everything.

MySQL's InnoDB has a similar split: some ALTER TABLE operations use INSTANT or INPLACE algorithms with no locking; others fall back to COPY, rebuilding the whole table.

Walking through a real example

Say you're renaming status to order_status on a 40 million row orders table.

What breaks:

ALTER TABLE orders RENAME COLUMN status TO order_status;
Enter fullscreen mode Exit fullscreen mode

Fast in Postgres, metadata-only. But any app server still on the previous deploy referencing status starts throwing "column does not exist" the instant this commits. Across 12 pods mid-rollout, that's a guaranteed multi-minute error spike.

Expand-contract version:

-- Step 1: expand
ALTER TABLE orders ADD COLUMN order_status varchar(20);

-- Step 2: backfill in batches, avoid long-running transactions
UPDATE orders SET order_status = status
WHERE id BETWEEN 1 AND 50000 AND order_status IS NULL;
-- repeat in batches of 50k

-- Step 3: dual-write in app code so new rows populate both columns

-- Step 4: deploy code that reads order_status, falls back to status,
-- for one full release cycle

-- Step 5: after zero fallback reads in logs for 7 days
ALTER TABLE orders DROP COLUMN status;
Enter fullscreen mode Exit fullscreen mode

Batch size matters at this scale. One giant UPDATE across 40M rows generates enough WAL/binlog volume to cause replication lag, which cascades into stale reads on any replica-dependent service. Batches of 10k-50k with short pauses keep lag under a second and avoid blocking autovacuum.

For bigger transformative changes (splitting a users table into users and user_profiles at 200M rows), expect this rough timeline:

Phase Duration Risk
Expand Minutes Low
Backfill 4-10 hours Low, if throttled
Dual-write period 1-2 weeks Medium, needs monitoring
Read cutover + verify 3-5 days Medium
Contract Minutes Low, if verification passed

More elapsed time, dramatically smaller blast radius. That's the trade.

The trade-offs, honestly

  • Dual-write complexity: two write paths double your bug surface. A missed dual-write is the most common cause of silent data drift.
  • Longer total timeline: a 10-minute blocking migration can become a multi-week incremental one. For low-traffic internal tools, this often isn't worth it.
  • Replica lag: even throttled backfills add write volume that can measurably increase lag.
  • Rollback complexity: a single ALTER TABLE rolls back trivially. A multi-phase migration needs a rollback plan per phase.

Tooling notes

Online schema change tools like gh-ost or pt-online-schema-change for MySQL automate much of this by building a shadow table, replaying changes via triggers/binlog, and swapping atomically at the end. They save manual effort but add operational overhead: monitoring the copy, watching for lag, handling mid-copy failures on huge tables.

For PostgreSQL, CREATE INDEX CONCURRENTLY and instant column additions have reduced the need for third-party tools in many cases. Large rewrites (changing a column type) still often need manual expand-contract or extension support.

Read the full breakdown here: Zero-downtime database migrations

Originally published on binadit.com

Top comments (0)