safe-migrations git:20260703.b05b13bA

Immutable. This exact content is served forever at /api/v1/blob/c37b310fa054afa5.

---
name: safe-migrations
description: Zero-downtime database schema and data migration discipline — expand → migrate → contract, never destructive in the release that ships the code change, backfills as jobs, every step reversible. INVOKE THIS PROACTIVELY whenever creating or editing a migration file (migrations/, alembic, Prisma, EF Core, Flyway, Liquibase, Rails, Django, knex), writing ALTER TABLE / CREATE INDEX / column or table adds, renames, drops, or type changes, changing enums or constraints, planning a data backfill, or shipping any change where code and schema move together. Not for designing a greenfield schema (that's [[code-standards]] section 2) or diagnosing a broken migration (that's [[debugging]]).
---

# Safe migrations — the schema change and the safe path to it are different artifacts

The single most expensive junior mistake in backend work: writing the migration for the **final state** instead of the **safe path** to it. `ALTER TABLE users RENAME COLUMN email TO email_address` looks correct — and it is correct, for a world where the deploy is instantaneous. Real deploys are rolling: for minutes (or hours, or days if the deploy fails), **old code and new schema run against each other**. The rename ships, old pods still SELECT `email`, and production is down before the deploy finishes. Every rule below exists because the database outlives and overlaps every version of the code that touches it.

## How to apply this

Before writing any migration, answer one question: **"will the *previous* release's code still work against the schema this migration produces?"** If no, the migration must be split into stages. Then walk the change through the expand → migrate → contract sequence, and run the self-check before shipping.

---

## 1. The core law: every migration is compatible one release in each direction

During a rolling deploy, blue/green switch, or a failed-deploy rollback, the schema meets both the old and the new code. So:

- The schema after the migration must work with the **previous** release's code (deploy in progress, or rolled back).
- The current code must work with the **pre-migration** schema only if your platform runs code before migrations — know your ordering; most run migrations first.

This is the invariant everything else derives from. A migration that requires the new code to already be everywhere is a migration that requires downtime — and nobody scheduled any.

## 2. Expand → migrate → contract

Any breaking change (rename, drop, type change, moving data) decomposes into three phases across **separate releases**:

1. **Expand** — add the new thing alongside the old: a new nullable column, a new table, a new index. Purely additive; old code doesn't notice.
2. **Migrate** — teach the code to write to both old and new (dual-write), backfill existing rows (section 4), then switch reads to the new location. Each of these can be its own deploy; each is individually reversible.
3. **Contract** — only after a full release cycle where nothing reads or writes the old location: drop it. The drop ships **alone**, in a release that changes nothing else about that data.

**Example — renaming `email` to `email_address`:**

```sql
-- Avoid: the "correct" one-step rename — downtime by design
ALTER TABLE users RENAME COLUMN email TO email_address;
-- Old pods now crash on every SELECT email. Rollback of the code doesn't
-- help — the column is already gone.

-- Prefer: the staged path
-- Release 1 (expand):    ALTER TABLE users ADD COLUMN email_address TEXT NULL;
--                        code dual-writes email + email_address
-- Release 1.x (migrate): backfill job copies email -> email_address (batched, section 4)
--                        code switches reads to email_address, still dual-writes
-- Release 2:             code stops writing email
-- Release 3 (contract):  ALTER TABLE users DROP COLUMN email;
```

Yes, it's four releases for a rename. That's the honest cost of zero downtime — and steps 1–3 are each trivially safe, boring, and reversible, which is the point.

## 3. Destructive operations ship alone, one release late

`DROP COLUMN`, `DROP TABLE`, type-narrowing, tightening a constraint: these are only safe when **nothing in any deployable version references the target**. That means:

- Never in the same release as the code change that stops using it — the rollback of that release would need the dropped thing back.
- Grep first: ORM models, raw SQL, views, triggers, reports, and that one cron job nobody remembers.
- The drop migration's PR description says what release removed the last reference, so the reviewer can verify the waiting period actually passed.

## 4. Backfills are jobs, not migrations

A migration that runs `UPDATE users SET email_address = email` on fifty million rows inside the deploy pipeline will hold locks for minutes, time out the deploy, and leave you half-migrated with no record of progress. Data movement is an **operational job**, not a schema migration:

- **Batched** (thousands of rows per transaction, not one transaction for everything), with progress recorded so it's **resumable** after interruption.
- **Throttled** — sleep between batches; the backfill shares the database with production traffic.
- **Idempotent** — running a batch twice produces the same result (`WHERE email_address IS NULL` style guards), because it *will* get re-run.
- **Verified** — before flipping reads: row counts match, spot-check a sample, and check the edge rows (NULLs, empty strings, the oldest records).

## 5. Every step has a down path — or an explicit confession

- Additive steps (expand phase) are trivially reversible: the down migration drops what the up added.
- Destructive steps are **not** reversible by a down migration — a `DROP COLUMN` down can restore the column but not the data. Mark these explicitly: `-- IRREVERSIBLE: take a snapshot/backup before running`, and make the backup a stated pre-step, not a hope.
- If you wrote a down migration, **run it once** against a copy — an untested down migration is a rollback plan made of wishes.

## 6. Know which operations lock — and use the non-blocking forms

The same logical change often has a blocking and a non-blocking form; the difference is invisible in a dev database with 200 rows and an outage at production scale. The patterns (engine-specific — verify against your engine's docs, see Provenance):

| Intent | Blocking form | Safer form |
|---|---|---|
| Add an index | `CREATE INDEX` (locks writes on the table in some engines) | `CREATE INDEX CONCURRENTLY` (Postgres) / `ALGORITHM=INPLACE` (MySQL) — outside a transaction |
| Add NOT NULL | `ALTER COLUMN SET NOT NULL` (full table scan under lock) | Add a `CHECK (col IS NOT NULL) NOT VALID`, then `VALIDATE CONSTRAINT` separately (Postgres) |
| Change a column type | In-place `ALTER` rewriting the table | New column + dual-write + backfill + swap (section 2) |
| Add a column with a volatile default | Table rewrite on older engines | Add nullable, backfill, then set the default |

General rules: keep every migration transaction **short**; never mix a schema change and a data change in one migration; and treat "how long will this hold what lock at production row counts" as a question the PR must answer, not discover.

## 7. Enums and constraints evolve additively too

- Adding an enum value is safe; **removing or renaming** one follows expand→contract like any rename (new value in, code migrated, old value retired after a release, then removed).
- New constraints validate **existing** data before they're enforced — add as non-validating first (or clean the data first), then enforce. A constraint that fails on legacy rows takes the deploy down with it.
- Foreign keys onto big tables: same non-blocking two-step where the engine supports it.

## 8. Rehearse against realistic data

A migration tested only against the dev database has been tested against nothing. Before production: run it against a recent production-shaped copy (a snapshot restore, a staging clone) and record **how long it took and what it locked**. That number is what tells you whether it's a deploy step or a scheduled maintenance job. This is the migration version of [[tdd]]'s "watch the test fail" — evidence over assumption.

---

## Self-check before you ship a migration

- Will the **previous** release's code run correctly against the post-migration schema? (If no: split into expand → migrate → contract.)
- Is anything destructive (drop/rename/narrow) shipping in the same release as the code that stops using it? (Move it one release later, shipping alone.)
- Is any data movement inside a schema migration? (Extract it into a batched, resumable, idempotent, throttled job.)
- Does every step have a tested down path — or an explicit `IRREVERSIBLE` marker with a backup as a stated pre-step?
- Do you know what locks this takes and for how long **at production row counts** — and did you use the non-blocking form where one exists?
- New constraints/enum changes: validated against existing data before enforcement? Enum removals staged like renames?
- Was the migration rehearsed against a production-shaped copy, with its duration recorded?

## Provenance and maintenance

The staging discipline (sections 1–5, 7–8) is durable. Section 6's lock behaviors are **engine- and version-specific** — last sanity-checked 2026-07 against Postgres and MySQL documentation. Before relying on a specific non-blocking form, verify it against your engine's current docs (`CREATE INDEX CONCURRENTLY` caveats, MySQL online DDL support per operation, and managed-service variants like Aurora/Azure SQL differ). The *principle* — know the lock, prefer the non-blocking form, rehearse at scale — survives any engine change.