DB Migration Safety Checker

Not safe as a single migration. Both changes need an expand-contract sequence on an 80M row table.

Lock and rewrite analysis

Safe sequence

1. Add order_status and region as nullable columns. Set lock_timeout = '5s'.
2. Deploy code that writes both status and order_status.
3. Backfill both columns in batches:

UPDATE orders SET order_status = status,
  region = map_region(shipping_country)
WHERE id BETWEEN :start AND :end
  AND order_status IS NULL;

Batches of 10k to 50k rows, a short pause between them.
4. ADD CONSTRAINT region_nn CHECK (region IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT, then SET NOT NULL (skips the scan on PG 12+).
5. Switch reads to order_status, deploy.
6. Stop writing status, then drop it in a later release.

Rollback plan

Steps 1 to 5 are reversible: old code still reads status. Only step 6 is one way, so take it after a full release cycle.

Next steps

• Define map_region and test it on a sample
• Run the backfill on a staging copy first