Skip to content

Why is a simple UPDATE waiting for minutes in PostgreSQL?

Last updated

View as Markdown

Quick answer: Minutes usually means the UPDATE is waiting for a lock, not running slowly, but confirm that before terminating anything. Check pg_stat_activity.wait_event_type. If it says Lock, use pg_blocking_pids() to find the holder and check whether it is idle in transaction. If it says anything else, or nothing, the statement is working, so terminating another session will not fix it.

Two situations look the same to an application that sees only a statement that has not returned.

Waiting. Another transaction modified a row your statement needs and has not committed. Your statement is doing nothing until that transaction ends.

Working. Your statement is executing. It may be scanning rows, maintaining indexes, firing triggers, checking foreign keys, writing WAL, or waiting on storage.

The first problem is fixed by dealing with the other transaction. The second is not. A common mistake is to assume the first problem and start terminating sessions. The results below were measured on PostgreSQL 18.6 in container dbg-pglock on 2026-09-20, against a 2,000,000 row table with four indexes.

Here is the second case, which the usual advice skips. A 500,000 row UPDATE on PostgreSQL 18.6 in dbg-pglock on 2026-09-20, with nothing else running:

Update on acct (actual time=12826.581..12826.582 rows=0.00 loops=1)
Buffers: shared hit=7055468 read=17382 dirtied=27083 written=27136
WAL: records=3026049 fpi=4655 bytes=274221418 buffers full=18320
-> Index Scan using acct_pkey on acct (actual time=0.066..335.531 rows=500000.00 loops=1)
Index Cond: (id <= 500000)

That took 12.8 seconds on 18.6 in dbg-pglock on 2026-09-20. The index scan that found the rows took 335 ms, and the rest was the write: 3,026,049 WAL records and 274 MB of WAL for one statement, on the same server and day.

Sampling that backend’s wait state 300 times while it ran, on 18.6 in dbg-pglock on 2026-09-20, found no lock wait:

what the sample showedsamples out of 300
active, no wait event240
active, LWLock / WALWrite36
active, IO (WAL and data file)22
active, LWLock / WALBufMapping2
Lock of any kind0

A point UPDATE by primary key on the same table took 1.8 ms, 2.1 ms and 3.6 ms across three runs, warm and unblocked, on PostgreSQL 18.6 in dbg-pglock on 2026-09-20. That is milliseconds, not microseconds, so do not use a sub-millisecond expectation as evidence of blocking.

One query answers the main question. Start with every session waiting on a lock:

SELECT a.pid,
a.usename,
a.application_name,
a.state,
a.wait_event_type,
a.wait_event,
now() - a.query_start AS query_age,
now() - a.xact_start AS xact_age,
left(a.query, 80) AS query
FROM pg_stat_activity AS a
WHERE a.wait_event_type = 'Lock'
ORDER BY a.query_start;

An empty result means that nothing is waiting on a PostgreSQL lock. The slow statement is working, so look at its plan rather than another session.

If your statement is in that list, find who is responsible:

SELECT blocked.pid AS blocked_pid,
left(blocked.query, 60) AS blocked_query,
blocker.pid AS blocking_pid,
blocker.state AS blocking_state,
blocker.wait_event_type AS blocking_wait_type,
now() - blocker.xact_start AS blocker_xact_age,
left(blocker.query, 60) AS blocking_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 cardinality(pg_blocking_pids(blocked.pid)) > 0;

The documentation recommends this function instead of reading the view by hand:

“It is better to use the pg_blocking_pids() function to identify which process(es) a waiting process is blocked behind.” (PostgreSQL 18, pg_locks, read 2026-09-20)

Then check what is locked. Neither query above provides that information:

SELECT l.pid,
l.locktype,
coalesce(c.relname, '-') AS relation,
l.mode,
l.granted,
l.transactionid::text AS xid,
l.waitstart
FROM pg_locks AS l
LEFT JOIN pg_class AS c ON c.oid = l.relation
WHERE l.locktype <> 'virtualxid'
ORDER BY l.granted, l.pid;

What a blocked row update really looks like

Section titled “What a blocked row update really looks like”

Two sessions, the first left idle in transaction after updating one row, the second updating the same row. PostgreSQL 18.6, dbg-pglock, 2026-09-20:

pid | state | wait_event_type | wait_event | blocked_by
-----+---------------------+-----------------+---------------+------------
990 | idle in transaction | Client | ClientRead | {}
995 | active | Lock | transactionid | {990}

Read the blocker’s row carefully, because it contradicts two common rules of thumb. Its state is idle in transaction, and it has a wait event: Client / ClientRead, which means it is waiting for its client to send something. A rule that says to follow the chain until reaching a session that is not waiting would pass over this session. The criterion is an empty result from pg_blocking_pids(), not the word waiting.

The locks behind that snapshot, same run:

pid | locktype | relation | mode | granted | xid
-----+---------------+--------------+------------------+---------+-----
990 | relation | acct | RowExclusiveLock | t |
990 | transactionid | - | ExclusiveLock | t | 835
995 | tuple | acct | ExclusiveLock | t |
995 | transactionid | - | ShareLock | f | 835

The ungranted lock is a ShareLock on transaction 835, the blocker’s transaction ID, rather than a row lock. PostgreSQL represents the wait this way, which is why wait_event reads transactionid rather than tuple.

“If a process is waiting for a row-level lock, it will usually appear in the view as waiting for the permanent transaction ID of the current holder of that row lock.” (PostgreSQL 18, pg_locks, read 2026-09-20)

What a table-level wait looks like, and what it does not prove

Section titled “What a table-level wait looks like, and what it does not prove”

wait_event = 'relation' means a relation-level lock. The relation can be a table, an index, a materialized view or a sequence. It does not identify a migration. Here is one with no DDL, on 18.6 in dbg-pglock on 2026-09-20: a session that ran LOCK TABLE acct IN ACCESS EXCLUSIVE MODE and then went idle in its transaction.

pid | state | wait_event_type | wait_event | blocked_by | query
------+---------------------+-----------------+------------+------------+---------------------------
1390 | idle in transaction | Client | ClientRead | {} | LOCK TABLE acct IN ACCE...
1403 | active | Lock | relation | {1390} | SELECT count(*) FROM acct;

TRUNCATE, REINDEX, VACUUM FULL, CLUSTER, a non-concurrent CREATE INDEX and a plain LOCK TABLE all produce the same wait event. Join pg_locks to pg_class and read the blocker’s query before naming the cause.

Code opens a transaction, updates a row, then makes an HTTP call, waits for a lock of its own, or throws an exception on a path that never rolls back. The connection goes back to the pool still in a transaction.

The database cannot distinguish that from a transaction that is about to do more work, so it waits. The session shows idle in transaction. For a session in that state, query is the last statement it ran, not something it is doing now. For an active session, the same column is the current statement.

A queue, where the blocker you find is not the cause

Section titled “A queue, where the blocker you find is not the cause”

Once a strong lock request is waiting, later requests line up behind it even if they would not have conflicted with the original holder. Three sessions on 18.6 in dbg-pglock on 2026-09-20, started two seconds apart:

pid | state | wait_event_type | wait_event | blocked_by | query
------+--------+-----------------+------------+------------+----------------------------------------
1217 | active | Timeout | PgSleep | {} | SELECT pg_sleep(25) FROM acct LIMIT 1;
1225 | active | Lock | relation | {1217} | ALTER TABLE acct ADD COLUMN note text;
1232 | active | Lock | relation | {1225} | SELECT count(*) FROM acct;

The SELECT count(*) is blocked by the ALTER TABLE in front of it, which has not acquired anything yet, rather than by the long read it could have run alongside. The same three sessions in pg_locks:

pid | relname | mode | granted | waitstart
------+---------+---------------------+---------+-----------
1217 | acct | AccessShareLock | t |
1225 | acct | AccessExclusiveLock | f | 07:21:54
1232 | acct | AccessShareLock | f | 07:21:56

Clear the head of the queue, which is the granted lock, not the loudest waiter.

SELECT ... FOR UPDATE takes row locks and blocks other writers of those rows just as an UPDATE does. This is easy to miss in a worker that claims jobs. Foreign key checks, INSERT ... ON CONFLICT, advisory locks and prepared transactions are also possible lock holders; no migration is required.

Cancel first because it is the gentler option:

SELECT pg_cancel_backend(12345);

“Cancels the current query of the session whose backend process has the specified process ID.” (PostgreSQL 18, System Administration Functions, read 2026-09-20)

An idle in transaction session has no current query, so there is nothing to cancel. Measured on 18.6 in dbg-pglock on 2026-09-20, against the blocked pair above: pg_cancel_backend(990) returned t, and two seconds later 990 was still idle in transaction and 995 was still waiting on it. The t means that the signal was sent, not that anything happened.

SELECT pg_terminate_backend(12345);

On the same pair, pg_terminate_backend(990) returned t, 990 disappeared, and 995’s UPDATE reported UPDATE 1. Termination released the locks because it ended the session rather than just the statement.

Termination rolls the open transaction back, and that rollback undoes database work but not external actions. An email the transaction sent, an HTTP call it made or a payment it took are already gone. Read blocking_query, application_name and client_addr before deciding.

A lock wait can also end in an error. These are six ordinary ways, each row produced on PostgreSQL 18.6 in dbg-pglock on 2026-09-20:

how it endsSQLSTATEwhat produced it here
lock wait aborted55P03SET lock_timeout = '2s' then an UPDATE on a row held by another transaction
row lock refused outright55P03SELECT ... FOR UPDATE NOWAIT, which failed in 1.4 ms
deadlock40P01two sessions updating the same two rows in opposite order
serialization failure40001a Repeatable Read transaction updating a row another session had changed since its snapshot
session ended by the server25P03idle_in_transaction_session_timeout expiring
session ended by the server25P04transaction_timeout expiring, PostgreSQL 17 and later

The deadlock message names both sides, which helps find the ordering bug:

ERROR: 40P01: deadlock detected
DETAIL: Process 1086 waits for ShareLock on transaction 839; blocked by process 1081.
Process 1081 waits for ShareLock on transaction 840; blocked by process 1086.
CONTEXT: while updating tuple (16668,118) in relation "acct"

Two of these conditions call for retries rather than prevention.

“As previously stated, applications using these levels must be prepared to retry transactions that fail due to serialization errors.” (PostgreSQL 18, Serialization Failure Handling, read 2026-09-20)

Retry with a bound and jitter, and only on the codes above. A retry loop around a statement with no timeout adds waiters rather than progress. A retry loop around a statement that failed in two seconds with 55P03 is the correct design.

For a worker claiming rows, SKIP LOCKED avoids the queue. Measured on 18.6 in dbg-pglock on 2026-09-20, with two of three rows locked by another transaction, SELECT ... FOR UPDATE SKIP LOCKED returned the one free row in 5.8 ms instead of waiting. It trades completeness for concurrency, so it suits a job queue but not a balance transfer.

The dangerous pattern is a transaction held open across an external call. An agent asked to “update the record and notify the service” may put the update and HTTP request inside the same transaction block because that is the order in the sentence. The transaction then lasts as long as the network call, and every row it touched stays locked for that time.

The second pattern is a retry loop with no timeout and no error classification. It retries a deadlock, a serialization failure and a genuine constraint violation in the same way.

Put a timeout on the application’s login role so the database cleans up after an application failure:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

“Terminate any session that has been idle (that is, waiting for a client query) within an open transaction for longer than the specified amount of time.” (PostgreSQL 18, Client Connection Defaults, read 2026-09-20)

Be precise about which sessions it reaches. The claim that “it leaves your own psql sessions alone” is true only if you do not log in as that role. Measured on 18.6 in dbg-pglock on 2026-09-20, with the setting at 5s on the role:

how the session connectedSHOW idle_in_transaction_session_timeout
psql -U app_user5s
psql -U postgres0
psql -U postgres then SET ROLE app_user0

A login as app_user gets the setting, including your session and your migration tool’s. SET ROLE does not, because the setting attaches to the role that authenticated. On the same server it worked as documented: a session left idle in a transaction was ended with FATAL: 25P03: terminating connection due to idle-in-transaction timeout.

Use it on application roles rather than everywhere. It does nothing about a transaction that is busy rather than idle, and ETL, migrations and interactive administration are cases where a long transaction is the job.

transaction_timeout bounds the whole transaction, whether idle or busy, which idle_in_transaction_session_timeout does not. It arrived in PostgreSQL 17, not 18, and it is documented in PostgreSQL 17, Client Connection Defaults, read 2026-09-20.

“Terminate any session that spans longer than the specified amount of time in a transaction.” (PostgreSQL 17, Client Connection Defaults, read 2026-09-20)

Measured on PostgreSQL 18.6 in container dbg-pglock on 2026-09-20, SET transaction_timeout = '3s' ended a transaction that had run one trivial statement and then sat there, with FATAL: 25P04. The same setting on PostgreSQL 17.11 in container dbg-pgskip17 on 2026-09-20 ended a transaction that was busy rather than idle, inside a pg_sleep, with the same FATAL: 25P04. Both settings default to zero, which disables them.

Set lock_timeout in migrations, inside the transaction, so a blocked migration fails fast instead of adding to a queue:

BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '15min';
-- the ALTER goes here
COMMIT;

“Abort any statement that waits longer than the specified amount of time while attempting to acquire a lock on a table, index, row, or other database object.” (PostgreSQL 18, Client Connection Defaults, read 2026-09-20)

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. Note that CREATE INDEX CONCURRENTLY cannot run inside a transaction block, so it needs its own step and scoping.

Do not assume every migration takes the same lock. Most do, but one common operation does not.

“An ACCESS EXCLUSIVE lock is acquired unless explicitly noted.” (PostgreSQL 18, ALTER TABLE, read 2026-09-20)

“Although most forms of ADD table_constraint require an ACCESS EXCLUSIVE lock, ADD FOREIGN KEY requires only a SHARE ROW EXCLUSIVE lock.” (PostgreSQL 18, ALTER TABLE, read 2026-09-20)

Measured on 18.6 in dbg-pglock on 2026-09-20, ADD FOREIGN KEY ... NOT VALID held ShareRowExclusiveLock on both the referencing and the referenced table, and nothing stronger. That leaves readers alone and blocks writers on both tables. A foreign key on a small child table can therefore stop writes to the large parent.

Give every connection an application_name, so the on-call engineer reading the blocking query can distinguish a migration runner from a report or worker.

Keep transactions short and free of network calls. Open late, commit early, and make the external call before the transaction or after it commits.

  • Why is my UPDATE hanging in PostgreSQL?

    Usually because it is waiting for a lock rather than doing work, but check before you act on that. If pg_stat_activity shows wait_event_type = 'Lock' it is a lock wait and pg_blocking_pids tells you who holds it. If it shows no wait event at all, the statement is running, and no amount of terminating other sessions will help. Measured on PostgreSQL 18.6 in container dbg-pglock on 2026-09-20, a 500,000 row UPDATE took 12.8 seconds and 300 samples of its wait state found no Lock wait at any point.

  • How do I find what is blocking my UPDATE?

    Query pg_stat_activity for sessions where wait_event_type = 'Lock' and call pg_blocking_pids() on them, which returns the process IDs holding the locks you are waiting for. Then join pg_locks to pg_class to see what object is locked, in what mode, and whether the request was granted. The blocker's state matters: idle in transaction means it is holding locks and doing nothing.

  • What does idle in transaction mean and why does it block me?

    It means a client opened a transaction, ran at least one statement, and then stopped sending anything without committing. The transaction is still open, so every lock it took is still held. Measured on PostgreSQL 18.6 in container dbg-pglock on 2026-09-20, such a session reports state idle in transaction with wait_event_type Client and wait_event ClientRead: it is waiting on the client, not on the database.

  • Should I kill the blocking query?

    It depends what the blocker is doing. pg_cancel_backend cancels the current query, and an idle-in-transaction session has none: measured on PostgreSQL 18.6 in container dbg-pglock on 2026-09-20, it returned true and released nothing, and the waiter was still blocked afterwards. pg_terminate_backend ended the session and the waiting UPDATE completed. Terminating rolls back uncommitted database work but cannot undo an email or a payment the transaction already sent.

  • How do I stop an UPDATE waiting forever?

    Set lock_timeout before the statement so it fails fast instead of queueing behind an unknown blocker. Measured on PostgreSQL 18.6 in container dbg-pglock on 2026-09-20, lock_timeout = '2s' aborted a blocked UPDATE with SQLSTATE 55P03 and named the tuple it was waiting on. Use SET LOCAL inside the migration transaction so the value cannot leak into a pooled connection.