Connection pooling

A database connection is expensive to open and cheap to reuse, so every service keeps a pool of them. The pool is also the narrowest point in the whole system: ten connections means ten queries in flight, whatever the thread count, and when a slow query holds one, everything behind it waits. Most "the database is slow" incidents are pool incidents. This lesson is why the pool exists, how to size it, the timeouts that turn a hang into an error, and the two ways a pool gets exhausted.

#Why pool

Opening a PostgreSQL connection forks a backend process, negotiates TLS, authenticates, and allocates memory (several megabytes per backend); MySQL is lighter but still a handshake and a thread. That is tens of milliseconds and real server resources per connection, for a query that takes two. A pool opens N connections once and hands them out per unit of work: a request borrows one, runs its statements, returns it.

Spring Boot's default pool is HikariCP, and its defaults are what most services run:

#Sizing: fewer than you think

The instinct is that more connections mean more throughput. Past a small number it is the opposite. A database server has a fixed number of cores and disks; a hundred connections running queries at once do not run a hundred queries in parallel, they context-switch, contend for the same buffers and locks, and each finishes later. HikariCP's own documentation gives the formula that came out of measuring this:

connections = cores × 2 + effective_spindle_count

For a database on eight cores with SSDs that is roughly sixteen to twenty connections, for the whole database, across every service that talks to it. A pool of 10 per service, with twelve service instances, is 120 connections against a server that does its best work at twenty. When a request needs more connections than that, the fix is a queue in front of the database, not a bigger pool, and that queue is exactly what the pool's wait already is.

Then look at what the connection does while it is borrowed. Little's law: connections in use = request rate × time each holds a connection. 200 requests per second holding a connection for 40 ms is eight connections; the same rate holding one for 400 ms is eighty, and the pool of ten is exhausted with seventy requests waiting. Shortening the hold time is worth ten times more than adding connections.

#What holds a connection

A connection is borrowed for as long as the unit of work that took it, and the unit of work is usually a Spring @Transactional method. Everything inside that method holds the connection, including:

• an HTTP call to another service, at that service's latency;
• a Kafka send() with acknowledgement;
• a slow query, or a lock wait behind someone else's slow query;
• a lazy-loaded collection being walked, one query at a time;
• and with spring.jpa.open-in-view=true, the entire request, from controller entry to response written, because the persistence context stays open and so does its connection.

Every one of these is a reason to make the transaction smaller: call out first, then transact; publish after commit (@TransactionalEventListener(phase = AFTER_COMMIT)); turn open-in-view off.

#Timeouts

Three timeouts, at three layers, and a service needs all of them:

spring:
  datasource:
    hikari:
      connection-timeout: 3000        # ms to wait for a free connection; 30 s default is a hung request
      max-lifetime: 1800000
      keepalive-time: 300000
      leak-detection-threshold: 20000 # log any connection held over 20 s
    url: jdbc:postgresql://db/app?socketTimeout=30&connectTimeout=5
  jpa:
    open-in-view: false

• connection-timeout bounds the wait for the pool. When it fires, getConnection() throws SQLTransientConnectionException: ... request timed out after 3000ms, the request fails fast with a 503, and the caller can retry elsewhere. At the default 30 seconds every request during an incident hangs for half a minute and then fails, which is worse than failing at once.
• socketTimeout (JDBC URL) bounds a single statement's network wait, so a query that never returns does not hold the connection forever.
• statement_timeout on the server (SET statement_timeout = '10s' per session, or in the connection init SQL) makes the database itself cancel a runaway query, which also frees the locks it holds.

Without these, one hung query becomes one hung connection, becomes a pool that drains as requests queue for it, becomes every thread in Tomcat blocked in getConnection(), becomes a health check that fails, becomes a restart that reconnects everything at once.

#Leak detection

A leak is a connection borrowed and never returned: a code path that gets a Connection or an EntityManager by hand and returns early on an exception. The pool shrinks by one on each occurrence until it is empty, hours or days later, with no error at the leak site. leak-detection-threshold logs the stack trace of any connection held longer than the threshold, which points straight at the method. Set it to a value above your slowest legitimate transaction, and treat every log line as a bug.

#Two exhaustion incidents

The slow query. A report query with a missing index starts taking 40 seconds after a table grows. Ten requests hit it; ten connections are held for 40 seconds; every other request, including the ones that would take 5 ms, waits in getConnection() and times out. The symptom is "everything is slow", the cause is one query, and pg_stat_activity (or SHOW PROCESSLIST) shows it: nine connections running the same statement. Fix the index; add statement_timeout so it cannot recur.

The external call inside the transaction. A checkout service calls the payment gateway inside @Transactional. The gateway degrades to 8-second responses. Each checkout holds a connection for 8 seconds; at 3 checkouts per second that is 24 connections needed against a pool of 10; the pool is exhausted in seconds, the order lookup endpoint that shares the pool starts failing, and the incident looks like a database outage. The database was idle. The fix is the shape: reserve, commit, call the gateway, then a second short transaction to record the result.

#When the pool is not enough: PgBouncer

Many services each with a pool of ten add up to hundreds of server connections. PostgreSQL in particular pays per connection in memory and in planning overhead. PgBouncer in transaction-pooling mode multiplexes them: services keep their pools, PgBouncer holds a small pool of real connections and assigns one to a client only for the duration of a transaction. The trade: session-level state (prepared statements by name, SET variables, advisory locks) does not survive across transactions, so the JDBC driver needs prepareThreshold=0 or PgBouncer 1.21+ with protocol-level prepared statement support. MySQL's equivalent is ProxySQL. Neither replaces sizing the application pools sanely; they cap the damage when there are many of them.

#Under the hood: what a pooled connection is, and what getConnection does

A JDBC Connection from Hikari is a thin proxy (ProxyConnection, generated at build time with Javassist for speed) around the driver's real connection, which is a socket, a TLS session, and on the server side a process (PostgreSQL forks one backend per connection, each with its own memory and catalog caches) or a thread (MySQL). getConnection() takes the pool's lock-free ConcurrentBag: it first checks a ThreadLocal list of connections this thread returned recently (so a request thread tends to get "its" connection back, warm in the CPU cache), then the shared list, and if nothing is free it waits on a handoff queue for up to connection-timeout, during which a housekeeper thread may add a connection if the pool is below maximum-pool-size. Before handing one out it checks isValid() only if the connection has been idle longer than aliveBypassWindow (500 ms), which is why a healthy pool's borrow costs microseconds and a pool full of stale sockets costs a round trip each.

close() on the proxy does not close anything. It resets the connection to a known state and returns it to the bag: rolls back if there was an open transaction, restores auto-commit, isolation level, read-only flag, catalog and network timeout if any were changed (ProxyConnection tracks a dirty bit per setting), and closes any Statement the code left open. Anything the reset does not cover leaks to the next borrower: session variables set with SET (a search_path, a statement_timeout set by hand, a MySQL SET @user_id), temporary tables, advisory locks, and named prepared statements. That is why "run this SET on borrow" belongs in connection-init-sql, which Hikari runs once when the physical connection is created, and why a SET LOCAL inside the transaction is the safe per-request form.

The three timeouts sit at three layers of that stack. connection-timeout is the bag's handoff wait, purely client-side. socketTimeout on the driver URL is Socket.setSoTimeout: the longest the client will block in read() waiting for the server's next packet, which catches a dead server or a network partition, but also kills a legitimately long query with a SocketTimeoutException that leaves the server still running it. statement_timeout is server-side: the backend cancels its own query, releases its locks, and returns an error over a healthy socket, which is why it is the one that frees resources rather than abandoning them. max-lifetime is the fourth, quieter one: it retires connections on a jittered schedule so that a load balancer's or PgBouncer's own idle limit never surprises the pool, and so a server-side memory leak per backend cannot grow forever.

#Walkthrough: the pool that emptied one connection a day

A service's hikaricp.connections.active gauge climbed by one every day or so and never came down, until getConnection started timing out three weeks after a deploy.

public void export(long id, OutputStream out) throws IOException {
    var conn = dataSource.getConnection();                 // borrowed by hand, not via JdbcTemplate
    try (var ps = conn.prepareStatement(BIG_QUERY)) {
        ps.setLong(1, id);
        writeCsv(ps.executeQuery(), out);                  // throws IOException when the client disconnects
    } finally { /* nothing */ }
}

1. The code borrowed a connection directly and closed the statement in a try-with-resources, but never closed the connection. On the happy path the connection stayed borrowed forever anyway; the gauge climbed on every export. On an IOException (a browser closing the download) the same happened, which is why the leak rate tracked usage, one or two per day.
2. No transaction was left open, since auto-commit was on, so nothing showed up as idle in transaction. The server-side backend simply stayed alive with its cursor, the socket stayed open, and Hikari counted the connection as active. pg_stat_activity showed the leaked sessions as idle, with query still set to the export statement and backend_start matching the export log.
3. leak-detection-threshold had been left at 0. Set to 60 s in staging, the next export logged Connection leak detection triggered for conn ... on thread ... , stack trace follows, pointing at line 2. The threshold is a debugging tool, not a fix: it logs and does nothing else.
4. The fix was try (var conn = dataSource.getConnection(); var ps = ...), and a review rule: getConnection() appears nowhere in application code; JdbcTemplate, JdbcClient or @Transactional borrow and return on your behalf, including on exceptions.
5. The team also asked whether max-lifetime would have contained it, and it would not: the pool retires only connections that are idle in the bag, and a borrowed connection is retired when it is returned. The lifetime setting protects against server-side surprises, not application bugs. It was set to 30 minutes anyway, for the load balancer's sake.

A leak is a borrow with no return path on some branch, and close() is the return. The pool cannot reclaim what it has handed out.

#Try it yourself

#Misconceptions

• "close() closes the connection." On a pooled proxy it resets the JDBC-level state and returns the physical connection to the bag; server-side session state persists to the next borrower.
• "max-lifetime will clean up a leak." It retires idle connections in the bag. A borrowed connection is never retired until returned.
• "socketTimeout cancels the query." It abandons the socket; the server keeps running the statement and holding its locks. Only statement_timeout cancels server-side.
• "A bigger pool is more capacity." Beyond the database's parallelism it is a longer queue with more contention. Hold time is the lever.
• "One pool per service is right." One pool per workload class is often right: reports and transactions should not share a queue.

#Going deeper

• HikariCP wiki: "About Pool Sizing", "Configuration" (every property with its rationale) and "Bad Behavior: Handling Database Down".
• HikariCP source, ConcurrentBag and ProxyConnection, for the borrow path and the reset-on-close list.
• PostgreSQL manual, "Client Connection Defaults" (statement_timeout, idle_in_transaction_session_timeout) and the JDBC driver's connection parameters (socketTimeout, connectTimeout, prepareThreshold).
• PgBouncer documentation, "Features" and the prepared-statements section for 1.21+.
• Brett Wooldridge, "Down the Rabbit Hole" (HikariCP blog), on why the pool is lock-free.