A connection pooler sits between an application and PostgreSQL, keeping a bounded set of database connections and assigning them to client work. It trades some application-side waiting for healthier database concurrency: many application connections can be accepted while only a configured number reach PostgreSQL at once. section 6.3 section 6.13
The tempting mistake is to equate more open connections with more parallel work. PostgreSQL uses a process-per-user model, so each client session requires a backend process. Too many processes add operating-system overhead and CPU context switching; excessive concurrency can instead reduce throughput and increase latency. A pooler makes surplus requests wait rather than immediately creating more database work. section 6.3 section 6.3
Many connections, little real concurrency
An application may hold 5,000 connections while only 15 to 30 tasks are actively running in the database. Put PgBouncer between the application and PostgreSQL, set the allowed database-side concurrency, and observe pg_stat_activity to distinguish active database work from connections the application merely wants to keep open. section 6.3 section 6.3
Idle sessions are not free. PostgreSQL must examine open connections when compiling MVCC snapshot information, making snapshot-taking cost proportional to the total connection count. Large language- or framework-side pools can therefore slow later connection opens even when most sessions are idle. Prefer an intermediary pooler over retaining thousands of database sessions to absorb request spikes. section 6.4.2 section 6.4.2
Treat pooling as an operational control, not a substitute for diagnosing workload behavior:
- Restrict database-side clients and allow application requests to queue; increase application timeouts if waiting is expected. section 6.3
- Monitor pg_stat_activity for LWLock wait_event_type activity and for actual database concurrency. section 6.3
- Investigate a pooler when logs report insufficient connection slots, while first determining whether demand is legitimate or excessive. section 7.3.1
- Avoid idle-in-transaction sessions and long-running queries: they can block other queries and delay or block autovacuum. section 6.13
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
|
The client-count results show that adding clients can lower TPS while sharply increasing latency, supporting a bounded-concurrency pool rather than an ever-larger connection count. section 6.3
