Connections & poolingadvancedFree in full

Diagnose PgBouncer queueing and session-state leaks

A one-backend transaction pool queued three clients while keeping PostgreSQL at one server connection. The same lab proved plain SET work_mem leaked to another client, while SET LOCAL reverted safely at COMMIT.

Problem

What you're actually looking at

The symptom as it shows up on a real server.

PgBouncer has two separate budgets: client connections and PostgreSQL server connections. In transaction pooling, clients can queue behind a small server pool, and connection-level SET state can be seen by whichever client receives that server connection next.

Meridian accepts four application clients through a transaction pool backed by one PostgreSQL connection. Three queue while one sleeps; a plain SET work_mem = '64MB' then appears in a different client, proving session state is unsafe across transaction-pool clients.

In plain English

PgBouncer can accept many client connections while opening only a few PostgreSQL backends. That is the point of pooling — but it also means clients can wait inside PgBouncer, and a server connection can carry session settings from one client to another. SHOW POOLS tells you whether clients are queued; SET LOCAL keeps transaction-specific settings from leaking.

Before you start

  • • PgBouncer admin access for SHOW POOLS / SHOW SERVERS.
  • • Knowledge of pool_mode and max_prepared_statements for the affected pool.
  • • Docker Compose for the repository harness at docker/labs/ha-perf/perf/run-pgbouncer.sh.
  • • All credentials in the compose file are lab-only and must not be reused.

How to identify it

  • ›SHOW POOLS has cl_waiting > 0 and maxwait is growing.
  • ›sv_active + sv_idle is at the configured server-pool ceiling.
  • ›PostgreSQL backend count stays low while application requests wait.
  • ›A plain SET value appears in a different transaction-pooled client.

Pitfalls to avoid

  • ✕Do not read max_client_conn as the PostgreSQL backend ceiling; default_pool_size, max_db_connections, users, and databases determine server connections.
  • ✕Do not use plain SET for request-scoped state in transaction pooling. Use SET LOCAL inside an explicit transaction, or configure the setting at role/database level.
  • ✕Do not claim all prepared statements work: max_prepared_statements tracks protocol-level named statements, not SQL PREPARE/EXECUTE commands.
  • ✕Do not call sv_idle = 0 overload by itself. It is pressure only when clients are also waiting.

Trace it

Run these against the affected server. To build a copy of this scenario instead, see “Reproduce it in a lab” below.

  1. 01

    Diagnose: read the queue, not just PostgreSQL activity

    Run these on the PgBouncer admin database. cl_waiting and maxwait expose pool pressure that pg_stat_activity cannot show.

    Shell
    PGPASSWORD=labpass psql   -h localhost -p 56432 -U labuser -d pgbouncer   -c "SHOW POOLS;"   -c "SHOW SERVERS;"
    Captured during the one-backend queue
    database | user    | cl_active | cl_waiting | sv_active | sv_idle | maxwait | pool_mode
labdb   | labuser |         1 |          3 |         1 |       0 |       1 | transaction
  2. 02

    Diagnose: compare client admission with server capacity

    SHOW CONFIG separates max_client_conn from the one-backend database pool. The application can connect successfully and still queue because these are different budgets.

    Shell
    PGPASSWORD=labpass psql           -h localhost -p 56432 -U labuser -d pgbouncer           -c "SHOW CONFIG;"
    Captured in Docker · PgBouncer 1.25.2
    max_client_conn      | 100
        default_pool_size    | 1
        max_db_connections   | 1
        max_prepared_statements | 100
        pool_mode            | transaction

Resolution approach

  1. 1.Run SHOW POOLS and inspect cl_waiting, sv_active, sv_idle, and maxwait.
  2. 2.Right-size the server pool against PostgreSQL capacity; keep max_client_conn as the client-side admission budget.
  3. 3.Replace request-scoped SET with SET LOCAL inside the transaction.
  4. 4.Enable max_prepared_statements only when the driver uses the extended protocol and you have measured its memory overhead.

Stop it recurring

  1. 01

    Keep request state transaction-local

    SET LOCAL automatically reverts at COMMIT, so the next client receives the server default rather than another request's state.

    SQL
    BEGIN;
SET LOCAL work_mem = '32MB';
-- run this request's query
COMMIT;
SHOW work_mem;  -- back to the server default

Reproduce it in a lab

Builds the scenario above on a throwaway database so you can practise the fix. Skip this if you are working a live incident.

  1. 01

    Lab setup (run this first)

    Starts PG16.14 plus session, transaction, and prepared-statement pools; then proves queueing, state leakage, SET LOCAL safety, and protocol-level prepared statements.

    Shell
    cd docker/labs/ha-perf/perf
bash run-pgbouncer.sh
    Measured harness result · PostgreSQL 16.14 · PgBouncer 1.25.2
    queued_clients=3 server_connections=1 maxwait=1
session_same_client_work_mem=64MB
transaction_cross_client_leaked_work_mem=64MB
transaction_after_set_local=4MB
status=PASS

Verify you're done

SELECT 'Run SHOW POOLS on PgBouncer and confirm cl_waiting returns to 0' AS verification;

Last verified 2026-08-16 · PostgreSQL 16.14 + PgBouncer 1.25.2 (docker/labs/ha-perf/perf)

Related errors533000800626000

Related errors

SQLSTATEs this runbook resolves

The error pages that send an on-call engineer here.

Connected

How this connects to the rest of the library

A live view of this page's real cross-references — what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.

Open in the interactive map →