What this prompt does
This prompt generates a complete, production-ready migration playbook by forcing the AI to reason through six distinct phases rather than jumping straight to SQL. The structure — pre-migration audit, strategy selection, batched scripts, shadow testing, cutover sequence, and rollback — mirrors how experienced DBAs actually approach live migrations. Because you specify [migration_tool], [language], [downtime_tolerance], and [peak_traffic] upfront, the output is calibrated to your exact constraints rather than generic advice.
The dual-write and blue-green framing is intentional. The prompt explicitly asks the AI to identify the application code changes required during a dual-read/write period, which is the part most migration guides skip. Data volume and peak traffic slots force the AI to reason about batching window sizes and locking risk instead of producing a naive ALTER TABLE that will take down a production database.
When to use it
- Renaming or splitting a heavily-used column on a table with tens of millions of rows where a simple
ALTER TABLEwould lock the table for minutes. - Moving from a monolithic MySQL schema to a normalized multi-tenant structure while the application stays live.
- Migrating from one database engine to another (e.g., MySQL to PostgreSQL) with zero-downtime cutover using replication or a tool like
pglogical. - Adding non-nullable columns to a high-write table without a maintenance window, requiring a default-then-backfill approach.
- Merging two databases after an acquisition, where both systems stay live during transition.
Example output
For a migration described as "split users.address text column into user_addresses table, 8M rows, peak 1,200 req/s, MySQL 8, Laravel app, 0s downtime tolerance":
Strategy: Online dual-write (no blue-green needed — schema is additive)
Phase 1 – Add nullable column (seconds, no lock):
ALTER TABLE users ADD COLUMN address_id BIGINT UNSIGNED NULL;
Phase 2 – Backfill in batches of 5,000 rows (estimated 27 min):
INSERT INTO user_addresses (user_id, raw_address, created_at)
SELECT id, address, NOW() FROM users
WHERE id BETWEEN :start AND :end AND address IS NOT NULL;
Phase 3 – Dual-write window (deploy app v2):
UserObserver::saved() writes to both users.address and user_addresses
Go/no-go: address_id fill-rate >= 99.95% on prod replica before cutting reads
Rollback point-of-no-return: after dropping users.address column (irreversible)
Rollback time if before PNR: ~4 minutes (revert deploy, backfill remains harmless)
Pro tips
- Set
[peak_traffic]honestly — if you understate it, the AI will recommend batch sizes that cause replication lag spikes during your actual peak hours. - For
[migration_tool], name the real tool you have access to (gh-ost,pt-online-schema-change,pglogical,AWS DMS) — the prompt produces tool-specific commands rather than pseudocode. - The idempotency requirement in Phase 1 is your resumability guarantee. Tell the AI your
[language](PHP, Python, Go) so the batch script uses your existing DB connection pool, not a raw shell script that bypasses connection limits. - Run the shadow migration on a replica restored from a production backup taken within 24 hours — not a staging environment with sample data. The prompt asks for this; actually do it.
- After cutover, keep the dual-write period running for at least one full traffic cycle (24h for most apps) before dropping the old column. The prompt identifies the point-of-no-return; treat it as a hard gate, not a suggestion.
