How to preflight an AI-written migration for locks and rewrites
Quick answer: Run the migration against a copy of the schema and read
pg_class.relfilenodebefore and after each statement. A changed number means the table was rewritten. That is a classifier, not a safety verdict: readpg_locksfor the mode, watchpg_blocking_pidsfor the queue, and test the conversion against real data, because a no-data copy accepts values your table will reject. Then add alock_timeoutand astatement_timeout.
What is the problem?
Section titled “What is the problem?”Two separate things decide whether a schema change is safe, and generated migrations get the first right while ignoring the second:
- Which lock it takes.
ALTER TABLEtakesACCESS EXCLUSIVEunless the specific subform documents otherwise, and several do. The mode is a property of the subcommand, not ofALTER TABLE. There is a measured table further down. - How long it holds it. A catalog-only change is fast once it has the lock:
on PostgreSQL 17.11, container
dbg-audit, 2026-09-20,ADD COLUMNon a 5,000,000 row table measured 2.7 ms, and the same statement with a constant default measured 3.1 ms. Both are catalog-only, and the difference between them is noise. A change that rewrites the table holds the lock for the length of the rewrite.
For the common forms, the lock level is the same in both cases. The duration is the difference between a deploy nobody notices and an outage.
A third effect can turn a millisecond-scale statement into an incident: while an
ACCESS EXCLUSIVE request is waiting, later requests that conflict with it
queue behind it. ACCESS EXCLUSIVE conflicts with every table lock mode,
including the ACCESS SHARE that a plain SELECT takes. A fast migration
behind one slow report can therefore stall the table.
Reproduced on PostgreSQL 17.11, container dbg-audit, 2026-09-20. Three
sessions, started two seconds apart: a 20 second read, then the ADD COLUMN,
then a plain SELECT count(*). The query is at the end of this page. One
snapshot while all three were live:
pid | wait_event_type | mode | granted | blocked_by | query------+-----------------+---------------------+---------+------------+------------------------------------------ 1361 | Timeout | AccessShareLock | t | {} | SELECT pg_sleep(20) FROM orders LIMIT 1; 1368 | Lock | AccessExclusiveLock | f | {1361} | ALTER TABLE orders ADD COLUMN note text; 1375 | Lock | AccessShareLock | f | {1368} | SELECT count(*) FROM orders;In the third row, a SELECT count(*) is blocked by the ADD COLUMN in front of
it, not by the long read it could have coexisted with.
The preflight: watch relfilenode
Section titled “The preflight: watch relfilenode”A rewrite gives the table a new file on disk, and pg_class.relfilenode is that
file’s identity. Read it, run the statement, and read it again.
SELECT relname, relfilenodeFROM pg_classWHERE relnamespace = 'public'::regnamespace AND relname IN ('orders', 'orders_pkey');Take the indexes as well as the heap. A type change rewrites both: on PostgreSQL
17.11, container dbg-audit, 2026-09-20, text to varchar(100) moved the
table from relfilenode 16562 to 16569 and its primary key index from 16567 to
16572 in the same statement. A heap number that did not move is not proof that
nothing was rebuilt.
Do this on a copy: a restored dump, a branch database, or a container with the production schema and no data. A no-data copy answers one question: whether the statement rewrites. It cannot tell you how long the rewrite takes, how much transient disk it needs, how much WAL it generates, how far a replica falls behind, or whether anything is waiting on the lock, because those depend on data you did not copy.
It also cannot tell you whether your data survives the conversion. On
PostgreSQL 17.11, container dbg-audit, 2026-09-20, the same statement on two
tables:
CREATE TABLE dd_empty (s text);CREATE TABLE dd_full (s text);INSERT INTO dd_full VALUES (repeat('a', 80));
ALTER TABLE dd_empty ALTER COLUMN s TYPE varchar(50);ALTER TABLE dd_full ALTER COLUMN s TYPE varchar(50);The first succeeded. The second returned ERROR: value too long for type character varying(50). A preflight on an empty schema checked no
rows. Run the conversion, the NOT NULL check, and any new
unique index against a representative copy separately. Treat that as
a different test with a different purpose.
Here is what the rewrite measurement gives, with every row run against
PostgreSQL 17.11 in container dbg-audit on 2026-09-20:
| statement | rewrite? |
|---|---|
ADD COLUMN c text | no |
ADD COLUMN c text DEFAULT 'hello' | no |
ADD COLUMN c timestamptz DEFAULT clock_timestamp() | yes |
ALTER COLUMN s TYPE varchar (from text) | no |
ALTER COLUMN s TYPE varchar(100) (from text) | yes |
ALTER COLUMN s TYPE varchar(200) (from varchar(50)) | no |
ALTER COLUMN s TYPE varchar(50) (from varchar(200)) | yes |
ALTER COLUMN s TYPE text (from varchar(50)) | no |
ALTER COLUMN id TYPE int (from bigint) | yes |
ALTER COLUMN id TYPE numeric (from bigint) | yes |
The first row never rewrote. The second is the PostgreSQL 11 change that made adding a column with a default cheap. The third is easy to miss: a volatile default has to be evaluated per row, so the table is rewritten.
The four varchar rows are worth reading together because the type name is not
what decides it. text and varchar use the same storage. Adding or tightening
a length limit causes a rewrite because PostgreSQL has to check every value
against it. Removing the limit or raising it is free. text to varchar with
no length is free; text to varchar(100) is not; varchar(200) to
varchar(50) is not.
That is a rule of thumb. Run your exact source type, target type, and USING
clause on your exact major version.
Measuring the lock, not guessing it
Section titled “Measuring the lock, not guessing it”Take the lock in a transaction and read it before committing. Schema-qualify the
relation, because 'orders'::regclass resolves against the search path and will
happily answer about a different table:
SELECT c.relname, l.mode, l.grantedFROM pg_locks lJOIN pg_class c ON c.oid = l.relationWHERE l.pid = pg_backend_pid() AND l.locktype = 'relation' AND c.relname IN ('orders', 'orders_pkey')ORDER BY 1, 2;Measured on PostgreSQL 17.11, container dbg-audit, 2026-09-20, one statement
per transaction, reading the locks before rolling back. One row is the
exception. CREATE INDEX CONCURRENTLY cannot run inside a transaction block, so
the query above cannot produce its row. That row was measured on PostgreSQL
17.11 in container dbg-audit on 2026-09-20 from a second session polling
pg_locks while the build ran, with l.pid set to the building backend rather
than pg_backend_pid().
| statement | lock, and on what |
|---|---|
ADD COLUMN | AccessExclusiveLock on the table |
ALTER COLUMN ... SET NOT NULL | AccessExclusiveLock on the table |
ADD CONSTRAINT ... CHECK ... NOT VALID | AccessExclusiveLock on the table |
ALTER COLUMN ... SET STATISTICS | ShareUpdateExclusiveLock on the table |
VALIDATE CONSTRAINT | ShareUpdateExclusiveLock on the table |
CREATE INDEX | ShareLock on the table, AccessExclusiveLock on the new index |
CREATE INDEX CONCURRENTLY | ShareUpdateExclusiveLock on the table |
ADD FOREIGN KEY | ShareRowExclusiveLock on both tables |
ADD FOREIGN KEY ... NOT VALID | ShareRowExclusiveLock on both tables |
Three things in that table are easy to miss. SET STATISTICS is not
ACCESS EXCLUSIVE, so “every ALTER TABLE blocks reads” is wrong. ADD FOREIGN KEY is not either, but SHARE ROW EXCLUSIVE conflicts with the ROW EXCLUSIVE
that ordinary writes take. It blocks INSERT, UPDATE, and DELETE on the
referenced table as well as the referencing one, while leaving reads alone.
VALIDATE CONSTRAINT uses SHARE UPDATE EXCLUSIVE, which is why the
NOT VALID two-step is useful.
This is one backend’s held locks. It says nothing about what the statement waited for, which is the next section.
The checklist for a generated migration
Section titled “The checklist for a generated migration”Is there a lock_timeout and a statement_timeout? There almost never is
either. Add both inside the transaction that runs the statement:
BEGIN;SET LOCAL lock_timeout = '3s';SET LOCAL statement_timeout = '15min';-- the ALTER goes hereCOMMIT;They cap different things, and you need both. lock_timeout bounds the wait to
acquire the lock and does nothing once it is granted. statement_timeout
bounds the whole statement, including the wait, which caps a rewrite or a
validation scan. Without a lock_timeout, the request waits until the holder
releases the lock or statement_timeout fires. While it waits, it builds the
queue shown above.
Use SET LOCAL rather than SET, because a plain SET lasts for the session
and a pooled connection hands that session to the next caller. A 3 second
lock_timeout leaking into application traffic is its own incident.
This is the cheapest line in the file, not a substitute for the rest of it. A
lock_timeout converts an outage into a failed deploy, which only helps if the
release process can tell a retryable lock timeout apart from a migration that is
wrong, and does not blindly retry a statement that left an invalid index behind.
Does any statement rewrite? Use the table above, then confirm with
relfilenode rather than trusting it.
Is CREATE INDEX concurrent?
CREATE INDEX CONCURRENTLY idx_events_kind ON events (kind);It cannot run inside a transaction block, so it has to be outside the transaction that wraps the rest of the migration. Whether that means its own file depends on the tool; some support a non-transactional step in place.
Concurrent has costs of its own. It scans the table twice and waits for
transactions that could be using the index, so it takes longer and holds
ShareUpdateExclusiveLock throughout the build. That blocks VACUUM and other
DDL on the table. If it fails, it leaves an invalid index behind that still costs
you on every write and is ignored for planning. Someone has to notice and drop
it:
SELECT c.relnameFROM pg_class cJOIN pg_index i ON i.indexrelid = c.oidWHERE NOT i.indisvalid;Is a NOT NULL being added in one step? That scans the whole table under
ACCESS EXCLUSIVE, which blocks reads for the length of the scan. Measured on
17.11, container dbg-audit, 2026-09-20, 5,000,000 rows: 259 ms with the table
unavailable throughout.
The safe form moves the scan out from under that lock. Add a NOT VALID check
constraint, validate it, then set NOT NULL, which PostgreSQL can now prove
without scanning again:
ALTER TABLE events ADD CONSTRAINT events_user_id_nn CHECK (user_id IS NOT NULL) NOT VALID;ALTER TABLE events VALIDATE CONSTRAINT events_user_id_nn;ALTER TABLE events ALTER COLUMN user_id SET NOT NULL;Each step, on PostgreSQL 17.11 in container dbg-audit on 2026-09-20, against
a 5,000,000 row table of the same shape:
| step | time | lock held |
|---|---|---|
ADD CONSTRAINT ... NOT VALID | 3.4 ms | ACCESS EXCLUSIVE |
VALIDATE CONSTRAINT | 313 ms | SHARE UPDATE EXCLUSIVE |
SET NOT NULL | 7.4 ms | ACCESS EXCLUSIVE |
The total is slightly longer than the one-step form. The scan now runs under a lock that lets reads and writes through, and the table is unavailable for the two short steps on either side of it rather than throughout.
On PostgreSQL 18 the same thing is one constraint instead of two:
ALTER TABLE events ADD CONSTRAINT events_user_id_nn NOT NULL user_id NOT VALID;ALTER TABLE events VALIDATE CONSTRAINT events_user_id_nn;Measured on PostgreSQL 18.6, container dbg-pglock, 2026-09-20, against
5,000,000 rows: 2.5 ms under ACCESS EXCLUSIVE, then 233 ms under SHARE UPDATE EXCLUSIVE. The column reads as attnotnull immediately, and the constraint
carries convalidated = false until the second step. On 17 and
earlier that syntax does not exist; use the check-constraint form above.
Are several ALTER TABLE statements in one transaction? Locks are held to
the end of the transaction, so the exclusive lock lasts for all of them together
rather than for each in turn. That cuts both ways. Several subcommands on the
same table in one ALTER TABLE are better combined, because PostgreSQL makes
one pass over the table instead of one per change. Avoid holding a strong lock
across unrelated slow work, such as a rewrite, a backfill, and a report in one
transaction.
Does anything add a foreign key without NOT VALID? Adding one validates
every existing row while holding SHARE ROW EXCLUSIVE on both tables,
measured on 17.11 in container dbg-audit on 2026-09-20. That leaves reads
alone and blocks INSERT, UPDATE, and DELETE on the referenced table too:
adding a foreign key to a small child table stops writes to the large parent.
NOT VALID skips the initial scan and still enforces the key for new and
changed rows. The later VALIDATE CONSTRAINT does the scan under
SHARE UPDATE EXCLUSIVE.
How do I confirm it against the real thing?
Section titled “How do I confirm it against the real thing?”The preflight above tells you what the statement does. Two more checks tell you whether now is a safe moment to do it.
Is anything long-running that would make the migration queue?
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60) AS queryFROM pg_stat_activityWHERE xact_start IS NOT NULLORDER BY xact_startLIMIT 5;While the migration is running, watch the queue rather than the lock. This is the query that produced the three-row snapshot at the top of the page:
SELECT a.pid, a.wait_event_type, l.mode, l.granted, pg_blocking_pids(a.pid) AS blocked_by, left(a.query, 40) AS queryFROM pg_stat_activity aJOIN pg_locks l ON l.pid = a.pid AND l.relation = 'public.orders'::regclassWHERE a.pid <> pg_backend_pid()ORDER BY a.query_start;pg_blocking_pids is the column that matters. granted = false tells you
something is waiting; blocked_by tells you what it is waiting for, and a chain
of pids means a convoy is forming. Cancel the migration rather than the
application.
How big is the table you are about to rewrite? The rewrite duration scales with its size:
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total_size;Why does AI-generated code cause this?
Section titled “Why does AI-generated code cause this?”A generated migration is usually correct SQL that has never met a concurrent
workload. It will not include lock_timeout because nothing in the request
mentioned locks. It tends to write CREATE INDEX rather than CREATE INDEX CONCURRENTLY, because that is the form in the documentation and the concurrent
form has awkward constraints.
Transaction boundaries are more the tool’s doing than the model’s: many migration frameworks wrap each file in a transaction by default, and what arrives is whatever that framework produces. Either way, the boundary is a decision somebody has to make on purpose, and usually nobody did.
It will also cheerfully change a column type, which reads like a small edit and rewrites the table.
None of this is the model being careless. The information that decides safety, how big the table is and what else is running, is not in the prompt.
How do I stop it coming back?
Section titled “How do I stop it coming back?”Put the rewrite classifier in CI. Restore the production schema, run the
migration, and diff relfilenode for every table and index. It is fast because
there is no data, and it fails on the change rather than on the deploy. Keep its
scope honest: it answers “does this rewrite”, while duration, disk, replica lag,
and whether the data converts require a representative copy.
Make lock_timeout a default in the migration tool, not something each author
has to remember.
Keep a written list of the cheap and expensive forms for the version you run,
measured on that version rather than copied from a blog post. Every number on
this page came from PostgreSQL 17.11 in container dbg-audit on 2026-09-20,
except the NOT NULL NOT VALID one, which is 18.6 in container dbg-pglock on
the same day and does not exist before 18. Managed services, logical replication,
and read replicas add constraints of their own on top of the same SQL.
Do not migrate while a long report is running. Check first; it is the difference between a lock and a queue.
How do I tell whether an ALTER TABLE rewrites the table?
Read pg_class.relfilenode before and after the statement on a copy of the schema. A rewrite gives the table a new file, so the number changes, and the indexes get new files too. That is a rewrite detector, not a lock measurement: read pg_locks separately for the mode and pg_blocking_pids for the queue. A no-data copy answers which statements rewrite and cannot answer how long, how much disk, or whether your data survives the conversion.
Which ALTER TABLE forms rewrite in PostgreSQL?
Measured on PostgreSQL 17.11, container dbg-audit, 2026-09-20: ADD COLUMN nullable and ADD COLUMN with a constant default do not rewrite; ADD COLUMN with a volatile default such as clock_timestamp() does. text to varchar(100) rewrites, and so does varchar(200) to varchar(50), bigint to int and bigint to numeric. text to varchar with no length, varchar(50) to varchar(200), and varchar to text do not. The rule is the length limit, not the type name: adding or tightening one rewrites, removing or widening one does not.
Does a short lock matter if the statement is fast?
It matters if the lock cannot be taken immediately. An ACCESS EXCLUSIVE request that waits sits ahead of later requests that conflict with it, and ACCESS EXCLUSIVE conflicts with every table lock mode including the ACCESS SHARE a plain SELECT needs. Reproduced on PostgreSQL 17.11, container dbg-audit, 2026-09-20: a 20 second read held the table, an ADD COLUMN queued behind it, and a SELECT count(*) issued after that queued behind the ADD COLUMN, blocked by a statement that would have taken 2.7 ms.
What lock does a plain CREATE INDEX take?
SHARE, measured on PostgreSQL 17.11, container dbg-audit, 2026-09-20, which blocks writes but not reads. CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE instead, measured on the same server by polling pg_locks while a build ran, so writes continue too, at the cost of a longer build, two table passes, and an invalid index left behind if it fails.
What should every generated migration have that it usually does not?
A lock_timeout, and a statement_timeout beside it. lock_timeout caps the wait to acquire the lock; it does nothing once the lock is granted, so a rewrite or a validation scan needs statement_timeout too. Generated migrations almost never include either, because on an empty development database there is nothing to wait for and nothing to scan.