# 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.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`.

## 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:

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.

## 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.

```sql
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:

<!-- sql-check: discard -->
<!-- sql-check: expect-error 22001 at 5 -->
```sql
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

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:

```sql
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()`.

| 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

**Is there a `lock_timeout` and a `statement_timeout`?** There almost never is
either. Add both inside the transaction that runs the statement:

```sql
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?**

```sql
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:

```sql
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:

<!-- sql-check: discard -->
```sql
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:

<!-- sql-check: pg 18+ -->
<!-- sql-check: discard -->
```sql
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?

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?

```sql
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:

```sql
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:

```sql
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total_size;
```

## 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?

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 DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent the facts a
migration review needs and a diff cannot supply: table and index sizes, the
schema and how it has changed over time, blocking episodes with the sessions on
both ends, and what is actually running against the database. An agent that can
see how large a table is writes a different migration from one that cannot.
[Get started free →](https://app.dbgorilla.com/signup)

## Related

- [Which ALTER TABLE statements lock a Postgres table?](https://www.dbgorilla.com/learn/postgres/which-alter-table-statements-lock-a-postgres-table/)
- [How to handle database migrations safely](https://www.dbgorilla.com/learn/postgres/how-to-handle-database-migrations-safely/)
- [How to add a NOT NULL column safely in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-add-a-not-null-column-safely-in-postgresql/)