Design a Database Connection Pool (HikariCP style)

Problem Statement

Design a database connection pool manager (similar to HikariCP). The manager should initialize a set of database connections, allow multiple application threads to borrow connections, handle timeout configurations when connections are unavailable, and reclaim connections when threads finish. Crucially, the pool must prevent clients from closing the physical connections directly, routing closure actions back to the pool instead.

Asked In Companies

Amazon Oracle Goldman Sachs JPMorgan

---

Functional Requirements

• Maintain a pool of active database connections up to a configurable maximum size.
• Allow threads to lease connections: getConnection(timeout).
• Block threads temporarily if all connections are in use, throwing a timeout exception if the limit is breached.
• Reclaim connections when the client calls close(), returning them to the available pool rather than destroying the socket.
• Ensure thread safety during connection allocation, return, and lookup operations.

---

Objects Required

• Connection (Base interface mapping standard DB interactions)
• PhysicalConnection (Concrete mock representing raw network connections)
• ProxyConnection (Decorator proxy that overrides closing logic)
• ConnectionPool (Core pool manager and factory)

---

Connection Interface & PhysicalConnection

The Connection interface matches standard JDBC behaviors. Decorating this interface prevents our pool logic from tightly coupling to physical network classes.

public interface Connection {
    void execute(String sql);
    void close();
}

The execute() method simulates running queries, while close() handles lifecycle cleanups. Here is the concrete mock implementation:

public class PhysicalConnection implements Connection {
    private final int id;

    public PhysicalConnection(int id) {
        this.id = id;
    }

    @Override
    public void execute(String sql) {
        System.out.println("Executing SQL on physical connection #" + id + ": " + sql);
    }

    @Override
    public void close() {
        System.out.println("Destroying physical socket for connection #" + id);
    }
}

The PhysicalConnection represents the real TCP link to the database. We mock its execution outputs and track its ID for debugging purposes.

---

ProxyConnection Class

To ensure connections are returned to the pool instead of destroyed when the client calls close(), we wrap the real connection inside a proxy wrapper. This is a classic application of the **Proxy Design Pattern**.

public class ProxyConnection implements Connection {
    private final Connection realConnection;
    private final ConnectionPool pool;

    public ProxyConnection(Connection realConnection, ConnectionPool pool) {
        this.realConnection = realConnection;
        this.pool = pool;
    }

    @Override
    public void execute(String sql) {
        realConnection.execute(sql);
    }

    @Override
    public void close() {
        System.out.println("Close intercepted. Returning connection to the pool.");
        pool.releaseConnection(this);
    }

    public Connection getRealConnection() {
        return realConnection;
    }
}

The overridden close() method delegates resource cleanup back to the ConnectionPool object. Any client calling this method returns the wrapper to the active stack, keeping the physical socket alive.

---

ConnectionPool Class

The ConnectionPool coordinates active states, tracks borrowed entities, and exposes thread-safe borrowing mechanics. We utilize a concurrent BlockingQueue to block and wake threads dynamically.

import java.util.Collections;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.TimeUnit;

public class ConnectionPool {
    private final int maxPoolSize;
    private final LinkedBlockingQueue<Connection> availableConnections;
    private final Set<Connection> activeConnections;
    private int connectionIdCounter = 0;

    public ConnectionPool(int initialSize, int maxPoolSize) {
        this.maxPoolSize = maxPoolSize;
        this.availableConnections = new LinkedBlockingQueue<>(maxPoolSize);
        this.activeConnections = Collections.newSetFromMap(new ConcurrentHashMap<>());

        for (int i = 0; i < initialSize; i++) {
            availableConnections.add(createNewConnection());
        }
    }

    private synchronized Connection createNewConnection() {
        connectionIdCounter++;
        return new PhysicalConnection(connectionIdCounter);
    }

    public Connection getConnection(long timeoutMs) {
        try {
            // Retrieve available connection, waiting if none exist up to timeout limits
            Connection conn = availableConnections.poll(timeoutMs, TimeUnit.MILLISECONDS);
            if (conn == null) {
                // Check if we can dynamically grow the pool to maxPoolSize
                synchronized (this) {
                    if (availableConnections.size() + activeConnections.size() < maxPoolSize) {
                        conn = createNewConnection();
                    }
                }
                if (conn == null) {
                    throw new RuntimeException("Timeout error: No available connections in pool.");
                }
            }

            ProxyConnection proxy = new ProxyConnection(conn, this);
            activeConnections.add(proxy);
            return proxy;
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            throw new RuntimeException("Thread interrupted while borrowing connection.");
        }
    }

    public void releaseConnection(Connection proxyConnection) {
        if (proxyConnection instanceof ProxyConnection) {
            ProxyConnection proxy = (ProxyConnection) proxyConnection;
            if (activeConnections.remove(proxy)) {
                availableConnections.offer(proxy.getRealConnection());
            }
        }
    }
}

Here is an explanation of the core operations inside the ConnectionPool class:

• The constructor configures size limits and prepopulates the queue with mock connections.
• createNewConnection() acts as our internal factory, instantiating new physical connection records with incremental IDs.
• getConnection() attempts to poll from the available queue. If empty, it attempts to scale to maximum pool capacity. If it still fails, it throws a timeout error. On success, it creates a new ProxyConnection wrapper.
• releaseConnection() removes the wrapper instance from our active tracker set and returns the unwrapped realConnection to the available queue.

---

Main Driver Class

This class tests our database connection pool. It initializes a small pool, borrows connections, handles threshold limits, triggers timeout blocks, and demonstrates connection recycle recovery paths.

public class Main {
    public static void main(String[] args) {
        // Create pool: initial size = 2, max size = 3
        ConnectionPool pool = new ConnectionPool(2, 3);

        System.out.println("--- Borrowing Connection 1 ---");
        Connection conn1 = pool.getConnection(1000);
        conn1.execute("SELECT * FROM users");

        System.out.println("\n--- Borrowing Connection 2 ---");
        Connection conn2 = pool.getConnection(1000);
        conn2.execute("SELECT * FROM orders");

        System.out.println("\n--- Borrowing Connection 3 (Scales pool to max capacity) ---");
        Connection conn3 = pool.getConnection(1000);
        conn3.execute("SELECT * FROM products");

        // Attempting to borrow when pool is completely exhausted
        try {
            System.out.println("\n--- Borrowing Connection 4 (Expect timeout exception) ---");
            Connection conn4 = pool.getConnection(1000);
            conn4.execute("SELECT * FROM logs");
        } catch (Exception e) {
            System.out.println("Caught Expected Exception: " + e.getMessage());
        }

        // Return connection 1 to release pool pressure
        System.out.println("\n--- Returning Connection 1 ---");
        conn1.close();

        // Try to borrow again
        System.out.println("\n--- Borrowing Connection 4 again (Should succeed now) ---");
        Connection conn4 = pool.getConnection(1000);
        conn4.execute("SELECT * FROM logs");
        conn4.close();
    }
}

The main() driver initializes the resources, allocates connections up to maximum limits, demonstrates safety assertions, and displays how physical connections are recycled rather than terminated.

---

Class Diagram

---

Also See

• Most Commonly Asked Low Level Design Interview Questions
• Top 10 Low Level Design Interview Questions