sdlc.dev

Backend engineer path · Data and tests · Topic 11 of 18

The connection pool (HikariCP)

Not started

Opening a database connection means a TCP handshake, often TLS, authentication and session set-up: milliseconds that you don't want to pay on every request. A connection pool opens a fixed number of connections up front and lends them out. Spring Boot picks HikariCP when it is on the classpath, which it is with any JDBC or JPA starter. Every transaction, and every repository call outside one, borrows a connection and gives it back when it finishes.

Why it matters: The pool is the narrowest pipe in most Spring services; too small and requests queue, too large and the database slows down for everyone.

  1. 1Borrow, use, return

    Every transaction borrows one connection from the pool and returns it at commit or rollback. Opening a new one per request would cost far more.

  2. 2The defaults

    Boot uses HikariCP with maximumPoolSize 10, a fixed-size pool, and connectionTimeout 30 seconds.

  3. 3Bigger is not faster

    A database does a limited amount of work at once; more connections than it can serve add contention, not throughput.

  4. 4Watch the waiting

    Pending threads and acquire time tell you whether the pool, or the code holding connections, is the problem.

1

How it works

The explanation, with examples you can run.

The settings that matter

spring:  datasource:    url: jdbc:postgresql://db:5432/shop    hikari:      maximum-pool-size: 10      # default 10; also the size, since minimum-idle defaults to it      connection-timeout: 2000   # ms; default 30000: how long a caller waits for a free connection      max-lifetime: 1500000      # ms (25 min); default 30 min: keep it below any database or proxy cut-off      pool-name: shop-db

Hikari's own advice is to leave minimum-idle unset, so the pool stays at a fixed size and never has to open connections during a spike. max-lifetime retires connections before a firewall or the database drops them silently; it should be a little shorter than any such limit.

How big?

It is tempting to match the pool to Tomcat's 200 threads. Hikari's sizing page argues the opposite. A database server with 4 cores can only execute a handful of queries truly at once; past that, extra connections add context switching and lock contention. Its starting formula is:

connections = (core_count x 2) + effective_spindle_count

core_count is the database server's physical cores, and effective_spindle_count is about 0 when the working set is cached (and has not been analysed for SSDs). For 4 cores that gives roughly 9 or 10, which is why 10 is a reasonable default. Treat it as a starting point and measure.

Remember the pool is per application instance. Ten replicas with 10 connections each is 100 connections on the database, and its max_connections is shared by everyone.

When the pool is the bottleneck

Usually it is not the size but how long connections are held:

  • A transaction that calls a slow HTTP service keeps its connection the whole time.
  • Open-session-in-view holds one until the response is written.
  • REQUIRES_NEW needs a second connection while the first is still held. With every thread doing it at once, the pool can lock up completely; the Spring docs say such a pool must exceed the number of concurrent threads.

Seeing it

With Actuator, Boot publishes pool metrics with the hikaricp prefix (and generic jdbc.connections.* gauges):

MetricTells you
hikaricp.connections.activeConnections in use now
hikaricp.connections.pendingThreads waiting for one
hikaricp.connections.acquireHow long they waited
hikaricp.connections.timeoutCallers that gave up

A pending count above zero most of the time means requests are queuing for the database. Shorten what holds connections before raising the size, and set a connection-timeout that fails fast rather than parking threads for 30 seconds.

2

Check yourself

3 questions. Get them all right to finish this topic.

  1. 1.All 10 connections are in use and an eleventh request needs one. What happens with Boot's defaults?
  2. 2.Using HikariCP's starting formula, roughly how many connections suit a database server with 4 physical cores and its data set cached in memory?
  3. 3.Each request thread holds a connection and then, through REQUIRES_NEW, asks for a second one. With 10 threads and a pool of 10, what can happen?

0 of 3 right so far. Get all three to finish this topic.

Go deeper