Skip to content

How to preflight an AI-written migration for locks and rewrites

Last updated

View as Markdown

Quick answer: Run the migration against a copy of the schema and read pg_class.relfilenode before and after each statement. A changed number means the table was rewritten. That is a classifier, not a safety verdict: read pg_locks for the mode, watch pg_blocking_pids for the queue, and test the conversion against real data, because a no-data copy accepts values your table will reject. Then add a lock_timeout and a statement_timeout.

Two separate things decide whether a schema change is safe, and generated migrations get the first right while ignoring the second:

  1. Which lock it takes. ALTER TABLE takes ACCESS EXCLUSIVE unless the specific subform documents otherwise, and several do. The mode is a property of the subcommand, not of ALTER TABLE. There is a measured table further down.
  2. 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 COLUMN on 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.

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, relfilenode
FROM pg_class
WHERE 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:

statementrewrite?
ADD COLUMN c textno
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.

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.granted
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE 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().

statementlock, and on what
ADD COLUMNAccessExclusiveLock on the table
ALTER COLUMN ... SET NOT NULLAccessExclusiveLock on the table
ADD CONSTRAINT ... CHECK ... NOT VALIDAccessExclusiveLock on the table
ALTER COLUMN ... SET STATISTICSShareUpdateExclusiveLock on the table
VALIDATE CONSTRAINTShareUpdateExclusiveLock on the table
CREATE INDEXShareLock on the table, AccessExclusiveLock on the new index
CREATE INDEX CONCURRENTLYShareUpdateExclusiveLock on the table
ADD FOREIGN KEYShareRowExclusiveLock on both tables
ADD FOREIGN KEY ... NOT VALIDShareRowExclusiveLock 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.

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 here
COMMIT;

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.relname
FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
WHERE 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:

steptimelock held
ADD CONSTRAINT ... NOT VALID3.4 msACCESS EXCLUSIVE
VALIDATE CONSTRAINT313 msSHARE UPDATE EXCLUSIVE
SET NOT NULL7.4 msACCESS 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 query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 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 query
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid AND l.relation = 'public.orders'::regclass
WHERE 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;

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.

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.