# Why is a simple UPDATE waiting for minutes in PostgreSQL?

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

## What is the problem?

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 showed | samples out of 300 |
|---|---|
| `active`, no wait event | 240 |
| `active`, `LWLock` / `WALWrite` | 36 |
| `active`, `IO` (WAL and data file) | 22 |
| `active`, `LWLock` / `WALBufMapping` | 2 |
| `Lock` of any kind | 0 |

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.

## How do I confirm it?

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

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

```sql
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`](https://www.postgresql.org/docs/18/view-pg-locks.html),
> read 2026-09-20)

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

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

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`](https://www.postgresql.org/docs/18/view-pg-locks.html),
> read 2026-09-20)

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

## What you will usually find

### An application that forgot to commit

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

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.

### A lock you asked for on purpose

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

## What to do right now

Cancel first because it is the gentler option:

```sql
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](https://www.postgresql.org/docs/18/functions-admin.html),
> 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.

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

## The errors your application has to handle

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 ends | SQLSTATE | what produced it here |
|---|---|---|
| lock wait aborted | `55P03` | `SET lock_timeout = '2s'` then an `UPDATE` on a row held by another transaction |
| row lock refused outright | `55P03` | `SELECT ... FOR UPDATE NOWAIT`, which failed in 1.4 ms |
| deadlock | `40P01` | two sessions updating the same two rows in opposite order |
| serialization failure | `40001` | a Repeatable Read transaction updating a row another session had changed since its snapshot |
| session ended by the server | `25P03` | `idle_in_transaction_session_timeout` expiring |
| session ended by the server | `25P04` | `transaction_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](https://www.postgresql.org/docs/18/mvcc-serialization-failure-handling.html),
> 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.

## Why does AI-generated code cause this?

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.

## How do I stop it coming back?

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

<!-- sql-check: skip creates a role, which the shared check database does not allow -->
```sql
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](https://www.postgresql.org/docs/18/runtime-config-client.html),
> 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 connected | `SHOW idle_in_transaction_session_timeout` |
|---|---|
| `psql -U app_user` | `5s` |
| `psql -U postgres` | `0` |
| `psql -U postgres` then `SET ROLE app_user` | `0` |

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](https://www.postgresql.org/docs/17/runtime-config-client.html),
read 2026-09-20.

> "Terminate any session that spans longer than the specified amount of time in a
> transaction."
> ([PostgreSQL 17, Client Connection Defaults](https://www.postgresql.org/docs/17/runtime-config-client.html),
> 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:

<!-- sql-check: discard -->
```sql
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](https://www.postgresql.org/docs/18/runtime-config-client.html),
> 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`](https://www.postgresql.org/docs/18/sql-altertable.html),
> 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`](https://www.postgresql.org/docs/18/sql-altertable.html),
> 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.

## How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent the blocking history
rather than only the current instant: which sessions were blocked, which
sessions blocked them, the wait event type, and when it happened, plus the
count of backends sitting idle in transaction and, on CloudNativePG, the
maximum transaction duration. That matters because `pg_stat_activity` shows only this moment, and
the blocker is usually gone by the time anyone looks.
[Get started free →](https://app.dbgorilla.com/signup)

## Related

- [Understanding lock contention in PostgreSQL](https://www.dbgorilla.com/learn/postgres/understanding-lock-contention-in-postgresql/)
- [What is blocking my migration? How to read pg_locks](https://www.dbgorilla.com/learn/postgres/what-is-blocking-my-migration-how-to-read-pg-locks/)
- [How to fix idle in transaction connections](https://www.dbgorilla.com/learn/postgres/how-to-fix-idle-in-transaction-connections/)