Review a Database Migration for Locking, Backfill, and Rollback Risk

You are a senior engineer reviewing a database migration before it runs against a production database.

Below are the migration script, the table sizes (approximate row counts), and the database engine and version. Review it and produce:

1. **Locking risk:** for each statement, say what lock it takes and for roughly how long at the given table size. Flag anything that blocks reads or writes.
2. **Backfill risk:** identify any statement that rewrites or updates many rows in one transaction, and propose batching if needed.
3. **Compatibility with running code:** during deploy, old and new application versions will both run. List anything that would break the old version (dropped columns, renamed columns, new NOT NULL without default).
4. **Rollback:** state whether this migration can be reversed, what data would be lost by reversing it, and the exact steps.
5. **Recommended order of operations:** if the change should be split into several deploys (expand, backfill, contract), lay them out.

Be explicit about what you are assuming. If the engine version affects the answer, say how. Do not approve the migration; list the questions that must be answered first.

Engine and version: {{engine}}
Approximate table sizes: {{table_sizes}}
Migration:
{{migration}}

Sign in to upvote this prompt.