Zero-Downtime Database Migrations: The Expand and Contract Pattern

Zero-downtime schema changes come down to one rule: never ship a migration that the currently running code cannot live with. The expand and contract pattern turns every breaking change into a sequence of compatible ones: add the new shape next to the old, move the data, switch the code, and only then remove what is no longer used. This article walks through the pattern, the two changes that trip people up most (renaming a column and adding NOT NULL), and the locking details that decide whether a migration is invisible or an outage.

Why a plain ALTER TABLE can take you down#

Two things go wrong when a breaking migration runs against a live system. The first is time. Some DDL rewrites the whole table or scans every row while holding a lock that blocks reads and writes. On a table with a hundred million rows that is minutes of unavailability, with a queue of blocked queries behind it that can exhaust the connection pool.

The second is the gap between schema and code. During a rolling deployment, old and new versions of the application run at the same time, for minutes or, with a canary, for hours. If the migration renamed customers.telephone to phone, every request served by the old version now fails on a missing column, and if the deployment is rolled back the new column is there and the old one is gone. The migration and the release are coupled in exactly the wrong way.

Expand and contract breaks that coupling. Every migration is compatible with the version running before it and the version deployed after it, so migrations and deployments can happen in either order and roll back independently.

The pattern in four steps#

The steps map to several small migrations across several releases rather than one migration in one release. That feels slower. It is slower for the change and faster for the team, because none of the steps needs a maintenance window, and any of the first three can be abandoned without losing data.

Worked example: renaming a column#

Renaming customers.telephone to phone is a single fast statement on PostgreSQL, and still the classic example, because the rename itself is not the problem; the running code is. Expanded:

-- Release 1, migration: expand
ALTER TABLE customers ADD COLUMN phone varchar(30);

-- Release 1, migration: keep both columns in sync while old code still writes telephone
CREATE OR REPLACE FUNCTION customers_sync_phone() RETURNS trigger AS $$
BEGIN
    IF NEW.phone IS NULL THEN NEW.phone := NEW.telephone; END IF;
    IF NEW.telephone IS NULL THEN NEW.telephone := NEW.phone; END IF;
    RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER customers_sync_phone BEFORE INSERT OR UPDATE ON customers
    FOR EACH ROW EXECUTE FUNCTION customers_sync_phone();

Then backfill in batches, so that no single statement locks the table for long or floods the write-ahead log:

UPDATE customers SET phone = telephone
WHERE id IN (
    SELECT id FROM customers
    WHERE phone IS NULL AND telephone IS NOT NULL
    ORDER BY id LIMIT 5000
);
-- repeat until it updates zero rows

Release 2 reads phone. Release 3 drops the trigger and the old column. Many teams do the dual write in application code rather than in a trigger; either works. The trigger has the advantage that it covers every writer, including the ones you forgot about.

Adding NOT NULL without locking the table#

Adding NOT NULL to an existing column is the most common contract step, and the one with the biggest gap between the naive and the safe version. On PostgreSQL, ALTER TABLE ... ALTER COLUMN ... SET NOT NULL takes an ACCESS EXCLUSIVE lock and scans the whole table to check existing rows. Nobody reads or writes while it runs. The safe sequence:

-- 1. Add the constraint without checking existing rows: fast, brief lock.
ALTER TABLE customers
    ADD CONSTRAINT customers_phone_not_null CHECK (phone IS NOT NULL) NOT VALID;

-- 2. Validate it: scans the table, but blocks only other schema changes, not reads or writes.
ALTER TABLE customers VALIDATE CONSTRAINT customers_phone_not_null;

-- 3. SET NOT NULL now uses the validated constraint and skips the scan (PostgreSQL 12 and later).
ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;

-- 4. The check constraint is redundant now.
ALTER TABLE customers DROP CONSTRAINT customers_phone_not_null;

Foreign keys follow the same shape: ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID, then VALIDATE CONSTRAINT. Adding a column with a constant default has been a metadata-only change since PostgreSQL 11, so ADD COLUMN status text NOT NULL DEFAULT 'active' is fine on any size of table; a default that is a volatile expression such as now() or gen_random_uuid() still rewrites every row. Indexes go on with CREATE INDEX CONCURRENTLY, which cannot run inside a transaction, so the migration tool has to be told to run that file outside one: Django migrations set atomic = False, Alembic offers autocommit_block(), Liquibase takes runInTransaction="false" on the changeSet, and Flyway has a per-script configuration setting for it.

Locks and lock_timeout#

Even a fast DDL statement waits for an exclusive lock, and while it waits, every query that arrives after it queues behind it. A migration that needs a millisecond can therefore block a table for as long as the longest transaction that was already running. Two settings make this safe:

SET lock_timeout = '3s';
SET statement_timeout = '30s';
ALTER TABLE customers ADD COLUMN phone varchar(30);

If the lock is not granted within three seconds the statement fails instead of building a queue; the migration tool reports an error and you retry once the long transaction is gone. A retry loop around the DDL, with a short lock timeout, is the standard recipe. Keep the rest of the discipline simple: one DDL statement per transaction where the engine allows it, no data backfills inside the same transaction as DDL, and no migration that reads a huge table while holding a lock on it. Linters such as Squawk flag the dangerous PostgreSQL statements in a pull request before they run anywhere.

MySQL, SQL Server and Oracle#

The pattern is engine-independent; the details are not. MySQL 8.0 performs many changes online: ALTER TABLE ... ADD COLUMN ..., ALGORITHM=INSTANT is a metadata change, and ALGORITHM=INPLACE, LOCK=NONE lets reads and writes continue during others. Ask for the algorithm explicitly; if the engine cannot honour it, the statement fails instead of silently taking a table lock. For changes that still copy the table, gh-ost and pt-online-schema-change build a shadow table and swap it in. MySQL has no transactional DDL, so a failed multi-statement migration leaves the schema half changed; keep migrations to one statement each and idempotent where you can. Transactional DDL explained covers what that means for each tool.

On SQL Server, adding a nullable column or a column with a default is a metadata operation, while changing a type rewrites the rows, and the ONLINE = ON option on index operations is not available in every edition. Oracle performs most column additions online and offers DBMS_REDEFINITION for the rest. In every case the expand and contract sequence is the same; only the statement that is safe in the expand step changes.

Feature flags, dual writes and the application side#

The schema is half of the work. The application has to be written so that each version tolerates both shapes:

• Never SELECT * into a structure that fails on an unexpected column, and never rely on column position.
• Put the read switch behind a feature flag so that step 3 is a configuration change, not a deployment, and can be flipped back in seconds.
• Make dual writes idempotent and tolerant of the other column being NULL; the backfill and the live writes overlap.
• Version your ORM mappings deliberately. An ORM that synchronizes the schema on startup will try to drop the old column the moment the entity stops mentioning it, which is step 4 running at step 2.

How migration tools fit the pattern#

Flyway, Liquibase, Alembic, Django, Prisma and the others do not know about expand and contract, and they do not need to. What matters is how you use them: one migration file per step, each shipped in the release that needs it, each compatible with the previous release. Resist the temptation to squash the four steps into one file after the fact; the history should show that the change was made safely, and the contract migration should stay in a later release than the switch. Down migrations, where you write them, should undo one step, not the whole change. The list of migration anti-patterns covers the habits that undo this work.

Where SchemaDrawer fits#

During an expand and contract change the schema deliberately has two shapes at once, and that is easy to lose track of across releases. SchemaDrawer draws the schema that a set of migration files produces by running them against a throw-away database of the engine and version you choose, so a diagram generated in the pull request of each step shows the expanded state, then the contracted one. When the GitHub Action opens a pull request with the new diagram, a reviewer can see that step 4 really removed only the old column and nothing else. The diagram covers tables, columns, primary keys and foreign keys; triggers and indexes are not part of the picture.

Key takeaways#

• Every migration must be compatible with the code running before it and the code deployed after it.
• Expand, migrate, switch, contract: several small migrations across several releases, each reversible until the last.
• On PostgreSQL use NOT VALID plus VALIDATE CONSTRAINT, constant defaults, and CREATE INDEX CONCURRENTLY outside a transaction.
• Set lock_timeout and retry rather than queue behind a long transaction.
• On MySQL request the algorithm explicitly and keep migrations to single, idempotent statements.