Connection Pooling

graph LR subgraph app["2000 app-side connections"] a1["app conn 1"] a2["app conn 2"] a3["... 2000 total"] end subgraph real["50 real Postgres connections"] r1["real conn 1"] r2["... 50 total"] end app --> pgb["pgBouncer<br/>(transaction-mode pool)"] pgb --> real

Why: new TCP + TLS + app handshake (PostgreSQL auth) = 50–200ms. Pooling pre-establishes connections and reuses them.

Parameters: max size (limits parallelism — tune by concurrency × query_duration), min idle (warm connections for bursty traffic), max lifetime (reconnect stale connections), idle timeout (close unused connections).

pgBouncer modes: - Session: one DB connection per client session. Simple, wasteful. - Transaction: one DB connection per transaction. High multiplexing (recommended). Breaks SET, prepared statements, advisory locks. - Statement: one connection per statement. Most aggressive.

PostgreSQL allocates ~5MB RAM per connection. At 1000 connections = 5GB RAM. pgBouncer sits between app and DB, multiplexing 1000 app connections onto 20 DB connections.

Worked example: a service runs 200 app instances, each opening 10 direct connections to Postgres at startup — 2,000 connections. Postgres's max_connections is set to 500 (5GB of the DB host's RAM reserved for connection state alone). Under a traffic spike, new instances autoscale up, try to open more connections, and start getting FATAL: too many clients already — the database is rejecting connections before it's even under query load. Fixing it: put pgBouncer in transaction mode between the app and Postgres. The 2,000 app-side connections multiplex onto a pool of 50 real Postgres connections, because most connections are idle between queries — a connection is only borrowed from the real pool for the duration of one transaction, then returned. Total Postgres-side RAM drops from 10GB to 250MB, and max_connections no longer caps how many app instances can scale out.

Common pitfall

Transaction-mode pooling's multiplexing surfaces immediately if the app uses session-level features: a SET search_path or advisory lock issued on one borrowed connection won't still be there on the next query, because the next query may land on a different real connection underneath. This is subtle specifically because it works correctly for the vast majority of stateless query patterns — it only breaks for the specific subset of features that assume "same session, same connection," which won't show up until that exact code path runs.