PgBouncer is a connection pooler placed between an application and PostgreSQL when application connection demand is far higher than useful database concurrency. Rather than letting every client create a PostgreSQL backend process, it accepts many application connections, permits a defined number through, and queues the remainder briefly. This trades some application-side waiting for database headroom. section 6.3
The tempting mistake is to raise PostgreSQL connection capacity until it matches every client. PostgreSQL uses a process-per-user model: each client has a backend process, coordinated through shared memory and semaphores. Thousands of processes add operating-system scheduling, accounting, IPC, and context-switching overhead; excessive concurrency can therefore reduce throughput and sharply increase latency. section 6.3 section 6.4.2
Table 6.1 Test results showing TPS and latency with various numbers of clients
|
Number of clients
|
TPS
|
Latency average
|
|---|---|---|
|
2,000
|
352.1
|
5,535.267 ms
|
|
1,500
|
506.8
|
2,869.986 ms
|
|
1,000
|
586.1
|
1,694.616 ms
|
|
500
|
628.4
|
794.688 ms
|
|
250
|
635.7
|
392.554 ms
|
|
125
|
556.9
|
224.411 ms
|
|
63
|
613.0
|
102.758 ms
|
|
32
|
572.3
|
55.907 ms
|
|
16
|
301.8
|
53.009 ms
|
Use the benchmark results to see why more clients are not automatically more work: throughput peaks at lower client counts while latency rises dramatically at 2,000 clients. Pooling constrains admission to a level the server can sustain instead of treating max_connections as an application concurrency target. section 6.3
Transaction Mode is especially useful when many connections are mostly idle. An unused connection can be handed to another transaction that needs it, avoiding a large population of idle backend processes and reducing the risk that an application suddenly activates thousands of already-open sessions during a traffic spike. This is preferable to relying on idle_session_timeout, which can surprise clients that expect their sessions to remain open. section 6.4.2
Investigate pooling when connection pressure appears as a production symptom:
- Monitor pg_stat_activity to compare active database work with the number of connections requested; real in-database concurrency may be much lower than the connection count. section 6.3
- Treat frequent LWLock wait_event_type activity as evidence that excessive connection-related contention may be harming performance. section 6.3
- Investigate FATAL messages about unavailable connection slots before simply increasing limits; they can indicate either legitimate demand or excessive application requests. section 7.3.1
Many clients, few active tasks
An application may hold 5,000 connections while only 15 to 30 tasks are concurrently active inside PostgreSQL. PgBouncer can keep the application-facing connection count high while limiting database-side clients to a sustainable level, making surplus requests wait rather than creating thousands of backend processes. section 6.3
NOTE: Pooling addresses unnecessary session and process overhead, not every performance failure. Idle-in-transaction sessions and long-running queries can still block work and delay autovacuum; query slowness still calls for EXPLAIN-based diagnosis. section 6.13
