AI Migration Works on Dev, Fails on Prod Schema: The Fix

Q: Is adding a column with a `DEFAULT` actually safe now, or does it rewrite the table?

A **constant** default (a literal value) is safe on every supported Postgres version as of June 2026: the value is stored in the catalog and the `ALTER` is near-instant with no rewrite. A **volatile** default like `now()` or `gen_random_uuid()` still rewrites every row under an exclusive lock — for those, add the column nullable, backfill in batches, then set the default for future rows.

Q: The migration is hung at "waiting for AccessExclusiveLock". What now?

Either cancel it with `SELECT pg_cancel_backend(pid)` (or `pg_terminate_backend(pid)` if cancel does not work) and retry off-hours, or rely on `lock_timeout` for a clean abort next time. Do not let it block reads and writes for many minutes — outages compound as connections pile up. Find the blocker with `SELECT * FROM pg_locks WHERE not granted`.

An AI-written migration runs clean on an empty dev DB but blows up on prod with constraint, lock, or NULL errors. Fix it with prod-clone testing and safe SQL patterns.

Published: May 24, 2026 Updated: Jun 17, 2026 Author: AI Productivity Guide Team 🌐 查看中文版本

The AI wrote a migration to add a NOT NULL column to users. On your laptop it ran in 80ms against a near-empty dev database. In staging it ran in 12s. In production it hung for 9 minutes, then threw null value in column "country" violates not-null constraint. The migration assumes a world that does not match prod: every existing row already has the column populated, no long-running transactions hold locks, and the table has 50 rows, not 50 million.

Fastest fix: before you run anything on prod, restore a recent prod backup into a throwaway database and run the exact migration against it. Almost every AI-generated migration bug surfaces immediately on real row counts and real historical data. If it passes on the clone, rewrite the risky statements using the safe patterns below (constant-default column adds, CREATE INDEX CONCURRENTLY, NOT VALID + VALIDATE for constraints, expand-and-contract for renames) and set a lock_timeout so a stuck migration aborts cleanly instead of stalling the app.

AI-generated migrations are uniquely dangerous because they read clean and confident, and the dev environment lies to you. Production schemas carry legacy data, partial NULL backfills, constraints from old migrations, and table sizes that turn a 0.1s operation into a 30-minute incident.

Which bucket are you in?

Match your symptom to find the cause and jump to the fix.

Symptom you see	Likely cause	Go to
`null value in column "X" violates not-null constraint`	NOT NULL column added with no backfill	Cause 1 / Step 2
Migration “hangs” for minutes, app latency spikes site-wide	Long-held `ACCESS EXCLUSIVE` lock	Cause 2 / Step 7
App writes time out for 30+ min during an index build	`CREATE INDEX` without `CONCURRENTLY`	Cause 3 / Step 3
`check constraint "X" is violated by some row`	Constraint added on data that violates it	Cause 4 / Step 4
Queries on the parent table get slow after the migration ships	FK added without an index on the child column	Cause 5 / Step 5
App throws `column "X" does not exist` right after a clean migration	Single-step rename while old code is running	Cause 6 / Step 6
Unique index on a text column hits duplicate-key errors that “can’t happen”	Migration depends on dev’s collation/locale	Cause 7

Common causes

Ordered by frequency in real incidents.

1. Adding a NOT NULL column with no default and no backfill

AI emits:

ALTER TABLE users ADD COLUMN country text NOT NULL;

Dev: empty table, no rows to violate the constraint. Prod: 8M existing rows, every one of them now has NULL in country, the migration aborts.

How to spot it: the error message contains null value in column "X" violates not-null constraint during the migration.

2. Long-held exclusive lock on a hot table

ALTER TABLE on Postgres takes an ACCESS EXCLUSIVE lock for most operations. If anything else is writing to the table, your migration waits for them; new readers and writers then queue behind your migration. A single ALTER can stall the whole table.

How to spot it: the migration “hangs” for minutes with no progress, then app latency spikes site-wide. Check pg_locks for blocked and blocking pids.

3. Index creation without CONCURRENTLY

AI writes CREATE INDEX idx_users_email ON users(email);. On a 10M-row table this holds a lock that blocks writes for the entire build. CREATE INDEX CONCURRENTLY is required for online index creation.

How to spot it: app writes time out during the index build; the migration takes 30+ minutes.

4. Constraint added on a column with violating data

ALTER TABLE orders ADD CONSTRAINT chk_amount_positive CHECK (amount > 0); succeeds in dev where no rows have amount <= 0. Prod has 47 historical rows with amount = 0. The migration fails.

How to spot it: check constraint "X" is violated by some row on the migration step.

5. Foreign key added without an index on the referencing column

AI adds a FK on orders.user_id referencing users.id. Postgres does not create an index on the referencing column automatically — only the referenced primary-key side is indexed. Without an index on orders.user_id, every delete or update on users does a full table scan on orders. The migration succeeds; the next prod DELETE FROM users WHERE id = ? takes 4 minutes.

How to spot it: after the migration ships, queries that touch the parent table get dramatically slower. EXPLAIN shows sequential scans on the child table.

6. Column rename in a single migration with the app running

ALTER TABLE users RENAME COLUMN name TO full_name; works in dev because no one is hitting it. In prod, the old code is still running, references users.name, and breaks the moment the migration commits. AI did not generate the safe multi-phase rename.

How to spot it: the migration completes successfully, and the app immediately starts throwing column "name" does not exist.

7. Migration assumes a default collation, timezone, or encoding

Dev DB is en_US.UTF-8, prod is C or some legacy locale. Sort orders, case-insensitive comparisons, or text-vs-varchar handling differ. The AI generated a query that depends on dev’s locale behavior.

How to spot it: the migration runs, but a unique index on a text column produces duplicate-key errors that “should not happen”.

Before you start

Confirm the exact prod schema: pg_dump --schema-only or your DB’s equivalent. Do not trust the ORM model.
Get a row count for any table the migration touches: SELECT count(*) FROM <table>.
Check active sessions and locks on prod for the target table.
Ensure you have a tested rollback plan. Every forward migration needs a down migration or a recovery script.
Run the migration against a production clone, not a fresh dev DB. Anonymize the data if needed, but keep the row count and constraint history.

Information to collect

The full migration SQL the AI generated.
Production schema for affected tables: pg_dump --schema-only -t <table>.
Row counts and any NULL distribution: SELECT count(*), count(<column>) FROM <table>.
Current ungranted locks during business hours: SELECT * FROM pg_locks WHERE not granted.
Existing constraints, indexes, and triggers on the target table.
Whether the app does zero-downtime (rolling) deploys or stops the world during the migration.

Step-by-step fix

Ordered to prevent the immediate incident first, then harden.

Step 1: Run the migration against a prod clone

pg_dump prod > prod.dump
createdb prod_clone
pg_restore -d prod_clone prod.dump
psql prod_clone -f migrations/2026_05_add_country.sql

If it fails here, you caught it before prod. Most AI migration bugs surface immediately on real data volume plus real constraints. Run it twice if you can: once to confirm it succeeds, once to confirm your down-migration restores the original state.

Step 2: Add NOT NULL columns the safe way

What “safe” means depends on your Postgres version and the kind of default.

Constant default (a literal like 'US', 0, false): since Postgres 11 (and every supported version as of June 2026 — 14 through 18 are current), a non-volatile default is stored in the table’s metadata and applied on read. There is no table rewrite and the ALTER is near-instant even on huge tables. One statement is safe:

ALTER TABLE users ADD COLUMN country text NOT NULL DEFAULT 'US';

Volatile default (now(), gen_random_uuid(), a per-row computed value), or you must backfill from existing data: there is no constant to store, so a single statement rewrites every row under an exclusive lock. Split it into three phases:

-- Phase 1: nullable add (instant, no rewrite)
ALTER TABLE users ADD COLUMN country text;

-- Phase 2: backfill in chunks so no single transaction is huge
UPDATE users SET country = 'US'
WHERE country IS NULL AND id BETWEEN 1 AND 100000;
-- repeat in batches via a loop, a job, or psql \watch

-- Phase 3: enforce NOT NULL only after the backfill is complete
ALTER TABLE users ALTER COLUMN country SET NOT NULL;

Phase 3 still scans the whole table by default. To skip even that scan on a large table, add a validated CHECK first — Postgres will then prove non-nullness without re-scanning:

ALTER TABLE users ADD CONSTRAINT users_country_not_null
  CHECK (country IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_country_not_null; -- SHARE UPDATE EXCLUSIVE, does not block writes
ALTER TABLE users ALTER COLUMN country SET NOT NULL;          -- scan skipped, the valid CHECK proves it
ALTER TABLE users DROP CONSTRAINT users_country_not_null;     -- optional cleanup

Step 3: Use CONCURRENTLY for index creation on large tables

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

CONCURRENTLY does not block writes. It is slower and cannot run inside a transaction, so if your migration framework wraps each migration in a transaction (Rails, most ORMs do), put this in its own non-transactional migration. If a CONCURRENTLY build fails partway it leaves an INVALID index behind — find it with SELECT * FROM pg_index WHERE NOT indisvalid;, DROP it, and retry.

Step 4: Validate constraints against existing data before adding

Before:

ALTER TABLE orders ADD CONSTRAINT chk_amount_positive CHECK (amount > 0);

Run:

SELECT count(*) FROM orders WHERE NOT (amount > 0);

If non-zero, decide: fix the data, exempt history with NOT VALID, or scope the check to new rows only. Use the two-step pattern so the initial add does not hold an exclusive lock while it scans the whole table:

ALTER TABLE orders ADD CONSTRAINT chk_amount_positive CHECK (amount > 0) NOT VALID;
-- backfill or repair the offending rows
ALTER TABLE orders VALIDATE CONSTRAINT chk_amount_positive;

Adding the CHECK with NOT VALID skips the initial scan, so it takes only a brief ACCESS EXCLUSIVE lock instead of holding one for the whole scan; VALIDATE runs the scan later under a SHARE UPDATE EXCLUSIVE lock, which does not block reads or writes.

Step 5: Add an index before adding a foreign key

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

Two things AI almost never adds: the index on the referencing column (Postgres does not create it for you), and the NOT VALID / VALIDATE split so the FK addition does not hold a long lock while it scans both tables.

Step 6: For renames, use the expand-and-contract pattern

Never do RENAME COLUMN while old code is running. Use three deploys:

-- Deploy 1: add the new column, dual-write at the app layer
ALTER TABLE users ADD COLUMN full_name text;
-- app code now writes to BOTH `name` and `full_name`

-- Deploy 2 (later): read from full_name, stop writing to `name`

-- Deploy 3 (later): drop the old column
ALTER TABLE users DROP COLUMN name;

Tell the AI to generate the expand-and-contract version explicitly; otherwise it defaults to a single destructive rename. The same shape (add, dual-write, backfill, switch reads, drop) covers type changes and column splits.

Step 7: Set a lock timeout on the migration

SET lock_timeout = '5s';
SET statement_timeout = '5min';

If the migration cannot acquire its lock within the lock_timeout, it aborts cleanly instead of queuing behind a long-running query and blocking every request behind it. Retry off-hours. A short lock_timeout is the single most effective guard against an ALTER TABLE taking the whole table down. Most migration frameworks expose this (Rails via disable_ddl_transaction! plus a per-migration timeout, Prisma and Flyway via session settings, strong_migrations enforces it by default).

How to confirm it’s fixed

The migration runs end-to-end against a prod clone with realistic data volume.
No new constraint or index leaves the database with a degraded query plan: run EXPLAIN (ANALYZE, BUFFERS) on the top affected queries and confirm no unexpected sequential scans.
No NULL rows in newly-required columns: SELECT count(*) FROM <table> WHERE <col> IS NULL returns 0.
During the migration, app health metrics (latency, error rate, lock waits) stay in their normal range.
The down-migration / rollback path is tested and actually restores the prior schema.

Long-term prevention

Keep a “migration safety checklist” in CLAUDE.md (or your AI tool’s rules file) and require the AI to walk through it before emitting any migration.
Run a migration linter in CI. squawk statically flags the exact patterns above (missing CONCURRENTLY, missing NOT VALID, unsafe NOT NULL adds); pgroll runs expand-and-contract migrations for you on Postgres; gh-ost covers online MySQL changes.
Always run migrations against a recent prod clone in CI. Catching prod-only failures before deploy is the highest-ROI gate you have.
Enforce statement_timeout and lock_timeout at the database role level for the migration user, not just per session.
Adopt expand-and-contract as a project-wide rule. AI follows it once it sees the pattern in your existing migration history.
Keep migrations small and idempotent: one logical change per file. AI tends to merge schema change plus data backfill into one migration on a large table — reject that in review.

Common pitfalls

Running the AI’s migration against an empty dev DB and declaring it “tested”.
Trusting that the AI knows about CONCURRENTLY, NOT VALID, or expand-and-contract. It usually does not unless you instruct it explicitly.
Assuming any DEFAULT on ADD COLUMN rewrites the table. As of June 2026 a constant default is metadata-only and instant on Postgres 11+; only a volatile default (or pre-11 Postgres) forces a full rewrite. Knowing the difference saves you an unnecessary three-phase migration.
Skipping the index-before-FK step because “the FK already creates an index”. It does not — Postgres indexes only the referenced primary-key side, never the referencing column.
Combining a schema change and a data backfill in one migration on a large table. Split them.
Ignoring lock_timeout and watching the migration block hundreds of queries before someone kills it.
Leaving an INVALID index behind after a failed CREATE INDEX CONCURRENTLY and assuming the column is indexed.

For related issues see AI hallucinated a file, AI removed working logic, and AI code broke the build.

FAQ

Q: The AI wrote a migration that “passed locally”. Why is that not enough?

Local dev DBs have tiny row counts, no concurrent traffic, and no legacy data. Most migration failures are about scale, concurrency, or historical NULLs — none of which exist on your laptop. Always test against a prod clone with the real row count and the real constraint history.

Q: Is adding a column with a DEFAULT actually safe now, or does it rewrite the table?

A constant default (a literal value) is safe on every supported Postgres version as of June 2026: the value is stored in the catalog and the ALTER is near-instant with no rewrite. A volatile default like now() or gen_random_uuid() still rewrites every row under an exclusive lock — for those, add the column nullable, backfill in batches, then set the default for future rows.

Q: Can I just ask the AI to “make this migration safe for production”?

Sometimes. You get far better results by listing the specific rules: CONCURRENTLY for indexes, NOT VALID + VALIDATE for constraints, three-phase NOT NULL adds (or a constant default), expand-and-contract for renames, and a lock_timeout. The AI then follows the named recipes instead of guessing.

Q: The migration is hung at “waiting for AccessExclusiveLock”. What now?

Either cancel it with SELECT pg_cancel_backend(pid) (or pg_terminate_backend(pid) if cancel does not work) and retry off-hours, or rely on lock_timeout for a clean abort next time. Do not let it block reads and writes for many minutes — outages compound as connections pile up. Find the blocker with SELECT * FROM pg_locks WHERE not granted.

Q: My ORM generated the migration, not an AI. Does this still apply?

Yes. Most ORMs default to “convenient”, not “safe at scale”. The same expand-and-contract, NOT VALID, and CONCURRENTLY rules apply to ORM-generated migrations. A linter like squawk or the strong_migrations gem catches the unsafe ones before they reach prod.

Tags: #Troubleshooting #AI coding #migrations #Database #Schema