Your Database Has 8 Cores. Why Does It Have 800 Connections?

Pool size times pod count is a number nobody owns until the database refuses connections. Little's law sizes the pool; a simulation shows why bigger is slower.


Forty application pods each open a pool of 20 connections, 800 in total, against a PostgreSQL server with 8 cores and max_connections of 100; a simulation shows a pool of 8 keeping p99 at 114 ms through traffic bursts while a pool of 100 collapses to a p99 of 35 seconds

Here’s a story that plays out, in some form, at a lot of companies running services on Kubernetes. Call it a composite; the details change, the arithmetic doesn’t.

The ORM ships with a default pool of 10 or 20 connections. The service has 10 replicas, so it uses 200 connections, and the database allows 100 by default, so someone raises max_connections to 500 and moves on. Six months later the service autoscales to 40 replicas during a traffic spike, asks for 800 connections, and starts logging FATAL: sorry, too many clients already. Someone raises the limit again.

Nobody chose 800. It’s the product of two settings owned by two different people: pool size, in an application config file, and replica count, in an autoscaler. The database has 8 cores. It can actually run 8 queries at a time.

How many connections do you actually use?

Little’s law answers this in one line: the average number of things in a system equals arrival rate times time spent in the system. For a database pool, that’s

busy connections = queries per second × average query time

A service doing 1,400 queries per second at 4 ms each keeps 5.6 connections busy on average. Not 200. Not 800. Even during a burst to twice the traffic, it needs about a dozen.

Every other connection in the pool sits idle, holding a Postgres backend process and its memory, doing nothing until a burst arrives. Then they all wake up at once, and that’s where the trouble starts.

Why a bigger pool is slower

A database with 8 cores can execute 8 queries in parallel. Give it 100 active queries and it doesn’t do 100 things at once; it time-slices between them, so each one takes longer. Worse, the overhead isn’t free. Lock contention, context switches and CPU cache misses grow with the number of active queries and eat into the CPU that should be doing useful work.

The HikariCP pool sizing guide puts it in one sentence: “You want a small pool, saturated with threads waiting for connections.” Waiting in the application’s pool queue is cheap. Waiting inside the database, competing for its CPU, slows everyone down at once.

Here’s a simulation. An 8-core database, queries that need 4 ms of CPU, 1,400 queries per second, with a 2-second burst to 2,100 per second every 20 seconds. Queries that can’t get a connection wait in the app. One modelling assumption, stated up front: each active query beyond the core count costs 1% of the server’s throughput in contention. That number is made up. Real databases vary a lot, which is why the script also runs with zero contention cost.

"""Connection pool size vs. a database with 8 cores, simulated in 0.5 ms ticks.

Each query needs 4 ms of CPU. Queries arrive at 1,400/s (70% utilisation), with
a 2-second burst at 2,100/s every 20 seconds. A query that can't get a
connection waits in the application's pool queue.

Contention model (an assumption, not a measurement): when more queries are
active than there are cores, the server time-slices them, and each extra active
query beyond the core count costs 1% of total throughput in lock contention,
context switches and cache misses.
"""
import random
from collections import deque

CORES, CPU_MS, BASE_RATE, BURST_RATE, TICK, SECONDS = 8, 4.0, 1400, 2100, 0.5, 120
PENALTY = 0.01


def run(pool, penalty=PENALTY, seed=3):
    rnd = random.Random(seed)
    waiting, active, lat = deque(), [], []        # active: [arrival_ms, cpu_left]
    for i in range(int(SECONDS * 1000 / TICK)):
        now = i * TICK
        rate = BURST_RATE if (now % 20000) < 2000 else BASE_RATE
        # Poisson arrivals this tick
        n, p = 0, rnd.random()
        lam = rate * TICK / 1000
        while p > 2.718281828 ** -lam:
            n += 1
            p *= rnd.random()
        waiting.extend([now] * n)
        while waiting and len(active) < pool:
            active.append([waiting.popleft(), CPU_MS])
        k = len(active)
        if k:
            capacity = CORES / (1 + penalty * max(0, k - CORES))   # CPUs of useful work
            share = min(1.0, capacity / k) * TICK
            for q in active:
                q[1] -= share
            done = [q for q in active if q[1] <= 0]
            for q in done:
                active.remove(q)
                lat.append(now + TICK - q[0])
    lat.sort()
    pct = lambda x: lat[int(len(lat) * x) - 1]
    backlog = len(waiting) + len(active)
    return (f"pool {pool:4d} | completed {len(lat):6d} | p50 {pct(.5):6.1f} ms | "
            f"p99 {pct(.99):7.1f} ms | p99.9 {pct(.999):7.1f} ms | left in flight {backlog}")


for size in (4, 8, 16, 32, 100, 400):
    print(run(size))
print("-- same, with zero contention cost (pure time-slicing) --")
for size in (8, 100, 400):
    print(run(size, penalty=0.0))
pool    4 | completed 119996 | p50 19292.5 ms | p99 38241.5 ms | p99.9 38828.5 ms | left in flight 56482
pool    8 | completed 176470 | p50    4.0 ms | p99   114.0 ms | p99.9   140.0 ms | left in flight 8
pool   16 | completed 176469 | p50    4.5 ms | p99   361.0 ms | p99.9   399.0 ms | left in flight 9
pool   32 | completed 176469 | p50    5.5 ms | p99   627.0 ms | p99.9   666.5 ms | left in flight 9
pool  100 | completed 124983 | p50 18099.0 ms | p99 35439.5 ms | p99.9 35720.5 ms | left in flight 51495
pool  400 | completed  48634 | p50 43868.0 ms | p99 86029.5 ms | p99.9 86893.0 ms | left in flight 127844
-- same, with zero contention cost (pure time-slicing) --
pool    8 | completed 176470 | p50    4.0 ms | p99   114.0 ms | p99.9   140.0 ms | left in flight 8
pool  100 | completed 176469 | p50    4.5 ms | p99   142.0 ms | p99.9   166.5 ms | left in flight 9
pool  400 | completed 176469 | p50    4.5 ms | p99   215.5 ms | p99.9   244.0 ms | left in flight 9

Read it top to bottom.

Too small is bad too. A pool of 4 can only push 1,000 queries per second through 4 ms queries. Traffic is 1,400. It never catches up. The right size isn’t “as small as possible”; it’s “enough for the peak, and no more than the database can run at once”.

8 is the sweet spot here, and it equals the core count. Bursts queue briefly in the app and drain in about 100 ms.

16 and 32 still work, but the tail gets steadily worse. Same throughput, longer p99, because during a burst every query competes inside the database instead of waiting its turn outside it.

100 and 400 fall over and don’t get up. This is the part that matters. During a burst, all 100 connections go active, contention eats into throughput, and the database now completes fewer queries per second than arrive, even at normal traffic. The backlog keeps every connection busy, which keeps contention high, which keeps throughput low. The burst ends and the system stays broken. That’s a metastable failure, and it’s exactly what “the database got slow and never recovered until we restarted the app” looks like from the outside.

Even with zero contention cost, bigger is worse at the tail. Pure time-slicing alone takes p99 from 114 ms to 215 ms between 8 and 400 connections. The contention penalty decides whether a big pool is merely slower or catastrophic; it never makes it faster.

Sizing it for real

Start with the formula HikariCP quotes from the PostgreSQL community:

connections = (core_count × 2) + effective_spindle_count

with core count excluding hyperthreads, and spindle count zero if your working set fits in memory. For an 8-core database with everything cached, that’s 16. Then check it against Little’s law for your peak traffic, and load test around that number. HikariCP’s own caveat applies: “Pool sizing is ultimately very specific to deployments.”

The crucial step most teams skip: that number is for the whole database, not per pod. Divide it across every client:

"""Per-pod pool size from a database-wide connection budget."""
import math

def per_pod_pool(db_budget, max_replicas, services=1, reserve=0.2):
    """db_budget: connections the database should actually run (e.g. 2 x cores).
    reserve: fraction held back for migrations, admin, cron, and humans."""
    usable = math.floor(db_budget * (1 - reserve))
    return max(1, usable // (max_replicas * services))

print(per_pod_pool(db_budget=16, max_replicas=40))            # -> 1
print(per_pod_pool(db_budget=16, max_replicas=10))            # -> 1
print(per_pod_pool(db_budget=64, max_replicas=10, services=2))  # -> 2
1
1
2

A pool of one connection per pod looks absurd, and that’s the point. Once you have more than a handful of replicas, direct per-pod pools can’t be sized correctly at all: either each pod gets a uselessly small pool, or the total blows through what the database can run. That’s the signal you need a pooler in the middle.

PgBouncer, and what it costs you

PgBouncer sits between your pods and Postgres. Each pod can open as many client connections as it likes; PgBouncer maps them onto a small, fixed number of real server connections. The setting that matters is the pool mode:

  • Session pooling assigns a server connection for as long as the client stays connected. Safe, and it multiplexes almost nothing while your pods hold connections open.
  • Transaction pooling assigns a server connection only for the duration of a transaction. This is the mode that turns 800 client connections into 16 server connections.

Transaction mode has a price. Between transactions, your next query may land on a different server connection, so session state doesn’t follow you. PgBouncer documents that SET/RESET, LISTEN, WITH HOLD cursors, SQL-level PREPARE/DEALLOCATE and session-level advisory locks are never supported in transaction mode. Protocol-level prepared statements, which many drivers use under the hood, do work, but only after you set max_prepared_statements to a non-zero value.

Before switching, grep your code for SET statements, LISTEN, and pg_advisory_lock. If you rely on advisory locks for leader election, move them to a direct connection or to transaction-scoped pg_advisory_xact_lock.

Raising max_connections is the wrong fix

The PostgreSQL docs give max_connections a default of typically 100, settable only at server start, with three of those slots reserved for superusers. When you hit it, raising it feels like the fix. But every Postgres connection is a separate backend process. Raising the limit doesn’t add cores. It raises the ceiling on how badly the database can overload itself, which is the 100 and 400 rows above.

Treat max_connections as a circuit breaker, not a capacity setting. It’s the thing that stops a misconfigured autoscaler from turning a traffic spike into a database meltdown. If you keep hitting it, the fix is upstream: smaller pools, or a pooler.

A checklist

  1. Compute your total: pool size × max replicas, summed across every service that talks to the database. Compare it with the core count, not with max_connections.
  2. Measure busy connections, not open ones. In Postgres, count pg_stat_activity rows with state = 'active' and graph it. That’s your real concurrency, and Little’s law should predict it.
  3. Size the database-wide budget around 2× cores, then load test.
  4. If per-pod pools come out below 2–3 connections, put PgBouncer in transaction mode in front, and audit for session state first.
  5. Set a pool acquire timeout shorter than your request deadline, so a saturated pool fails fast instead of piling up waiting requests.
  6. Leave max_connections alone, unless you’ve done the math and know why.

Your database doesn’t need more connections. It needs fewer things trying to run on it at once.


Related: Queueing Theory for SREs: Little’s Law and the Utilisation Knee · Cascading Failure: Backpressure, Load Shedding, and Graceful Degradation · Percentiles, Properly: You Cannot Average a p99

Frequently asked questions

How big should a database connection pool be?

Smaller than most people think. By Little's law, the number of connections actually busy at once is query throughput multiplied by average query time: 1,400 queries per second at 4 ms each keeps about 5.6 connections busy. Size the pool a little above that peak, and close to what the database can execute in parallel, which is bounded by its CPU cores. HikariCP's guidance starts from connections = (core_count × 2) + effective_spindle_count, then tunes under real load.

Why does adding connections make a database slower?

A database can only execute as many queries in parallel as it has cores. Past that point, extra active connections don't add throughput; the server time-slices between them, so every query takes longer, and lock contention, context switching and cache misses take a growing share of the CPU. Queueing in the application's pool is cheap and visible. Queueing inside the database is expensive and makes everything slower at once.

What is the default max_connections in PostgreSQL?

The PostgreSQL documentation gives the default as typically 100 connections, possibly less if kernel settings don't support it, and it can only be changed at server start. Three of those slots are reserved for superusers by default (superuser_reserved_connections), so ordinary application roles get 97.

What is the difference between PgBouncer session and transaction pooling?

In session pooling, a client keeps one server connection for as long as it stays connected, so it multiplexes nothing while clients are idle. In transaction pooling, a server connection is assigned only for the duration of a transaction, so hundreds of client connections can share a few dozen server connections. The cost is that session state doesn't survive between transactions: PgBouncer documents that SET/RESET, LISTEN, WITH HOLD cursors, SQL-level PREPARE/DEALLOCATE and session-level advisory locks are never supported in transaction mode.

Comments