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. It also proved work_mem and search_path leaked cross-client, and a second client inherited and unlocked the first client's session advisory lock.

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 while PostgreSQL looks idle, and connection-level state can be seen by whichever client receives that server connection next. That includes plain SET values and session advisory locks. The outage symptom is application latency with a quiet database.

Meridian accepts four application clients through a transaction pool backed by one PostgreSQL connection. Three queue while one sleeps; work_mem and search_path set by one client then appear in another. A session advisory lock survives the first client's disconnect and the next client can unlock it, proving that server-session ownership is not application-session ownership.

Simple terms

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 settings or locks from one client to another. SHOW POOLS exposes the hidden queue. SET LOCAL and transaction advisory locks keep request state inside the transaction PgBouncer actually promises to preserve. Lab-verified on PostgreSQL 16.14 + PgBouncer 1.25.2.

Before you start

• PgBouncer admin access for SHOW POOLS / SHOW SERVERS / SHOW CONFIG.
• 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. cl_waiting is clients waiting for a server connection; maxwait is the age in seconds of the oldest waiting client.
›sv_active + sv_idle is at the configured server-pool ceiling (default_pool_size / max_db_connections). PostgreSQL backend count stays low while application requests wait.
›A plain SET value (work_mem, search_path, TimeZone, etc.) appears in a different transaction-pooled client after the first client disconnects or returns the connection.
›A session advisory lock remains in pg_locks after its client disconnects, or another client unexpectedly unlocks it with pg_advisory_unlock.
›pool_mode is transaction (or statement) for the affected database, so session semantics were never promised.

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 use session advisory locks through transaction pooling. They stay with a server connection that may serve another client; use pg_advisory_xact_lock so COMMIT releases ownership.
✕Do not assume server_reset_query = DISCARD ALL protects transaction pooling. PgBouncer does not run it after every transaction unless server_reset_query_always is enabled, which adds overhead and still does not make session semantics portable.
✕Do not assume track_extra_parameters = search_path fixes stock PostgreSQL. PgBouncer can track only parameters the server reports through ParameterStatus; its documentation names Citus 12+ as an extension that reports search_path, while this plain PostgreSQL 16.14 lab did not.
✕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 (cl_waiting > 0).

Trace it

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

01
Diagnose: read the queue, not just PostgreSQL activity
Run on the PgBouncer admin database (db pgbouncer). Column meanings: cl_active = clients currently linked to a server; cl_waiting = clients blocked for a server slot; sv_active = server conns in a transaction/query; sv_idle = server conns checked in; maxwait = seconds the oldest waiter has waited; pool_mode = session|transaction|statement. cl_waiting>0 with low PostgreSQL CPU is classic pool saturation.
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
```

Diagnose: prove whether server-session state crosses clients

Run these as two separate clients through a one-backend transaction pool. Client B seeing pg_catalog and successfully unlocking key 424242 proves both values belong to the reused server connection, not to client A. pg_advisory_xact_lock is the safe contrast because COMMIT releases it before PgBouncer reassigns the backend.

Shell

# Client A
PGPASSWORD=labpass psql -h localhost -p 56432 -U labuser -d labdb   -c "SET search_path = pg_catalog;"   -c "SELECT pg_advisory_lock(424242);"

# Client B, after client A disconnects
PGPASSWORD=labpass psql -h localhost -p 56432 -U labuser -d labdb   -c "SHOW search_path;"   -c "SELECT pg_advisory_unlock(424242);"

# Safe form: the lock ends at COMMIT
PGPASSWORD=labpass psql -h localhost -p 56432 -U labuser -d labdb   -c "BEGIN; SELECT pg_advisory_xact_lock(424243); COMMIT;"

Captured with one transaction-pool backend

search_path
-------------
 pg_catalog

 pg_advisory_unlock
------------------
 t

 advisory locks after pg_advisory_xact_lock COMMIT: 0

03
Diagnose: compare client admission with server capacity
SHOW CONFIG separates max_client_conn (how many app connections PgBouncer accepts) from default_pool_size / max_db_connections (how many PostgreSQL backends this pool may open). The application can connect successfully and still queue because these are different budgets. Also compare to PostgreSQL max_connections so the pool cannot stampede the server if PgBouncer is bypassed.
Shell
```
PGPASSWORD=labpass psql   -h localhost -p 56432 -U labuser -d pgbouncer   -c "SHOW CONFIG;"
# On PostgreSQL, cross-check:
# SELECT name, setting FROM pg_settings
# WHERE name IN ('max_connections','superuser_reserved_connections');
```
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. Success baseline: cl_waiting = 0 and maxwait = 0 under normal load.
2.2. If cl_waiting > 0, right-size the server pool (default_pool_size / pool_size) against PostgreSQL capacity; keep max_client_conn as the client-side admission budget so the queue stays in PgBouncer, not in PostgreSQL.
3.3. Replace request-scoped SET with SET LOCAL inside an explicit transaction. Success: SHOW work_mem after COMMIT returns the server default on the next checkout.
4.4. Replace pg_advisory_lock with pg_advisory_xact_lock in transaction-pooled code, or route that workflow through a session pool when lock ownership must span transactions. Success: pg_locks shows zero advisory locks after COMMIT.
5.5. Enable max_prepared_statements only when the driver uses the extended protocol and you have measured its memory overhead.
6.6. Alert on cl_waiting and maxwait from SHOW POOLS (or the equivalent stats socket), not only on PostgreSQL connection count.

Stop it recurring

01
Keep request state transaction-local
SET LOCAL and pg_advisory_xact_lock automatically revert or release at COMMIT, so the next client does not inherit another request's state. Success signals shown in comments.
SQL
```
BEGIN;
SET LOCAL work_mem = '32MB';
SELECT pg_advisory_xact_lock(424243);
-- run this request's work
COMMIT;
SHOW work_mem;  -- back to the server default
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';  -- 0
```
02
Confirm the queue drained after capacity change
After raising pool_size or fixing a stuck server connection, re-check SHOW POOLS. Success: cl_waiting=0, maxwait=0, sv_active within the new ceiling.
Shell
```
PGPASSWORD=labpass psql   -h localhost -p 56432 -U labuser -d pgbouncer   -c "SHOW POOLS;"
```

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.

Lab setup (run this first)

Starts PG16.14 plus session, transaction, and prepared-statement pools; then proves queueing, work_mem/search_path leakage, advisory-lock ownership leakage, transaction-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
transaction_cross_client_leaked_search_path=pg_catalog
session_advisory_locks_after_client_a=1
cross_client_advisory_unlock=t
advisory_locks_after_unlock=0
advisory_locks_after_xact_commit=0
status=PASS

Verify you're done

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

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

Related errors53300 08006 26000

Related errors

SQLSTATEs this runbook resolves

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

Related runbooks

Continue the same incident path

Sibling procedures that cover the adjacent setup, recovery, or prevention step.

Survive a connection storm when there is no poolerFree

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 →