2026-09-14·5 min read·Simon

What 40 agent-written migrations got wrong

We asked a coding agent for forty ordinary Postgres migrations and ran the free rule set over the result. Twenty-seven were flagged. The pattern in what it got right and wrong is more interesting than the count.

ai-workflows

postgres

migrations

data

A small experiment. We gave a current coding agent the role of "helpful assistant on a Postgres web app" and asked it, in forty separate requests, for the migrations a team writes in a normal quarter: add a column, add an index, add a foreign key, change a type, rename, drop, backfill, truncate, vacuum. No hints about safety, no linter in the loop, first draft only. Then we ran the eighteen rules the free CLI bundles over the forty files.

The numbers

Result	Files
Clean	13
Flagged, warning	25
Flagged, critical	2
Flagged, note only	1

Rule	Files	What it is
BV003	10	Index created without CONCURRENTLY
BV004	4	Column type change that rewrites the table
BV005	4	Rename or drop inside the deploy window
BV011	4	SET NOT NULL scanning the table under an exclusive lock
BV008	2	Foreign key added without NOT VALID
BV013	1	TRUNCATE in a migration
BV015	1	VACUUM FULL under a full lock
BV014	1	Unbounded UPDATE
BV016	1	DROP INDEX without CONCURRENTLY

What it got right

This is the part worth reading before the part about what it got wrong. Every time the agent was asked for a required column, it added a default: ADD COLUMN status text NOT NULL DEFAULT 'open', ADD COLUMN email_verified boolean NOT NULL DEFAULT false. That is the correct modern form, it is instant on Postgres 11 and later, and BV002 never fired once. The single most famous migration mistake did not appear. Models have read a lot of blog posts about that one.

It also wrote a correct USING clause on every type change, used string_to_array sensibly for the tags split, and wrote a clean CREATE TABLE with a foreign key when asked for a new table. The SQL was never malformed. Not one of the forty files had a syntax error.

What it got wrong, and why it is the same mistake ten times

Ten of the forty files create an index, and all ten do it the plain way. Not one CONCURRENTLY. On a dev database the plain form is faster and simpler, and the agent has no reason to prefer the form that only matters when the table has fifty million rows and writes going to it. This is the shape of most of the flagged files: the agent picks the form that is locally best, and the form that is locally best is the one that blocks production.

The four SET NOT NULL statements are the same story. The agent even did the right thing first, backfilling with an UPDATE before setting the constraint, and then set the constraint the way that scans the whole table under an exclusive lock, because that is the short way to write it. The two foreign keys were added without NOT VALID, so they validate every existing row while holding locks on both tables. The pattern is consistent enough that you could predict which files would be flagged from the request alone.

The two criticals were a TRUNCATE and a VACUUM FULL, both of which were literally what was asked for. That is a fair point in the agent's defence and it is also the point: the request was reckless, the agent did not push back, and the only thing standing between the request and production was whether anyone was checking.

The fixes, side by side

Every flagged file has a one or two line correction. The four that account for twenty of the twenty-seven:

-- BV003: the agent wrote            ->  the fix
CREATE INDEX idx ON orders (customer_id);
CREATE INDEX CONCURRENTLY idx ON orders (customer_id);

-- BV011: backfilled, then
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- fix: prove it under a weak lock first, then the SET NOT NULL is a catalog change (PG12+)
ALTER TABLE users ADD CONSTRAINT users_email_nn CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_nn;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_email_nn;

-- BV004: a type change rewrites the table under ACCESS EXCLUSIVE
ALTER TABLE orders ALTER COLUMN total TYPE bigint;
-- fix: new column, batched copy, swap, drop (expand / migrate / contract)
ALTER TABLE orders ADD COLUMN total_new bigint;
UPDATE orders SET total_new = total WHERE total_new IS NULL AND id BETWEEN 1 AND 50000;  -- repeat
-- ... switch the application, then rename and drop in a later release

-- BV008: validates every row while locking both tables
ALTER TABLE payments ADD CONSTRAINT payments_order_fk FOREIGN KEY (order_id) REFERENCES orders (id);
-- fix: record now, validate under a lock that lets writes through
ALTER TABLE payments ADD CONSTRAINT payments_order_fk FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID;
ALTER TABLE payments VALIDATE CONSTRAINT payments_order_fk;

The honest caveats

One agent, one run, forty prompts i wrote. A different model or a different day would move the numbers. We do not think it would change the shape.
The free rule set is the outage core, eighteen rules. The full corpus would have flagged more, and some warnings here are advisory by design: a rename is only dangerous during a deploy window, and the rule says so.
We asked for first drafts. An agent that is told to review its own migration, or that has a checker as a tool, does better. That is the whole argument for giving it one.

Thirteen of forty clean is not a verdict on the agent. It is a measurment of what "looks right" means when the thing that makes a migration wrong is a table the author will never see. The fix is not a better prompt. It is the same check, run the same way, on every file, before it merges.

The rules behind this post

warning

PostgreSQL

free in the CLI

BV003 Non-concurrent index creation

CREATE INDEX holds a SHARE lock for the entire build, blocking INSERT/UPDATE/DELETE on the table. Build time scales with table size, so a migration that is instant in dev blocks writes for minutes in production.

What is wrong

CREATE INDEX idx_orders_region ON orders (region);

Do this instead

Build the index with CREATE INDEX CONCURRENTLY in its own migration, outside any transaction. It takes only SHARE UPDATE EXCLUSIVE, so writes continue during the build. It can fail and leave an INVALID index — drop it and retry rather than ignoring it.

-- In its own migration file, run outside a transaction:
CREATE INDEX CONCURRENTLY idx_orders_region ON orders (region);

Read the rule

Catch this before it ships: npx bolvrk check migration.sql runs it locally, no account needed. Sign in for the full corpus, pull-request comments and live-schema checks.

warning

PostgreSQL

free in the CLI

BV011 SET NOT NULL scanning the table under ACCESS EXCLUSIVE

SET NOT NULL must verify every existing row, and it does so while holding ACCESS EXCLUSIVE — the table is fully unavailable for the scan. The safe path is a CHECK (col IS NOT NULL) NOT VALID constraint, VALIDATE CONSTRAINT (which does not block), then SET NOT NULL, which sees the validated constraint and skips the scan.

What is wrong

ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

Do this instead

Add CHECK (col IS NOT NULL) NOT VALID (instant), VALIDATE it in a later migration (non-blocking scan), then SET NOT NULL — on Postgres 12+ it sees the validated constraint and skips its own scan, making the ACCESS EXCLUSIVE window a metadata flip. Drop the helper constraint afterwards.

SET lock_timeout = '5s';
ALTER TABLE orders ADD CONSTRAINT orders_region_nn
  CHECK (region IS NOT NULL) NOT VALID;
-- later migrations:
--    ALTER TABLE orders VALIDATE CONSTRAINT orders_region_nn;  -- non-blocking
--    ALTER TABLE orders ALTER COLUMN region SET NOT NULL;      -- scan skipped (PG12+)
--    ALTER TABLE orders DROP CONSTRAINT orders_region_nn;

Read the rule

Catch this before it ships: npx bolvrk check migration.sql runs it locally, no account needed. Sign in for the full corpus, pull-request comments and live-schema checks.

warning

PostgreSQL

free in the CLI

BV004 Column type change forcing a table rewrite

ALTER COLUMN TYPE takes ACCESS EXCLUSIVE and, unless the change is binary-coercible, rewrites every row. The table is completely unavailable — reads included — for the duration.

What is wrong

ALTER TABLE orders ALTER COLUMN id TYPE bigint;

Do this instead

For a widening change on a busy table, expand/contract instead of retyping in place: add a new column, keep it in sync with a trigger, backfill in batches, then swap names and drop the old column across later releases. Only binary-coercible changes (e.g. varchar(n) to text) skip the rewrite and are safe done directly with a lock_timeout guard.

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN id_new bigint;
-- keep id_new in sync with a trigger, backfill in batches from a job,
-- then swap column names and drop the old one in later migrations

Read the rule

Catch this before it ships: npx bolvrk check migration.sql runs it locally, no account needed. Sign in for the full corpus, pull-request comments and live-schema checks.

warning

PostgreSQL

free in the CLI

BV008 Foreign key added without NOT VALID

Adding a validated foreign key scans every existing row while holding a SHARE ROW EXCLUSIVE lock on both tables. With NOT VALID the constraint applies to new writes instantly, and VALIDATE CONSTRAINT can scan later with a much weaker lock.

What is wrong

ALTER TABLE orders ADD CONSTRAINT fk FOREIGN KEY (user_id) REFERENCES users (id);

Do this instead

Add the constraint NOT VALID — it applies to new writes immediately without scanning existing rows — then VALIDATE CONSTRAINT in a later migration; validation takes only SHARE UPDATE EXCLUSIVE on the table and ROW SHARE on the referenced table, so traffic continues. Index the referencing column first (see BV017).

-- migration 1 (alone, outside a transaction): cover the column first
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders (user_id);
-- migration 2: the constraint, instant for existing rows
--    SET lock_timeout = '5s';
--    ALTER TABLE orders ADD CONSTRAINT orders_user_id_fkey
--      FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
-- migration 3, non-blocking scan:
--    ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

Read the rule

Catch this before it ships: npx bolvrk check migration.sql runs it locally, no account needed. Sign in for the full corpus, pull-request comments and live-schema checks.