0Pricing
PostgreSQL Performance & Query Optimization · Урок

Диагностика насыщения пула и очередей

Изучайте статистику PgBouncer, чтобы обнаружить исчерпанные пулы и ожидающих клиентов до того, как это заметят пользователи.

«Диагностика насыщения пула и очередей» — бесплатный урок PostgreSQL Performance & Query Optimization на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения PostgreSQL Performance & Query Optimization, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс PostgreSQL Performance & Query Optimization содержит 4 уроков всего.

Части этого урока еще не переведены и отображаются на английском.

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.

Часто задаваемые вопросы

Урок «Диагностика насыщения пула и очередей» бесплатный?

Да — полный текст урока «Диагностика насыщения пула и очередей» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс PostgreSQL Performance & Query Optimization, подпишись на CoddyKit PRO. Курс PostgreSQL Performance & Query Optimization содержит 4 уроков всего.

Чему я научусь в уроке «Диагностика насыщения пула и очередей»?

Изучайте статистику PgBouncer, чтобы обнаружить исчерпанные пулы и ожидающих клиентов до того, как это заметят пользователи. Ты практикуешь PostgreSQL Performance & Query Optimization с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать PostgreSQL Performance & Query Optimization?

Предыдущий опыт не требуется. PostgreSQL Performance & Query Optimization на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «Диагностика насыщения пула и очередей»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке PostgreSQL Performance & Query Optimization?

Да. Каждый урок PostgreSQL Performance & Query Optimization включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Почему соединения дороги в PostgreSQL
  2. Режимы пулов транзакций и сеансов
  3. Размер пулов с учётом числа ядер
  4. Диагностика насыщения пула и очередей
← Назад к PostgreSQL Performance & Query Optimization