Database connections are limited resources. Opening a new connection for every small operation can add latency and create unnecessary load, while holding connections indefinitely can exhaust the database’s capacity. Python web applications need a deliberate approach to connection creation, reuse, and cleanup.
Understand Connection Pooling
A connection pool maintains a set of reusable database connections. Instead of establishing a new connection for every operation, an application borrows one from the pool and returns it when finished.
Pooling is commonly supported by database drivers and libraries such as SQLAlchemy. Its configuration should reflect the database’s capacity and the number of application workers.
Use a Managed Session Lifecycle
With SQLAlchemy, a session factory can be configured once and used to create sessions for individual units of work.
from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerengine = create_engine( "postgresql+psycopg://user:password@localhost/appdb", pool_size=10, max_overflow=5, pool_timeout=30, pool_pre_ping=True,)SessionLocal = sessionmaker( bind=engine, expire_on_commit=False,)The connection string is illustrative. Production credentials should come from secure configuration rather than being committed to source control.
A request handler can manage the session with a context manager:
def get_customer(customer_id: int): with SessionLocal() as session: return session.get(Customer, customer_id)This assumes the Customer model is defined elsewhere. For write operations, transaction boundaries and commit behavior should be explicit.
Size Pools Across All Workers
A common mistake is to configure each application process independently without considering the total number of connections.
If six worker processes can each open fifteen connections, the application may consume up to ninety connections before accounting for other services. The database’s maximum connection limit must accommodate the aggregate workload.
Start with a conservative pool size and monitor connection wait time, database utilization, and request latency before increasing it.
Handle Transactions Correctly
A connection being available does not guarantee that a transaction is healthy. Failed operations may leave a transaction requiring rollback, and long-running transactions can hold locks or prevent database maintenance.
Use transaction contexts or explicit commit and rollback logic. Keep transactions short, and avoid making slow network calls while holding a database transaction open.
Monitor Pool Exhaustion
Symptoms of poor pool configuration include requests waiting for connections, rising latency, and timeouts under load. Investigate whether sessions are being closed correctly before simply increasing pool limits.
Also test how the application behaves when the database becomes temporarily unavailable and connections must be re-established.
Conclusion
Reliable database access depends on managed session lifecycles, sensible pool limits, and short transaction boundaries. Pool sizing should be based on the complete deployment topology rather than a single process in isolation.
