DB connection budget¶
Since 2026-09-24 every pod on dev, stage and prod reaches Postgres through the DBaaS PgBouncer in transaction pooling (dev since 2026-09-23). Up to three pools sit between a query and a backend, each with its own limit, timeout and error text. This page is the map: where the wall is per environment, who opens connections, and which knob to turn when an alert fires. History and the DBaaS checklist live in the 2689 plan; sizing decisions are made here, not during an incident.
Where the wall is¶
| Env | Path | Wall | Reserve | Budget | What sizes a pod |
|---|---|---|---|---|---|
| dev | DBaaS PgBouncer 1.25.1, transaction |
60 in-flight transactions fleet-wide | 5 | 55 | CPU and memory; the HPA adds DB concurrency |
| stage | DBaaS PgBouncer 1.25.1, transaction |
70 in-flight transactions fleet-wide | 5 | 65 | same as dev (openshift-app-config #67) |
| prod | DBaaS PgBouncer 1.25.1, transaction |
70 in-flight transactions fleet-wide | 5 | 65 | same as stage (openshift-app-config #68) |
The reserve is the migration Job, the db-dump CronJob and one psql.
Stage and prod differ from dev only in the pool (70) and
max_prepared_statements (500). Prod's 70 is what the overlay assumes,
to be confirmed with DBaaS; the open ask is default_pool_size 100 for
the 2026-09-28 opening (openshift-app-config #68). The
fleet-ceiling rule below no
longer binds any environment; it stays for direct-Postgres paths.
Transaction pooling¶
Bouncer config per environment, agreed with DBaaS (dev 2026-09-23, stage and prod 2026-09-24):
| Setting | dev | stage, prod | Before (session mode) |
|---|---|---|---|
pool_mode |
transaction |
transaction |
session |
default_pool_size |
60 | 70 | 25 |
min_pool_size |
5 | 5 | 5 |
max_client_conn |
1000 | 1000 | 1000 |
max_prepared_statements |
200 | 500 | 0 |
query_wait_timeout |
10 s | 10 s | 120 s |
A server slot is held only while a transaction is in flight, not for the
life of the client connection. That retires the old rule: client
connections to the bouncer are cheap (1000 allowed, an idle one costs
Postgres nothing), so pods × (DB_POOL_SIZE + DB_MAX_OVERFLOW) no longer
has to fit under the wall. What is bounded is concurrency: at most
60 (dev) or 70 (stage, prod) transactions at once fleet-wide, the next
one queues for
query_wait_timeout and then fails with
psycopg.errors.ProtocolViolation: query_wait_timeout.
Pods are therefore sized by CPU and memory, and scale-out is what adds DB concurrency (openshift-app-config #59, #60, #67, #68):
| Role | DB_POOL_SIZE + DB_MAX_OVERFLOW |
DB_POOL_TIMEOUT |
Memory (request = limit) | CPU request / limit | Replicas | MAX_CONCURRENT_JOBS |
|---|---|---|---|---|---|---|
| backend | 5 + 45 | 5 s | 512 Mi | 100m / 500m | HPA 3..6, 70% CPU target | – |
| worker | 5 + 15 | 5 s | 768 Mi | 150m / 1000m | HPA 1..3, 60% CPU target | 4 |
Memory request must equal limit on this cluster; the HPA's memory target is off.
Why query_wait_timeout is 10 s. A request queued at the bouncer
already holds its pod pool slot, so DB_POOL_TIMEOUT (5 s) cannot cut the
wait. At 120 s the pod's pool fills with waiting requests. Until
2026-09-24 its /ready check then failed too, the pod went NotReady,
traffic shifted to the other pods and they saturated in turn: the
2026-09-08 and 2026-09-17 incidents. /ready now stays 200 through a DB
wait once the pod has reached the DB (plan 2049), but a full pool still
fails every request on that pod. At 10 s the request is cut, the slot is
handed back and the error stays on that one request, answered as a 503.
10 s is also the app's request-latency alert threshold.
Prepared statements. psycopg 3.3.6 with libpq 18 replays named
prepares through the bouncer natively, so the app keeps psycopg's default
prepare_threshold (#2921 briefly set it to None as a stopgap, #2922
removed it the same day).
Code invariants under transaction pooling, all already true (audit in the plan):
- advisory locks are
pg_advisory_xact_lockonly (app/tasks/_locks.py); - temp staging tables are
ON COMMIT DROP; - no
LISTEN/NOTIFY, no session-levelSET, noWITH HOLDcursors; - the app sends
application_name=co2-<pod>, which the bouncer forwards, sopg_stat_activitycan still tell pods apart.
Probes (backend/scripts/, run through the bouncer with that
environment's DB_URL, off-hours, when nobody is testing):
| Script | Proves |
|---|---|
probe_pgbouncer_wait_timeout.py |
the effective query_wait_timeout: fills the pool with open transactions until one client queues, then times the wait (#2923) |
probe_pgbouncer_prepared.py |
prepared-statement replay across rotating server connections (#2922) |
probe_pgbouncer_pool.py |
the session-mode pool only; its autocommit SELECT 1 hands the slot back instantly, so it cannot fill a transaction-mode pool |
Who opens connections¶
Per pod, in the order they start (app/main.py lifespan):
| Loop | Runs on | Cadence | Connections at once |
|---|---|---|---|
DB health poller (_db_health.py) |
all pods | 30 s (5 s until first success) | 1, never held past 1 s; /ready reads its verdict |
Pod heartbeat (_pod_heartbeat.py) |
all pods | 30 s | 1, also refreshes the server-side gauge |
| Pipeline reconciler | worker only (API pods set RUN_PIPELINE_RECONCILER=false in every overlay, #2854) |
60 s | 1 |
Safety poller (_poller.py) |
worker only (RUN_BACKGROUND_POLLER) |
2 s | 1, plus what it dispatches |
Job runner (runner.py) |
worker | per job | up to 3 per running job (job session, data session, chain helper); measured 1 most of the time, see below |
| Request handlers | backend | per request | 1 from the route's first query to the end of the response; auth releases its own before the route body runs |
SSE streams (data_sync.py) |
backend | per 2 s poll | 1 for a few ms per poll, none between polls |
Measured (2026-09-18, local Postgres, pg_stat_activity every 50 ms via
backend/tests/performance/pipeline_connections.py; one 500-row upload
per module type, then three units at once for six modules):
| Situation | held connections, peak | median |
|---|---|---|
| pod at rest (loops only) | 1 | 0 |
| one pipeline, ingest phase | 2 | 1 |
| one pipeline, recalc phase | 2 | 1 |
| one pipeline, aggregation phase | 3 | 1 |
| three pipelines at once, whole pod | 5 | 3 |
"Held" is any state but idle; an idle row is a pooled connection nobody
is using, which is what pool_size keeps open on purpose. Behind the bouncer an idle row costs no slot either; only the held ones
count against the 60 or 70. Rule of thumb: a pod at rest holds 1 briefly, a worker peaks at
MAX_CONCURRENT_JOBS + 2, and 3 × MAX_CONCURRENT_JOBS + 3 is the
worker's direct-Postgres sizing rule, a 2× margin over that peak.
The three layers¶
| Layer | Waits for | Timeout | What you see |
|---|---|---|---|
SQLAlchemy QueuePool |
a slot in this pod's pool (DB_POOL_SIZE + DB_MAX_OVERFLOW) |
DB_POOL_TIMEOUT, 5 s |
sqlalchemy.exc.TimeoutError: QueuePool limit of size … reached; counter db.pool.timeouts |
| PgBouncer | a free server slot for its transaction (default_pool_size 60 dev, 70 stage and prod) |
query_wait_timeout, 10 s |
NOTICE client being queued at once, counter db.pgbouncer.queued; then psycopg.errors.ProtocolViolation: query_wait_timeout, counter db.pgbouncer.queue_timeouts |
| Postgres login | a backend under max_connections (100) |
none, immediate | FATAL: remaining connection slots are reserved / too many clients already; counter db.connect.failures{sqlstate="53300"} |
| Postgres execution | locks, I/O | statement_timeout, lock_timeout |
canceling statement due to … |
The app logs which layer said no, in plain words, next to the raw error
(app/db.py, explain_db_wait). pool_size connections stay open for
the pod's life with TCP keepalives every 30 s (_PG_KEEPALIVES, #2566),
so a pod notices a dead peer in about a minute; overflow connections are
opened on demand and closed on check-in, so each one is a fresh login
(about 15 ms through the bouncer).
Direct Postgres: the fleet-ceiling rule¶
No environment runs on direct Postgres since 2026-09-24. The rule stays for any direct path: a laptop on the shared DB, local Docker Postgres, or DBaaS taking the bouncer away.
Fleet ceiling = pods × (DB_POOL_SIZE + DB_MAX_OVERFLOW) must fit
under max_connections, minus a rollout surge and the human clients.
Overflow past that is not capacity: it is a 53300 refusal that hits every
client, the DBA included.
- Rollout surge: a role with one replica rolls with
maxSurge: 1,maxUnavailable: 0, so one extra pod is alive at base size while the old one drains; a role with two or more rolls withmaxSurge: 0,maxUnavailable: 1(helm/templates/backend-deployment.yaml,backend-worker-deployment.yaml). Nothing surges on stage or prod (three backends, two workers). Budgetpool_sizefor each surging pod. - Human clients: the migration Job (runs during the rollout), the db-dump
CronJob, one psql, pgAdmin, and Postgres's 3 superuser slots. A laptop
running the app with the default
DB_POOL_SIZE=20from.env.exampletakes 20 slots on its own; point local runs at Docker Postgres. DB_POOL_SIZEis what stays open forever. On direct Postgres an idle connection is a cheap backend process, so set it at the measured steadychecked_out(5 on prod) and avoid churning a login per request.- Worker ceiling:
MAX_CONCURRENT_JOBS+ 3 at the floor, 3 ×MAX_CONCURRENT_JOBS+ 3 when the budget allows. When the budget is tight, spend the margin on the backend, not on more worker overflow. DB_POOL_TIMEOUTstays 5 s: failing in-process beats waiting on the wall. Never-1forDB_MAX_OVERFLOW.
Sizing formula¶
Size the ceiling, never the steady state: the ceiling is what a burst reaches, and reaching the wall is the incident (dev, 2026-09-17, while the bouncer was still in session mode).
W = Postgres max_connections − superuser_reserved (100 − 3 = 97)
R = other clients: migration Job, dump CronJob, one psql = 3,
plus what pg_stat_activity shows outside the fleet (pgAdmin: 2)
B = W − R fleet ceiling budget
J = MAX_CONCURRENT_JOBS per worker pod
P = pool_size, measured steady (5)
nb, nw = backend and worker replicas
S = surge: P for each role that runs one replica, else 0
Cw = worker ceiling per pod = 3J + 3, floor J + 3 (measured peak J + 2)
Cb = backend ceiling per pod = ⌊(B − nw·Cw − S) / nb⌋
DB_MAX_OVERFLOW = C − P for each role
check: nb·Cb + nw·Cw ≤ B, Cb ≥ 3 (a request holds 1 for its duration;
95 rps × 80 ms ≈ 8 in flight fleet-wide at the dev load-test peak)
| Environment | W | B | nb / nw | J | Cw | Cb | backend / worker | ceiling / surge |
|---|---|---|---|---|---|---|---|---|
| dev, bouncer 60, transaction mode | – | 55 | 3..6 / 1..3 | 4 | – | – | 5+45 / 5+15 | formula does not bind; 55 concurrent transactions |
| stage, bouncer 70, transaction mode | – | 65 | 3..6 / 1..3 | 4 | – | – | 5+45 / 5+15 | formula does not bind; 65 concurrent transactions |
| prod, bouncer 70, transaction mode | – | 65 | 3..6 / 1..3 | 4 | – | – | 5+45 / 5+15 | formula does not bind; 65 concurrent transactions |
| prod until 2026-09-24, direct | 97 | 92 | 3 / 2 | 4 | 15 full | 20 | 5+15 / 5+10 | 90 / 90 |
| same, six jobs, same pools | 97 | 92 | 3 / 2 | 6 | 15 (2J+3) | 20 | 5+15 / 5+10 | 90 / 90 |
The two direct-Postgres rows are prod before openshift-app-config #68 and stay as the worked example: a worker's 15 is 2J + 3 at J = 6, a 1.9× margin over the measured peak of 8.
Budgets per environment¶
| Env | Wall | Budget | backend | worker | MAX_CONCURRENT_JOBS |
Fleet client connections at ceiling |
|---|---|---|---|---|---|---|
| dev | 60 in-flight transactions (bouncer, transaction mode) | 55 | 5+45 × 3..6 | 5+15 × 1..3 | 4 | 120 to 360 of 1000 max_client_conn |
| stage | 70 in-flight transactions (bouncer, transaction mode) | 65 | 5+45 × 3..6 | 5+15 × 1..3 | 4 | 120 to 360 of 1000 max_client_conn |
| prod | 70 in-flight transactions (bouncer, transaction mode) | 65 | 5+45 × 3..6 | 5+15 × 1..3 | 4 | 120 to 360 of 1000 max_client_conn |
Prod moved behind the bouncer with openshift-app-config #68 (2026-09-24),
same shape as stage. Before that (#47) it ran 5+13 × 3 and 5+10 × 2
against a budget of 90 on direct Postgres; stage had peaked at 14
checked_out fleet-wide on 2026-09-15 on the same path.
Behind the bouncer the pools stop being the wall, so the two HPAs are bounded by the namespace quota instead, and both maxima must fit it at once: on dev at 4 workers the backend was Forbidden past 3 pods (quota 6Gi used 6.03Gi, 2026-09-24). Worker max is 3 on all three for that reason; the arithmetic is in the worker block of each overlay.
Values live in openshift-app-config, overlays/<env>/kustomization.yaml,
backend and worker blocks. The VPN path goes through the bouncer on all
three: a VPN client's idle connection is free, its open transactions
count against the 60 or 70.
Dashboard¶
The Grafana "Specific graphs" dashboard (openshift-app-config #61, #62)
has rows Traffic / Database / Worker / Scaling. On dev the wall panel
reads "wall (60) and budget (55)", on stage and prod "wall (70) and budget (65)",
and the layer-2 counters are
db.pgbouncer.queued and db.pgbouncer.queue_timeouts. A DbBouncerQueued
bar means "more than 60 in-flight transactions at once", which is allowed
by design; DbBouncerQueueTimeout is the one that failed requests.
Which knob, when an alert fires¶
| Signal | Meaning | Knob | Owner |
|---|---|---|---|
db.pool.timeouts > 0 |
a pod ran more than DB_POOL_SIZE + DB_MAX_OVERFLOW concurrent requests (50 everywhere, so rare) |
look at the HPA, the pod should have scaled out; on the worker, MAX_CONCURRENT_JOBS against its 5+15. On a direct-Postgres path raise DB_MAX_OVERFLOW only if the fleet ceiling still fits |
us |
db.pgbouncer.queued > 0 |
the fleet exceeded 60 (dev) or 70 (stage, prod) concurrent transactions; 10 s until errors | a stray client (application_name in pg_stat_activity), a long transaction holding slots (COPY-based factor upsert, long routes), or ask DBaaS for a bigger default_pool_size |
us + DBaaS |
db.pgbouncer.queue_timeouts > 0 |
same, and the requests behind them failed after 10 s | same | us + DBaaS |
db.connect.failures{sqlstate="53300"} > 0 |
Postgres itself is full: the bouncer passed logins through, or direct clients and orphans filled the rest | orphans and stray clients first; then cap the bouncer (max_db_connections) below max_connections, or raise max_connections |
DBaaS |
db.connect.failures{sqlstate="server_login_retry"} > 0 |
the bouncer cannot reach Postgres (restart, failover, network) and refuses every login until it gets back in; 2026-10-05 was a DBaaS update (CHG0048468), under a minute each time | none on our side, no pool is full: check the SI_POSTGRESQL changes, and ask DBaaS (SVC1757) if it lasts more than a minute | DBaaS |
The bouncer multiplexes on all three environments, so the old seesaw between pod ceilings and the bouncer pool is over: a pod that needs more concurrency scales out, and the only shared limit is 60 or 70 transactions at once.
The DB alerts carry a runbook_url pointing at this section
(openshift-app-config, overlays/<env>/monitoring). Keep this heading
as it is, or update the three overlays in the same change.
Open follow-ups¶
- A request-deadline middleware in the API would bound a request's total
wait on our side and let
query_wait_timeoutrise to about 30 s for the worker's benefit. - A
jobs.runninggauge on the worker'sMAX_CONCURRENT_JOBSsemaphore. - Prod
default_pool_size100 for the 2026-09-28 opening, asked in openshift-app-config #68.