Luca Brunner·
Made Claude Code review my migration plan before touching prod. It caught a lock on a 40M-row table that would have taken us down at noon.
Reviews a planned database migration for locking, backfill, and rollback hazards before it hits production.
Zero-Downtime Migration Plan Reviewer
You are a database reliability engineer reviewing a migration plan before it runs against production Postgres. Assume the database serves live traffic and any exclusive lock longer than one second is an incident.
Here is the context:
- Database: Postgres {{pg_version}}
- Largest affected table: {{table_name}} with approximately {{row_count}} rows
- Peak traffic window: {{peak_hours}}
- Migration SQL / ORM migration code:
{{migration_code}}
Review it in four passes:
1. **Locking analysis** — for every statement, state which lock it takes, on which relation, and for roughly how long given the table size. Call out anything that takes ACCESS EXCLUSIVE or rewrites the table. Suggest the non-blocking alternative (e.g. CREATE INDEX CONCURRENTLY, NOT VALID constraints validated later, adding columns without defaults on old versions).
2. **Backfill strategy** — if data needs to be backfilled, propose a batched approach with batch size, sleep interval, and a progress query. Never backfill in the same transaction as the schema change.
3. **Rollback plan** — write the exact reverse migration, and state which steps are irreversible (dropped columns, lossy type changes). If any step is irreversible, propose a two-deploy expand/contract sequence instead.
4. **Go/No-Go checklist** — a short list I can paste into the PR: what to verify before, during, and after the run, including the statement_timeout and lock_timeout values to set.
Be specific to my SQL, not generic. If the plan is fine, say so briefly — do not invent problems.
Ergebnisse
## Migration Review: add `billing_region` to `orders`
### 1. Locking Analysis
| Statement | Lock | Duration @ 40M rows | Verdict |
|---|---|---|---|
| `ALTER TABLE orders ADD COLUMN billing_region text DEFAULT 'eu'` | ACCESS EXCLUSIVE | **Milliseconds on PG 15** — fast default, no rewrite | ✅ OK |
| `UPDATE orders SET billing_region = ...` (single statement) | ROW EXCLUSIVE, long transaction | **8-15 min**, bloats WAL, blocks vacuum | 🚨 **Blocker** |
| `CREATE INDEX idx_orders_region ON orders(billing_region)` | SHARE — blocks writes | **5-10 min of blocked writes** | 🚨 **Blocker** |
**Fixes:** keep the ADD COLUMN as-is; replace the index with `CREATE INDEX CONCURRENTLY` (run outside the transaction block — your ORM needs `disable_ddl_transaction!`).
### 2. Backfill Strategy
Batch by primary key range, 10,000 rows per batch, 100ms sleep:
```sql
UPDATE orders SET billing_region = compute_region(country_code)
WHERE id BETWEEN $1 AND $1 + 10000 AND billing_region = 'eu';
```
Progress query:
```sql
SELECT count(*) FILTER (WHERE billing_region = 'eu') AS remaining FROM orders;
```
Run via a background job, not the migration. At ~40M rows expect ~70 minutes total — schedule outside 11:00-14:00 peak.
### 3. Rollback Plan
```sql
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_region;
ALTER TABLE orders DROP COLUMN billing_region;
```
Both steps reversible. No expand/contract needed since no reads depend on the column until deploy 2.
### 4. Go/No-Go Checklist
- [ ] `SET lock_timeout = '2s'; SET statement_timeout = '30s';` in the migration session
- [ ] Confirm no long-running transactions: `SELECT * FROM pg_stat_activity WHERE state != 'idle' AND xact_start < now() - interval '5 min';`
- [ ] Index built with CONCURRENTLY and verified `indisvalid = true`
- [ ] Backfill job deployed separately and monitored
- [ ] Alert on replication lag > 10s during backfill
Modell: Claude Code
48 Likes28 SavesScore: 38
2 Kommentare
Jonas Weber·
The lock_timeout in the checklist is the difference between a retry and an outage. Good template.
Marco Rossi·
Would add: check autovacuum settings on the table before a 40M-row backfill. Otherwise solid.