levelcode

Concept · Transactions & indexes

Zero-downtime migrations

Expand the schema, move the code, backfill, then contract — so old and new code both work mid-deploy.

4 minexpand and contractparallel changeonline schema changebackfill

During a rolling deploy, the old and new versions of your code run at the same time against one database. So a schema change has to work with both versions. Renaming a column in one step breaks whichever version expects the other name. The fix is to split every breaking change into backwards-compatible steps. This is called expand and contract, or parallel change.

Example: rename users.name to full_name

  1. Expand. Add the new column, nullable, with no default that needs a table rewrite. Old code ignores it.
    sql
    ALTER TABLE users ADD COLUMN full_name text;
  2. Dual-write. Deploy code that writes both columns and still reads name.
  3. Backfill. Copy existing data in small batches so you never hold a long lock or one huge transaction.
    sql
    UPDATE users SET full_name = name
WHERE id IN (SELECT id FROM users WHERE full_name IS NULL LIMIT 5000);
-- repeat until 0 rows
  4. Switch reads. Deploy code that reads full_name and still writes both.
  5. Stop writing the old column. Deploy again.
  6. Contract. Once no running code mentions it, drop name. If you need it, add NOT NULL to full_name here too, as a constraint added NOT VALID and validated separately (PostgreSQL 18 accepts NOT NULL … NOT VALID directly; on older versions, use a CHECK (full_name IS NOT NULL) NOT VALID constraint).

Each step is safe to deploy and safe to roll back on its own. It takes several deploys instead of one, and that is the cost of never going down.

Operations that lock (PostgreSQL)

ChangeRiskSafer path
CREATE INDEXBlocks writes for the whole buildCREATE INDEX CONCURRENTLY
Add NOT NULL / foreign key / CHECKScans the table under lockAdd as NOT VALID, then VALIDATE CONSTRAINT
Change a column's typeOften rewrites the tableNew column + backfill + switch
Any ALTER waiting behind a long queryQueues all later queries behind itSet lock_timeout (e.g. '5s') and retry

Habits

  • Keep migrations separate from deploys. Run them as their own step, so a slow migration doesn't hold a deploy hostage and a rollback doesn't try to undo the schema.
  • Never drop or rename in the same release that stops using something. Old pods may still be serving traffic.
  • Treat data migrations as code. They should be batched, resumable and idempotent.

This is the database half of rolling and canary deploys, and the migrations lesson applies it in Go.

Learn it in depth

Related concepts

PreviousComposite and covering indexesNext Implicit interfaces