PgBouncer: What Transaction Mode Breaks, and the Fix

PgBouncer is a small connection pooler that sits between your app and PostgreSQL. Your app opens as many connections as it likes to PgBouncer on port 6432, and PgBouncer hands each one a real Postgres connection only while it needs it. That's the whole idea, and it's why a box with max_connections = 100 can serve a few thousand serverless functions without falling over.

The catch is that the useful setting, transaction pooling, quietly changes what a "connection" means. Code that was fine on a direct connection can start doing strange things once two clients share one server connection. Everything here is checked against the PgBouncer config docs and the changelog on November 1, 2026 (latest release 1.26.1).

Why Postgres needs a pooler at all

Every Postgres connection is a separate operating system process with its own memory. A hundred of them is normal. A thousand is a server spending its time on processes instead of queries. MySQL handles lots of connections far more cheaply, which is why people coming from Laravel and MySQL are often surprised by this.

You don't notice it with one app server holding a pool of 10. You notice it the week you move to Lambda, Vercel functions or a queue with 50 workers, each opening its own connections, and Postgres starts answering with FATAL: sorry, too many clients already.

The three pool modes

pool_mode decides when PgBouncer takes a server connection back:

• session (the default): when the client disconnects. Safe, and almost pointless for the problem above, because a client that stays connected keeps its server connection the whole time.
• transaction: when the transaction ends. This is the mode everyone actually wants.
• statement: after every statement, which means multi-statement transactions are refused. I've never seen a good reason to use it for an application.

With transaction mode, max_client_conn (default 100, raise it) is how many clients can connect to PgBouncer, and default_pool_size (default 20) is how many real connections it opens per user and database pair. A thousand clients on twenty server connections is a normal setup, as long as transactions are short.

What transaction mode breaks

The docs put it in one line: clients "must not use any session-based features, since each transaction ends up in a different connection." In practice that means:

• SET outside a transaction. You run SET search_path = tenant_42 or SET statement_timeout = '5s', and your next query may land on a different server connection that never saw it. Worse, the server connection you set it on now carries it for whoever gets it next. PgBouncer only tracks parameters Postgres reports back to the client, and search_path is reported only from Postgres 18 on. On 17 and older, a SET search_path isn't tracked at all, so it leaks to other clients unless you turn on server_reset_query_always. Use SET LOCAL inside a transaction instead, or put the setting on the role.
• LISTEN/NOTIFY listeners. A listener needs one connection that stays open. Give that one process a direct connection to Postgres.
• Session-level advisory locks (pg_advisory_lock). The lock belongs to a server connection your client may not have a second later. pg_advisory_xact_lock inside a transaction works.
• Temporary tables that outlive a transaction, and cursors declared WITH HOLD.
• SQL-level PREPARE and EXECUTE. PgBouncer passes these straight through without tracking them.

That search_path one is the scary one, because nothing errors. Plenty of production databases are still on 16 or 17, and managed providers move slowly. A multi-tenant app that switches schema with a plain SET can read the wrong tenant's rows. If you inherit an app that does this, fix it before you put a pooler in front of it.

Prepared statements: fixed, mostly

For years the most common PgBouncer error was some form of prepared statement "pdo_stmt_00000001" does not exist. Drivers like PHP's PDO for Postgres, and plenty of Node and Go libraries, prepare a statement on one server connection and execute it on another.

Version 1.21 (October 2023) added max_prepared_statements, which tracks protocol-level prepared statements and re-prepares them on whichever server connection the client gets. You had to switch it on. Since 1.24 (January 2025) it defaults to 200, so a current PgBouncer handles this on its own. If your distro still ships an older one, check the version before you start debugging the driver.

Prisma has its own rules: its docs still have you add pgbouncer=true to the pooled connection string, and migrations need a directUrl that skips the pooler, because prisma migrate uses session features.

Managed versions

If you're on Supabase you already have a pooler: Supavisor, with transaction mode on port 6543 and session mode on 5432. The same rules about SET, LISTEN and advisory locks apply there. Amazon RDS has RDS Proxy, which does the same job with its own "pinning" behaviour when it sees session state, and it bills per vCPU of the database. Neon puts PgBouncer in front of every project. On a plain VPS, PgBouncer from the distro packages, configured with auth_type = scram-sha-256, is a ten-minute job.

My default: run transaction mode, give migrations, cron jobs with advisory locks and any LISTEN process a direct connection, and grep the codebase for SET before switching. That last grep has the best effort-to-trouble ratio of anything on this list.

If you're moving an app to serverless functions and the database is the part you're worried about, that's work I take on as part of full-stack development. The first call is free.