The migration that could not be rolled back, and nobody noticed until it mattered

TL;DR#

A rename is two irreversible operations wearing one name. ALTER TABLE … RENAME COLUMN means the old name stops existing immediately — and your old code, still running on half the fleet during a rolling deploy, is querying the old name.

So the deploy window is an outage window, and "roll back the deploy" does not help, because the schema went with it and the migration's down cannot recover data it already discarded.

Expand-contract is the pattern: add the new thing, write both, migrate readers, then remove the old thing in a later release. Each step is independently reversible, which is the property a down migration cannot provide.

The case this post is about#

Renaming users.name to users.full_name, with a migration that looked complete:

-- V37__rename_name_to_full_name.sql
ALTER TABLE users RENAME COLUMN name TO full_name;

-- V37 down
ALTER TABLE users RENAME COLUMN full_name TO name;

A down existed, it was tested on a local copy, and it worked. The code change went in the same pull request.

The symptom#

Errors for the length of the deploy:

14:30:02  migration V37 applied
14:30:05  ERROR  column "name" does not exist
            at UserRepository.findById  (pods still on the previous image)
14:30:05  …repeated, ~2,400 errors
14:33:41  rolling deploy completes, errors stop

Three and a half minutes of a 500 on any request touching a user — because a rolling deploy means old and new code run simultaneously by design, and the schema can only be one of them.

Then the rollback made it worse. A separate, unrelated bug in the same release prompted a revert:

14:52:10  deploy rolled back to previous image
14:52:12  ERROR  column "full_name" does not exist     ← new code gone, new schema still there

The code went back and the schema did not, because nothing in the rollback path runs a down migration. We then ran V37 down by hand, which produced a third window of errors against the pods that had not finished reverting.

And the genuinely irreversible case, two weeks later: a DROP COLUMN in V41, after which the down was:

ALTER TABLE users ADD COLUMN legacy_tier VARCHAR(20);   -- the column, not the data

The column came back empty. 180,000 rows of legacy_tier were gone, and the down migration had been reviewed and approved as "reversible".

Why the obvious fix failed#

"Write a down migration." We had one. A down restores structure, not data, and it does nothing about the window where one of the two code versions is wrong. It is reversibility theatre for anything destructive.

"Run migrations after the deploy instead of before." Flips which half of the window breaks: now new code runs against the old schema. There is no ordering that makes a single incompatible change safe, which is the actual insight.

"Take a maintenance window." Honest, and it works. It is also a product cost for something solvable, and it does not help with the rollback case — a window gets you through the migration, not through a revert three hours later.

"Snapshot before migrating." We did, and restoring a snapshot means losing every write since it was taken. For a destructive change it is the last resort you want to have and not the plan.

The actual mechanism#

Two independent facts that interact badly.

A rolling deploy runs two code versions at once. That is what makes it zero-downtime. For some period, both the old and new image serve traffic.

A schema migration is atomic and global. The database is in one state. There is no per-pod schema.

So any change where old code and new code need different schemas has a window in which one of them is broken. A rename is exactly that: old code needs name, new code needs full_name, and the column has one name.

And destructive operations add a second problem on top:

DROP COLUMN discards data. There is no inverse. A down that re-adds the column restores the shape and not the contents. The operation is irreversible at the moment it commits, regardless of what the migration file claims.

A migration is safe when both the current and the next code version work against it. That is the test, and it immediately rules out renames, drops, and most constraint tightenings as single steps.

That test is mechanical enough to run in CI on the migration file itself, before anyone reviews the deploy plan:

import re

# Each rule: (pattern, verdict, why). Order matters — the first match wins, so the
# narrow safe cases sit above the broad unsafe ones they would otherwise be caught by.
RULES = [
    (r"ADD COLUMN .* (NULL|DEFAULT)",  "safe",      "old code ignores it, new code reads it"),
    (r"CREATE INDEX CONCURRENTLY",     "safe",      "no exclusive lock, no schema contract change"),
    (r"ADD COLUMN .* NOT NULL(?!.*DEFAULT)", "unsafe",
                                       "old code INSERTs without it and fails"),
    (r"RENAME COLUMN|RENAME TO",       "unsafe",    "one name, two code versions needing different ones"),
    (r"DROP COLUMN|DROP TABLE",        "destructive", "no inverse — a down() restores shape, not data"),
    (r"ALTER COLUMN .* SET NOT NULL",  "unsafe",    "old code may still write NULL"),
    (r"ADD CONSTRAINT .* CHECK(?!.*NOT VALID)", "unsafe",
                                       "validates existing rows under an exclusive lock"),
]

def lint(sql):
    findings = []
    for statement in [s.strip() for s in sql.split(";") if s.strip()]:
        verdict, why = "safe", "no rule matched"
        for pattern, v, reason in RULES:
            if re.search(pattern, statement, re.I):
                verdict, why = v, reason
                break
        if verdict != "safe":
            findings.append((verdict, statement.splitlines()[0][:60], why))
    return findings

On the named case — the migration as it was written and merged:

$ migration-lint db/migrate/20260914_rename_name.sql

unsafe       ALTER TABLE users RENAME COLUMN name TO full_name
             one name, two code versions needing different ones
destructive  ALTER TABLE users DROP COLUMN legacy_name
             no inverse — a down() restores shape, not data

Both statements were in one file, and the file had a down() that looked complete. The linter's value is not that it is clever — it is that it reads the SQL rather than the down(), and the down() was the thing everybody had been reassured by.

The fix#

Expand-contract, across three releases. More steps, each one boring.

Release 1 — expand. Add the new column; write both; read the old one:

-- V37__add_full_name.sql   (additive: old code unaffected)
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

// code: write both, read the old
fun save(u: User) = db.update("UPDATE users SET name = ?, full_name = ? WHERE id = ?",
                              u.fullName, u.fullName, u.id)
fun load(id: Long): User = db.query("SELECT name FROM users WHERE id = ?", id)

Both code versions work: old code reads and writes name, which is still there and still maintained.

Backfill, as a separate, resumable, non-blocking job:

-- in batches, not one statement — a single UPDATE locks the table
UPDATE users SET full_name = name
WHERE full_name IS NULL AND id BETWEEN ? AND ?;

Batching is not an optimisation here. One UPDATE over 180,000 rows holds locks long enough to time out application queries, which is its own incident.

Release 2 — migrate readers. Still writing both; now reading the new column:

fun load(id: Long): User = db.query("SELECT full_name FROM users WHERE id = ?", id)

Rolling back to Release 1 is safe: both columns are populated.

Release 3 — contract. Stop writing the old column, and only then drop it:

-- V41__drop_name.sql   — only after Release 2 is fully deployed and stable
ALTER TABLE users DROP COLUMN name;

Four properties worth naming:

Every step is independently reversible without data loss, because at every point both columns are valid.

The drop happens when no running code references the old column. Which is a deployment fact, not a code fact — so it needs verifying rather than assuming.

The gap between Release 2 and Release 3 is deliberate. Days, not minutes. It is the window in which you discover that an overnight batch job, a BI query, or a mobile client three versions back was still reading name.

And a check that makes the gate mechanical:

-- before any DROP: has anything read this column recently?
SELECT seq_scan, idx_scan FROM pg_stat_user_tables WHERE relname = 'users';

Better still, a lint rule on the migrations themselves:

# CI: a destructive statement needs an explicit, dated waiver naming the release that stopped using it
if grep -qiE 'DROP (COLUMN|TABLE)|RENAME COLUMN' "$MIGRATION"; then
  grep -q '^-- safe-to-drop: .* release=' "$MIGRATION" \
    || { echo "❌ destructive migration without a safe-to-drop annotation"; exit 1; }
fi

How we proved it#

That the window exists, measured rather than reasoned about. A staging test running both images against one database:

old image + new schema (rename applied)
  requests to /users   2,431 / 2,431 failed    column "name" does not exist

new image + old schema
  requests to /users   2,388 / 2,388 failed    column "full_name" does not exist

Both orderings fully broken is the demonstration that ordering was never the fix.

That expand-contract has no such window:

Release 1 schema (both columns present)
  old image   2,500 / 2,500 OK
  new image   2,500 / 2,500 OK

Release 2 schema (unchanged)
  Release 1 image  2,500 / 2,500 OK
  Release 2 image  2,500 / 2,500 OK

Four combinations green is the property being claimed, and testing the cross combinations is the part that is easy to skip.

That the backfill does not lock the table:

single UPDATE over 180,000 rows:  lock held 41s, 1,840 application queries timed out
batched at 5,000 rows:            max lock 180ms, 0 timeouts

And that the CI guard catches a bare drop:

$ git commit -m 'drop legacy_tier'
❌ destructive migration without a safe-to-drop annotation

One limit I want to be explicit about: the 180,000 rows of legacy_tier were not recovered. We restored a two-week-old snapshot into a scratch database and backfilled what we could join by id, which covered about 94% of rows; the remainder were created after the snapshot and are gone. The process fix came out of that, and the data did not come back. That is the actual cost of a reviewed-and-approved down migration that only restored a column definition.

What this generalizes to#

Reversible means "both versions work", not "there is a down script". The test for any schema, API, protocol or format change is whether the previous and next consumers can both function against it. A down migration answers a different and less useful question.

Two things that change independently cannot change together. Code deploys roll gradually; schema changes are instant and global. Any change requiring them to agree has a window, and the only fix is a sequence where they never need to.

Destructive operations need a waiting period, not just a plan. The days between "stop reading it" and "delete it" are when you find the consumer nobody knew about. Compressing that gap is how the deletion becomes the incident.

A migration's blast radius includes its locks. A correct statement that holds a lock for forty seconds is an outage. Batch anything that touches a large table, and measure the lock rather than the row count.

Related#

• The retry that turned a blip into an outage — another well-reviewed change that amplified
• A version catalog is a contract, and a transitive bump breaks it quietly — invisible changes in review
• Your error message is an API, and changing it breaks someone — contracts you did not know you had

Keep reading#

This is part of 100 practices of senior engineers — the habits that separate five years of experience from one year five times. Follow MobileByteSensei for the rest.