After the pooler was switched to transaction mode, a scheduled job started blocking unrelated clients on a lock nobody was holding

Reported symptoms

PgBouncer was switched from session mode to transaction mode to reduce backend connections. Backend count fell from 400 to 65, as intended.

Within a day:

• A scheduled job that takes a session-level advisory lock began blocking unrelated clients for minutes at a time. The blocked clients are waiting on a lock that no running query holds.
• A reporting job that sets work_mem at the start of its session now produces plans consistent with the cluster default — intermittently.
• A data-loading job using temporary tables fails with relation "staging_rows" does not exist, but only under concurrency.

Everything worked correctly in staging, where the job runs alone.

Reverting to session mode makes all three disappear, and the backend count return to 400.

Evidence provided

$ SHOW CONFIG;

 default_pool_size           | 10
max_client_conn             | 500
pool_mode                   | transaction
server_reset_query          | DISCARD ALL
server_reset_query_always   | 0
query_wait_timeout          | 120
server_idle_timeout         | 600
server_lifetime             | 3600

$ psql "port=6432" -c "SET work_mem = '64MB'" -c "SHOW work_mem"

SET
work_mem 
----------
64MB
(1 row)

$ psql "port=6432" -c "SELECT pg_advisory_lock(42)" -c "SELECT count(*) FROM pg_locks WHERE locktype='advisory'"

 pg_advisory_lock 
------------------

(1 row)

locks_held 
------------
        1
(1 row)

-- then, on a DIRECT connection:
-- SELECT pg_advisory_lock(42);   <- blocked; killed after 120 s

At 100 clients the pool served the workload on 10 server connections while a direct connection failed outright against max_connections of 100. At 80 clients, where direct works: 5124 tps direct against 2627 pooled.

Work the evidence before reading on

1. Clients are waiting on an advisory lock that no running query holds. Who holds it?
2. The SET persisted through the pool in the test above. Does that mean transaction pooling preserves settings?
3. Why did staging not reproduce any of this?
4. The pool is half the speed of a direct connection. Why deploy one?

Root cause

Transaction pooling breaks the identity between client and server connection

The advisory lock does leak, and that is the serious one

Staging could not have found this

Reverting to session mode fixed all three because it restores the identity — at the cost of the 400 backends the change was made to avoid.

Resolution

Audit which session-scoped features each application uses. This is an application audit, not a database change, and it is the whole work: SET outside a transaction, CREATE TEMP TABLE, pg_advisory_lock, LISTEN, server-side prepared statements, WITH HOLD cursors.

Then fix each at the application, because the pooler cannot.

Advisory locks — use the transaction-scoped variant:

-- instead of pg_advisory_lock(key), which is session-scoped
SELECT pg_advisory_xact_lock(12345);

A strict improvement even without a pooler: it cannot be leaked by an awkward client disconnect either.

Settings — SET LOCAL inside the transaction, or on the role:

BEGIN;
SET LOCAL work_mem = '256MB';
SELECT ...;
COMMIT;

ALTER ROLE reporting SET work_mem = '64MB';

Temporary tables — create and use them inside a single transaction, or use an ordinary table with a session identifier, or ON COMMIT DROP so the lifetime is explicit.

Prepared statements — most drivers can be told not to use server-side prepared statements, or to re-prepare. Check the driver’s documentation rather than assuming; this is the most common silent incompatibility.

LISTEN/NOTIFY — cannot work through transaction pooling at all. Give those clients a separate pool in session mode, or connect directly.

If an application genuinely needs session state, give it its own pool rather than reverting the estate:

[databases]
orders          = host=db1 dbname=orders pool_mode=transaction
orders_sessions = host=db1 dbname=orders pool_mode=session

Verification

The scheduled job no longer blocks unrelated clients — confirmed from pg_locks, not from an absence of complaints:

SELECT l.pid, a.state, l.locktype, l.objid, l.granted,
       now() - a.state_change AS in_state_for
FROM pg_locks l LEFT JOIN pg_stat_activity a USING (pid)
WHERE l.locktype = 'advisory'
ORDER BY l.granted, l.pid;

A row with granted = true on a backend in state idle is a leaked session lock. Under the fix there should be none.

The reporting job’s plans are consistent — checked from inside the job’s own transaction, since a separate session tells you nothing about which server connection the job got.

The loading job succeeds under concurrency, tested under concurrency. Staging did not reproduce any of this because the job ran alone, and a single-client test will pass for the same reason.

Backend count stays at the pooled level, and PgBouncer’s own view agrees:

SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend';

SHOW POOLS;
SHOW SERVERS;

Latency is measured and the trade acknowledged: 5124 tps direct against 2627 pooled at 80 clients, and direct failing outright at 100. Both numbers matter, and neither should surprise anybody afterwards.

Prevention

Audit for session-scoped features before switching pool mode. Transaction pooling is a change to application semantics, not a configuration tweak.

Prefer pg_advisory_xact_lock to pg_advisory_lock everywhere.

Prefer SET LOCAL to SET, and role settings to both.

Test pooling changes under concurrency. A single-client test hands the same server connection back every time and will pass.

Alert on advisory locks held by idle backends — the direct detection of the leak:

SELECT count(*) FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE l.locktype = 'advisory' AND l.granted AND a.state = 'idle';

Give incompatible applications their own pool in session mode, rather than reverting the estate for one job.

Know what a pooler is for. Not speed — direct was nearly twice as fast at a concurrency where direct works. Its value is that 100 clients work at all on a cluster whose max_connections is 100.

Set query_wait_timeout deliberately. A client waits up to 120 seconds by default for a server connection; whether that is a graceful queue or a hidden outage depends on your application’s own timeouts, and the two should be chosen together.