Why PgBouncer in Transaction Mode Is the Default

A bare Postgres instance handles a few hundred connections before context switching, per-connection memory, and the cost of fork() per backend eat into latency. Our backend, four workers, a chatbot consumer, and an embedding consumer all want their own pools. Add a serverless cron and the math gets worse.

PgBouncer in transaction mode multiplexes thousands of client connections onto a small fixed pool of server connections. The pool is held across requests, never per-transaction — that’s the whole point.

What you give up

Transaction-mode pooling forbids session-level state that survives a transaction boundary:

• SET LOCAL is fine. Plain SET is not — the next transaction might land on a different server connection.
• Prepared statements break unless the driver re-prepares per connection or you use protocol-level prepared statements (PG16+, pg_stat_statements configured accordingly).
• LISTEN/NOTIFY is out. We use NATS for pub/sub anyway, so this didn’t cost us anything.
• Cursors that outlive a transaction don’t work.

For an ORM-heavy backend this is mostly invisible. Drizzle and Payload both behave correctly here. The one place we got bit was a long-running analytics query that opened a cursor and streamed rows — we moved it to a direct connection bypassing the bouncer.

What it bought us

• Connection count to Postgres dropped from ~180 idle to ~25 active.
• p95 query latency under load fell because new requests no longer triggered backend startup.
• We could finally scale workers horizontally without thinking about max_connections.

The rule

Default everything to the pooled connection. Bypass the bouncer only for the rare workload that needs session state — and write that workload’s config down explicitly, because the next engineer to touch it will assume the pool is universal.