There's a great genre of article about connection pools — size them (cores × 2), don't make them bigger when they exhaust, put PgBouncer in front so ten app instances don't each open their own twenty connections. All true. All written from the application's side of the glass: your SQLAlchemy pool, your pool_timeout, your worker threads piling into a queue.
The trouble is that from the app's side, pool exhaustion has exactly one symptom, and it's useless. Every request hangs. Pages spin. Eventually: FATAL: too many connections. You go look at the database, braced for a fire, and it's fine — CPU low, queries fast. The database is healthy and your app is dead anyway.
So everyone concludes the database has nothing to tell you. It has plenty to tell you. You were just standing on the wrong side of the glass. pg_stat_activity is the connection log, and it separates the three ways a pool drains into three different shapes.
One query, three fingerprints
The number behind FATAL: too many connections is max_connections. So the first thing you want is the count against it — but broken down by what each connection is doing, because that breakdown is the whole diagnosis:
SELECT count(*) FILTER (WHERE backend_type = 'client backend') AS used,
count(*) FILTER (WHERE backend_type = 'client backend'
AND state = 'active') AS active,
count(*) FILTER (WHERE backend_type = 'client backend'
AND state = 'idle') AS idle,
count(*) FILTER (WHERE backend_type = 'client backend'
AND state = 'idle in transaction') AS idle_in_txn,
current_setting('max_connections')::int AS max_conn,
current_setting('superuser_reserved_connections')::int AS reserved
FROM pg_stat_activity;
backend_type = 'client backend' throws out autovacuum, the walwriter, and the other internal processes — they're real backends, but they don't draw on the pool a client competes for, so counting them would just inflate the number that matters. What's left is your applications' connections, sorted into three buckets. The buckets are the tell.
The leak. Code borrows a connection, opens a transaction, and an exception fires before the COMMIT/ROLLBACK — or before the with block that would have returned it. The connection isn't running anything. It's sitting on an open transaction, holding its slot (and any locks it took) forever:
used | active | idle | idle_in_txn | max_conn | reserved
------+--------+------+-------------+----------+----------
98 | 2 | 7 | 89 | 100 | 3
89 connections idle in transaction. Nobody is doing any work — active is 2 — and yet you're one connection off the ceiling. That's not load. That's a leak, bleeding one slot per un-returned connection until it hits the wall. idle in transaction pegged near the limit is the single most diagnostic shape in this whole panel.
The slow query. Same ceiling, completely different fingerprint:
used | active | idle | idle_in_txn | max_conn | reserved
------+--------+------+-------------+----------+----------
97 | 94 | 2 | 1 | 100 | 3
94 connections genuinely active. Everybody's running SQL — it's just that queries which should take 20ms are taking 2 seconds (an unindexed scan, a lock wait), so each connection is held 100× longer and the pool drains 100× faster under the same traffic. This is the one case where the database really is the bottleneck, and the count tells you so honestly: nothing is leaked, there's just more concurrent work than there are cores to run it.
The pools multiplying. The one that only ever shows up in production:
used | active | idle | idle_in_txn | max_conn | reserved
------+--------+------+-------------+----------+----------
96 | 3 | 91 | 2 | 100 | 3
91 connections plain idle. Not in a transaction — just open, doing nothing, warm. This is ten app instances each keeping a "reasonable" pool of twenty connections alive, against a server whose limit is 100. 20 × 10 = 200 wanted, 100 available, and the connections aren't even busy — they're the resting pool, reserved by the app just in case. You never saw it in staging because staging runs one instance. It appears the moment you scale out. idle high while active is near zero is the shape of pools competing for a fixed ceiling.
Three causes the app-side dashboard renders as one identical hang, told apart by which column is large.
Why you get locked out before it's "full"
Notice reserved in that query. superuser_reserved_connections (default 3) holds back slots so a superuser can still get in to fix things when the pool is jammed. The catch: those slots are subtracted from your budget, not added to the top. A normal application login starts failing at max_conn − reserved, i.e. 97, not 100. So the panel that says "97 / 100, you've got headroom" is wrong for everyone who isn't a superuser — they were refused three connections ago. Surface reserved next to the count or you'll misread the last few slots every time.
Naming the connection that's stuck
The counts tell you which failure. To fix a leak you need the specific session — who opened it, from which app, and how long ago they wandered off:
SELECT pid, usename, application_name, state,
EXTRACT(EPOCH FROM (now() - query_start))::int AS secs,
query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY (state = 'active') DESC, query_start ASC NULLS LAST;
For an idle in transaction row, query is the last statement it ran before going idle, and secs is how long ago that statement started — so a big secs on an idle-in-transaction row is a transaction somebody opened, ran one thing in, and abandoned. (If you want the strictly precise "time in this state," that's now() − state_change; query_start runs a hair longer because it includes the statement's own runtime, but for spotting the abandoned transaction it's the same story.) Sort active first so live work is on top, then oldest-first so the connection that's been squatting longest floats up. That row is your leak, with a pid you can hand to whoever owns that application_name.
MySQL has the count, not the breakdown
The equivalent on MySQL is Threads_connected against max_connections:
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';
You get the ceiling and the headroom — the "too many connections" number — but not the by-state split, because SHOW STATUS doesn't bucket threads into active / idle / idle-in-transaction the way pg_stat_activity does. (You can dig equivalents out of performance_schema, but it's not the one clean counter Postgres hands you.) So the MySQL version answers are you near the wall, but not which of the three walls — you'd fall back to SHOW PROCESSLIST and read states by hand.
The honest part
None of this fixes anything. It's a read-only look at a live view — it can't return a leaked connection or make a slow query fast. The actual repair is on the app's side of the glass, exactly where the pool articles put it: return every connection with with/try-finally, put PgBouncer in front so your instances stop multiplying, size the pool to your cores instead of your fears.
But here's the thing the app's own dashboard structurally cannot do: it only sees its own pool. Instance #4 has no idea instances #1–3 and #5–10 exist, let alone that the ten of them together just overran a 100-connection server. The only place the sum is visible — every instance's connections, every state, side by side against the real ceiling — is pg_stat_activity, on the database's side of the glass. Which of the three failures you have is a question only the database can answer, and it answers it in one query.
This is one piece of cli2ui — a local-only web UI over the
psqlcommands you keep half-remembering. No AI, no SaaS. It's MIT-licensed on GitHub. When your app hangs, do you look at the app, or atpg_stat_activity?
Top comments (0)