# Schema change recipes

Verified 2026-09 against <https://www.postgresql.org/docs/current/sql-altertable.html>.

Each recipe is a sequence of **migration** (M) and **deploy** (D) steps. A step may only assume the
code from the previous D step is running — during any rolling deploy, both versions are live.

## Rename a column

| # | Type | Step |
|---|---|---|
| 1 | M | `ALTER TABLE users ADD COLUMN full_name text` (nullable) |
| 2 | D | Code writes both `name` and `full_name`; reads `name` |
| 3 | M | Backfill `full_name` from `name` in batches |
| 4 | D | Code reads `full_name`; still writes both |
| 5 | D | Code writes only `full_name` |
| 6 | M | `ALTER TABLE users DROP COLUMN name` — a later release |

Step 6 is the one to delay. Everything before it is reversible.

## Add a NOT NULL column

| # | Type | Step |
|---|---|---|
| 1 | M | `ADD COLUMN status text` (nullable) |
| 2 | D | Code writes `status` on every insert and update |
| 3 | M | Backfill existing rows in batches |
| 4 | M | `ALTER TABLE … ADD CONSTRAINT status_not_null CHECK (status IS NOT NULL) NOT VALID` |
| 5 | M | `VALIDATE CONSTRAINT status_not_null` (scans without an exclusive lock) |

A column default that the engine can apply as metadata only (a constant) lets you collapse 1–3. A
volatile or computed default does not — it rewrites the table.

## Change a column's type

Treat it as a rename to a new column of the new type. In-place `ALTER TYPE` rewrites the table and
takes an exclusive lock, and it fails outright when a value does not convert.

| # | Type | Step |
|---|---|---|
| 1 | M | Add `amount_minor bigint` beside `amount numeric` |
| 2 | D | Dual-write both, read the old one |
| 3 | M | Backfill with the conversion, batched; log rows that do not convert |
| 4 | D | Read the new column |
| 5 | D | Stop writing the old |
| 6 | M | Drop the old column, later |

## Split one column into two

Same shape: add both new columns, dual-write, backfill with the parsing logic, switch reads, stop
writing, drop. Keep the parsing logic in *code* during the dual-write phase so it is testable, not
in a one-off SQL expression.

## Move data to another table

| # | Type | Step |
|---|---|---|
| 1 | M | Create the new table |
| 2 | D | Write to both old and new |
| 3 | M | Backfill the new table from the old, batched and idempotent |
| 4 | D | Read from the new table |
| 5 | D | Stop writing the old |
| 6 | M | Drop the old table, later |

Reconcile before step 4: count rows and compare a checksum of a stable column on both sides.

## Add a foreign key

| # | Type | Step |
|---|---|---|
| 1 | M | Ensure an index exists on the referencing column (`CONCURRENTLY`) |
| 2 | M | Fix or remove orphan rows, batched |
| 3 | M | `ADD CONSTRAINT … FOREIGN KEY … NOT VALID` — fast, applies to new writes |
| 4 | M | `VALIDATE CONSTRAINT …` — scans, but takes a weaker lock |

Adding a validated foreign key in one statement scans the whole table under a lock that blocks
writes on both tables.

## Add an index

Single step, but the form matters:

- Postgres: `CREATE INDEX CONCURRENTLY` — cannot run inside a transaction, so tell the migration
  tool not to wrap it. If it fails it leaves an `INVALID` index; drop it (also concurrently) and
  retry.
- SQL Server: `WITH (ONLINE = ON, RESUMABLE = ON)` where the edition supports it.
- MySQL: `ALGORITHM=INPLACE, LOCK=NONE`, stated explicitly.

## Drop anything

The rule that prevents most incidents: **a drop happens at least one full release after the last
code that referenced it stopped being deployed anywhere** — including any long-running worker,
scheduled job or analytics query outside the main application.