How to perform a zero-downtime database schema migration

What and why

Changing a schema while users are online risks errors if old and new code disagree about the shape of data. The expand-and-contract pattern splits a breaking change into small, backward-compatible steps so the application keeps working at every moment. This is essential for renaming columns, splitting tables, or changing types under live traffic.

Prerequisites

• A production database serving requests you cannot stop.
• A pipeline that can ship application code in stages.
• The ability to add nullable columns and run background jobs.

Steps

1. Plan backward-compatible changes

Never rename or drop in one move. Decompose, for example, renaming name to full_name into: add full_name, write both, backfill, read new, drop name.

2. Expand the schema

Add the new structure without removing the old. Adding a nullable column is cheap and non-blocking:

ALTER TABLE users ADD COLUMN full_name TEXT;

Avoid NOT NULL with a default on huge tables in one statement; on older engines it rewrites the table.

3. Dual-write from the app

Deploy code that writes to both old and new columns so new rows are always correct:

UPDATE users SET name = $1, full_name = $1 WHERE id = $2;

Reads still use the old column at this stage.

4. Backfill in batches

Copy existing rows in small chunks to avoid long locks and replication lag:

UPDATE users SET full_name = name
WHERE full_name IS NULL AND id BETWEEN $1 AND $2;

Loop over id ranges with short pauses, monitoring lag.

5. Switch reads to the new shape

Once backfill completes and all new rows are dual-written, deploy code that reads full_name. Keep dual-writes until this version is fully rolled out.

6. Contract the old schema

After the read switch is stable, stop writing the old column, then drop it in a final migration:

ALTER TABLE users DROP COLUMN name;

Verification

At each stage, confirm error rates stay flat and both code versions function during rollout. After backfill, check SELECT count(*) FROM users WHERE full_name IS NULL returns zero. After contract, confirm no code references the dropped column.

Next Steps

Apply the same pattern to type changes and table splits, add a feature flag to gate the read switch, and automate batched backfills as idempotent, resumable jobs.