Connection Pool Sizing: The Math Behind Why Your App Is Slow

Why Should You Care About Connection Pool Sizing?

Connection pooling is one of the most common sources of database performance problems — and one of the most overlooked. Everyone wants to ship features. Nobody wants to think about how many connections their app holds open. But get this wrong and you'll see mysterious slowdowns, timeouts, and outages that no amount of query tuning will fix.

Let's start from first principles and build up to a real-world calculation.

What Is a Database Connection?

A connection is a session between your application and the database. Over this session, SQL statements are sent and query results come back. The lifecycle looks simple:

1. Open a connection to the database
2. Execute your SQL statement
3. Close the connection

Sounds easy, right? The problem is that opening and closing connections are extremely expensive operations.

The Hidden Cost of Opening a Connection

Here's what actually happens when you open a single database connection (e.g., HikariCP to Oracle):

That's 7 to 10 network round trips just to establish one connection. Now let's see how that translates to real time:

If you're cross-region, opening a single connection can take over a second. Imagine doing that for every query your application sends. You'd spend more time connecting than actually querying.

This is exactly why connection pools exist.

What Does a Connection Pool Actually Do?

A connection pool sits between your application and the database. It maintains a set of pre-opened connections so that when a query arrives, it doesn't need to open a new connection — it just grabs one from the pool.

The pool tracks two types of connections:

• Active connections — currently executing queries
• Idle connections — waiting for incoming requests

But idle connections aren't free. Each open connection holds 10-30 MB of memory on the database server. With hundreds of connections, that memory adds up fast.

The pool also manages the connection lifecycle — it can dynamically add connections when demand rises (if you've configured a min/max range). But spinning up new connections under load adds overhead too, so you want to get the sizing right upfront.

Application-Side vs Database-Side Pools

There are two places you can manage connection pools:

• Application-side pools (e.g., HikariCP, c3p0, pgBouncer per-app) — configured per application, tailored to that app's workload. This is what most of you are using.
• Database-side pools — used when applications don't manage their own connections properly (they open connections randomly and don't close them). This is essentially treating a symptom of bad application-side pooling.

Every application should have its own connection pool. If they don't, you'll be forced to manage it on the database side — and that's a worse place to solve the problem.

When Requests Exceed Connections: Enter Queuing Theory

What happens when your application sends more requests than there are open connections? They queue. They line up and wait.

And a queue has math behind it. Your connection pool is literally a queuing system:

The service time (query execution time) is the bottleneck. If your servers (connections) are busy all the time, the queue grows unbounded. The first-in, first-out principle applies — connections that arrive first get served first.

The critical rule: if your arrival rate exceeds your maximum throughput, your queue will grow to infinity. Your app will eventually time out and crash.

Little's Law: Finding the Minimum Pool Size

In 1954, mathematician John Little proved a beautifully simple theorem that applies to any stable queuing system. It's used in manufacturing, retail, project management — and it works perfectly for connection pools.

The service time W is the bottleneck. Reduce it and the whole system gets faster.

A Simple Example: The Bookstore

Imagine a bookstore where 10 visitors arrive every hour. Each visitor spends 30 minutes (0.5 hours) browsing before paying:

L = λ × W
L = 10 visitors/hour × 0.5 hours
L = 5 customers in the store at any time

Applying Little's Law to Connection Pools

Now replace the bookstore with your application:

• Your app executes 50 transactions per second (throughput λ)
• Each transaction takes 100 milliseconds on average (service time W = 0.1 seconds)

L = λ × W
L = 50 tx/sec × 0.1 sec
L = 5 connections needed at any given time

So 5 connections is the minimum you need to handle this workload. Since we're working with averages, this is the minimum mean — assuming stable workload.

Great, so we set the pool to 5 and call it a day? No. Absolutely not.

Kingman's Formula: Why 100% Utilization Breaks Everything

If we set the pool to exactly 5, our resources would be at 100% utilization. And that's where things get dangerous.

In 1962, British mathematician Sir John Kingman developed an approximation that shows:

Even a small increase in utilization can cause a disproportionately large increase in waiting time.

This is the key insight that most engineers miss. The relationship between utilization and wait time isn't linear — it's exponential.

What Happens in the Danger Zone (Above ~80%)

When your connection pool runs above roughly 80% utilization, three bad things happen simultaneously:

1. Queue buildup — Additional requests must wait for a free connection. The queue grows rapidly and waiting times spike.
2. Resource contention — More threads/processes compete for the same connections, leading to delays and potential deadlocks.
3. Exponential backoff — The time to acquire a connection increases exponentially. Each additional request waits longer than the last, leading to cascading timeouts.

As the HikariCP documentation puts it:

"You want a small pool, saturated with threads waiting for connections."

A small, well-tuned pool outperforms a large, over-provisioned one every time.

When the Pool Is Too Big: The Context Switching Tax

So small pools cause queuing and large pools are fine? Not quite. Oversized pools create a different problem on the database side: context switching.

When the database has more active connections than it can physically handle, the CPU starts time-sharing — rapidly switching between processes to treat them all equally:

Every context switch burns CPU cycles on saving and restoring process state instead of actually executing your queries. The more connections fighting for CPU time, the worse the thrashing gets. Your queries all get slower, even though individually they're simple.

The Formula: Calculating Max Pool Size

The maximum number of connections your database server can handle efficiently comes from the process-to-core ratio:

Modern CPUs can handle multiple threads per core (typically 2 with hyper-threading). The spindle count accounts for disk I/O parallelism — it's usually a small number (1-8) and doesn't change the calculation much when you have many cores.

Keep in mind: your database server runs other processes too (background workers, replication, monitoring). Don't assume all cores are available for query execution.

Real-World Example: A Banking System

Let's walk through a complete, real-world example. This was a finance system handling customer transactions.

The Setup

Step 1: Calculate the Hardware Limit

max_pool_size = (core_count × 2) + spindle_count
max_pool_size = (104 × 2) + 8
max_pool_size = 216 connections

Current config: 640 connections → 3× over the hardware limit!

Step 2: Calculate What the Workload Actually Needs (Normal Day)

Mean transaction time:  100 ms (0.1 sec)
Mean load:              50 transactions/sec

L = λ × W
L = 50 × 0.1
L = 5 connections needed

Per JVM: 5 / 8 = 0.625 connections
→ Even 2 connections per JVM would be more than enough!

Step 3: Calculate for Peak Load (Black Friday)

SLA max transaction time:  2,000 ms (2 sec) — card timeout
Peak load:                 100 transactions/sec

L = λ × W
L = 100 × 2
L = 200 connections needed

Per JVM: 200 / 8 = 25 connections

The Verdict

The server was properly sized for the workload. But the connection pool was 3x larger than the hardware could handle. All those extra connections were just causing context switching, wasting memory, and slowing everything down.

One Pool or Many? Separating Workloads

Can you use a single pool for everything? If your workload is uniform — same types of queries, similar execution times — sure. But most real applications have mixed workloads:

• Short transactions — user-facing reads and writes (5-50ms)
• Long-running batch jobs — reports, data exports, ETL (seconds to minutes)

If both share a single pool, a batch job holding a connection for 30 seconds blocks a user transaction that only needs 10ms. The solution: separate pools for different workload types.

Key Takeaways

1. You absolutely need a connection pool — opening connections per-request wastes 5ms to 1.5 seconds every time.

2. Small pools can hurt, but large pools hurt more — oversized pools overload the database with context switching and memory pressure.

3. Know your hardware — the max pool size formula is (CPU cores × 2) + spindle count. Never exceed this.

4. Use Little's Law for minimum connections — L = λ × W tells you how many connections your workload actually needs.

5. Use Kingman's Formula to stay safe — never run above ~80% utilization. The wait time curve goes exponential after that.

6. Separate different workloads — don't let batch jobs starve your user-facing transactions. Use multiple pools.

7. Monitor and adjust — calculate first, then observe. Your workload will change, and your pool config should change with it.

Connection pool sizing isn't glamorous work. But getting it right is the difference between an application that handles Black Friday gracefully and one that tips over at 80% capacity. Do the math. Size the pool. Sleep better at night.