Connection Pooling — Explained with Examples

In this tutorial, you'll learn about Connection Pooling. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

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