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

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:

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

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.

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.

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

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:

SET LOCAL lock_timeout = '3s';

The PostgreSQL 18 client configuration documentation 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 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 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 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 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

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 UPDATEs 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 can help

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.

Start free

FAQ

• How do I find what is blocking my migration in PostgreSQL?

  Confirm it is a lock wait first: look for wait_event_type = 'Lock' in pg_stat_activity. Then call pg_blocking_pids() on the waiting backend to get its immediate blockers, and join those back to pg_stat_activity for the application name, user, client address and transaction age. Keep pg_locks for the lock target and mode, which pg_blocking_pids does not give you. One caveat measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container with default settings: pg_blocking_pids returns immediate blockers only, so in a three-session queue the waiting reader's blockers came back as the migration ahead of it, not the session at the head of the queue.

• Why does one blocked ALTER TABLE block reads of the table?

  Because a pending conflicting request blocks later conflicting requests on the same object. An ALTER TABLE waiting for ACCESS EXCLUSIVE on orders makes a later plain SELECT on orders wait too, even though that SELECT would not have conflicted with the original holder. It is scoped to that object: measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container with default settings, a SELECT on a different table returned immediately throughout the same four-session test.

• What is the difference between a granted and an ungranted lock in pg_locks?

  The granted column is the status of that one lock request, not of the session. False means the request is queued, and waitstart says when the wait began. Two gaps measured on 2026-09-19, both on PostgreSQL 18.6 in a stock postgres:18.6 container started with max_prepared_transactions set to 10: a lock row has a null pid when a prepared transaction holds it, so joining pg_locks to pg_stat_activity on pid drops that blocker silently; and many waits are not relation locks, so a second UPDATE queued on a row showed a transactionid wait while the ungranted rows for the table itself numbered zero.

• How do I stop a migration waiting forever on a lock?

  Set a short lock_timeout for the migration session, which bounds each lock acquisition attempt. It does not prevent a queue: measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container on 8 vCPU with shared_buffers at 128MB, a reader arriving one second behind a migration that had lock_timeout set to 5s waited 4.1 seconds before the migration gave up and the queue drained. Pair it with statement_timeout, which lock_timeout does not replace: in the same container a three-second statement that never waited for a lock completed under lock_timeout of 1s and was aborted under statement_timeout of 1s.

• Does CREATE INDEX CONCURRENTLY avoid the problem?

  It avoids blocking reads and writes. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container with default settings, it holds SHARE UPDATE EXCLUSIVE on the table while ordinary reads and updates continue, where a plain CREATE INDEX takes SHARE, which permits reads and blocks writes. It still waits for older transactions to finish, and in the same test that wait appeared as a virtualxid lock held by a transaction that had never touched the table, so it did not show up in a pg_locks query filtered on that table.

Related

• Which ALTER TABLE statements lock a Postgres table?
• How to handle database migrations safely