Zero-Downtime Database Migrations: The Expand-Contract Pattern

Renaming a column sounds like a one-line change. In production it is one of the most reliable ways to take yourself down.

Here is the shape of the accident. You rename users.name to users.full_name, ship the migration and the new code together, and something still breaks. The deploy is rolling, so for a few minutes two versions of your app are live at once. The old version asks for name and gets an error because the column is already renamed. Or the migration runs first against old code, or the code deploys first against the old schema. Every ordering fails somewhere, because the problem is not ordering. The problem is that schema changes are instant and code deploys are not.

Expand-contract is the pattern that respects that gap. You never change a schema in one step. You grow it, move the code and the data over, and only then shrink it. Three phases, usually three separate deploys.

Phase 1: Expand

Expand means: add the new shape, keep the old shape fully working.

To rename name to full_name, you do not rename anything. You add a new column:

ALTER TABLE users ADD COLUMN full_name text;

In Postgres this is a metadata-only change. The new column is nullable, existing rows get null, no table rewrite happens, and the lock is held for milliseconds. Your old code keeps reading and writing name exactly as before. Nothing about production behavior has changed yet, which is precisely the point.

The same thinking applies to indexes. If the new shape needs an index, build it without blocking writes:

CREATE INDEX CONCURRENTLY idx_users_full_name ON users (full_name);

CONCURRENTLY takes longer and cannot run inside a transaction block, but it does not stop inserts and updates while it builds. A plain CREATE INDEX on a hot table blocks writes for the entire build, and on a large table that is an outage with extra steps.

Phase 2: Migrate

Now deploy code that lives in both worlds. It writes to both columns, and it reads the new column with a fallback to the old one:

func (s *Store) UpdateName(ctx context.Context, id int64, name string) error {
    _, err := s.db.ExecContext(ctx, `
        UPDATE users
        SET name = $1, full_name = $1
        WHERE id = $2`, name, id)
    return err
}

SELECT id, COALESCE(full_name, name) AS display_name
FROM users
WHERE id = $1;

Dual writes keep the two columns in agreement for every row your new code touches. But old rows that nobody updates still have a null full_name, so you backfill them, in batches, in the background:

UPDATE users
SET full_name = name
WHERE id IN (
    SELECT id FROM users
    WHERE full_name IS NULL
    ORDER BY id
    LIMIT 5000
);

Run that until it touches zero rows. Batching matters more than it looks. One giant update holds locks for minutes and generates a huge pile of WAL that your replicas have to replay. Five thousand rows at a time keeps each transaction short, and a short pause between batches lets replication and normal traffic breathe.

One more trap hides in this phase. If an older app version is still running somewhere, it writes only name, and your new column silently drifts out of sync. A trigger covers that gap until the old version is gone:

CREATE OR REPLACE FUNCTION sync_full_name() RETURNS trigger AS $$
BEGIN
    IF NEW.full_name IS NULL THEN
        NEW.full_name := NEW.name;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_sync_full_name
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_full_name();

The trigger is scaffolding, not architecture. It exists to make the transition safe, and it gets dropped in the last phase.

Phase 3: Contract

Contract is where you finally remove the old shape, and it is deliberately boring. Before you drop anything, verify with counts rather than confidence:

SELECT count(*) FROM users WHERE full_name IS NULL;

That number has to be zero. Then, in a separate deploy, after every running app version reads the new column, you remove the dual write, remove the fallback, remove the trigger, and drop the old column:

ALTER TABLE users DROP COLUMN name;

Dropping a column in Postgres is also metadata-only. The space is reclaimed later by normal vacuum work, but the logical change is instant and the lock is brief. If you rush this phase and an old binary is still serving traffic, that binary now fails on every query. The waiting is not ceremony. The waiting is the safety.

The NOT NULL version of the problem

Sooner or later the migration is not a rename, it is making a column required. SET NOT NULL on a large table scans the whole table while holding a strong lock. Postgres 12 and later let you prove the constraint first, in the background:

ALTER TABLE users
    ADD CONSTRAINT users_full_name_not_null
    CHECK (full_name IS NOT NULL) NOT VALID;

ALTER TABLE users VALIDATE CONSTRAINT users_full_name_not_null;

ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

ALTER TABLE users DROP CONSTRAINT users_full_name_not_null;

The NOT VALID constraint starts enforcing the rule for new writes immediately, without scanning. VALIDATE CONSTRAINT then checks existing rows while holding a much weaker lock that does not block writes. Once validation passes, SET NOT NULL sees the proof and skips its own scan. Same end state, no long lock.

Rules that keep the pattern safe

One phase per deploy. Expand in one release, migrate in the next, contract in a third. Bundling phases back into a single release rebuilds the exact problem the pattern exists to solve.

A rename is never one step. A rename is add, migrate, drop. A type change is add, migrate, drop. A column move between tables is add, migrate, drop. Once you see every breaking change as the same three moves, migrations stop being creative and start being routine, which is what you want from them.

Protect the table from your own migration. Migration tools happily wait for locks, and a migration waiting on an ACCESS EXCLUSIVE lock blocks every query behind it. Fail fast instead, and retry when the table is quiet:

SET lock_timeout = '2s';
ALTER TABLE users ADD COLUMN full_name text;

A migration that errors in two seconds is a nuisance. A migration that queues for ten minutes behind a long transaction, with a hundred app queries queued behind it, is an incident.

Verify every phase with numbers. Row counts before and after the backfill. Null counts down to zero. Error rates watched during each deploy. The pattern gives you a checkpoint at every phase, and checkpoints you do not measure are decoration.

The takeaway

Zero-downtime migration is not a tool or a framework feature. It is the discipline of never letting the database and the code disagree about the shape of the data, even for a minute. Expand first so both shapes exist. Migrate the writes, the reads, and the old rows. Contract last, only when counts prove nothing depends on the old shape.

If you take one thing from this: the next time a migration looks like one line, ask which of your running app versions it breaks. Then split it into three.