When is a migration "small" enough to skip review?

Never. Even adding a default can rewrite or lock a table on older MySQL, and an unbounded `UPDATE` looks tiny in a diff.

Should AI auto-approve simple migrations?

No. Let it suggest improvements and assign a GREEN/YELLOW/RED verdict; a human signs off.

How do I size a migration safely?

Give the reviewer row count, average row size, and concurrent QPS, then plan the lock and backfill from there.

Can I run migrations during business hours?

Small additive, metadata-only changes (with `lock_timeout` set), yes. Schema-altering rewrites or large backfills go off-hours.

Do I need feature flags for a new schema?

Yes, whenever behaviour depends on the new column being populated. Ship the schema dark, backfill, then flip the flag.

What if I can't write a rollback?

Split the migration with expand → migrate → contract. Anything that cannot be rolled back is a hidden risk.

Prompt Library

Database Migration Review Prompts for Safe Schema Changes

12 migration review prompts that catch table-locking ALTERs, backfill races, and silent column drops across Postgres, MySQL, and SQLite. Tested against Opus 4.7 and GPT-5.5.

Published: May 19, 2026 Updated: Jun 06, 2026 Author: AI Productivity Guide Team 🌐 查看中文版本

Most “the migration locked the table” outages share one root cause: a reviewer who typed “looks fine” without knowing the row count or the lock class. A real example from the Postgres community: a three-second ALTER TABLE ... ADD COLUMN queued behind a long-running analytics query, every later query stacked behind that waiting ACCESS EXCLUSIVE lock, and a connection pool of ~2,000 connections was exhausted before anyone noticed — a 45-minute outage from a statement that should have taken three seconds.

A good migration review prompt prevents that. It forces the model to name the table size class, the lock the statement actually takes, the rollback recipe, and a hard rule against bundling schema + data + behaviour into one migration. Below are 12 copy-ready templates plus the lock reference an AI reviewer needs to be useful.

TL;DR

Paste your migration diff into template #1 (“Migration safety triage”) for a GREEN / YELLOW / RED verdict in one pass.
Always give the model the row count — every safety estimate depends on it.
Use Claude Opus 4.7 or GPT-5.5 (both score 58%+ on SWE-bench Pro as of June 2026) for review reasoning; they catch deploy-order hazards smaller models miss.
The non-negotiable rules: CONCURRENTLY for Postgres index builds, SET lock_timeout before any DDL, batched backfills, and a rollback in the same PR.

Who this is for

Anyone who signs off on migrations: DBAs, backend leads, founders shipping pre-launch, and on-call engineers verifying a forward-only release.

Skip these prompts for greenfield schema design (that is a modelling problem, not a migration review) and when you genuinely do not know the table’s row count — get that number first.

The lock reference your AI reviewer needs

A migration review is only as good as its grasp of which statement takes which lock. Paste this table into the model’s context so it stops hand-waving about “might be slow.” Lock behaviour below is Postgres 14+ as of June 2026.

Operation	Lock taken	Blocks reads?	Blocks writes?	Safe pattern
`ADD COLUMN` (no volatile default)	`ACCESS EXCLUSIVE`, metadata-only	No (instant)	Brief	Safe since PG 11; still `SET lock_timeout`
`ADD COLUMN ... DEFAULT <const>`	`ACCESS EXCLUSIVE`, metadata-only	No	Brief	Constant default is stored in catalog, no rewrite
`ADD COLUMN ... NOT NULL` (big table)	Rewrite / long lock	Yes	Yes	Add nullable, backfill, `CHECK ... NOT VALID`, `VALIDATE`
`CREATE INDEX`	`SHARE`	No	Yes	Use `CREATE INDEX CONCURRENTLY` (`SHARE UPDATE EXCLUSIVE`)
`ADD UNIQUE` / `PRIMARY KEY`	`ACCESS EXCLUSIVE`	Yes	Yes	Build index `CONCURRENTLY`, then `ADD CONSTRAINT ... USING INDEX`
`ALTER COLUMN TYPE`	Rewrite	Yes	Yes	Expand-contract with a new column
`DROP COLUMN`	`ACCESS EXCLUSIVE`, metadata-only	No	Brief	Safe, but check no live code still reads it

Two rules the model should enforce on every Postgres migration: set lock_timeout (5 seconds is a common default) so a DDL statement fails fast instead of queueing all traffic behind it, and never let a long DDL sit waiting — a waiting ACCESS EXCLUSIVE lock blocks every conflicting query that arrives after it, which is how a fast statement causes a long outage.

12 copy-ready prompt templates

Each template uses [bracketed] placeholders. Paste the lock table above into the same chat first so the model reasons from real lock semantics. For multi-migration or deploy-order analysis, a 1M-token model (Opus 4.7, Sonnet 4.6, or Gemini 3.1 Pro, all 1M as of June 2026) lets you drop the whole diff plus the calling code in one message.

1. Migration safety triage

Review this migration: [migration SQL]. Output:
(1) Lock duration estimate based on table size class (S < 100k, M < 10M, L > 10M rows),
(2) Concurrent-deploy compatibility — does the old code break against the new schema?
(3) Required backfill,
(4) Rollback recipe,
(5) GREEN / YELLOW / RED verdict with the single biggest risk.

Swap: the migration SQL.

2. NOT NULL on a big table

Plan to add NOT NULL to a table with [rowCount] rows. Steps:
(1) Add a nullable column,
(2) Backfill in batches of N with idle waits,
(3) Verify zero NULLs,
(4) ADD CONSTRAINT ... CHECK (col IS NOT NULL) NOT VALID,
(5) VALIDATE CONSTRAINT (Postgres),
(6) optionally SET NOT NULL once validated.
Specify batch size and a downtime estimate.

Swap: rowCount.

3. DROP / RENAME hazard check

This migration DROPs / RENAMEs `[columnOrTable]`. List which application code
reads / writes it (file:line). Compute the deploy-order risk: if old code reads
it after the new schema applies, you have downtime. Output a deploy-order plan,
or "BLOCK — old code still active".

Swap: columnOrTable.

4. Index creation on a big table

Plan to create an index on a [rowCount]-row table. Decide:
(1) CREATE INDEX CONCURRENTLY (Postgres) or gh-ost / pt-online-schema-change (MySQL)?
(2) Estimated build time and lock impact,
(3) Disk space needed,
(4) How to detect a duplicate-blocked invalid index afterward,
(5) Plan B if cancelled mid-build (drop the INVALID index, retry).

Swap: rowCount.

5. Backfill batching plan

Backfill [nRows] rows of column `[col]`. Plan:
(1) Batch size and sleep between batches,
(2) Idempotency (resume on failure from last committed PK),
(3) Progress tracking,
(4) Replica lag monitoring and a throttle threshold,
(5) Abort criteria.
Output as a runnable script outline.

Swap: nRows, col.

6. Forward-only verification

This migration is forward-only (no DOWN). Verify it is recoverable forward:
(1) If applied partially, can the next deploy re-run it safely (idempotent)?
(2) Is the new schema observable by old code (NULLABLE / DEFAULT)?
(3) Is the behaviour change feature-flag-protected?
Output GO / NO-GO with the blocking item.

7. Rollback recipe

Write a rollback recipe for this migration:
(1) Revert SQL (or compensating writes),
(2) Data restore strategy if rows were transformed in place,
(3) Order relative to the application revert,
(4) Deadline for safe rollback (after which data drift makes it unsafe).

8. Lock-aware DML rewrite

This UPDATE will lock [tableName] for a long time: [sql].
Rewrite it as batched UPDATEs with a lock-friendly WHERE clause, using
keyset (cursor) iteration over the primary key. Show the rewritten SQL plus a
runner skeleton that commits each batch and sleeps between them.

Swap: tableName, sql.

9. Concurrent migration audit

Two migrations land this week: [migA] and [migB]. Check:
(1) Do they touch the same table?
(2) Will they serialize on the same lock?
(3) Is the deploy order specified?
(4) Are both idempotent if one fails mid-deploy?
Output a coordination plan.

Swap: migA, migB.

10. Migration test plan

Generate a test plan for this migration:
(1) Run on a copy of prod-shaped data (not seed fixtures),
(2) Time it under realistic concurrency,
(3) Assert the post-state matches expected (row counts, constraints, indexes valid),
(4) Run app smoke tests against the migrated DB.
Output a runnable checklist.

11. RLS / policy migration review

This migration adds / changes row-level security policies on `[table]`. Verify:
(1) Existing queries still authorise correctly (no accidental lockout),
(2) Service-role / bypass queries are unaffected,
(3) Policy combination — PERMISSIVE (OR) vs RESTRICTIVE (AND),
(4) Tests cover authed, anon, and cross-tenant paths.

Swap: table.

12. Migration post-mortem

A migration caused this incident: [incidentSummary]. Write a brief post-mortem:
(1) What lock / write pattern caused the outage,
(2) Why review didn't catch it,
(3) One process change (e.g., require row-count in the PR description),
(4) One automated check to add (e.g., a linter that flags ADD COLUMN NOT NULL).
200 words max.

Swap: incidentSummary.

Which model to use for migration review

Migration review is a reasoning task, not a generation task: the model has to trace deploy order, lock interactions, and code that reads the changed column. As of June 2026 the two strongest choices are Claude Opus 4.7 (64.3% on SWE-bench Pro) and GPT-5.5 (58.6%); both reason well enough to flag a rename that an old replica still reads. Sonnet 4.6 and Gemini 3.1 Pro are cheaper and fine for single-table triage. All four ship a 1M-token context window, so you can paste the migration diff, the lock table, and the relevant application code together — which is exactly what catches concurrent-deploy hazards.

If you run reviews from the terminal, see Claude Code execution prompts for wiring these templates into an agent that reads the diff and the calling code itself.

Common mistakes

Adding NOT NULL to a big table in one statement (full rewrite, long lock).
Renaming a column while old code still reads it — instant downtime on deploy.
CREATE INDEX without CONCURRENTLY on a hot table.
No lock_timeout, so a waiting DDL queues all traffic behind it.
No rollback recipe, so the first failure becomes a long outage.
Combining schema change + data backfill + behaviour change in one migration.
Skipping the row-count question — every safety check depends on it.
Testing only on seed fixtures, which hide scale-only bugs.

How to push results further

State the table size class in the PR description so the reviewer (human or AI) starts from facts.
Expand → migrate → contract: never do all three in one PR.
Backfills need batches, sleep, replica-lag throttling, and resume-on-failure.
Postgres: CONCURRENTLY for indexes, SET lock_timeout before DDL. MySQL: ALGORITHM=INSTANT when the change qualifies, otherwise gh-ost or pt-online-schema-change.
Test migrations on a copy of prod data, not seed fixtures.
Every migration PR should answer: “If this lands at 3pm Friday, what breaks?”
Keep the rollback in the same PR. If you cannot write it, the migration is not safe.

FAQ

When is a migration “small” enough to skip review?: Never. Even adding a default can rewrite or lock a table on older MySQL, and an unbounded UPDATE looks tiny in a diff.
Should AI auto-approve simple migrations?: No. Let it suggest improvements and assign a GREEN/YELLOW/RED verdict; a human signs off.
How do I size a migration safely?: Give the reviewer row count, average row size, and concurrent QPS, then plan the lock and backfill from there.
Can I run migrations during business hours?: Small additive, metadata-only changes (with lock_timeout set), yes. Schema-altering rewrites or large backfills go off-hours.
Do I need feature flags for a new schema?: Yes, whenever behaviour depends on the new column being populated. Ship the schema dark, backfill, then flip the flag.
What if I can’t write a rollback?: Split the migration with expand → migrate → contract. Anything that cannot be rolled back is a hidden risk.

Tags: #Prompt #Coding #Database #Migration #Schema