Skip to content

Count DB pool timeouts and connection failures instead of inferring them

Follow-up to #2566 and EPFL-ENAC/openshift-app-config#30.

Problem

The two DB failure modes are both alerted on, but only through gauge thresholds standing in for events that are actually countable:

failure mode signal before this change gap
QueuePool timeout (pod-local, recoverable) DbPoolSaturationHigh > 80% for 10m a pod can hit 100%, time out real requests and recover inside the window
FATAL: remaining connection slots (global) DbServerConnectionsHigh > 80 for 2m dev went 60 → 100 in minutes on 2026-08-31; the burst outran the window

HighErrorRate is the backstop — both modes end in 500s — but it cannot say which, and on 2026-08-31 that distinction was the whole diagnosis: pod-local saturation sat at ~20% while the server itself was full.

Design

db.pool.timeouts — a subclassed pool, not handle_error

handle_error is the obvious single choke point and it does not work. A QueuePool checkout timeout never reaches the DBAPI, so no listener on that event ever sees one. Verified against a live engine (pool_size=1, max_overflow=0, pool_timeout=1, two concurrent checkouts): sqlalchemy.exc.TimeoutError propagated and the handle_error listener recorded nothing.

InstrumentedQueuePool subclasses AsyncAdaptedQueuePool and counts in connect(). That is the single call every checkout makes, so it is exactly one increment per timed-out request — unlike _do_get, which re-enters itself on the overflow path and would double-count.

It reaches the engine through _pool_kwargs, which already returns {} for sqlite. That is what makes the counter a no-op under NullPool/StaticPool, for free and in the same place as the existing guard.

db.connect.failures — handle_error, narrowed twice

handle_error does fire for connect failures, and its ExceptionContext discriminates. Verified on a live engine:

  • connect failure (closed port) → context.connection is None
  • query error (select 1/0) → context.connection is the Connection

connection is None alone is not enough, and this is the trap in this change. The real engine sets pool_pre_ping=True, and since #2566 the DB reaps idle backends after 30 min — so a stale pooled socket is routine. Verified with pool_pre_ping=True and the pooled backend terminated server-side:

handle_error: connection=None is_pre_ping=True orig=AdminShutdown sqlstate='57P01'
second checkout succeeded, select 1 -> 1

That is the pool healing itself, and the request that follows succeeds. Counting it would tick the counter all day on a healthy pod and make increase(...) > 0 useless — the exact alert this change exists to enable.

Skipping is_pre_ping costs nothing during a real outage: when the reconnect also fails, that attempt fires its own event with is_pre_ping false. Verified by killing the pooled backend and setting connection limit 0 on the role in the same breath — the #2566 shape:

connection=None is_pre_ping=True  orig=AdminShutdown     sqlstate='57P01'   <- skipped
connection=None is_pre_ping=False orig=OperationalError  ...too many connections for role...

Driven through the shipped listener, that scenario counts [(1, {'sqlstate': '53300'})], and the healthy reconnect counts nothing.

The listener is registered only when the engine is not sqlite, mirroring _connect_args / _pool_kwargs.

The SQLSTATE label is derived from message text, and that is a ceiling

psycopg exposes no SQLSTATE on connection-establishment errors. Verified against a real 53300, forced locally with create role ... connection limit 1 and a second login: err.sqlstate and err.diag.sqlstate are both None, and the pgconn handed to the exception is a FinishedPGconn carrying only error_message text. libpq has no result object at that point, so there is nothing else to read. (Query-time errors are unaffected — those carry a real sqlstate, e.g. 22012.)

So connect_failure_sqlstate() prefers a real sqlstate when one exists, and otherwise recognises 53300 from the four messages Postgres uses for it:

  • too many clients already
  • remaining connection slots are reserved
  • too many connections for role
  • too many connections for database

Anything unrecognised is still counted, labelled unknown — the total is what says "connections are failing", the label only says which kind.

The ceiling: this is message matching, so a server running with a non-English lc_messages would label a real 53300 as unknown. All three of our instances are English. If that ever changes, the alert should move to the unlabelled total.

DB_POOL_TIMEOUT 30 → 5

A pool timeout today means a user watches a spinner for 30 seconds and then gets an error. Five carries the same information at a sixth of the harm, and surfaces the counter sooner. Under normal load it changes nothing: measured peak is ~4 checked_out per pod against a ceiling of 13 (#2566), so nothing ever waits.

Steps

  • InstrumentedQueuePool + db.pool.timeouts counter (backend/app/db.py), wired through _pool_kwargs so sqlite skips it.
  • count_connect_failure + db.connect.failures counter, registered as a handle_error listener on engine.sync_engine for non-sqlite only, and narrowed on both context.connection and context.is_pre_ping.
  • connect_failure_sqlstate with the four 53300 messages and the unknown bucket.
  • DB_POOL_TIMEOUT default 30 → 5 (backend/app/core/config.py), with backend/.env.example and the pinned assertion in backend/tests/unit/test_db_pool_settings.py updated to match.
  • Tests in test_db_pool_settings.py: a real exhausted-pool checkout timeout driven through greenlet_spawn (the async engine's own wrapper around every pool call), a successful checkout that must not count, the four 53300 messages, the unknown fallback, a real sqlstate winning over the message, and handle_error neither on an open connection nor on a failed pre-ping counting. All three verified failing with the code they cover removed.
  • Prometheus rules. Deployed as DbPoolCheckoutTimeout and DbServerConnectionSlotsExhausted (names below are the plan's drafts): DbPoolTimeouts and DbConnectionsRefused on increase(...) > 0 live in EPFL-ENAC/openshift-app-config (overlays/*/monitoring/specific-namespace-alerts.yaml) and are a separate PR now that the metrics exist.

Open

  • No auth/network split. With psycopg dropping SQLSTATE at connect time, every non-53300 failure lands in unknown — an unreachable host and a bad password are indistinguishable on the label. Add markers for 28P01 / 3D000 if that distinction is ever wanted; the total already counts them.
  • Not exercised against a real 53300 in a deployed environment. The label logic is pinned to the verbatim psycopg message text captured from a forced local 53300, but the end-to-end path (server full → counter → Prometheus) will only be confirmed by the next incident, or by deliberately squeezing dev's max_connections.
  • Neither counter counts a failure that is retried and succeeds. For connections that is deliberate — pool_pre_ping recovery is excluded by design. For timeouts there is no retry today, but if one is ever added the counter stops measuring failed requests.
  • DB_POOL_TIMEOUT may be pinned in the deploy repo. #2566 sets DB_POOL_SIZE explicitly in dev's backend.env and worker.env; if DB_POOL_TIMEOUT is set in either, this default change is a no-op there. Check both maps when the alert-rules PR lands.
  • #2566 — engine disposal, the checked_in/max_overflow gauge series, and the incident that produced both failure modes on the same day.
  • #1723 — the original pool sizing, and where DB_POOL_TIMEOUT came from.