Database Connection Pooling

Scenario

Your app goes viral. You get 5,000 concurrent users. Suddenly, Postgres refuses all connections with `FATAL: sorry, too many clients already`. Your app is entirely offline.

Mental model

A connection pool is like a taxi rank at an airport. Instead of buying a new car every time a passenger arrives (expensive and slow), passengers take an available taxi, use it, and return it to the rank for the next person.

Connection pooling maintains a fixed number of open database connections. The application borrows a connection, executes a query, and returns the connection to the pool rather than closing it. This eliminates the massive overhead of TCP and authentication handshakes on every request.

Deep dive

Opening a TCP connection to a database is extremely slow and expensive. It requires TCP handshakes, TLS negotiation, and authentication. If every HTTP request opened a new connection, your API latency would be terrible.

Opening database connections is slow.

Databases also have strict limits on concurrent connections (e.g., Postgres defaults to 100). If you have 200 web workers all trying to connect at once, the database will reject the excess connections and crash your application.

Databases limit the total number of simultaneous connections.

A Connection Pool solves this. When your application starts, it creates a 'pool' of a fixed number of open database connections (e.g., 20). When a request comes in, it borrows a connection from the pool, uses it, and returns it.

Pools reuse a small number of connections to serve thousands of requests.

SQLAlchemy provides connection pooling out of the box via the Engine. You configure pool_size (number of persistent connections) and max_overflow (how many extra it can create under heavy load). If the pool is empty, requests will wait in a queue until a connection is returned.

SQLAlchemy's Engine manages the pool automatically.

Code examples

Configuring the SQLAlchemy Pool

from sqlalchemy.ext.asyncio import create_async_engine

# Create an engine with a connection pool
engine = create_async_engine(
    "postgresql+asyncpg://...",
    pool_size=20,       # Keep 20 connections open
    max_overflow=10,    # Allow up to 10 extra during spikes (30 total)
    pool_timeout=30     # Wait up to 30s for a free connection before crashing
)

The engine manages the pool. When an AsyncSession is created, it checks out a connection from this pool. When the session closes, the connection is returned to the pool, not destroyed.

Leaking a connection (The Bug)

@app.get("/data")
async def get_data():
    session = async_sessionmaker()
    # BUG: If we don't close() or use a context manager/dependency,
    # the connection is never returned to the pool.
    # After 20 requests, the entire application will hang forever.
    result = await session.execute(select(User))
    return result.scalars().all()

This is why we strictly use FastAPI dependencies (`yield session`) to manage sessions. They guarantee that session.close() is called (which returns the connection to the pool) even if the request crashes.

Key points

• Connection pooling is mandatory: Never create a raw connection per request. It destroys database performance.
• Return your connections: If you don't close the session, the connection is 'leaked'. The pool will quickly empty and the app will freeze.
• PgBouncer: For massive scale, an external pooler like PgBouncer sits in front of Postgres to multiplex thousands of app connections onto a few dozen real database connections.

Common mistakes

• Setting pool_size too high: More connections do not equal more speed. If you have 8 CPU cores on your database, a pool size of 500 will cause context-switching thrashing. A smaller pool (e.g., 20) with a queue is significantly faster.

Recall questions

• Why is opening a new database connection per HTTP request a bad idea?
• What does pool_size=20 mean in SQLAlchemy?
• What happens if a request asks for a connection, but the pool is empty?

Questions & answers

Our FastAPI app works fine for 5 minutes after deployment, but then completely freezes. Database CPU is at 0%. What is the most likely cause?

Connection leaking. The application is checking out connections from the pool but failing to close/return them. After 5 minutes, the pool is empty, and all new requests are stuck waiting indefinitely for a connection to become available.

We have 50 Kubernetes pods, each running 4 Gunicorn workers. Each worker has a SQLAlchemy pool_size of 20. Our Postgres max_connections is 100. What will happen?

50 pods × 4 workers × 20 connections = 4,000 potential connections. Postgres will instantly reject them with a 'too many clients' error. You need to lower the pool size or use an external multiplexer like PgBouncer.

Continue learning

Previous: Transactions and Atomicity

Return to Python Backend Roadmap