Connection Pool
Object Pool for reusing expensive database connections. Proxy wrappers return connections on close() instead of destroying them. Singleton per data source prevents resource exhaustion.
Key Abstractions
Interface — execute query, close
Proxy — wraps real connection, overrides close() to return to pool
Factory — creates driver-specific connections: MySQL, Postgres
Enum: AVAILABLE, IN_USE, VALIDATING, EXPIRED
Object Pool + Singleton — manages borrow/return lifecycle
Max size, min idle, max wait time, validation query
Class Diagram
How It Works
Database connections are expensive to create. Each one requires a TCP handshake, TLS negotiation (if encrypted), authentication, and session setup on the database server. In a typical web application handling hundreds of requests per second, creating and tearing down a connection per request would waste enormous amounts of time and exhaust the database server's connection limit.
A connection pool solves this by creating a fixed set of connections upfront and lending them out to callers on demand. When a caller is done, the connection goes back to the pool rather than being destroyed. The next caller gets the same underlying connection without paying the creation cost again. This is the Object Pool pattern: one of the oldest and most practical patterns in systems programming.
The Proxy pattern makes this invisible to callers. A PooledConnection wraps the real connection and implements the same Connection interface. When application code calls close(), it thinks it's closing the connection. In reality, the proxy intercepts that call and returns the connection to the pool. The calling code never knows a pool exists.
Requirements
Functional
acquire()borrows a connection from the pool, blocking if none are availablerelease()/close()returns a connection to the pool for reuse- Connections are validated before being handed out to detect stale or broken connections
- Pool is bounded: never exceeds
maxSizeopen connections - Pre-fills
minIdleconnections on startup to avoid cold-start latency
Non-Functional
- Thread-safe: multiple threads borrow and return connections concurrently
- Bounded blocking:
acquire()waits up tomaxWaitMsthen throws a timeout, preventing indefinite thread stalls - Singleton per data source: one pool per database URL to prevent connection count explosion
Design Decisions
Couldn't callers just return connections manually?
Without the Proxy pattern, every caller must remember to call pool.release(conn) explicitly. That's error-prone. If an exception occurs between acquire() and release(), the connection leaks. With the Proxy pattern, callers use conn.close(): the same idiom they already know from non-pooled connections. The Proxy intercepts close() and returns the connection to the pool. It works naturally with try-with-resources (Java) or context managers (Python), making leaks much harder to introduce.
Does creating connections on demand work?
Creating a real connection involves DNS resolution, TCP three-way handshake, TLS negotiation, and database authentication. This takes 5-50ms depending on the database and network. Under load, creating a connection per request adds latency and can overwhelm the database with simultaneous connection attempts. The pool amortizes this cost: connections are created once and reused thousands of times. HikariCP (the most popular Java pool) benchmarks show pool acquisition in under 1 microsecond vs. 5+ milliseconds for fresh connections.
How should you validate connections before handing them out?
Before handing out a connection, the pool should verify it's still alive. Connections can die due to network issues, database restarts, or idle timeouts. Three common strategies: (1) Test on borrow: run a lightweight query like SELECT 1 before each borrow. Adds a round-trip per borrow but catches all stale connections. (2) Test on return: validate when the connection comes back. Wastes effort if the connection isn't borrowed again soon. (3) Background validation: a maintenance thread periodically pings idle connections. Best for production but more complex. Most pools (HikariCP, DBCP2) default to test-on-borrow with a configurable interval.
What happens if two parts of the app create separate pools?
If two parts of the application independently create pools for the same database, the total connection count doubles. Databases have hard limits (max_connections in PostgreSQL defaults to 100). Singleton-per-DSN ensures one pool per database, giving central control over connection counts. The Singleton is keyed on the data source name (host:port/database), so different databases get separate pools.
Interview Follow-ups
- "How would you detect connection leaks?" Track the borrow timestamp for each in-use connection. A background thread scans in-use connections and logs warnings for any held longer than a configurable threshold (e.g., 30 seconds). HikariCP calls this
leakDetectionThreshold. Some pools also capture the stack trace at borrow time so you can identify the leaking code path. - "How does dynamic pool sizing work?" Start with
minIdleconnections. When demand increases, grow the pool up tomaxSize. When demand drops, a maintenance thread can shrink the pool by closing idle connections that exceedminIdle. HikariCP tracks connection usage frequency and ages out underused connections. The key is hysteresis: don't shrink immediately after a spike or you'll just re-create connections on the next burst. - "How do health checks work in production pools?" HikariCP uses
Connection.isValid(timeout)(JDBC 4+) instead of running a validation query, which is faster because the driver can use a protocol-level ping. It also runs a backgroundhousekeepertask every 30 seconds to evict idle connections that exceedmaxLifetime(default 30 minutes) and refill tominimumIdle. - "What makes HikariCP faster than other pools?" HikariCP uses a custom lock-free
ConcurrentBagcollection instead of a blocking queue. Borrowing threads check their own thread-local list first (avoiding contention), then steal from a shared list. It also usesFastListinstead ofArrayListto avoid range checks, and the bytecode is carefully tuned to fit in CPU cache lines. These micro-optimizations add up to sub-microsecond acquire times under contention.
Code Implementation
1 from __future__ import annotations
2 from abc import ABC, abstractmethod
3 from dataclasses import dataclass, field
4 from enum import Enum
5 from threading import Condition, Lock, current_thread
6 import time
7 import uuid
8
9
10 # --------------- Config ---------------
11
12 @dataclass(frozen=True)
13 class PoolConfig:
14 """Immutable pool configuration."""
15 max_size: int = 5
16 min_idle: int = 2
17 max_wait_sec: float = 3.0
18 validation_query: str = "SELECT 1"
19
20 def __post_init__(self):
21 if self.max_size <= 0:
22 raise ValueError("max_size must be positive")
23 if self.min_idle < 0 or self.min_idle > self.max_size:
24 raise ValueError("min_idle must be between 0 and max_size")
25
26
27 # --------------- State ---------------
28
29 class ConnectionState(Enum):
30 AVAILABLE = "AVAILABLE"
31 IN_USE = "IN_USE"
32 VALIDATING = "VALIDATING"
33 EXPIRED = "EXPIRED"
34
35
36 # --------------- Connection Interface ---------------
37
38 class Connection(ABC):
39 @abstractmethod
40 def execute(self, query: str) -> str: ...
41
42 @abstractmethod
43 def close(self) -> None: ...
44
45
46 # --------------- Real Connection ---------------
47
48 class RealConnection(Connection):
49 """Simulates an actual database connection with TCP handshake cost."""
50
51 def __init__(self, host: str, port: int):
52 self.id = str(uuid.uuid4())[:8]
53 self.host = host
54 self.port = port
55 self.connected = False
56 self.created_at = time.monotonic()
57
58 def connect(self) -> None:
59 time.sleep(0.05) # Simulate TCP handshake + auth
60 self.connected = True
61 print(f" [RealConn-{self.id}] Connected to {self.host}:{self.port}")
62
63 def execute(self, query: str) -> str:
64 if not self.connected:
65 raise RuntimeError("Connection is not open")
66 return f"Result from {self.id} for: {query}"
67
68 def close(self) -> None:
69 if self.connected:
70 self.connected = False
71 print(f" [RealConn-{self.id}] Disconnected")
72
73 def is_valid(self) -> bool:
74 return self.connected
75
76
77 # --------------- Proxy ---------------
78
79 class PooledConnection(Connection):
80 """
81 Proxy wrapper. Callers use the standard Connection interface.
82 close() returns the underlying connection to the pool instead of
83 destroying it.
84 """
85
86 def __init__(self, real_conn: RealConnection, pool: "ConnectionPool"):
87 self._real = real_conn
88 self._pool = pool
89 self._closed = False
90
91 def execute(self, query: str) -> str:
92 if self._closed:
93 raise RuntimeError("Cannot use a closed pooled connection")
94 return self._real.execute(query)
95
96 def close(self) -> None:
97 if not self._closed:
98 self._closed = True
99 self._pool.release(self._real)
100
101
102 # --------------- Factory ---------------
103
104 class ConnectionFactory:
105 """Creates driver-specific real connections."""
106
107 def __init__(self, host: str, port: int):
108 self._host = host
109 self._port = port
110
111 def create(self) -> RealConnection:
112 conn = RealConnection(self._host, self._port)
113 conn.connect()
114 return conn
115
116
117 # --------------- Connection Pool (Object Pool + Singleton) ---------------
118
119 class ConnectionPool:
120 """
121 Object Pool that manages borrow/return of database connections.
122 Uses threading.Condition so acquire() can block until a connection
123 is returned by another thread or until the timeout expires.
124 """
125
126 _instances: dict[str, "ConnectionPool"] = {}
127 _class_lock = Lock()
128
129 @classmethod
130 def get_instance(cls, dsn: str, config: PoolConfig | None = None) -> "ConnectionPool":
131 """Singleton per data source name."""
132 with cls._class_lock:
133 if dsn not in cls._instances:
134 if config is None:
135 config = PoolConfig()
136 host, port_str = dsn.split(":")
137 port = int(port_str)
138 cls._instances[dsn] = ConnectionPool(config, ConnectionFactory(host, port))
139 return cls._instances[dsn]
140
141 @classmethod
142 def clear_instances(cls) -> None:
143 """For testing : remove all singleton instances."""
144 with cls._class_lock:
145 for pool in cls._instances.values():
146 pool.shutdown()
147 cls._instances.clear()
148
149 def __init__(self, config: PoolConfig, factory: ConnectionFactory):
150 self._config = config
151 self._factory = factory
152 self._lock = Lock()
153 self._not_empty = Condition(self._lock)
154
155 self._available: list[RealConnection] = []
156 self._in_use: set[str] = set() # keyed by conn.id
157 self._states: dict[str, ConnectionState] = {}
158 self._all: dict[str, RealConnection] = {}
159
160 # Pre-fill min_idle connections
161 for _ in range(config.min_idle):
162 self._create_connection()
163
164 # ---- Internal helpers ----
165
166 def _create_connection(self) -> RealConnection:
167 conn = self._factory.create()
168 self._all[conn.id] = conn
169 self._states[conn.id] = ConnectionState.AVAILABLE
170 self._available.append(conn)
171 return conn
172
173 def _total_count(self) -> int:
174 return len(self._all)
175
176 def _validate(self, conn: RealConnection) -> bool:
177 self._states[conn.id] = ConnectionState.VALIDATING
178 valid = conn.is_valid()
179 if not valid:
180 self._states[conn.id] = ConnectionState.EXPIRED
181 return valid
182
183 # ---- Public API ----
184
185 def acquire(self) -> Connection:
186 """Borrow a connection from the pool. Blocks up to max_wait_sec."""
187 with self._not_empty:
188 deadline = time.monotonic() + self._config.max_wait_sec
189
190 while True:
191 # Try to grab an available, valid connection
192 while self._available:
193 conn = self._available.pop(0)
194 if self._validate(conn):
195 self._states[conn.id] = ConnectionState.IN_USE
196 self._in_use.add(conn.id)
197 thread = current_thread().name
198 print(f" [{thread}] Acquired conn-{conn.id}")
199 return PooledConnection(conn, self)
200
201 # Connection was stale : discard it
202 print(f" Discarded stale conn-{conn.id}")
203 del self._all[conn.id]
204 del self._states[conn.id]
205
206 # No available connections : can we create a new one?
207 if self._total_count() < self._config.max_size:
208 conn = self._factory.create()
209 self._all[conn.id] = conn
210 self._states[conn.id] = ConnectionState.IN_USE
211 self._in_use.add(conn.id)
212 thread = current_thread().name
213 print(f" [{thread}] Acquired NEW conn-{conn.id}")
214 return PooledConnection(conn, self)
215
216 # Pool exhausted : wait
217 remaining = deadline - time.monotonic()
218 if remaining <= 0:
219 raise TimeoutError(
220 f"Could not acquire connection within "
221 f"{self._config.max_wait_sec}s (pool size={self._config.max_size})"
222 )
223 print(f" [{current_thread().name}] Waiting for connection...")
224 self._not_empty.wait(timeout=remaining)
225
226 def release(self, conn: RealConnection) -> None:
227 """Return a connection to the pool."""
228 with self._not_empty:
229 if conn.id in self._in_use:
230 self._in_use.discard(conn.id)
231 self._states[conn.id] = ConnectionState.AVAILABLE
232 self._available.append(conn)
233 print(f" Returned conn-{conn.id} to pool "
234 f"(available={len(self._available)}/{self._config.max_size})")
235 self._not_empty.notify()
236
237 def shutdown(self) -> None:
238 """Close all connections and clear the pool."""
239 with self._lock:
240 for conn in self._all.values():
241 conn.close()
242 self._available.clear()
243 self._in_use.clear()
244 self._states.clear()
245 self._all.clear()
246 print(" Pool shut down : all connections closed.")
247
248 @property
249 def stats(self) -> dict:
250 with self._lock:
251 return {
252 "total": self._total_count(),
253 "available": len(self._available),
254 "in_use": len(self._in_use),
255 }
256
257
258 # --------------- Demo ---------------
259
260 if __name__ == "__main__":
261 import threading
262
263 ConnectionPool.clear_instances()
264
265 config = PoolConfig(max_size=3, min_idle=2, max_wait_sec=2.0)
266 pool = ConnectionPool.get_instance("localhost:5432", config)
267
268 print("=== Pool Stats After Init ===")
269 print(f" {pool.stats}")
270
271 print("\n=== Acquire & Execute ===")
272 c1 = pool.acquire()
273 c2 = pool.acquire()
274 print(f" c1 result: {c1.execute('SELECT * FROM users')}")
275 print(f" c2 result: {c2.execute('SELECT * FROM orders')}")
276 print(f" Stats: {pool.stats}")
277
278 print("\n=== Return c1 : Pool Reuses It ===")
279 c1.close()
280 print(f" Stats: {pool.stats}")
281
282 print("\n=== Acquire Again : Should Reuse Returned Connection ===")
283 c3 = pool.acquire()
284 print(f" c3 result: {c3.execute('SELECT count(*) FROM products')}")
285 print(f" Stats: {pool.stats}")
286
287 print("\n=== Exhaust Pool & Show Timeout ===")
288 # c2 and c3 are in use, pool max=3, acquire one more to fill it
289 c4 = pool.acquire()
290 print(f" Stats (full): {pool.stats}")
291
292 def try_acquire_blocked():
293 try:
294 conn = pool.acquire()
295 print(f" Blocked thread got conn: {conn.execute('SELECT 1')}")
296 conn.close()
297 except TimeoutError as e:
298 print(f" TimeoutError: {e}")
299
300 t = threading.Thread(target=try_acquire_blocked, name="Thread-Blocked")
301 t.start()
302
303 # Let the thread block, then return c2 so it can proceed
304 time.sleep(0.5)
305 print("\n=== Returning c2 to unblock waiting thread ===")
306 c2.close()
307 t.join()
308
309 print("\n=== Cleanup ===")
310 c3.close()
311 c4.close()
312 pool.shutdown()
313
314 print("\n=== Singleton Verification ===")
315 ConnectionPool.clear_instances()
316 p1 = ConnectionPool.get_instance("db.prod:5432", PoolConfig(max_size=3, min_idle=1))
317 p2 = ConnectionPool.get_instance("db.prod:5432")
318 print(f" Same instance? {p1 is p2}")
319 p1.shutdown()
320 ConnectionPool.clear_instances()
321
322 print("\nAll operations completed successfully.")Common Mistakes
- ✗Not returning connections (connection leak): the pool exhausts and all threads block
- ✗Skipping validation before borrowing: stale connections throw on first query
- ✗No max wait timeout: threads block forever when pool is exhausted
- ✗Creating multiple pools for the same data source: doubles connection count, hits DB max_connections
Key Points
- ✓Object Pool avoids repeated create/destroy of expensive resources: each connection costs a TCP handshake plus auth
- ✓Proxy is transparent: calling code uses the same Connection interface and doesn't know about the pool
- ✓State tracks each connection's lifecycle, preventing use of expired connections
- ✓Singleton per data source ensures one pool per database, preventing connection count explosion