PgBouncer SET search_path lost between queries: session variables in transaction mode

Your application sets search_path (or timezone, or role, or statement_timeout) after connecting, and everything works in staging. In production, under concurrent load, queries intermittently hit the wrong schema, run with the wrong role context, or fail with “relation does not exist” for tables that clearly exist. Restarting the app “fixes” it briefly. Nothing in PgBouncer’s metrics looks wrong.

This is pool mode mismatch: the application depends on session-level state, but PgBouncer is running in transaction pooling mode, where a client is assigned a different server connection for every transaction. Session state set on one server connection is not present on the next one. The failure is silent, load-dependent, and looks exactly like an application logic bug. PgBouncer itself reports nothing.

What this means

In transaction pooling mode, PgBouncer holds a server connection only for the duration of a transaction. When the transaction commits, the server connection goes back to the pool and the next client transaction may land on a completely different server connection. Anything the client set with a session-scoped SET lives on the server connection’s PostgreSQL session, not on the client’s PgBouncer connection. Once the transaction ends, the association is gone.

The result has two faces:

• State loss. The client’s next transaction runs on a fresh server connection where search_path is back to the default. Unqualified table names resolve differently, and the application reads or writes the wrong schema, or errors out.
• State leak. The leftover SET still lives on the server connection that was returned to the pool. A different client that checks out that connection inherits someone else’s search_path, role, or timezone. Under concurrency this produces non-deterministic “wrong data” bugs that cannot be reproduced locally.

Whether leftover state is scrubbed on return depends on server_reset_query and server_reset_query_always behavior in your PgBouncer version and pool mode. Either way, the guarantee you need (“my session variables survive across my transactions”) does not exist in transaction mode. No PgBouncer metric captures this; only application error logs and data anomalies reveal it.

The same mechanism breaks prepared statements, temp tables, advisory locks, and LISTEN/NOTIFY. See PgBouncer prepared statement does not exist and PgBouncer LISTEN/NOTIFY not working for those variants.

flowchart LR
  subgraph clients["Clients"]
    A["Client A
SET search_path = tenant_1"]
    B["Client B
no SET"]
  end
  subgraph pool["PgBouncer pool (transaction mode)"]
    S1["Server conn 1
search_path = tenant_1"]
    S2["Server conn 2
search_path = default"]
  end
  A -- "txn 1: sets state" --> S1
  A -- "txn 2: reassigned" --> S2
  B -- "txn 1: inherits leftover state" --> S1

Common causes

Quick checks

All of these are read-only and safe.

# 1. Confirm the pool mode (the decisive check)
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW CONFIG;" | grep -i pool_mode

# 2. Check pool health: this failure mode shows NORMAL metrics
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

-- 3. On the application side, prove the state loss directly.
-- Run these through PgBouncer as separate autocommit statements:
SET search_path = tenant_1;
SELECT current_setting('search_path');   -- returns tenant_1 (same transaction)
-- Now, in a NEW transaction:
SELECT current_setting('search_path');   -- likely back to "$user", public

-- 4. On PostgreSQL, list the pooled backends so you can cross-reference
-- with PgBouncer SHOW SERVERS and spot which server connections churn.
-- pg_stat_activity does not expose search_path; use this to map linkage.
SELECT pid, usename, application_name, state, backend_start
FROM pg_stat_activity
WHERE backend_type = 'client backend';

# 5. Grep application logs for the telltale errors
grep -E "relation .* does not exist|permission denied|must be owner" /var/log/app/*.log | tail -20

# 6. Confirm which databases/users the affected app connects as,
#    so you know which pool to change if you switch modes
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW CLIENTS;" | head -30

How to diagnose it

1. Confirm transaction pooling. Run SHOW CONFIG and check pool_mode. If it says transaction or statement, session state is not guaranteed across transactions. In session mode, this failure class does not apply.
2. Reproduce deterministically. Through PgBouncer, run SET search_path = x and SELECT current_setting('search_path') in one transaction, then read the setting again in a second transaction. If the second read returns the default, you have confirmed the mechanism. Repeat several times; under a busy pool, reassignment is near-certain.
3. Inventory the session state the app depends on. Grep the codebase, ORM config, and migration tooling for SET search_path, SET ROLE, SET TIME ZONE, SET statement_timeout, SELECT set_config(...), and any options= connection-string parameters. Note which are session-scoped versus per-transaction.
4. Distinguish loss from leak. If errors are “my setting is gone,” that is state loss. If queries return another tenant’s data or fail with privileges the app never requested, that is leftover state bleeding between clients on a reused server connection. The leak variant is a data-integrity incident; treat it accordingly.
5. Rule out a red herring: check that PgBouncer is otherwise healthy. Pool metrics (cl_waiting, sv_active, avg_query_time) look normal for this failure mode. If they are not normal, you may have pool exhaustion instead. See PgBouncer pool exhaustion.
6. Correlate timing with deploys. This failure class often appears right after a switch from session to transaction mode, after moving an app behind PgBouncer, or after an ORM upgrade that changed connection initialization behavior.

Metrics and signals to monitor

No PgBouncer metric detects this directly. These signals provide context and corroboration.

Fixes

Use SET LOCAL inside the transaction (preferred when code changes are possible)

SET LOCAL scopes the setting to the current transaction. Because transaction pooling guarantees the client keeps its server connection for the whole transaction, the setting is valid exactly where it is needed and vanishes at commit, so nothing leaks to the next client either.

BEGIN;
SET LOCAL search_path = tenant_1, public;
SELECT ... ;
COMMIT;

Tradeoff: requires every query path to run inside an explicit transaction. Autocommit single statements cannot carry a SET LOCAL. Chatty ORMs may need a per-request transaction wrapper, which most already support.

Schema-qualify object names

If search_path is only used to avoid writing schema prefixes, qualify the names (tenant_1.orders) and stop depending on the setting at all. This is the most robust fix but can mean touching a lot of SQL. Some ORMs support a per-entity schema mapping that generates qualified names.

Move the setting to the role or database level in PostgreSQL

Defaults that should apply to every connection can be set server-side so they do not depend on session SET at all:

ALTER ROLE app_user SET search_path = tenant_1, public;
ALTER ROLE app_user SET statement_timeout = '5s';

These apply at connection establishment, so every pooled checkout gets the same baseline. Tradeoff: the value is static per role/database. It cannot vary per tenant or per request.

Switch the affected pool to session mode

For databases or users that genuinely need session semantics, set pool_mode = session for that specific pool (per-database override in the [databases] section) and RELOAD. This preserves SET, prepared statements, temp tables, and advisory locks for those clients.

Tradeoff: session mode holds a server connection for the entire client session, which sharply reduces multiplexing. You are paying for it in PostgreSQL connection slots, so revisit pool sizing. See PgBouncer capacity planning. A common pattern is session mode for the migration/tooling user and transaction mode for the stateless app workload.

Split tenants by database or role instead of search_path

If search_path is doing tenant routing, moving tenants to separate databases (or separate roles with role-level defaults) makes each tenant a separate PgBouncer pool. That eliminates cross-tenant state bleed structurally, at the cost of more pools and more total server connections.

Do not rely on “scrubbing” as the fix

Trying to make the pool safe by resetting connections harder (for example forcing a reset query in transaction mode via server_reset_query_always) converts silent, non-deterministic breakage into deterministic breakage: clients always lose their state after every transaction. It does not give you session semantics; it just makes the failure uniform. Fix the application’s assumption or the pool mode, not the reset behavior.

Prevention

• Audit before switching pool modes. Before moving any database/user to transaction mode, grep the application for SET, PREPARE, LISTEN, advisory locks, temp tables, and set_config. This single audit prevents the entire failure class.
• Load-test with real concurrency. Single-user testing passes because the same server connection gets reused. State loss only appears when the pool is busy enough to reassign connections. Test with at least as many concurrent clients as pool_size.
• Prefer role/database-level defaults. Anything that should always be true for a workload belongs in ALTER ROLE ... SET or ALTER DATABASE ... SET, not in per-connection initialization.
• Document pool mode per database. Make SHOW CONFIG output part of your deploy verification so a pool mode change is a deliberate, reviewed event.
• Alert on application error classes, not just infrastructure. “Relation does not exist” at a rate that scales with concurrency is a pooling-mode symptom. Wire app logs into your triage path for PgBouncer-fronted services. See PgBouncer monitoring checklist.

How Netdata helps

• Pool metrics prove innocence. Netdata’s PgBouncer collector charts cl_waiting, sv_active, sv_idle, and maxwait per pool, so you can confirm in seconds that the pool is healthy and stop chasing exhaustion.
• Latency attribution. avg_wait_time versus avg_query_time side by side shows whether latency comes from the pool or the backend. In this failure mode both look normal, which is itself the diagnostic clue.
• Per-pool breakdown. Because pools are per (database, user), Netdata shows you exactly which pool serves the failing application, which is the pool whose mode you need to change.
• Correlation with deploys. A drop in successful transaction rate on one pool, lining up with app error reports and a pool mode change, brackets when the state-loss behavior started.
• Correlation with PostgreSQL. Viewing PgBouncer server connection counts alongside backend activity helps confirm the reassignment churn that makes the bug load-dependent.

Related guides

• PgBouncer advisory locks in transaction mode: orphaned locks and mysterious contention
• PgBouncer avg_wait_time high: the latency the pool itself is injecting
• PgBouncer capacity planning: runway for pools, clients, and PostgreSQL slots
• How PgBouncer actually works in production: a mental model for operators
• PgBouncer LISTEN/NOTIFY not working: why pub/sub needs session pooling
• PgBouncer maxwait high: the oldest client waiter and how close it is to timing out
• PgBouncer monitoring checklist: the signals every connection pooler needs
• PgBouncer monitoring maturity model: from survival to expert
• PgBouncer pool exhaustion: clients queue, wait times climb, and the retry cascade
• PgBouncer pool utilization high: sv_active approaching pool_size before clients queue
• PgBouncer prepared statement does not exist: transaction pooling and lost session state
• PgBouncer query_wait_timeout: clients disconnected after waiting too long for a connection