An AI schema migration is reviewed the way all generated code is reviewed: somebody reads the SQL and decides whether it looks right. For a migration, that review misses the thing that matters. Two ALTER TABLE statements can differ by one word, read as equally routine, and differ by three orders of magnitude in how long they hold a lock that stops every query on the table.
Here is that pair, measured rather than argued. The table is orders: 5,000,000 rows, 394 MB, on PostgreSQL 16.13.
Two statements, one word apart
-- A alter table orders add column status text not null default 'pending'; -- 16.226 ms -- B alter table orders add column ext_ref uuid not null default gen_random_uuid(); -- 14293.018 ms
Statement A took 16 milliseconds. Statement B took 14.3 seconds against the same table, roughly 880 times longer. Both are ADD COLUMN with a NOT NULL DEFAULT. Both take ACCESS EXCLUSIVE, the lock mode that excludes everything, including plain SELECT.
The difference is what the DEFAULT expression is. Since Postgres 11, a constant default is recorded in the catalog and applied on read, so statement A changes metadata and touches no rows. gen_random_uuid() is volatile: every row needs a different value, so Postgres has no choice but to write every row.
You can watch the file change underneath:
select relfilenode from pg_class where relname = 'orders'; -- before A: 16386 -- after A: 16386 (unchanged, metadata only) -- after B: 16397 (new relfilenode, the table was rewritten)
A changed relfilenode is the whole story: the table was copied. For 14.3 seconds, on a table serving production traffic, nothing else could read it.
The same statement shape against three table states. Only the third one is a production incident, and the statement text is what changes least between them.
The development database reports four milliseconds
The obvious defense is to run the migration somewhere safe first. So I did, against a structurally identical but empty clone:
create table orders_dev (like orders including all); alter table orders_dev add column ext_ref2 uuid not null default gen_random_uuid(); -- 4.042 ms
Four milliseconds. The dangerous statement, the one that rewrites five million rows in production, returns in less time than the safe one did and reports no warning of any kind. A test that measures duration against a small database does not under-report this cost slightly. It under-reports it by a factor of about 3,500, and it gets the ranking backwards: on the dev clone the rewriting statement looks like the cheaper of the two.
This is the general failure we have written about in why staging did not catch your slow migration: the test environment is a biased sample, and the bias grows with the size of the table. What is specific to a generated migration is that the agent proposing it usually has no other estimate to fall back on. A human author of a data-heavy migration often knows, roughly, that orders is the big one. That knowledge is not in the schema.
What the model is actually given
Ask what an agent receives when it is asked to write a migration. In most setups it is a schema: a dump, an introspection of information_schema, or a serialized ORM model. Here is what a schema-only dump of this table contains on the subject of size:
pg_dump --schema-only -t orders | grep -iE 'reltuples|rows|size|bytes' SET row_security = off;
One match, and it is a session setting. No row count, no table size, no write rate, no indication that this table is the one with five million rows in it while the one next to it has forty. The planner's own estimate exists in the catalog, pg_class.reltuples reads 5000000 for this table, but it is not part of what a schema hands to a model, and nothing in the DDL points at it.
So the model is asked to make a decision whose entire cost is a function of three facts it was not given: how many rows the table holds, whether the default expression is volatile, and whether anything else is holding a lock right now. It gets the second one from the SQL it is writing. The other two are state, and state is not in the prompt.
The lock is held, and then it is queued behind
There is a second cost that compounds the first, and it does not need a rewrite to hurt. A metadata-only ADD COLUMN doing 16 milliseconds of work still asks for ACCESS EXCLUSIVE. If it cannot get the lock immediately, it waits, and Postgres queues every subsequent reader behind the waiter even when those readers would not have conflicted with anything currently held.
Measured with one ordinary long-running read open on the table:
alter table orders add column note text; -- waited 28146.713 ms select count(*) from orders where id = 42; -- waited 26144.988 ms
That second line is a primary key lookup. It normally returns in about a millisecond. It took 26 seconds because it arrived after a migration that had not yet started doing its 16 milliseconds of work. The mechanism, and the fifteen lines of Postgres source that produce it, are the subject of why ALTER TABLE blocks SELECT in Postgres. The point here is narrower: the statement an agent writes cannot tell you this will happen, because whether it happens depends on who else is connected at the moment it runs.
Bounding what you cannot predict
None of this argues that agents should not write migrations. It argues that the safety has to come from the execution path rather than from the text, because the text is where the information is not.
Three controls, each measured on the same table.
Set lock_timeout, always. Run the same blocked migration with a ceiling on how long it will wait:
set lock_timeout = '2s'; alter table orders add column note2 text; -- ERROR: canceling statement due to lock timeout -- Time: 2000.675 ms
The migration fails. That is the success case. A read arriving immediately afterwards returned in 1.047 milliseconds instead of waiting 26 seconds. The incident becomes a failed job and a retry, which is the trade you want and which lock_timeout gets you and does not get you in more detail.
Split validation off the blocking lock. Adding a CHECK constraint the direct way scans the whole table while holding ACCESS EXCLUSIVE. Adding it NOT VALID and validating separately does not:
alter table orders add constraint chk check (total_cents >= 0); -- 208.819 ms at ACCESS EXCLUSIVE alter table orders add constraint chk check (total_cents >= 0) not valid; -- 0.490 ms at ACCESS EXCLUSIVE alter table orders validate constraint chk; -- 253.651 ms at SHARE UPDATE EXCLUSIVE, blocks neither reads nor writes
Same end state. The blocking lock is held for half a millisecond instead of 208, and the expensive part runs at a lock level that lets traffic through.
Put the row count in front of the reviewer. The approval surface, not the model, is where the missing state belongs. A review that shows the exact statement plus the target table's current row count, size, and whether the change rewrites is a review a person can actually perform. A Slack prompt showing only the SQL reproduces the agent's blind spot in a human. That is the argument of our guide to human-in-the-loop database migrations, and this measurement is why the row count has to be on the card.
Reproducing this
Everything above came from one throwaway cluster. If you want the numbers for your own tables rather than mine:
-- does this statement rewrite? compare before and after select relfilenode, reltuples::bigint, pg_size_pretty(pg_total_relation_size(oid)) from pg_class where relname = 'orders'; -- what is actually blocked, while it is blocked select a.pid, left(a.query, 48) as query, l.mode, l.granted from pg_locks l join pg_stat_activity a using (pid) where l.relation = 'orders'::regclass;
The second query is the one worth keeping. During the blocked run it showed the readers holding AccessShareLock with granted = t, the migration waiting with AccessExclusiveLock and granted = f, and behind it a plain SELECT also sitting at granted = f, blocked by a lock mode it does not conflict with. Version tested: PostgreSQL 16.13.
What this asks of an agent platform
The pattern under all of it is that a migration's risk is a property of the database, not of the diff, and every layer that reviews the diff alone inherits the same blind spot: the model, the pull request, and the approval prompt. Closing it means putting the state that decides the outcome, the row counts, the sizes, the locks currently held, in front of whoever or whatever is about to say yes, and enforcing the ceilings in the path the statement travels rather than in the instructions that produced it.
That is the problem Datapace works on: giving AI systems resolved, trustworthy context about a production database, and governing what they are allowed to execute against it. A model that could read orders is 394 MB and holds five million rows would have written statement A instead of statement B. It wrote B because nobody told it, and default gen_random_uuid() looks, in the SQL, exactly as harmless as default 'pending'.