Real incident: a migration adding a NOT NULL column to a 80M-row users table held AccessExclusiveLock for 8 minutes. All writes against users blocked — auth flows, profile updates, signups. Total outage. Mesrai catches the shape on every schema PR.

The vulnerable diff

sqlmigrations/2026_alter_users.sql

-- migrations/2026_alter_users.sql (the buggy migration)
ALTER TABLE users
  ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

-- On Postgres < 11 or with non-constant default: rewrites every row.
-- On 80M-row users: AccessExclusiveLock held ~8 minutes.
-- All writes against users blocked for that window:
--   - auth (sign-in updates last_login)
--   - signups (INSERT INTO users)
--   - profile updates
--   - email verification

What is wrong

Same shape as cluster 4 post 39, real-incident version. ADD COLUMN with NOT NULL + DEFAULT on Postgres < 11 (or with non-constant default on any version) rewrites every row inside an exclusive lock. On a 80M-row table that took 8 minutes. The fix splits into 3 steps: add nullable column (instant), backfill in batches outside the migration (no lock), then ALTER to NOT NULL + DEFAULT (instant on Postgres 12+).

The attack

Post-mortem:

textexploit

t+0:00 — Migration applied via runner.
t+0:01 — AccessExclusiveLock on users.
t+0:01 — App writes start failing: "could not obtain lock on relation users".
t+0:02 — Auth fails (last_login update). Sign-in unavailable.
t+0:02 — Signups fail. Marketing campaign traffic conversion: 0%.
t+0:05 — Pager: "users table locked"
t+0:06 — Cannot interrupt migration safely (would leave partial state).
t+0:09 — Migration completes. Lock released.
t+0:09 — Traffic recovers.

8 minutes of total auth outage. Real revenue impact.

Avoidable.

Mesrai's review comment

textreview-comment.txt

mesraipilot · Bot · reviewed 30 sec ago

[mesrai] [code-review] [Performance] [Migration-Lock] [critical]

`ADD COLUMN NOT NULL DEFAULT` on `users` will rewrite every row under
AccessExclusiveLock. Users is your largest write-active table — this
will cause a write outage.

Split into 3 safe migrations:

  -- migrations/A_add_nullable.sql (instant, metadata-only)
  ALTER TABLE users ADD COLUMN status TEXT;

  -- migrations/B_backfill.ts (app code, chunked 10K rows/sec, throttled)
  -- runs over hours or days, no lock impact

  -- migrations/C_add_constraints.sql (instant on Postgres 12+)
  ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
  ALTER TABLE users ALTER COLUMN status SET NOT NULL;

On Postgres 11+ with constant default, the original ALTER is metadata-
only and safe. Confirm your version AND your default. The runner
should refuse to run unsafe migrations on tables above a size
threshold.

The fix

sqlmigrations/ (fixed, three files)

-- migrations/A_add_users_status_nullable.sql
ALTER TABLE users ADD COLUMN status TEXT;

-- migrations/B_backfill_users_status.ts  (separate runner)
-- chunked UPDATE in batches of 10K, throttled.

-- migrations/C_add_users_status_constraints.sql
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

Three safe migrations. The whole change rolls out over hours/days with no lock impact at any step.

Why human review missed it

Migration lock incidents are catastrophic but completely preventable. Mesrai catches every ALTER TABLE pattern known to require a rewrite.

Related rules + further reading

Mesrai rule pack: performance/migration-rewrite-on-large-table (cluster 4 ref).

Strong Migrations / Squawk — catalog of unsafe Postgres patterns.

Cluster 4 post 39 covered the same shape; cluster 10 shows the incident cost.

Takeaway

Schema migrations under load need the three-step pattern. Mesrai catches single-step rewrites on every PR.

The Migration That Locked the User Table for 8 Minutes — Avoided