Connection Pooling: HikariCP, PgBouncer and Pool Sizing

In one line

Database connections are expensive (a PostgreSQL connection is a process with several MB of memory), so applications reuse them through a pool. Size pools small (throughput peaks at a few connections per CPU core), set connection and statement timeouts, detect leaks, and put PgBouncer in front when many application instances would otherwise exceed max_connections.

0/5 · 0%

Think of it like this

A bank with 8 counters. Letting 500 customers crowd the counters doesn't serve anyone faster; a queue in front of 8 counters does. The database is the counters; the pool is the queue.

Key ideas

01
PostgreSQL forks a backend process per connection; each uses memory and contends for locks and CPU. Hundreds of active connections cause context switching and lock contention that reduce throughput.
02
Pool sizing starting point: connections ≈ (CPU cores × 2) + effective spindles for the database, split across all application instances. 20 app pods × 10 connections = 200, often too many for a 16-core database; lower per-pod pools or add PgBouncer.
03
HikariCP settings: maximumPoolSize (fixed size is recommended: min = max), connectionTimeout (how long a request waits for a connection; fail fast, e.g. 2–5 s), maxLifetime (retire connections before network or database timeouts, e.g. 30 min), idleTimeout, leakDetectionThreshold (log stack traces for connections held too long).
04
PgBouncer transaction pooling: thousands of client connections share a few dozen server connections, each assigned only for the duration of a transaction. Session state (SET, advisory session locks, temp tables, some prepared statement modes) doesn't survive across transactions; PgBouncer 1.21+ supports protocol-level prepared statements.
05
Pool saturation shows as rising hikaricp_connections_pending and connection timeouts while the database itself looks idle, usually caused by slow external calls inside transactions or leaks.

Code & diagrams

application.ymlyaml

spring:
  datasource:
    url: jdbc:postgresql://pgbouncer:6432/app
    hikari:
      maximum-pool-size: 10          # per instance; total = instances x 10
      minimum-idle: 10
      connection-timeout: 3000       # ms to wait for a free connection, then fail fast
      max-lifetime: 1800000          # 30 min, below any LB/firewall idle cut-off
      leak-detection-threshold: 20000
      data-source-properties:
        options: "-c statement_timeout=5000 -c idle_in_transaction_session_timeout=60000"

pgbouncer.iniini

[databases]
app = host=10.0.1.5 port=5432 dbname=app

[pgbouncer]
listen_port = 6432
pool_mode = transaction
; server connections per db/user pair
default_pool_size = 40
max_client_conn = 5000
; protocol-level prepared statements (1.21+)
max_prepared_statements = 200
server_idle_timeout = 300

Interview problem

The problem

Connection storms after autoscaling

An API autoscales from 20 to 120 pods at peak, each with a Hikari pool of 30. PostgreSQL has max_connections = 500. At peak, new pods fail with "too many clients" and latency rises across the board. Fix the design.

When it breaks

Connection leak

What you see

A code path doesn't close a connection on an exception; the pool slowly drains until every request waits for connectionTimeout and fails.

Fix & prevent

Use try-with-resources or framework-managed transactions; enable leakDetectionThreshold; alert on active connections at the pool maximum.

Explain it without notes

Why can a bigger pool make throughput worse?

Practice

Calculate per-pod pool size for a 16-core database, 24 app pods, target ~40 active DB connections, with no PgBouncer.

Trade-offs

↔
Transaction pooling maximises connection reuse but breaks session-level features; session pooling is compatible but reuses less.

Done when you can

I can size pools, configure timeouts and leak detection, and decide when PgBouncer is needed.

Command Palette