HIVE80lab — Ops notes

Database Migration Checklist for Small Teams

Small teams do not get a database change review board, a DBA, or a staging cluster that matches production. What you get instead is one rule that carries most of the safety: never change a live schema in place — expand, migrate, contract. Add the new shape alongside the old one, move the data in batches, let the application tolerate both, and only then remove what you replaced. This page is the checklist that makes that pattern a habit instead of a heroic memory.

Why in-place ALTERs hurt small teams specifically

On a 40-million-row table, a naive ALTER TABLE ... MODIFY or RENAME can take an exclusive lock for minutes. During those minutes your app cannot read or write. At small-team scale there is no "maintenance mode" customer tolerance and no idle replica fleet to fail over to. The fix is not better hardware — it is making every migration additive first, so the lock window is milliseconds: add a new column or table (fast), copy the data in the background, switch reads and writes over deliberately, and drop the old path in a later, calm PR.

The checklist

  1. Write the undo first, test it on a copy. The undo script is written before the migration and executed against a restored copy of production — not "imagined". An undo that has never run is a rumor.
  2. Answer the five-question change card (same card as your change advisory board): what breaks if this fails, how do we undo it, who is on-call when it ships, what does the customer feel, why this window.
  3. Ship the additive change alone. New column (nullable), new table, new index. No application code in the same deploy — one variable at a time.
  4. Backfill in batches. 5k–50k rows per batch with a pause between batches; watch replica lag and p95 latency as you go. Stop on breach. A backfill during peak traffic is a self-inflicted incident.
  5. Dual-write, then verify with a parity check. Write to both old and new paths behind a flag. Run a comparison job (row counts plus checksums on the changed columns) until divergence is zero for a window you chose on purpose — typically 24–48 hours, not "looks fine".
  6. Cut reads, then writes, deliberately. Flip reads to the new path, watch error rate and the parity check, then flip writes. Keep the old path writing for one more day; it is your rollback, not a habit.
  7. Contract in a later PR. Remove the old column and the dual-write flag days later, after the parity check stayed clean and nobody needed the rollback. Cleanup PRs are also how you keep the checklist honest — a schema with three generations of "temporary" columns is a future incident with extra steps.

The five traps

Worked example: the RENAME that cost a Saturday

An eight-person invoicing SaaS needed to rename orders.status and widen it. One in-place ALTER on the live table: a 14-minute exclusive lock, checkout failing twice, 300 tickets, and a rollback that could not restore the lost writes. The rerun with this checklist: additive status_v2 column (200 ms), batched backfill at 10k rows/batch overnight, dual-write behind a flag, parity check clean for 48 hours, reads cut on a Tuesday morning, writes the next day, cleanup PR the following week. Zero tickets, zero downtime, and the undo script was never needed — which is the point of writing it.

Counter-example: a sister team skipped the parity check because "dual-write is deterministic". A timezone bug meant the new path wrote UTC while the old path wrote local time. The divergence was discovered by a customer whose invoice showed the wrong due date. The parity check is not bureaucracy; it is the only part of the pattern that can be checked before customers are.

Metrics that keep the pattern honest

From the HIVE80lab kit

Related: the change advisory board is where this checklist gets its five questions answered before merge, the deployment rollback checklist covers the code half of a failed change, and the disk-full runbook is what you reach for when the backfill grew the database instead of the free space.