Review a database migration for what it does to production before it runs there

Review this migration for what it will actually do to a live production database, not just whether the SQL is syntactically valid. A migration that is syntactically perfect and locks a hot table for eight minutes during business hours is still a bad migration.

MIGRATION SCRIPT
ALTER TABLE orders ADD COLUMN loyalty_tier VARCHAR(20) NOT NULL DEFAULT 'standard'; followed by a backfill UPDATE orders SET loyalty_tier = ... in the same transaction.

TABLE SIZE AND TRAFFIC CONTEXT
orders has 42 million rows and receives roughly 200 writes per minute during business hours, none at night.

DEPLOY STRATEGY
Rolling deploy across four instances, roughly 6 minutes for all instances to pick up new code, old and new code both serve traffic during that window.

DATABASE ENGINE
PostgreSQL 16

ROLLBACK PLAN IF THIS NEEDS TO BE UNDONE
A corresponding down-migration exists that drops the column; it has been tested against a pre-migration snapshot but not against a partially-completed backfill.

CHECK EACH OF THESE, IN ORDER
1. Locking behavior — for PostgreSQL 16 specifically, does this exact statement take a lock that blocks reads, blocks writes, or blocks both, and for how long is that lock plausibly held against a table of the size described above? A statement that is instant on a thousand-row table can hold a blocking lock for minutes on a hundred-million-row one, and the two cases require citing the actual mechanism, not a generic 'this should be fine.'
2. Backward compatibility during the deploy window — under Rolling deploy across four instances, roughly 6 minutes for all instances to pick up new code, old and new code both serve traffic during that window., old application code and new application code both run against the database simultaneously for some period. Does the schema after this migration still satisfy every query the old code will still issue during that window? A column drop or a rename that the new code expects but the old code does not know about is the single most common way a rolling deploy causes an outage that a migration reviewed in isolation would never catch.
3. Data loss risk — does this migration drop, truncate, or irreversibly transform any column or row, and if so, is there a real, tested way to get that data back if the migration turns out to be wrong, or is the loss permanent the moment it runs?
4. Reversibility — does A corresponding down-migration exists that drops the column; it has been tested against a pre-migration snapshot but not against a partially-completed backfill. actually work against the state the database will be in after this migration ran partway and then failed, not just after it ran to completion? A rollback plan tested only against the fully-succeeded case does not tell you what happens if this fails at row four million of a five-million-row backfill.
5. Index and constraint changes — does adding an index or constraint here lock the table for the duration of the build, and does this database engine support building it concurrently or online instead, if that option exists and was not used.

OUTPUT FORMAT
For each of the five checks: SAFE, RISKY, or BLOCKING, with the specific mechanism, not a generic caution. End with a recommended execution plan — run as-is, run with an explicit statement timeout and lock-wait retry, split into multiple smaller migrations, or do not run this until it is rewritten — and say plainly which of the five checks drove that recommendation.

Do not soften a BLOCKING finding into a RISKY one because the migration is otherwise well-written; the two ratings track different things, and this only tracks whether it is safe to run against this specific database in this specific state.