Pattern 7: Connection Pooling

The Problem: Connection Overhead

Without pooling: Each request opens a new connection to the database/API. Expensive (TLS handshake, auth, setup time).

With pooling: Reuse existing connections. Once authenticated, keep the connection alive and reuse it.

Trade-off: Need to manage pool size (too small = waits, too large = resource waste).

Connection Lifecycle

The gain: Handshake happens once. Then thousands of queries reuse that connection. 10-100× faster than creating new connections each time.

Pool State: Managing Open Connections

sequenceDiagram participant Pool participant Conn1 as Conn 1 participant Conn2 as Conn 2 participant DB as Database Note over Pool: Pool size=2, all idle rect rgb(200, 150, 255) Note over Pool: Request A arrives Pool->>Conn1: "Use this connection" Conn1->>DB: SELECT ... DB-->>Conn1: Results end rect rgb(200, 150, 255) Note over Pool: Request B arrives Pool->>Conn2: "Use this connection" Conn2->>DB: SELECT ... DB-->>Conn2: Results end rect rgb(255, 200, 150) Note over Pool: Request C arrives, no idle connections Pool-->>Pool: Queue request (wait for one to return) Conn1->>Pool: Done! Returning connection Pool->>Conn1: Give to Request C end

Code: Using Built-in Pools

HTTPx Client with Connection Pooling (Built-in)

import httpx
import asyncio

async def main():
    # ✅ GOOD: Share one client, reuse connections
    async with httpx.AsyncClient(limits=httpx.Limits(max_connections=100)) as client:
        tasks = [client.get(f"https://api.example.com/user/{i}") for i in range(1000)]
        results = await asyncio.gather(*tasks)

    # After the with block, all connections are closed gracefully

# ❌ BAD: Create new client per request
async def bad_example():
    for i in range(1000):
        async with httpx.AsyncClient() as client:  # Opens & closes connection each time!
            response = await client.get(f"https://api.example.com/user/{i}")
            # New TLS handshake every iteration!

asyncio.run(main())

Database Connection Pooling

import asyncpg  # PostgreSQL async driver

# Create pool at startup, reuse across all requests
async def init_db():
    pool = await asyncpg.create_pool(
        "postgresql://user:password@localhost/dbname",
        min_size=10,  # min connections to keep open
        max_size=20,  # max connections allowed
    )
    return pool

async def get_user(pool, user_id):
    # Get a connection from pool (reuse if available, else create)
    async with pool.acquire() as conn:
        result = await conn.fetchrow("SELECT * FROM users WHERE id = $1", user_id)
    return result

async def main():
    pool = await init_db()

    # All 1000 requests share the same 10-20 connections
    tasks = [get_user(pool, i) for i in range(1000)]
    results = await asyncio.gather(*tasks)

    await pool.close()  # Close pool on shutdown

asyncio.run(main())

Pool Configuration: min_size vs max_size

Setting	Default	Tuning
`min_size`	1-5	Keep this many open always. Lower memory, slight latency on burst.
`max_size`	10-100	Max to create on burst. Higher = handle spikes, but DB connection limits!