System Design Data Access
Connection pooling
Checkout on a payment page looks like one button click. Behind it, hundreds of API pods each need a database session. If every request opens a brand-new plus , the primary spends its life connecting instead of querying.
keeps warm connections ready and caps concurrency. A stops an autoscaling fleet from exhausting max_connections on the database.
Request journey
Borrow from pool
Take a warm connection, or wait with a timeout when every slot is busy.
Run the query
Hold the borrowed connection only for the work that needs it.
Return to pool
Release promptly; forgotten checkouts look exactly like pool exhaustion.
Per-instance caps must still fit the database connection limit.
What actually happens
The checkout story above is the default without reuse. Creating a relational connection can cost about 15 ms in-region ( + + auth + process/thread setup) and hundreds of milliseconds cross-region. At 10K queries per second (QPS) without pooling, you invent 10K new sessions per second. A pool of about 50 reused connections turns that into near-zero setup overhead.
Process-per-connection databases often spend about 10 MB per backend. A thousand connections become about 10 GB of RAM before query work starts. That is why should match backend capacity, not frontend instance count.
Use : needed connections ≈ QPS × average database time. Add headroom, then divide the global database budget across instances. If autoscaling doubles pods without shrinking per-pod pools, you overload the database. A sits between many clients and few backends when the fleet would otherwise blow past max_connections.
How to choose pool sizing and placement
Once you accept that every request cannot open a fresh session, three questions decide how large each pool is and where it lives.
Start from concurrency. Use arrival rate multiplied by mean connection-hold time to estimate average busy connections in a stable workload. Include the whole transaction and any time the application holds a borrowed connection, not just one SQL statement. p95 and p99 reveal tail risk and help with load tests, but substituting p95 into Little’s Law does not turn it into an exact capacity formula.
2. Cap to the database budget. Reserve sessions for admin and replication; split the rest across app instances, or insert a when instance count would exceed the limit.
3. Separate workloads. Give reads, writes, and background jobs different caps so a backfill cannot starve checkout traffic that users are waiting on.
Simple rule of thumb
Prefer a small, well-monitored pool. A common starting heuristic is roughly (cores × 2) + effective spindles for a single relational instance: often tens of connections outperform hundreds. Fix slow queries before growing the . In simple terms: reuse connections, bound concurrency, and never let autoscaling invent more sessions than the database can hold.
From problem to mechanism
Here is the payment checkout path again, slowed down so you can see where naive per-request connections fail and what pooling changes.
Problem. Hundreds of API pods each need a database session for checkout. Opening a new and on every request burns tens of milliseconds in-region and far more across regions, before any query runs.
Naive system. Each request creates a session. At 10K QPS that is 10K handshakes per second, and process-per-connection backends burn about 10 MB each until RAM is gone (see Numbers to know below).
Break it. The primary hits max_connections, new checkouts wait or time out, and autoscaling adds more pods that each open more sockets. Slow queries pin connections longer, so arrives even faster.
Mechanism. keeps a bounded set of warm connections, hands one out with a wait timeout, and returns it after the query. Size the pool with and a fleet-wide budget; add a when many clients must share few backends. Prefer for multiplexing unless you need session-scoped features.
Pool sizing playbook
The mechanism above says size from concurrency and a global budget. This is the math and the workload split in practice. Common starting heuristic: connections ≈ (core_count × 2) + effective_spindle_count. Beyond optimal, connections compete for CPU and I/O; context switching dominates.
Numbers to know
- About 15 ms same-datacenter setup; 300 ms or more when the path is cross-region.
- Fifty continuously busy connections can support 10K queries/s only if average connection occupancy is about 5ms per query; longer holds need more concurrency or less load.
- About 10 MB per backend process; one thousand connections is roughly 10 GB of RAM.
- Example: 2,000 QPS × 0.012 s average DB time = 24 busy; add 50% headroom → 36.
- Database budget example: 600 max safe, reserve 100, 500 for apps; 50 instances → 10 each.
needed_connections ≈ QPS × average_DB_time_seconds
Example write path:
2,000 QPS × 0.012 sec average DB time = 24 busy connections
Add 50% headroom -> 36
If p95 DB time is 50 ms, queue timeout must protect users
and the database from long waits.database max safe connections = 600
reserve for admin/replication/migrations = 100
available to app = 500
50 app instances means:
per_instance_pool_cap = floor(500 / 50) = 10
If autoscaling grows to 100 instances:
100 × 10 = 1000 connections -> database overloadRead pool
Size from read QPS and latency. Keep read-your-writes on the primary or use session-aware routing.
Write pool
Keep smaller and stricter. Long transactions hold locks and should time out quickly.
Background pool
Give reports and backfills a separately bounded concurrency budget so they cannot consume every session needed by checkout. A queue can hold batch work until capacity is available, but interactive requests need a short pool-acquisition deadline that fits the user’s overall timeout. If acquisition expires, return a controlled overload result rather than waiting indefinitely and retaining request memory. Monitor acquisition time separately from SQL execution time to identify the actual bottleneck.
Interview trap
Increasing is not a fix for slow queries. After the database saturates, a bigger pool only creates more waiting, locks, and context switching.
Pooler modes
Feature support is version-sensitive. The PgBouncer feature matrix distinguishes protocol-level prepared plans from SQL PREPARE and distinguishes LISTEN from NOTIFY; check the exact feature before deciding that transaction pooling is compatible.
When a sits in front of the database, the mode decides how aggressively it multiplexes clients onto backends. Expand each mode for when to use it and what breaks.
(recommended default)
In transaction pooling, one backend connection is assigned for a transaction and returned at COMMIT or ROLLBACK. A client that pauses between transactions no longer occupies a backend during that pause, allowing many mostly idle clients to share fewer database sessions. The multiplexing ratio depends on how much time clients spend inside transactions; a long transaction pins its backend and removes the sharing benefit for that interval.
Limitations:
- Prepared statement support depends on the protocol, pooler version, and configuration; current PgBouncer can track protocol-level prepared statements when enabled, while SQL PREPARE has different restrictions.
- No session-level SET commands that expect state to stick around.
- Persistent LISTEN subscriptions and session advisory locks require session affinity. NOTIFY and transaction-scoped features have different compatibility rules.
Statement pooling
[connection_pooler]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 50
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
server_lifetime = 3600Client pools, HTTP reuse, and production shapes
App-side pools need timeouts and leak detection, and the same reuse idea shows up on HTTP between services. Two common architectures show why a pooler becomes mandatory at scale.
Client pool configuration
maximumPoolSize: 10
minimumIdle: 10
connectionTimeout: 30000
idleTimeout: 600000
maxLifetime: 1800000
validationTimeout: 5000
leakDetectionThreshold: 60000
connectionTestQuery: "SELECT 1"Leak detection
Record a warning when a connection has been borrowed longer than the expected transaction envelope and include the acquisition stack or trace identifier. The threshold is a diagnostic choice; some legitimate operations are long, and a warning is not proof of a leak. Always return connections on success, exception, cancellation, and timeout. Do not hold a database connection while waiting for an unrelated payment HTTP call unless the transaction design truly requires it.
Metrics
Watch active, idle, pending, and timeout counts. Pending > 0 for tens of seconds means is near.
HTTP pooling and multiplexing
HTTP/1.1
Keep-alive reuses TCP sequentially. Needs a larger per-host pool; one slow response blocks the connection.
HTTP/2
many streams on 1–2 connections. Pool mainly for redundancy.
HTTP/3
QUIC streams avoid TCP head-of-line blocking; 0-RTT resumption helps mobile paths.
Production architectures
Sharded photo-feed service
Each worker pools per shard; a in sits in front of each primary. Connection overhead is a first-class optimization target at 100K+ queries/sec per shard.
Serverless + relational DB
Each cold invocation may open a new socket. A managed connection proxy multiplexes thousands of function clients into a small backend pool.
What still breaks, and what to watch
Putting the pieces together, pooling removes handshake storms, but the design can still fail when leaks, slow queries, or fleet growth ignore the global budget (see Numbers to know above).
What to watch
- Pending wait time and timeout counters rising; early signal that exhaustion is near.
- Active versus idle connections, so leaks show up as active stuck high for too long.
- Per-instance pool caps times pod count versus database max, after every autoscaling event.
- p95 query time climbing; growing the pool then only multiplies locks and context switches.
Still fails when…
- Long transactions pin connections; checkout waits while a batch holds locks open.
- Stale sockets survive failover and return errors until max lifetime or health checks recycle them.
- Autoscaling doubles pods at a fixed per-pod cap and blows past the database budget.
Trade-offs
- Larger pools cut wait time until the database saturates and slows everyone.
- Smaller pools protect backends but raise application waiting under burst traffic.
- multiplexes well but breaks session-level features callers may need.
Choosing and explaining connection pooling
A complete explanation connects the mechanism to a requirement. Describe what improves when you introduce it, which new failure cases become possible, and how you would know that the trade-off is worthwhile.
Explain the core idea
Pools reuse expensive connections and cap concurrency so backends are not overwhelmed.
Pool size should match backend capacity, not frontend instance count.
Use multiplexing proxies like connection pooler when many app instances would create too many DB sessions.
When it belongs
Each request would otherwise open TCP, TLS, auth, or database sessions.
The database has a low practical connection limit.
Serverless or autoscaled apps fan out too many clients.
How it fails
Pool exhaustion causes request queueing and timeout cascades.
Long transactions pin connections.
Stale connections survive failover and return errors.
Trade-offs to weigh
Larger pools reduce wait time until the database saturates.
Smaller pools protect backends but increase application waiting.
Transaction pooling improves multiplexing but breaks session-level features.
Common misunderstandings
Creating one large pool per service instance without global budgeting.
Ignoring max lifetime, idle timeout, and health checks.
Using pooling to hide slow queries instead of fixing them.
Try it: connection pool exhaustion
Borrow and release pooled database connections. Saturate the pool to grow a wait queue, then observe timeouts when demand exceeds max waiters.
Conn 1
Idle
Conn 2
Idle
Conn 3
Idle
Conn 4
Idle
- Pool size
- 4
- Idle
- 4
- Wait queue
- 0
- Exhausted
- No
Borrow connections from the pool. When all are busy, new callers wait or time out once the wait queue fills.
Going deeper
Connection pooling
The mechanism and its boundary
A connection pool bounds expensive backend connections and reuses them across requests. It must be sized from concurrency and service time, not guesswork, or it becomes either a bottleneck or a database overload tool.
Pool sizing feedback
Request arrives
Wait for slot
Run query
Release connection
Work through the sizing assumptions
When it belongs
Creating connections is expensive or backend connection count is limited. App instances outnumber safe database sessions. You need backpressure instead of unlimited concurrent queries.
Assumptions that matter
Pool size is coordinated across all app instances. Read and write paths may need separate pools. Queue wait, active count, and saturation are monitored.
Easy mistakes
Giving every application instance the full database connection limit multiplies the intended cap across the fleet, so reserve a total budget before dividing it among pools. A transaction can occupy a connection while doing no database work, so measure checkout duration and idle-in-transaction time as well as query execution time. An unbounded pool wait queue hides overload inside the application; impose a queue limit, an acquisition deadline, and a defined rejection response.
How it fails
Connection leak drains the pool. Slow queries occupy all connections and block cheap queries. Autoscaling app nodes multiplies pool capacity beyond database limits.
Trade-offs to name
A larger pool permits more work in parallel until the database’s CPU, I/O, or locking becomes the limit, after which additional connections can make latency worse. A smaller pool protects that shared backend by queueing callers, but the queue must be bounded so a burst cannot consume all application memory or deadline budget. Short acquisition timeouts release overloaded callers quickly while rejecting some bursts that might otherwise recover, so align the timeout with the useful lifetime of a request.
Wrap-up
You have seen the cost of fresh sessions, the sizing math, and the pooler modes. Follow the primary sources for the tooling details behind the examples above.
Topic-specific references
- PgBouncer Docs: Features : session vs transaction pooling modes used in the accordion above.
- PostgreSQL Docs: Connection Settings :
max_connectionsand why pools exist in the first place. - HikariCP: About Pool Sizing : the (cores × 2) + spindles heuristic behind the playbook numbers.
Key takeaway
Connection pooling is how high-QPS systems stop drowning in handshakes. Keep pools small and monitored, budget connections fleet-wide, prefer transaction-mode poolers when you need multiplexing, and treat rising wait time as a signal to fix queries, not only to raise the cap.
Practice and further reading
Use these exercises to connect the lesson to a design decision. State your assumptions, trace what happens to one request, and explain the rule that must remain true even during a failure. That rule is the design's correctness invariant.
Focus for this lesson: Size bounded pools from database capacity and queue wait, avoid per-instance explosion, clean session state, handle leaks, proxies, and serverless concurrency.
Explain the decision
Why does increasing every application pool often reduce throughput?
Read the reasoning
Aggregate connections overwhelm database memory/schedulers/locks; queueing moves into the database and tail latency rises.
Apply it to a production scenario
Autoscaling doubles app replicas and database connections double instantly. What protects the database?
Read one approach
Use a global connection budget, smaller dynamic pools, a multiplexing proxy, warm-up, admission, and scale based on queue wait.
Work through the numbers
A database safely supports 1,200 connections across 80 app instances with 20% reserve. Pool cap per instance?
Read the worked calculation
Usable 960 / 80 = 12 connections per instance, adjusted for unevenness and administrative access.
Reason through a failure
What must a returned pooled connection guarantee?
Read the recovery reasoning
No open transaction, tenant/session variable, temp state, or credentials leak to the next borrower; validate/reset or discard it.
Sources and further study
Continue with Database Connection Patterns.