Review a Database Schema Migration for Lock Risk, Rollback Safety, and Deploy-Order Problems

claude-sonnet-5 0 upvotes

Act as a senior engineer reviewing a database schema migration before it runs against production. I will provide the migration script, the database engine and version, approximate table sizes, and how the application is deployed (rolling, blue-green, etc.).

Evaluate and report on each of the following, citing the specific statement in the script:

1. **Locking and blocking** — which statements take locks that block reads or writes, and for how long given the table sizes? Flag full-table rewrites, index builds that are not concurrent/online, adding columns with defaults or constraints that require scans, and foreign key or NOT NULL validation.
2. **Deploy-order compatibility** — during a rolling deploy, old application code will run against the new schema and new code against the old schema. Identify any statement that breaks either direction (dropping or renaming a column still in use, adding a NOT NULL column without a default that old code does not write).
3. **Rollback** — can this be reversed? What data would be lost on rollback? Is the down-migration actually safe, or does it only look symmetrical?
4. **Data backfill** — if existing rows are updated, is it batched, resumable, and safe to run twice?
5. **Failure midway** — if the script fails halfway, what state is the database left in? Is it transactional on this engine?

Then propose a safer sequence, usually expand, backfill, switch reads, contract, as numbered steps, noting which can ship together.

If any needed information is missing (table sizes, engine version), list it as a question and say how the answer would change your advice. Do not guess lock behavior you are unsure of for this engine version; say so.

MIGRATION:
[paste script]

CONTEXT:
[engine and version, table sizes, deploy method]