safe-migrations

Safe Postgres Migrations

Your job when this skill is active: for a given migration, (1) classify each statement's risk, (2) explain the lock risk briefly, and (3) if unsafe, give the zero-downtime rewrite from the matching recipe below.

Output in this order, and keep it tight. A senior reviewer is brief, not exhaustive.

• Risk: one line per statement (SAFE / RISKY / DANGEROUS), naming the statement.
• Why: two or three sentences. Name the lock, the mechanism (rewrite or full scan), and the impact on a busy table. No numbered lists, no paragraphs of theory.
• Rewrite: the safe SQL only, terse. A few-word comment per step at most. Do not repeat SET lock_timeout on every statement; note once that each ALTER should set a lock_timeout, then leave it out of the SQL. No closing recap. Do not restate the SQL or repeat yourself. If every statement is safe, say so in one line and stop.

Risk classification

Label each statement one of:

• SAFE: metadata-only or non-blocking; ships as-is.
• RISKY: safe-looking but can cascade on a busy table; ship with guards.
• DANGEROUS: rewrites or full-scans the table under ACCESS EXCLUSIVE; must be rewritten before shipping.

Risk depends on lock severity, how long the lock is held, and how busy the table is. A brief ACCESS EXCLUSIVE on a low-traffic table is fine; a long one on a high-traffic table is an outage.

The detection rules

1. The lock-queue cascade (applies to EVERY ALTER TABLE)

Any ALTER TABLE takes a brief ACCESS EXCLUSIVE lock, the coarsest lock, which blocks all reads and writes. Even a metadata-only change is DANGEROUS on a busy table if it must queue behind a long-running query: while the ALTER waits, every query behind it also waits, and the table stalls. Classify RISKY at minimum and require a lock_timeout guard (Recipe A) on any ALTER TABLE against a hot table.

2. Table rewrites (DANGEROUS)

These copy the whole table under ACCESS EXCLUSIVE for the full duration:

• ADD COLUMN ... DEFAULT <volatile>, e.g. gen_random_uuid(), random(), clock_timestamp(). A volatile default is evaluated per row, which forces a rewrite.
• ALTER COLUMN ... TYPE ...: most type changes rewrite (and recheck indexes).

Do NOT flag these as rewrites:

• ADD COLUMN ... DEFAULT <constant> (e.g. 'pending', 1, false): metadata-only since PG 11. Safe for rewrite (still subject to rule 1's lock-queue risk).
• ADD COLUMN ... nullable, no default: metadata-only, SAFE.
• DEFAULT now() or current_timestamp: STABLE, not volatile. SAFE.

3. Constraint validation scans (DANGEROUS)

Adding a constraint validates all existing rows under lock:

• ALTER COLUMN ... SET NOT NULL on an existing column: full scan.
• ADD CONSTRAINT ... CHECK (...) or FOREIGN KEY without NOT VALID: full scan. (FKs lock the parent table too.)

Rewrite with NOT VALID then VALIDATE (Recipe C).

4. Index builds (DANGEROUS)

CREATE INDEX (non-concurrent) takes a SHARE lock that blocks writes for the entire build. ADD CONSTRAINT UNIQUE builds an index under ACCESS EXCLUSIVE. Use CREATE INDEX CONCURRENTLY (Recipe D); for UNIQUE, build the index concurrently then attach it with ADD CONSTRAINT ... USING INDEX.

5. Drops and renames during rolling deploys (RISKY)

DROP COLUMN and RENAME COLUMN/TABLE break app code still running the old version mid-deploy. Use expand/contract across releases (Recipe E).

The rewrite recipes

Recipe A: lock_timeout guard (wrap every risky ALTER)

SET lock_timeout = '5s';
-- the ALTER here; fails fast instead of cascading, migration runner retries

Recipe B: add a NOT NULL column with a value for all rows

Replaces a volatile-default or NOT NULL add. Steps run as SEPARATE migrations or transactions, not one block: -- 1. add nullable (metadata-only) ALTER TABLE {table} ADD COLUMN {col} {type}; -- 2. set default for NEW rows (metadata-only) ALTER TABLE {table} ALTER COLUMN {col} SET DEFAULT {default_expr}; -- 3. backfill existing rows in batches (never one giant UPDATE) UPDATE {table} SET {col} = {default_expr} WHERE {col} IS NULL AND {pk} IN ( SELECT {pk} FROM {table} WHERE {col} IS NULL LIMIT 10000); -- repeat step 3 until 0 rows affected -- 4. enforce NOT NULL without a full-table lock scan (PG 12+): ALTER TABLE {table} ADD CONSTRAINT {table}_{col}nn CHECK ({col} IS NOT NULL) NOT VALID; ALTER TABLE {table} VALIDATE CONSTRAINT {table}{col}nn; ALTER TABLE {table} ALTER COLUMN {col} SET NOT NULL; -- uses the constraint, skips the scan on PG12+ ALTER TABLE {table} DROP CONSTRAINT {table}{col}_nn;

Recipe C: add a CHECK, NOT NULL, or FK constraint safely

ALTER TABLE {table} ADD CONSTRAINT {name} {constraint} NOT VALID;  -- fast, no scan
ALTER TABLE {table} VALIDATE CONSTRAINT {name};  -- scans with a weaker lock, concurrent reads and writes OK

Recipe D: create an index without blocking writes

CREATE INDEX CONCURRENTLY {name} ON {table} ({cols});
-- CONCURRENTLY cannot run inside a transaction block. With Prisma or Drizzle,
-- isolate this statement so the runner does not wrap it in BEGIN/COMMIT.

Recipe E: drop or rename across releases (expand/contract)

-- Release N:   stop reading and writing the column in app code (deploy first)
-- Release N+1: ALTER TABLE {table} DROP COLUMN {col};
-- Renames: add new col, backfill, dual-write, then drop old. Never RENAME live.