# What is blocking my migration? How to read pg_locks > **Quick answer:** Your migration is queued behind a conflicting lock, and > later requests for conflicting locks **on that same table** are queued behind > the migration. Confirm that it is a lock wait, find the blocker with > `pg_blocking_pids()` rather than reading `pg_locks` by hand, deal with that > session, and give the next migration a short `lock_timeout` so it fails fast > instead of remaining at the head of the queue. ## What is the problem? `ALTER TABLE` takes an `ACCESS EXCLUSIVE` lock by default, and that mode conflicts with every other mode, including the `ACCESS SHARE` lock that a plain `SELECT` takes. Not every schema change does, though. Assuming otherwise can lead you to avoid the wrong statements. The [PostgreSQL 18 `ALTER TABLE` documentation](https://www.postgresql.org/docs/18/sql-altertable.html) sets the rule: "Note that the lock level required may differ for each subform. An `ACCESS EXCLUSIVE` lock is acquired unless explicitly noted. When multiple subcommands are given, the lock acquired will be the strictest one required by any subcommand." The problem is the **queue** that forms behind the lock. ### The queue, measured A pending conflicting request blocks later conflicting requests on the same object. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock `postgres:18.6` container, 8 vCPU, `shared_buffers` 128MB, with four sessions started in this order: | session | statement | `wait_event` | `pg_blocking_pids()` | |---|---|---|---| | 91, report | `SELECT count(*) FROM orders` in an open transaction | none, holds `AccessShareLock` | `{}` | | 98, migration | `ALTER TABLE orders ADD COLUMN note text` | `Lock` / `relation` | `{91}` | | 111, reader | `SELECT count(*) FROM orders` | `Lock` / `relation` | `{98}` | | unrelated | `SELECT count(*) FROM other` | none, **returned immediately** | `{}` | The reader does not conflict with the report. It conflicts with the migration, which is itself still waiting, and that is enough to make it wait. That is a soft block, and it explains how "one migration took the site down". The fourth row is the relevant qualification. This applies to one lockable object. A query that never touches `orders` is unaffected, and whether the queue becomes an outage depends on your workload, pool size and how many statements touch that table. On a busy table with a small pool it can exhaust the pool in seconds. ## How do I confirm it? Use three steps in order. Skipping the first can lead you to diagnose a lock problem that is not a lock problem. **Step one: is it a lock wait at all?** ```sql SELECT count(*) FILTER (WHERE wait_event_type = 'Lock') AS waiting_on_locks, count(*) AS total_backends, max(now() - xact_start) AS oldest_transaction FROM pg_stat_activity WHERE backend_type = 'client backend'; ``` **Step two: who is blocking whom.** This query includes enough attribution to find the owner of the blocking session rather than only its number: ```sql SELECT blocked.pid AS blocked_pid, blocked.wait_event_type, blocked.wait_event, left(blocked.query, 60) AS blocked_query, blocker.pid AS blocking_pid, blocker.application_name AS blocking_app, blocker.usename AS blocking_user, blocker.client_addr, blocker.state AS blocking_state, now() - blocker.xact_start AS blocker_xact_age, now() - blocker.query_start AS blocker_query_age, left(blocker.query, 60) AS blocking_last_query FROM pg_stat_activity AS blocked CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) JOIN pg_stat_activity AS blocker ON blocker.pid = b.pid WHERE blocked.wait_event_type = 'Lock' ORDER BY blocker_xact_age DESC NULLS LAST; ``` Three caveats about that query are easy to miss. **It shows immediate blockers only.** The [PostgreSQL 18 information functions documentation](https://www.postgresql.org/docs/18/functions-info.html) defines the rule: "One server process blocks another if it either holds a lock that conflicts with the blocked process's lock request (hard block), or is waiting for a lock that would conflict with the blocked process's lock request and is ahead of it in the wait queue (soft block)." In the measurement above the reader's blockers were `{98}`, the migration, and **not** `{91}`, the report that started it all. Walk the chain to the original holder by reading the output as a graph or using a recursive query. **A blocker can have no PID.** The [PostgreSQL 18 information functions documentation](https://www.postgresql.org/docs/18/functions-info.html) says "when a prepared transaction holds a conflicting lock, it will be represented by a zero process ID". Measured on 2026-09-19 on PostgreSQL 18.6 in a stock `postgres:18.6` container started with `-c max_prepared_transactions=10`, with a prepared transaction holding `ACCESS EXCLUSIVE`, the waiting session's `pg_blocking_pids()` returned `{0}`, and the `pg_locks` row for the holder had a null `pid` with only `virtualtransaction` to identify it. The join above finds no row for PID 0, so the blocked session appears to have no blocker. When that happens, look in `pg_prepared_xacts`. **`blocking_last_query` is not necessarily the statement that took the lock.** For a session that is `idle in transaction`, `query` is the last statement it submitted, which may be unrelated to the lock it is still holding. Use it as a lead, not as evidence. **Step three: `pg_locks`, for information the function does not provide.** The mode, target, and wait start time are available there: ```sql SELECT l.pid, l.locktype, l.mode, l.granted, l.waitstart, l.virtualtransaction, c.relname FROM pg_locks AS l LEFT JOIN pg_class AS c ON c.oid = l.relation WHERE NOT l.granted ORDER BY l.waitstart; ``` Read `granted` as the status of **that lock request**, not of the session: false means the request is queued, and `waitstart` says when the wait began, though it can be briefly null in the instant after a wait begins. ### Not every wait is a table lock Filtering `pg_locks` on one relation is right for the motivating case but wrong as a general habit, because several common waits have no relation at all. Both of these were measured on 2026-09-19 on PostgreSQL 18.6 in a stock `postgres:18.6` container with default settings, and in both cases the ungranted rows for the table in question numbered zero: | what is happening | `locktype` of the ungranted row | |---|---| | a second `UPDATE` waiting on the first one's row | `transactionid` | | `CREATE INDEX CONCURRENTLY` waiting for older transactions to end | `virtualxid` | The second case illustrates the distinction. `CREATE INDEX CONCURRENTLY` held a granted `ShareUpdateExclusiveLock` on the table and was waiting on a `virtualxid` lock belonging to a transaction that had never touched that table. A query filtered to the relation would not have explained the stall. Advisory locks, object locks and database locks behave the same way. Start from `wait_event_type = 'Lock'`, find the lock target, and only then filter. ## What to do right now **If a queued DDL statement is the head-of-line blocker, cancelling it is usually the least disruptive first action.** ```sql SELECT pg_cancel_backend(12345); ``` Measured on 2026-09-19 on PostgreSQL 18.6 in a stock `postgres:18.6` container with default settings, that worked as expected: the reader that had been waiting behind the `ALTER TABLE` completed immediately, while the original report still held its `ACCESS SHARE`, because the two readers never conflicted with each other. ### What happens to the locks that session already held The rule most people carry is that PostgreSQL holds locks until the end of the transaction, so a cancelled statement inside a transaction block should leave its earlier locks in place. That is correct for a transaction that is still live. A cancel does not leave one live. Here is the whole test. The victim session must stay connected throughout, because if it exits you are measuring a disconnect and the result looks identical. Run on 2026-09-19 against PostgreSQL 18.6 in a stock `postgres:18.6` container started with `-c max_prepared_transactions=10 -c log_lock_waits=on`, 8 vCPU, `shared_buffers` 128MB, everything else default. The container is called `pg` below. ```bash { cat <<'SQL' BEGIN; LOCK TABLE other IN ACCESS EXCLUSIVE MODE; SELECT pg_backend_pid() AS victim_pid, count(*) AS my_locks FROM pg_locks WHERE pid = pg_backend_pid() AND relation IS NOT NULL; SELECT pg_sleep(60); SQL sleep 20 cat <<'SQL' SELECT 'still in aborted txn?' AS probe; SQL sleep 10 cat <<'SQL' ROLLBACK; SQL sleep 5; } | docker exec -i pg psql -U postgres & sleep 3 PID=$(docker exec pg psql -U postgres -tAc \ "SELECT pid FROM pg_stat_activity WHERE query LIKE '%pg_sleep(60)%' AND pid <> pg_backend_pid()") docker exec pg psql -U postgres -tAc \ "SELECT 'BEFORE: '||count(*) FROM pg_locks WHERE pid=$PID AND relation IS NOT NULL" docker exec pg psql -U postgres -qtAc "SELECT pg_cancel_backend($PID)" sleep 4 docker exec pg psql -U postgres -tAc \ "SELECT 'AFTER: '||count(*) FROM pg_locks WHERE pid=$PID AND relation IS NOT NULL" docker exec pg psql -U postgres -tAc \ "SELECT 'state='||state FROM pg_stat_activity WHERE pid=$PID" ``` The observing session printed: ``` BEFORE: 2 AFTER: 0 state=idle in transaction (aborted) ``` and the victim session printed, in order: ``` BEGIN LOCK TABLE victim_pid | my_locks ------------+---------- 654 | 2 ERROR: canceling statement due to user request ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK ``` That second `ERROR` makes the result meaningful. It proves the session was still inside the transaction block at that moment, and `pg_locks` shows it holding no locks. The cancel raises an error, the error aborts the transaction, and the abort releases the locks. Cleanup of the transaction state is deferred until `ROLLBACK`, which is why the session continues reporting `idle in transaction (aborted)` and refusing statements while holding no locks. `backend_xmin` was null after the cancel for the same reason. The lock graph therefore clears, but the session remains unable to accept statements until it rolls back. Reassess the remaining graph rather than assuming either that the incident is over or that the old locks are still present. **If the holder is an abandoned transaction, ending it is an incident decision, not a reflex.** ```sql SELECT pg_terminate_backend(12345); ``` `idle in transaction` says the client is not currently running a statement. It does not say the client is abandoned, that the work is disposable, or that this session is the one holding the lock you care about. Terminating it rolls back its transaction, and a connection pooler or an application retry loop may turn that into a visible error. Identify the owner first from `application_name`, `usename`, `client_addr` and transaction age, and decide based on the business impact. For the general case use `idle_in_transaction_session_timeout` so the condition does not become an incident. Whether you can see other sessions at all, let alone cancel them, depends on your role and on your provider's policy. On a managed service, check before you need that access. ## How do I stop it coming back? ### Bound the wait, and know what the bound does not cover Give the migration session a short `lock_timeout`. Inside an explicit transaction use `SET LOCAL` so it applies to that transaction and nothing else: ```sql SET LOCAL lock_timeout = '3s'; ``` The [PostgreSQL 18 client configuration documentation](https://www.postgresql.org/docs/18/runtime-config-client.html) is precise about the scope: `lock_timeout` aborts "any statement that waits longer than the specified amount of time while attempting to acquire a lock", and "the time limit applies separately to each lock acquisition attempt". So a statement that takes several locks gets the budget several times. **It does not stop a queue forming.** Measured on 2026-09-19 on PostgreSQL 18.6 in a stock `postgres:18.6` container, 8 vCPU, `shared_buffers` 128MB: a report holding `ACCESS SHARE`, a migration with `lock_timeout = '5s'` behind it, and a reader arriving one second later. The reader waited **4.1 seconds** and then succeeded when the migration timed out. The timeout bounded the damage; it did not prevent it. Choose the value based on the longest stall you can tolerate on that table, not as a magic number. **It does not bound execution.** Measured on 2026-09-19 on PostgreSQL 18.6 in a stock `postgres:18.6` container with default settings, `SET lock_timeout = '1s'` followed by a three-second statement that was not waiting for anything completed normally; the same statement under `SET statement_timeout = '1s'` was aborted. The [PostgreSQL 18 client configuration documentation](https://www.postgresql.org/docs/18/runtime-config-client.html) puts it plainly: "Unlike `statement_timeout`, this timeout can only occur while waiting for locks." Use both when you need both limits. **Set them per session or per migration role.** The [PostgreSQL 18 client configuration documentation](https://www.postgresql.org/docs/18/runtime-config-client.html) says of each one: "Setting `lock_timeout` in `postgresql.conf` is not recommended because it would affect all sessions." **And roll back before retrying.** Measured on 2026-09-19 on PostgreSQL 18.6 in a stock `postgres:18.6` container with default settings, after the timeout fired inside an explicit transaction, the next statement returned `ERROR: current transaction is aborted`. A retry loop that reissues the DDL without ending the transaction retries nothing. ### Know which change you are making Lock level is per operation. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock `postgres:18.6` container with default settings, taking the lock inside a transaction and reading `pg_locks` for that backend: | statement | lock on the table | |---|---| | `ALTER TABLE ... ADD COLUMN` | `AccessExclusiveLock` | | `ALTER TABLE ... ADD FOREIGN KEY` | `ShareRowExclusiveLock` | | `CREATE INDEX` | `ShareLock` | | `CREATE INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | | `ANALYZE` | `ShareUpdateExclusiveLock` | The `ADD FOREIGN KEY` row is documented. The [PostgreSQL 18 `ALTER TABLE` documentation](https://www.postgresql.org/docs/18/sql-altertable.html) says: "Although most forms of `ADD table_constraint` require an `ACCESS EXCLUSIVE` lock, `ADD FOREIGN KEY` requires only a `SHARE ROW EXCLUSIVE` lock." Duration and level are separate. Whether a change rewrites the table determines how long the lock is held, which is what can turn a lock into an outage. Measured on 2026-09-19 against PostgreSQL 18.6 in a stock `postgres:18.6` container with default settings, on Linux 6.17.0 under Docker 29.1.3, by comparing `pg_relation_filenode` before and after each statement: | statement | rewrite? | |---|---| | `ADD COLUMN c text` | no | | `ADD COLUMN c text DEFAULT 'x'` | no | | `ADD COLUMN c uuid DEFAULT gen_random_uuid()` | **yes** | | `ALTER COLUMN s TYPE varchar(20)` from `varchar(10)` | no | | `ALTER COLUMN s TYPE text` from `varchar` | no | | `ALTER COLUMN id TYPE bigint` from `int` | **yes** | The volatile default often surprises people, and the [PostgreSQL 18 `ALTER TABLE` documentation](https://www.postgresql.org/docs/18/sql-altertable.html) names it: "Adding a column with a volatile `DEFAULT` (e.g., `clock_timestamp()`), a stored generated column, an identity column, or a column with a domain data type that has constraints will cause the entire table and its indexes to be rewritten." A type change is not automatically a rewrite either. Test the specific conversion on your version rather than inferring it from the statement's shape. ### Build indexes concurrently, with the caveats ```sql CREATE INDEX CONCURRENTLY idx_orders_region ON orders (region); ``` Measured on 2026-09-19 on PostgreSQL 18.6 in a stock `postgres:18.6` container with default settings, this holds `SHARE UPDATE EXCLUSIVE` on the table, and ordinary reads and `UPDATE`s on that table ran throughout. A plain `CREATE INDEX` takes `SHARE`, which allows reads but blocks writes. The benefit is that writes keep working, not that the lock changes from `ACCESS EXCLUSIVE` to a weaker mode. It still conflicts with anything else wanting `SHARE UPDATE EXCLUSIVE` or stronger, so it serialises against `VACUUM`, `ANALYZE`, another concurrent build and any real DDL on that table. It cannot run inside a transaction block, and that is easy to trip over. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock `postgres:18.6` container with default settings, `psql -c "SET application_name='x'; CREATE INDEX CONCURRENTLY ..."` fails with `ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block`, because multiple statements in one simple-query message are wrapped in one. The same applies to a migration tool that wraps each file in a transaction. If it fails, it leaves an invalid index behind. The planner ignores that index, but PostgreSQL still maintains it on every write, so it continues to consume resources until you deal with it. The usual fix is to drop it and retry, but that is not the only option. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock `postgres:18.6` container with default settings, after cancelling a concurrent build, `pg_index.indisvalid` was false for the leftover index, and `REINDEX INDEX CONCURRENTLY` on it returned it to valid. ### Make the schema change safe, not just the lock A short `lock_timeout` does not make a breaking migration safe. For anything that changes an existing column's shape, use expand and contract: 1. Add the new, backward-compatible structure. 2. Deploy application code that tolerates the old shape and the new one. 3. Backfill in bounded batches, outside a long transaction. 4. Validate constraints and move reads, then writes. 5. Remove the old structure only once no running version needs it. That approach applies to adding `NOT NULL`, changing a type, adding a foreign key, renaming, and dropping anything. Lock waits are the failure you notice in minutes. Incompatibility often surfaces only in production. ### Keep long transactions away from what you migrate Long-lived transactions, reporting queries and backups delay DDL that requests a conflicting lock **on the same object**. A plain `SELECT` holds `ACCESS SHARE`, which conflicts only with `ACCESS EXCLUSIVE`, so it delays an `ADD COLUMN` and not an `ADD FOREIGN KEY`. Know which migrations fall into that class before scheduling around a backup window. ## Why does AI-generated code cause this? Migration files generated from a model tend to contain valid SQL without lock discipline. They may omit `lock_timeout`, combine several `ALTER TABLE` statements into one long transaction that holds the strictest lock any of them needs for the whole run, and suggest a plain `CREATE INDEX` because that is the form that appears in most documentation. The second pattern is the volatile default. `ADD COLUMN id uuid DEFAULT gen_random_uuid()` is a natural line to generate and it rewrites the table under `ACCESS EXCLUSIVE`, where the same column added without the default does not. The third is the transaction wrapper. Generated migrations routinely put `CREATE INDEX CONCURRENTLY` inside a `BEGIN`, where it cannot run at all. These choices may work on an empty development database but cause an outage on a busy one. The SQL in a generated migration can be valid while still lacking testing under concurrency. ## How do I stop it coming back, in five lines **Set `lock_timeout` and `statement_timeout` for the migration session**, not in `postgresql.conf`, and make the retry loop roll back before it retries. **Check the lock level and the rewrite behaviour of each statement** on your version, rather than treating "schema change" as one category. **Start a diagnosis at `wait_event_type = 'Lock'`**, then find the lock target. Filtering `pg_locks` on your table first will miss `transactionid`, `virtualxid` and advisory waits. **Record who owns each session you might cancel**, through `application_name`, so an incident decision is not made on a PID alone. **Design the change with expand and contract**, because the lock is the part that fails loudly and compatibility is the part that does not. ## How DBGorilla helps DBGorilla connects read-only and gives your AI coding agent the blocking episodes it would otherwise have missed: which session was blocked, which sessions were blocking it, the wait event, and the chain between them, recorded over time rather than sampled by hand at the moment someone thought to look. It also keeps topology snapshots with their history, so a schema change and the stall around it can be lined up. That is the schema as it stood at each snapshot, plus your Flyway history or Alembic version table when one exists. [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/)