Intermittent 'too many clients' errors on Scalingo managed PgBouncer

Answer to the Core Questions

1. Independent adjustment of max_client_conn or pool_size
Scalingo ties both parameters to the underlying database plan. The add‑on does not expose a UI or API to change them independently; you would need to request a plan upgrade or contact support to alter the limits.

2. Distinguishing PgBouncer queue waits from backend saturation
Dashboard metrics show aggregated client_conn counts and PostgreSQL max_connections usage, but they do not break down per‑application queue depth or show how many client sessions are waiting for a server slot. Application logs likewise report the “sorry, too many clients already” error but not the duration of the wait. The only way to see queue depth is via PgBouncer admin commands (e.g., SHOW POOLS).

3. Configurability of server_reset_query and DISCARD ALL latency
On the managed add‑on server_reset_query is fixed to DISCARD ALL in transaction mode. This is not user‑configurable. The overhead is small—only the cost of clearing session state and re‑planning statements—but it can become noticeable under very high transaction rates or when temporary objects are heavy.

Likely Explanation of the Intermittent Errors

The error originates from PgBouncer’s client‑slot exhaustion. In transaction pooling a slot is held from the moment an application checks out a connection until the transaction commits or rolls back. Frameworks such as HikariCP or node‑pool often keep connections checked out across multiple statements, especially when long‑running or idle‑in‑transaction transactions are present. Under variable load the total number of checked‑out connections can exceed the plan’s max_client_conn even though PostgreSQL still has free server slots.

Practical Steps for Your Current Situation

1. Audit application pool sizes
   Inspect each service’s connection pool configuration. Ensure the maxPoolSize (or equivalent) is set below the plan’s max_client_conn with a safety margin (e.g., 80 % of the limit).
2. Enable bounded acquire timeouts
   Configure the pool to fail fast when a connection cannot be obtained within a short timeout (e.g., 200 ms). This prevents long waits that can trigger the error during spikes.
3. Monitor PgBouncer stats during a spike
   When the error occurs, run:

   SHOW STATS;
   SHOW POOLS;
   SHOW CLIENTS;
   SHOW SERVERS;
   

   Compare cl_active and sv_active against the plan’s limits. A high cl_wait count indicates queue saturation.
4. Check PostgreSQL for idle‑in‑transaction sessions
   Run:

   SELECT pid, state, query_start, query
   FROM pg_stat_activity
   WHERE state = 'idle in transaction';
   

   Long‑running idle sessions suggest that application code is keeping transactions open unnecessarily.
5. Consider scaling the plan if limits are consistently hit
   After applying the above mitigations, if the error persists, contact Scalingo support to request a higher max_client_conn / pool_size by upgrading the database plan.

Missing Diagnostic Detail

To refine the recommendation, could you provide the current SHOW STATS output (or the cl_active and cl_wait values) at the moment the error is logged? That will confirm whether the issue is truly a client‑slot exhaustion or a backend saturation problem.