Connection Blocking Detection | PG Pilot PostgreSQL Monitoring

What is this issue?

PostgreSQL uses locks to ensure data consistency. When one transaction holds
a lock that another needs, the second transaction waits (blocks).

Blocking becomes problematic when:
- The blocker holds locks for a long time
- Multiple queries pile up waiting
- A blocking chain forms (A blocks B, B blocks C)

Common causes:
- Long-running transactions holding row locks
- DDL operations (ALTER TABLE) blocking queries
- Explicit LOCK TABLE statements
- Unindexed foreign key deletes

Why it matters

Query Delays

Blocked queries can't complete until the blocker finishes

Connection Exhaustion

Blocked connections pile up, exhausting the pool

Cascade Effect

One blocker can stall dozens of queries

Application Timeouts

Users experience errors when queries time out

How PG Pilot detects it

```sql
SELECT
  blocked.pid AS blocked_pid,
  blocked.usename AS blocked_user,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.usename AS blocking_user,
  blocking.query AS blocking_query,
  blocked.wait_event_type,
  blocked.wait_event
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.relation = blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
  AND blocking_locks.granted
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;
```

How to fix it

1

Identify the blocking chain

Find all blocked and blocking sessions:

SELECT
  a.pid,
  a.usename,
  a.query,
  a.wait_event_type,
  a.wait_event,
  l.mode,
  l.granted
FROM pg_stat_activity a
JOIN pg_locks l ON a.pid = l.pid
WHERE a.pid IN (blocked_pid, blocking_pid);

2

Evaluate the blocker

Check if the blocking query is:
- Near completion (let it finish)
- Stuck or runaway (consider canceling)
- A forgotten transaction (terminate)

SELECT pid, state, query_start, xact_start
FROM pg_stat_activity
WHERE pid = blocking_pid;

3

Cancel or terminate if necessary

Cancel the blocking query:

SELECT pg_cancel_backend(blocking_pid);

If it doesn't respond, terminate:

SELECT pg_terminate_backend(blocking_pid);

4

Set lock timeout

Prevent future long waits:

SET lock_timeout = '30s';

Or globally:

ALTER SYSTEM SET lock_timeout = '30s';

Prevention

Keep transactions short
Set lock_timeout to fail fast instead of waiting
Schedule DDL during low-traffic periods
Index foreign key columns to speed up cascading operations

Related Features

Connection Management

PostgreSQL Documentation

PostgreSQL Explicit Locking