# Plan a zero-downtime database migration

Creates a step-by-step expand-and-contract plan for a schema or data change, with backfill, verification, and rollback at each step.

- Topic: Software Development
- Framework: Few-Shot
- Level: Standard
- Tags: Write, Plan, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/plan-a-zero-downtime-database-migration

## Prompt

```text
Help me plan a database migration that won't cause downtime or data loss.

**Database and version:** [e.g. "PostgreSQL 15", "MySQL 8", "MongoDB"]
**Current schema:** [RELEVANT TABLES/COLLECTIONS]
**Change I need:** [e.g. "rename a column", "split a table", "change a column type", "add NOT NULL"]
**Table size and traffic:** [ROW COUNT, WRITES PER SECOND - or "unknown"]
**How the app is deployed:** [e.g. "rolling deploys, old and new versions run together for 10 minutes"]
**Migration tooling:** [e.g. "Rails migrations", "Flyway", "Alembic", "raw SQL"]

Produce a phased plan using expand-and-contract:
1. Expand: additive schema changes that old code tolerates.
2. Dual-write or sync: how new data reaches both shapes.
3. Backfill: batched approach, batch size guidance, throttling, and how to resume if it stops.
4. Verify: queries to compare old and new data before switching reads.
5. Switch reads, then stop old writes.
6. Contract: remove old columns or tables only after a safe waiting period.

For every phase include: the deploy or migration step, the SQL or pseudo-code, what could go wrong, how to detect it, and how to roll back.

Specifically flag operations that may take long locks or rewrite the table on my database (e.g. adding a column with a default, creating indexes without the concurrent option, changing types) and the safer alternative. If behavior depends on the exact version, say "[CONFIRM for my version]" instead of guessing.

End with a pre-flight checklist: backups verified, tested on a production-sized copy, monitoring in place, owner on call.
```

## What to fill in

- e.g. "PostgreSQL 15", "MySQL 8", "MongoDB"
- RELEVANT TABLES/COLLECTIONS
- ROW COUNT, WRITES PER SECOND - or "unknown"
- e.g. "Rails migrations", "Flyway", "Alembic", "raw SQL"
- CONFIRM for my version

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.