What is connection pooling with PgBouncer and when should you use it?

PgBouncer is a lightweight connection pooler that sits between the application and PostgreSQL. PostgreSQL forks a new OS process per connection (~5-10MB RAM). With 1000 application connections, PostgreSQL would have 1000 processes — this degrades performance severely due to process overhead, context switching, and lock table size. PgBouncer maintains a small pool of actual PostgreSQL connections and multiplexes many client connections onto them. Configuration: pool_mode = transaction (most efficient — connection returned to pool after each transaction), default_pool_size = 20 (20 server connections per database/user), max_client_conn = 1000. Limitations in transaction mode: cannot use session-level features like prepared statements (use protocol-level prepared statements), advisory locks, SET commands, or LISTEN/NOTIFY. Pgcat and Odyssey are modern alternatives with more features.