How do you size a database connection pool?

The Answer

Say this in the room. 45 seconds.

• Little's Law is the whole formula: connections = arrival rate × hold time. A thousand requests a second holding a connection for 5ms needs five connections, not a hundred.
• Smaller is usually faster. Past the point where the database can genuinely execute more statements at once, extra connections queue inside the database and add lock contention, cache pressure and context switching.
• HikariCP's guidance is roughly (cores × 2) + effective spindles — for most services that lands between 10 and 20, and people routinely configure 100.
• Hold time is the variable that matters, and it is the one nobody measures. The same pool of 20 serves 10,000 requests a second at a 2ms hold and 100 at a 200ms hold.
• Never hold a connection across a call you do not control. A 200ms remote call inside a transaction turns a working pool into an outage.
• When the pool is exhausted, requests fail at acquisition, with a timeout — not at the query. That is a different error, in a different place, and it is what you alert on.
• Virtual threads make this more urgent, not less. Ten thousand virtual threads can all reach for a pool of ten.

Understand It

The formula

// Little's Law, which is the only sizing arithmetic worth memorising:
//   connections = arrival rate x how long each request holds a connection
System.out.println("  at 1000 requests per second:");
for (double hold : new double[] { 2, 5, 20, 200 })
    System.out.printf("    holding a connection for %5.0f ms -> %6.1f connections needed%n",
        hold, connectionsNeeded(1000, hold));

System.out.println();
System.out.println("  the same pool of 20, at different hold times:");
for (double hold : new double[] { 2, 5, 20, 200 })
    System.out.printf("    %5.0f ms hold -> serves %7.0f requests per second%n",
        hold, 20 / (hold / 1000.0));

  at 1000 requests per second:
    holding a connection for     2 ms ->    2.0 connections needed
    holding a connection for     5 ms ->    5.0 connections needed
    holding a connection for    20 ms ->   20.0 connections needed
    holding a connection for   200 ms ->  200.0 connections needed

  the same pool of 20, at different hold times:
        2 ms hold -> serves   10000 requests per second
        5 ms hold -> serves    4000 requests per second
       20 ms hold -> serves    1000 requests per second
      200 ms hold -> serves     100 requests per second

Read the second table, not the first. The same pool of twenty serves ten thousand requests a second or a hundred, depending entirely on hold time. Nothing about the pool changed.

That is why "how big should the pool be" is the wrong question asked first. The right one is how long does a request hold a connection, and almost nobody knows the answer for their own service — because the number includes everything between getConnection() and close(), not just the time the query spends executing.

It also explains why raising the pool size so often does nothing. If hold time is 200ms because a transaction spans a remote call, then serving 1000 requests a second genuinely needs 200 connections, which no database wants to give you. The fix is the 200ms, not the 200.

Bigger is not faster

// A model, not a measurement: the database executes at most `dbParallelism`
// statements at once and everything beyond that queues inside it.
int dbParallelism = 8;
double serviceMillis = 5;

System.out.println("  database that can genuinely run 8 statements at once, 5 ms each:");
for (int pool : new int[] { 4, 8, 16, 50, 200 })
    System.out.printf("    pool of %3d -> %6.0f requests per second%n",
        pool, throughputWith(pool, dbParallelism, serviceMillis));

  database that can genuinely run 8 statements at once, 5 ms each:
    pool of   4 ->    800 requests per second
    pool of   8 ->   1600 requests per second
    pool of  16 ->   1600 requests per second
    pool of  50 ->   1600 requests per second
    pool of 200 ->   1600 requests per second

This one is a model and it is the optimistic version. It assumes work beyond the database's real parallelism simply queues, at no extra cost. In practice a real engine gets worse past the knee, not merely flat — more concurrent connections mean more lock contention, more buffer-cache churn, more context switches, and on some engines a per-connection memory cost. The flat line above should slope down.

The knee is at eight because that is where the database's actual capacity to execute statements runs out. A pool of two hundred does not create more CPU cores or disks; it creates two hundred places for work to wait, and moves the queue from your application — where you can see it, time it, and shed load from it — into the database, where you cannot.

This is the counterintuitive claim worth being able to defend: a pool of 10 frequently outperforms a pool of 100 on the same hardware, and the reason is that the pool is a concurrency limit, not a capacity. Its job is to protect the database from more concurrent work than it can do well.

What exhaustion actually looks like

// A pool of 10. Fifty requests arrive at once, each needing a connection for
// 100ms, and the acquisition timeout is 250ms.
var pool = drive(10, 50, 100, 250);

System.out.println("  pool size            : " + pool.size);
System.out.println("  requests             : 50");
System.out.println("  served               : " + pool.served.get());
System.out.println("  failed to get one    : " + pool.timedOut.get());
System.out.println("  peak connections used: " + pool.peakInUse.get());

  pool size            : 10
  requests             : 50
  served               : 30
  failed to get one    : 20
  peak connections used: 10

Thirty served, twenty refused, and the arithmetic is exact rather than lucky: a 250ms acquisition timeout divided by a 100ms hold means three batches of ten get a connection before the deadline, and everyone else gives up.

The important part is where the failure happens. Those twenty requests never reached the database. They failed at getConnection(), with a pool timeout — in HikariCP, SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 250ms. No slow query, no database error, nothing in the database's own logs.

That distinction is the whole diagnostic value. A slow query means the database is struggling. A pool timeout means your application is holding connections longer than it can afford, and the database may be entirely idle while it happens. They look identical from a latency graph and want opposite investigations.

The way pools actually die

// The same pool of 10 and the same 50 requests. The only difference is what
// the request does while holding the connection.
var dbOnly = drive(10, 50, 100, 250);
System.out.println("  100 ms of query only                    -> served " + dbOnly.served.get()
                 + ", refused " + dbOnly.timedOut.get());

var withRemoteCall = drive(10, 50, 300, 250);
System.out.println("  100 ms query + 200 ms call, same holding -> served " + withRemoteCall.served.get()
                 + ", refused " + withRemoteCall.timedOut.get());

  100 ms of query only                    -> served 30, refused 20
  100 ms query + 200 ms call, same holding -> served 10, refused 40

Same pool. Same fifty requests. Same database work. Three times fewer served, because each request kept its connection during a 200ms call to something else.

This is the single most common way a healthy pool becomes an incident, and it does not look like a database problem from any angle. The database is fine. The queries are fast. The pool is "big enough" by whatever calculation was done originally. What changed is that a payment call, an audit write, or an enrichment lookup ended up inside the transaction — and now hold time is dominated by a service you do not control.

It is the same rule as the deadlock entry, applied to a different scarce resource: never hold a lock, or a connection, across a call you do not control. When the remote service slows down, your pool empties, and every unrelated endpoint that needs a connection starts failing at acquisition.

Reference

The correct implementation, the configuration, and the migration path. Copy from here.

Sizing, in order

1. Measure hold time.        How long between getConnection() and close()?
2. Measure arrival rate.     Requests per second that touch the database.
3. Little's Law.             connections = rate x hold time
4. Sanity-check the ceiling. (cores x 2) + effective spindles, on the DB host
5. Take the smaller number, then load-test it.

Step 1 is the one that gets skipped and the one that matters. If hold time turns out to be 300ms, stop sizing and go fix that — no pool size makes a 300ms hold acceptable.

HikariCP, with the settings that matter

# The size. Start here, not at 100.
spring.datasource.hikari.maximum-pool-size=10

# Keep it fixed. A pool that grows and shrinks makes latency unpredictable
# and hides the fact that you are over the database's real capacity.
spring.datasource.hikari.minimum-idle=10

# Fail fast at acquisition. This is the number that decides whether an
# overloaded service sheds load or queues until everything times out.
spring.datasource.hikari.connection-timeout=3000

# Leak detection: log a stack trace when a connection is held this long.
# The single most useful setting on this list — it names the code holding it.
spring.datasource.hikari.leak-detection-threshold=20000

# Shorter than the database's own idle timeout, or you hand out dead ones.
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.idle-timeout=600000

Turn on leak-detection-threshold in every environment. It costs nothing and it answers the question this whole page is about — what is holding connections — with a stack trace rather than a theory.

Watch these four numbers

pending is the one to alert on. It is zero on a healthy service and non-zero the moment demand exceeds the pool, and it moves before users see errors — unlike the timeout counter, which only rises once requests are already failing.

Adding up every pool

6 service instances x pool of 10          =  60
+ 2 batch workers   x pool of 5           =  10
+ 1 admin tool      x pool of 5           =   5
+ replication and monitoring connections  =  ~10
                                            ---
                                             85 against max_connections

PostgreSQL's default max_connections is 100, and every connection costs it a backend process and memory whether or not it is doing anything. Count the total across every instance and every tool before raising any individual pool — autoscaling to twelve instances quietly doubles the first line.

Past a few hundred, a connection pooler in front of the database — PgBouncer in transaction mode — multiplexes many client connections onto few server ones. It is the right answer at that scale and it constrains you: session-level state such as prepared statements, temporary tables and advisory locks does not survive transaction pooling.

Virtual threads

// On Java 21 this is cheap, and the pool is now the only limit that bites.
var executor = Executors.newVirtualThreadPerTaskExecutor();
for (int i = 0; i < 10_000; i++)
    executor.submit(() -> repository.findById(id));    // 10,000 threads, pool of 10

Before virtual threads, a platform thread pool of 200 was an implicit cap on database concurrency — a second limit that happened to protect you. Virtual threads remove it, so ten thousand tasks can all reach for a pool of ten simultaneously.

Nothing about the sizing changes; what changes is that the pool is now the only backpressure in the system. That makes connection-timeout a load-shedding decision rather than a footnote, and it makes pending the metric that tells you whether the service is holding.

Scenarios

Real situations, with the decision and the argument.

1. The service is timing out and someone wants to raise the pool from 10 to 100.

Ask what the timeouts say first. If they are acquisition timeouts, connections are being held too long and a bigger pool buys a little headroom while making the database's contention worse. If they are query timeouts, the pool is not the problem at all.

Then ask for hold time. At 5ms, a pool of 10 serves 2000 requests a second and the problem is elsewhere. At 300ms, it serves 33 — and the fix is the 300ms, not the pool. Raising the number is defensible only once you know which of those you are in.

2. Latency is fine on average and terrible at p99, with the database idle.

Classic pool contention. Most requests get a connection immediately; the unlucky ones queue behind whatever is holding one. An idle database is the tell — nothing is slow, something is held.

hikaricp_connections_pending and the acquire-time percentile confirm it in seconds. Then turn on leak-detection-threshold and let it name the code, rather than guessing which method holds longest.

3. Everything worked until a downstream service slowed down.

The transaction spans that call, so its slowness became your hold time. This is the case from the last block: the same pool serves a third as many requests when 200ms of remote call sits inside the connection's lifetime.

Move the call outside the transaction. If it genuinely cannot move — it needs the same atomicity — then the answer is an outbox rather than a bigger pool, because there is no pool size that survives an unbounded downstream. Adding a timeout to the remote call is worth doing regardless, and is a mitigation rather than a fix.

4. Autoscaling from 4 to 20 instances and the database starts refusing connections.

Each instance has its own pool, so the total is instances × pool size and it grew fivefold. At a pool of 10 that is 200 connections against a PostgreSQL default max_connections of 100.

Two directions, and they combine. Shrink the per-instance pool, since more instances means each needs fewer. And put a pooler in front if the instance count is genuinely elastic — PgBouncer in transaction mode multiplexes them, at the cost of session-level state such as prepared statements and temporary tables.

5. Someone proposes one big shared pool for the whole application.

Reasonable-sounding and worth resisting for one specific reason: a batch job and a request handler have very different hold times, and sharing a pool lets the batch job's long holds starve the requests.

Separate pools give you isolation you can reason about — a small one for interactive requests with a short acquisition timeout, a separate one for batch work that is allowed to wait. The total connection count is what needs watching, not the number of pools.

Interviewer's Next Move

The question after the question. This is what the round is actually testing.

1. "How do you size a connection pool?" Little's Law: connections equal arrival rate times hold time. A thousand requests a second holding a connection for 5ms needs five. Then sanity-check against what the database can actually execute concurrently, and take the smaller number.

2. "Our pool is 100. Is that right?" Almost certainly too big. It implies either enormous throughput or a very long hold time, and the second is far more common. The question to ask back is what the hold time is.

3. "Why would a smaller pool be faster?" Because the pool is a concurrency limit, not capacity. Past what the database can genuinely run at once, extra connections queue inside the database and add lock contention, cache churn and context switching — and they move the queue somewhere you cannot see or shed load from.

4. "What is the most important number?" Hold time. The same pool of twenty serves ten thousand requests a second at a 2ms hold and a hundred at 200ms. It is also the number almost nobody measures, because it covers everything between getConnection() and close(), not just query execution.

5. "What happens when the pool is exhausted?" Requests fail at acquisition with a pool timeout, before reaching the database. The database may be completely idle. It is a different error in a different place from a slow query, and it wants the opposite investigation.

6. "How would you find what is holding connections?" leak-detection-threshold in HikariCP — it logs a stack trace for any connection held longer than the threshold, which names the code instead of narrowing it down. Alongside that, alert on connections_pending, which moves before users see errors.

7. "You have twelve instances. What is your pool size?" Smaller than for one instance, and the total is what matters: instances times pool size, plus batch workers, admin tools and monitoring, against max_connections. Autoscaling silently multiplies the first term.

8. "What breaks if you put PgBouncer in front?" In transaction mode, session-level state does not survive — prepared statements, temporary tables, advisory locks, SET for the session. It is the right answer past a few hundred connections and it constrains what the application may rely on.

9. "Do virtual threads change any of this?" The sizing, no. The urgency, yes. A platform thread pool used to cap database concurrency as a side effect; virtual threads remove that, so ten thousand tasks can reach for a pool of ten and the pool becomes the only backpressure in the system.

Code traps

Trap A — predict before you run:

@Transactional
public void confirm(Long orderId) {
    Order order = repository.findById(orderId).orElseThrow();
    paymentGateway.charge(order.total());          // p99 900ms
    order.setStatus(CONFIRMED);
}

Trap B:

spring.datasource.hikari.maximum-pool-size=200
spring.datasource.hikari.connection-timeout=60000

Trap C:

public List<Report> generate() throws SQLException {
    Connection c = dataSource.getConnection();
    var reports = buildFrom(c);                   // may throw
    c.close();
    return reports;
}

Common wrong answers

Check Yourself

Q1. A service handles 1000 requests a second. What pool size does it need?

Q2. Requests are failing, the pool is exhausted, and the database is idle. Where do you look?

Q3. Why might reducing a pool from 100 to 20 improve throughput?

Practice