88 agents work. You get the best.

Rollback-safe database migrations

A migration is safe when every deploy step can be undone and the running application keeps working at every point in between. Ship a schema change as a sequence of small, backward-compatible steps, never as one big switch.

Before you write anything

1. Measure the table: row count, write rate, and whether it sits on a hot path. Over about a million rows or a few hundred writes per second, treat it as large: plan batching and lock limits.
2. Confirm a backup exists and that a restore from it was tested recently. If nobody can say when a restore last worked, stop and say so.
3. Find every reader and writer of the affected columns: application code, background jobs, reports, replicas, ETL. A column is unused only when nothing reads it.

Expand, migrate, contract

Never rename, retype or drop in place. Split the change across deploys:

1. Expand: add the new column or table, nullable or with a constant default, and no constraint that scans the table yet.
2. Dual write: ship code that writes both the old and the new shape. Reads stay on the old one.
3. Backfill: copy existing rows in batches (below).
4. Switch reads: move reads to the new shape behind a flag you can turn off.
5. Contract: after reads have run on the new shape for a full release without errors, stop writing the old shape, then drop it in a later deploy.

Each step is its own deploy and its own rollback. Rolling back step N returns to the state after step N-1; it never means restoring data.

Lock-safe DDL

• Set limits in the migration's session, so a blocked lock fails fast instead of queueing every query behind it:
  • PostgreSQL: SET lock_timeout = '3s'; SET statement_timeout = '15min';
  • MySQL: SET SESSION lock_wait_timeout = 3; and ask for ALGORITHM=INSTANT or ALGORITHM=INPLACE, LOCK=NONE, so the statement fails instead of copying the table.
• Indexes: CREATE INDEX CONCURRENTLY in PostgreSQL (outside a transaction; if it fails, drop the INVALID index it leaves before retrying). For large MySQL tables, online DDL or gh-ost.
• Constraints in PostgreSQL: add them NOT VALID, then VALIDATE CONSTRAINT as a separate step. For NOT NULL, validate a CHECK (col IS NOT NULL) constraint first, then set the column NOT NULL.
• When a statement hits its lock timeout, retry with a short backoff. Do not raise the timeout until it goes through.

Backfills that can stop and resume

• Batch by primary key range, 1,000 to 10,000 rows per batch, each in its own short transaction.
• Make every batch idempotent, so running it twice is harmless: UPDATE t SET new_col = old_col WHERE id BETWEEN $1 AND $2 AND new_col IS NULL.
• Record the last finished key, so the job resumes where it stopped.
• Sleep between batches and watch replication lag. Pause when it passes your threshold, resume when it recovers.
• Run the backfill as a job, never inside the deploy's migration step.

The down migration

Write it in the same change and run up, down and up again against a production-sized copy before merging. When a step cannot be reversed without losing data (a drop, a lossy type change), say so in the migration, keep the old data until the contract step, and require an explicit approval for that step.

Ship it

• Before: dry-run on a staging copy, time it, and write down the expected locks and duration.
• During: watch the error rate, p95 latency, lock waits and replication lag.
• After: compare row counts and a checksum of the backfilled column with the source. Keep the dual write until reads have run clean.

Stop and ask when

• The table has no primary key, or no index you can batch on.
• There is no tested restore.
• The change needs downtime, or one statement is expected to hold a lock for more than a few seconds.

Hand back

The migration files (up and down), the deploy sequence with the step each file belongs to, the backfill command, the checks to run after each step, and every step that cannot be rolled back.