0Pricing
PostgreSQL Performance & Query Optimization · Lesson

Diagnosing Pool Saturation and Queueing

Read PgBouncer stats to spot exhausted pools and waiting clients before users notice.

Diagnosing Pool Saturation and Queueing is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Pools Saturate

PgBouncer multiplexes many client connections onto a small set of server connections. In transaction pooling, a server connection is borrowed only for the duration of a transaction, then returned to the pool.

A pool saturates when every server connection is busy and new client requests must wait in a queue. The queue is invisible from the database's side — Postgres just sees a steady, capped number of backends — so you must read PgBouncer's own stats to see the pressure building.

  • pool_size = max server connections per (database, user) pool
  • Waiting clients = demand that exceeds that ceiling

Connecting to the Admin Console

PgBouncer exposes a virtual database called pgbouncer. Connect to it with psql using a user listed in admin_users, then run SHOW commands to read its internal state.

This is your primary diagnostic surface — there is no separate dashboard required.

-- Connect to the PgBouncer admin console
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

-- Once inside, list the available diagnostic views
SHOW HELP;

SHOW POOLS: the cl_waiting Column

SHOW POOLS is the single most important command for spotting saturation. Each row is one pool, identified by database and user.

  • cl_active — clients currently bound to a server connection
  • cl_waiting — clients queued, waiting for a free server connection
  • sv_active — server connections busy serving a transaction
  • sv_idle — server connections free to be assigned

The rule of thumb: any sustained cl_waiting > 0 with sv_idle = 0 means the pool is saturated.

-- Run inside the pgbouncer admin database
SHOW POOLS;

Reading a Saturated Pool

Compare two snapshots. A healthy pool keeps spare idle servers and an empty wait queue:

  • cl_active=18 cl_waiting=0 sv_active=6 sv_idle=14 → plenty of headroom
  • cl_active=20 cl_waiting=47 sv_active=20 sv_idle=0 → saturated and queueing

In the second case sv_active equals pool_size, sv_idle is zero, and 47 clients are stuck in line. Latency users feel = queue wait + actual query time.

maxwait: How Long the Queue Has Been Stuck

SHOW POOLS also reports maxwait and maxwait_us — the time the oldest waiting client has been queued, in seconds and microseconds.

This is your early-warning metric. A non-zero and growing maxwait means clients are not just queued but starving. If maxwait approaches your application's statement or connection timeout, requests will start failing before users even get a response.

  • maxwait = 0 → nobody is waiting right now
  • maxwait = 4 and climbing → act now, the pool is too small or the DB is too slow
-- Focus on the queue-pressure columns
-- (column subset shown conceptually; SHOW POOLS returns all)
SHOW POOLS;
-- Watch: database | user | cl_waiting | sv_idle | maxwait | maxwait_us

Polling for Saturation Trends

A single snapshot can mislead — pools fill and drain in bursts. Watch the trend by polling the admin console on an interval and logging the key columns.

From a shell you can loop psql and grep the pool you care about. Rising cl_waiting across samples confirms real saturation rather than a momentary spike.

-- Poll PgBouncer every 2 seconds, watch one pool
watch -n 2 "psql -h 127.0.0.1 -p 6432 -U pgbouncer \
  -d pgbouncer -c 'SHOW POOLS;' | grep ' app_db '"

SHOW STATS: Throughput and Query Time

Saturation has two root causes: too little capacity, or queries that hold server connections too long. SHOW STATS separates them.

  • avg_query_time — average query duration in microseconds
  • avg_xact_time — average transaction duration; long transactions hog server connections
  • avg_query_count — queries per second

If avg_xact_time is high, raising pool_size only delays the problem — fix the slow transactions instead.

-- Per-database throughput and timing averages
SHOW STATS;

Correlating With Postgres Itself

PgBouncer tells you clients are waiting; Postgres tells you why the server connections are busy. Query pg_stat_activity on the real database to see what those backends are doing.

Long-running queries, idle-in-transaction sessions, or lock waits will pin the limited server connections and feed the PgBouncer queue.

-- On the actual Postgres server: find what is holding backends
SELECT pid,
       state,
       wait_event_type,
       wait_event,
       now() - xact_start AS xact_age,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE datname = 'app_db'
  AND state <> 'idle'
ORDER BY xact_age DESC NULLS LAST;

The idle in transaction Trap

A frequent cause of PgBouncer queueing is sessions left idle in transaction: the app opened a transaction, then stalled (waiting on an external call, a slow loop, or a bug) without committing. That server connection stays checked out and unavailable to the pool.

Hunt these down explicitly — they often explain a saturated pool whose avg_query_time looks low.

-- Sessions holding a connection open but doing no work
SELECT pid,
       usename,
       now() - state_change AS idle_for,
       left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_for DESC;

SHOW CLIENTS and SHOW SERVERS

For finer detail, two more admin views drill into individual connections:

  • SHOW CLIENTS — every client link; a state of waiting plus a large wait value pinpoints the starving clients
  • SHOW SERVERS — every server link and which client (if any) currently owns it

Use these when SHOW POOLS shows queueing and you need to identify exactly which clients or application hosts are affected.

-- Per-connection detail; look for state='waiting' and high 'wait'
SHOW CLIENTS;

-- Which server links are linked to which clients
SHOW SERVERS;

Turning Diagnosis Into Action

Once the stats point to a cause, the fix follows directly:

  • High cl_waiting, low avg_xact_time → genuine capacity shortage; raise pool_size (and check Postgres max_connections has room)
  • High avg_xact_time or many idle in transaction → fix the app/queries; more pool size won't help
  • maxwait near app timeout → tune query_wait_timeout so clients fail fast instead of hanging
  • One pool starved, others idle → consider a dedicated pool or reserve_pool_size
-- Example pgbouncer.ini tuning after diagnosis
[databases]
app_db = host=10.0.0.5 port=5432 dbname=app_db pool_size=40

[pgbouncer]
pool_mode = transaction
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
query_wait_timeout = 10

Quick Check

You read SHOW POOLS and see this row for one pool.

Recap

You can now diagnose PgBouncer pool saturation before users feel it:

  • SHOW POOLS — watch cl_waiting, sv_idle, and maxwait; sustained waiting with zero idle servers means saturation
  • SHOW STATS — use avg_xact_time to tell a capacity shortage from slow transactions
  • pg_stat_activity — find the long-running and idle in transaction backends pinning your pool
  • SHOW CLIENTS / SHOW SERVERS — drill down to the exact affected connections

Act on the cause: add capacity only when queries are fast; otherwise fix the transactions, and tune query_wait_timeout so clients fail fast instead of hanging.

Frequently asked questions

Is the “Diagnosing Pool Saturation and Queueing” lesson free?

Yes — the full text of “Diagnosing Pool Saturation and Queueing” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “Diagnosing Pool Saturation and Queueing”?

Read PgBouncer stats to spot exhausted pools and waiting clients before users notice. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Diagnosing Pool Saturation and Queueing” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Why Connections Are Expensive in PostgreSQL
  2. Transaction vs Session Pooling Modes
  3. Sizing Pools Against Core Count
  4. Diagnosing Pool Saturation and Queueing
← Back to PostgreSQL Performance & Query Optimization