Connection pool sizing: how many DB connections you need

Connection pool sizing is the question of how many open database connections your application should keep ready. The short answer comes from Little's law: the connections busy at any moment equal your query rate multiplied by how long each query holds a connection. Size the pool a little above that number, cap the total across every app instance, and put a pooler like PgBouncer in front when the instance count grows.

Bigger is not safer. A pool that is too large moves the queue from your app into the database, where it is more expensive.

What is a database connection pool?

A connection pool is a set of already-open database connections that your application borrows for a query or transaction and then returns. Opening a fresh connection costs a TCP handshake, often TLS, authentication, and on PostgreSQL a new server process. Reusing connections skips all of that.

Every pool has two numbers that matter: the maximum number of connections it will open, and how long a caller will wait for one before giving up. Most production incidents involving pools trace back to one of those two being chosen by accident.

How do you calculate connection pool size?

Little's law says the average number of items inside a system equals the arrival rate multiplied by the average time each item spends there. For a pool, the items are borrowed connections.

If your service runs 500 queries per second and each one holds a connection for 20 ms, then on average 500 × 0.020 = 10 connections are in use. That is the floor. A pool of 10 is exactly full on average, which means any burst queues.

Arrivals are random, so you need headroom above the average. A quick simulation with random arrivals and random hold times around 20 ms shows how fast the wait disappears once you clear the floor:

import heapq, random

def sim(pool, rate=500, hold=0.020, n=200_000, seed=1):
    rnd = random.Random(seed)
    t, free, waits = 0.0, [0.0] * pool, []
    heapq.heapify(free)
    for _ in range(n):
        t += rnd.expovariate(rate)                 # next request arrives
        start = max(t, heapq.heappop(free))         # wait for a free connection
        waits.append(start - t)
        heapq.heappush(free, start + rnd.expovariate(1 / hold))
    waits.sort()
    return sum(waits) / n * 1000, waits[int(0.99 * n)] * 1000

for size in (11, 12, 15, 20, 40):
    mean, p99 = sim(size)
    print(f"pool={size:>2}  mean wait={mean:6.2f} ms  p99 wait={p99:6.2f} ms")

Going from 11 to 15 connections removes almost all the waiting. Going from 20 to 40 buys nothing in the app, and costs something in the database. The useful rule: measure your real query rate and hold time at peak, multiply, then add roughly 50 to 100 percent.

Hold time is the number people forget. It is not query execution time. It is everything between borrowing and returning: the queries, the network round trips, and any application code that runs while a transaction is open. Calling an external API inside a transaction can turn a 5 ms hold into a 500 ms hold, and the pool you need grows a hundredfold.

Why does a bigger pool make the database slower?

A database has a fixed number of CPU cores and a fixed amount of disk and memory bandwidth. Past a point, more concurrent queries don't finish more work per second. They compete for locks, buffers and CPU time, and each query takes longer.

PostgreSQL makes this sharper because each connection is a separate server process with its own memory. Hundreds of mostly idle connections still use memory, and hundreds of active ones spend real time on context switching and lock contention. The default max_connections is 100 for a reason.

So when every connection is busy, the right place for extra requests to wait is in the app's pool queue, not inside the database. A short pool wait with a timeout is a form of backpressure. It pairs well with load shedding: if a caller can't get a connection within, say, a few hundred milliseconds, fail that request fast instead of letting it pile on.

How do multiple app instances change the math?

Pool size is per process. The database sees the sum.

total connections = instances × processes per instance × pool size

Ten containers with four worker processes each and a pool of 20 means 800 possible connections against a database that allows 100. It works fine at low traffic, then fails during the exact spike when autoscaling adds more containers. Serverless functions are the extreme case, since each concurrent invocation may hold its own connection.

Two ways out:

• Divide your database's connection budget across instances and set each pool to its share, leaving room for migrations, admin sessions and background jobs.
• Put a connection pooler between the app and the database so many client connections share a small set of real ones.

How does PgBouncer transaction pooling work?

PgBouncer is a lightweight proxy that accepts thousands of client connections and maps them onto a much smaller pool of real PostgreSQL connections. Its defaults are max_client_conn = 100 and default_pool_size = 20 per user and database pair, and you will usually raise the first and keep the second small.

It has three pool modes:

Transaction mode is the one most people want, and it has a catch. Consecutive transactions from the same client can run on different server connections, so anything stored in the session can leak to another client or vanish. PgBouncer's docs list these as unsupported in transaction mode:

• SET and RESET of session settings (use SET LOCAL inside the transaction instead)
• LISTEN
• Session-level advisory locks (transaction-level pg_advisory_xact_lock is fine)
• SQL-level PREPARE and DEALLOCATE
• WITH HOLD cursors and temp tables that outlive the transaction

Protocol-level prepared statements, the kind most drivers create automatically, are supported since PgBouncer 1.21 through the max_prepared_statements setting. It became non-zero by default (200) in version 1.24. On older versions you either turn it on or disable prepared statements in your driver, otherwise you see errors like "prepared statement already exists".

Also note that the server_reset_query, DISCARD ALL by default, does not run between clients in transaction mode. PgBouncer assumes you aren't using session state at all.

With a pooler in place, the app-side pool still matters. It now limits connections to PgBouncer, and PgBouncer's own default_pool_size becomes the number to size with Little's law. Long transactions hurt here too, for the same reason as before, and they are a common source of the slow outliers described in tail latency at scale.

Key takeaways

• Busy connections = queries per second × seconds each query holds a connection. Size the pool 50 to 100 percent above that.
• Hold time includes everything inside the transaction, so keep slow work outside it.
• Too many connections slow the database down; let excess requests wait briefly in the app and time out.
• Multiply pool size by every process and instance, and keep the total under the database limit.
• PgBouncer in transaction mode multiplies capacity but forbids session state like SET, LISTEN and session advisory locks.

FAQ

What is a good default connection pool size?

There isn't a universal one. Measure peak queries per second and average hold time, multiply them, and add headroom.

Should the pool timeout be short or long?

Short. A wait of a few hundred milliseconds to a couple of seconds lets you fail fast under overload instead of stacking requests behind an exhausted pool.

Do I still need an app-side pool if I use PgBouncer?

Usually yes. It avoids the overhead of reconnecting to PgBouncer on every request and caps how many connections each process opens.