PgBouncer Transaction Pooling for Multi-Tenant SaaS

Transaction-mode pooling lets a few hundred Postgres backends serve thousands of tenant connections, but it silently breaks any tenant scoping that relies on session-level state. This page sits under connection pooling in multi-tenant systems, and covers how to keep row-level security and search_path correct when PgBouncer is handing the same backend to a different tenant on every transaction.

Problem Framing

PgBouncer has three pool modes. In session mode a client owns a server connection until it disconnects; in transaction mode the server is returned to the pool at every COMMIT or ROLLBACK; in statement mode it is returned after each statement. Transaction mode is the one that gives multi-tenant SaaS its leverage: a Postgres backend is expensive (each is a process with its own memory), so multiplexing thousands of idle app connections onto a small backend pool is what makes high tenant density affordable.

The catch is that transaction mode shares one physical backend across many tenants over its lifetime. Anything you set on the session — SET search_path, SET ROLE, a session-scoped SET app.current_tenant GUC used by an RLS policy — outlives the transaction that set it and leaks into the next tenant's transaction on the same backend. The query is perfectly parameterized and the application code looks correct, but tenant B executes against tenant A's search_path or RLS GUC. That is a cross-tenant read with no injection string anywhere.

This failure is hard to catch in development because it only surfaces under concurrency. With one developer and one connection, the same backend always carries the right state, so tests pass and the bug ships. In production the pool churns: a backend that served tenant A's analytics export is handed to tenant B's checkout milliseconds later, and whichever session-scoped value A left behind is still active. The defect is therefore load-dependent and intermittent — the worst kind to reproduce — which is why the discipline below has to be enforced structurally, in a shared helper, rather than left to each query author to remember.

The diagram shows why a session-scoped SET is unsafe and a SET LOCAL is not: the dashed transaction boundary is exactly where PgBouncer recycles the backend.

In transaction mode the backend is recycled at every COMMIT, so only transaction-scoped state (SET LOCAL) stays bound to the right tenant.

Step-by-Step Guide

1. Configure PgBouncer for transaction pooling

Set pool_mode = transaction and size the pools deliberately. default_pool_size caps backends per (user, database) pair, and max_client_conn caps the front-side connections PgBouncer accepts. The whole point is that max_client_conn is large while default_pool_size stays small.

[databases]
appdb = host=10.0.0.5 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 25
max_client_conn = 5000
server_reset_query =
server_reset_query_always = 0

Note server_reset_query is intentionally empty for transaction mode. The default DISCARD ALL is a session-mode safeguard and is wasted work between transactions; in transaction mode you must instead guarantee that no session state is set in the first place. Leaning on server_reset_query as your isolation mechanism is a trap — it adds a round trip to every transaction and still does nothing if a SET happens mid-transaction, so treat it as belt-and-suspenders at most, never as the primary control. Size default_pool_size against the database's real max_connections minus headroom for migrations, replication, and admin sessions; oversubscribing the backend pool is a common way to turn a pooling win into connection-refused errors during traffic spikes.

2. Scope all tenant state with SET LOCAL inside a transaction

Replace every session-level SET with SET LOCAL, which is reverted automatically at the end of the transaction. This is the single most important change. The tenant GUC that your RLS policy reads, and any search_path for a schema-per-tenant layout, must be set this way and only inside an open transaction.

BEGIN;
SET LOCAL app.current_tenant = '550e8400-e29b-41d4-a716-446655440000';
SET LOCAL search_path = tenant_550e8400, public;
SELECT id, total FROM orders;        -- RLS reads current_setting('app.current_tenant')
COMMIT;                              -- both SET LOCAL values vanish here

3. Force every query through a transaction in the application layer

SET LOCAL outside a transaction is a no-op that emits a warning, so the app must open an explicit transaction even for a single read. Wrap tenant context establishment and the query in the same transaction.

import { Pool } from "pg";

const pool = new Pool({ host: "pgbouncer", port: 6432, database: "appdb" });

export async function withTenant<T>(
  tenantId: string,
  fn: (run: (sql: string, params?: unknown[]) => Promise<any>) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    // set_config(..., true) === SET LOCAL; the third arg binds tenantId safely
    await client.query("SELECT set_config('app.current_tenant', $1, true)", [tenantId]);
    const result = await fn((sql, params) => client.query(sql, params));
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

Using set_config(name, value, true) lets you bind the tenant ID as a parameter; the literal SET LOCAL app.current_tenant = '...' form cannot take a bind parameter and would force string interpolation.

4. Disable client-side prepared statements (or use a compatible mode)

Transaction pooling and the extended-query protocol's named prepared statements do not mix on older PgBouncer: a PREPARE lands on one backend, and the matching EXECUTE may be routed to another that never saw it, raising prepared statement "S_1" does not exist. PgBouncer 1.21+ added max_prepared_statements to track and replay prepared statements across backends; if you cannot enable it, turn off statement caching in the driver.

// node-postgres: pass query text each time instead of named prepared statements
await client.query({ text: "SELECT id FROM orders WHERE total > $1", values: [100] });
// JDBC URL: prepareThreshold=0 disables server-side prepares
// Prisma / many tools append ?pgbouncer=true to the connection string

5. Tag connections per tenant for observability

PgBouncer's application_name passthrough and SHOW POOLS let you see pool pressure, but per-tenant attribution needs an explicit tag. Set application_name per transaction so slow-query logs and pg_stat_activity show which tenant held a backend.

await client.query("SET LOCAL application_name = $1", [`tenant:${tenantId}`]);

Verification

Prove the leak is closed by interleaving two tenants over the same pool and asserting that neither sees the other's GUC. Because transaction mode may hand both calls the same backend, a session-scoped SET would fail this test; SET LOCAL passes it.

-- Run in a transaction; outside one, current_setting returns the leaked value.
BEGIN;
SET LOCAL app.current_tenant = 'tenant-a';
SELECT current_setting('app.current_tenant');  -- 'tenant-a'
COMMIT;
-- New transaction on a possibly-reused backend:
BEGIN;
SELECT current_setting('app.current_tenant', true);  -- NULL, not 'tenant-a'
COMMIT;

A clean run logs the second value as empty. If it returns tenant-a, a session-level SET is still in your code path:

 current_setting
-----------------

(1 row)

Failure Modes & Gotchas

Session SET leaking across tenants. Symptom: a tenant intermittently reads another tenant's rows under load. Root cause: a session-scoped SET ROLE/SET search_path persisted on a recycled backend. Fix: replace every SET with SET LOCAL (or set_config(..., true)) inside an explicit transaction.
SET LOCAL outside a transaction. Symptom: RLS sees no tenant and returns zero rows, with a SET LOCAL can only be used in transaction blocks warning. Root cause: autocommit mode ran the SET LOCAL as its own transaction. Fix: open BEGIN before setting context and keep the query in the same transaction.
Prepared-statement errors. Symptom: prepared statement "S_1" does not exist under transaction mode. Root cause: EXECUTE routed to a backend that never saw the PREPARE. Fix: enable max_prepared_statements on PgBouncer 1.21+, or disable named prepares in the driver.
Advisory locks and LISTEN/NOTIFY silently break. Symptom: session advisory locks never release, or notifications never arrive. Root cause: both are session-scoped and the backend moves between transactions. Fix: use transaction-level advisory locks (pg_advisory_xact_lock) and run LISTEN consumers through a session-mode port.

FAQ

Can I still use Postgres row-level security behind transaction-mode PgBouncer? Yes, and it is the recommended pattern. Store the tenant in a GUC with SET LOCAL app.current_tenant (or set_config(..., true)) inside the transaction, and have the RLS policy read current_setting('app.current_tenant'). Because the value is transaction-scoped, it cannot leak to the next tenant on a recycled backend.

Why not just use session pooling to avoid all of this? Session mode pins one backend per client connection, so your tenant density is capped by max_connections rather than by transaction throughput — exactly the limit transaction pooling exists to remove. For high tenant counts the memory cost of one backend per client is prohibitive; transaction mode with SET LOCAL keeps both isolation and density.

Do I need a second PgBouncer for session-scoped features? Often, yes. Features that genuinely require a stable session — LISTEN/NOTIFY, session advisory locks, server-side cursors held open across transactions — should connect through a separate database entry configured with pool_mode = session, while your normal request traffic uses the transaction-mode port.