It Is Up but Behaving Oddly — What Do You Look At First
In one line
In incident response, the expensive part is not fixing things but working out where the cause is. A database usually does not die. It stays up and goes strange, and the place where the symptom shows up is almost never the place where the cause lives.
Why this was needed
Two order APIs are not responding. Not an error, not a timeout — they just do not come back.
What you do first is fixed. You look at slow queries. But the list is empty. CPU is idle, disk is idle, and the connection count is the same as usual. Every metric is green. If you conclude here "the DB is fine, isn't it?" and start digging on the application side, the whole day is gone.
There is a reason the metrics are green. Nobody is doing any work. Everyone is waiting. A waiting session does not use CPU, does not touch the disk, and does not appear in the slow query list. And the session holding them up is not even running a query.
How it works
The single view pg_stat_activity has almost everything you need. There is an order in which to read it.
| Column | What it tells you |
|---|---|
state |
active means it is running a query right now, idle means it is idle outside a transaction, idle in transaction means it has a transaction open and is idle |
wait_event_type · wait_event |
What it is waiting for. Lock means a lock wait; Client · ClientRead means the client is not sending the next command |
xact_start |
When the transaction started. The single fact that this is old explains most incidents |
pg_blocking_pids(pid) |
The list of pids blocking this session |
The third value of state is the key. If idle in transaction and ClientRead appear together, that session is waiting for a client that handed off work and then disappeared. From the server's point of view nothing is wrong, so no warning ever fires.
Follow the chain one step and you find a victim
This is a screen captured as is in this course's lab environment.
pid | application_name | state | wait_event_type | wait_event | blocked_by
------+------------------+---------------------+-----------------+---------------+-----------
784 | nightly-batch | idle in transaction | Client | ClientRead | {}
796 | api-cart | active | Lock | transactionid | {784}
795 | api-order | active | Lock | tuple | {796}
api-order is blocked. If you ask who blocked it, the answer is 796. But 796 is blocked too. Even if you kill 796, 795 just waits for the next in line, and the real cause, 784, remains.
So the question to ask is not "who blocked me" but "where is the point at which nobody is blocked?"
select distinct b as root_pid
from pg_stat_activity a, unnest(pg_blocking_pids(a.pid)) b
where cardinality(pg_blocking_pids(b)) = 0;
A pid that is blocking others while not being blocked by anyone — that is the end of the chain. On the screen above, it returns the single pid 784.
Do not skip past wait_event either. transactionid means "waiting for the earlier transaction to finish," and tuple means "waiting for the person ahead of me in the queue aiming at the same row." If both values are mixed, it means the queue already has two layers.
Where the connections go
A waiting session holds on to one connection each. This is how this failure spreads.
The default value of lock_timeout is 0, which means unlimited. With a limit in place, it ends like this.
ERROR: 55P03: canceling statement due to lock timeout
It returns as an error after 1 second and the connection goes back to the pool immediately. Without a limit, that connection is tied up forever, and the more requests pile onto the same row, the more connections get tied up. From the moment the pool runs dry, even requests that have nothing to do with that row fail because they cannot get a connection.
This is why the symptom shows up as "the server is not responding" rather than "the DB is slow," and why this incident is easily misdiagnosed as an application problem rather than a database problem.
Raising max_connections is not the first move. It is not that there are too few connections but that they are not being returned, so raising the limit only raises the ceiling on how many connections can get tied up. One process starts per backend, so the memory and context-switch costs grow honestly. The first thing to do is find out why they are not being returned.
What it looks like in the field
First, add "the age of the oldest transaction" to your monitoring metrics. Monitoring that looks only at slow queries will never catch idle in transaction. It is not running a query, so there is nothing to catch. One line is enough.
select max(now() - xact_start) as oldest
from pg_stat_activity where state <> 'idle';
This single value warns you in advance about half of the incidents covered in this course.
Second, set the limit on the connection, not in the code. Putting set lock_timeout in every query means you will forget it someday. If you set it as a session default when the pool lends out a connection, there is nowhere left to forget it. lock_timeout is the limit on "waiting," and idle_in_transaction_session_timeout is the limit on "sitting idle with a transaction open," so they prevent different incidents. In the lab environment I set the latter to 3 seconds and checked: the idle session was simply disconnected and vanished.
Third, always set lock_timeout on a migration. alter table requires ACCESS EXCLUSIVE. In the same environment, I held that lock and ran select count(*), and even the read was blocked.
ERROR: canceling statement due to lock timeout
LINE 1: select count(*) from products
If there is just one long query ahead of it, the migration goes into the wait queue, and every query that comes after it lines up behind the migration. Reads included. With a limit in place, the deployment merely fails, but without one the service stops. A failing deployment is cheaper than a stopped service.
What you will do in the next lab
You read a pg_stat_activity snapshot shaped like the real thing and separate what is blocked from what is blocking. You confirm with numbers the criterion for finding the end of the chain, why idle in transaction escapes monitoring, and why raising max_connections is not the first move.