Bound background-job concurrency and size the DB pool¶
Problem¶
Uploading a factor CSV produced QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00.
Root cause: backend/app/db.py creates the async engine with no pool config, so SQLAlchemy defaults to pool_size=5, max_overflow=10, pool_timeout=30 — the exact numbers in the error. schedule_job -> fire_and_forget(dispatch_job) (app/tasks/_poller.py) has no concurrency bound: the poller sweeps up to POLLER_BATCH_LIMIT=100 jobs every 2s, and chain_job's deferred-dispatch drain fans out the same way. A factor upload chains one emission_recalc child per (module, det); each running job holds 1-2 connections (runner job_session + handler data_session) for its whole runtime, so the 15-connection default pool exhausts on the first sweep — the HTTP upload request is the collateral timeout victim.
INGEST_COPY_BATCH_SIZE is unrelated. Lowering POLLER_BATCH_LIMIT would only shrink one of the two stampede paths (poller sweep, chain-job drain), not both.
We run 2-3 replicas in prod: starvation is per-pod, but the fleet also multiplies total Postgres connections — the design accounts for both.
Design¶
1. Per-pod job concurrency bound (the fix)¶
New MAX_CONCURRENT_JOBS setting (proposed default 4/pod -> 8-12 running jobs fleet-wide at 2-3 replicas). One asyncio.Semaphore in the runner, acquired in run_job before the DB claim.
The ordering is the load-balancing trick: a queued job holds no connection and no claim, so an idle replica's poller can claim it instead of it sitting stuck behind a busy pod's semaphore — saturation on pod A drains via pods B/C.
The heartbeat task is exempt: it must beat while a job runs, and a queued job has no claim to heartbeat yet.
2. Explicit pool settings (conscious tuning, same PR)¶
New settings, wired into create_async_engine in backend/app/db.py (skipped for sqlite):
DB_POOL_SIZE(default 10)DB_MAX_OVERFLOW(default 10)DB_POOL_TIMEOUT(default 30)
Documented in env.example next to the poller block.
Fleet math: 3 replicas x 20 max (pool_size + overflow) = 60 connections peak vs Postgres default max_connections=100. Per pod: <=4 jobs x 2 conns = 8 reserved for background work, >=12 remain for HTTP.
Superseded numbers (#2566, 2026-08-31): the fleet math below assumed connections are released when a pod exits. They were not —
engine.dispose()was never called, so each terminated pod orphaned its pool for ~2 h and dev hitmax_connections.DB_POOL_SIZEis now5inhelm/values.yaml, sized from measured peakchecked_out(~4/pod). See 2566.
3. Ops note: no PgBouncer (superseded 2026-09-08)¶
Superseded: DBaaS put PgBouncer in front of dev, stage and prod. The ceiling is now the bouncer's server pool, not
max_connections— see the database overview.
Direct connections fit comfortably at 2-3 replicas. PgBouncer adds a hop, and its transaction-pooling mode sits badly with long recalc transactions. Revisit when replicas x (pool_size + overflow) approaches ~80% of max_connections — roughly 4+ replicas at these defaults, or sooner on a managed-PG tier with a small max_connections.
Steps¶
- Add
MAX_CONCURRENT_JOBSsetting (default 4; kept the plan's default — see "Implementation notes"). - Add one
asyncio.Semaphore(MAX_CONCURRENT_JOBS)in the runner, acquired inrun_jobbefore the DB claim; exempt the heartbeat task. - Add
DB_POOL_SIZE/DB_MAX_OVERFLOW/DB_POOL_TIMEOUTsettings (defaults 10/10/30 — kept the plan's default) and pass them tocreate_async_engineinbackend/app/db.py, skipped for sqlite. - Document the new pool settings in
backend/.env.examplenext to the poller block (repo uses.env.example, notenv.example). - Add ops note on skipping PgBouncer, with the
replicas x (pool_size + overflow)~= 80% ofmax_connectionsrevisit trigger — landed in ADR-004, which already documented pooling/PgBouncer. - Unit tests: N blocking fake jobs -> at most
MAX_CONCURRENT_JOBSrun concurrently; a queued job staysNOT_STARTED(claimable by another pod); heartbeats unaffected; settings passthrough into the engine. Seebackend/tests/unit/tasks/test_runner_concurrency.pyandbackend/tests/unit/test_db_pool_settings.py. - Real repro: re-run the same factor CSV upload in a live environment — success is no QueuePool timeout and
pg_stat_activitystaying within the per-pod cap during the recalc fan-out. Not run as part of this PR (no live repro environment in this session) — flagged for the owner before merge.
Implementation notes (as landed)¶
MAX_CONCURRENT_JOBS=4, not 8: kept the plan's conservative default. 8/pod x 2-3 replicas = 16-24 concurrent jobs x ~2 connections each = 32-48 connections just for background work, eating deep into theDB_POOL_SIZE=10 + DB_MAX_OVERFLOW=10 = 20/pod budget before HTTP traffic is accounted for. 4/pod keeps the fleet-wide job-connection footprint (8-16 connections) comfortably under headroom; raise later if throughput data justifies it.DB_POOL_SIZE=10/DB_MAX_OVERFLOW=10, not 5+10: the default 5+10 is exactly what produced the original bug report, so keeping it unchanged would leave the pool the same size while only adding the job-concurrency bound as the fix. Doublingpool_sizeto 10 gives the explicit-settings change real headroom of its own (20/pod vs the previous 15), matching the plan's fleet math (3 x 20 = 60 vs Postgres defaultmax_connections=100).- The semaphore is acquired via
async with _get_job_semaphore()wrapping everything fromclaim_jobthroughfinish_job(including the heartbeat-task lifecycle) — released only whenrun_jobreturns. The heartbeat task itself never acquires the semaphore (it's a separateasyncio.Task), satisfying the "heartbeat exempt" requirement without extra bookkeeping.