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 anINVALIDindex; 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.