Database connection pooling

suggest change

A connection pool keeps a set of open database connections and lends them out. Your code asks the pool for a connection, uses it for a query or a transaction, and gives it back. The physical connection (socket, authentication, server-side session) stays open and serves the next request.

The JDBC article introduces DataSource and shows a minimal pool. This article goes further: what a pool actually does, how to configure HikariCP, how to pick a pool size, and how to diagnose the usual problems (exhausted pools, leaked connections, session state leaking between borrowers).

The examples use HikariCP, the pool Spring Boot uses by default, and H2 as the database so they run without a server:

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>7.1.0</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.5.252</version>
</dependency>
<!-- HikariCP logs through SLF4J; any binding works -->
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-simple</artifactId>
    <version>2.0.17</version>
</dependency>

The examples were run with JDK 25, each as a single source file:

$ java -cp "HikariCP-7.1.0.jar;h2-2.5.252.jar;slf4j-api-2.0.17.jar;slf4j-simple-2.0.17.jar" Example.java

(Use : instead of ; as the classpath separator on Linux and macOS.) Without an SLF4J binding on the classpath HikariCP still works, but SLF4J prints a "No SLF4J providers were found" warning and you lose the pool's log messages, which are useful.

Why pool connections

Opening a connection to a database server means a TCP handshake, often a TLS handshake, authentication, and the server setting up a session (PostgreSQL even starts a new process per connection). A query that takes a fraction of a millisecond can be dwarfed by the cost of connecting. This program starts an H2 server on a random TCP port and runs the same trivial query 500 times, first opening a new connection each time, then borrowing from a pool:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.h2.tools.Server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;

public class WhyPool {
    static final int N = 500;

    public static void main(String[] args) throws Exception {
        // a real TCP database server on a random free port
        Server server = Server.createTcpServer("-tcpPort", "0", "-ifNotExists").start();
        String url = "jdbc:h2:tcp://localhost:" + server.getPort() + "/mem:shop;DB_CLOSE_DELAY=-1";

        long t0 = System.nanoTime();
        for (int i = 0; i < N; i++) {
            try (Connection c = DriverManager.getConnection(url, "sa", "");
                 Statement st = c.createStatement()) {
                st.execute("SELECT 1");
            }
        }
        long direct = System.nanoTime() - t0;

        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername("sa");
        config.setPassword("");
        config.setMaximumPoolSize(5);
        try (HikariDataSource ds = new HikariDataSource(config)) {
            run(ds); // warm-up: the pool opens its connections
            t0 = System.nanoTime();
            for (int i = 0; i < N; i++) {
                run(ds);
            }
            long pooled = System.nanoTime() - t0;
            System.out.printf("DriverManager: %d queries in %d ms (%.3f ms each)%n",
                    N, direct / 1_000_000, direct / 1e6 / N);
            System.out.printf("HikariCP:      %d queries in %d ms (%.3f ms each)%n",
                    N, pooled / 1_000_000, pooled / 1e6 / N);
        }
        server.stop();
    }

    static void run(DataSource ds) throws SQLException {
        try (Connection c = ds.getConnection(); Statement st = c.createStatement()) {
            st.execute("SELECT 1");
        }
    }
}

Output:

DriverManager: 500 queries in 1848 ms (3.698 ms each)
HikariCP:      500 queries in 125 ms (0.252 ms each)

The exact numbers depend on the machine and vary from run to run, but an order of magnitude difference is typical even for a local server. Against a database on another host, with TLS and password hashing, the cost of a new connection is higher still. Opening connections on demand also puts a burst of load on the database exactly when traffic spikes, and nothing limits how many connections your application opens.

A pool solves both problems: it pays the connection cost once, and its maximum size caps how many connections your application ever uses.

How a pool works

The core idea fits in a few lines. Open N connections up front, keep idle ones in a queue, and hand out a wrapper whose close() puts the connection back instead of closing it:

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.TimeUnit;

// A deliberately minimal pool, to show the idea. Use HikariCP in real code.
public class TinyPool implements AutoCloseable {
    private final BlockingQueue<Connection> idle;

    TinyPool(String url, int size) throws SQLException {
        idle = new ArrayBlockingQueue<>(size);
        for (int i = 0; i < size; i++) {
            idle.add(DriverManager.getConnection(url));   // open physical connections once
        }
    }

    Connection borrow() throws SQLException, InterruptedException {
        Connection real = idle.poll(1, TimeUnit.SECONDS);   // wait for a free one
        if (real == null) throw new SQLException("pool exhausted");
        // hand out a proxy whose close() returns the connection instead of closing it
        return (Connection) Proxy.newProxyInstance(
                Connection.class.getClassLoader(), new Class<?>[]{Connection.class},
                (proxy, method, args) -> {
                    if (method.getName().equals("close")) {
                        idle.add(real);
                        return null;
                    }
                    try {
                        return method.invoke(real, args);
                    } catch (InvocationTargetException e) {
                        throw e.getCause();
                    }
                });
    }

    @Override
    public void close() throws SQLException {
        for (Connection c : idle) c.close();
    }

    public static void main(String[] args) throws Exception {
        try (TinyPool pool = new TinyPool("jdbc:h2:mem:tiny", 2)) {
            for (int i = 1; i <= 4; i++) {
                try (Connection c = pool.borrow();
                     Statement st = c.createStatement();
                     ResultSet rs = st.executeQuery("SELECT SESSION_ID()")) {
                    rs.next();
                    System.out.println("borrow " + i + " -> session " + rs.getInt(1)
                            + ", idle now " + pool.idle.size());
                }
            }
            Connection a = pool.borrow();
            Connection b = pool.borrow();
            try {
                pool.borrow();
            } catch (SQLException e) {
                System.out.println("third borrow: " + e.getMessage());
            }
            a.close();
            b.close();
        }
    }
}

Output:

borrow 1 -> session 3, idle now 1
borrow 2 -> session 4, idle now 1
borrow 3 -> session 3, idle now 1
borrow 4 -> session 4, idle now 1
third borrow: pool exhausted

Only two physical sessions (3 and 4) served four borrowers, and the third simultaneous borrower had to wait and then failed. Because of the proxy, the calling code is ordinary JDBC code with try-with-resources. This is why code written against javax.sql.DataSource works the same with or without a pool.

A real pool has to handle much more than this toy: validating connections that died while idle (the database restarted, a firewall dropped the socket), retiring old connections, resetting state the previous borrower changed, opening connections in the background, detecting connections that are never returned, guarding against double close(), and doing all of it without becoming a lock bottleneck under high concurrency. Use HikariCP (or another mature pool) rather than writing your own.

Configuring HikariCP

You configure a HikariConfig and pass it to HikariDataSource. The data source is the pool: create one per database when the application starts, share it everywhere as a DataSource, and close it on shutdown.

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.Statement;
import java.time.Duration;

public class Config {
    public static void main(String[] args) throws Exception {
        HikariConfig config = new HikariConfig();
        config.setPoolName("orders-db");
        config.setJdbcUrl("jdbc:h2:mem:orders;DB_CLOSE_DELAY=-1");
        config.setUsername("sa");
        config.setPassword("");

        // size: a fixed-size pool (minimumIdle defaults to maximumPoolSize)
        config.setMaximumPoolSize(10);

        // timeouts
        config.setConnectionTimeout(Duration.ofSeconds(5).toMillis());  // max wait in getConnection()
        config.setMaxLifetime(Duration.ofMinutes(25).toMillis());      // retire connections after this
        config.setKeepaliveTime(Duration.ofMinutes(2).toMillis());     // ping idle connections
        config.setValidationTimeout(Duration.ofSeconds(2).toMillis()); // max time for isValid()

        // per-connection defaults; the pool restores them on every return
        config.setAutoCommit(true);
        config.setTransactionIsolation("TRANSACTION_READ_COMMITTED");
        config.setConnectionInitSql("SET TIME ZONE 'UTC'");

        // debugging
        config.setLeakDetectionThreshold(Duration.ofSeconds(30).toMillis());

        try (HikariDataSource ds = new HikariDataSource(config);
             Connection c = ds.getConnection();
             Statement st = c.createStatement()) {
            st.execute("SELECT 1");
            System.out.println("pool:      " + ds.getPoolName());
            System.out.println("isolation: " + c.getTransactionIsolation()
                    + " (READ_COMMITTED = " + Connection.TRANSACTION_READ_COMMITTED + ")");
            System.out.println("total now: " + ds.getHikariPoolMXBean().getTotalConnections());
            Thread.sleep(500);
            System.out.println("total later: " + ds.getHikariPoolMXBean().getTotalConnections());
        }
    }
}

Output (log lines go to stderr):

[main] INFO com.zaxxer.hikari.HikariDataSource - orders-db - Starting...
[main] INFO com.zaxxer.hikari.pool.HikariPool - orders-db - Added connection conn0: url=jdbc:h2:mem:orders user=SA
[main] INFO com.zaxxer.hikari.HikariDataSource - orders-db - Start completed.
pool:      orders-db
isolation: 2 (READ_COMMITTED = 2)
total now: 1
total later: 10
[main] INFO com.zaxxer.hikari.HikariDataSource - orders-db - Shutdown initiated...
[main] INFO com.zaxxer.hikari.HikariDataSource - orders-db - Shutdown completed.

The constructor opens one connection synchronously (so a wrong URL or password fails at startup) and a background thread fills the pool up to its size. That's why the total is 1 at first and 10 a moment later. Give every pool a poolName: it appears in log messages, thread names and metrics, which matters once an application talks to more than one database.

All durations are in milliseconds. The settings you'll actually touch:

Setting Default Meaning
maximumPoolSize 10 Upper limit on connections, idle and in use
minimumIdle same as max Idle connections to keep. HikariCP recommends leaving it unset, which gives a fixed-size pool
connectionTimeout 30000 How long getConnection() waits before throwing SQLTransientConnectionException. Minimum 250
idleTimeout 600000 Close idle connections after this, only if minimumIdle < maximumPoolSize
maxLifetime 1800000 Retire a connection after this long (when it's not in use). Minimum 30000
keepaliveTime 120000 How often to ping idle connections so firewalls and the server don't drop them. Must be less than maxLifetime
validationTimeout 5000 Maximum time for a liveness check (Connection.isValid())
leakDetectionThreshold 0 (off) Log a warning if a connection is held longer than this. Minimum 2000
initializationFailTimeout 1 How long the constructor tries to get the first connection. -1 starts the pool even if the database is down
autoCommit, transactionIsolation, readOnly, schema, catalog driver defaults Default state for every connection, restored when a connection is returned
connectionInitSql none SQL run once on each new physical connection

Set maxLifetime a few seconds or minutes shorter than any connection time limit imposed by the database or the network (for example MySQL's wait_timeout, a proxy's or load balancer's idle timeout). Otherwise the pool may hand out a connection the other side has already closed.

There is no connectionTestQuery in the list above on purpose: modern (JDBC 4) drivers implement Connection.isValid(), which HikariCP uses for validation. Only set a test query for very old drivers.

Driver properties and configuration files

Driver-specific settings are passed through with addDataSourceProperty(name, value). The MySQL article uses it for Connector/J's prepared statement cache and batch rewriting, and the PostgreSQL article for pgjdbc's reWriteBatchedInserts.

Instead of jdbcUrl you can name the driver's DataSource class with dataSourceClassName and configure it with dataSource.* properties. Configuration can also come from a properties file, which keeps credentials and tuning out of the code:

poolName=reports
dataSourceClassName=org.h2.jdbcx.JdbcDataSource
dataSource.url=jdbc:h2:mem:reports;DB_CLOSE_DELAY=-1
dataSource.user=sa
dataSource.password=
maximumPoolSize=4
connectionTimeout=3000
autoCommit=false
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;

public class FromProperties {
    public static void main(String[] args) throws Exception {
        HikariConfig config = new HikariConfig("db.properties");   // file path or classpath resource
        try (HikariDataSource ds = new HikariDataSource(config);
             Connection c = ds.getConnection()) {
            System.out.println(ds.getPoolName() + ": max=" + ds.getMaximumPoolSize()
                    + " autoCommit=" + c.getAutoCommit()
                    + " driver=" + c.getMetaData().getDriverName());
        }
    }
}

Output:

reports: max=4 autoCommit=false driver=H2 JDBC Driver

In Spring Boot you don't create the pool yourself. It builds a HikariDataSource from spring.datasource.* and passes everything under spring.datasource.hikari.* to it:

spring.datasource.url=jdbc:postgresql://localhost:5432/shop
spring.datasource.username=app
spring.datasource.password=secret
spring.datasource.hikari.pool-name=shop-pool
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.connection-timeout=5000
spring.datasource.hikari.max-lifetime=1500000

When the pool runs out

When every connection is in use, getConnection() blocks until one is returned or connectionTimeout passes. Then it throws SQLTransientConnectionException. HikariPoolMXBean shows what the pool is doing:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariPoolMXBean;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLTransientConnectionException;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class Exhausted {
    public static void main(String[] args) throws Exception {
        HikariConfig config = new HikariConfig();
        config.setPoolName("small");
        config.setJdbcUrl("jdbc:h2:mem:ex;DB_CLOSE_DELAY=-1");
        config.setMaximumPoolSize(2);
        config.setConnectionTimeout(500);   // ms; the minimum allowed is 250

        try (HikariDataSource ds = new HikariDataSource(config);
             ExecutorService exec = Executors.newVirtualThreadPerTaskExecutor()) {
            HikariPoolMXBean mx = ds.getHikariPoolMXBean();
            CountDownLatch holding = new CountDownLatch(2);
            CountDownLatch release = new CountDownLatch(1);

            // two slow tasks take both connections and keep them
            for (int i = 0; i < 2; i++) {
                exec.submit(() -> {
                    try (Connection c = ds.getConnection()) {
                        holding.countDown();
                        release.await();
                    }
                    return null;
                });
            }
            holding.await();
            System.out.printf("active=%d idle=%d total=%d waiting=%d%n",
                    mx.getActiveConnections(), mx.getIdleConnections(),
                    mx.getTotalConnections(), mx.getThreadsAwaitingConnection());

            long t0 = System.nanoTime();
            try (Connection c = ds.getConnection()) {
                System.out.println("got a connection?!");
            } catch (SQLTransientConnectionException e) {
                System.out.printf("after %d ms: %s%n", (System.nanoTime() - t0) / 1_000_000,
                        e.getMessage());
            }

            release.countDown();   // slow tasks finish and return their connections
            Thread.sleep(100);
            try (Connection c = ds.getConnection()) {
                System.out.printf("retry ok, active=%d idle=%d%n",
                        mx.getActiveConnections(), mx.getIdleConnections());
            }
        }
    }
}

Output:

[main] INFO com.zaxxer.hikari.HikariDataSource - small - Starting...
[main] INFO com.zaxxer.hikari.pool.HikariPool - small - Added connection conn0: url=jdbc:h2:mem:ex user=
[main] INFO com.zaxxer.hikari.HikariDataSource - small - Start completed.
active=2 idle=0 total=2 waiting=0
after 505 ms: small - Connection is not available, request timed out after 502ms (total=2, active=2, idle=0, waiting=0)
retry ok, active=1 idle=1
[main] INFO com.zaxxer.hikari.HikariDataSource - small - Shutdown initiated...
[main] INFO com.zaxxer.hikari.HikariDataSource - small - Shutdown completed.

The exception message includes the pool's state, which is often enough to diagnose a problem from a log file. active equal to total with a growing waiting count means requests hold connections too long or there are too many of them. Common causes:

The default 30-second connectionTimeout is long for a web request. A shorter one (a few seconds) makes an overloaded application fail fast with a clear error instead of piling up waiting requests.

ds.getHikariPoolMXBean() works without any setup. Set registerMbeans to true to also publish the pool's counters over JMX, or call setMetricsTrackerFactory() to feed them to Micrometer, Dropwizard Metrics or Prometheus. Spring Boot does the Micrometer wiring automatically. Graph active connections, waiting threads and connection acquisition time in production.

Detecting connection leaks

A connection that is borrowed and never closed is lost to the pool for good. Leak a few and the pool is exhausted. The usual cause is a code path, often an exception path, that skips close(). leakDetectionThreshold makes HikariCP log a warning, with the stack trace of the code that borrowed the connection, when a connection has been out longer than the threshold:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class Leak {
    static HikariDataSource ds;

    // BUG: the connection is never closed when the query succeeds
    static int countUsers() throws SQLException {
        Connection c = ds.getConnection();
        Statement st = c.createStatement();
        var rs = st.executeQuery("SELECT COUNT(*) FROM INFORMATION_SCHEMA.USERS");
        rs.next();
        return rs.getInt(1);
    }

    public static void main(String[] args) throws Exception {
        HikariConfig config = new HikariConfig();
        config.setPoolName("leaky");
        config.setJdbcUrl("jdbc:h2:mem:leak;DB_CLOSE_DELAY=-1");
        config.setMaximumPoolSize(3);
        config.setLeakDetectionThreshold(2000);   // ms; the minimum is 2000

        ds = new HikariDataSource(config);
        System.out.println("users: " + countUsers());
        Thread.sleep(2500);
        System.out.println("active after 2.5 s: " + ds.getHikariPoolMXBean().getActiveConnections());
        ds.close();
    }
}

Output:

[main] INFO com.zaxxer.hikari.HikariDataSource - leaky - Starting...
[main] INFO com.zaxxer.hikari.pool.HikariPool - leaky - Added connection conn0: url=jdbc:h2:mem:leak user=
[main] INFO com.zaxxer.hikari.HikariDataSource - leaky - Start completed.
users: 1
[leaky:housekeeper] WARN com.zaxxer.hikari.pool.ProxyLeakTask - Connection leak detection triggered for conn0: url=jdbc:h2:mem:leak user= on thread main, stack trace follows
java.lang.Exception: Apparent connection leak detected
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:99)
	at Leak.countUsers(Leak.java:13)
	at Leak.main(Leak.java:28)
	...
active after 2.5 s: 1
[main] INFO com.zaxxer.hikari.HikariDataSource - leaky - Shutdown initiated...
[main] INFO com.zaxxer.hikari.HikariDataSource - leaky - Shutdown completed.

The stack trace points straight at countUsers, line 13. The warning doesn't take the connection away: it's still active. If the connection is eventually closed, HikariCP logs "Previously reported leaked connection ... was returned to the pool (unleaked)", which means it was a slow operation rather than a real leak. Set the threshold well above your longest legitimate transaction, or you'll get false alarms.

The fix is always the same: open every Connection, Statement and ResultSet in try-with-resources.

What the pool resets, and what it doesn't

A pooled connection carries state from one borrower to the next. HikariCP tracks changes made through the JDBC API and restores them when the connection is returned: auto-commit, transaction isolation, read-only, catalog, schema and network timeout. If a borrower turned off auto-commit and returned the connection with uncommitted work, HikariCP rolls it back. State changed with SQL is not tracked:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class StateReset {
    public static void main(String[] args) throws SQLException {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:h2:mem:state;DB_CLOSE_DELAY=-1");
        config.setMaximumPoolSize(1);   // so we always get the same physical connection

        try (HikariDataSource ds = new HikariDataSource(config)) {
            try (Connection c = ds.getConnection(); Statement st = c.createStatement()) {
                st.execute("CREATE TABLE items (name VARCHAR(20))");
            }

            // 1st borrower: changes settings and "forgets" to commit
            try (Connection c = ds.getConnection(); Statement st = c.createStatement()) {
                c.setAutoCommit(false);
                c.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                st.execute("INSERT INTO items VALUES ('uncommitted')");
                st.execute("SET @user_id = 42");   // session variable
            }

            // 2nd borrower gets the same connection
            try (Connection c = ds.getConnection(); Statement st = c.createStatement()) {
                System.out.println("autoCommit: " + c.getAutoCommit());
                System.out.println("isolation:  " + c.getTransactionIsolation());
                try (ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM items")) {
                    rs.next();
                    System.out.println("rows:       " + rs.getInt(1));
                }
                try (ResultSet rs = st.executeQuery("SELECT @user_id")) {
                    rs.next();
                    System.out.println("@user_id:   " + rs.getObject(1));
                }
            }
        }
    }
}

Output:

autoCommit: true
isolation:  2
rows:       0
@user_id:   42

Auto-commit and isolation are back to the pool defaults and the uncommitted insert was rolled back, but the session variable set with SQL survived into the next borrower's session. The same goes for temporary tables, session settings changed with SET (time zone, search_path in PostgreSQL, sql_mode in MySQL), advisory locks, and anything else the database keeps per session. Rules that follow from this:

Choosing the pool size

The intuitive answer, "one connection per thread" or "as many as we have users", is wrong. A database server can only execute as many queries in parallel as it has CPU cores and disks to run them. Extra connections just compete for the same resources, add context switching and lock contention, and consume memory on the server. Past a point, a bigger pool makes every query slower.

HikariCP's documentation ("About Pool Sizing") gives a starting point for the pool size of a single application talking to a single database:

connections = (core_count * 2) + effective_spindle_count

core_count is the number of cores on the database server (not the application server), and effective_spindle_count is roughly the number of disks that can seek in parallel (with SSDs and a dataset that fits in memory, it approaches 0). Treat this as the first value to try, then measure under realistic load. Many applications run well with 10 to 20 connections.

The database has a global limit too: PostgreSQL's max_connections and MySQL's max_connections cover all clients combined. Ten instances of a service with a pool of 20 each need 200 connections. Running many instances, or serverless functions with their own pools, is where a server-side pooler such as PgBouncer (for PostgreSQL) or ProxySQL (for MySQL) helps.

Virtual threads and small pools

Virtual threads make it cheap to have thousands of concurrent requests, but they don't make the database any bigger. A small pool works fine with many virtual threads: those that can't get a connection simply wait in getConnection(), without blocking a platform thread:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariPoolMXBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.atomic.AtomicInteger;

public class VirtualThreads {
    public static void main(String[] args) throws Exception {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:h2:mem:vt;DB_CLOSE_DELAY=-1");
        config.setMaximumPoolSize(10);

        try (HikariDataSource ds = new HikariDataSource(config)) {
            HikariPoolMXBean mx = ds.getHikariPoolMXBean();
            AtomicInteger maxWaiting = new AtomicInteger();
            AtomicInteger maxActive = new AtomicInteger();
            AtomicInteger done = new AtomicInteger();

            long t0 = System.nanoTime();
            try (ExecutorService exec = Executors.newVirtualThreadPerTaskExecutor()) {
                for (int i = 0; i < 1000; i++) {
                    int id = i;
                    exec.submit(() -> {
                        try (Connection c = ds.getConnection();
                             PreparedStatement ps = c.prepareStatement("SELECT ? * 2")) {
                            maxActive.accumulateAndGet(mx.getActiveConnections(), Math::max);
                            maxWaiting.accumulateAndGet(mx.getThreadsAwaitingConnection(), Math::max);
                            ps.setInt(1, id);
                            try (ResultSet rs = ps.executeQuery()) {
                                rs.next();
                            }
                            Thread.sleep(10);   // pretend the query takes 10 ms
                            done.incrementAndGet();
                        }
                        return null;
                    });
                }
            }
            long ms = (System.nanoTime() - t0) / 1_000_000;
            System.out.println("requests done:       " + done.get());
            System.out.println("max active conns:    " + maxActive.get());
            System.out.println("max waiting threads: " + maxWaiting.get());
            System.out.println("elapsed:             " + ms + " ms");
        }
    }
}

Output:

requests done:       1000
max active conns:    10
max waiting threads: 998
elapsed:             1273 ms

A thousand virtual threads shared ten connections. The pool acted as a limiter: never more than ten queries ran against the database at once, and the elapsed time is what you'd expect for 1000 jobs of 10 ms through 10 lanes. That's exactly what you want: the database stays within its capacity and requests queue in the application, where waiting is cheap. Size the pool for the database, not for the number of threads.

Starting when the database is down

By default new HikariDataSource(config) fails if it can't open the first connection, which surfaces configuration mistakes immediately. In environments where the application may start before the database (containers starting in parallel), set initializationFailTimeout to -1: the pool starts empty and keeps trying in the background, and getConnection() fails with a timeout until the database is reachable:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.pool.HikariPool.PoolInitializationException;

import java.sql.Connection;
import java.sql.SQLException;

public class DbDown {
    static HikariConfig config(long initFailTimeout) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:h2:tcp://localhost:1/mem:nothing-listens-here");
        config.setConnectionTimeout(1000);
        config.setInitializationFailTimeout(initFailTimeout);
        return config;
    }

    public static void main(String[] args) {
        // default (1 ms): fail fast if the first connection can't be opened
        try (HikariDataSource ds = new HikariDataSource(config(1))) {
            System.out.println("started");
        } catch (PoolInitializationException e) {
            System.out.println("startup failed: " + e.getCause().getClass().getSimpleName());
        }

        // -1: start anyway; getConnection() fails until the database is reachable
        try (HikariDataSource ds = new HikariDataSource(config(-1))) {
            System.out.println("started without a database");
            try (Connection c = ds.getConnection()) {
                System.out.println("connected");
            } catch (SQLException e) {
                System.out.println(e.getClass().getSimpleName() + ": " + e.getMessage());
            }
        }
    }
}

Output:

startup failed: JdbcSQLNonTransientConnectionException
started without a database
SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 1001ms (total=0, active=0, idle=0, waiting=0)

HikariPool-2 is the generated name for an unnamed pool, one more reason to set poolName. Once the database comes up, the pool fills and getConnection() starts succeeding without a restart. The same happens after a database restart at run time: connections that fail validation are thrown away and replaced.

Pooling and embedded databases

With an embedded database like SQLite or H2 in embedded mode there's no network or authentication, so opening a connection is cheap and the main reason for pooling disappears. Pooling can still be useful for setup cost (pragmas run once per connection via connectionInitSql) and to limit concurrency. SQLite allows only one writer at a time, so many connections writing in parallel just wait on each other's locks. A pool of size 1 for writes (and, in WAL mode, a separate pool for reads) is a common arrangement.

Be careful with in-memory databases. A named H2 in-memory database (jdbc:h2:mem:shop) is shared by all connections, but by default it's deleted when the last connection closes. That's why the examples add DB_CLOSE_DELAY=-1: otherwise retiring connections (maxLifetime) could close all of them at once and the data would disappear. SQLite's jdbc:sqlite::memory: is worse for pooling: every connection gets its own private, empty database, so each borrower would see different data. Use a file, or a shared-cache URI, with a pool.

Other pools

HikariCP is the default choice, but you may meet others:

Pool Notes
HikariCP Small, fast, well-maintained. Default in Spring Boot
Apache Commons DBCP2 org.apache.commons:commons-dbcp2. Older, many configuration knobs (maxTotal, testOnBorrow and more)
Tomcat JDBC Pool Ships with Tomcat, used for container-managed (JNDI) data sources
Agroal Used by Quarkus
c3p0 Legacy, still found in older Hibernate setups

All of them implement javax.sql.DataSource, so application code doesn't change when you switch. Drivers also ship DataSource classes (org.postgresql.ds.PGSimpleDataSource, com.mysql.cj.jdbc.MysqlDataSource, org.h2.jdbcx.JdbcDataSource). Most of these are not pools: every getConnection() opens a new physical connection. They're meant to be wrapped by a pool, which is exactly what dataSourceClassName does.

Best practices

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:



Table Of Contents