KAHIBARO
Discord Login Register ☰
☰

12.12. Connection Pooling

Table of Contents

Why Connection Pooling Matters

Every time your backend talks to a database, it needs a connection. Creating a new database connection is expensive. It usually involves:

If your application opens and closes a fresh connection for every request, it will:

Connection pooling solves this by reusing a small number of long‑lived connections instead of creating and destroying them all the time.

Core idea: A connection pool is a controlled, reusable set of open database connections that your application borrows and returns instead of creating new ones each time.

In modern backends, especially with ORMs, connection pooling is not an optional optimization. It is the normal, recommended way to talk to a production database.

What Is a Connection Pool?

A connection pool is a component that:

  1. Opens a configured number of database connections in advance, or on demand.
  2. Keeps those connections open and ready.
  3. Hands out a free connection when your code needs to talk to the database.
  4. Puts the connection back into the pool when your code is done.

You can think about it like a library:

If every reader had to buy a new copy of the book for a 5‑minute read, this would be wasteful. That is what it looks like when you create a new database connection per request.

Basic lifecycle

  1. Application starts.
  2. Connection pool is created with some parameters, for example:
    • pool_size = 5
    • max_overflow = 10
  3. A web request arrives that needs the database:
    • Pool gives a free connection if one is available
    • If not available, the pool may open a new one (depending on config) or wait
  4. Your query runs using that connection.
  5. When you finish, the connection is returned to the pool.

The connection is not closed, it is reused for future requests.

Connection Pool Settings and Terminology

Different ORMs and drivers use different names, but the ideas are the same. Here are common settings using SQLAlchemy style names, since that is what most Python ORMs build upon.

SettingWhat it controlsTypical effect
pool_sizeBase number of open, reusable connectionsConcurrency capacity from the pool
max_overflowExtra connections over pool_size allowed under loadHow much the pool can temporarily grow
pool_timeoutHow long to wait for a free connection before raising errorControls waiting vs failing fast
pool_recycleMax lifetime (seconds) of a connectionHelps avoid stale / dropped connections
max_connsDriver specific total connection limitHard safety cap (used in some drivers)

pool_size

pool_size controls how many connections the pool will maintain regularly.

max_overflow

max_overflow allows the pool to temporarily open more connections than pool_size.

This helps handle traffic spikes without permanently increasing the base pool size.

pool_timeout

If all allowed connections (base plus overflow) are busy, new requests will either:

pool_timeout is that waiting limit. For example, pool_timeout = 30 means:

pool_recycle

Many databases or network devices drop idle connections after some timeout, for example after 8 hours. If your application keeps connections longer than this, you can get weird errors like:

pool_recycle forces connections to be closed and re‑created after a certain age.

For example, pool_recycle = 1800 (30 minutes) means:

Rule of thumb: Set pool_recycle to a value lower than your database or network idle timeout to avoid stale connection errors.

Connection Pooling with SQLAlchemy

SQLAlchemy includes connection pooling by default. You rarely need to create a pool manually, it is created when you configure your Engine.

Basic example

python 
from sqlalchemy import create_engine
DATABASE_URL = "postgresql+psycopg2://user:password@localhost:5432/mydb"
engine = create_engine(
    DATABASE_URL,
    pool_size=5,        # base pool
    max_overflow=10,    # extra connections for spikes
    pool_timeout=30,    # wait up to 30 seconds
    pool_recycle=1800,  # recycle connections after 30 minutes
)

When you do:

python 
with engine.connect() as conn:
    result = conn.execute("SELECT 1")

SQLAlchemy uses the pool under the hood:

  1. It checks out a connection from the pool.
  2. Executes the query.
  3. Returns the connection to the pool when the context manager exits.

You do not call engine.connect() + connection.close() per request to create new database connections. Those calls use pooled connections.

Using the engine with a session

With the ORM, you usually work through a Session object:

python 
from sqlalchemy.orm import sessionmaker
SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False)
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Here:

This pattern is common in FastAPI dependencies and similar frameworks.

Common Pooling Patterns in Web Applications

One engine per process

A typical backend process will:

For example, in a FastAPI style application:

python 
# db.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
DATABASE_URL = "postgresql+psycopg2://user:password@localhost/db"
engine = create_engine(
    DATABASE_URL,
    pool_size=5,
    max_overflow=5,
    pool_timeout=30,
    pool_recycle=1800,
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Each web request gets its own SessionLocal() instance, but all of them share the same underlying connection pool.

One pool per process, many processes

In production, you often run multiple worker processes.

For example:

If pool_size = 5 and max_overflow = 5:

You must consider this when configuring the database maximum connections.

Synchronous vs Asynchronous Connection Pooling

In modern Python backends you may have:

Pooling still exists in both cases, but the implementation details differ.

Sync engine example

python 
from sqlalchemy import create_engine
engine = create_engine(
    DATABASE_URL,
    pool_size=5,
    max_overflow=5,
)

All database operations block the current thread until a response is received. The pool manages TCP connections that are used by synchronous DB calls.

Async engine example

python 
from sqlalchemy.ext.asyncio import create_async_engine
DATABASE_URL = "postgresql+asyncpg://user:password@localhost/db"
async_engine = create_async_engine(
    DATABASE_URL,
    pool_size=5,
    max_overflow=5,
)
from sqlalchemy.ext.asyncio import async_sessionmaker
AsyncSessionLocal = async_sessionmaker(
    bind=async_engine,
    expire_on_commit=False,
)
async def get_db():
    async with AsyncSessionLocal() as session:
        yield session

Here:

Connection Pool Tuning

Choosing pool settings is about balancing:

There is no single best number, but there are practical guidelines.

Basic rules of thumb

  1. Start small, then measure.
    For many small applications you can start with:
    • pool_size between 5 and 10
    • max_overflow between 5 and 10
  2. Stay under the database limit.
    Suppose:
    • PostgreSQL max_connections = 100
    • You will have 4 application processes
    • Other services need some connections too, for example background workers

You might choose:

  1. Set pool_recycle.
    Use a value lower than any known idle timeout in:
    • Database configuration
    • Load balancers
    • Cloud networking

If you are unsure, 30 minutes to 1 hour is often safe.

  1. Avoid giant pools.
    A pool with 100 connections in one process often means:
    • The database is overloaded by idle or competing connections
    • You are hiding a deeper performance or design problem

It can be better to tune queries and add caches than to keep growing the pool.

Example scenarios

Low traffic development environment

Suggested config:

python 
engine = create_engine(
    DATABASE_URL,
    pool_size=3,
    max_overflow=2,
    pool_timeout=30,
    pool_recycle=1800,
)

Medium traffic production API

Suggested config per process:

python 
engine = create_engine(
    DATABASE_URL,
    pool_size=5,
    max_overflow=5,
    pool_timeout=30,
    pool_recycle=1800,
)

Maximum app connections:

Common Problems and How to Avoid Them

Problem 1: "Too many connections" errors

You may see errors like:

Possible reasons:

How to fix:

Problem 2: Stale or dropped connections

Symptoms:

Common cause:

Fix:

python 
engine = create_engine(
    DATABASE_URL,
    pool_pre_ping=True,
    pool_recycle=1800,
)

Problem 3: Deadlocks or "database feels stuck"

If connections are never returned to the pool, the pool eventually runs out. Every new request will:

Common causes:

Solutions:

python 
  def get_db():
      db = SessionLocal()
      try:
          yield db
      finally:
          db.close()

Important rule: Always return connections to the pool. In SQLAlchemy, that means closing sessions or connection objects when you are done.

Practical Examples

Example: API endpoint using a pooled session

Imagine a simple user repository method using SQLAlchemy ORM:

python 
from sqlalchemy.orm import Session
from .models import User
class UserRepository:
    def __init__(self, db: Session):
        self.db = db
    def get_user_by_id(self, user_id: int) -> User | None:
        return self.db.query(User).filter(User.id == user_id).first()

An endpoint in a FastAPI app might look like:

python 
from fastapi import Depends, HTTPException
from .db import get_db
from .repositories import UserRepository
@app.get("/users/{user_id}")
def read_user(user_id: int, db: Session = Depends(get_db)):
    repo = UserRepository(db)
    user = repo.get_user_by_id(user_id)
    if not user:
        raise HTTPException(status_code=404, detail="User not found")
    return user

Key connection pooling behaviors here:

No extra code is needed to manage the pool directly. It is handled by the ORM and engine configuration.

Example: Background worker and pooling

If you use a background worker process, for example Celery, do not create a new engine for every task. Instead:

python 
# worker_db.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
DATABASE_URL = "postgresql+psycopg2://user:password@localhost/db"
engine = create_engine(
    DATABASE_URL,
    pool_size=3,
    max_overflow=2,
    pool_recycle=1800,
)
SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False)

Then in a task:

python 
from .worker_db import SessionLocal
def send_daily_report():
    db = SessionLocal()
    try:
        # use db to query and update data
        ...
        db.commit()
    except:
        db.rollback()
        raise
    finally:
        db.close()  # important to return connection to the pool

Again, each task checks out a pooled connection and returns it when finished.

Key Takeaways

A solid understanding of connection pooling helps you design backends that scale without overwhelming your database, and it prevents many hard to debug production issues.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3705 / 24h
Impressum / Imprint
Datenschutz / Data Policy