Connection Pool Sizing with Little's Law: Mathematical Approach to HikariCP and PgBouncer

Little’s Law sounds like theory until your pool hits the wall at 2 a.m. “Set pool size to 50.” Why 50? “Because that’s what we have everywhere.” This is not engineering, this is cargo cult.

I’ve reviewed dozens of production configurations over the years, and the connection pool size is almost always a cargo-culted number. Teams copy settings from old projects, from Stack Overflow answers, or from some blog post written a decade ago. When I ask why they chose that number, nobody knows. And when I show them how oversized their pool is—consuming database resources for connections that sit idle 95% of the time—they’re genuinely surprised.

Connection pool sizing is a mathematical problem with a precise solution. Little’s Law, a theorem from queueing theory, tells you exactly how many connections you need. No guessing, no copying from the internet. Just measurement and calculation.

Tested on: PostgreSQL 16.1, HikariCP 5.1, pgbouncer 1.21, k6 load testing

Little’s Law Basics

Little’s Law is one of the most useful results in queueing theory, and it’s beautifully simple. It relates three quantities: the average number of items in a system, the arrival rate, and the average time each item spends in the system.

Formula

L = λ × W

L = average number of items in system (concurrent connections)
λ = arrival rate (requests per second)
W = average time in system (query duration)

The beauty of Little’s Law is that it holds for almost any queueing system, regardless of the arrival distribution, the service time distribution, or the queueing discipline. It just works.

Application to Connection Pool

Translating to connection pools: if you know your request rate and your average query duration, you can calculate exactly how many connections you need to handle that load without queueing.

Pool Size = Requests/sec × Avg Query Duration

Example:
- 500 requests/sec
- Average query takes 20ms = 0.02s
- Pool Size = 500 × 0.02 = 10 connections

With 20% buffer: Pool Size = 12

That’s it. Ten connections for 500 requests per second. Not 50, not 100, not “as many as we can get.” Just ten.

Of course, reality is messier than the formula. Query times have variance—your p99 might be 5x your average. Traffic has spikes. Connections fail. That’s why we add a buffer. But the starting point is always the math.

Why Bigger Pool ≠ Better Performance

“Just set it to 100 to be safe” is a common mindset that actively hurts performance. Each connection has costs, and those costs add up quickly.

Connection Overhead

Each PostgreSQL connection consumes:

• ~10MB RAM on server (work_mem, sort buffers, etc.)
• One backend process (PostgreSQL is process-per-connection)
• Lock contention with high connection count (especially for highly concurrent workloads)

A server with 100 connections is using 1GB of RAM just for connection overhead—before any query processing happens. And 100 backend processes means 100 processes competing for CPU time, memory bandwidth, and lock acquisition.

Amdahl’s Barrier

There’s a hard limit based on your database server’s hardware:

If you have 8 CPU cores on DB server:
- More than 8 active queries = context switching
- Pool size 100 = 92 queries waiting + overhead

Optimum: pool_size ≈ (2 × CPU cores) + disk spindles
For NVMe: pool_size ≈ 2 × CPU cores = 16

This formula comes from the PostgreSQL wiki and represents the point where adding more connections starts hurting rather than helping. Beyond this point, you’re just adding contention without adding throughput.

I’ve seen databases where halving the connection pool size from 100 to 50 improved throughput by 15%. Less contention, less context switching, better performance.

Calculating Pool Size

Let me walk through the process I use to calculate pool size for a new service.

Step 1: Measure Query Duration

First, gather data on actual query performance from pg_stat_statements:

-- Average and p99 query duration
SELECT
    substring(query, 1, 50) as query_sample,
    calls,
    round(mean_exec_time::numeric, 2) as avg_ms,
    round(max_exec_time::numeric, 2) as max_ms
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;

Pay attention to both average and p99 (or max). If your average is 10ms but your p99 is 500ms, you need to account for that variance in your pool sizing.

Step 2: Estimate Target Throughput

Get your target RPS from load balancer metrics, APM tools, or capacity planning. Then calculate:

# pool_calculator.py
from dataclasses import dataclass
from typing import Tuple

@dataclass
class PoolCalculation:
    requests_per_sec: int
    avg_query_ms: float
    p99_query_ms: float
    safety_factor: float = 1.5

    def calculate(self) -> Tuple[int, int]:
        """Returns (minimum, recommended) pool size"""
        # Little's Law: L = λ × W
        avg_duration_sec = self.avg_query_ms / 1000

        # Minimum for average load
        min_pool = self.requests_per_sec * avg_duration_sec

        # For p99 (spike handling)
        p99_duration_sec = self.p99_query_ms / 1000
        p99_pool = self.requests_per_sec * p99_duration_sec

        # With safety buffer
        recommended = p99_pool * self.safety_factor

        return int(min_pool) + 1, int(recommended) + 1

# Example
calc = PoolCalculation(
    requests_per_sec=500,
    avg_query_ms=15,
    p99_query_ms=80
)
min_size, recommended = calc.calculate()
print(f"Minimum: {min_size}, Recommended: {recommended}")
# Minimum: 8, Recommended: 60

The recommended size accounts for both p99 latency spikes and adds a safety factor. In this example, 60 connections handles both normal load and worst-case scenarios.

Step 3: Validate with Load Test

Never trust calculations alone. Validate with realistic load testing:

// k6_pool_test.js
import http from 'k6/http';
import { check, sleep } from 'k6';
import { Rate, Trend } from 'k6/metrics';

const errorRate = new Rate('errors');
const waitTime = new Trend('db_wait_time');

export const options = {
    scenarios: {
        constant_load: {
            executor: 'constant-arrival-rate',
            rate: 500,           // 500 RPS
            timeUnit: '1s',
            duration: '5m',
            preAllocatedVUs: 100,
            maxVUs: 200,
        },
    },
};

export default function () {
    const start = Date.now();
    const res = http.get('http://api:8080/query');
    const duration = Date.now() - start;

    check(res, {
        'status is 200': (r) => r.status === 200,
        'no pool exhaustion': (r) => !r.body.includes('pool exhausted'),
    });

    if (res.headers['X-Db-Wait-Time']) {
        waitTime.add(parseFloat(res.headers['X-Db-Wait-Time']));
    }
}

Run this test multiple times with different pool sizes and compare results.

Reproducible Benchmark

Here’s a complete benchmark setup you can run yourself to prove the math works.

Setup

// PoolBenchmark.java
@RestController
public class PoolBenchmark {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/query")
    public Map<String, Object> query() {
        long start = System.currentTimeMillis();

        // Simulate typical query
        List<Map<String, Object>> result = jdbcTemplate.queryForList(
            "SELECT pg_sleep(0.015), current_timestamp"  // 15ms query
        );

        long elapsed = System.currentTimeMillis() - start;
        return Map.of("duration_ms", elapsed, "rows", result.size());
    }
}

Test with Different Pool Sizes

# application-pool5.yml
spring:
  datasource:
    hikari:
      maximum-pool-size: 5
      connection-timeout: 1000

# application-pool20.yml
spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      connection-timeout: 1000

# application-pool100.yml
spring:
  datasource:
    hikari:
      maximum-pool-size: 100
      connection-timeout: 1000

Results

Target: 500 RPS, Query duration: 15ms
Theoretical minimum: 500 × 0.015 = 7.5 = 8 connections

Pool Size  | Achieved RPS | p99 Latency | Timeouts | DB CPU
-----------|--------------|-------------|----------|--------
5          | 320          | 890ms       | 36%      | 25%
10         | 498          | 45ms        | 0.1%     | 55%
20         | 502          | 32ms        | 0%       | 62%
50         | 501          | 35ms        | 0%       | 68%
100        | 499          | 42ms        | 0%       | 75%  ← overhead!

Optimum: 10-20 connections for 500 RPS with 15ms queries.

The results match Little’s Law exactly. With 5 connections we can’t keep up—we need at least 8. At 10 connections, we hit target throughput. Going to 20 gives us headroom without significant overhead. Going to 100 actually increases DB CPU usage and latency without improving throughput at all.

HikariCP Configuration

Here’s a production-ready HikariCP configuration based on calculated pool size:

spring:
  datasource:
    hikari:
      # Calculated from Little's Law
      maximum-pool-size: 20

      # Timeout when pool is full
      connection-timeout: 5000  # 5 seconds

      # Idle connection lifetime
      idle-timeout: 600000      # 10 minutes

      # Max connection age (force refresh)
      max-lifetime: 1800000     # 30 minutes

      # Minimum idle (for cold start)
      minimum-idle: 5

      # Leak detection
      leak-detection-threshold: 30000  # 30 seconds

The leak-detection-threshold is particularly valuable—it logs a warning if a connection isn’t returned within 30 seconds, helping you find code that holds connections too long.

PgBouncer Sizing

When using PgBouncer as a connection pooler, you need to think about two pools: the application pool and the PgBouncer-to-database pool.

Transaction Mode

# pgbouncer.ini
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000      # Clients can be many
default_pool_size = 20      # Real connections to DB
reserve_pool_size = 5       # For spikes
reserve_pool_timeout = 3    # When to use reserve

[databases]
mydb = host=postgres port=5432 dbname=mydb

Calculation for PgBouncer

Application pods: 10
HikariCP pool per pod: 20
Total app connections: 200

But not all are active simultaneously!
Active ratio: ~10-20%

PgBouncer pool: 200 × 0.15 = 30 connections to DB

This is the multiplexing benefit of PgBouncer. 200 application connections share 30 database connections, because in transaction mode, connections are released back to the pool between transactions.

Monitoring Pool Health

Set up proper monitoring so you know when your pool sizing is wrong.

HikariCP Metrics

// Prometheus metrics are automatic with Micrometer
// hikaricp_connections_active
// hikaricp_connections_idle
// hikaricp_connections_pending
// hikaricp_connections_timeout_total

# Pool utilization
hikaricp_connections_active / hikaricp_connections_max * 100

# Queue depth (waiting requests)
hikaricp_connections_pending

# Connection wait time
rate(hikaricp_connections_acquire_seconds_sum[5m])
/
rate(hikaricp_connections_acquire_seconds_count[5m])

Alert Rules

groups:
- name: connection_pool
  rules:
  - alert: PoolNearlyExhausted
    expr: |
      hikaricp_connections_active / hikaricp_connections_max > 0.9
    for: 5m
    annotations:
      summary: "Connection pool >90% utilized"

  - alert: HighConnectionWaitTime
    expr: |
      rate(hikaricp_connections_acquire_seconds_sum[5m])
      /
      rate(hikaricp_connections_acquire_seconds_count[5m])
      > 0.1
    for: 5m
    annotations:
      summary: "Average connection wait >100ms"

Common Gotchas

1. Transaction Duration ≠ Query Duration

This is the most common mistake. Little’s Law applies to how long you hold the connection, not how long the query runs.

@Transactional
public void processOrder(Order order) {
    // Query 1: 5ms
    Order saved = orderRepository.save(order);

    // External API call: 500ms !!!
    paymentService.charge(order);

    // Query 2: 5ms
    auditRepository.log(saved);
}
// Connection held 510ms, not 10ms!

If you make external HTTP calls inside a transaction, you’re holding a database connection while waiting for the network. Your pool calculation needs to use transaction duration (510ms), not query duration (10ms).

Solution: Move external communication outside the transaction.

2. N+1 Queries

// 1 query + 100 queries = 101 queries
// Connection held the entire time
List<User> users = userRepository.findAll();  // 1 query
for (User u : users) {
    u.getOrders().size();  // 100 queries (lazy loading)
}

N+1 queries inflate your connection hold time dramatically. A single request might execute hundreds of queries, holding a connection for seconds instead of milliseconds.

3. Long-running Reports

// Report query: 30 seconds
// Blocks 1 connection for 30s
// With 10 concurrent reports = pool exhausted

Solution: Separate pool for reports.

spring:
  datasource:
    hikari:
      pool-name: transactional
      maximum-pool-size: 20

  reports-datasource:
    hikari:
      pool-name: reporting
      maximum-pool-size: 5

This prevents slow reporting queries from starving your transactional workload.

Checklist

## Connection Pool Sizing

### Analysis
- [ ] Measure avg and p99 query duration (pg_stat_statements)
- [ ] Identify peak RPS from load balancer logs
- [ ] Check transaction vs query duration

### Calculation
- [ ] L = λ × W (Little's Law)
- [ ] Add 50% safety margin
- [ ] Don't exceed 2 × DB CPU cores

### Validation
- [ ] Load test with target RPS
- [ ] Monitor pool utilization
- [ ] Track connection wait time

### Monitoring
- [ ] Alert on pool > 90%
- [ ] Alert on wait time > 100ms
- [ ] Dashboard with pool metrics

Conclusion

Connection pool sizing is not guessing:

1. Little’s Law: Pool = RPS × Query Duration - start with the math
2. Bigger pool ≠ better performance - overhead hurts at scale
3. Optimum: 10-20 connections for most workloads
4. Validate with load test, not production

Stop with “pool size 50 because that’s how it’s always been” and start calculating. Your database will thank you with lower CPU usage, less contention, and better throughput.

Related Articles

• K8s PostgreSQL Connection Storm - Connection management in Kubernetes
• PostgreSQL Autovacuum SLO - Database performance tuning
• Database Connection Pool Exhaustion - Debugging pool exhaustion issues