诊断连接池饱和与排队
读取 PgBouncer 统计信息，在用户察觉前发现连接池耗尽和客户端等待。
诊断连接池饱和与排队 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 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 connectioncl_waiting— clients queued, waiting for a free server connectionsv_active— server connections busy serving a transactionsv_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 headroomcl_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 nowmaxwait = 4and 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_usPolling 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 microsecondsavg_xact_time— average transaction duration; long transactions hog server connectionsavg_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; astateofwaitingplus a largewaitvalue pinpoints the starving clientsSHOW 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, lowavg_xact_time→ genuine capacity shortage; raisepool_size(and check Postgresmax_connectionshas room) - High
avg_xact_timeor manyidle in transaction→ fix the app/queries; more pool size won't help maxwaitnear app timeout → tunequery_wait_timeoutso 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 = 10Quick 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, andmaxwait; sustained waiting with zero idle servers means saturation - SHOW STATS — use
avg_xact_timeto tell a capacity shortage from slow transactions - pg_stat_activity — find the long-running and
idle in transactionbackends 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.
用 AI 导师学习 SQL — 免费
在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。
- 课程
- 22
- 课程
- 88
常见问题解答
「诊断连接池饱和与排队」课时是免费的吗？
是的 — 「诊断连接池饱和与排队」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。
「诊断连接池饱和与排队」这节课中我会学到什么？
读取 PgBouncer 统计信息，在用户察觉前发现连接池耗尽和客户端等待。 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 PostgreSQL Performance & Query Optimization 需要有经验吗？
无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。
「诊断连接池饱和与排队」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？
能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。