Connection Pooling — Explained with Examples

Connection pooling reuses a cache of database connections to avoid the overhead of establishing a new connection for every request.

Connection Pooling is a technique where a pool of pre-established database connections is maintained and shared across requests. When an application needs a connection, it borrows one from the pool and returns it after use — avoiding the expensive TCP handshake, authentication, and SSL negotiation for each interaction.

Why Connection Pooling Matters

Opening a database connection takes 10–50ms of overhead (TCP, auth, SSL). For an application handling 1000 requests per second, creating a new connection per request wastes 10–50 seconds of CPU time per second. Pooling reduces this to near zero while also preventing database server resource exhaustion.

Real-World Analogy

A valet parking service. Instead of every guest driving to the garage, finding a spot, and walking back (open/close connection per request), the valet keeps a fleet of cars ready. You grab one (borrow from pool), drive it (execute query), and return it (return to pool). No parking overhead.

Example: Connection Pooling in Python

from queue import Queue
import time

class ConnectionPool:
    def __init__(self, max_size=5):
        self._pool = Queue(maxsize=max_size)
        for _ in range(max_size):
            self._pool.put(self._create_connection())

    def _create_connection(self):
        return {"id": id(self), "created": time.time()}

    def acquire(self):
        conn = self._pool.get()
        print(f"Acquired connection {conn['id']}")
        return conn

    def release(self, conn):
        print(f"Released connection {conn['id']}")
        self._pool.put(conn)

# Without pooling (slow)
def query_no_pool():
    conn = create_connection()  # TCP + auth + SSL = ~30ms
    result = execute_query(conn)
    close_connection(conn)      # overhead
    return result

# With pooling (fast)
pool = ConnectionPool(max_size=3)
def query_with_pool():
    conn = pool.acquire()       # ~0ms (pre-connected)
    result = execute_query(conn)
    pool.release(conn)          # reusable
    return result

Related Terms

Database Transaction, Replication, Database Index