What is PgBouncer?

PgBouncer is a lightweight, high-performance connection pooler* for PostgreSQL that sits between your applications and the database, efficiently managing a smaller pool of actual database connections, reducing server overhead, and improving scalability, especially for apps with many short-lived connections like web apps, by reusing connections instead of opening new ones for every request. It offers various pooling modes (session, transaction, statement) to balance feature support with efficiency, helping databases handle more users without resource exhaustion. 

*Connection pooling is a caching technique that stores open database connections in a "pool" to be reused for multiple requests, drastically reducing the expensive overhead of establishing new connections (DNS lookups, TCP handshakes, authentication, authorization, SSL negotiation) for every transaction, thereby improving application performance, scalability, and resource efficiency, especially for high-traffic web apps

Key Functions & Benefits:

• Connection Pooling: Reuses a pool of database connections, preventing the PostgreSQL process-per-connection overhead.
• Performance: Reduces latency and CPU/memory usage on the database server.
• Scalability: Allows more application clients to connect without overwhelming the database.
• Modes: Supports different pooling levels:
• • Session Pooling: Assigns a server connection for the client's entire session (most feature-rich).
  • Transaction Pooling: Assigns a connection only for the duration of a transaction (more aggressive).
  • Statement Pooling: Assigns a connection only for a single statement (most aggressive, requires autocommit).
• High Availability: Can be used in failover scenarios to manage connections across servers. 

How it Works:

1. Applications connect to PgBouncer (typically on port 6432) instead of directly to PostgreSQL.
2. PgBouncer holds a pool of real PostgreSQL connections.
3. When an app needs a connection, PgBouncer assigns one from the pool.
4. When the app disconnects or finishes a transaction (depending on the mode), the connection is returned to the pool for reuse.