Decision: How to set up connection pooling?
Every database connection carries memory and scheduling cost; an uncontrolled growth in connection count slows the server down or even brings it down. A connection pool solves this by reusing a small number of persistent connections. This tree guides decisions on where the pool sits, which mode (session/transaction/statement) to use, and the right size, and stresses why an external pooler is mandatory in serverless environments.
How does your application architecture connect?
The pooling decision starts with the architecture. Is it a single long-lived application process (classic server), many independent processes/instances (microservices, many pods), or serverless/Lambda functions that are born and die with every request? Each requires a different pool placement.
- One/few long-lived application processes
- Many processes/instances/pods connect to the same DB
- Serverless / Lambda / short-lived functions
- First show me how to choose the right pool size
How should the middleware pooler mode be chosen?
With many clients, a middleware pooler (PgBouncer, ProxySQL) keeps the connection count on the DB low. But the pool mode depends on the features the application uses: do you rely on session-bound state (prepared statements, temporary tables, SET, advisory locks, session-level features), or does everything complete within a single transaction?
- I depend on session state (SET, temporary tables, session prepared statements)
- Transactions are short and independent; I keep no session state
Recommendation: In-application connection pool
For one/few long-lived processes, an in-application pool library (HikariCP, the built-in pool of Go's database/sql, .NET connection pooling) covers most needs. Connections are opened once and reused over and over.
Recommendation: Size the pool correctly
The pool should be neither too small (requests wait) nor too large. Too many connections create CPU context switching and lock contention and reduce throughput. The size is not a number to grow indefinitely, but a small number based on cores and I/O.
Recommendation: Session pool mode
In session mode, a client connection is pinned to a backend server connection until its session ends. Session-level state (SET, temporary tables, advisory locks, session prepared statements) is preserved, but connection sharing is minimal.
Recommendation: Transaction pool mode
In transaction mode, a server connection is lent to the client only for the duration of a transaction and returns to the pool when the transaction ends. Thus thousands of clients can work with very few real connections on the DB; it is the most common high-scale mode.
Recommendation: An external pooler is mandatory for serverless
Serverless/Lambda functions scale rapidly and each instance opens its own connection; an in-application pool does not help here because instances are short-lived and share nothing. When hundreds/thousands of instances spring up suddenly, the DB connection limit is blown. The solution is an external connection pool in front of the DB.