Review a database migration

Migrations that pass in development cause outages in production because production tables are large and busy. The usual causes: a statement that takes an exclusive lock and then waits behind a long transaction while every other query queues behind it; a type change or default that rewrites the whole table; a constraint or NOT NULL that scans the table under lock; a non-concurrent index build that blocks writes; a rename or drop that breaks the old application code still running during a rolling deploy; a data backfill in the same transaction as the schema change; and a down migration that cannot bring dropped data back. Lock behaviour differs by engine and version, so the review must be specific to the database named.

Review this migration for [DATABASE]:

Only if [TABLE_SIZES] is given:

Table sizes and deploy process: [TABLE_SIZES]

1. If it is a framework migration, translate each operation into the SQL the framework will actually run, including implicit transactions and anything the framework adds (default indexes, constraint names, column type mappings).
2. For each statement, determine for this engine and version: the lock it takes and what that lock blocks; whether it rewrites the table or scans it while holding the lock; and how long it would run at the given table sizes. When sizes are missing, say how the risk changes with size.
3. Check each risk:

• Locking without lock_timeout (PostgreSQL) or with long metadata-lock waits (MySQL), and the queue that forms behind a waiting DDL statement.
• Table rewrites: column type changes, volatile defaults, and engine-specific cases (in MySQL, which operations support ALGORITHM=INSTANT or INPLACE with LOCK=NONE and which fall back to COPY).
• Constraints validated under lock: foreign keys, check constraints and NOT NULL on existing columns, and the safer path (NOT VALID then VALIDATE CONSTRAINT in PostgreSQL).
• Index builds that are not concurrent or online, and CONCURRENTLY used inside a transaction (which fails), including how the framework disables its transaction.
• Missing indexes on new foreign-key columns or on columns the shipped code will filter by.
• Unique indexes or constraints added over data that may already contain duplicates.
• Deploy-order breakage: renames, drops and new NOT NULL columns without defaults that old code still running cannot handle, and ORMs that cache column lists.
• Data changes mixed with schema changes: unbatched UPDATE or DELETE on large tables, long transactions and replication lag.
• Irreversibility: drops, narrowing type changes and down migrations that cannot restore data.

1. Write a safer version: split into separate migrations where needed, set timeouts, use concurrent or online operations, move backfills into batched jobs, and follow expand and contract for anything that old and new code must both survive.
2. Give the deploy order relative to application releases, the pre-flight queries to run (duplicate checks, long-running transactions, table sizes), and the rollback for each step.

If the engine version is ambiguous in a way that changes lock behaviour, state the version you assumed.

• Base every lock claim on the named engine and version; when behaviour changed between versions, say from which version it applies.
• Rank findings by outage or data-loss risk, not by style. Do not comment on naming unless it breaks something.
• Never recommend running the migration on production as a test. Pre-flight checks must be read-only.
• Keep the safer version equivalent in end state to the original unless a change is required for safety, and say when it is.
• Separate what you verified from what you inferred. Mark inferences as such.
• When you do not know, say "I don't know" once and state what would settle it.

Verdict

One line: safe to merge | merge with the changes below | do not merge. Then the main reason in one sentence.

Statement analysis

Table: statement | lock taken | blocks | rewrite or scan | estimated duration | risk (low, medium, high).

Findings

Numbered, most severe first. Each: the statement, what goes wrong in production, and the fix.

Safer migration

Code blocks in the same format as the input (SQL or the framework's), split into ordered migrations.

Deploy order

Numbered steps interleaving migrations and application releases.

Pre-flight checks

Read-only SQL to run before deploying, each with what result means stop.

Rollback

Per step: how to undo it, and which steps cannot be undone.