Connection pooling (pgbouncer)

Postgres uses one OS process per connection. Each connection costs roughly 10MB of RAM, plus fork overhead. At 500 connections, you have used 5GB just for connection state. Past 1000, performance collapses.

pgbouncer sits between your app and Postgres. App connects to pgbouncer (cheap, no fork). pgbouncer maintains a small pool of real Postgres connections and assigns them to clients on demand.

Three pooling modes

• Session pooling: client gets a server connection for its entire session. Same as no pooling, just multiplexed.
• Transaction pooling: client gets a server connection only during a transaction. Released on COMMIT/ROLLBACK. Most common mode.
• Statement pooling: client gets a server connection only for one statement. Most aggressive. Breaks transactions, prepared statements, session state.

Why pool?

• Web apps: each request opens a connection, queries, closes. Without pooling, connection setup is 50ms of overhead per request.
• Serverless: every Lambda invocation opens a connection. Without pooling, you blow past max_connections instantly.
• Long-lived clients: ORMs hold connections idle. Pool gives them up between queries.

Limits with transaction pooling

Transaction pooling breaks anything that relies on session state across transactions:

• SET (session variables)
• PREPARE (server-side prepared statements)
• LISTEN/NOTIFY
• Temporary tables
• Cursors held outside a transaction
• Advisory locks at session scope

Use SET LOCAL (transaction scope) instead.

The interview line

"Postgres connections are heavy: one OS process, around 10MB RAM, fork overhead. pgbouncer multiplexes many client connections onto a small server pool. Transaction pooling is the standard mode: clients borrow a server connection only during a transaction. Session-level features like LISTEN/NOTIFY or server-side prepared statements break, you trade them for handling thousands of clients on a 50-connection pool."

Alternatives

• pgbouncer: lightweight, single-threaded, the standard
• pgcat: Rust, multi-threaded, supports prepared statements
• Odyssey: Yandex, multi-threaded
• AWS RDS Proxy: managed, IAM auth
• Supabase Supavisor: their pgbouncer fork with HA