How Database Connection Pooling Keeps an API Responsive

Your API answers in 40 milliseconds on a quiet afternoon, then a traffic spike hits and requests start timing out while the database CPU sits at 30 percent. The database is not overloaded. It is drowning in connections. Database connection pooling is usually the fix, and getting its settings wrong is one of the most common reasons a service that looked fine in testing falls over in production.

This article explains what a pool does, how to size one with actual arithmetic instead of guesswork, and the failure modes worth watching for.

Why opening a connection per request is expensive

Opening a new connection to PostgreSQL or MySQL is not a cheap function call. The client and server perform a TCP handshake, often a TLS handshake, authentication, and session setup. On PostgreSQL, each connection also gets its own backend process on the server, with memory overhead commonly in the range of 5 to 10 MB once it has done real work.

Measured end to end, establishing a connection over TLS within the same data center typically costs somewhere between 5 and 30 milliseconds. If your query itself takes 3 milliseconds, the connection setup can be most of the request time. Worse, at 500 requests per second you are asking the database to create and tear down 500 processes every second. That is the scenario where CPU looks fine but latency climbs: the server spends its time on bookkeeping, not queries.

How database connection pooling works

A pool opens a fixed set of connections once, keeps them alive, and lends them to requests as needed. The flow for each request looks like this:

The handler asks the pool for a connection.
If one is idle, the pool hands it over immediately.
If all are busy and the pool is below its maximum size, it opens a new one.
If the pool is at maximum, the request waits in a queue until a connection is returned or a timeout expires.
When the handler finishes, it returns the connection to the pool instead of closing it.

Pools come in two broad forms. Application side pools live inside your process, such as HikariCP in Java, the pool built into SQLAlchemy, node-postgres Pool or database/sql in Go. External poolers such as PgBouncer or a managed proxy sit between many application instances and the database, multiplexing thousands of client connections onto a few dozen real ones.

Sizing a pool with arithmetic, not instinct

The instinct is to set the pool large. If 10 connections are good, 100 must be better. That instinct is usually wrong, and it helps to see why with numbers.

Start with Little's Law: the number of connections in use equals the request rate multiplied by the time each request holds a connection. If you serve 400 requests per second and each holds a connection for 15 milliseconds, you need 400 times 0.015, which is 6 connections on average. Add headroom for bursts and you might set a maximum of 10 to 15 per instance.

Then check the database side. The total across every instance must stay under what the database handles well.

Setup	Instances	Pool max per instance	Total connections
Small service	3	10	30
Autoscaled at peak	20	10	200
Autoscaled, oversized pools	20	50	1,000

The last row is a real outage pattern. PostgreSQL's default max_connections is 100. Autoscaling adds instances during a spike, each tries to open its 50 connections, and the database starts refusing new ones exactly when traffic peaks.

The counter-take: a bigger pool often makes things slower

When latency rises, the common advice in incident channels is to raise the pool size. Sometimes that helps. Often it makes things worse.

A database server has a limited number of CPU cores and a limited disk. If it has 8 cores, it can run roughly 8 queries truly in parallel. Push 200 active queries at it and they compete for CPU, locks and cache, so each one runs slower. A frequently cited starting formula from the HikariCP project is core count times two, plus the number of effective disk spindles. For an 8 core database on SSD, that suggests something like 16 to 20 active connections in total across all clients.

Fewer connections with a short queue in front usually produce lower overall latency than many connections fighting each other. If requests are queuing for a connection, the first question is why each request holds one so long, not how to add more. Slow queries, a missing index, or an N+1 query pattern that runs 50 queries per request will exhaust any pool size you pick.

Failure modes worth monitoring

Connection leaks

Code that borrows a connection and fails to return it on an error path slowly drains the pool. Everything works for hours, then every request times out at once. Always release connections in a finally block or use your language's scoped construct. Most pools offer a leak detection threshold, for example logging a stack trace if a connection is held longer than 10 seconds.

Long transactions

A transaction that waits on an external HTTP call while holding a connection ties it up for the full network round trip. Make the external call first, then open the transaction.

Stale connections

Firewalls and load balancers silently drop idle TCP connections, often after 5 to 15 minutes. Set the pool's maximum lifetime a little below that, around 10 minutes, and enable a cheap validation check.

Wait timeouts that are too long

If a request waits 30 seconds for a connection, the caller has usually given up long before. A timeout of 1 to 3 seconds that returns a clear 503 lets clients retry sensibly. Pair it with rate limiting so a flood of retries does not pile onto the same queue.

A practical starting configuration

Maximum pool size: calculate from Little's Law plus 50 percent, then confirm the total across your maximum instance count fits under the database limit.
Minimum idle: equal to maximum for steady traffic, which avoids opening connections during spikes.
Connection timeout: 2 seconds.
Max lifetime: 10 to 30 minutes, shorter than any network idle cutoff.
Metrics to graph: active connections, idle connections, pending requests and wait time. Pending requests above zero for more than a few seconds is your early warning.

If you run serverless functions or more than about 20 instances, put an external pooler such as PgBouncer in transaction mode in front of the database. Each function instance then holds a lightweight client connection, and the pooler keeps the real database connection count fixed. Pooling is one piece of how services hold up on busy nights, alongside the scaling patterns in how platforms scale their servers.

Frequently asked questions

What is a good connection pool size?

Start with request rate multiplied by average time each request holds a connection, then add about 50 percent headroom. Make sure the total across all instances stays well below the database's connection limit. For many services this lands between 10 and 20 per instance.

Do I need PgBouncer if my framework already has a pool?

Not for a few long running instances, where the built in pool is enough. PgBouncer becomes valuable with serverless functions, heavy autoscaling or many separate services sharing one database. It keeps the real connection count fixed no matter how many clients appear.

What happens when a connection pool is exhausted?

New requests wait in a queue for a connection to be returned. If none frees up before the configured timeout, the request fails with an error. Exhaustion usually signals slow queries, leaked connections or long transactions rather than a pool that is too small.