PostgreSQL queries stuck waiting on locks: find the blocker and stop the pile-up

#The symptom

The app times out, connection counts climb, and CPU on the database is low. In pg_stat_activity many sessions show wait_event_type = 'Lock'. Nothing is broken; sessions are queued behind one transaction that is holding a lock.

Common shapes:

#1. Confirm it is lock waiting

SELECT wait_event_type, wait_event, state, COUNT(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

wait_event_type = 'Lock' is a heavyweight lock wait, which this post covers. LWLock waits are internal contention (buffer mapping, WAL insertion) and have different causes.

#2. Find who blocks whom

pg_blocking_pids() returns the PIDs blocking a given session:

SELECT a.pid,
       pg_blocking_pids(a.pid)            AS blocked_by,
       a.usename, a.application_name, a.client_addr,
       a.state, a.wait_event,
       now() - a.xact_start               AS xact_age,
       now() - a.query_start              AS query_age,
       left(a.query, 100)                 AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY a.query_start;

#3. Find the head of the queue

Blockers can themselves be blocked. The one that matters is the session that blocks others but waits on nobody:

WITH blocked AS (
  SELECT pid, pg_blocking_pids(pid) AS blockers
  FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0
),
heads AS (
  SELECT DISTINCT unnest(blockers) AS pid FROM blocked
)
SELECT a.pid, a.usename, a.application_name, a.client_addr, a.state,
       now() - a.xact_start AS xact_age,
       (SELECT COUNT(*) FROM blocked b WHERE a.pid = ANY (b.blockers)) AS directly_blocking,
       left(a.query, 200) AS last_query
FROM pg_stat_activity a
JOIN heads h ON h.pid = a.pid
WHERE cardinality(pg_blocking_pids(a.pid)) = 0
ORDER BY xact_age DESC;

Tip

For a session that is idle in transaction, query shows the last statement it ran, not what it is holding. The lock may come from an earlier statement in the same transaction.

#4. See which locks are involved

SELECT l.pid, l.locktype, l.mode, l.granted,
       l.relation::regclass AS relation,
       l.transactionid
FROM pg_locks l
WHERE l.pid IN (12345, 23456)      -- head blocker and one waiter
ORDER BY l.pid, l.granted DESC;

Lock modes you will see most:

#5. Clear the blocker

Try cancel first; it stops the current query and keeps the connection:

SELECT pg_cancel_backend(12345);

Cancel does nothing useful for an idle in transaction session, because no query is running. Terminate it, which rolls back its transaction:

SELECT pg_terminate_backend(12345);

Warning

Terminating rolls back all uncommitted work in that session. A large rollback can take as long as the original work and hold its locks while it runs. Check xact_age and what the session was doing before you kill a batch job, and tell the owner.

On Amazon RDS and Azure Flexible Server, the admin role can cancel and terminate sessions of ordinary users but not of superuser-owned internal processes.

#The DDL lock queue

This is the most common cause of a sudden full outage after a deploy.

1. A report runs a long SELECT on orders and holds AccessShareLock.
2. A migration runs ALTER TABLE orders ADD COLUMN ... and waits for AccessExclusiveLock.
3. Every new SELECT and INSERT on orders now queues behind the ALTER, because lock requests are granted in order.

The ALTER itself would take milliseconds, but the table is unusable until the report finishes. Cancel the ALTER and the queue clears at once.

Run schema changes with a short lock timeout and retry:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN source text;
-- On "canceling statement due to lock timeout", wait and retry

Safer patterns for busy tables:

-- Build indexes without blocking writes (cannot run inside a transaction block)
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

-- Add a foreign key without a long scan under a strong lock
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;

If CREATE INDEX CONCURRENTLY fails, it leaves an INVALID index. Drop it with DROP INDEX CONCURRENTLY and try again.

#Deadlocks

PostgreSQL detects deadlocks after deadlock_timeout (default 1s) and cancels one transaction with ERROR: deadlock detected. The log entry names both processes and the statements. Count them per database:

SELECT datname, deadlocks FROM pg_stat_database WHERE datname IS NOT NULL;

The fix is in the application: update rows in a consistent order (for example, sorted by primary key), keep transactions short, and retry on SQLSTATE 40P01. For job-queue tables, SELECT ... FOR UPDATE SKIP LOCKED lets workers take different rows instead of waiting on each other.

#Prevent it next time

Set guard rails at the role or database level so one bad session cannot hold the table hostage:

ALTER DATABASE appdb SET idle_in_transaction_session_timeout = '5min';
ALTER ROLE app_user SET statement_timeout = '60s';
ALTER ROLE migration_user SET lock_timeout = '5s';

Log every lock wait longer than deadlock_timeout, so the next incident leaves evidence:

ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

On managed services, set log_lock_waits in the parameter group (RDS) or server parameters (Azure Flexible Server) instead of ALTER SYSTEM.

Checklist:

• Alert when sessions in wait_event_type = 'Lock' stay above a threshold for more than a minute, and when any transaction is older than your longest expected batch.
• Run migrations with lock_timeout and retry logic. Never run DDL on a hot table without it.
• Move long reports to a read replica so they do not hold locks on the primary.
• Review idle in transaction sessions weekly; they point to connection handling bugs in the app. See connection exhaustion for the pooling side.