Zero-Downtime Database Migrations: The Expand-and-Contract Pattern

Most teams I work with run database migrations like this: write the SQL, run it on staging, run it on production, hope the app does not break. Most of the time it works. Sometimes a migration locks a table for 30 seconds, the API times out, and the on-call engineer gets paged at 3am.

The fix is a pattern called expand-and-contract (also known as parallel change). It makes migrations safe to run in production, with zero downtime and a rollback path at every step. Every serious engineering team uses it. Most small teams do not, because it is not taught.

Here is how it works.

The problem with one-shot migrations

Consider this migration: rename the users.name column to users.full_name.

ALTER TABLE users RENAME COLUMN name TO full_name;

Run this in production and:

1. Postgres takes a brief lock to rename the column.
2. The lock releases.
3. Every existing request in flight that references users.name fails.
4. The new deploy fails until you ship code that uses users.full_name.

Even a rename, which is fast, breaks the app unless you time the deploy exactly right. Add a default value, build an index, or change a type, and the lock can hold for minutes.

The expand-and-contract pattern breaks the change into phases, with a working app at every step.

The four phases

1. Expand

Add the new structure without removing the old. The new structure coexists with the old.

For a rename:

-- Migration 1 (expand)
ALTER TABLE users ADD COLUMN full_name TEXT;

The old name column still exists. The new full_name column exists, nullable. The app keeps working as before.

2. Migrate

Backfill data from the old structure to the new. In batches, never all at once.

-- Migration 2 (migrate) - run in batches
UPDATE users
SET full_name = name
WHERE id BETWEEN 1 AND 10000 AND full_name IS NULL;
 
-- Next batch:
UPDATE users
SET full_name = name
WHERE id BETWEEN 10001 AND 20000 AND full_name IS NULL;
 
-- And so on.

Script this. Commit between batches. Watch the row counts. If a batch takes too long, lower the batch size.

For large tables (millions of rows), consider using a tool like pg_repack or writing a background job that backfills slowly over hours.

3. Switch

Deploy the new code that uses the new structure. The old structure still exists, so rollback is easy.

Two strategies here:

Dual-write: the new code writes to both name and full_name. Reads from full_name with fallback to name. Once you are confident everything works, you can stop the fallback.

Read-new-write-both: the new code reads from full_name (falling back to name if null), writes to both. This is safer because you can roll back to the previous deploy without losing data.

Either way, the goal is: if something breaks, you can revert the deploy and the database is still in a working state.

4. Contract

After the new code is confirmed stable in production (one to two weeks), drop the old structure.

-- Migration 3 (contract)
ALTER TABLE users DROP COLUMN name;

Now you can also add a NOT NULL constraint to full_name if appropriate:

ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

At this point, the migration is complete. The old structure is gone.

Operations and their lock behavior

Knowing what locks your DDL takes is half the battle.

Safe (brief lock, online)

• ALTER TABLE ADD COLUMN (nullable, no default) — brief metadata lock.
• CREATE INDEX CONCURRENTLY — no write lock. Cannot run in a transaction.
• CREATE TABLE — no impact on existing data.

Risky (lock for the duration of the operation)

• ALTER TABLE ADD COLUMN ... DEFAULT ... — on Postgres 11+, nullable with default is fast. On older versions, it rewrites the whole table.
• ALTER TABLE ALTER COLUMN TYPE ... — rewrites the table. Lock held for the duration. Hours on large tables.
• CREATE INDEX (without CONCURRENTLY) — blocks writes until done.

Avoid at all costs in production

• ALTER TABLE DROP COLUMN — fast but blocks while references are cleaned up. Plan ahead.
• ALTER TABLE RENAME COLUMN — breaks every existing query that references the old name.
• TRUNCATE TABLE — takes an exclusive lock.

For type changes on large tables, the pattern is:

1. Add new column of the right type.
2. Backfill with converted values.
3. Deploy code that uses the new column.
4. Drop the old column.

Never ALTER COLUMN TYPE directly on a large table.

Indexes

Always use CREATE INDEX CONCURRENTLY in production:

-- Bad — locks the table
CREATE INDEX idx_users_email ON users(email);
 
-- Good — no write lock, runs in background
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Caveats:

• Slower than regular CREATE INDEX.
• Cannot run inside a transaction block.
• Can fail and leave an invalid index. Drop and retry.

Check for invalid indexes after:

SELECT * FROM pg_index WHERE indisvalid = false;

If you find any, drop and rebuild.

The deployment order

The most important rule: never combine schema changes with code changes in the same deploy.

A safe migration looks like:

1. Deploy 1: expand migration (add column, add index, etc.). App code unchanged.
2. Deploy 2: new app code that uses the new schema. Old code path still works.
3. Wait: one to two weeks. Monitor errors, performance, data integrity.
4. Deploy 3: contract migration (drop old column). App code already updated.
5. Deploy 4: cleanup — remove the dual-write code, the fallback logic, the old code path.

Each deploy is independently rollback-able. If deploy 2 breaks, revert to deploy 1's code. The database still has both old and new structure. No data loss.

Backfill scripts

For backfilling data, do not hand-write SQL. Use a tool:

• Prisma migrate with custom scripts
• node-pg-migrate for Node.js projects
• pgmic or bytebase for more sophisticated setups

The script should:

• Run in batches of 1000-10000 rows
• Commit between batches
• Log progress
• Be resumable (use a last_id cursor)
• Have a kill switch if it runs too long

The checklist

Before any production migration:

• Schema change is additive (no drops or renames in the same step)
• Indexes use CREATE INDEX CONCURRENTLY
• Backfills run in batches with commits
• Code deploy is separate from schema deploy
• Rollback path exists at every phase
• Long operations are tested on a staging copy with production data size
• Migration is scheduled during low-traffic hours (in case something still goes wrong)
• Monitoring is in place for connection count, error rate, and query latency

Migrations do not need to be scary. Split them into expand, migrate, switch, and contract phases. Deploy each independently. The database stays working at every step.

Want zero-downtime migrations for your app?

I set up migration pipelines for Node.js and Postgres apps, with safety checks, batching, and rollback paths built in. Let's talk.

Frequently Asked Questions

How do I run database migrations without downtime?

Use the expand-and-contract pattern. Add the new schema in one migration, backfill data in batches in a second step, deploy code that uses the new schema, then drop the old schema in a final migration. Never combine these into a single deployment.

What does CREATE INDEX CONCURRENTLY do?

CREATE INDEX CONCURRENTLY builds an index without locking the table for writes. It takes longer than CREATE INDEX and cannot run inside a transaction, but it lets your app keep serving traffic while the index builds.

Why does my Postgres migration lock the table?

Most DDL operations like ALTER TABLE take a lock that blocks writes. Long-running migrations can hold this lock for minutes. Use additive changes, CREATE INDEX CONCURRENTLY for indexes, and batch UPDATEs to avoid holding locks.

How long should I wait before dropping an old column?

Wait at least one full release cycle (one or two weeks) after deploying the code that no longer uses the column. This gives you time to confirm the new schema works in production and gives you a rollback path if issues surface.