PostgreSQL Connection Saturation: Performance Problems That Look Like Slow SQL

A query can be innocent and still show up in a slow request. The request waited for a pool connection. The database session waited for the client. Another transaction held a lock. The SQL at the end of the trace gets blamed because it is the only thing with a name.

Connection saturation is one of those PostgreSQL problems that looks like slow SQL from the application side and like chaos from the database side. Too many sessions increase memory pressure, context switching, lock contention, and incident noise.

The fix starts by separating database execution time from waiting for a connection and waiting inside a connection.

Count sessions by state

The simplest snapshot is often enough to change the conversation. Are sessions active, idle, idle in transaction, or waiting?

SELECT
  state,
  count(*) AS sessions
FROM pg_stat_activity
GROUP BY state
ORDER BY sessions DESC;

Idle in transaction is not idle

An idle transaction can hold locks and keep old row versions alive. It may not be using CPU, but it can still make the system slower for everyone else.

SELECT
  pid,
  usename,
  application_name,
  now() - xact_start AS xact_age,
  wait_event_type,
  wait_event,
  left(query, 180) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

max_connections is not throughput

Raising max_connections can let more clients wait inside PostgreSQL, but it does not create more CPU, memory, I/O bandwidth, or lock capacity. In many OLTP systems, a smaller active working set with a real pool is faster than a giant connection pile.

Pool starvation belongs in the trace

Application metrics should separate time waiting for a pool connection from time executing SQL. Without that split, every pool incident becomes a fake slow-query incident.

• Track pool wait time.
• Track checked-out connections.
• Track transaction duration.
• Track query execution duration separately.
• Set timeouts for pool wait, statement execution, idle transactions, and lock waits.

Use PgBouncer deliberately

PgBouncer can reduce connection pressure, but the pool mode matters. Session pooling preserves session state. Transaction pooling is more efficient but changes assumptions around prepared statements, temporary tables, session variables, and advisory locks.

The pool mode should be part of the application contract, not a hidden infrastructure setting.

The saturation runbook

1. Count sessions by state and wait event.
2. Find old transactions and idle-in-transaction sessions.
3. Compare active sessions to CPU cores and storage capacity.
4. Check application pool wait metrics.
5. Confirm pool mode and driver behavior.
6. Add timeouts before increasing max_connections.

The production habit

The teams that get good at PostgreSQL performance do not chase every knob. They turn a vague complaint into a named failure mode, collect one clean measurement, make one change, and then compare the next measurement against the first. That rhythm is slower than guessing for the first hour and much faster by the end of the incident.