Luca Brunner·
Pasted our 14-step Postgres migration plan and it flagged the two steps that would have locked the users table during peak hours
Reviews a Postgres migration plan step by step for lock risks, long-running transactions, and rollback gaps before anything touches production.
Zero-Downtime Migration Plan Reviewer
You are a senior database reliability engineer reviewing a Postgres migration plan before it runs against production. Be pessimistic: your job is to find the step that takes the site down, not to be encouraging.
Migration plan:
{{migration_plan}}
Context:
- Approximate table sizes and row counts: {{table_sizes}}
- Peak traffic hours (avoid locks during these): {{peak_hours}}
- Postgres version: {{pg_version}}
For EACH step in the plan, analyze:
1. **Lock behavior** — exact lock mode acquired (ACCESS EXCLUSIVE, SHARE UPDATE EXCLUSIVE, etc.) and which reads/writes it blocks. Call out any ALTER TABLE that rewrites the table.
2. **Duration risk** — will this scan or rewrite a large table? Estimate whether it completes in milliseconds or minutes given the stated sizes.
3. **Safer alternative** — if risky, give the concrete zero-downtime pattern (e.g. add column NULL without default, backfill in batches, add NOT NULL via CHECK constraint NOT VALID + VALIDATE, CREATE INDEX CONCURRENTLY).
4. **Rollback** — can this step be reverted independently? If not, say what must be true before running it.
Then output:
- A verdict table: step | lock mode | risk (LOW/MED/HIGH) | fix
- A reordered, corrected plan if the original ordering is unsafe
- The two or three questions you would ask the author before approving
Do not soften findings. If a step is fine, one line is enough.
Ergebnisse
## Migration Plan Review — verdict first
Two steps are **HIGH risk** and would lock `users` (48M rows) during your stated peak window. Do not run this plan as ordered.
| Step | Operation | Lock mode | Risk | Fix |
|------|-----------|-----------|------|-----|
| 1 | `CREATE TABLE user_preferences` | ACCESS EXCLUSIVE (new table only) | LOW | Fine as is |
| 2 | `ALTER TABLE users ADD COLUMN tier text DEFAULT 'free'` | ACCESS EXCLUSIVE, **metadata-only on PG 15** | LOW | Fine — constant default is not a rewrite on your version |
| 3 | `ALTER TABLE users ADD COLUMN tier_set_at timestamptz NOT NULL DEFAULT now()` | ACCESS EXCLUSIVE | LOW | Same as step 2, metadata-only |
| 4 | `UPDATE users SET tier = 'pro' WHERE ...` (single statement) | Row locks, but **one 48M-row transaction** | **HIGH** | Batch it: `UPDATE ... WHERE id IN (SELECT id FROM users WHERE tier IS DISTINCT FROM 'pro' AND ... LIMIT 5000)` in a loop with a short sleep. One giant UPDATE bloats WAL, risks replication lag, and holds row locks for minutes |
| 5 | `ALTER TABLE users ALTER COLUMN tier SET NOT NULL` | ACCESS EXCLUSIVE + **full table scan** | **HIGH** | Use `ADD CONSTRAINT tier_not_null CHECK (tier IS NOT NULL) NOT VALID`, then `VALIDATE CONSTRAINT` (takes only SHARE UPDATE EXCLUSIVE), then SET NOT NULL is instant on PG 12+ because it reuses the validated constraint |
| 6 | `CREATE INDEX idx_users_tier ON users(tier)` | SHARE — **blocks writes for the whole build** | **HIGH** | `CREATE INDEX CONCURRENTLY`. Non-negotiable at this table size |
### Corrected ordering
1. Step 1 (new table)
2. Steps 2–3 (metadata-only column adds)
3. Step 6 as `CREATE INDEX CONCURRENTLY` — build it **before** the backfill so the batched UPDATE loop can use it
4. Step 4 as batched backfill, off-peak, monitor `pg_stat_replication` lag between batches
5. Step 5 via CHECK NOT VALID → VALIDATE → SET NOT NULL
### Rollback notes
- Steps 1–3 revert cleanly (`DROP TABLE`, `DROP COLUMN`).
- Step 4 has **no automatic rollback** — snapshot `tier` distribution first (`SELECT tier, count(*) ...`) so you can verify or reverse.
- `CREATE INDEX CONCURRENTLY` can leave an INVALID index on failure — check `pg_index.indisvalid` and drop/retry.
### Questions for the author
1. Is anything reading `tier` before the backfill completes — and does it handle NULL?
2. What is the replication topology? Batched UPDATE pacing depends on how much lag the read replicas tolerate.
3. Why NOT NULL at all — could the application default cover it and skip step 5 entirely?
Modell: Claude Code
48 Likes27 SavesScore: 34
2 Kommentare
Jonas Weber·
The CHECK NOT VALID → VALIDATE trick alone is worth the save. We learned that one the hard way.
Marco Rossi·
Saved. The replication lag question between batches is the part everyone forgets.