LOW LEVEL DESIGN · MACHINE CODING · MEDIUM · 16 OF 42

Design a Database Connection Pool: Low-Level Design with Java Code

A bounded database connection pool designed and coded: min and max size, borrow with a timeout using a fair semaphore, a warm-first idle deque, validation on borrow and on return, a lease proxy whose close() gives the connection back, idle eviction down to the minimum, leak detection, permit safety on failure, a thread pool executor as the follow-up, and full Java 17 code.

Patterns usedFactory methodObserverProxy

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
  16. 16
  17. 17
  18. 18
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
  24. 24
  25. 25
  26. 26
  27. 27
  28. 28
  29. 29
  30. 30
  31. 31
  32. 32
  33. 33
  34. 34
  35. 35
  36. 36
  37. 37
  38. 38
  39. 39
  40. 40
  41. 41
  42. 42
1 / 18

Press play to hear the narration, or step through at your own pace. The full Java code is below.

Full Java solution

One file, Java 17, 278 lines, with a small main() demo. Save it as ConnectionPool.java, then run javac ConnectionPool.java && java ConnectionPool.

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.Semaphore;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicLong;
import java.util.function.LongSupplier;

/**
 * A bounded pool of database connections: borrow with a timeout, validate before handing out,
 * return on close(), evict idle connections above the minimum, and report leases held too long.
 */
public final class ConnectionPool {

    // ---------- what the pool manages ----------
    /** The raw resource. Real code wraps java.sql.Connection; here a fake keeps the demo self-contained. */
    interface RawConnection {
        String id();

        boolean isValid(); // e.g. a cheap "SELECT 1" or the driver's isValid(timeout)

        void close();
    }

    /** Factory: how to open a new physical connection. Swapped for a fake in tests. */
    interface ConnectionFactory { RawConnection open(); }

    record Config(int minIdle, int maxTotal, long idleTimeoutMillis, long leakThresholdMillis) {
        Config {
            if (minIdle < 0 || maxTotal <= 0 || minIdle > maxTotal)
                throw new IllegalArgumentException("bad sizes");
        }
    }

    static final class PoolExhaustedException extends RuntimeException {
        private static final long serialVersionUID = 1L;

        PoolExhaustedException(String msg) { super(msg); }
    }

    /** An idle connection plus the time it was returned, for idle eviction. */
    private record Idle(RawConnection conn, long since) {}

    /** Who holds a connection and since when, for leak detection. */
    record LeaseInfo(String connId, String holder, long borrowedAt) {}

    /**
     * Proxy handed to callers. close() returns the connection to the pool instead of closing it,
     * and a second close() is ignored, so try-with-resources is always safe.
     */
    final class Lease implements AutoCloseable {
        private final RawConnection conn;
        private final AtomicBoolean returned = new AtomicBoolean();

        private Lease(RawConnection conn) { this.conn = conn; }

        String id() { return conn.id(); }

        String query(String sql) {
            if (returned.get()) throw new IllegalStateException("lease already returned");
            return conn.id() + " ran " + sql;
        }

        @Override
        public void close() {
            if (returned.compareAndSet(false, true)) giveBack(this);
        }
    }

    // ---------- pool state ----------
    private final Config config;
    private final ConnectionFactory factory;
    private final LongSupplier clock;
    private final Semaphore permits; // one permit per connection a caller may hold: total <= maxTotal
    private final LinkedBlockingDeque<Idle> idle = new LinkedBlockingDeque<>();
    private final Map<Lease, LeaseInfo> leased = new ConcurrentHashMap<>();
    private final AtomicInteger total = new AtomicInteger(); // open physical connections
    private final AtomicInteger created = new AtomicInteger(), destroyed = new AtomicInteger();
    private volatile boolean shutdown;

    ConnectionPool(Config config, ConnectionFactory factory, LongSupplier clock) {
        this.config = config;
        this.factory = factory;
        this.clock = clock;
        this.permits = new Semaphore(config.maxTotal(), true); // fair: waiters are served in order
        for (int i = 0; i < config.minIdle(); i++) idle.addLast(new Idle(openNew(), clock.getAsLong()));
    }

    /** Waits up to timeoutMillis for a connection; throws PoolExhaustedException if none frees up. */
    Lease borrow(String holder, long timeoutMillis) throws InterruptedException {
        if (shutdown) throw new IllegalStateException("pool is shut down");
        if (!permits.tryAcquire(timeoutMillis, TimeUnit.MILLISECONDS))
            throw new PoolExhaustedException("no connection within " + timeoutMillis + " ms (max "
                    + config.maxTotal() + ")");
        try {
            RawConnection conn = null;
            Idle candidate;
            while (conn == null && (candidate = idle.pollFirst()) != null) { // most recently used first: warm
                if (candidate.conn().isValid()) conn = candidate.conn();
                else destroy(candidate.conn(), "failed validation");
            }
            if (conn == null) conn = openNew(); // the permit guarantees total stays within maxTotal
            Lease lease = new Lease(conn);
            leased.put(lease, new LeaseInfo(conn.id(), holder, clock.getAsLong()));
            return lease;
        } catch (RuntimeException e) {
            permits.release(); // never lose a permit when opening a connection fails
            throw e;
        }
    }

    private void giveBack(Lease lease) {
        leased.remove(lease);
        if (shutdown || !lease.conn.isValid()) destroy(lease.conn, "broken on return");
        else idle.addFirst(new Idle(lease.conn, clock.getAsLong()));
        permits.release();
    }

    /** Called by a background housekeeper: close connections idle too long, but keep minIdle. */
    int evictIdle() {
        long now = clock.getAsLong();
        int evicted = 0;
        Idle oldest;
        while (idle.size() > config.minIdle() && (oldest = idle.peekLast()) != null
                && now - oldest.since() >= config.idleTimeoutMillis()) {
            if (idle.removeLastOccurrence(oldest)) {
                destroy(oldest.conn(), "idle too long");
                evicted++;
            }
        }
        return evicted;
    }

    /** Leases held longer than the threshold: probably a caller that forgot to close(). */
    List<LeaseInfo> findLeaks() {
        long now = clock.getAsLong();
        List<LeaseInfo> out = new ArrayList<>();
        for (LeaseInfo info : leased.values())
            if (now - info.borrowedAt() >= config.leakThresholdMillis()) out.add(info);
        return out;
    }

    void shutdown() {
        shutdown = true;
        Idle i;
        while ((i = idle.pollFirst()) != null) destroy(i.conn(), "shutdown");
    }

    int idleCount() { return idle.size(); }

    int activeCount() { return leased.size(); }

    int totalCount() { return total.get(); }

    String stats() {
        return "total=" + total.get() + " idle=" + idle.size() + " active=" + leased.size()
                + " created=" + created.get() + " destroyed=" + destroyed.get();
    }

    private RawConnection openNew() {
        RawConnection c = factory.open();
        total.incrementAndGet();
        created.incrementAndGet();
        return c;
    }

    private void destroy(RawConnection c, String why) {
        c.close();
        total.decrementAndGet();
        destroyed.incrementAndGet();
        System.out.println("  closed " + c.id() + ": " + why);
    }

    // ---------- demo ----------
    /** A fake database connection that can be broken on demand. */
    static final class FakeConnection implements RawConnection {
        private final String id;
        private volatile boolean broken;

        FakeConnection(String id) { this.id = id; }

        public String id() { return id; }

        public boolean isValid() { return !broken; }

        public void close() { broken = true; }

        void breakIt() { broken = true; }
    }

    static void check(boolean ok, String what) { if (!ok) throw new AssertionError(what); }

    public static void main(String[] args) throws InterruptedException {
        AtomicLong now = new AtomicLong(0);
        AtomicInteger ids = new AtomicInteger();
        List<FakeConnection> all = new ArrayList<>();
        ConnectionFactory factory = () -> {
            FakeConnection c = new FakeConnection("conn-" + ids.incrementAndGet());
            synchronized (all) { all.add(c); }
            return c;
        };
        ConnectionPool pool = new ConnectionPool(new Config(2, 3, 30_000, 60_000), factory, now::get);
        System.out.println("Started: " + pool.stats());

        Lease a = pool.borrow("checkout-service", 100);
        Lease b = pool.borrow("report-job", 100);
        Lease c = pool.borrow("admin-console", 100);
        System.out.println("Borrowed " + a.id() + ", " + b.id() + ", " + c.id() + ": " + pool.stats());
        try {
            pool.borrow("late-caller", 50);
            throw new AssertionError("expected exhaustion");
        } catch (PoolExhaustedException e) {
            System.out.println("4th borrow: " + e.getMessage());
        }

        String reused = c.id();
        c.close();
        c.close(); // double close is ignored
        try (Lease d = pool.borrow("late-caller", 100)) {
            System.out.println("After one close: " + d.query("SELECT 1"));
            check(d.id().equals(reused), "should reuse the returned connection");
        }

        b.close();
        all.get(1).breakIt(); // conn-2 dies while idle (database restarted)
        try (Lease e = pool.borrow("checkout-service", 100)) {
            System.out.println("Validated borrow got " + e.id());
            check(!e.id().equals("conn-2"), "broken connection handed out");
        }

        now.addAndGet(61_000); // a has been out for 61 s
        System.out.println("Leaks: " + pool.findLeaks());
        List<LeaseInfo> leaks = pool.findLeaks();
        check(leaks.size() == 1 && leaks.get(0).holder().equals("checkout-service"), "leak");
        a.close();

        // Eight threads, 200 borrows each, on a pool of at most 3: never more than 3 open at once.
        AtomicInteger maxSeen = new AtomicInteger(), done = new AtomicInteger();
        ExecutorService threads = Executors.newFixedThreadPool(8);
        CountDownLatch finished = new CountDownLatch(8);
        for (int t = 0; t < 8; t++) {
            threads.submit(() -> {
                try {
                    for (int i = 0; i < 200; i++) {
                        try (Lease l = pool.borrow("worker", 2_000)) {
                            maxSeen.accumulateAndGet(pool.activeCount(), Math::max);
                            l.query("UPDATE stock");
                            done.incrementAndGet();
                        }
                    }
                } catch (InterruptedException e) {
                    Thread.currentThread().interrupt();
                } finally {
                    finished.countDown();
                }
            });
        }
        finished.await();
        threads.shutdown();
        System.out.println("8 threads: " + done.get() + " queries, max active " + maxSeen.get()
                + ", " + pool.stats());
        check(done.get() == 1600 && maxSeen.get() <= 3 && pool.totalCount() <= 3, "bounded");
        now.addAndGet(31_000);
        int evicted = pool.evictIdle();
        System.out.println("Evicted " + evicted + " idle: " + pool.stats());
        check(evicted == 1 && pool.idleCount() == 2, "evict down to minIdle");
        pool.shutdown();
        check(pool.totalCount() == 0, "all closed");
        System.out.println("Shut down: " + pool.stats());
    }
}

Notes

Requirements

The requirements. Callers borrow a connection and give it back by closing it. The pool keeps a minimum number open and warm, and never opens more than a maximum, because the database itself has a session limit. When everything is lent out, a caller waits up to a timeout, then gets a clear error. Dead connections are never handed out, idle ones are closed after a while, long-held ones are reported as leaks, and many threads use it at once.

Clarify the edge cases. When the pool is empty, wait up to a timeout rather than fail at once, and serve waiters fairly in arrival order. Hand out the most recently used idle connection, because it is warm and the others can age out. Validate on borrow and on return. A suspected leak is reported with the holder's name, not forcibly closed, because the holder may still be using it. And the pool grows lazily up to its maximum.

Requirements
  • borrow(): get a connection
  • close(): give it back
  • minIdle kept open and warm
  • maxTotal never exceeded
  • wait up to a timeout, then fail
  • never hand out a dead connection
  • close connections idle too long
  • report leases held too long
  • safe from many threads
  • stats: total, idle, active
Clarify first
  • pool empty: wait or fail?
  • wait, up to a timeout
  • fair to waiters?
  • yes, first come first served
  • which idle one to lend?
  • most recently used: warm
  • validate when?
  • on borrow and on return
  • leak: close it or report it?
  • report with the holder name
  • grow lazily up to max

Entities

The entities. The pool is the service object. A semaphore holds one permit per connection that may be lent out, which is how the maximum is enforced. An idle deque holds the connections nobody is using. A lease wraps a raw connection while a caller holds it. A factory opens new physical connections. Notice the split: the semaphore answers may I have one, and the deque answers which one.

Class diagram

The types. A raw connection only needs three methods: an id, a validity check and close, so the demo can use a fake and real code can wrap a JDBC connection. The config is a record that rejects a minimum larger than the maximum in its constructor. An idle entry remembers when it was returned, for eviction, and the lease registry remembers who borrowed each connection and when, for leak detection.

Types
interface RawConnection {
  String id(); boolean isValid();
  void close();
}
record Config(int minIdle, int maxTotal,
    long idleTimeoutMillis,
    long leakThresholdMillis)
record Idle(RawConnection c, long since)
record LeaseInfo(String connId,
    String holder, long borrowedAt)

Proxy: the lease

Callers never touch the raw connection. They get a lease, which is the Proxy pattern: it looks like a connection, but its close method returns the connection to the pool instead of closing the socket. An atomic flag makes a second close a no-op, so a double return can never put one connection in the idle deque twice. And because it is auto-closeable, try-with-resources gives it back even when the query throws.

Lease implements AutoCloseable
final RawConnection conn;
final AtomicBoolean returned;

public void close() {
  if (returned.compareAndSet(false, true))
    giveBack(this);
}

try (Lease l = pool.borrow("svc", 100)) {
  l.query("SELECT 1");
}

Factory

How a connection is opened is a Factory. In production it calls the database driver with a URL and credentials; in tests it returns a fake with a numbered id, so the demo runs with no database at all. The pool as a whole is the Object Pool pattern: when something is expensive to create and cheap to reuse, create a bounded number and lend them out.

ConnectionFactory
interface ConnectionFactory {
  RawConnection open();
}

prod: () -> new JdbcConnection(
          DriverManager.getConnection(url))
test: () -> new FakeConnection(
          "conn-" + ids.incrementAndGet())

the whole pool is the Object Pool
  pattern: reuse, do not recreate

Bounding with permits

The semaphore starts with max-total permits. Borrowing first takes a permit with a timed try-acquire, which is exactly wait up to the timeout, then give up. Fairness is on, so the longest waiter gets the next free permit. Holding a permit means you may own one connection, either an idle one or a newly opened one, so the total can never pass the maximum. In the demo, three are lent out and the fourth caller fails after fifty milliseconds.

Wait with a timeout
permits = new Semaphore(maxTotal, true)

if (!permits.tryAcquire(timeout, MS))
  throw new PoolExhaustedException(
    "no connection within 50 ms")

holding a permit means:
  you may own one connection,
  idle or freshly opened
giveBack: permits.release()

Flow: borrow

The borrow flow. Take a permit. Then poll the warm end of the idle deque, and validate each connection; a dead one, say after a database restart, is closed and dropped, and we try the next. If no idle connection is left, open a new one, which the permit already allows. Record the holder and time in the registry and return the lease. If opening fails, release the permit before rethrowing, or the pool would slowly shrink to nothing.

borrow(holder, timeout)
acquire a permit or throw
try {
  while (conn == null && (c = pollFirst()))
    valid ? conn = c : destroy(c)
  if (conn == null) conn = openNew()
  leased.put(lease, info(holder, now))
  return lease
} catch (RuntimeException e) {
  permits.release(); throw e
}

Flow: give back

Giving back. Remove the lease from the registry. If the connection broke while in use, or the pool is shutting down, close it; otherwise push it on the warm end of the idle deque with the current time. Release the permit last. The order matters: the connection must already be in the deque when a waiting thread wakes up with that permit, or it would open a new connection for nothing.

giveBack(lease)
leased.remove(lease)
if (shutdown || !conn.isValid())
  destroy(conn, "broken on return")
else
  idle.addFirst(new Idle(conn, now))
permits.release()  // last

order matters: the connection is
  back in the deque before a waiter
  can take the permit

Idle eviction

Idle eviction. A housekeeper thread runs every thirty seconds or so. Because we always lend and return at the warm end, the cold end holds the connections that have been idle longest. Close them while the deque is above the minimum and the oldest has been idle past the timeout. After a burst grows the pool to three, quiet time shrinks it back to two. Real pools also retire connections after a maximum lifetime, to stay ahead of firewall and server timeouts.

evictIdle()
while (idle.size() > minIdle
       && oldest idle >= timeout)
  removeLast, destroy it

warm end: addFirst / pollFirst
cold end: peekLast / removeLast
  so busy periods reuse the same few
  and the extras age out together
also: max lifetime, e.g. 30 min,
  to rotate past firewall timeouts

Leak detection

Leak detection. The most common pool bug is a caller that borrows and never closes, and after a few of those the whole service hangs waiting for permits. Every loan is in the registry with its holder and time, so the housekeeper reports any lease held past a threshold. In the demo, the checkout service has held connection one for sixty-one seconds. Production pools also capture the stack trace at borrow time, which points straight at the missing close.

findLeaks()
for (LeaseInfo i : leased.values())
  if (now - i.borrowedAt()
        >= leakThresholdMillis)
    report(i)

LeaseInfo[conn-1,
  checkout-service, borrowedAt=0]

production: capture the stack trace
  at borrow time, log it on a leak

Concurrency

Concurrency without a global lock. The semaphore is the only place a thread waits. The idle set is a concurrent deque, the registry a concurrent map, the counters atomics, and each lease's return flag an atomic boolean. The permit ties them together: whoever holds one is entitled to exactly one connection. Crucially, we never hold a lock while opening a connection, which can take a hundred milliseconds, and every failure path gives its permit back.

Thread safety
  • Semaphore: the only place to wait
  • LinkedBlockingDeque: idle set
  • ConcurrentHashMap: lease registry
  • AtomicInteger: total, created
  • AtomicBoolean: return once
  • never hold a lock while opening
  • a connection (100 ms or more)
  • release the permit on every
  • failure path, in finally/catch

Extensibility

Extensions. Validating on every borrow costs a round trip, so busy pools validate idle connections in the housekeeper instead. Add a maximum lifetime, prefill to the minimum in the background, or split into per-tenant partitions so one noisy tenant cannot starve others. A metrics listener can record wait times, timeouts and leaks. And with a generic factory, the same skeleton pools sockets, buffers or anything else that is expensive to create.

Extension points
  • test-while-idle: validate in the
  • housekeeper, not on every borrow
  • max lifetime per connection
  • prefill to minIdle in background
  • per-tenant pools or partitions
  • metrics listener: wait time,
  • timeouts, leaks (Observer)
  • generic Pool<T> for sockets,
  • threads, buffers

Testing

The full code is below the player. The demo starts with two warm connections, lends three, and shows the fourth caller timing out. A returned connection is reused, a connection broken while idle fails validation and is replaced, and a sixty-one-second loan is reported as a leak. Then eight threads run sixteen hundred queries through a pool of three and never see more than three active, idle eviction shrinks it back to two, and shutdown closes everything.

Run it
Borrowed conn-1, conn-2, conn-3
4th borrow: no connection within 50 ms
After one close: conn-3 ran SELECT 1
  closed conn-2: failed validation
Leaks: [conn-1, checkout-service]
8 threads: 1600 queries, max active 3
  closed conn-4: idle too long
Evicted 1 idle: total=2 idle=2
Shut down: total=0 idle=0

Follow-ups

Expect follow-ups. The classic one: now design a thread pool executor. It is the same idea turned around: a bounded task queue and worker threads that loop taking tasks, a core size kept alive and a maximum for bursts, idle workers retired after a keep-alive, and a rejection policy when the queue is full. How big should a connection pool be? Smaller than you think. And remember that every service instance has its own pool, so instances times pool size must fit the database limit.

Follow-ups to expect
  • now design a thread pool executor
  • task queue + N worker threads
  • core vs max size, keep-alive
  • full queue: reject, caller-runs
  • how big should the pool be?
  • small: cores x 2 is a start
  • caller in a transaction waits
  • for a 2nd connection: deadlock
  • one pool per service instance x
  • instances vs db max sessions

Say it like this

The short version. A fair semaphore with max-total permits bounds the pool and gives borrow its timeout. Idle connections sit in a deque, lent and returned at the warm end, and are validated before they are handed out. Callers get a proxy whose close returns the connection exactly once. A housekeeper closes idle connections down to the minimum and reports leases held too long.

In the interview
  • "A fair semaphore of maxTotal
  • permits bounds the pool and gives
  • borrow a timeout. Idle ones sit
  • in a deque, warm end first,
  • validated on borrow. Callers get
  • a proxy whose close() returns it.
  • A housekeeper evicts idle ones
  • and reports long leases."

Interview questions

Why use a connection pool at all?

Opening a database connection costs a network handshake, authentication and server memory, often tens of milliseconds. A pool opens a bounded number once and lends them out, so each request pays microseconds instead, and the database is protected from more sessions than it can handle.

How do you enforce the maximum size and a borrow timeout?

A Semaphore with maxTotal permits. Borrow calls tryAcquire with the timeout and throws a pool-exhausted error if it fails; holding a permit entitles the caller to one connection, idle or newly opened, and returning the connection releases the permit.

Why give out the most recently used idle connection?

It is warm: recently proven to work and with caches primed. Using one end of a deque for borrow and return also means the other end collects the connections idle the longest, which is exactly what idle eviction wants to close.

How do you avoid handing out a dead connection?

Validate before lending, with a cheap query or the driver isValid check, and drop failures; also check on return. Busy pools often validate idle connections in a background task instead, trading a small risk for no extra round trip on each borrow.

How does the caller return a connection?

It receives a proxy whose close() returns the underlying connection to the pool. An AtomicBoolean makes close() idempotent so a double return cannot corrupt the idle set, and try-with-resources guarantees the return even when the query throws.

How do you detect connection leaks?

Record every lease with its holder, borrow time and ideally the stack trace. A housekeeper reports leases held longer than a threshold. Reporting is safer than force-closing, because the holder may still be in a long but legitimate transaction.

What can go wrong if opening a connection throws?

The permit taken at the start of borrow would never be released, and each failure silently shrinks the pool until every caller times out. Release the permit in a catch or finally block on every failure path.

How would you design a thread pool executor?

A bounded blocking queue of tasks and worker threads that loop taking from it. Keep a core number of workers, grow to a maximum when the queue is full, retire extras after a keep-alive, and apply a rejection policy such as abort or caller-runs when both are full.