0
Zero-downtime database schema migration plan
⁂auto-checked, 4 days oldAauraNovice
The prompt
I need to make this schema change: change_description on table table_name in database (production, actively serving traffic, row_count rows).
Produce a plan that avoids locking the table for writes and avoids breaking currently-deployed application code during the rollout. Structure:
1. Break the change into safe, backward-compatible steps (e.g. add-nullable-column before backfill before add-constraint before drop-old-column), each deployable independently.
2. For each step, specify: the exact DDL, whether it takes a lock and for how long is acceptable, and what application code version must already be deployed before this step runs.
3. Backfill strategy: batched, with a way to pause/resume, and an estimate of how long it will take given the row count.
4. The order in which application code changes and migration steps must be deployed relative to each other (expand/contract pattern), stated explicitly as a sequence.
5. A rollback plan for each step that doesn't require restoring from backup.
6. What to monitor during the backfill (replication lag, lock wait time, error rate) to know if it's safe to keep going.
Where it has been run
| Model | Version | People | Broke | Auto | Last |
|---|---|---|---|---|---|
| Claude | Haiku 4.5 | 0 | 0 | 1/1 | 4 days ago |
Example output
Step 1: `ALTER TABLE orders ADD COLUMN currency_code text;` (nullable, no default) - instant, metadata-only lock in Postgres. Deploy this before any app code references it.
Step 2: Deploy app code that writes `currency_code` on new rows (dual-write), while still reading the old implicit-USD assumption elsewhere.
Step 3: Backfill existing rows in batches of 5,000 by primary key range, with a 100ms sleep between batches; at 4.2M rows this takes roughly 90 minutes. Pause if replication lag exceeds 2 seconds.
Step 4: Once backfill completes and is verified (`SELECT count(*) WHERE currency_code IS NULL` returns 0), deploy app code that reads `currency_code` instead of assuming USD.
Step 5: `ALTER TABLE orders ALTER COLUMN currency_code SET NOT NULL;` only after step 4 is fully rolled out, since a premature NOT NULL would break any lagging app instance still doing null writes.
Rollback: each step reverses independently, step 1-3 rollback is a no-op (just stop backfilling), step 5 rollback is `DROP NOT NULL`.
0 comments
Sign in to comment or report what this prompt did for you.
Sign inNo comments yet. Run the prompt and report what you got.