PostgreSQL Connection Pooling with PgBouncer: A Complete Guide

PostgreSQL has a well-known scaling problem: every connection spawns a dedicated OS process. At 50 connections that's fine. At 500, the scheduler overhead starts showing up. At the default max of 100, your app returns errors.

The naive fix is to raise max_connections. Don't do that blindly. Each connection costs 5-10 MB of RAM plus shared memory overhead. The right fix is to reduce actual connections with a pooler, and PgBouncer is the standard tool for this.

This guide covers how PgBouncer's three pool modes work (session, transaction, statement), how to size your pool correctly, what breaks in transaction mode and how to fix it, and when you actually don't need a pooler at all.

Key insight: transaction mode can reduce 300 app threads down to 20 actual Postgres connections. But it breaks server-side prepared statements, advisory locks, and LISTEN/NOTIFY. Knowing which mode to pick and what breaks is the hard part.

Full write-up: https://rivestack.io/blog/postgresql-connection-pooling-pgbouncer