What this prompt does
This prompt assigns the AI a senior database engineer role and makes it produce a phased, executable plan for migrating a live production schema without downtime. You describe the [change_description], name the [db_engine], and state the table scale as [row_count] rows at [rps] writes per second. It returns a numbered runbook: phased steps (add column, backfill, dual-write, cut over, drop old column), the locking behaviour of each step, a backfill batch size, a rollback path per phase, monitoring signals, and a total duration estimate.
The structure works because it breaks a risky single ALTER into reversible phases that each avoid a long exclusive lock. By tying the backfill batch size to [row_count] and [rps], the prompt sizes the work to your actual write rate instead of guessing. The per-phase rollback path means you can abort safely mid-migration, and the monitoring signals (lock waits, replication lag, error rate) tell you when to. [db_engine] governs the exact locking semantics and tool commands.
When to use it
- You need to change a hot, large table without taking downtime during business hours.
- A single ALTER would take a table lock and you need a phased path instead.
- You want a backfill batch size sized to your real row count and write rate.
- You need a rollback plan at every phase, not just at the end.
- You want to know which signals to watch while the migration runs.
- You are splitting or renaming a column on a table that is too big to lock.
Example output
You get a numbered migration runbook: each phase (add column, backfill, dual-write, cut over, drop old column) with the exact SQL or tool commands, its locking behaviour and how to avoid a long lock, a batch-size recommendation, a safe mid-migration rollback path, the monitoring signals to watch, and a total estimated duration for the full online migration.
Pro tips
- State
[row_count]and[rps]accurately — together they drive the backfill batch size, and a wrong write rate yields batches that lock too long or crawl. - Set
[db_engine]to the exact version, since locking behaviour for adding columns and indexes differs across engines and releases. - Make
[change_description]specific, like "split full_name into first_name and last_name," so the dual-write phase is concrete. - Backfill in small batches and dual-write through the transition; the cutover should feel boring by the time you reach it.
- Wire up the monitoring signals before phase one so you can trigger rollback on lock waits or replication lag, not after damage is done.
- Treat the duration estimate as approximate and run the backfill during low-traffic windows to keep lock contention down.
