A migration dropping the `old_email` column from `users`. The column was added during a migration two years ago and replaced by `email` six months ago. Most code stopped reading it. One read-replica query in a background job still did. Mesrai flagged it.
The vulnerable diff
-- migrations/2026_10_drop_old_email.sql
ALTER TABLE users DROP COLUMN old_email;
-- Application code in the same release stops reading old_email.
-- But:
-- - Canary on previous version still selects old_email → 500s.
-- - Background worker on different deploy cadence still references it.
-- - Analytics job hits read replica with cached plan → error after column removed.What is wrong
Production has multiple versions of code running simultaneously: canary deployments, blue-green rollouts, sticky-session warmup, read-replica lag, edge zones rolling out hours apart. A migration that removes a column referenced by any of those versions causes errors during the rollout window. The fix is to never ship the drop in the same release as the code change that stopped using the column — phase the change across two releases at least.
The attack
Failure trace from a real incident:
t+0:00 — Migration applied. Column dropped.
t+0:00 — New code deployed, does not reference old_email. Fine.
t+0:01 — Canary instance still on previous version. SELECT old_email FROM users → 42703 column does not exist.
t+0:02 — 5% of requests errored. Pager fires.
t+0:05 — Analytics worker on different deploy cadence — same error.
t+1:30 — Rollback: cannot un-drop a column without backup restore.
t+1:30 — Incident escalates. Production read replica re-built from backup.
t+4:15 — Service fully restored.Reversibility is the property safe migrations have. DROP COLUMN does not have it.
Mesrai's review comment
mesraipilot · Bot · reviewed 2 min ago
[mesrai] [code-review] [Logic] [Migration] [critical]
DROP COLUMN is irreversible and any code path still reading the
column will error immediately. Canaries, background workers, edge
zones, and read-replica plan caches all create windows of
incompatibility.
Two-phase rollout, minimum:
Release N: stop all reads of old_email. Deploy. Verify zero
SELECT references via grep, code-search, and APM.
Release N+M: ALTER TABLE users DROP COLUMN old_email;
(M = at least one full deploy cycle, ideally one week
so any cron / scheduled job has run at least once.)
For columns under heavy use, consider keeping them nullable and
unused for a longer window (a month) before dropping — even after
the code stops reading, you want time to confirm no consumer was
missed.The fix
-- Release N (this PR): no SQL change. App code stops reading old_email.
-- Release N+M (≥ 1 week later, separate PR):
ALTER TABLE users DROP COLUMN old_email;Two-phase. First release stops reads. Second release, weeks later, drops the column. The gap lets any consumer that was missed surface — slow analytics jobs, scheduled tasks, third-party readers of the database all get a chance to stop referencing the column before the drop is permanent.
Why human review missed it
DROP COLUMN looks like a tidy-up. The author sees the application code no longer references the column and assumes it is safe. Production has more readers than the application code — background workers, scheduled jobs, analytics warehouses, sometimes external integrations. Mesrai catches the pattern by cross-referencing the column against every query in the codebase and warning if the drop is in the same release as the last reader removal.
Related rules + further reading
Mesrai rule pack: logic/drop-column-staging — flags DROP COLUMN in the same release as the last application reference removal.
Strong Migrations and Squawk both maintain catalogs of unsafe Postgres ALTERs.
The general principle: schema changes lag application changes; rollouts have multiple code versions in flight simultaneously.
Takeaway
Drop is permanent. Stop reading first. Drop in a separate release. Always.