A worker process can live for days. Its database session should have a much smaller job.

When those lifetimes get blurred, symptoms show up somewhere else: pool timeouts, idle transactions, failed tasks, or requests waiting for connections that appear to be available.

Before increasing the pool, trace who checks out a connection, what they do while holding it, and which path returns it.

Make the unit of work explicit

SQLAlchemy's Session manages ORM state and transaction work. A clear boundary makes success, failure, and cleanup visible together. The Session basics documentation describes context managers that commit on success, roll back on failure, and close the session.

The following is an illustrative task boundary, rather than a complete worker implementation:

python
from sqlalchemy.orm import sessionmaker

SessionFactory = sessionmaker(bind=engine)

def record_result(job_id, result):
    with SessionFactory.begin() as session:
        job = session.get(Job, job_id)
        if job is None:
            raise LookupError(job_id)
        job.result = result

Here, engine and Job belong to the application. The context owns a fresh session for this write. It also gives the exception path a transaction policy instead of leaving cleanup to a distant caller.

Keep slow work outside the transaction when possible

A task that reads a record, calls a slow service, transforms a document, and then saves a result may hold scarce database resources longer than the database work requires.

Often, the better boundary is a short read, the external work, and a separate conditional write. That write must revalidate the assumptions from the read, using a version check or another application-specific concurrency rule.

The tradeoff is explicit: shorter transactions improve resource availability, while the application must handle state changing between them.

A failed session needs recovery

Logging an exception is not a transaction policy. If an operation fails during a flush, the session requires rollback before reuse. A context-managed unit of work makes that path easier to follow. Avoid sharing a Session across independent concurrent tasks; the concurrency guidance is one Session per thread or AsyncSession per task.

Retry the whole appropriate unit of work with fresh state. Retrying one statement against an already failed session can preserve the original problem.

Pool tuning follows lifecycle diagnosis

Pool size is a capacity decision. Pool recycling and connection validation address different failure modes; neither repairs a session that the application forgot to release. The SQLAlchemy pooling reference explains these controls and connection return behavior.

Measure checkout duration, concurrent demand, transaction age, and the database's connection limit. Count every application replica and worker process that creates a pool. A setting that looks modest in one process can become a large total across a deployment.

Start with ownership and cleanup. Then tune the pool against the workload those boundaries actually produce.