Database Migrations I Can Roll Back at 2am

Description and use

The author explains how to prevent data loss during database migrations by shipping reversible steps, using expand‑and‑contract patterns, and ordering additive and destructive changes correctly.

Problem

A migration with no down step that dropped a column at 2am caused data loss and a three‑hour recovery effort.

Outcome

The author now avoids 2am data‑loss incidents by ensuring reversible migrations, using expand‑and‑contract patterns, and ordering migrations correctly.

Workflow

• Write the up step and a corresponding down step before shipping.
• Test the migration by running up, then down, then up again locally.
• Split risky changes into expand‑and‑contract stages across multiple deploys.
• Deploy additive migrations before code changes; deploy destructive migrations after code changes.
• Ensure rollback works against the live schema without running a down step.
• Make backfills idempotent and set statement timeouts for flaky migrations.
• Use structured logging to monitor the deploy window.
• Maintain a playbook with logging, retries, and migration rules.

Evidence

The worst database mistake I ever made was a migration with no down step, run at 2am, that dropped a column before I had a backup.

Source

I test the down step before I deploy. I run up, then immediately run down, then up again.

Source

The expand and contract way takes four steps across two or more deploys.

Source

The single rule that prevents most 2am incidents: additive migrations run before the deploy, destructive migrations run after.

Source