Data AccessGENERALDATABASE-SPECIFICRUNTIME-SPECIFICCLOUD-SPECIFIC
Connection Pools
The pool is what actually bounds your concurrency: 1,000 requests against 20 connections means 20 running and 980 waiting, silently, until they time out.
The requirement, the obvious build, and why it breaks
Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.
The question
How many database queries can my service really run at once, and what happens to the rest?
The requirement
The service must handle a traffic spike without falling over. Nobody has said anything about connections, because nobody configured any.
The obvious build
The framework created a pool with a default size. Requests come in, queries run, it works. Connection management is a solved problem handled by the library.
Why it breaks
At 1,000 concurrent requests against a pool of 20, twenty queries are executing and 980 requests are waiting for a connection. Not failing — waiting. The database is idle at 20 connections and the service looks broken.
How it breaks in production
- At 1,000 concurrent requests against a pool of 20, twenty queries are executing and 980 requests are waiting for a connection. Not failing — waiting. The database is idle at 20 connections and the service looks broken.
- Latency rises long before any error appears, because queueing is invisible: the request is in your process, holding memory and a socket, doing nothing.
- When the wait finally exceeds the acquire timeout, every queued request fails at once, so the service goes from "slow" to "all errors" with no intermediate state (Connection Pool Exhaustion).
- Ten instances with a pool of 20 each is 200 connections to one database. Postgres defaults to
max_connections = 100, so the eleventh instance cannot connect at all — and the failure is at start-up, during a deploy. - One slow query holds its connection for its whole duration, so a single unindexed statement removes capacity from every other endpoint in the service.
Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale
What is actually happening
- Opening a database connection is expensive: a TCP connection, a TLS handshake, authentication, and on Postgres the fork of a dedicated backend process per connection. A pool keeps them open and hands them out.
- A pool is a bounded resource with a queue.
acquire()returns a connection immediately if one is free, and otherwise blocks the caller until one is released or an acquire timeout fires. - That makes the pool a concurrency limiter, and usually the only one in the service. Your effective query concurrency is the pool size, whatever the runtime's request concurrency is.
- Throughput follows from that directly: if the mean query occupies a connection for time T, a pool of N connections can complete at most N/T queries per second. Doubling the pool doubles the ceiling only while the database can keep up; past that, more connections make everything slower.
- Little's law gives the queue: the number of requests waiting is the arrival rate multiplied by the time each spends waiting. Once arrival rate exceeds N/T, the wait grows without bound (Little's Law as Working Intuition).
- A connection is held for as long as the code holds it — not just for the query. A transaction spanning an HTTP call holds a connection for the duration of that call (External Calls Inside a Transaction).
1,000 requests, 20 connections
SIMPLIFIEDNumbers chosen to make the arithmetic legible, not measured. The shape — a hard cap at pool size with unbounded queueing behind it — is what transfers; the right pool size for your database is an entirely separate question.
Hold the arithmetic in mind, because everything else follows from it. A thousand requests arrive concurrently. The pool has twenty connections. Twenty queries are running. Nine hundred and eighty requests are sitting in a queue inside your process, each one holding a socket, a stack and whatever memory it has already allocated, doing nothing at all.
Nothing has failed. There are no errors. The database CPU is low, because it is executing twenty queries. Every dashboard that looks at the database says the database is healthy — and it is. The bottleneck is the doorway, not the room.
Then the acquire timeout fires. Not for one request: for the whole backlog, more or less at once. The service goes from elevated latency to a wall of errors with no gradual middle, which is why pool exhaustion feels so sudden in an incident.
UserLLMAgentToolDataDecisionHumanGuardrail
What decides the size
Pool sizing is not a tuning knob you turn until the graph looks nice. It is arithmetic with four inputs, and three of them are outside the application.
Start from the database: how many connections can it serve well? Divide by everything that connects — every application instance, every worker process, every cron job, every human with psql open. Leave headroom for a deploy that briefly runs old and new instances at the same time, and for the administrative connections you will need during the incident.
Only then ask whether that number gives you the throughput you need. If N/T is below your required query rate, the answer is usually to make T smaller — index the query, remove the round trips, stop holding connections across external calls — rather than to make N bigger.
| Input | Where it comes from | What it constrains |
|---|---|---|
| Server connection limit | Database configuration and memory | The hard ceiling for every client combined |
| Instance count (incl. deploy overlap) | Your deployment and autoscaling | Divides the ceiling; doubles briefly during rolling deploys |
| Workers, jobs, migrations, humans | Everything that is not request traffic | Must be subtracted, and is routinely forgotten |
| Mean connection hold time (T) | Query time plus anything else you hold it across | Throughput ceiling N/T; the input you can actually improve |
| Acquire timeout | Your choice | How long a starved request waits before failing |
| Statement timeout | Database or driver | The upper bound on T when a query goes wrong |
How pools fail, and what each failure looks like
These are the shapes worth recognising from a dashboard at 3am. The distinguishing question in almost every row is the same: is the connection *busy*, or is it *held by code that is not using it*?
Pool failure modes
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| Traffic spike beyond N/T | Latency climbs smoothly, then all requests 5xx at once | Wait queue grows until acquire timeouts fire together | Shed load at the edge, cap per-endpoint concurrency, reduce T (Backpressure) |
| One unindexed query deployed | Unrelated endpoints slow down | Long-running queries occupy connections, cutting effective N for everyone | Statement timeout as a backstop; index the query (Should I Add an Index?) |
| Error path that never releases | Capacity falls steadily over hours; restart fixes it | Connection leak — acquired and not returned on a throw | Always release in a finally block or a scoped helper; alert on in-use never returning to baseline |
| External API call inside a transaction | Pool saturated while the database is idle | Connections held for the duration of an HTTP call | Move the call outside the transaction (External Calls Inside a Transaction) |
| Autoscaler adds instances | New pods crash-loop with "too many connections" | Per-instance pools multiplied past the server limit | Shrink per-instance pools or put a connection proxy in front |
| Database failover | A burst of errors, then recovery | Pool holds sockets to a server that is gone | Validate connections on checkout; keep max lifetime bounded so connections rotate |
| Request needs two connections at once | Total deadlock at high concurrency, no CPU usage | Every request holds one and waits for another from the same pool | Pass the connection down instead of acquiring a second (The Four Conditions) |
| Health check shares the request pool | Instances removed from the load balancer under load, then the rest fall over | Saturation fails the check; traffic shifts to remaining instances | Separate pool or a liveness check that does not touch the database (Health Checks: Startup, Readiness, Liveness) |
How to build it
Most important first.
- Size the pool against the database's capacity, not the application's request concurrency. Total connections across every instance, every worker and every background process must stay comfortably under the server's limit, with headroom for administrative sessions.
- Set an acquire timeout that is shorter than your request timeout, so a starved request fails as a fast, attributable error rather than hanging.
- Set a statement timeout on the database side too. An acquire timeout bounds waiting; only a statement timeout bounds a query that is already running.
- Set an idle-in-transaction timeout so a connection left in an open transaction by a bug is reclaimed instead of leaking capacity permanently.
- Hold connections for the shortest possible span: acquire late, release early, and never span an external call (External Calls Inside a Transaction).
- Give background workers and migrations their own pools with their own limits, so a batch job cannot starve request traffic — the bulkhead idea applied to connections (Bulkheads).
- Use a connection proxy (PgBouncer and equivalents) when the number of application instances makes per-instance pools unworkable — many short-lived clients multiplexed onto few server connections.
- Export pool metrics before you need them: in-use, idle, waiting, and acquire wait time. Waiting count is the number that predicts an incident.
What can go wrong
Failure modes
- Pool exhaustion under load: every connection busy, a growing wait queue, latency climbing, then a cliff of timeout errors (Connection Pool Exhaustion).
- Connection leak: code that acquires and does not release on an error path. Capacity shrinks one connection at a time until the service dies hours after the deploy that caused it.
- A deadlock of your own making: a request holds one connection and needs a second (nested transaction, or a helper that opens its own) while every other request does the same. Nobody can proceed.
- Database connection limit reached, so new instances fail to start — an autoscaling event turns a slow service into a failing one.
- Stale connections after a database failover or an idle-timeout on a proxy: the pool hands out a socket the server has already closed, and the first query fails.
- Serverless: each instance has its own pool and instances scale independently, so connection count tracks concurrency rather than fleet size (Serverless and Database Connections).
- An oversized pool: more connections than the database has cores or memory for, so every query slows down and the fix looks like it made things worse.
What can race
- The classic self-inflicted deadlock: every request holds connection A and waits for connection B from the same pool. With a pool of N and N requests each needing two connections, nothing can complete (The Four Conditions).
- Acquire order matters when a request needs connections from two pools — primary and replica, or database and cache. Consistent ordering avoids a cross-pool deadlock (Lock Ordering).
- Health checks and application traffic compete for the same pool, so a saturated pool fails the health check and the instance is removed — which sends its traffic to the other instances and saturates their pools (Cascading Failure).
Security
- Connection credentials are secrets. They belong in a secret store, not in an image or a config file, and they should be rotatable without a code change (Secrets Are Not Configuration).
- Use least privilege for the runtime user: no DDL, no access to tables the service does not use (Database Privileges and Blast Radius).
- Require TLS to the database, including inside a private network. "It is internal" is not an encryption strategy.
- A connection string in an error message or a startup log is a credential leak. Redact before logging (Secrets in Logs).
- Pool exhaustion is a denial-of-service target: any endpoint whose query cost is controlled by the caller can be used to occupy every connection. Bound cost server-side (Resource Limits).
Misreads
- "More connections means more throughput." Past the point where the database can execute them in parallel, more connections means more context switching and more contention — less throughput, not more.
- "The database is slow." Check the pool first. Queueing to *reach* the database is the more common cause and produces identical dashboards (Why Is My API Slow?).
- "We are not getting errors, so the pool is fine." Requests queue silently before they fail. Waiting count, not error rate, is the signal.
- "The framework picked a sensible default." A default was picked without knowing your instance count, your database, or your query mix — three of the four inputs to the answer.
- "Async runtimes do not need a pool." An async runtime removes the thread-per-request limit and therefore removes the accidental concurrency cap, which makes the pool the *only* limit and more important, not less.
- "Idle connections are wasted." Idle is the pool working. Zero idle connections under load means you are at the ceiling.
Operating it
How you see it in production
- Waiting count and acquire wait time are the leading indicators. They move before latency does and long before errors do (Connection Pool Saturation: Waiting in Front of an Idle Database).
- In-use versus pool size as a utilisation ratio. Sustained saturation means the pool is your bottleneck, not the database.
- Connection count as seen from the database, summed across instances, against the server limit. The application cannot see this and the database can.
- A widening gap between request duration and query duration is queueing on the pool, and it looks exactly like "the database is slow" from a dashboard that only measures queries (Saturation: The Reading Utilization Cannot Give You).
- Alert on waiting count above zero for a sustained period. A pool that never queues is sized correctly for current load; one that queues constantly is a countdown.
What changes at 10x and 100x
- At 10x requests, nothing about the pool changes — and that is the point. It caps concurrency at N regardless of what arrives, converting extra load into queueing rather than into database overload. The pool is a protection mechanism as much as a resource.
- At 10x instances, total connections multiply by 10 and the database limit becomes the binding constraint. This is when a connection proxy stops being optional.
- Read replicas add a second pool with a second limit, and routing decides which one saturates (Read Replicas From the Application).
- The counter-intuitive result at scale: reducing pool size can increase throughput, because a database with fewer concurrent queries finishes each one faster and contends less (Low CPU, High Latency: Lock Contention).
What this costs
- A small pool means queueing under load; a large one means overloading the database and slowing every query. There is no size that avoids both — you are choosing where the backpressure lands.
- A short acquire timeout converts hangs into errors. That is better for the caller and worse for the success rate, and it is the right trade because a timed-out request stops consuming resources.
- A connection proxy adds a hop, an operational component and transaction-mode restrictions (session-level features stop working) in exchange for surviving many application instances.
- Separate pools per workload waste some capacity by construction. Isolation always does; that is what makes it isolation.
Where this applies
Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.
- GENERALBounded pool, queue behind it, and the N/T throughput ceiling are true of every pooled client — databases, HTTP clients and thread pools alike.
- DATABASE-SPECIFICPostgres allocates an operating-system process per connection, so each one costs memory and idle connections are not free;
max_connectionsdefaults to 100 and raising it far is a real cost, which is why PgBouncer is common. MySQL uses a thread per connection with a thread cache and tolerates higher connection counts more cheaply, so the same pool sizing advice does not transfer between them. - RUNTIME-SPECIFICA thread-per-request runtime has an implicit concurrency cap at its thread count, so an oversized pool is harmless there. Node and other event-loop runtimes accept thousands of in-flight requests with no such cap, which is why the pool is the only limit and why exhaustion arrives faster and harder.
- CLOUD-SPECIFICServerless functions scale instances independently and each holds its own pool, so connection count follows concurrency rather than a fixed fleet size. Providers offer a proxy for exactly this reason; a normal pool sized for a fixed fleet is the wrong model there (Serverless and Database Connections).
Where the depth lives
This domain teaches the application-side mechanism and hands the rest off.