Database Migration Rollbacks: The Recovery Plan Nobody Writes Until It Is Too Late

You wrote a migration to backfill a status column across your 12-million-row orders table. It passed code review. It ran fine on staging with 1,000 rows. You deployed it on a Tuesday morning and watched the metrics. Five minutes in, the database CPU hits 95%. Connection pool exhaustion kills every endpoint that touches orders. Your pager goes off.

Now you need to roll back. The forward migration is still running, half the rows have status = 'active' and half are still NULL. The old application code expects NULL everywhere and treats it as 'pending'. The new application code, already deployed, crashes when it sees NULL. You have no rollback script.

This is the scenario that every team faces eventually, and the fix is not “be more careful.” The fix is to treat rollbacks as a first-class artifact of every migration, written before the forward migration is deployed, tested in CI, and automated enough that someone woken up at 3 AM can run it without having to think.

The three categories of migration failures

Every migration failure falls into one of three buckets. The rollback strategy is different for each.

Schema failures. An ALTER TABLE locks the table longer than expected, triggers a deadlock with concurrent transactions, or changes a column that the still-running old application version depends on. The rollback is usually another schema change to reverse the DDL.

Data failures. A one-line UPDATE orders SET status = 'active' on a 100-million-row table bloats the table to 5x its original size, fills the WAL, and takes down replication. Or the logic is wrong and it sets the wrong status. The rollback must identify the affected rows and undo the transformation.

Performance failures. The migration itself runs fine, but the new schema triggers a different query plan that is 100x slower for a critical read path. Or a new index build blocks writes long enough to queue requests. The rollback removes the schema change and restores the old indexing strategy.

A good rollback plan handles all three. A rollback that only reverses the DDL but does not repair the data is not a rollback, it is a new incident.

Step 1: Write the rollback before the forward migration

This is the rule that eliminates the 3 AM panic: every forward migration ships with a rollback migration in the same deploy artifact. Not a TODO comment. Not a “we will write it if this fails.” An actual, tested SQL script or set of scripts.

Here is the pattern for three common scenarios.

Adding a nullable column

-- Forward migration
ALTER TABLE orders ADD COLUMN discount_percent numeric(5,2);

-- Rollback migration (safe to run at any time)
ALTER TABLE orders DROP COLUMN discount_percent;

This is the simplest case. The rollback is a one-liner because no data was transformed. But even here, test that no running code references the column before you drop it.

Adding a NOT NULL column with a default

-- Forward migration (expand-migrate-contract style)
-- Step 1: Add nullable
ALTER TABLE users ADD COLUMN email_verified boolean;
-- Step 2 (background): Backfill
UPDATE users SET email_verified = false WHERE email_verified IS NULL;
-- Step 3 (after app deploy): Set NOT NULL
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

-- Rollback migration
ALTER TABLE users ALTER COLUMN email_verified DROP NOT NULL;
-- Wait for app to stop writing to it...
ALTER TABLE users DROP COLUMN email_verified;

The rollback follows the expand-contract pattern in reverse. First relax the constraint, then remove the column. The DROP NOT NULL must happen before the DROP COLUMN because you cannot drop a column while the old app code might still reference it in a NOT NULL insert.

Backfilling data from another column

-- Forward migration (split across two deploys)
-- Deploy 1: add column
ALTER TABLE orders ADD COLUMN status text;
-- Deploy 2: backfill and use
UPDATE orders SET status =
  CASE
    WHEN shipped_at IS NOT NULL THEN 'shipped'
    WHEN paid_at IS NOT NULL THEN 'paid'
    ELSE 'pending'
  END;

-- Rollback migration
UPDATE orders SET status = NULL;
ALTER TABLE orders DROP COLUMN status;

The rollback reverses the data transformation before dropping the column. If the backfill was wrong (e.g., it marked paid orders as pending because of a logic error), the rollback restores the original state so the forward migration can be fixed and re-run.

Adding a new index

-- Forward migration
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at);

-- Rollback migration
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status_created;

Index rollbacks are usually safe because indexes do not change the visible schema. But DROP INDEX CONCURRENTLY still waits for in-progress transactions, so add a timeout guard.

Step 2: Automate rollback detection

The hardest part of a rollback is not running the SQL. It is knowing when to run it. Every migration needs a set of pre-conditions that are checked before it starts and monitored while it runs. When a pre-condition fails, the rollback should trigger automatically or at least present a clear, actionable alert.

Here is the monitoring pattern for migration health.

interface MigrationGuard {
  name: string;
  check: () => Promise<boolean>;
  timeoutMs: number;
  severity: 'warn' | 'abort';
}

async function runWithGuards(
  forwardSql: string,
  rollbackSql: string,
  guards: MigrationGuard[]
): Promise<void> {
  // Check pre-conditions before any SQL runs
  for (const guard of guards) {
    const ok = await withTimeout(guard.check(), guard.timeoutMs);
    if (!ok && guard.severity === 'abort') {
      console.error(`Guard failed: ${guard.name}. Rolling back.`);
      await runSql(rollbackSql);
      return;
    }
    if (!ok) {
      console.warn(`Guard warning: ${guard.name}. Continuing.`);
    }
  }

  await runSql(forwardSql);

  // Post-condition check
  for (const guard of guards) {
    const ok = await withTimeout(guard.check(), guard.timeoutMs);
    if (!ok) {
      console.error(`Post-check failed: ${guard.name}. Rolling back.`);
      await runSql(rollbackSql);
      return;
    }
  }
}

The guards that matter in production:

const migrationGuards: MigrationGuard[] = [
  {
    name: 'database-cpu-below-80%',
    check: async () => {
      const { rows } = await pool.query(
        `SELECT AVG(load_1min) < 0.8 AS ok
         FROM get_db_cpu_metric()`
      );
      return rows[0].ok;
    },
    timeoutMs: 5000,
    severity: 'abort',
  },
  {
    name: 'replication-lag-below-10s',
    check: async () => {
      const { rows } = await pool.query(
        `SELECT pg_wal_lsn_diff(
          pg_current_wal_lsn(),
          replay_lsn
        ) < 256 * 1024 * 1024 AS ok
        FROM pg_stat_replication`
      );
      return rows.length === 0 || rows[0].ok;
    },
    timeoutMs: 5000,
    severity: 'abort',
  },
  {
    name: 'no-long-running-transactions',
    check: async () => {
      const { rows } = await pool.query(
        `SELECT count(*) < 5 AS ok
         FROM pg_stat_activity
         WHERE state = 'active'
           AND now() - query_start > interval '30 seconds'
           AND pid <> pg_backend_pid()`
      );
      return rows[0].ok;
    },
    timeoutMs: 5000,
    severity: 'warn',
  },
];

These guards prevent the most common disaster pattern: a migration starts while the database is already under load and pushes it over the edge. The replication-lag guard prevents a migration from causing cascading replica failures.

For automated rollback in CI, pair each migration with a “canary” query that verifies the new schema behaves as expected:

-- Forward migration
ALTER TABLE orders ADD COLUMN status text;
UPDATE orders SET status = 'pending' WHERE status IS NULL;

-- Canary query (run immediately after forward migration)
-- If this returns rows, the backfill left NULLs, abort
SELECT count(*) FROM orders WHERE status IS NULL;

-- Rollback (triggered if canary fails)
UPDATE orders SET status = NULL;
ALTER TABLE orders DROP COLUMN status;

The canary runs in a transaction with the forward migration. If it fails, the transaction rolls back both the data change and the schema change automatically. This catches logic errors in the backfill before the migration is committed.

Step 3: Test rollbacks in CI

Most teams test forward migrations in CI. Almost no team tests rollbacks. The difference between a tested rollback and an untested one is the difference between 5 minutes of downtime and 2 hours.

Here is the test pattern for a Node.js project using the built-in test runner.

import { test, describe, before, after } from 'node:test';
import assert from 'node:assert';
import { Pool } from 'pg';

describe('migration rollback', () => {
  let pool: Pool;

  before(async () => {
    pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });
    // Start from a known schema state
    await pool.query(`
      CREATE TABLE orders (
        id serial PRIMARY KEY,
        total numeric(10,2) NOT NULL,
        paid_at timestamptz,
        shipped_at timestamptz
      );
      INSERT INTO orders (total, paid_at, shipped_at)
      SELECT
        random() * 500,
        CASE WHEN random() < 0.6 THEN now() - interval '1 day' ELSE NULL END,
        CASE WHEN random() < 0.3 THEN now() - interval '1 hour' ELSE NULL END
      FROM generate_series(1, 10000);
    `);
  });

  after(async () => {
    await pool.query('DROP TABLE IF EXISTS orders CASCADE');
    await pool.end();
  });

  test('rollback reverses schema change', async () => {
    // Apply forward migration
    await pool.query(`
      ALTER TABLE orders ADD COLUMN status text;
    `);
    await pool.query(`
      UPDATE orders SET status =
        CASE
          WHEN shipped_at IS NOT NULL THEN 'shipped'
          WHEN paid_at IS NOT NULL THEN 'paid'
          ELSE 'pending'
        END;
    `);

    // Verify forward migration worked
    const { rows: afterForward } = await pool.query(
      'SELECT count(*) FROM orders WHERE status IS NULL'
    );
    assert.strictEqual(Number(afterForward[0].count), 0);

    // Apply rollback
    await pool.query('UPDATE orders SET status = NULL;');
    await pool.query('ALTER TABLE orders DROP COLUMN status;');

    // Verify column is gone
    const { rows: columns } = await pool.query(`
      SELECT column_name FROM information_schema.columns
      WHERE table_name = 'orders' AND column_name = 'status'
    `);
    assert.strictEqual(columns.length, 0);

    // Verify original table shape is intact
    const { rows: originalColumns } = await pool.query(`
      SELECT column_name FROM information_schema.columns
      WHERE table_name = 'orders'
      ORDER BY ordinal_position
    `);
    assert.deepStrictEqual(
      originalColumns.map((c) => c.column_name),
      ['id', 'total', 'paid_at', 'shipped_at']
    );
  });

  test('rollback preserves untouched rows', async () => {
    // Snapshot current state
    const { rows: snapshot } = await pool.query(
      'SELECT id, total, paid_at, shipped_at FROM orders ORDER BY id'
    );

    // Apply forward migration
    await pool.query(`
      ALTER TABLE orders ADD COLUMN status text;
    `);
    await pool.query(`
      UPDATE orders SET status = 'pending';
    `);

    // Apply rollback
    await pool.query('UPDATE orders SET status = NULL;');
    await pool.query('ALTER TABLE orders DROP COLUMN status;');

    // Verify no columns were altered
    const { rows: afterRollback } = await pool.query(
      'SELECT id, total, paid_at, shipped_at FROM orders ORDER BY id'
    );

    for (let i = 0; i < snapshot.length; i++) {
      assert.strictEqual(
        Number(afterRollback[i].total),
        Number(snapshot[i].total)
      );
      assert.strictEqual(
        afterRollback[i].paid_at?.toISOString(),
        snapshot[i].paid_at?.toISOString()
      );
    }
  });
});

The first test validates schema integrity (the column is gone, original columns are in place). The second test validates data integrity (no column values were corrupted by the rollback). Both must pass before the forward migration is considered deployable.

For teams using a migration framework like node-pg-migrate or knex, the same pattern applies: write a down function that reverses the up function, then write a test that runs up followed by down and asserts the schema is identical to the starting state.

Handling irreversible migrations

Some migrations cannot be cleanly rolled back.

• DROP COLUMN with no backup of the dropped values
• DROP TABLE with no foreign key to prevent it
• ALTER COLUMN ... TYPE that truncates data
• Encrypting or hashing existing column values in place

For these, the rollback is not a SQL script. It is a restore from backup.

The pre-migration checklist for irreversible changes:

1. Take a logical backup of the affected table. Before running DROP COLUMN, dump the column values to S3 with a timestamp.

-- Before DROP COLUMN (backup the data)
COPY (SELECT id, shipping_address FROM orders)
  TO '/tmp/orders_shipping_address_backup_20260701.csv'
  WITH (FORMAT csv, HEADER true);

2. Add a post-migration verification step. Run a query that verifies the data after the migration and alerts if the numbers do not match.

-- Pre-migration count
SELECT count(*) FROM orders WHERE shipping_address IS NOT NULL;
-- Post-DROP count should be 0; alert if the pre-count is nonzero

3. Document the restore procedure. The backup file location, the restore SQL, and the estimated restore time should be in the migration commit message or a runbook linked from the migration. If it takes 4 hours to restore from a logical backup, the team should know that before choosing between rollback and hotfix.

For migrations that transform data in-place (hashing email addresses, encrypting PII), the safest pattern is to write to a new column and drop the old one only after verification.

-- Forward (safe): add encrypted column alongside original
ALTER TABLE users ADD COLUMN email_hash text;
UPDATE users SET email_hash = encode(sha256(email::bytea), 'hex');
-- Deploy app code that reads email_hash, stops reading email
-- Then drop email in a separate deploy

-- Rollback: app switches back to reading email, drop email_hash
ALTER TABLE users DROP COLUMN email_hash;

This expand-migrate-contract approach makes the migration reversible at every stage. The cost is an extra deploy and a few extra days of holding both columns.

The emergency rollback playbook

When a migration breaks production and you cannot find the person who wrote it, here is the decision tree.

1. Is the migration still running? Cancel it. SELECT pg_cancel_backend(pid) for the migration session. If it is an index build, pg_terminate_backend may be necessary (index builds ignore cancel). If the migration was already committed, proceed to step 2.

2. Is the schema change visible to running code? If the migration added a column that the old code ignores, and no data was transformed, apply the rollback immediately. If the migration transformed data that the old code now reads incorrectly, you have two options: roll back the app deploy first, then roll back the migration, or restore from a backup. Roll back the app deploy first. Code deploys are faster than data restores.

3. Is the database under load? Do not run the rollback SQL until the load returns to normal. A rollback that runs UPDATE on a table that is already hot will make things worse. If the migration caused the load, wait for the automatic guard (CPU, replication lag) to trigger the rollback, or manually run it only after confirming the database can absorb the writes.

4. Is the data repairable in place? Sometimes a hotfix is faster than a rollback. If the migration set every order to status = 'active' when it should have been 'pending', a single UPDATE that fixes the value is faster and safer than reversing the whole schema change and re-deploying.

-- Instead of full rollback: hotfix the data
UPDATE orders SET status = 'pending' WHERE status IS NULL;

The hotfix is the right answer when the damage is contained and the fix is simple. It is the wrong answer when the schema itself is broken (wrong column type, missing constraint, bad default) because the next deploy will redeploy the broken migration pattern.

5. Who has the backup? If the rollback SQL does not exist and the data change is irreversible, you are restoring from the last backup. Every team should test this restore at least quarterly. The restore time for a 500 GB database from a point-in-time backup is not 10 minutes, and knowing that number ahead of time changes the decision calculus.

Document this decision tree somewhere your on-call engineer can find it without logging into a wiki that requires SSO. A MIGRATION_EMERGENCY.md file in the repository root, linked from the deploy runbook, is the right place.

Post-rollback reconciliation

A rollback restores the schema and the data to a known state. It does not restore the data that was written by the now-rolled-back application code between the forward migration and the rollback.

Consider this timeline:

• 00:00: Forward migration adds a status column, backfills it, and the app deploys
• 00:05: App code starts writing new orders with status
• 00:10: Incident declared, rollback starts
• 00:12: Rollback drops the status column

Orders 1001 through 1050 were created during the 5-minute window. They were written with a status value in the application code, but the status column no longer exists. Those rows are silently truncated at the database level. The application logs show “inserted order 1001 with status = ‘pending’” but the database has no record of that field.

Three approaches to handling this data gap:

1. Log-based recovery. If your application logs the full insert payload, you can replay the inserts that were lost. This is manual and error-prone but works when the volume is low (tens of rows, not thousands).

2. CDC-based recovery. If you have change data capture enabled, the WAL contains the full row image for every insert and update. Replay the changes from the WAL position of the failed migration to reconstruct the dropped values. This is the cleanest approach but requires CDC to be set up ahead of time.

3. App-level audit log. Write every mutation to an append-only audit table before applying it to the main table. The audit table is not affected by schema rollbacks because it stores the raw payload as JSONB. After the rollback, replay the audit entries that landed during the window.

CREATE TABLE audit.orders_write_log (
  id bigserial PRIMARY KEY,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  operation text NOT NULL, -- 'INSERT', 'UPDATE', 'DELETE'
  record_id integer NOT NULL,
  payload jsonb NOT NULL   -- full row snapshot
);

-- Application code writes to both tables in a transaction
BEGIN;
INSERT INTO orders (...)
  VALUES (...)
  RETURNING id;
INSERT INTO audit.orders_write_log (operation, record_id, payload)
  VALUES ('INSERT', currval('orders_id_seq'), row_to_json(...));
COMMIT;

After the rollback, query the audit log for entries that were written after the migration timestamp and replay them against the restored schema. This is the only approach that does not require an external system (CDC) or manual reconstruction from logs.

Most teams skip this step until they lose data once. After that, the audit table becomes standard operating procedure for every migration that transforms or adds columns.

The takeaway

A migration without a rollback is not a deploy, it is a gamble. The rollback script must exist before the forward migration is merged, tested in CI with the same scrutiny as the forward migration, and paired with automated guards that detect failure conditions before the pager goes off.

The three rules:

1. Write the rollback first. Every migration directory should contain an up.sql and a down.sql. The down.sql is not optional. If the migration is irreversible, the backup-and-restore plan is the rollback, and it must be documented and tested.

2. Test both directions in CI. Run the forward migration, assert the schema and data are correct, run the rollback, and assert the schema and data are restored to the original state. This catches the common failure patterns: rollback SQL that references a column that was renamed in a different migration, rollback SQL that fails because a constraint was added in the same migration window, and data corruption from partial rollbacks.

3. Automate the decision to roll back. Pre-condition guards (CPU, replication lag, active transactions) catch the preventable failures. Post-condition canaries (null count, row count, constraint violations) catch the logic errors. When a guard or canary fails, the safest default is to roll back. An automated rollback that runs unnecessarily is annoying. A manual rollback that runs 20 minutes late is an incident.

Database migrations will fail. The question is not whether it happens, but whether the rollback is a practiced, tested procedure or a 3 AM improvisation. Write the rollback before you need it. Test it like you would test a production deploy. And when the pager goes off, trust the scripts you already validated, not the SQL you are writing under pressure.

---

A note from Yojji

Shipping database schema changes to production without a tested rollback is the most common source of preventable late-night incidents, and it is one of the first things Yojji’s engineering teams fix when they join a project. They bring a structured approach: every migration is paired with a rollback, every rollback is tested in CI with real data shapes, and every deploy pipeline includes pre-condition guards that stop a migration from starting when the database cannot absorb it. This kind of production engineering discipline is what keeps schema changes boring instead of terrifying.

Yojji is an international custom software development company founded in 2016, with offices in Europe, the US, and the UK. Their senior teams specialize in the JavaScript ecosystem (React, Node.js, TypeScript), cloud platforms (AWS, Azure, Google Cloud), and the kind of production-hardened backend architecture that keeps data safe during every deploy.