Md Nayeem Hossain | Software Engineer

Zero-Downtime Database Migrations with Expand and Contract#

The scariest deploys I have run were never about application code. They were schema changes: renaming a column on a table with forty million rows while traffic kept flowing. Application code can be rolled back in seconds. A dropped column cannot.

The technique that makes these changes boring is called expand and contract (also known as parallel change). This post walks through it with real examples.

Why migrations break production#

During a rolling deploy, old and new versions of your application run at the same time against the same database. If a migration renames users.name to users.full_name, every old instance still querying name starts failing the moment the migration runs. Rolling back the code does not help, because the column is already gone.

The rule that follows: every schema change must be compatible with both the current and the next version of the code.

The three phases#

Expand and contract splits one breaking change into several safe ones:

Expand — add the new structure alongside the old one. Nothing reads it yet.
Migrate — make the code write to both, backfill existing data, then switch reads to the new structure.
Contract — once nothing uses the old structure, remove it.

Each phase is its own deploy, and each one can be rolled back independently.

Example: renaming a column#

Deploy 1 — expand. Add the new column as nullable:

sql

1ALTER TABLE users ADD COLUMN full_name text;

Adding a nullable column without a default is a metadata-only change in PostgreSQL. It is instant even on huge tables.

Deploy 2 — dual write. The application writes both columns on every insert and update, but still reads the old one:

1await repo.update(id, { name: input.name, fullName: input.name });

Backfill. Copy existing data in small batches to avoid long locks and replication lag:

sql

1UPDATE users2SET full_name = name3WHERE id IN (4  SELECT id FROM users5  WHERE full_name IS NULL6  LIMIT 50007);

Run it in a loop with a short pause until it updates zero rows.

Deploy 3 — switch reads. Read full_name, keep writing both. Watch dashboards for a day.

Deploy 4 — contract. Stop writing name, then drop it:

sql

1ALTER TABLE users DROP COLUMN name;

Four deploys sounds like a lot for a rename. It is. That is the price of never having a broken window, and for core tables it is cheap insurance.

Dangerous operations and their safe versions#

Some DDL statements take locks that block reads or writes for the duration of the operation. On a busy table, that is an outage.

Adding an index: CREATE INDEX blocks writes. Use CREATE INDEX CONCURRENTLY, outside a transaction.
Adding a NOT NULL column with a default: fine on PostgreSQL 11+, but on older versions it rewrites the whole table. Know your version.
Adding a foreign key: validates every row while holding a lock. Add it as NOT VALID, then VALIDATE CONSTRAINT separately, which uses a weaker lock.
Changing a column type: usually rewrites the table. Treat it like a rename: new column, dual write, backfill, switch, drop.

sql

1ALTER TABLE orders2  ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id)3  REFERENCES customers (id) NOT VALID;4 5ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

Set a lock timeout#

Even "safe" statements need a brief exclusive lock. If a long-running query holds a conflicting lock, your migration waits — and every query behind it queues up too. One stuck migration can freeze the whole table.

Always set a timeout so the migration fails fast instead:

sql

1SET lock_timeout = '3s';2SET statement_timeout = '15min';

A failed migration you can retry is far better than a queue of blocked connections.

Process that keeps it safe#

Separate schema deploys from code deploys. Migrations should run before the code that needs them, never in the same step.
Review migrations like production code. Every pull request with a migration gets a second reviewer who checks lock behavior.
Test against production-sized data. A migration that takes 50 ms on a dev database can take 40 minutes on the real one.
Never edit a migration that has already run. Write a new one.

Conclusion#

Zero-downtime migrations are not about clever SQL. They are about discipline: small reversible steps, compatibility between versions, and respect for locks. Once the team adopts expand and contract as the default, schema changes stop being events and become routine.