How do I run a database migration without breaking production?
The smoother the migration plan looks, the more it needs someone to argue against it. Data incidents usually come from nobody planning for a mid-way failure, not from a typo in the script.
How to do it
Map who the migration touches
Give the schema change, dependency map, and traffic volume to 'Map the Blast Radius of a Change'. List affected readers and writers, scheduled jobs, reports, and downstream systems; rank the top three by people affected and recovery difficulty; set rollback thresholds. This list is the input to the plan review, not an appendix nobody reads until launch.
Have someone try to veto the plan
Give the plan with data volume, downtime window, rollback approach, and dependent teams to 'Veto this migration plan'. It should list the concrete conditions that make the migration fail, the issues currently deferred as 'we'll deal with it later', and a smaller alternative such as migrating part of the data, dual-writing, or migrating reads only. Only then ask which two gaps would make it acceptable.
Write migration, rollback, and verification together
Give the schema and the goal to 'Write a Reversible Data Migration'. Require batching, re-runnability, and a cap per batch to avoid long transactions and table locks. Mark irreversible steps and how to reconcile after a rollback. Verification SQL should run after backfill, before the switch, and before rollback, each with an expected result and threshold. Ask it to name the step most likely to lose data first.
Indexes follow queries, not columns
Give the schema, data volume, and slow queries to 'Review the Index Design'. For each index, state which query's WHERE or ORDER BY it serves, flag redundant indexes, ones that can never be used, and patterns that disable them. Estimate write and storage cost, then conclude keep, drop, or add. Create new indexes after the migration finishes to avoid extra write amplification.
Verify with real queries, not row counts
After the migration, run the top queries against a shadow database or read replica and give them to 'SQL performance tuning' with table names, row counts, timings, and execution plans. Ask for the bottleneck, a rewritten query, the index it needs, and the write-side cost. Do not experiment in production, and do not treat a successful row count as verification.
Prompts used
FAQ
Does a database migration always require downtime?
No, it depends on the change. Adding a nullable column or building an index concurrently can usually be done online. Changing a column type, splitting a table, or changing a primary key often needs dual writes or batched backfills, with downtime limited to the final switch. Classify the change first, then choose the window.
Does the migration script need a rollback?
Yes, and it should be reviewed with the migration. Dropping a column or overwriting data is irreversible in substance; a rollback script can restore structure, not the data. That is why step 3 asks you to mark the irreversible step and prepare a backup or dual write.
How do I migrate a table with millions of rows?
In batches, with a cap per batch and recorded progress so it can resume. Run it on a small subset or a shadow database first, then move batch by batch. A single large transaction makes lock time and failure cost unpredictable.