# Reviewing AI-Written Postgres Migrations Before They Run

> An agent writes valid SQL that passes on an empty table and locks a live one. The lock every Postgres migration statement takes, and the review that catches it.

URL: https://www.matthewswong.com/en/blog/ai-database-migration-safety-review/

- Author: Matthews Wong (https://www.matthewswong.com/en/about/)
- Published: 2026-09-15
- Updated: 2026-09-15
- Category: Backend

> **Key takeaway:** An AI agent writes migrations that are valid SQL and pass on an empty test table, then lock a live one. Review every migration diff for the lock each statement takes rather than the logic: ACCESS EXCLUSIVE blocks even a plain SELECT, a queued ALTER blocks everything behind it, and only expand-then-contract survives a rolling deploy.

The migration was three statements long and every one of them was correct. An agent had added a status column, renamed a column that had been misspelled ammount_cents since 2023, and created an index to support the new filter — exactly what I asked for, in valid SQL, applied cleanly to a test database holding twelve rows. The same file went to a table with over 40 million rows, and the API stopped answering before the deploy had finished rolling out.

This is the review I now run on every migration diff, whoever or whatever wrote it. Every lock level and version-specific behaviour below is checked against the PostgreSQL manual rather than recalled: which ALTER TABLE forms take ACCESS EXCLUSIVE, what stopped rewriting the table in Postgres 11 and 12, what a failed concurrent index build leaves behind, and why renaming a column safely is four deploys instead of one.

## Review the locks, not the logic

A review that reads migration SQL for correctness will pass almost everything an agent produces, because the SQL is correct. The question that separates a safe migration from an outage is not what a statement does, it is which lock it takes and how long it holds it. The manual states the default plainly: ALTER TABLE acquires an ACCESS EXCLUSIVE lock unless a subform is explicitly noted otherwise, and when several subcommands are given, the lock acquired is the strictest one any of them requires.

Lock mode

Taken by

What it blocks

ACCESS SHARE

A plain SELECT, and any query that only reads

Nothing except a holder of ACCESS EXCLUSIVE

ROW EXCLUSIVE

INSERT, UPDATE, DELETE

SHARE and stricter modes, never another writer

SHARE UPDATE EXCLUSIVE

CREATE INDEX CONCURRENTLY, VALIDATE CONSTRAINT, VACUUM

Schema changes and other vacuums, not reads or writes

SHARE ROW EXCLUSIVE

ALTER TABLE ADD FOREIGN KEY, on both tables involved

Writers, not readers

ACCESS EXCLUSIVE

Most ALTER TABLE forms, DROP TABLE, TRUNCATE, VACUUM FULL

Every other lock mode, including a plain SELECT

The strictest-lock rule is the one agents trip over, because folding several changes into one ALTER TABLE looks like an optimisation, and on a small table it is one: the manual's own justification for allowing it is that multiple scans or rewrites collapse into a single pass. On a large table it is a trap. A cheap ADD COLUMN sitting next to an ALTER COLUMN TYPE inherits that type change's lock and its full table rewrite, and the diff still reads as one tidy statement.

## The lock it waits for is the lock that hurts

Most migration outages are not caused by the statement running. They are caused by the statement waiting. ACCESS EXCLUSIVE conflicts with every other lock mode, so an ALTER TABLE cannot begin while any open transaction still holds so much as an ACCESS SHARE on the table — a long analytics SELECT, a forgotten psql session, a connection sitting idle inside a transaction. The manual is blunt about what happens next: a transaction seeking a table-level or row-level lock waits indefinitely for the conflicting locks to be released.

That would be survivable if the waiter waited alone. It does not. PostgreSQL queues lock requests rather than granting them out of order, so every SELECT that arrives while the ALTER is queued stops behind it, and the table looks frozen even though the migration has not done a single byte of work yet. Only an ACCESS EXCLUSIVE lock blocks a plain SELECT, which is why this failure belongs to schema changes and to nothing else. The defence is to refuse to wait: bound the wait with lock_timeout, then retry.

lock_timeout aborts any statement that waits longer than the limit while attempting to acquire a lock, and the limit applies separately to each lock acquisition attempt. Retry the whole file rather than the statement, because the abort rolls back the transaction that contained it. Five attempts with a growing pause is usually enough. If it is not, the honest reading is that something is holding a long transaction open, and the migration is not the thing that needs fixing.

> Set both timeouts for the migration session rather than in postgresql.conf — the manual advises against the global setting because it affects every session. And mind the interaction: if statement_timeout is nonzero, setting lock_timeout at or above it is pointless, because the statement timeout would always fire first.

## NOT NULL, and what really changed in Postgres 11, 12 and 18

The most common shape I get from an agent is a new column that is NOT NULL with a default, added in a single statement. On a current server this is nearly free and the review should pass it: since Postgres 11, whose release notes list the change as allowing a column with a non-null default to be added without a table rewrite, the default is evaluated once and stored in the table's metadata, then returned for existing rows on read. What the manual excludes from that fast path is the part worth checking — a volatile default such as clock_timestamp, a stored generated column, an identity column, or a domain type carrying constraints each still rewrite the entire table and all of its indexes.

Adding NOT NULL to a column that already exists is the harder case, and it is the one an agent writes without hesitating. It takes ACCESS EXCLUSIVE and scans every row to prove no NULL is present: no rewrite, but a full scan with reads and writes locked out for its duration. Postgres 12 added the escape hatch, and the manual documents the rule — if a valid CHECK constraint exists that proves no NULL can exist, and it is not dropped in the same command, the table scan is skipped.

The middle statement is what makes the sequence safe. A constraint added NOT VALID commits immediately without scanning, and validating it afterwards acquires only a SHARE UPDATE EXCLUSIVE lock, which does not lock out concurrent updates. Postgres 18 shortens this further by letting ALTER TABLE set the NOT VALID attribute on a not-null constraint directly. Which of those paths applies depends on the server version running in production, not the one on the laptop the migration was tested against, and no agent can read that from your repository.

*The order is blast radius, not how the statement reads. Notice the last band, which is the one that passes review: DROP COLUMN holds its lock for a moment and costs almost nothing to run, and it breaks the release that is still deployed.*

## The column the running release is still selecting

A rolling deploy means two versions of your application talk to one database at the same time, for as long as the rollout takes. That window is the thing an agent has no way to know about, and it is why the cheapest statement in a migration is so often the one that causes the outage. Dropping or renaming a column is a catalog operation that finishes in milliseconds. The old release, still serving traffic, goes on asking for a column that no longer exists.

It is worth reading what an ORM actually sends, because the usual objection — we stopped using that field months ago — misses the mechanism entirely. Mapped entities generate explicit column lists, so a column your code never references by name is still in every SELECT the previous release issues. The fix is not a faster deploy. It is to never remove anything in the same release that stops using it.

Two smaller facts belong in the same review. A dropped column does not hand back its space: the manual notes that immediate reclamation requires one of the rewriting forms of ALTER TABLE, so the disk saving people expect from a drop is not there until something rewrites the table. And a rename is strictly worse than a drop, because it breaks the old release and the new one in opposite directions at the same moment, which is precisely what expand-then-contract exists to prevent.

## CREATE INDEX without CONCURRENTLY, and the invalid index it leaves

A standard index build locks out writes on the table, though not reads, until it finishes. CONCURRENTLY avoids that by building without taking locks that prevent inserts, updates or deletes, and the manual is careful to list what it costs: two scans of the table, a wait for existing transactions to terminate before each scan, significantly more total work, and only one concurrent build on a table at a time. It also cannot run inside a transaction block, which is the detail that breaks migration tools, since most of them wrap each migration file in one by default.

The failure mode is the part I have never seen an agent mention. If the build hits a deadlock or a uniqueness violation, CREATE INDEX fails and leaves an invalid index behind in the catalog. It is ignored for querying because it might be incomplete, it still consumes update overhead on every write to the table, and if it was a unique index it goes on enforcing its uniqueness constraint afterwards — so a failed migration can start rejecting legitimate inserts. Recovery is manual: drop the index and build it again, or rebuild it with REINDEX INDEX CONCURRENTLY.

> An invalid index does not announce itself. Queries simply do not use it, so the symptom is a slow endpoint discovered weeks later, plus write overhead nobody can account for. Put the pg_index check in the post-migration script rather than in your memory.

## A backfill is not one statement

The last thing an agent hands you is usually a single UPDATE that sets the new column for every row. It is correct, it is idempotent, and on a large table it is the most expensive statement in the file. PostgreSQL does not remove the old version of an updated row immediately — that is what makes MVCC work — so an update across the whole table writes a new version of every row and leaves a dead one behind it.

The manual's guidance on what follows is unambiguous: after massive update activity, plain vacuuming may not be satisfactory, and reclaiming the space can require VACUUM FULL or CLUSTER, both of which rewrite the entire table. There is a second cost that only appears in production. The whole update is one transaction, so it commits as one burst of WAL, and every standby has to receive and replay all of it. A read replica falls behind by however long that takes, which is when the tickets about stale data arrive.

Batching over a bounded primary-key range fixes both problems. Each batch is its own transaction, so row locks and dead row versions are released as the job runs and autovacuum keeps pace instead of meeting the whole table at the end, and the WAL reaches the replicas in pieces they can replay between batches. The pause matters as much as the batch size. A backfill that finishes in an hour without anyone noticing is a better outcome than one that finishes in five minutes and pages someone.

## Expand then contract: a rename is four deploys

Every failure above shares one root cause: a migration and the code that depends on it shipped together. Expand-then-contract removes that coupling by making the schema hold the old and the new shape at once, so no running release ever disagrees with the database about what exists. Renaming ammount_cents to amount_cents is the canonical example, and doing it safely takes four deploys.

Deploy one, migration only. Add amount_cents as a nullable column. Nothing reads it, nothing writes it, and rolling back means dropping a column no code has ever touched.

Deploy two, code only. Write both columns on every insert and update. Once every instance is on this release, run the batched backfill: new rows are already correct, so the job only has to catch up the old ones.

Deploy three, code only. Read from amount_cents, keep writing both. This is the reversible cutover — if the numbers look wrong, the next deploy flips reads back and nothing was lost, because the old column has been maintained the whole time.

Deploy four, code and then migration. Stop writing ammount_cents, confirm nothing in the codebase or in any report still references it, and only then drop it. The drop can trail by a release; waiting costs nothing.

Four deploys sounds like ceremony until you count what each one buys. Every step is independently reversible, and no step asks the schema and a running release to agree about a name at the same instant. The backfill is deliberately not a deploy: it is a job that runs between two of them, and it can be stopped and resumed without blocking anything. If a change cannot be expressed in this shape, that is a finding in itself, and it is much better to know it before the migration runs than during it.

*Read the second line of each panel: it says what the currently deployed code is doing, and no two adjacent panels ever require the schema and the code to change in the same instant. That property is the entire point of the sequence.*

## The checklist I run on every migration diff

This is what I ask of a migration now, and it is the same list whether a person or an agent wrote it. It takes about two minutes on a diff, and it has caught something on roughly every third one.

Which lock does each statement take, and does any subcommand upgrade the lock for the whole statement? Combined ALTER TABLE subcommands take the strictest lock any one of them needs.

Does the session set lock_timeout, and is the file safe to rerun from the top after an aborted attempt?

Does anything scan or rewrite the table — a SET NOT NULL with no valid CHECK behind it, an ALTER COLUMN TYPE, a volatile default, a stored generated column, an identity column?

Is every index build CONCURRENTLY, with the tool's transaction wrapper disabled for that file, and is there a check afterwards for an invalid index left behind?

Does the diff drop or rename anything the currently deployed release still selects, including columns the code no longer mentions but the ORM still maps?

Is any backfill batched over a bounded key range, one transaction per batch, with a pause between them?

The honest note to finish on is that an agent is good at this, better than that first draft suggested. Ask for the expand-then-contract sequence by name, give it the row count, the server version and the fact that deploys are rolling, and what comes back is four files that are close to right, with the index build already outside a transaction. The failure was never the model's SQL. It was the request: I asked for a migration, and a migration is exactly what I got.

> Three facts an agent cannot read from your repository decide whether its migration is safe: how many rows the table holds, which Postgres version production runs, and whether deploys are rolling. Put all three in the prompt and the first draft comes back a different shape.

The rule I carry now is short enough to apply while reading a diff: a migration is safe when it can run without the currently deployed release noticing, and finish without a reader waiting. Everything above is a way of testing those two properties. Review for the locks and for the other release, and it stops mattering who wrote the SQL.

## Sources

- PostgreSQL manual, ALTER TABLE — lock levels, table rewrites, SET NOT NULL and NOT VALID constraints
- PostgreSQL manual, Explicit Locking — the lock modes, what each conflicts with, and waiting behaviour
- PostgreSQL manual, CREATE INDEX — building indexes concurrently and the invalid index a failure leaves
- PostgreSQL manual, Client Connection Defaults — lock_timeout and its interaction with statement_timeout
- PostgreSQL manual, Routine Vacuuming — dead row versions left by massive update activity

## Source links

- https://www.postgresql.org/docs/current/sql-altertable.html
- https://www.postgresql.org/docs/current/explicit-locking.html
- https://www.postgresql.org/docs/current/sql-createindex.html
- https://www.postgresql.org/docs/current/runtime-config-client.html
- https://www.postgresql.org/docs/current/routine-vacuuming.html

## FAQ

### Why does an AI-written database migration pass tests and still break production?

Because the tests run against a table with a few rows and production does not. The SQL is valid and the intent is right, so nothing in the diff looks wrong; what changes with scale is the lock. A statement that finishes instantly on twelve rows can hold an ACCESS EXCLUSIVE lock for the length of a full table scan on 40 million, and every read waits behind it.

### Which ALTER TABLE statements take an ACCESS EXCLUSIVE lock in PostgreSQL?

The PostgreSQL manual says an ACCESS EXCLUSIVE lock is acquired for ALTER TABLE unless a subform is explicitly noted otherwise, and that combined subcommands take the strictest lock any of them needs. ADD FOREIGN KEY is one documented exception, needing only SHARE ROW EXCLUSIVE, and VALIDATE CONSTRAINT needs only SHARE UPDATE EXCLUSIVE. ACCESS EXCLUSIVE is also the only mode that blocks a plain SELECT.

### How do I add a NOT NULL constraint to a large Postgres table without a long lock?

Add a CHECK constraint marked NOT VALID first, which commits immediately without scanning the table. Then run VALIDATE CONSTRAINT, which scans under a SHARE UPDATE EXCLUSIVE lock that does not lock out concurrent updates. Finally run SET NOT NULL: since Postgres 12 the manual documents that the scan is skipped when a valid CHECK constraint already proves no NULL can exist.

### What happens if CREATE INDEX CONCURRENTLY fails halfway through?

It leaves an invalid index behind in the catalog. The manual states that such an index is ignored for querying because it might be incomplete, but it still consumes update overhead on every write, and a failed unique build goes on enforcing its uniqueness constraint. The recommended recovery is to drop the index and run the build again, or to rebuild it with REINDEX INDEX CONCURRENTLY.

### How many deploys does it take to rename a database column safely?

Four, if the application is deployed in a rolling fashion. One migration adds the new nullable column, one code deploy writes both columns, one code deploy switches reads to the new column while still writing both, and one stops writing the old column before it is finally dropped. The backfill is a job that runs between deploys, not a deploy of its own.

---

- HTML version: https://www.matthewswong.com/en/blog/ai-database-migration-safety-review/
- Bahasa Indonesia: https://www.matthewswong.com/id/blog/ai-database-migration-safety-review/
- Site index for agents: https://www.matthewswong.com/llms.txt
- Sitemap: https://www.matthewswong.com/sitemap.xml
- API description (OpenAPI): https://www.matthewswong.com/openapi.json