scalingestablished · low operational burden

Connection Pooling

Also known as: db-connection-pool, pgbouncer-proxy

Intent

Reuse a bounded set of long-lived database connections across many requests to avoid connection setup cost and server overload.

Problem

Opening a database connection involves network handshakes, authentication and often a dedicated server process, which is slow and memory heavy. Without limits, traffic spikes or many app instances open thousands of connections and exhaust the database. Each request should not pay setup cost or compete unboundedly.

Forces

•Connection setup is expensive in latency and server memory
•Databases handle a limited number of concurrent connections well
•Many application instances multiply connection counts
•Requests must wait fairly when the pool is exhausted

Solution

Overview

A connection pool keeps connections open and lends them out. The database sees a small steady number of sessions, and requests skip the handshake. More connections do not mean more throughput; past a point they reduce it.

How it works

Application-level pool settings and the proxy layout.

# HikariCP style
maximumPoolSize=10
connectionTimeout=2000   # ms to wait for a free connection
maxLifetime=1800000      # recycle before server or LB closes it

200 app pods x 10 = 2000 connections  -> too many
pods -> PgBouncer (transaction mode, 100 server conns) -> Postgres

A common starting size is roughly twice the CPU cores of the database server, shared across all clients.

Queueing theory explains the sizing. A database with sixteen cores completes a bounded number of queries concurrently, and extra connections only add context switching and lock contention. Little law says concurrency equals throughput times latency, so a service handling five hundred queries per second at ten milliseconds each needs about five connections in flight. Pools should therefore be small, with waiting requests time-limited, so overload turns into fast errors rather than a growing queue that hides the problem.

Pitfalls

Sizing the pool per instance without multiplying by instance count.
Forgetting to release connections on error paths, causing leaks.
Holding a connection while calling slow external services.
Using prepared statements or session variables through a transaction-mode pooler.

Key takeaways

Total connections across all clients must fit the database.
Always bound the wait and fail fast when the pool is empty.
Keep transactions short so connections turn over quickly.
Use a pooling proxy for serverless and large fleets.

✓ When to Use

•Any service issuing frequent short database queries
•Serverless or highly replicated apps that could exceed connection limits
•Databases with costly per-connection resources

✗ When Not to Use

•Single long-running batch jobs with one connection
•Workloads relying on session state that a transaction-mode pooler would break
•Databases designed for connectionless HTTP access

Pros

+Removes per-request connection setup latency
+Protects the database from connection storms
+Predictable resource use

Cons

−Pool exhaustion makes requests queue or fail
−Session features such as temp tables and prepared statements may break behind a proxy
−Sizing needs measurement

Connection Pooling

Intent

Problem

Forces

Solution

Overview

How it works

Pitfalls

Key takeaways

✓ When to Use

✗ When Not to Use

Pros

Cons

Cost Profile

Infrastructure

Operational

Cognitive

Failure Modes

Real-World Examples

Alternatives

Related Patterns

Competency Domains