PostgreSQL Connection Pooling: Part 2 – PgBouncer - High Scalability -

PgBouncer: The PostgreSQL Connection Pooler

PgBouncer is a lightweight connection pooler that sits between PostgreSQL clients and the database server. It speaks the PostgreSQL protocol, emulating a PostgreSQL server so clients connect to it using the same syntax they would use for a direct connection. Because of this transparency, PgBouncer requires no client-side code changes.

How Connection Pooling Works

When a client connects to PgBouncer, the pooler first handles authentication on behalf of the PostgreSQL server. Two authentication paths are available:

1. PgBouncer checks its userslist.txt file, which contains username and MD5-encrypted password tuples. If the username is found, the provided password is matched against the stored value, with no connection made to PostgreSQL.
2. If passthrough authentication is configured and the user is not in userslist.txt, PgBouncer executes an auth_query against PostgreSQL. It connects as a predefined user (whose password must exist in the users list) to look up the client's password and verify it.

Once authentication succeeds, PgBouncer looks for a cached connection matching the same username and database combination. If found, that connection is returned to the client. If not, a new connection is created unless doing so would exceed one of the configurable limits:

• pool_size – maximum connections per pool
• max_client_connections – maximum incoming client connections
• max_db_connections – maximum connections to a database
• max_user_connections – maximum connections from a user

Violating the first three limits causes PgBouncer to queue the connection until one becomes available. Violating max_client_connections aborts the connection immediately.

Pooling Modes

PgBouncer determines when to return a server connection to the pool based on the configured pooling mode:

• Session pooling – a connection is returned only when the client closes its session.
• Transaction pooling – a connection is returned when a transaction completes (typically on commit or rollback). Session-based features such as prepared statements are not supported, since there is no guarantee that two transactions from the same client will use the same server connection.
• Statement pooling – a connection is returned as soon as a statement finishes executing, with autocommit always enabled.

Before a connection is returned to the pool, PgBouncer runs a reset query to strip it of session state, ensuring connections can be safely shared between clients. This query is configurable.

Transaction pooling is the most commonly used mode, though session pooling can be a better fit for certain workloads. Since pooling modes are configured per pool, you can use transaction mode for typical connections and session mode only where features like prepared statements are required.

Strengths and Limitations

What PgBouncer Does Well

• Lightweight – PgBouncer is a single process. Client commands and server responses pass through without processing, so it only maintains a small memory footprint.
• Passthrough authentication – Secure authentication can be performed without PgBouncer having access to plaintext or encrypted user passwords. This also means passwords can be changed in PostgreSQL without updating PgBouncer.
• Easy setup – Minimal configuration is required to get started.
• Performance – Properly deployed, PgBouncer can significantly improve transactions per second and scales to very large numbers of clients.

What PgBouncer Does Not Provide

PgBouncer does not include automated load balancing or high-availability features. For those, the recommended approach is to pair PgBouncer with tools like HAProxy, placing a load balancer in front of multiple PgBouncer instances or database replicas.

For read-heavy architectures, PgBouncer can be configured in front of replica nodes, with HAProxy routing read traffic across them. The master node would typically be excluded from this pool.

Setting Up PgBouncer

PgBouncer is distributed through the PostgreSQL repository and can be installed via standard package managers. For managed PostgreSQL deployments, PgBouncer can often be enabled from the cluster details view with configurable pooling mode and pool size. These settings can be adjusted later without downtime.

Once installed, the essential configuration parameters are:

1. A list of username and MD5-encrypted password tuples for client authentication, or a passthrough authentication setup for a more secure deployment.
2. Interface IP and port combinations to listen on for incoming client connections.
3. Pool definitions. A pool is a name clients use as the database name when connecting to PgBouncer, mapped to a connection string. The simplest definition is of the form:
   * = host=
   This creates dynamic pools for each database and user combination, connecting to the defined host using the provided port, database name, and username.

Additional settings are recommended for production deployments, but this minimal configuration is enough to get PgBouncer running quickly.