A schema migration reaches production and turns out to be wrong. When is a scripted rollback actually a viable recovery, and when do you instead forward-fix by shipping a new migration?

## Two different meanings of "rollback" Candidates conflate three things: 1. **Transactional rollback** — a migration that fails mid-statement is undone by the database. On engines with transactional DDL (PostgreSQL, SQL Server) this is automatic and complete. On MySQL or older Oracle, DDL commits implicitly, so a multi-statement migration can leave the schema half-changed. This is free and always desirable; it is not what the interview question is about. 2. **Scripted rollback / down migration** — a second script that undoes the first, executed deliberately after the change committed. 3. **Restore from backup or point-in-time recovery** — rewinding the whole database, which loses every write since that point. The interesting judgement is about (2) versus forward-fixing, with (3) as the break-glass option. ## Why down migrations disappoint A down script can restore *structure*. It cannot restore *information*. - `ALTER TABLE orders DROP COLUMN legacy_ref` is reversible in shape (`ADD COLUMN legacy_ref`) and irreversible in content — every value is gone. - `UPDATE customers SET country = upper(country)` has no inverse: you cannot tell which values were already uppercase. - `ALTER TABLE t ALTER COLUMN amount TYPE NUMERIC(10,2)` may have truncated precision; widening it back does not restore the lost digits. Even for a genuinely reversible change, running the down script is only safe if nothing began depending on the new state in between. Once the deployed application writes into a new column, dropping it discards production data written by the current version — a data-loss incident dressed up as a recovery. And there is the maintenance argument: down scripts are written speculatively and executed almost never, so they rot. A rollback path exercised for the first time during an incident is exactly the code you do not want to trust. ## When rollback is genuinely fine The safe envelope is narrow but real: - The migration was **purely additive** — new table, new nullable column, new index, new non-validated constraint. - **No writes yet depend on it**, either because the code using it has not been enabled or because you can prove the new object is empty. - The window is **short**, minutes rather than days. - The rollback itself is cheap — dropping an index built over hours is instant, but you will pay for it again. A classic legitimate case: a new index degraded the plan for an important query, and dropping it immediately restores previous behaviour with zero data implication. ## Forward-fix as the default Forward-fixing means diagnosing what the schema now is and appending a migration that moves it to what it should be. Advantages: - The path is the same one every environment exercises constantly, so it is well tested. - The migration history stays a truthful, linear narrative — including the mistake and the correction — which is what you want during a post-mortem. - It composes with the application: you can ship a code fix and a schema fix as one ordinary deploy rather than an exceptional procedure. What makes forward-fix always available is designing changes so the *previous* application version still works against the *new* schema. Then a bad deploy is recovered by rolling back the **code** — fast, safe, well-understood — while the schema simply stays ahead. That is why teams separate additive schema changes from destructive ones and defer the destructive half by days or weeks. ## Making destructive changes recoverable When you truly must remove information, buy yourself an undo: - Copy first: `CREATE TABLE customer_legacy_ref_backup AS SELECT id, legacy_ref FROM customer` in the migration before the drop, and remove the backup table in a much later migration. - Rename instead of drop as the first step (`ALTER TABLE … RENAME COLUMN x TO x_deprecated`); reversal is a rename back, and the data survives. - For big data transformations, write the result into a new column and keep the original until you are confident. Each turns "irreversible" into "reversible for a bounded period", which is what actually makes an incident survivable. ## The break-glass option Point-in-time recovery is the last resort: it restores everything, including data you wanted to keep, and it costs downtime. It is the correct call for genuine corruption — a migration that mangled a table's contents across the board — and the wrong call for a merely inconvenient schema. Knowing your RPO/RTO and having actually rehearsed a restore is what makes this an option rather than a hope. ## What to say in an interview "Forward-fix by default; keep down scripts only for additive changes and treat them as convenience, not safety; make destructive steps deferrable and reversible by copying data first; and rely on rolling back the *application* rather than the schema, which is why the schema must stay compatible with the previous release."