Models are great at spitting out ALTER TABLE statements and terrible at remembering that migrations run on live data with existing rows. I got burned adding a NOT NULL column with no default to a table with 2M rows: the migration locked the table and failed halfway.
My fixed prompt forces a safe, phased approach (add nullable, backfill in batches, then enforce) and requires a matching reversible down for every up. It also has to state the locking behavior of each statement before writing it.
How do people prompt for backfills that don't blow up the transaction log on Postgres? Batching by PK range has been my go-to.
You are writing a Postgres migration for table `{TABLE}`. The change: {DESCRIBE_CHANGE}. The table has roughly {ROW_COUNT} rows and is written to in production.
Rules:
- Produce two SQL blocks: `-- UP` and `-- DOWN`. DOWN must fully reverse UP. If a change is truly irreversible, say so explicitly and explain the data-loss risk instead of pretending.
- Never add a `NOT NULL` column without a plan. Use the phased pattern: (1) add column nullable, (2) backfill in batches of {BATCH_SIZE} by primary-key range with a short transaction per batch, (3) add the `NOT NULL` / constraint `NOT VALID` then `VALIDATE CONSTRAINT` separately.
- For each statement, state on its own comment line the lock it takes (e.g. ACCESS EXCLUSIVE, SHARE UPDATE EXCLUSIVE) and whether it blocks reads/writes.
- Prefer `CREATE INDEX CONCURRENTLY` (and note it can't run in a transaction block).
- Wrap only the statements that are safe to group in `BEGIN/COMMIT`; keep concurrent/index and validate steps outside transactions.
- Add a `-- VERIFY` block: SQL that confirms the migration succeeded (row counts, NULL checks, constraint validity) so it can be asserted in CI.
Assume default `READ COMMITTED`. Call out any statement that would take a long lock on a hot table and give the low-lock alternative.