Zero-Downtime Migrations

Zero downtime means old and new application versions run simultaneously against the same schema. Every change must therefore be compatible in both directions for the duration of the deploy.

Expand and contract

Four phases, each shipped separately:

1. Expand. Add the new structure. The old code ignores it.
2. Dual write. New code writes both old and new. Old code still works.
3. Backfill. Copy historical data into the new structure, in batches.
4. Contract. Once no code reads the old structure, remove it — usually a release later.

Each phase is independently reversible, which is the whole point: at no moment does a rollback require restoring data.

Renaming a column

A rename is not a rename. ALTER TABLE … RENAME COLUMN breaks every running instance of the old code instantly.

-- 1. Expand
ALTER TABLE users ADD COLUMN email_address text;
 
-- 2. Deploy code that writes both columns and reads the old one.
 
-- 3. Backfill in batches, checking replication lag between batches.
UPDATE users SET email_address = email
WHERE email_address IS NULL AND id BETWEEN $1 AND $2;
 
-- 4. Deploy code that reads the new column.
-- 5. Contract, one release later.
ALTER TABLE users DROP COLUMN email;

Changing a column type

Treat it as a rename with a conversion: add the new column with the new type, dual write with the cast, backfill, switch reads, drop. An in-place ALTER TYPE on a large table rewrites it under an exclusive lock.

Adding a NOT NULL column with a default

On modern PostgreSQL and MySQL 8.0 this is a metadata-only operation for non-volatile defaults. On older versions it rewrites the table. Confirm behaviour for your version; when in doubt, add the column nullable, backfill, then add the constraint as NOT VALID and validate separately.

Adding an index

-- PostgreSQL: does not block writes, but cannot run inside a transaction.
CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);
 
-- Verify it succeeded; a failed concurrent build leaves an invalid index behind.
SELECT indexrelid::regclass AS index_name
FROM pg_index WHERE NOT indisvalid;

In MySQL, prefer ALGORITHM=INPLACE, LOCK=NONE and verify the server accepts it for that specific change; where it does not, use gh-ost or pt-online-schema-change.

Splitting a table

The same pattern at a larger scale, usually with change data capture doing the backfill: create the new table, dual write, backfill historical rows, verify counts and checksums, move reads across behind a feature flag, then stop writing the old table.

Verification before contract

Never drop the old structure on the assumption that nothing reads it. Confirm it:

• Check query statistics (pg_stat_statements, the slow query log) for references to the old column or table over a full business cycle — including monthly jobs.
• Grep the codebase and any reporting or BI tools, which are the usual forgotten consumers.
• Rename before dropping where possible: a rename is instantly reversible, a drop is not.