problemmediumood

Design and Implement Connection Pooling

MediumUpdated: Oct 16, 2025

Design and Implement Connection Pooling

Problem

Design an efficient and re-usable connection pool application programming interface.

Solution

In the world of application designing, we often encounter the need to fetch, update or delete objects stored in the database. To enable this, we would require a database connection object.

What is connection poolin[Design and Implement Connection Pooling](design-and-implement-connection-pooling)ol]].

Opening and closing the database connection object can be very costly as it involves, creating the network connection to the database followed by doing the authentication using the username and password. Hence, database connection is a scarce resource and need to be utilized efficiently and effectively.

Here are the components needed:

Connection interface:

interface IConnection {
 int read(); // reads an int from the connection
 void close(); // closes the connection
}

An implementation reading from a stream could look like this (ignoring exceptions, EOF handling, etc):

class StreamConnection implements IConnection {
 private final InputStream input;
 int read(){ return input.read(); }
 void close(){ input.close(); }
}

Problem

Design an efficient, reusable connection pool API that hands out reusable connections to clients, supports returning/releasing connections, and provides administrative operations (free count, leak detection). The design should address performance and concurrency concerns.

Solution

1. Requirements Analysis

Functional requirements:

  • Provide an API to request a connection (getConnection) and to release it back to the pool (releaseConnection).
  • Maintain a fixed/poolable set of underlying connections up to a configurable maximum.
  • Allow inspection of pool state (getFreeConnectionCount) and administrative actions (reclaim idle connections).
  • Wrap raw connections so clients call close() to return to pool (transparent to clients).

Non-functional requirements:

  • Thread-safety: safe for concurrent clients.
  • Performance: low-latency handout and return of connections.
  • Robustness: detect and recover from leaked or broken connections; configurable max size and timeouts.

Operational concerns (from source):

  • Track which client requested which connection and when to aid in diagnosing leaks.
  • Reclaim or recreate connections that become invalid or idle for too long.

2. Use Case Diagram

Actors: Client (application code), Administrator/Monitor

Use cases: Request Connection, Release Connection, Inspect Pool

graph TD
	subgraph Connection Pool System
		UC1(Request Connection)
		UC2(Release Connection)
		UC3(Inspect Pool)
	end
	Client --> UC1
	Client --> UC2
	Admin --> UC3

3. Class Diagram

Core classes and responsibilities (derived from the original notes):

  • IConnection / Connection: abstraction for a resource with close() semantics.
  • StreamConnection / DBConnection: concrete implementations of IConnection.
  • ConnectionPool / IConnectionPool: manages available/used connections, hands out and reclaims connections.
  • MyConnection: wrapper returned to clients that delegates operations and overrides close() to return to pool.
classDiagram
	class IConnection {
		<<interface>>
		+int read()
		+void close()
	}

	class DBConnection {
		+String url
		+String userId
		+String password
	}

	class ConnectionPool {
		+List~Connection~ availableConnections
		+List~Connection~ usedConnections
		+int MAX_CONNECTIONS
		+Connection getConnection()
		+boolean releaseConnection(Connection)
		+int getFreeConnectionCount()
	}

	class MyConnection {
		-Connection delegate
		+void close()
		+Object execute(Object)
	}

	ConnectionPool "1" -- "0..*" MyConnection : hands out
	MyConnection ..> Connection : delegates
	DBConnection ..|> IConnection

4. Activity Diagrams

Activity: Requesting a Connection

graph TD
	U[Client requests connection] --> P[Pool checks available list]
	P --> C{Available?}
	C -- Yes --> H[Remove from available, wrap and return]
	C -- No --> W[Wait or create new if under max]
	W --> H

Activity: Releasing a Connection

graph TD
	U[Client calls close()] --> W[Wrapper returns to pool]
	W --> V[Move connection from used to available]
	V --> R[Optional validation/recreation]

5. High-Level Code Implementation

Below are cleaned Java skeletons adapted from the original examples and a Python equivalent (protocols/dataclasses). Keep these as skeletons: method signatures and responsibilities only.

Java

// Connection abstraction
public interface IConnection {
		int read();
		void close();
}

// Example concrete connection (DB)
public class DBConnection implements IConnection {
		private final String url;
		private final String userId;
		private final String password;

		public DBConnection(String url, String user, String pwd) { /* ... */ }
		public int read() { /* ... */ return 0; }
		public void close() { /* close underlying resource */ }
}

// Pool interface
public interface IConnectionPool {
		IConnection getConnection();
		boolean releaseConnection(IConnection con);
		int getFreeConnectionCount();
}

// Simple threaded pool (skeleton)
public class ConnectionPool implements IConnectionPool {
		private List<IConnection> availableConnections = new ArrayList<>();
		private List<IConnection> usedConnections = new ArrayList<>();
		private final int MAX_CONNECTIONS = 5;

		public ConnectionPool(String url, String user, String password) {
				// initialize pool
		}

		private IConnection createConnection() { /* create DBConnection */ return null; }

		public synchronized IConnection getConnection() {
				// hand out or wait/create
				return null;
		}

		public synchronized boolean releaseConnection(IConnection con) {
				// return to pool and notify waiters
				return false;
		}

		public int getFreeConnectionCount() { return availableConnections.size(); }
}

// Wrapper returned to clients; close() returns to pool
public class MyConnection implements IConnection {
		private final IConnection delegate;
		private final ConnectionPool pool;

		public MyConnection(IConnection delegate, ConnectionPool pool) { this.delegate = delegate; this.pool = pool; }

		public int read() { return delegate.read(); }
		public void close() { pool.releaseConnection(this); }
}

Python

from __future__ import annotations
from dataclasses import dataclass
from typing import Protocol, List
from datetime import datetime

class IConnection(Protocol):
		def read(self) -> int: ...
		def close(self) -> None: ...

@dataclass
class DBConnection:
		url: str
		user: str
		password: str

		def read(self) -> int:
				raise NotImplementedError

class IConnectionPool(Protocol):
		def get_connection(self) -> IConnection: ...
		def release_connection(self, con: IConnection) -> bool: ...
		def get_free_connection_count(self) -> int: ...

class ConnectionPool:
		def __init__(self, max_connections: int = 5) -> None:
				self.available: List[IConnection] = []
				self.used: List[IConnection] = []
				self.max = max_connections

		def get_connection(self) -> IConnection:
				# synchronized / thread-safe in real implementation
				raise NotImplementedError

		def release_connection(self, con: IConnection) -> bool:
				raise NotImplementedError

		def get_free_connection_count(self) -> int:
				return len(self.available)

References

Practical Pooling Advice

The following text summarizes practical advice and additional notes about connection pooling, merged from a concise reference note.

Connection pooling maintains a group (or pool) of active database connections. When an application tries to open a database connection, an open connection is retrieved from the pool (if available). Closing a connection returns it to the pool for other processes to utilize. Connection pooling is utilized (by default) unless otherwise specified.

Pooling advice

You should be judicious in your use of connection pooling. Here are a few tips when using it:

  • Only open connections when needed. That is, timing is everything, so open a connection just before you need it and not any sooner. Also, close that connection as soon as you are finished with it—don't wait for the garbage collector to do it.
  • Close user-defined transactions before closing related connections.
  • To maintain the connection pool, you should keep at least one connection open. Therefore, do not close all your connections in the pool. If server resources become a problem, you may close all connections, and the pool will be recreated with the next request.
  • Do not use connection pooling if integrated security is utilized. This results in a unique connection string per user, so each user has a connection pool that is not available to other users. The end result is poor performance, so pooling should be avoided in this scenario.
  • ClearPool clears/empties the connection pool that is associated with the connection. If additional connections associated with connection are in use at the time of the call, they are marked appropriately and are discarded (instead of being returned to the pool) when Close is called on them.
public static void ClearPool ( SqlConnection connection)
  • ClearAllPools resets (or empties) the connection pool. If there are connections in use at the time of the call, they are marked appropriately and will be discarded (instead of being returned to the pool) when Close is called on them.
public static void SqlConnection.ClearAllPools()
  • Connection Pooling for OleDb, Odbc, and OracleClient are documented on Microsoft's ADO.NET docs.
  • Pooled vs. Non-pooled connections: see SQL Server & pooling articles for guidance.
  • @@SPID returns the session ID of the current user process; use sp_who to list active connections and their SPIDs.

Example SQL to retrieve current session id:

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

For server-side process inspection you can use sp_who. Each time you create the connection, you can execute SELECT @@SPID to get the connection SPID if you are going to need it.

Source links and further reading:

Comments