Troubleshoot PostgreSQL Locks and Blocking Queries

A query that hangs forever is usually waiting on a lock another session never released. This guide shows you how to find that blocking session in PostgreSQL, decide whether to cancel or terminate it, and stop the same lock wait from happening again.

You will use pg_locks, pg_stat_activity and pg_blocking_pids on a Linux VPS. All commands run as the postgres system user or a role with pg_monitor privileges.

Before you start

You need:

• Shell access to the VPS as a sudo-capable user, for example deploy.
• PostgreSQL 13 or newer (Ubuntu 22.04/24.04, Debian 12, AlmaLinux/Rocky 8–9 all ship compatible versions; PGDG packages are newer still).
• A role that can read pg_stat_activity — superuser, or a role granted pg_monitor.
• The name of the database or table where queries hang, if you already know it.

Connect with psql:

sudo -u postgres psql

Find the blocked and blocking sessions

Start with the view that already tells you who is waiting on whom:

SELECT pid,
       usename,
       state,
       wait_event_type,
       wait_event,
       pg_blocking_pids(pid) AS blockers,
       now() - query_start AS runtime,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
ORDER BY runtime DESC;

pg_blocking_pids returns the PIDs that hold a lock your session needs. An empty result means nothing is blocked right now — the hang is elsewhere, such as a long-running statement or a network problem.

Read the state column: active with wait_event_type = 'Lock' is a genuine lock wait. idle in transaction is the classic culprit — a client opened a transaction, changed a row, and then went quiet without committing.

Inspect the locks in detail

When you need to know which relation and which lock mode are involved, query pg_locks directly:

SELECT l.pid,
       l.locktype,
       l.mode,
       l.granted,
       l.relation::regclass AS relation,
       a.state,
       a.usename
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT l.granted
ORDER BY l.pid;

Rows with granted = false are the waiters. Take their PIDs and look up the holders:

SELECT l.pid, l.mode, l.relation::regclass, a.state, a.query_start, left(a.query, 100)
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.granted
  AND l.relation IS NOT NULL
  AND l.relation::regclass::text = 'orders'
ORDER BY l.pid;

Replace orders with your table. The holder's state and query_start tell you whether it is a stuck transaction or merely a slow statement.

Check whether a transaction is idle

Idle transactions are the most common cause of blocking on a busy database. Find them:

SELECT pid, usename, state, now() - state_change AS idle_for, left(query, 100)
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY idle_for DESC;

An idle in transaction session holding locks for many minutes is almost always an application bug: a connection pool that opens a transaction and forgets to commit, or a script that crashed between BEGIN and COMMIT.

You can make those sessions fail fast by setting a timeout at the database level:

ALTER DATABASE appdb SET idle_in_transaction_session_timeout = '5min';

New connections to appdb will then be disconnected after five idle minutes inside a transaction. Existing sessions keep their old setting until they reconnect.

Cancel the blocking query

Cancel is the polite option. It interrupts the statement the session is running, leaves the connection open, and makes the client see an error. The transaction that statement belonged to is aborted, so any changes made in it are rolled back as well:

SELECT pg_cancel_backend(12345);

Replace 12345 with the blocker PID you found above. Expect t on success. Then confirm:

SELECT pid, state, wait_event_type, left(query, 60)
FROM pg_stat_activity
WHERE pid = 12345;

If the session stays active and the waiters remain blocked after a few seconds, cancel did not take effect. A statement in the middle of a long ALTER TABLE or a VACUUM FULL often ignores cancel until it reaches a safe point.

Terminate the session if cancel fails

Terminating closes the connection and rolls back the whole transaction:

SELECT pg_terminate_backend(12345);

Do this only when cancel has failed and the blocked work matters more than the blocker's transaction. Immediately afterwards, check that the waiters moved on:

SELECT count(*) AS still_blocked
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Stop it from recurring

Fix the root cause rather than killing sessions every week:

1. Set a statement_timeout for the application role so runaway queries cannot hold locks indefinitely: ALTER ROLE app_user SET statement_timeout = '30s';
2. Use lock_timeout on maintenance jobs so they fail instead of queueing behind a lock: SET lock_timeout = '5s'; before an ALTER TABLE.
3. Make sure every code path that issues BEGIN also commits or rolls back — connection pools with leaked transactions are the usual source.
4. Run ALTER TABLE and index builds in a maintenance window, and prefer CREATE INDEX CONCURRENTLY where the table is live.
5. Review your autovacuum settings; a backlog of dead tuples slows maintenance and keeps locks around longer. See [tune PostgreSQL autovacuum and VACUUM