PostgreSQL Production Post-Mortem: When the DB Is Up but Every Request Hangs

⏳ TL;DR: The Short Version

• The night every endpoint timed out, the database was alive, CPU was sitting around 10%, and the slow-query log had recorded nothing. The backends were not **running**; they were **waiting**.
• PostgreSQL records why a backend is paused in two columns of `pg_stat_activity`: `wait_event_type` and `wait_event`. Since PostgreSQL 17 there is also a `pg_wait_events` view (`type`, `name`, `description`) that translates those wait codes into plain English.
• The diagnostic order is fixed: find sessions with `wait_event_type = 'Lock'`, walk the blocking chain with `pg_blocking_pids(pid)` until you reach the real blocker, confirm the lock type with `pg_locks`, and only then decide whether to cancel or terminate.
• What turns an intermittent window into a full outage is rarely one slow query. It is usually a single **idle in transaction** session holding a lock it no longer needs.

Disclosure: this post contains independent third-party affiliate links. Buying through them does not change your price but may earn this site a commission. Purchasing is unrelated to the troubleshooting method described here.

The Incident: Database Up, Every Endpoint Stuck

Version details matter here, because several of the commands below behave differently across releases:

Item	Version / note
OS	Ubuntu 24.04 LTS
Database	PostgreSQL 18.6 (released 2026-08-13); one instance still on 17.11
Pooler	PgBouncer 1.25.2
Tools	pg_stat_activity, pg_locks, pg_stat_statements, pg_wait_events
Network	2.5G switch shared by the primary and a read replica

It was a textbook e-commerce afternoon. Around 14:00 the order API's P99 jumped from 80ms to 12s, and thirty seconds later 504 responses piled up. The first instinct is always "the database got slow," so we checked three things:

1. top: the PostgreSQL backend processes together used just over 10% CPU.

2. Slow-query log: log_min_duration_statement = 1000 was on, yet that hour recorded almost nothing.

3. Connections: PgBouncer's cl_active had climbed to the max_client_conn ceiling, and requests were queuing behind it.

Those three facts together rule out "one query scanning too much." **Low CPU + a clean slow-query log + a full connection pool** all point the same way: most sessions were not burning CPU at all. They were waiting on a lock, and waiting does not show up in the slow-query log unless you also enable log_lock_waits.

When I actually ran this incident, my first move was a restart, which is close to the least useful action for a waiting-type failure and wiped the live evidence on the way down. I spent 35 minutes from 14:05 to 14:40, and the real diagnosis took under ten of them. The workflow below is the checklist I wrote afterward so the next on-call engineer does not lose those 25 minutes.

First, Quantify "Waiting": wait_event_type and wait_event

Since PostgreSQL 9.6, pg_stat_activity has exposed wait_event_type and wait_event. Whenever a backend is not spending time on CPU work, it writes the reason it paused into those two columns. The broad categories are few:

• `Lock`: heavyweight locks (table, row, `transactionid`, and so on) and the most common outage culprit.
• `LWLock`: lightweight locks, usually internal contention such as `WALWrite` or `ProcArray`.
• `IO`: disk I/O waits such as `DataFileRead` or `WALWrite`.
• `Client`: waiting on the client, classically `ClientRead` when the application has the result but has not committed or sent the next query.
• `IPC`, `Timeout`, `BufferPin`, `Activity`: internal messaging, timeout waits, buffer pinning, and process main loops.

Keep one counter-intuitive fact in mind: wait_event_type = 'Client' with wait_event = 'ClientRead' usually means the database is fine and the application is stalling. On read replicas this is easily misread as database trouble.

Start with a single read-only query that aggregates every non-idle session by wait:

SELECT wait_event_type, wait_event, count(*) AS sessions
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND state <> 'idle'
GROUP BY 1, 2
ORDER BY sessions DESC;

If the Lock row suddenly shows dozens of sessions, you have found your incident. This statement is read-only and safe to run in production at any time.

PostgreSQL 17's pg_wait_events Turns Wait Codes Into Plain English

pg_stat_activity.wait_event returns short identifiers such as WALWrite, ClientRead, or relation. Before version 17, understanding them meant digging through the documentation or the wait_event_names.txt file in the source tree. Starting with PostgreSQL 17, those descriptions were frozen into a system view called pg_wait_events:

Column	Type	Meaning
`type`	text	Broad wait category, matching `wait_event_type`
`name`	text	Specific wait name, matching `wait_event`
`description`	text	One human-readable sentence

The view is read-only, readable by every role, needs no statistics reset, and has no runtime cost. It is documentation that ships with the server. A stock 17.x instance returns roughly 350 rows, and the count grows with each major release as new wait points are instrumented. Join it to pg_stat_activity on type / name and you can read the real answer at the incident scene:

SELECT a.pid, a.usename, a.state, a.wait_event_type, a.wait_event,
       now() - a.xact_start AS xact_age,
       now() - a.query_start AS query_age,
       w.description
FROM pg_stat_activity a
JOIN pg_wait_events w
  ON a.wait_event_type = w.type
 AND a.wait_event = w.name
WHERE a.wait_event IS NOT NULL
  AND a.state = 'active'
ORDER BY xact_age DESC;

xact_age (how long the transaction has been open) matters more than query_age (how long the current statement has run). A statement that started 200ms ago can still hold a lock acquired 18 minutes earlier by the surrounding transaction.

The Three-Query Workflow

Query one, find who is blocked:

SELECT pid, usename, application_name, wait_event_type, wait_event,
       now() - query_start AS waited, left(query, 80) AS stmt
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY waited DESC;

Query two, walk the chain to the root:

SELECT blocked.pid   AS blocked_pid,
       pg_blocking_pids(blocked.pid) AS blocker_pids,
       blocked.state AS blocked_state,
       blocker.pid   AS blocker_pid,
       blocker.state AS blocker_state,
       now() - blocker.xact_start AS blocker_xact_age,
       left(blocker.query, 80) AS blocker_stmt
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocker
  ON blocker.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock'
ORDER BY blocker_xact_age DESC;

pg_blocking_pids() returns an **array**, so there may be more than one blocker. Pay close attention to blocker_state: if it reads idle in transaction, that backend stopped doing useful work long ago and is still holding locks. That is the single most common, and most fixable, cause.

Query three, confirm which lock is being waited on:

SELECT l.pid, l.locktype, l.relation::regclass, l.mode, l.granted,
       now() - a.query_start AS waited
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY waited DESC;

There is a trap with row-level contention: because row locks are stored on disk, pg_locks often shows no tuple entry and instead reports a wait on transactionid. So "I checked pg_locks and saw no row lock" is expected behavior, not a dead end.

Post-Mortem: Five Real Errors and How to Fix Them

Error 1: ERROR: canceling statement due to lock timeout

ERROR:  canceling statement due to lock timeout

Symptom: a batch job that updates order status fails every so often, then succeeds on retry.

**Root cause:** the statement could not acquire its lock within lock_timeout and was cancelled. Note this is not statement_timeout. lock_timeout caps how long a statement queues for a lock; statement_timeout caps total execution time. **Raising statement_timeout will not rescue a statement cancelled by lock_timeout.**

**Fix:** use query two to find the blocker. If it is an abandoned idle in transaction session, release it with pg_terminate_backend(). If it is a legitimate but heavy transaction, reshape the job into a short lock_timeout (say 100ms) plus an application-level retry loop, so it fails fast and loudly instead of occupying a connection in the queue.

Error 2: ERROR: relation "pg_wait_events" does not exist

ERROR:  relation "pg_wait_events" does not exist
LINE 1: SELECT * FROM pg_wait_events LIMIT 3;

Symptom: you paste the SQL from this article and immediately get a missing-relation error.

**Root cause:** the view was **introduced in PostgreSQL 17** (commit 1e68e43d). On 16 and earlier it simply does not exist. Confirm with SHOW server_version; or SELECT version();.

**Fix:** on versions below 17, fall back to the wait_event_type / wait_event columns in pg_stat_activity and read the official wait-event tables. The durable fix is upgrading to a supported major version. Note that **PostgreSQL 14 stops receiving fixes on 2026-11-12**, so any instance still on 14 should plan an upgrade soon.

Error 3: ERROR: deadlock detected

ERROR:  deadlock detected
DETAIL:  Process 52210 waits for ShareLock on transaction 998877; blocked by process 51990.
Process 51990 waits for ShareLock on transaction 552211; blocked by process 52210.
HINT:  See server log for query details.

Symptom: two sessions each wait for a row lock the other holds, forming a cycle.

**Root cause:** two code paths acquire locks in **opposite order**. Path A locks orders then inventory; path B locks inventory then orders. After deadlock_timeout (default 1s), PostgreSQL's detector intervenes and rolls back one transaction at random.

**Fix:** timeouts cannot cure this. Standardize lock ordering so every transaction touching both orders and inventory accesses them in the same fixed order. Turn on log_lock_waits = on so the log preserves the evidence chain.

Error 4: FATAL: terminating connection due to idle-in-transaction timeout

FATAL:  terminating connection due to idle-in-transaction timeout

Symptom: sporadic "server closed the connection" errors that always follow a few reporting endpoints.

**Root cause:** the application opened a BEGIN, then did something outside the database (calling an external API, running post-processing), leaving the transaction open the whole time. On PostgreSQL 18.6 / 17.11, with idle_in_transaction_session_timeout set, such idle sessions are forcibly disconnected and rolled back. **This is a protective mechanism, not a bug** — but it exposes a wrongly drawn transaction boundary.

**Fix:** move external calls and message sends out of the transaction. For genuinely long work, use short transactions plus idempotent retries. Also set idle_in_transaction_session_timeout (say 60s) on ordinary business databases so a forgotten transaction dies early instead of dragging the whole site down.

Error 5: pg_blocking_pids() returns an empty array, yet the session is clearly waiting

**Symptom:** a session reports wait_event_type = 'Lock', but pg_blocking_pids(pid) returns {}. Nobody appears to block it.

**Root cause:** pg_blocking_pids() only resolves heavyweight lock chains **within the same instance**. If the session is actually waiting on Client (a ClientRead, waiting for the app to consume the result) or another internal wait, the function has no blocker to return. The blocker could also live in a different database, or connection pooling can change PID semantics so the chain looks empty.

**Fix:** go back to pg_stat_activity and read the real wait_event_type. Is it Lock, or is it Client / IO / LWLock? Only Lock justifies chasing a blocker. And set application_name on every application so sessions are traceable to a service instead of guessed at inside the pool.

Seeing "Waiting" in EXPLAIN: SERIALIZE and MEMORY in PG 17

Not every "slow" is a lock. Some of it is data conversion and planner memory. PostgreSQL 17 added two EXPLAIN options:

• `SERIALIZE`: shows the time spent converting the result set into the format sent over the network, which can dominate for large result sets or complex types.
• `MEMORY`: reports memory used during query planning.

Stack them with ANALYZE and BUFFERS, and you get the full picture:

EXPLAIN (ANALYZE, BUFFERS, SERIALIZE, MEMORY, TIMING)
SELECT o.id, o.status, o.total
FROM orders o
WHERE o.batch_id = 42;

Watch three things: whether actual rows matches the rows= estimate in order of magnitude (a large gap means stale statistics, so run ANALYZE); whether read= buffer counts are abnormal (missing index or bloat); and whether the Serialization portion is disproportionate (the bottleneck is conversion, not scanning).

Hardening: Turn "Intermittent" Into "Contained"

1. **Tier your timeouts** instead of using one global value. Use a tight statement_timeout for OLTP; relax it (or set it to 0) only for migrations and analytics sessions. Always set lock_timeout so lock waits fail early instead of queuing forever, and always set idle_in_transaction_session_timeout.

2. **Use low-lock migration patterns.** Build indexes in production with CREATE INDEX CONCURRENTLY, keep heavy table changes off peak hours, and SET lock_timeout before an ALTER TABLE so you can back off and retry rather than queue a DDL behind a long query and create a lock queue.

3. **Make the log carry evidence.** Turn on log_lock_waits, tune deadlock_timeout to the workload's sensitivity, and standardize application_name, so the next incident can be reconstructed quickly.

Self-Hosted PostgreSQL Lab and Desk Gear (Affiliate Links)

These three are a common combination for a small self-hosted lab used to reproduce the waits described above: a low-power node for a test instance, a solid cable for primary-replica traffic, and a card for a cold backup. They are buying references only and are unrelated to the method itself:

• Raspberry Pi 5 (8GB): a low-power node for a self-hosted PostgreSQL test instance, handy for reproducing lock waits and replication lag. 👉 Check it on Amazon >>
• Amazon Basics Cat 6 Ethernet cable: for primary-replica and internal traffic; pick the length for your rack or desk. 👉 Check it on Amazon >>
• Amazon Basics microSDXC 128GB: a single-board-computer system disk for a disposable test environment. 👉 Check it on Amazon >>

Prices shift with memory markets and promotions; check the live listing on Amazon.

FAQ

**Q: Does wait_event_type = 'Client' still count as a database problem?**

A: No. Client means the backend is waiting for the client to read the result or send the next statement; the bottleneck is the application. A flood of ClientRead usually means the app holds a connection without committing.

Q: I cannot see other users' sessions in pg_stat_activity. What now?

A: An ordinary role sees query text only for its own sessions, but wait_event_type / wait_event / pid remain readable. For the full view, use the pg_monitor role or a superuser.

Q: Does pg_wait_events add production overhead?

A: No. It is a static catalog view with no statistics reset and no runtime cost, queryable at any time.

Q: What lock_timeout value is right?

A: There is no universal number. Short OLTP transactions often start near 1s, while migrations and DDL use something shorter (for example 100ms) with retries. The point is to fail early and loudly rather than queue indefinitely.

Q: Will idle_in_transaction_session_timeout kill legitimate long transactions?

A: It can, so set the threshold per workload. It measures time spent with a transaction open but doing nothing; genuinely running long transactions are unaffected. What gets killed is usually a transaction boundary that was drawn wrong.

Final Thoughts

The lesson compresses into one line: **in database incidents, "slow" and "waiting" are different problems, and pg_wait_events makes "waiting" readable for the first time.** Read the wait type correctly, trace the blocking chain to its root, and tier your three timeouts, and most "every endpoint suddenly hangs" events can be classified within ten minutes instead of being fixed by restarting and hoping.

Further reading:

👉 Join MiniMax Token Plan: AI coding acceleration for businesses

👉 Join Xiaomi MiMo Platform: Leading AI model platform with cost-effective inference

👉 Join Aliyun AI: Top AI products with exclusive coupons for business innovation

📌 This article was AI-assisted generated and human-reviewed | TechPassive — An AI-driven content testing site focused on real tool reviews