Skip to content

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_once wrapped the probe in asyncio.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 /ready reported "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

  1. get_current_user releases its connection (app/core/security.py): after the lookup it expunges the User and rolls the session back, so the connection is in the pool before the route body runs. The route's own get_db session is untouched: its first query autobegins again and takes a connection only then. User has no relationships, so a detached instance is complete. get_current_user_detached is 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).
  2. /ready survives 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).
  3. Poller log noise (app/tasks/_poller.py): "dispatching job N" is logged inside schedule_job after the in-flight guard, once per real dispatch instead of every 2 s sweep.
  4. 5+50 on backend and worker, dev, stage, prod (openshift-app-config, both configMapGenerator blocks 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_TIMEOUT stays 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.
  5. application_name=co2-<pod> on every Postgres connection (app/db.py): behind the bouncer every backend shares one client_addr, so this is what tells pods, and laptops running the app against dev, apart in pg_stat_activity.
  6. Docs: database/01-overview.md is 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 in pg_stat_activity with the laptop's own IP, so the VPN path is probably not bounced. Check with SELECT inet_client_addr() from psql on dev: the bouncer's address means the psql path is bounced and the SET + 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=100 when 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}/test reads 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=100 reference line, plot the ~40 plateau, add a Loki alert on query_wait_timeout per 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_config is not a parallel fan-out: _gather runs once on one helper session. A running job holds three connections at most (job_session, data_session, the helper), so the worker's demand is 3 × MAX_CONCURRENT_JOBS plus 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 a query_wait_timeout wave does not retry itself into a storm.
  • SIGTERM reaches uvicorn: the Dockerfile CMD is sh -c "exec …", so the lifespan teardown and engine.dispose() (#2566) run. The backend Deployment has a 5 s preStop and 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.75 like the pods. A pgAdmin tab or a laptop running the app against dev holds server slots exactly like a pod.

Rejected

  • max_overflow 200 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, backend make type-check green.
  • 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_timeout count stays at 0, and /ready never 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 — read cl_waiting, sv_active, sv_idle, maxwait

cl_waiting above 0 at rest means a stray client is holding slots.

  • pool_mode says transaction — 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.py through 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_statements above 0 → #2922 goes ahead

  • max_prepared_statements is 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.py through 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_timeout shorter than the role's idle_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_timeout 10 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.