Migrations

---
name: migrations
description: >
  Changing a schema that already holds real data: expand/contract, reversible
  steps, backfills that don't lock, and stating what a deploy strategy must
  respect. Trigger: load when altering an existing table, renaming or dropping
  anything, backfilling data, or when a release couples code to a schema change.
license: Apache-2.0
metadata:
  author: jrodrigopuca
  version: "1.1"
---

_Engine facts here were verified against PostgreSQL 11–17 semantics (September
2026). What a given operation locks, and for how long, is a property of the
engine AND the version — re-check before teaching it as current._

## Critical Patterns

### 1. Expand, migrate, switch, contract — never rename in place

The rename is the trap: it makes old code and new code mutually exclusive, so
there is a moment where one of them is broken no matter the deploy order.
Instead: **expand** (add the new column/table, nothing reads it yet),
**migrate** (write to both, backfill the old rows), **switch** (reads move to
the new shape), **contract** (stop writing the old, then drop it — in a later
release). Each step is deployable alone and revertible alone. The sequence is
slower and it is the only version that can be undone on a Friday.

### 2. A migration coupled to a deploy is a rollback you cannot do

If rolling back the code requires rolling back the schema, you have no rollback
— restoring a schema means restoring data written since. Schema changes go out
BEFORE the code that needs them and stay backward-compatible with the currently
running version. That constraint is not negotiable by release pressure; it is
what makes the release reversible at all, and
[devops's `ci-cd`](../../../devops/skills/ci-cd/SKILL.md) is designing the
rollout around it.

### 3. Backfills are batched, resumable, and observable

Updating ten million rows in one statement takes a lock, blows the transaction
log, and leaves you with nothing if it fails at 90%. Batch it, commit per
batch, record progress so it can resume, and rate-limit it so production
traffic keeps its resources. Run it as a separate operation from the schema
change — a backfill inside a migration turns a five-second deploy into an
outage of unknown length.

### 4. Know which operations take which lock, on YOUR engine — and never wait for one

"Adding a column is cheap" is true on some engines, in some versions, for some
defaults, and catastrophic in the rest. Postgres since 11 stores a constant
(non-volatile) default in the catalog — no rewrite, milliseconds at any size —
but a volatile default (`now()` is fine, `clock_timestamp()` and `random()` are
not) still rewrites every row under an exclusive lock, and so does a type
change — unless the old type is binary-coercible to the new one and `USING`
leaves the values alone (widening a `varchar`, `varchar` → `text`), where
Postgres only touches the catalog. Which side of that line a change falls on
is a fact to check, not to assume. MySQL's answer depends on version and engine (instant DDL exists for some
operations since 8.0, not for all). This is the one place where engine
specifics dominate the judgment: check what your engine and version actually do
for that operation at your table size, and say the answer in lock duration,
not in adjectives.

Whatever the lock, **set `lock_timeout` (or the engine's equivalent) on every
DDL statement** and retry with backoff. An ALTER waiting behind one long
transaction is not itself the outage; it is that every read and write arriving
after it queues behind IT. Fail fast in seconds, retry, and the application
never notices. A DDL without a lock timeout is a migration betting that
production is idle.

### 5. Constraints on a populated table: add without validating, then validate

A CHECK or foreign key added the naïve way scans the whole table under a lock
that blocks writes. Add it `NOT VALID` — committed instantly, enforced for new
and updated rows from that moment — then `VALIDATE CONSTRAINT` in a second
statement, which scans under a lock that lets writes through. `NOT NULL` on
Postgres before 18 has no `NOT VALID` of its own: add `CHECK (col IS NOT NULL)
NOT VALID`, validate it, then `SET NOT NULL`, which uses the validated check to
skip the scan (12+). Indexes follow the same rule: build them `CONCURRENTLY`
(or the engine's online build) or you have taken a write lock for the length
of the build.

### 6. An ORM-generated migration is a draft, not a decision

Autogenerated migrations diff two models and emit the shortest SQL between
them — which is exactly the rename #1 forbids, the type change #4 warns about,
and the constraint #5 says not to add naïvely. Read the SQL the tool wrote
before it runs anywhere; the model diff says what changed, not how to change it
safely under load. Keep the tool for the boilerplate, replace its steps with
the expand/contract sequence by hand.

### 7. Window or online: decide by measured lock duration, not by hope

Two durations, and they are not the same one. `lock_timeout` bounds how long a
statement may WAIT to acquire its lock; it says nothing about how long the
statement then HOLDS it. An ALTER that gets its lock instantly and then rewrites
fifty million rows holds an exclusive lock for minutes, inside any timeout you
set. A change goes out online when the hold, measured, is short for every
statement and nothing scans the full table under an exclusive lock. It needs a
window when a rewrite is unavoidable — a type change that is not
binary-coercible, a volatile default, an engine without online builds — and the
window is sized by running the migration on a production-sized copy and timing
it, not by guessing. "It should be quick" is the sentence that precedes the incident.

### 8. Migrations are code — reviewed, versioned, and tested against real shape

They belong in version control, run in order, and execute exactly once. Test
them against a dataset with production-like volume and skew: a migration
verified only against an empty dev database has been verified against the one
case that never occurs. And write the down path, or state explicitly that there
isn't one — an irreversible migration is a decision worth making consciously,
not a detail discovered during an incident.

### 9. Destructive steps wait, and they wait longer than feels necessary

Dropping the old column right after the switch feels tidy and removes your
escape route. Let the contract phase lag by a full release cycle at minimum:
long enough that a rollback to the previous version still finds what it needs,
and long enough that a bug in the new path can be fixed by pointing back rather
than by restoring from backup.

## Resources

- Sibling skills: [data-modeling](../data-modeling/SKILL.md) (the shape you are
  moving toward), [query-performance](../query-performance/SKILL.md) (index
  builds are migrations with their own locking story)
- Rollout, windows and rollback mechanics:
  [devops's `ci-cd`](../../../devops/skills/ci-cd/SKILL.md); proving the
  migration ran correctly before claiming it did:
  [generalist's `verification`](../../../generalist/skills/verification/SKILL.md)