Apache Airflow and SQLAlchemy: Connection Pool Exhaustion in Distributed Executor Environments

Recommended sizing strategy

First determine the hard connection limit of your metadata database (max_connections). Subtract a safety headroom (e.g., 10‑20 %) for Airflow’s own metadata sessions, monitoring tools, and any other services that share the same instance. The remaining connections are the budget for worker‑side SQLAlchemy pools.

Let W be the total number of concurrent worker processes (scheduler + executor workers). Divide the budget by W to obtain a per‑worker target T. Configure Airflow so that:

• sql_alchemy_pool_size + sql_alchemy_max_overflow ≤ T
• Prefer a modest pool_size (e.g., 5) and allow max_overflow to absorb short bursts, but never exceed T.
• Enable pool_pre_ping=True and a reasonable pool_recycle (e.g., 1800 s) to avoid stale connections in long‑lived workers.

Scope the SQLAlchemy Engine and Session to the task execution context (e.g., inside the task function or a with Session(engine) as session: block). This ensures connections are returned to the pool promptly, regardless of executor type.

Session teardown differences

CeleryExecutor

Worker processes are long‑lived and reuse the same Engine across many task invocations unless you recreate it per task. If a session is not explicitly closed or used in a context manager, the connection stays checked out for the lifetime of the worker (or until the worker is recycled). This can cause connections to accumulate during high‑concurrency bursts.

KubernetesExecutor

Each task runs in an isolated pod that is terminated after the task finishes. The Engine and any Session objects are created when the pod starts and are destroyed when the pod exits, automatically returning connections to the database. Even if a session is not closed, the pod’s termination releases the connection, making leaks less persistent than with Celery workers.

Verification steps (safe, scoped)

1. Check the database’s hard limit:

SELECT setting::int AS max_connections FROM pg_settings WHERE name = 'max_connections';

2. Measure current active connections during a peak load window:

SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

3. Confirm the per‑worker pool settings in airflow.cfg or environment variables:

echo $AIRFLOW__DATABASE__SQL_ALCHEMY_POOL_SIZE
 echo $AIRFLOW__DATABASE__SQL_ALCHEMY_MAX_OVERFLOW

4. Validate that (pool_size + max_overflow) * worker_count stays below the headroom‑adjusted max_connections.

If you do not know your database’s max_connections, please provide that value; it directly influences the per‑worker pool target and may change the recommendation.