Expand and contract: schema changes with zero downtime

In one sentence

Never change a schema and its consumers at the same time — add the new shape, migrate to it, then remove the old shape, with a deploy between each step.

Why it matters

During any rolling deploy, old and new application code run simultaneously against one database. A migration that assumes otherwise breaks in the window between the first pod restarting and the last:

• Rename a column in one step and every instance still running old code starts erroring on a column that no longer exists.
• Drop a column in one step and you cannot roll back the deploy, because the old code needs it and the data is gone.
• Add a NOT NULL column with no default and old code — which does not know to supply it — fails every insert.

Expand and contract removes the simultaneity. Each individual deploy is compatible with the one before it, so every step is independently reversible.

The five steps

Take the concrete case of renaming users.name to users.full_name.

1. Expand. Add the new column. Nullable, no default, no constraints. This is a metadata-only change on modern Postgres and MySQL, so it takes milliseconds and holds no meaningful lock.

ALTER TABLE users ADD COLUMN full_name text;

2. Dual-write. Deploy code that writes both columns and still reads the old one. Now every new row is correct in both places, and the read path has not changed, so this deploy is safe to roll back.

await db.update(users).set({ name: value, full_name: value }).where(...)

3. Backfill. Copy the existing rows in batches, not in one statement. A single UPDATE users SET full_name = name on a large table takes a lock, generates one enormous transaction, and blocks replication for its duration.

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

Run it in a loop, commit each batch, and sleep briefly between batches so replicas can keep up. Watch replication lag while it runs and slow down if it climbs. For a very large table this is a job that runs for hours or days, and that is fine — nothing depends on it finishing.

4. Switch reads. Once the backfill is verified complete, deploy code that reads full_name and still writes both. Verify before deploying, with an actual query:

SELECT count(*) FROM users WHERE full_name IS NULL AND name IS NOT NULL;
-- must be 0

If this deploy goes wrong, roll back: the old column is still being written and is still correct.

5. Contract. After the switch has been stable long enough that you will not roll back past it — a few days, not a few minutes — stop writing the old column, deploy, then drop it. Add the NOT NULL constraint now, if you want one.

ALTER TABLE users DROP COLUMN name;

Five steps, four deploys. It feels like a lot of ceremony for a rename, and it is the reason the rename does not become an incident.

Locks are the thing to check

Every DDL statement takes a lock. The question is which one, and for how long. Some operations that look identical differ enormously:

The lock duration matters less than what queues behind it. An ALTER TABLE that waits on a long-running transaction will block every subsequent query on that table while it waits, because lock acquisition is a queue. Always set a short lock_timeout before DDL and retry, so a migration fails fast instead of stalling the application.

SET lock_timeout = '3s';

Making migrations reviewable

• One migration, one change. Mixing a backfill into a DDL migration means the whole thing runs inside a transaction that holds locks for the backfill's duration.
• Forward-only in production. Write the down migration for local development, but plan to recover by rolling forward. A down migration that drops a column destroys data that the rollback was supposed to save.
• Run it against a copy first. Production-sized data is the only honest test of a backfill; the timing on your laptop tells you nothing.
• Say what locks it takes in the pull request description. It is the one thing a reviewer cannot see from the diff.

Common pitfalls

• Backfilling in one statement, discovering the lock wait at 20 million rows.
• Adding a foreign key without NOT VALID, which locks both tables while it checks every existing row.
• Deploying the contract step in the same release as the switch, so there is no version you can roll back to.
• Forgetting that queues and cron jobs are also consumers. Old code lives in workers too, and they often restart on a different schedule than the web tier.

Further reading

• PostgreSQL: ALTER TABLE — the notes on which forms require a rewrite are the important part.
• Martin Fowler: ParallelChange — the same pattern, described generally.