windchillscalanthes-ship-it/safe-migrations

Safe Migrations

Catch the schema changes that quietly lock a table or rewrite it on disk — and turn them into safe, zero-downtime migrations before they reach production.

A single innocent-looking line (SET NOT NULL, CREATE INDEX, ADD FOREIGN KEY, a column with a DEFAULT) can take an ACCESS EXCLUSIVE lock or force a full-table rewrite. On a large, busy table that means every read and write queues behind the migration — an outage. This skill reviews a migration, explains exactly what would lock and why, and produces a safe rewrite.

When to use this

• You are writing a new migration and want it checked before commit.
• You are reviewing a migration in a PR/diff.
• You are about to run a migration against a table that already has rows.
• Someone asks "is this migration safe?", "will this lock the table?", or "how do I do this with zero downtime?".

If the target table is empty or tiny (a fresh table created in the same migration, a config table with a handful of rows), most of these risks don't apply — say so and don't over-engineer.

Workflow

1. Establish context. Identify the engine (PostgreSQL vs MySQL/MariaDB and, if you can, the major version) and the framework. Version matters: several operations are safe on Postgres 11/12+ but not before. If the version is unknown, flag the operations whose safety is version-dependent.

2. Locate the operations. Read each migration file and list every schema or data operation it performs. One migration often bundles several.

3. Classify each operation against the risk catalog below. Assign a severity:

   • 🔴 Unsafe — will take a blocking lock, rewrite the table, or break a running app version. Must be rewritten.
   • 🟡 Caution — safe only under conditions (small table, specific engine version, low traffic, correct settings). Call out the condition.
   • 🟢 Safe — metadata-only or otherwise non-blocking. No change needed.

4. Rewrite the unsafe ones. For each 🔴/🟡, give:

   • the concrete risk (which lock, what it blocks, rough impact),
   • a safe rewrite as runnable steps (SQL and, when a framework is in use, the idiomatic framework code), and
   • the deploy sequence when app code must change in lockstep (see Expand / Contract below).

5. Report using the output template at the end. Lead with the unsafe items.

For engine- and framework-specific depth, load the reference files on demand:

• reference/postgres.md — PostgreSQL locks, versions, and safe rewrites (the most detailed).
• reference/mysql.md — MySQL/MariaDB online DDL, ALGORITHM/LOCK, gh-ost.
• reference/patterns.md — engine-agnostic patterns: expand/contract, batched backfills, lock_timeout, N-1 compatibility.
• reference/frameworks.md — how to express concurrent indexes, disable the DDL transaction, and split state vs. schema in each framework.

Read the reference file relevant to the migration in front of you rather than guessing version-specific behavior.

Risk catalog (the high-frequency offenders)

Postgres-centric; MySQL differences are in reference/mysql.md. "Rewrite" = the whole table is copied on disk; "scan" = full read of the table; both are held under a lock that blocks concurrent traffic.

#	Operation	Why it's dangerous	Safe approach
1	Add column with a DEFAULT	Volatile default (e.g. gen_random_uuid(), random()) rewrites the table under ACCESS EXCLUSIVE. Any default rewrites on Postgres < 11.	Add the column nullable with no default → ALTER … SET DEFAULT (new rows only) → backfill existing rows in batches → add NOT NULL (row 2).
2	SET NOT NULL on an existing column	Full-table scan under ACCESS EXCLUSIVE to verify no nulls — blocks reads and writes.	Add CHECK (col IS NOT NULL) NOT VALID → VALIDATE CONSTRAINT (non-blocking) → SET NOT NULL (Postgres 12+ reuses the validated check and skips the scan) → optionally drop the check.
3	CREATE INDEX (non-concurrent)	Holds a SHARE lock that blocks writes for the entire build.	CREATE INDEX CONCURRENTLY — must run outside a transaction; can leave an INVALID index if it fails (drop and retry).
4	DROP INDEX	ACCESS EXCLUSIVE on the table.	DROP INDEX CONCURRENTLY (Postgres 9.2+).
5	Add FOREIGN KEY	Locks both tables and scans the child to validate existing rows.	Add the constraint NOT VALID (fast, no scan) → VALIDATE CONSTRAINT in a separate step (takes only SHARE UPDATE EXCLUSIVE).
6	Add UNIQUE constraint	Builds a unique index under a blocking lock.	CREATE UNIQUE INDEX CONCURRENTLY → ALTER TABLE … ADD CONSTRAINT … UNIQUE USING INDEX <name> (attaches the existing index, fast).
7	Add CHECK constraint	Scans the table under ACCESS EXCLUSIVE.	ADD CONSTRAINT … CHECK (…) NOT VALID → VALIDATE CONSTRAINT.
8	ALTER COLUMN … TYPE	Usually rewrites the table and its indexes under ACCESS EXCLUSIVE. (Exceptions: widening/removing a varchar length limit is metadata-only.)	Expand/contract: add a new column, dual-write + backfill, switch reads, drop the old column.
9	RENAME column/table	The DDL is instant, but it breaks the running app version the moment it commits.	Never rename in place on a live app. Expand/contract: add new name, dual-write, backfill, switch reads, drop old — across two deploys.
10	DROP COLUMN	App code still referencing it (including SELECT *) breaks; the data is gone (hard to roll back).	Contract phase only: ship app code that stops using the column first, then drop in a later migration.
11	Backfill in one big UPDATE	Long-running transaction → lock contention, table bloat, replication lag.	Batch by primary-key ranges with a commit between batches; keep each batch short. Do it outside the schema migration.
12	No lock_timeout / statement_timeout	Even an instant DDL needs a brief ACCESS EXCLUSIVE lock. If it queues behind a long query, it blocks every query that arrives after it — a cascading stall.	Set a low lock_timeout (and statement_timeout) before DDL so the migration fails fast instead of taking the site down.

Guiding principles

• Expand / contract (parallel change). Any change that isn't backward compatible must be split so the database is simultaneously compatible with the old and new app versions during a rollout. Expand (add, backward compatible) → migrate data / dual-write → contract (remove) — usually across two or more deploys. This is the backbone of renames, type changes, splits, and merges.
• One logical change per migration. Easier to review, reason about, and roll back. Never mix a schema change with a large data backfill in the same transaction.
• N-1 compatibility. During a deploy, both versions of the app run at once. The schema must serve both.
• Separate schema from data. Structural DDL and row backfills belong in different steps (and often different deploys).
• Right-size the advice. Empty/tiny/brand-new tables don't need any of this. Don't turn a two-line change into a five-step dance when the table has 12 rows.

Output template

## Migration Safety Review

**Engine:** <e.g. PostgreSQL 14>  ·  **Framework:** <e.g. Rails>  ·  **Files reviewed:** <n>

### 🔴 <file> — UNSAFE
**Operation:** <what it does>
**Risk:** <which lock / rewrite, what it blocks>
**Likely impact:** <e.g. "global lock on `users` for the duration of a full scan; on a 40M-row table, seconds-to-minutes of blocked traffic">
**Safe rewrite:**
<runnable steps — SQL and/or framework code>
**Deploy sequence:** <only if app code must change in lockstep>

### 🟡 <file> — CAUTION
**Operation / Risk / Condition / Recommendation**

### 🟢 <file> — SAFE
<one line: why it's safe, no action needed>

---
**Summary:** <n unsafe, n caution, n safe.> <one-line bottom line.>

Always show the safe rewrite as something the user can copy and run — not just a description of what to do.