DB queries queue at the DBaaS PgBouncer (#2689)¶
Symptom¶
2026-09-08, dev, while bulk-uploading CSVs from the backoffice: streams
showed "Connection to sync job lost", /ready flapped to 503 on backend
and worker pods, ingest jobs died mid-run, and the pod-saturation panel
showed backend pod 5p8vq at 17/17 (94%). Waves at 13:52, 13:58, 14:15,
14:17, 14:19, 14:21 UTC, then again around 20:30 to 22:00 UTC. Loki counts
66 query_wait_timeout lines in dev over the day, none in stage or prod
in the same two days.
What actually happened¶
Every error, backend and worker, is
psycopg.errors.ProtocolViolation: query_wait_timeout
on trivial statements (SELECT ... FROM data_ingestion_jobs WHERE id = 71,
SELECT 1, pg_advisory_xact_lock), each after ~120 s. That string is
PgBouncer's "client waited too long for a server connection". DBaaS
confirmed on 2026-09-08 that dev, stage and prod are bounced, with "about
1000" as the client limit. On dev every server-side connection arrives
from one address (10.95.64.75), the bouncer.
Tempo for the incident window ({duration>1s}, 148 traces):
| Root | Count | What it was doing |
|---|---|---|
GET /v1/sync/pipelines/{id}/stream |
46 | per-poll job select blocked 112 to 118 s |
GET /v1/sync/jobs/{id}/stream |
29 | same |
bare SELECT / UPDATE / INSERT (backend + worker background loops) |
36 | 50 to 120 s, all query_wait_timeout |
worker job csv_ingest / factor_ingest |
15 | up to 700 s |
GET /v1/sync/pipelines, /active-pipelines, /year-configuration/ |
5 | 88 s on SELECT users (get_current_user) |
Nobody was slow. Everybody was queued at the bouncer. The 17 on pod 5p8vq
were 17 coroutines each holding one SQLAlchemy slot while waiting; the
error-level log export for the whole day contains zero
QueuePool limit ... reached lines, so our pool never refused anyone. The
in-flight list on that pod at the peak (kind=server && duration>2s) is 19
stream polls and one connector check, all waiting, no holder.
Prometheus max(db_server_connections) over 21 days:
| Env | Baseline | Peaks |
|---|---|---|
| dev | 20 | 38 to 41 on 09-02, 09-03, 09-07, 09-08 |
| stage | 19 | 46 on 09-01 |
| prod | 17 | 31 on 09-01 |
Caveat: that gauge is emitted by the pod heartbeat, which itself blocks at the bouncer during a stall, so the flat line during a wave is a frozen value. The repeated ~40 plateau across four days is real.
Root cause on our side¶
get_current_user took its session from get_db, a yield dependency.
Since FastAPI 0.118 a yield dependency is released only after the
response is fully sent, and the SELECT users autobegins a transaction, so
every authenticated request pinned one pooled connection from auth to the
last byte. For a JSON route that is milliseconds. For an SSE stream it is
minutes, for a CSV temp-upload it is the whole S3 write, for the connector
test it is the outbound HTTP call. #2654 patched the two streams with a
second dependency (get_current_user_detached) and left every other route
as it was. Under a bulk upload the pinned connections outnumbered the
bouncer's server pool, and from then on every new checkout, including the
health poller's SELECT 1, queued 120 s and died.
Two more of ours made it worse:
_db_health._check_oncewrapped the probe inasyncio.timeout(1). Cancelling a SQLAlchemy call mid-checkout blocks in the connection teardown until the server answers, so the loop stopped ticking for the whole 120 s wave and/readyreported "unknown"/503 (worker log: 13:52:05 to 13:52:55, 13:57:05 to 13:58:52, 14:13:22 to 14:15:59 UTC, each ending on a Task-6 invalidation).- The worker overlay still shipped
DB_MAX_OVERFLOW: "50"in all three environments while its comment said "both 2+15".
Shipped¶
get_current_userreleases its connection (app/core/security.py): after the lookup it expunges theUserand rolls the session back, so the connection is in the pool before the route body runs. The route's ownget_dbsession is untouched: its first query autobegins again and takes a connection only then.Userhas no relationships, so a detached instance is complete.get_current_user_detachedis deleted; the two streams and their tests use the one dependency. Regression test:tests/unit/core/test_get_current_user_releases_connection.py(fails on the old code: the user is still attached and the session in a transaction)./readysurvives a stall (app/tasks/_db_health.py): the probe runs as its own task and is waited for, never cancelled. A probe that outlives the 1 s wait is reported "down" now and re-awaited on the next tick, so a hung teardown neither freezes the loop nor stacks a second connection behind the stall. Regression test:test_stuck_probe_teardown_does_not_freeze_the_loop(fails on the old code: the tick takes the whole hang).- Poller log noise (
app/tasks/_poller.py): "dispatching job N" is logged insideschedule_jobafter the in-flight guard, once per real dispatch instead of every 2 s sweep. - 5+50 on backend and worker, dev, stage, prod (openshift-app-config,
both
configMapGeneratorblocks per overlay, comment rewritten). 5 is the per-pod base DBaaS recommends; overflow sockets are closed on check-in, so 50 is burst room.DB_POOL_TIMEOUTstays 5 s: it bounds the wait for one of our own slots, never the in-query wait at the bouncer, and a request that fails in 5 s in-process beats one that queues 120 s. application_name=co2-<pod>on every Postgres connection (app/db.py): behind the bouncer every backend shares oneclient_addr, so this is what tells pods, and laptops running the app against dev, apart inpg_stat_activity.- Docs:
database/01-overview.mdis the canonical PgBouncer note; ADR-004 and plan 1723's "no PgBouncer" sections are marked superseded; the architecture pages drop the "no PgBouncer" claims.
Pool sizing (2026-09-09, after the probe)¶
The 5+50 shipped in the morning assumed a ~40 bouncer pool and turned out
wrong twice: dev's pool probed at 35 (so 20 base connections left no room
for a rollout), and prod's bouncer never queues at all, Postgres refuses
at max_connections=100, so 3 × 55 + 55 = 220 could lock Postgres for
everyone. Replaced by the measured budgets, rule and per-env table in
database/02-connection-budget.md;
values in openshift-app-config#41. MAX_CONCURRENT_JOBS 10 on dev was
never deployed (#40 merged before that commit) and is now 2 there.
Still open¶
- Pool mode on the app path. From pgAdmin over VPN on stage,
SET+ pid stay stable across autocommit statements, which is session behaviour, but that session shows up inpg_stat_activitywith the laptop's own IP, so the VPN path is probably not bounced. Check withSELECT inet_client_addr()from psql on dev: the bouncer's address means the psql path is bounced and theSET+ pid test is conclusive there. Otherwise run it from a pod:
oc -n svc1751d-co2-calculator-dev exec deploy/co2-calculator-backend -- \
uv run python -c "
import asyncio, psycopg
from app.db import engine
url = engine.url.render_as_string(hide_password=False)
async def main():
async with await psycopg.AsyncConnection.connect(url, autocommit=True) as c:
for _ in range(5):
cur = await c.execute('select inet_client_addr(), pg_backend_pid()')
print(await cur.fetchone())
asyncio.run(main())"
Same pid every time: session pooling. Changing pid: transaction pooling,
and then prepare_threshold=None goes into connect_args unless the
bouncer is 1.21+ with max_prepared_statements.
- Server pool size. DBaaS says ~1000; that is a client limit. The
number that ran out is the bouncer's pool towards Postgres, or Postgres
max_connections=100when the bouncer cannot open a new backend. The ~40 plateau is the effective cap until DBaaS says otherwise. Questions sent per environment:pool_mode,default_pool_size/max_db_connections,query_wait_timeout,server_idle_timeout/server_lifetime(rotation, so orphans from dead pods do not hold slots),client_idle_timeout, PgBouncer version. - Routes that hold their own session across slow work. After this PR
the connection is held from a route's first query to the end of the
response.
POST /v1/connectors/{connector}/testreads config, then does the outbound probe, then audits, all on one session. Audit the others with{kind=server && duration>500ms}in Tempo once dev has run a week on this build. - Dashboard and alert (openshift-app-config): drop the
max_connections=100reference line, plot the ~40 plateau, add a Loki alert onquery_wait_timeoutper namespace (one Grafana per cluster). - Long ingests (job 59: 144k rows in one transaction, ~70 min at 34 rows/s): read the trace first. Batch commits touch pipeline idempotency (310-series, 1215, 1219, 1559, 1723) and need a written plan reviewed by both maintainers.
Checked and closed¶
_build_aggregation_scope_configis not a parallel fan-out:_gatherruns once on one helper session. A running job holds three connections at most (job_session,data_session, the helper), so the worker's demand is3 × MAX_CONCURRENT_JOBSplus its loops, not 50.- A handler failure ends in FINISHED+ERROR, no automatic retry. Only the
stale-RUNNING sweep re-dispatches, after
STALE_JOB_TIMEOUT_MINUTES, so aquery_wait_timeoutwave does not retry itself into a storm. - SIGTERM reaches uvicorn: the Dockerfile
CMDissh -c "exec …", so the lifespan teardown andengine.dispose()(#2566) run. The backend Deployment has a 5 spreStopand 35 s grace; the worker Deployment has neither and gets the Kubernetes default 30 s, which also caps how long a running ingest gets to finish before the stale sweep takes over. - Every path to dev goes through the bouncer, VPN included: a psql
session from a laptop shows
client_addr = 10.95.64.75like the pods. A pgAdmin tab or a laptop running the app against dev holds server slots exactly like a pod.
Rejected¶
max_overflow200 or -1: moves the queue from our pool to the bouncer, where the wait is 120 s instead of 5 s.- Raising
DB_POOL_TIMEOUT: same reason, and it does not bound the in-query wait anyway. pool_use_lifo=True: it only changes which idle connection is reused; it does nothing for queueing. Worth adding if the pod-side test says session pooling, so idle base connections age out server-side.- A second auth dependency per slow route (the #2654 shape): every route is a slow route behind a stalled bouncer. One dependency that releases is smaller than two that differ.
- Frontend retry tolerance on the streams (first cut of #2654): hides the 500 and leaves the queue in place.
Verification¶
uv run pytest tests/unit: 2905 passed on sqlite; both regression tests verified failing with the fix reverted.make lint, backendmake type-checkgreen.- After the config PR:
db_client_connections_usage{state="size"}reads 5 per pod on every env. - Next upload burst in dev: Loki
query_wait_timeoutcount stays at 0, and/readynever leaves 200 for more than one probe.
Update 2026-09-23 — named prepares off ahead of transaction pooling¶
Dev's bouncer moves from pool_mode: session to transaction, and its
max_prepared_statements is unconfirmed (1.25.1 defaults to 200; the
DBaaS chart may pin 0). Shipped: prepare_threshold: None in the Postgres
connect_args of backend/app/db.py, safe under both modes. psycopg
3.3.5 with libpq 18 supports prepared statements through PgBouncer ≥ 1.22
natively, so the line is deleted — not tuned — once SHOW CONFIG on the
dev bouncer reports max_prepared_statements > 0 and the probe above
shows a changing pg_backend_pid().
Code audit for transaction-mode hazards, all clean: advisory locks are
pg_advisory_xact_lock only, both temp staging tables are ON COMMIT
DROP, no LISTEN, no session-level SET, no WITH HOLD cursors.
Update 2026-09-23 evening — named prepares back on¶
DBaaS raised max_prepared_statements 0 → 200 the same day; the
probe_pgbouncer_prepared.py run on dev succeeded across three server
pids (transaction mode and replay confirmed). Merged as #2922.
The #2921 stopgap prepare_threshold: None is removed: psycopg 3.3.6 with
libpq 18 replays prepared statements through PgBouncer ≥ 1.22 natively,
so hot queries get their cached plan back. A unit test pins libpq ≥ 17 so
a psycopg-binary downgrade cannot silently reintroduce prepared statement
did not exist. Bump psycopg 3.3.5 → 3.3.6 in the same PR.
Checklist 2026-09-24 — DBaaS session¶
Dev bouncer runs transaction pooling since 2026-09-23: 1000 clients, pool 60, min 5. Two draft PRs wait on this session:
- co2-calculator#2922 — prepared statements back on
- openshift-app-config#60 — pods sized by CPU/memory, real HPA
1. Read the current config¶
-
SHOW CONFIG— write down these seven values
pool_mode, max_prepared_statements, query_wait_timeout,
server_idle_timeout, max_db_connections, default_pool_size,
reserve_pool_size
-
SHOW POOLS— readcl_waiting,sv_active,sv_idle,maxwait
cl_waiting above 0 at rest means a stray client is holding slots.
-
pool_modesaystransaction— that is the definitive check
The single-connection probe higher up is only suggestive: a quiet client often gets the same server connection back.
- Run
scripts/probe_pgbouncer_prepared.pythrough the bouncer
Two connections interleaved; it prepares a statement on one and
re-executes it while the other forces the bouncer to rotate server
connections. A clean run across several pids means
max_prepared_statements > 0 in practice, and #2922 is safe.
2. Decide on prepared statements¶
-
max_prepared_statementsabove 0 → #2922 goes ahead -
max_prepared_statementsis 0 → ask for 200
Refused? Close #2922. The #2921 stopgap stays.
3. Ask for these settings¶
-
query_wait_timeout→ 10 s (default 120)
Gate for #60. Past 60 in-flight transactions the bouncer queues, and a queued request holds its pod slot for the whole wait. At 120 s that is the 2026-09-17 lockup (#48).
- Verify it:
scripts/probe_pgbouncer_wait_timeout.pythrough the bouncer
Fills the pool with open transactions until one client is queued,
then times how long the bouncer lets it wait. Prints the measured
query_wait_timeout and the #60 verdict. Blocks dev for fill time +
20 s at most; run it when nobody is testing.
-
reserve_pool_size→ 10,reserve_pool_timeout→ 3 s
A burst gets ten extra server slots after three seconds of queueing.
-
max_db_connections→ 80
Postgres max_connections is 100. A second user/db pair would
otherwise get its own pool of 60.
-
server_idle_timeoutshorter than the role'sidle_session_timeout
Otherwise Postgres kills the bouncer's idle server connections.
Bring the pg_settings and pg_db_role_setting output (the role
GUC was set 2026-08-31, #2566).
4. Ask about the other environments¶
- Stage: same bouncer setup, 2026-09-24 (pool 70, max_prepared_statements 500, max_client_conn 1000). openshift-app-config #67 is stage's #60.
- Prod: when?
Until then prod keeps its direct-Postgres numbers.
5. Back at the desk¶
- Fix the worker replica count
Dev ran 4 worker pods on 2026-09-23. Chart default is 1, the overlay
sets none. Scale back or pin worker.replicaCount before #60.
- Merge #2922
Watch the worker log for ten minutes: no
prepared statement did not exist.
- Merge #60
Watch oc get hpa and the pool saturation panel for an hour.
Before the stage/prod version of #60¶
Collect from prod:
- p95 and peak requests per second over 30 days
- p95 CPU and peak memory per backend pod
- peak worker memory on the largest CSV job
- peak concurrent active backends in
pg_stat_activity
Prod has no bouncer yet. Its pool math stays the direct-connection kind until DBaaS extends the bouncer there.
Update 2026-09-24 afternoon — stage joins, and the quota trap¶
DBaaS put stage behind the same bouncer (transaction mode, pool 70,
max_prepared_statements 500). openshift-app-config #67 gives stage dev's
shape: backend 100m / 512Mi with HPA 3..6 on 70 % CPU and pool 5+45,
worker 250m / 768Mi with HPA 1..3 on 60 % and pool 5+15, dashboard and
the two DbBouncer* alerts with stage's numbers.
Two things to confirm on stage, from backend/ with the stage DB_URL:
- pool 70:
uv run python -m scripts.probe_pgbouncer_pool --wait 2 -
query_wait_timeout10 s:uv run python -m scripts.probe_pgbouncer_wait_timeout(gate met at 15 s; if it is still 120 s, ask DBaaS for 10 s as on dev)
Quota trap. #60 let the two HPAs reach 6 × 512Mi + 4 × 768Mi = 6144Mi, the whole 6Gi quota, with frontend, docs and the otel collector (256Mi) on top. On 2026-09-24 the workers were at 4 and the backend was Forbidden past 3 pods. A ResourceQuota is checked at admission, so nothing prioritises one deployment over another; the sum of both maxima has to fit. Worker max is 3 on dev and stage since #67: 5839Mi + the 128Mi migration Job of 6144Mi. Same day, the otel collector sat at 99 % of its 256Mi limit; that is the platform dashboard's "memory from limits" panel, and a separate follow-up.
Floor of 3 for the opening week. The school-wide opening is Monday
2026-09-28, and prod has never seen users (peak 1.46 rps over 30 days,
probes included). Backend minReplicas is 3 on dev, stage and prod
(openshift-app-config #68, #69) so the 9:00 login burst lands on three
pods before the HPA has reacted; back to 2 the week after. Capacity
math from the dev ladder: ~33 ms CPU per request, one locust user ≈
0.3 rps, six pods burst to ~180 rps on paper, and the bouncer's server
pool is the wall first, ~60 rps on 70 slots. The lever that moves
Monday's ceiling is default_pool_size (100 asked for prod), not more
pods. Prod's version of #60 is openshift-app-config #68, gated on the
1.4.17 release to main (chart 1.0.1781 has no worker HPA template).
Update 2026-10-09 — a dead server is not a full pool¶
On 2026-10-05 a DBaaS update (CHG0048468) restarted prod's Postgres twice,
17:22 and 18:06 UTC, under a minute each. The bouncer stayed up and
answered FATAL: server login has been failing, cached error: connect
failed (server_login_retry), sometimes after a query_wait_timeout.
explain_db_wait matched query_wait_timeout first and logged "Its pool
... is full", ticked db.pgbouncer.queue_timeouts (the critical
DbBouncerQueueTimeout), and the 103 connect failures were labelled
unknown. server_login_retry is now checked first: its own
explanation, no queue-timeout tick, and the connect-failure label
server_login_retry. Runbook row in
the connection budget.