Zero-Downtime Database Migrations: The Expand-Contract Pattern in Practice

Somewhere in your production database is a migration that ran fine in staging and then took down the app for six minutes. Usually it's an ALTER TABLE that looked innocent — adding a NOT NULL column, renaming something, adding an index — and turned into a full table lock on a table with 40 million rows. The deploy pipeline didn't fail. The database just stopped taking writes while everyone stared at a spinning dashboard.

Zero-downtime database migrations are not a checkbox in your migration tool. They're a discipline: you stop thinking of a schema change as one atomic step and start thinking of it as a sequence of backward-compatible steps, each safe to ship on its own. The name for that discipline is the expand-contract pattern, and if you're running any service that can't afford a maintenance window, it should be the default way your team changes schema — not the thing you reach for only on the scary migrations.

Why "just run the migration" breaks in production

The naive approach — write the migration, run it in the same deploy as the code that depends on it — works fine on a database with a few thousand rows and no live traffic. It falls apart for three reasons at scale:

1. Locking. Many schema changes take locks that block reads or writes for the duration of the operation. ADD COLUMN ... NOT NULL DEFAULT in older Postgres versions rewrites the entire table. Adding a foreign key validates every existing row under a lock. On a large table, that's not milliseconds — it's minutes.
2. Ordering. A single deploy is not atomic across your fleet. If you deploy new application code and a new schema in the same release, there is a window — sometimes seconds, sometimes the length of a rolling deploy — where old code is running against the new schema, or new code against the old schema. If the schema change isn't backward-compatible with the previous app version, that window produces errors or corrupted data.
3. No rollback path. If the migration itself is destructive (dropped column, renamed table), rolling back the application code doesn't undo the database change. You're stuck fixing forward under pressure.

The fix isn't a smarter migration tool. It's separating "the database supports both old and new shapes" from "the application only uses the new shape" into two distinct, independently deployable steps.

The expand-contract pattern

The pattern has three phases:

• Expand — add the new schema alongside the old one. Nothing is removed. Both old and new application code can run against this schema simultaneously.
• Migrate — backfill data and cut application code over to the new schema, in a rolling deploy, while the old schema is still present as a fallback.
• Contract — once every instance is running the new code and you've verified nothing depends on the old shape, remove the old schema.

Each phase is its own migration, its own deploy, and — critically — its own decision point where you can pause, verify, or abort without an outage.

Example: renaming a column

Renaming users.email to users.email_address is the textbook case for why you can't do this in one step. Here's the expand-contract version using a tool like golang-migrate, Flyway, or Rails migrations — the SQL is the same regardless of tool.

Expand (migration 1):

ALTER TABLE users ADD COLUMN email_address TEXT;

-- Backfill in batches, not one giant UPDATE
UPDATE users SET email_address = email
WHERE id BETWEEN 1 AND 10000 AND email_address IS NULL;
-- repeat in batches until done, or use a background job

At this point, email and email_address both exist and are kept in sync. To handle rows written by old code during the rollout, add a trigger or, more simply, dual-write from the application:

def create_user(email):
    db.execute(
        "INSERT INTO users (email, email_address) VALUES (%s, %s)",
        (email, email),
    )

Migrate (deploy, then migration 2): Ship application code that reads from email_address instead of email. Because the expand step guarantees email_address is populated for every row — old and new — this deploy is safe to roll out gradually. If it goes wrong, you roll back the app deploy, not the database.

Contract (migration 3, days or weeks later):

ALTER TABLE users DROP COLUMN email;

Only run this after you've confirmed — via query logs, APM, or a grep across the codebase — that nothing still references the old column. This is the step teams skip, and skipped contracts are how you end up with a database full of zombie columns nobody's brave enough to remove.

Example: adding a NOT NULL column safely

This is the single most common way teams accidentally lock a production table. Don't do this:

ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';

On Postgres 11+ this specific case is actually safe because a constant default doesn't require a full rewrite — but the moment your default is an expression, or you're on an older engine, or you're adding a NOT NULL to an existing nullable column, you're back to a full table scan under lock. The expand-contract version sidesteps engine version trivia entirely:

-- Expand: nullable, no default constraint yet
ALTER TABLE orders ADD COLUMN status TEXT;

-- Backfill in batches
UPDATE orders SET status = 'pending' WHERE status IS NULL AND id BETWEEN 1 AND 50000;

-- Once fully backfilled and app writes it on every insert:
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

Enforce the NOT NULL constraint only after every row has a value and every code path writes one. Validating a NOT NULL constraint on Postgres does still require a scan, but with NOT VALID / VALIDATE CONSTRAINT for check constraints (and native support for this pattern with foreign keys) you can split even that into a non-blocking step:

ALTER TABLE orders ADD CONSTRAINT status_not_null CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT status_not_null; -- scans without blocking writes

Implementation checklist

If you're introducing this pattern to a team that's used to single-step migrations, here's the sequence that works:

1. Classify the migration. Additive (new column, new table, new index) is usually safe in one step if you use CREATE INDEX CONCURRENTLY and nullable columns. Destructive or renaming changes always need expand-contract.
2. Write the expand migration and get it deployed and backfilled before touching application code.
3. Batch your backfills. A single UPDATE across 40 million rows holds locks and bloats your WAL. Batch in chunks of 1,000–50,000 rows with a short sleep between batches, or push the backfill into a background job with retry logic.
4. Deploy app code changes separately from schema changes, and make sure the app can tolerate the schema in both its old and new states for the duration of the rollout.
5. Verify before contracting. Check query logs, dashboards, or add temporary logging on the old column/table to confirm nothing still touches it.
6. Set a contract deadline. Expand steps that never get contracted are how schemas accumulate cruft. Put the contract migration on the sprint board, not just "someday."

Common pitfalls

Doing it all in one deploy anyway, because "it's a small migration." Table size isn't the only risk — lock duration depends on your database's specific mechanics per operation. ADD COLUMN behavior differs across Postgres versions and wildly between Postgres and MySQL. Don't estimate risk by table size alone; know what your specific migration does under your specific engine version.

Forgetting the dual-write step. If you backfill once and don't dual-write during the migrate phase, any row created or updated between your backfill and your cutover deploy is missing data in the new column. This is the single most common bug in expand-contract migrations — a silent partial backfill that only surfaces later as null-pointer-shaped bugs in production.

Skipping the contract phase indefinitely. Every unfinished contract is a small tax: extra storage, extra confusion for new engineers, extra surface area for someone to write new code against the wrong column. Track expand migrations in the same place you track tech debt, and set a real deadline for contracting them.

Running backfills without rate limiting. A backfill that saturates your primary's I/O will show up as latency spikes for every other query hitting that table. Batch it, and if you have read replicas, consider running the backfill against off-peak traffic windows or throttling based on replication lag.

Testing migrations only against a small local database. A migration that runs in 40ms locally can take 20 minutes against production data volume, and lock behavior under concurrent traffic doesn't show up in a single-connection local test at all. Run migration timing tests against a production-sized snapshot before you ship anything destructive.

Treating this as a database-only concern. Expand-contract only works if application code is written to tolerate both schema states during the migrate phase. That's a code review discipline, not just a DBA discipline — reviewers need to know a migration is in flight and check that new code doesn't assume the contract phase has already happened.

The payoff

None of this is exotic. Every mature engineering org running a database at scale ends up converging on some version of expand-contract, usually after their first bad lock-related outage. The teams that do it well just formalize it earlier — as a written playbook, a migration template, and a CI check that flags destructive schema changes so they get routed through the multi-step process instead of shipped in one commit.

If your team is still debating whether a migration needs a maintenance window, or you've had a deploy take down writes because of a lock nobody anticipated, that's usually a sign the process needs codifying, not just the next migration needing more care. We help engineering teams build exactly this kind of migration discipline into their CI/CD pipeline — from templated expand-contract migrations to the automated checks that catch a risky ALTER TABLE before it ships. Get in touch if you want a second set of eyes on your migration strategy before your next major schema change.