Schema Migration Without Rollback: Mesrai's Always-Reversible Rule

A schema migration adding a NOT NULL column with a default. Simple ALTER. On any sizeable table on the wrong Postgres version, this rewrites every row in a held-write lock — minutes to hours of downtime.

The vulnerable diff

-- migrations/2026_10_add_status.sql
ALTER TABLE orders
  ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';
-- On Postgres 11+: instant (metadata-only).
-- On Postgres < 11 or with non-trivial default: rewrites every row.
-- On MySQL InnoDB: always rewrites + holds lock.

What is wrong

Postgres 11+ optimized `ADD COLUMN ... DEFAULT` to be metadata-only when the default is a constant — the rewrite is deferred until the row is next touched. Earlier versions, and any case where the default is a non-constant expression, rewrite the entire table inside an exclusive lock. MySQL InnoDB still rewrites in all cases. The safe migration shape splits into three steps: add the column nullable (fast), backfill in batches (no lock), then add the NOT NULL constraint as a separate ALTER (fast metadata change).

The attack

Production impact on a 50M-row table:

-- Single ALTER on Postgres 10 / MySQL:
-- AccessExclusiveLock held for ~22 minutes
-- All writes blocked. Reads blocked.
-- Backend connections stall waiting for the lock.
-- Application observes total outage.

Split migration: zero downtime, batches of 10K rows per second, lock held for <100ms each on the final NOT NULL constraint addition.

Mesrai's review comment

mesraipilot · Bot · reviewed 2 min ago

[mesrai] [code-review] [Performance] [Migration] [high]

`ADD COLUMN NOT NULL DEFAULT` on Postgres < 11 rewrites the entire
table while holding AccessExclusiveLock. Production write lock for
the duration of the rewrite.

Safe migration in three steps:

  -- 1. Add nullable column (instant, metadata only)
  ALTER TABLE orders ADD COLUMN status TEXT;

  -- 2. Backfill in batches outside this migration
  UPDATE orders SET status = 'pending'
   WHERE id BETWEEN <lo> AND <hi> AND status IS NULL;
  -- chunked 10K rows at a time via app code or pg_repack

  -- 3. Constrain (instant validation on Postgres 12+ with NOT VALID)
  ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
  ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

For Postgres 11+ with constant default: the single ALTER is metadata-
only and safe. Confirm your version + your default before relying on
this fast-path.

The fix

-- migrations/2026_10_add_status_step1.sql
ALTER TABLE orders ADD COLUMN status TEXT;

-- migrations/2026_10_add_status_step2_backfill.ts (or job)
-- chunked UPDATE in batches of 10K, throttled, idempotent.

-- migrations/2026_10_add_status_step3.sql
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

Three steps, each one safe to run in production. Step 1 is instant metadata. Step 2 is bounded application work (chunked UPDATE, never locks more than a few rows at a time). Step 3 is instant metadata on Postgres 12+ (NOT NULL validation can be deferred with `NOT VALID` then validated in a separate step if needed).

Why human review missed it

Migration scripts run quickly on dev databases with thousands of rows. The lock impact only shows up at production scale. The author here likely tested locally, saw it complete in 80ms, and assumed it was safe. Mesrai catches every `ADD COLUMN NOT NULL` (with or without DEFAULT) and flags as 'verify Postgres version + table size before merging'.

Related rules + further reading

Mesrai rule pack: performance/migration-table-rewrite — flags ADD COLUMN NOT NULL on tables above a size threshold.

Postgres docs: Notes on ALTER TABLE.

Strong Migrations gem (Ruby) maintains a catalog of unsafe migration patterns applicable here.

Takeaway

Single-step NOT NULL ADD COLUMN is the migration that takes the site down. Three-step is the migration that does not. Mesrai catches the difference.