Connection Pooling

HikariCP sizing, pool exhaustion, and why opening a connection per request kills throughput.

Intermediate9 min read

Read these first

Advanced SQL Patterns

Why this matters

Opening a Postgres connection takes 20–50ms; at 1000 RPS that overhead alone exceeds query time.
Pool exhaustion — every connection busy, requests queue — is a classic production outage pattern.
HikariCP is Spring Boot's default pool; misconfigured maximumPoolSize starves or overwhelms Postgres.
Interview system-design questions pair pool sizing with thread pool sizing and database max_connections.

Clustered (PK)

Table rows (sorted by PK)

Row 1id=1

Row 2id=2

Row 3id=3

Non-clustered (email)

email index→ row pointer

Table heapunordered rows

A clustered index sorts table rows by key. Non-clustered indexes point to row locations.

Why connections are expensive

Each Postgres connection spawns a backend process consuming ~5–10 MB RAM. The handshake involves TCP, TLS, authentication, and session setup:

Java

// Anti-pattern: new connection per request
Connection conn = DriverManager.getConnection(url, user, pass);
// ... query ...
conn.close();

VaultCommerce's early prototype did this under load — Postgres hit max_connections (100) within minutes of a marketing email blast.

How pooling works

The pool maintains warm connections. Application threads borrow, execute SQL, and return:

Pool lifecycle

Startup — Pool opens minimumIdle connections.
Borrow — Thread requests connection; pool assigns idle one or waits (up to timeout).
Use — Thread runs queries on the borrowed connection.
Return — Connection goes back to idle queue, not closed.
Eviction — Stale or broken connections are replaced.

HikariCP configuration

Spring Boot auto-configures HikariCP when spring-boot-starter-jdbc is on the classpath:

Java

spring:
  datasource:
    url: jdbc:postgresql://db.vaultcommerce.internal:5432/vaultcommerce
    username: order_service
    hikari:
      pool-name: vault-order-pool
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000      # ms to wait for a free connection
      idle-timeout: 600000           # retire idle connections after 10 min
      max-lifetime: 1800000          # recycle connections every 30 min

VaultCommerce runs eight order-service pods × 20 connections = 160 total — must stay below Postgres max_connections minus admin and replica slots.

Sizing formula

A starting heuristic: pool_size ≈ (core_count * 2) + spindle_count. VaultCommerce's 4-vCPU pods use 15–25 connections for short OLTP queries. Validate with metrics:

Signals to tune

Threads awaiting connection — Pool too small or queries too slow.
Idle connections always at max — Pool may be oversized; wasted RAM on both sides.
pg_stat_activity count — Total sessions should match expected aggregate pool size.

Java

SELECT count(*), state
FROM pg_stat_activity
WHERE datname = 'vaultcommerce'
GROUP BY state;

Pool exhaustion symptoms

When every connection is in use, HikariCP throws SQLTransientConnectionException after connection-timeout. Tomcat threads block waiting — API latency spikes, circuit breakers trip, customers see timeouts.

Root causes at VaultCommerce:

Long transactions holding connections during external payment API calls.
Missing index causing 30-second table scans.
Connection leak — borrowed connection never returned (rare with try-with-resources, common with manual JDBC).

JdbcTemplate and JPA both draw from the same pooled DataSource bean — one pool per application instance.

Quick recall

Everything you need if you only revisit this box.

Connection pools reuse warm Postgres sessions and eliminate per-request handshake overhead.
HikariCP is Spring Boot default; configure maximum-pool-size per instance, not globally.
Total connections = pod count × pool size — must stay under Postgres max_connections.
Pool exhaustion causes timeouts; fix slow queries and long transactions, not just pool size.
Route heavy reports to read replicas with a dedicated smaller pool.

Test yourself

Answer these before moving on — recall is what makes it stick.