LaoHandDeveloper Cheatsheet
Databases

PostgreSQL Connection Pooling: PgBouncer and the Connection Explosion

Production dies with FATAL: sorry, too many clients already and the connection count grows like a stock chart. This guide covers the exact symptoms of exhausted max_connections, a comparison of the three PgBouncer pooling modes and their feature limitations, and concrete queries to locate a connection storm.

By LaoHand Team·10 min read·Updated 2026-09-30

Recognising the symptoms of a connection explosion

The loudest symptom is `FATAL: sorry, too many clients already` in the application log. But that is only half the story — the other half is connection leakage: the application sees no error while the database connection count climbs monotonically until it hits the ceiling. A typical field report is "fine for 10 minutes after deploy, then mass timeouts, fine again right after a restart, then it creeps back".

The second class of symptom is resources consumed by the connections themselves. Every PostgreSQL connection is a separate OS process with its own memory space. At a few thousand connections, process scheduling and memory management alone slow the whole machine even when no query is running.

The third is lock and snapshot bloat. Idle connections that still hold an open transaction keep locks alive and block vacuum cleanup, causing table bloat. This is also why adding a pool can make things slower: the pool is fine, the pooling mode is wrong, and idle connections sit in the middle of a transaction.

The first debugging step is always to establish facts: `SHOW max_connections;` for the ceiling, `SELECT count(*) FROM pg_stat_activity;` for the current count, then group `pg_stat_activity` by `state` and `application_name` to find who is holding the connections. Without those numbers, any "optimisation" is guesswork.

SHOW max_connections;
SHOW superuser_reserved_connections;
SELECT application_name, state, count(*)
FROM pg_stat_activity
GROUP BY 1, 2 ORDER BY 3 DESC;
SELECT now() - xact_start AS xact_age, pid, state, left(query, 80)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND now() - xact_start > interval '5 min'
ORDER BY xact_age DESC;

Session, transaction and statement pooling compared

**Session mode**: after a client connects it exclusively holds one backend PostgreSQL connection until it disconnects. This is pooling in name only — it saves TCP handshake cost but not connection count. Ten pgbouncer instances with 100 clients each still means 1000 backends.

**Transaction mode** (the common choice): a backend is borrowed at `BEGIN` and returned immediately after `COMMIT` or `ROLLBACK`. A pool of 50 backends comfortably serves 1000 clients. This is the right default for web applications, but it requires that the application not rely on session state outside a transaction.

**Statement mode**: the connection is returned after every single statement. It is the most aggressive mode and disables multi-statement transactions, `LISTEN`/`NOTIFY`, `WITH HOLD` cursors, session-level `SET` and advisory locks, so it suits pure OLTP batch or migration workloads only.

**Feature limitations** to check in transaction mode: session-level `SET`, `LISTEN`/`NOTIFY`, `WITH HOLD` cursors, `PREPARE`/`DEALLOCATE` lifetimes that cross transaction boundaries, and advisory locks leaking to arbitrary clients. If your application needs these, enable `max_prepared_statements` or fall back to session mode.

[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction        # session | transaction | statement
max_client_conn = 1000          # 客户端上限
default_pool_size = 50          # 每库/每用户的真实后端连接数
min_pool_size = 5
reserve_pool_size = 10          # 突发时的应急余量
reserve_pool_timeout = 3
max_db_connections = 200        # 所有数据库合计的后端上限
auth_type = md5
admin_users = pgbouncer_admin

Locating the leak: diagnosing a connection storm

The most common root cause of a connection storm is not the database but the application running without pooling. Three leak patterns dominate: opening a connection per request and forgetting to close it; a pool whose `max_size` far exceeds what the database can serve (20 instances × 100 against `max_connections=200`); or ORM sessions whose lifetime does not match the request boundary.

A second root cause is a retry storm triggered by the storm itself. Once connections start being refused, applications commonly retry, so every request retries at 30ms, 60ms and 120ms — 1000 refused requests become 3000 connection attempts and recovery slows down. The cure is a hard connection timeout plus a retry ceiling.

The concrete method: group `pg_stat_activity` by `application_name` or `client_addr` to see who holds the most; check `numbackends` in `pg_stat_database` to find which database is ballooning; then inspect `pg_stat_activity` filtered to `backend_type = 'client backend'` and look at `query_start` for connections that are idle yet unreleased.

Also verify pgbouncer itself. `SHOW POOLS` reports `cl_active` (active clients), `sv_active` (active backends), `maxwait` and `maxwait_us` per pool. A persistently non-zero `maxwait` means the pool is too small or queries are slow; `sv_active` pinned at `default_pool_size` means you need more backends, not more clients.

SHOW POOLS;
SHOW DATABASES;
SHOW STATS;
SHOW CLIENTS;

SELECT datname, numbackends FROM pg_stat_database ORDER BY numbackends DESC;
SELECT client_addr, state, count(*) FROM pg_stat_activity GROUP BY 1,2 ORDER BY 3 DESC;

Sizing max_connections and a working template

`max_connections` is neither "bigger is better" nor "smaller is better". The ceiling is set by memory: each backend process needs `work_mem` plus fixed overhead, commonly estimated at 2–10 MB per connection. The rule of thumb is `max_connections × per-connection cost < 60% of RAM`, leaving room for shared_buffers, the OS and the page cache.

Above `max_connections` sits `superuser_reserved_connections` (3 by default), reserved for superuser emergency connections. When ordinary connections are exhausted you can still connect as a superuser and run `pg_terminate_backend()` — that reserved path is the only way in during an incident, so never remove it.

The recommended topology is small application pools + pgbouncer + a modest `max_connections`. Ten application instances, pgbouncer `default_pool_size=40`, PostgreSQL `max_connections=200` — the database-side connection count becomes a bounded constant no matter how many client requests arrive, and memory usage becomes predictable.

One caveat often overlooked: a pool reuses connections, it does not make slow queries faster. If `maxwait` is high while `sv_active` is not saturated, some query is holding connections for too long. The fix is an index or a faster query, not a bigger pool.

# postgresql.conf
max_connections = 200
superuser_reserved_connections = 3
shared_buffers = 8GB
work_mem = 8MB

# 应用连接串统一走 pgbouncer
# postgresql://user:pass@pgbouncer-host:6432/appdb

Official References

Each command links to its official documentation below, so you can verify the latest usage and read deeper.

← Back to Guides

More guides

MySQL Slow Query Tuning: Slow Log, EXPLAIN and Indexing"The database is slow" usually means a handful of slow SQL statements. This guide offers a repeatable flow: use the slow log to circle the most expensive queries, read the plan with EXPLAIN, decide whether an index pays off, and spot the most common index-defeating patterns.Redis Slowdown Playbook: Big Keys, Blocking Commands and EvictionRedis processes commands on a single thread, so one blocking hotspot slows the whole instance. This guide follows the order "memory level → big keys → blocking commands → eviction and persistence", each step with copy-ready commands.MySQL Logical Backup and Restore: mysqldump, Faster Imports, and Recovering from Accidental DeletesExporting with mysqldump is easy; the hard parts are whether the dump restores cleanly, whether the restores fast enough, and whether an accidental delete comes back. This guide covers consistency flags, single-transaction isolation, import-acceleration knobs, and a binlog-based recovery flow.