HomeCasa Prompts

Zero-downtime database migration plan (expand and contract)

Plan a production schema change without an outage: expand, migrate and contract phases, batched backfill, verification queries and a rollback path for every step. Designed for ChatGPT, Claude, Gemini and Microsoft Copilot. It includes 5 customizable variables and you can copy it for free in one click.

Advanced · Code · Pending editorial review

Designed for:

Prompt

Act as a database engineer experienced in zero-downtime production migrations.

The change I want to make: {{change}}
- Engine and version: {{engine}}
- Size of the affected tables and load: {{volume}}
- How the application is deployed: {{deployment}}
- Migration tool: {{tool}}

Deliverables:
1. Risk analysis: which statements lock or rewrite the table on this engine and version, and roughly how long they could block at this volume (qualitatively if there is no data).
2. A phased plan using the expand and contract pattern. For each phase: exact SQL, application code changes, which deploy it ships in, and the condition for moving on.
   - Expand: add columns, tables or indexes without breaking the previous code version (no long locks, concurrent index builds where supported).
   - Dual writes or temporary sync between the old and new structure.
   - Batched backfill: an idempotent, resumable script, batch size, pauses, and how to track progress.
   - Switch reads to the new structure.
   - Contract: remove the old structure only once nothing uses it.
3. Verification queries that confirm old and new data match.
4. A rollback plan for each phase, and the point after which going back is no longer trivial.
5. What to monitor during the migration (locks, replication lag, latency, errors) and when to stop.
6. A final checklist for the day of the change.

Constraints:
- Assume two code versions run side by side for a few minutes unless I say otherwise.
- Do not invent table or column names: use mine or [FILL IN].
- If critical information is missing (e.g., replicas or foreign keys), ask me first.
- Recommend rehearsing the whole plan in staging with a realistic copy of the data.

How to use it

  1. Describe the change using your real table and column names.
  2. Say how you deploy (rolling, blue-green, single instance) and which migration tool you use.
  3. Rehearse every phase in staging before touching production.
  4. Attach the plan to the change ticket so the rollback is at hand.

Usage example

Change: split customers.full_name into first_name and last_name · PostgreSQL 15 · 8 million rows · Rolling deploy on Kubernetes · Alembic.

Sample output

1. Risk

On PostgreSQL 15, ADD COLUMN without a default only touches the catalog, but it still needs a brief ACCESS EXCLUSIVE lock: if a long query is ahead of it, every write queues behind it. That is why we set lock_timeout.

2. Phased plan

PhaseDeployMove on when…
ExpandNColumns created without errors
Dual writeN+1All 4 replicas write both structures
BackfillScript0 rows pending
Read newN+2Verification queries return 0
ContractN+3No code reads full_name
-- Phase 1: expand (fail fast instead of blocking the table)
SET lock_timeout = '2s';
ALTER TABLE customers
    ADD COLUMN first_name text,
    ADD COLUMN last_name text;

-- Phase 3: batched backfill, idempotent and resumable by id
UPDATE customers
SET first_name = split_part(full_name, ' ', 1),
    last_name  = NULLIF(substr(full_name,
                   length(split_part(full_name, ' ', 1)) + 2), '')
WHERE id > :last_id AND id <= :last_id + 5000
  AND first_name IS NULL;

Watch out: the split rule is [FILL IN]. Splitting on the first space breaks for compound first names ("Mary Ann") or multi-part surnames; review a sample before running the backfill…

Key facts

Category
Programming & development · SQL
Output type
Code
Level
Advanced
Designed for
ChatGPT, Claude, Gemini and Microsoft Copilot
Verified in
Pending editorial verification
Variables
Schema change, Engine and version, Volume and load, Deployment method and Migration tool
Language
English
Price
Free, no sign-up
Updated
Nov 7, 2026

Frequently asked questions

What is the expand and contract pattern?

It turns one breaking change into several compatible steps: add the new structure without removing the old, migrate data and reads, and drop the old structure last. Every deploy then works with the previous code version.

Why backfill in batches instead of one UPDATE?

A single massive UPDATE can hold row locks for a long time, generate huge log volume and lag replicas. Small, resumable batches limit that impact and let you stop if something goes wrong.

What is this prompt for?

Plan a production schema change without an outage: expand, migrate and contract phases, batched backfill, verification queries and a rollback path for every step.

Which AI tools does it work with?

It is designed for ChatGPT, Claude, Gemini and Microsoft Copilot. We have not verified it in these tools yet, so results may vary between them.

How do I customize the prompt?

Click “Customize” and fill in the variables (Schema change, Engine and version, Volume and load, Deployment method and Migration tool). The text updates instantly and you can then copy it in one click. Values are stored only in your browser.

Do I need experience to use it?

It is advanced: it is meant for people with experience in the topic who can evaluate and adjust the result.

Is this prompt free?

Yes. Every HomeCasa Prompts prompt is free, with no sign-up or subscription. You can also use them in professional projects.

Can I trust the AI output?

Treat it as a high-quality draft: always check data, figures and important claims before publishing or making decisions.

Related prompts