Luca Brunner·
Used this to plan a NOT NULL column rollout on a 40M-row table. Zero downtime, three deploys, and the rollback path was written before the first ALTER ran.
Turns a schema change request into a staged expand-contract migration plan with lock analysis, batching, and rollback steps.
Zero-Downtime Postgres Migration Planner
You are a senior database reliability engineer. I need a zero-downtime migration plan for Postgres 16.
Current table DDL:
{{table_ddl}}
Requested change:
{{change_description}}
Traffic profile: {{traffic_profile}} (e.g. 300 writes/sec, hot table, p99-sensitive API in front of it).
Produce a staged migration plan using the expand-contract pattern. Requirements:
1. For every DDL statement, state the exact lock it takes (ACCESS EXCLUSIVE, SHARE UPDATE EXCLUSIVE, etc.) and whether it blocks reads or writes on this Postgres version.
2. Never allow a long-running ACCESS EXCLUSIVE lock. If a statement would rewrite the table or scan it under lock, split it: add column without default, backfill in batches, add constraint as NOT VALID, then VALIDATE separately.
3. Backfill: give me a batched UPDATE loop (batch size, sleep between batches, progress query) that is safe to kill and resume.
4. Split the plan into numbered deploys. Each deploy must leave the system in a state where both the old and the new application code work.
5. For each deploy, write the rollback: the exact SQL to revert, and whether rollback is still possible after the next deploy.
6. End with a pre-flight checklist: statement_timeout and lock_timeout values to set, replication lag check, and what to watch in pg_stat_activity during the run.
Be explicit and pessimistic. If any part of my request cannot be done without downtime, say so directly and propose the closest safe alternative.
Ergebnisse
## Migration Plan: `orders.fulfillment_status` NOT NULL rollout
**Pattern:** expand → backfill → contract, 3 deploys.
### Deploy 1 — Expand (safe, instant)
```sql
SET lock_timeout = '2s';
SET statement_timeout = '10s';
ALTER TABLE orders ADD COLUMN fulfillment_status text;
```
- Lock: ACCESS EXCLUSIVE, but adding a nullable column without default is a catalog-only change on PG16 — millisecond hold.
- App state after deploy: old code ignores the column, new code writes it on insert/update. Both work.
- Rollback: `ALTER TABLE orders DROP COLUMN fulfillment_status;` — safe until Deploy 3.
### Deploy 2 — Backfill (batched, resumable)
```sql
-- run in a loop from a worker, NOT in one transaction
WITH batch AS (
SELECT id FROM orders
WHERE fulfillment_status IS NULL
ORDER BY id
LIMIT 5000
FOR UPDATE SKIP LOCKED
)
UPDATE orders o
SET fulfillment_status = CASE
WHEN o.shipped_at IS NOT NULL THEN 'shipped'
ELSE 'pending' END
FROM batch WHERE o.id = batch.id;
```
- 5,000 rows/batch, 200 ms sleep between batches → ~40 min for 40M rows at your write load.
- Progress: `SELECT count(*) FROM orders WHERE fulfillment_status IS NULL;` (use an index-only estimate if this scans too much).
- Safe to kill and resume: SKIP LOCKED avoids contention with live writes.
### Deploy 3 — Contract
```sql
ALTER TABLE orders ADD CONSTRAINT orders_fstatus_nn
CHECK (fulfillment_status IS NOT NULL) NOT VALID; -- instant
ALTER TABLE orders VALIDATE CONSTRAINT orders_fstatus_nn; -- SHARE UPDATE EXCLUSIVE, does not block writes
ALTER TABLE orders ALTER COLUMN fulfillment_status SET NOT NULL; -- PG12+ uses the validated constraint, no scan
ALTER TABLE orders DROP CONSTRAINT orders_fstatus_nn;
```
- Rollback after this deploy: `ALTER COLUMN ... DROP NOT NULL` — still cheap, but dropping the column now loses backfilled data. Point of no easy return.
### Pre-flight checklist
- `lock_timeout = 2s`, `statement_timeout = 10s` for all DDL sessions — a blocked ALTER queues behind it every other query. Fail fast instead.
- Check `pg_stat_replication` lag < 5s before backfill; pause batches if lag exceeds 30s.
- Watch `pg_stat_activity` for `wait_event_type = 'Lock'` during each deploy.
- Run Deploy 1 and 3 outside your peak window anyway. Cheap insurance.
Modell: Claude Code
21 Likes12 SavesScore: 15
2 Kommentare
Jonas Weber·
The NOT VALID + VALIDATE split is the part most teams miss. This survives code review at my shop.
Priya Nair·
Stealing the SKIP LOCKED backfill loop for our warehouse sync backfills. Nice one.