Guide

Zero downtime database migration: a practical guide

A zero downtime database migration changes your schema while the application keeps serving reads and writes. Instead of one big locking ALTER, you split the change into small, reversible, backwards-compatible steps. This is the approach the Node Sync dashboard simulates across tenants.

1. The expand-contract pattern

Every breaking change becomes three phases, each deployable on its own:

  1. Expand: add new columns or tables without removing anything. Old and new code both work.
  2. Migrate: backfill data and switch the application to read and write the new shape.
  3. Contract: once nothing uses the old shape, drop it.
-- expand
ALTER TABLE users ADD COLUMN full_name text;           -- nullable, no default rewrite
-- app writes both name + full_name (dual write)
-- migrate: backfill (see below), then read from full_name
-- contract
ALTER TABLE users DROP COLUMN name;

2. Shadow tables and atomic swaps

When a change would rewrite the whole table (type changes, re-partitioning), build a shadow copy, keep it in sync with triggers or change data capture, then swap names in one short transaction.

CREATE TABLE orders_new (LIKE orders INCLUDING ALL);
-- alter orders_new freely; sync writes via trigger
BEGIN;
  SET LOCAL lock_timeout = '2s';
  ALTER TABLE orders RENAME TO orders_old;
  ALTER TABLE orders_new RENAME TO orders;
COMMIT;

A strict lock_timeout means a busy table aborts the swap rather than queueing every query behind it, so you can retry safely.

3. Batched backfills

Never update millions of rows in one statement. Walk the primary key in small batches so locks stay short and replicas keep up.

UPDATE users SET full_name = name
WHERE id > $last_id AND id <= $last_id + 5000
  AND full_name IS NULL;
-- sleep briefly, check replication lag, repeat

4. Lock-free writes during the migration

Application writes keep flowing. Optimistic concurrency (compare-and-swap on a version column) avoids holding row locks; conflicting transactions retry a bounded number of times and are aborted cleanly. See it in the transaction queue simulator.

5. Multi-tenant rollout and rollback

  • Migrate tenants in waves (canary first), watching replication lag and error rates.
  • Keep every step reversible until contract: rolling back is just pointing the app at the old shape.
  • Log every cut-over and rollback per tenant. Browse a live example in the rollback logs.

Checklist

  • New columns are nullable or have cheap defaults.
  • Indexes are built concurrently.
  • Every DDL statement has a lock timeout.
  • Backfills are batched and resumable.
  • Old code and new code both run against every intermediate schema.