sdlc.dev

CI/CD and DevOps path · Release · Topic 10 of 13

Database migrations: expand and contract

Not started

In a zero-downtime deploy the old and new versions of your app run at the same time against one database. So a schema change can't be a single step that suits only the new code. **Expand and contract** (also called parallel change) splits it into steps that each keep both versions working: add the new shape, move to it, and only then remove the old one.

Why it matters: Code can be rolled back in seconds; a dropped column can't. Schema changes are where zero-downtime deploys usually fail, unless each step works with both the old and new code.

  1. 1Old and new code share one database

    During any rolling, blue-green or canary deploy, both versions read and write the same tables, so every schema change must suit both.

  2. 2Expand first

    Add the new column, table or index alongside the old one. Nothing that runs today breaks.

  3. 3Migrate, then switch

    Write to both, backfill old rows in batches, then move reads to the new shape, one deploy at a time.

  4. 4Contract last

    Only when no running version uses the old shape, drop it. That step is the one you can't undo, so it waits.

1

How it works

The explanation, with examples you can run.

The steps, for a column rename

Say users.fullname should become users.display_name.

  1. Expand. Add display_name as a nullable column. Old code ignores it. Deploy.
  2. Dual write. Release code that writes both columns and still reads fullname. Deploy.
  3. Backfill. Copy fullname into display_name for existing rows, in batches.
  4. Switch reads. Release code that reads display_name (still writing both, so a rollback is safe). Deploy.
  5. Stop the old writes. Release code that only uses display_name.
  6. Contract. When no running or rollback-candidate version touches fullname, drop it, and add NOT NULL or constraints you need.
-- 1. expand: cheap, no table rewrite in PostgreSQL 11+ for a nullable columnALTER TABLE users ADD COLUMN display_name text; -- 3. backfill: small batches, safe to re-runUPDATE users SET display_name = fullnameWHERE id IN (SELECT id FROM users WHERE display_name IS NULL LIMIT 1000); -- 6. contract: only after every running version has stopped using fullnameALTER TABLE users DROP COLUMN fullname;

Six steps for a rename feels like a lot. It is the cost of never having a moment where a running version and the schema disagree.

Migrations in the pipeline

  • Version every migration in the repository and run it with a tool (Flyway, Liquibase, Alembic, Prisma Migrate, golang-migrate), never by hand.
  • Run migrations as their own pipeline step before the code that needs them is deployed, and make each migration safe for the code that's already running.
  • Run them first against staging with production-like data volumes; a migration that takes a second on test data can lock a large table for minutes.
  • Watch for locks. Adding an index on a busy PostgreSQL table should use CREATE INDEX CONCURRENTLY; adding a column with a volatile default or changing a column type can rewrite the whole table. Set a lock_timeout so a migration fails fast instead of queueing behind long transactions and blocking everyone.
  • Keep destructive steps (drops, NOT NULL on existing data, type narrowing) in a separate, later release.

Why this ties back to rollback

Rolling back code is easy only while the previous version still works against the current schema. Expand-and-contract keeps that true at every step except the last, and you choose when to take the last step.

2

Check yourself

3 questions. Get them all right to finish this topic.

  1. 1.You want to rename column `fullname` to `display_name`. Why not just run `ALTER TABLE ... RENAME COLUMN` in the deploy?
  2. 2.In which step can you no longer roll the application back to the old version?
  3. 3.Why backfill a large table in batches rather than one UPDATE?

0 of 3 right so far. Get all three to finish this topic.

Go deeper