# Migration Patterns — Recipes

# Contents

• Expand → migrate → contract (column rename)
• Adding NOT NULL without a long lock (PostgreSQL)
• Index creation without blocking writes
• Batched backfill
• Safe destructive migrations
• Divergent heads
• Gotchas

# Expand → migrate → contract (column rename)

Renaming users.phone → users.phone_number across releases:

-- Migration 1 (expand): release N
ALTER TABLE users ADD COLUMN phone_number text;

-- Release N code: write both columns, read phone_number
-- COALESCE(phone_number, phone) during transition.

-- Migration 2 (backfill): separate migration, batched (see below)
-- Migration 3 (contract): release N+1, after all rows copied
ALTER TABLE users DROP COLUMN phone;

Down paths: 1 → DROP COLUMN phone_number; 3 is irreversible — document it.

# Adding NOT NULL without a long lock (PostgreSQL)

A plain ALTER TABLE ... SET NOT NULL scans the whole table under an ACCESS EXCLUSIVE lock. Split it:

-- Step 1: instant, does not validate existing rows
ALTER TABLE orders ADD CONSTRAINT orders_customer_id_nn
  CHECK (customer_id IS NOT NULL) NOT VALID;

-- Step 2: scans without blocking writes (SHARE UPDATE EXCLUSIVE)
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_nn;

-- Step 3 (PG 12+): SET NOT NULL is instant because the valid CHECK proves it
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_nn;

# Index creation without blocking writes

-- PostgreSQL: must run OUTSIDE a transaction block
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

• Alembic: op.create_index(..., postgresql_concurrently=True) with with op.get_context().autocommit_block():
• If it fails it leaves an INVALID index — drop it and retry: DROP INDEX CONCURRENTLY idx_orders_customer_id;
• MySQL (InnoDB): plain CREATE INDEX is already online (ALGORITHM=INPLACE).

# Batched backfill

Never one giant UPDATE. Batch by primary key with a pause per batch:

-- Repeat until 0 rows affected (driver loop or DO block):
UPDATE users
SET phone_number = phone
WHERE id IN (
  SELECT id FROM users
  WHERE phone_number IS NULL AND phone IS NOT NULL
  ORDER BY id
  LIMIT 5000            -- 5000 keeps each transaction < ~1s on typical rows
);
-- sleep 100ms between batches to let replication and vacuum breathe

Run as a repeatable script or data migration — separate from schema migrations.

# Safe destructive migrations

Dropping a table/column:

1. Confirm zero references: grep the codebase AND check pg_stat_user_tables.seq_scan/idx_scan deltas over a week if possible.
2. Rename first, drop later (rename is instantly reversible):

-- Release N: soft-drop
ALTER TABLE legacy_events RENAME TO legacy_events_dropped_20260805;
-- Release N+2 (weeks later): real drop, after nothing broke
DROP TABLE legacy_events_dropped_20260805;

# Divergent heads

Two branches each added a migration on the same parent:

• Alembic: alembic merge -m "merge heads" <rev1> <rev2>
• Rails/Prisma/Flyway: re-timestamp the UNAPPLIED migration only (prisma migrate dev handles resolution; Flyway: bump the V number).
• Never renumber a migration that has been applied to any shared environment.

# Gotchas

• PostgreSQL DDL is transactional; MySQL DDL is not. A failed multi-statement MySQL migration leaves partial state — write idempotent statements (ADD COLUMN IF NOT EXISTS) so re-running converges.
• CREATE INDEX CONCURRENTLY inside a transaction fails — migration tools wrap migrations in transactions by default; use the tool's autocommit escape.
• Default values: PG 11+ adds ADD COLUMN ... DEFAULT ... instantly (metadata-only) for constant defaults; volatile defaults (now(), uuid()) still rewrite the table — add the column first, set the default after.
• Down migrations that drop data are lies — a down for DROP COLUMN restores the column, not its contents. Say so in the migration header.
• Foreign keys on busy tables: add as NOT VALID, then VALIDATE CONSTRAINT separately (same trick as NOT NULL).
• Renaming with views/triggers attached — PG updates them automatically; MySQL does not for triggers referencing the old name. Check information_schema.