Database rollback and the data question — forward-fix vs backward-restore, the migration discipline

Database rollback is the hardest of the five mechanisms because the data is real. The other four rollbacks restore configuration: a Deployment’s pod template, a Terraform resource, a configuration file. None of them have to deal with the data their changes produced. The database change is the data; rolling back the schema does not roll back the rows the schema wrote. The discipline of Part LIX-06 is to recognise that database rollback is forward-fix, not backward-restore, and to design every migration so a forward-fix is always possible.

Why database rollback is fundamentally different

The five mechanisms share a property: their rollback is a configuration change. The Deployment rollback reverts the pod template; the Terraform rollback reverts the resource declaration; the Ansible rollback reverts the playbook. None of them produces persistent state that the rollback must reconcile.

The database is different. A schema change that adds a column produces rows in that column. A schema change that backfills a value produces values. A schema change that drops a column destroys data. The rollback cannot undo the rows; the rollback can only change the schema, and the data the schema now describes is what it is.

flowchart LR
    A["Migration applied"] --> B["Schema + data"]
    B --> C["Old schema cannot read new data"]
    C --> D{"Compensating migration?"}
    D -- "yes" --> E["Forward-fix"]
    D -- "no" --> F["Backup restore"]

The two failure modes:

• The new schema wrote data the old schema cannot read. A column was added with a default; the old application does not know about the column. The rollback to the old schema leaves the column in place (with the default value) but the old application does not read it. The data is recoverable; the application is not broken.
• The old schema wrote data the new schema cannot read. A column was dropped; the new application expects the column. The data is gone; the rollback to the new schema fails because the data is missing. This is the catastrophic case.

The expand-and-contract pattern makes both cases recoverable: every change is applied in two steps (expand, then contract), so the old schema can always read what the new schema wrote, and the data is always reconstructible from the column history.

The forward-fix discipline

A compensating migration is a migration that undoes the effect of a previous migration by transforming the data forward. The schema may or may not return to the previous shape; what matters is that the data the application reads is in the correct state.

-- Bad migration
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;

-- Compensating migration (forward-fix)
UPDATE users SET email_verified = TRUE WHERE created_at < '2024-01-01';

The bad migration added a column with a default of FALSE for all existing users. The application, when reading the column, treats FALSE as “unverified” and asks the user to verify their email; users who had been verified for years are now asked to re-verify. The compensating migration backfills the column with the correct value for users who were verified before the bad migration. The application is correct; the data is correct; no writes are lost.

The migration framework (Flyway, Liquibase, sqitch, Alembic, Rails migrations, Django migrations) is the rollback surface. Each migration has a version, an up script, and optionally a down script. The down script is rarely the rollback; the up script of the next migration is the forward-fix.

When backward-restore is the right answer

Three cases where backward-restore is correct:

• Catastrophic corruption. A migration wrote garbage data that cannot be reconstructed by a compensating migration. The schema is correct; the data is unrecoverable. A restore from a known-good backup is the only option.
• Pre-production rollback. A migration that has not yet been promoted to production has no continuous writes to lose. A restore from the staging backup is trivial; the forward-fix is unnecessary.
• Compliance or audit requirement. Some regulated workloads require that a bad change be reversed atomically, not forward-fixed. The audit trail demands a restore; the compensating migration is not an acceptable record.

In all three cases the restore is preceded by a backup of the current state (so the analysis can continue) and followed by a verify (so the team knows the restore actually succeeded).

pg_dump --no-owner --schema-only $DATABASE > $BACKUP_PATH_SCHEMA
pg_dump --no-owner --data-only $DATABASE > $BACKUP_PATH_DATA

The dump is the rollback’s safety net: even if the forward-fix succeeds, the pre-rollback dump lets the team return to the bad state for forensic analysis.

Expand-and-contract: the migration discipline

The discipline that makes every rollback possible is expand-and-contract: every schema change is applied in two steps, with the old schema still in place until the new schema is proven.

sequenceDiagram
    participant App as Application
    participant DB as Database
    Note over DB: Expand
    App->>DB: Deploy v1 (reads old, writes old + new nullable)
    DB->>DB: Add new column nullable
    Note over DB: Backfill
    App->>DB: Deploy v2 (reads new, writes new)
    DB->>DB: Backfill new column from old
    Note over DB: Contract
    App->>DB: Deploy v3 (reads new, writes new)
    DB->>DB: Drop old column

Three deployments, three migrations, three rollback surfaces:

• After Expand: Roll back to v1’s schema; the new column is dropped. The data is preserved (it was in the old column).
• After Backfill: Roll back to v2’s schema; the new column has the backfilled data, but v2 reads it. The old column is still there with the original data. No data loss.
• After Contract: Roll back to v3’s schema; the old column is gone. But the data is in the new column; v3 reads it. No data loss.

The discipline is that no step is skipped and no step is done simultaneously. A migration that adds and backfills in one step is not expand-and-contract; it is a single destructive migration that cannot be rolled back.

Production discipline

1. Apply every schema change in two steps: expand, then contract. Never add and drop in a single migration.
2. Write a compensating migration for every bad migration. The forward-fix is the rollback; the backup is the safety net, not the rollback.
3. Treat backward-restore as the last resort. Use it only when the corruption is unrecoverable by a migration and the lost writes are an acceptable cost.
4. Test every migration’s rollback path in staging. The expand step’s rollback (drop the new column) must be tested before the expand is applied to production.
5. Dump the database before every migration in production. The dump is the safety net; the forward- fix is the default.

Cross-course references

• This course, Part LVIII-02 (Rolling update) covers the coexistence window that expand-and-contract exploits.
• Terraform for Production Sysadmins - Part XII (State) covers state-driven recovery, which is the cloud-resource analogue of the forward-fix.
• PostgreSQL for Production Sysadmins - Part XXIV (Migrations) covers expand-and-contract with PostgreSQL- specific mechanics.

Quiz