Connection pooling in Postgres: PgBouncer modes and what each one breaks

01Why connections are expensive

Postgres forks a backend process per connection. Each carries its own memory for work areas, catalogue caches and temporary buffers, so idle connections are not free - they consume memory that could be serving queries and add scheduling overhead.

Serverless platforms make this dramatically worse. A function that opens a connection per invocation and exits without closing cleanly leaves the server holding connections that no client is using, and a traffic spike exhausts the limit almost immediately.

02The three modes and what they break

The mode determines when a server connection is returned to the pool, and therefore how much client state can survive between statements. More aggressive reuse means more clients per server connection and less state you can rely on.

03Transaction mode is the right default, with caveats

Most application workloads are short transactions with no session state, which is exactly what transaction mode is designed for. It is the mode that delivers the large connection multiplier people adopt pooling for.

What it breaks is specific: session-level SET commands that outlive a transaction, advisory locks held across statements, LISTEN/NOTIFY, and temporary tables expected to persist. Prepared statements are the awkward one - older setups broke outright, newer PgBouncer versions handle them, and whether yours works depends on version and driver configuration rather than on theory.

04Sizing the pool

The instinct is to make the pool large. The opposite is usually correct: a pool bigger than the database can service converts a fast queue in the pooler into contention inside Postgres, where it is far more expensive. A useful starting point is a small multiple of available cores, then tune against measured wait time.

Watch pool wait time rather than utilisation. Clients waiting a few milliseconds is healthy; clients waiting hundreds of milliseconds means the pool or the database is undersized, and which one it is determines whether you raise the pool or fix the queries.

• Start from core count, not from client count.
• Alert on pool wait time, not pool utilisation.
• Separate pools for background jobs and interactive traffic.
• Cap per-application pools so one service cannot starve the rest.

05The application-side mistake

Running an application-level pool behind a server-side pooler is common and frequently misconfigured. If every one of twenty application instances holds a pool of twenty, you are asking for four hundred connections regardless of what the pooler allows.

Application pools should be small when a pooler sits in front - the pooler is doing the multiplexing. Two layers each sized as though they were the only one is how teams exhaust connections while believing they are pooling correctly.

06Diagnosing exhaustion

When connections run out, check what they are doing before adding more. Sessions sitting in idle-in-transaction are the usual culprit - an application opened a transaction, did something slow that was not database work, and never committed. Those hold locks and block vacuum while contributing nothing.

Set a timeout for idle transactions so the database reclaims them rather than waiting for an application that may never return. It converts a slow outage into a clear application error, which is much easier to fix.

• Inspect pg_stat_activity for idle-in-transaction sessions first.
• Set idle_in_transaction_session_timeout as a backstop.
• Never do network calls inside an open transaction.
• Add connections only after ruling out held transactions.