Scaling PostgreSQL Throughput: Achieving 4x Performance with PgBouncer

Originally published on tamiz.pro.

In high-traffic applications, database connection management often becomes a significant bottleneck long before the database itself runs out of steam. Opening and closing connections is an expensive operation, consuming CPU cycles and memory on both the client and server. For PostgreSQL, this overhead is particularly pronounced due to its process-per-connection model. This deep-dive explores how we tackled this challenge, achieving a remarkable 4x improvement in throughput by strategically implementing PgBouncer as a connection pooler.

The PostgreSQL Connection Overhead Problem

PostgreSQL's architecture is robust and highly reliable, but its handling of client connections can introduce performance limitations under specific workloads. When a new client connects, PostgreSQL forks a new backend process dedicated to that connection. This process handles authentication, query parsing, execution, and result transmission. While this model offers excellent isolation and stability, it incurs a non-trivial cost:

• Forking Overhead: Creating a new process involves memory allocation, copying parent process state, and CPU cycles, which is slow.
• Memory Footprint: Each backend process consumes memory, and a large number of active connections can quickly exhaust available RAM.
• Context Switching: With many concurrent connections, the operating system spends more time context switching between processes, reducing effective CPU utilization for query execution.
• Resource Limits: The maximum number of connections (controlled by max_connections) is a hard limit, and reaching it leads to connection refused errors.

Our application experienced these exact symptoms. Under peak load, response times degraded, and we observed high CPU usage on the database server, even when queries themselves were optimized. Monitoring revealed a high rate of new connection attempts and a significant portion of server time spent on connection setup and teardown.

Introducing PgBouncer: The Connection Pooler Solution

PgBouncer is a lightweight, open-source connection pooler for PostgreSQL. It acts as a proxy between your application and the PostgreSQL server. Instead of connecting directly to PostgreSQL, applications connect to PgBouncer. PgBouncer then maintains a pool of open connections to the PostgreSQL server and reuses them for incoming client requests.

How PgBouncer Works

PgBouncer operates in a few key ways to mitigate connection overhead:

1. Client Connection Acceptance: It accepts incoming connections from clients (applications).
2. Server Connection Pooling: It maintains a fixed or dynamic pool of connections to the actual PostgreSQL server.
3. Connection Reuse: When a client requests a connection, PgBouncer either provides an existing idle connection from its pool or establishes a new one to the server if the pool is not full.
4. Connection Release: When a client disconnects, PgBouncer doesn't close the connection to the PostgreSQL server; instead, it returns it to the pool, making it available for the next client.

PgBouncer Pooling Modes

PgBouncer offers three primary pooling modes, each with different characteristics:

• Session Pooling (default): This is the most common and generally recommended mode. PgBouncer assigns a server connection to a client for the entire duration of the client's session. Once the client disconnects, the server connection is returned to the pool. This mode is safe for all PostgreSQL features, including transactions, temporary tables, and prepared statements, as the client always has a dedicated server connection for its session.
• Transaction Pooling: A server connection is assigned to a client only for the duration of a single transaction. After the COMMIT or ROLLBACK, the connection is immediately returned to the pool. This mode can offer higher concurrency but comes with restrictions: temporary tables, prepared statements, and session-level settings that persist across transactions are not safe, as the next transaction might get a different server connection. This mode is ideal for workloads dominated by short, atomic transactions.
• Statement Pooling: The most aggressive pooling. A server connection is assigned for a single statement. This mode has even stricter limitations than transaction pooling and is rarely used unless the workload consists solely of independent, single-statement queries without any session state dependencies.

For our use case, which involved a mix of short and longer transactions, and relied on prepared statements, Session Pooling was the safest and most suitable choice. While Transaction Pooling could potentially offer higher raw throughput for very specific workloads, the compatibility risks were too high.

Implementation Steps and Configuration

Our implementation involved setting up PgBouncer on a dedicated EC2 instance, separate from the PostgreSQL database server. While it can run on the same machine, separating them provides better resource isolation and scalability.

1. Installation

On an Ubuntu-based system, installation is straightforward:

sudo apt update
sudo apt install pgbouncer

2. Configuration (/etc/pgbouncer/pgbouncer.ini)

The pgbouncer.ini file is central to configuring PgBouncer. Key sections we focused on:

[databases] Section

This section defines the databases PgBouncer will proxy. The format is database_name = host=DB_HOST port=DB_PORT dbname=DB_NAME auth_user=AUTH_USER. auth_user is a special user PgBouncer uses to connect to PostgreSQL itself for authentication.

[databases]
my_app_db = host=10.0.0.10 port=5432 dbname=my_app_db auth_user=pgbouncer_auth

[pgbouncer] Section

This section contains global PgBouncer settings.

• listen_addr: The IP address PgBouncer listens on for client connections. * for all interfaces.
• listen_port: The port PgBouncer listens on.
• auth_type: How PgBouncer authenticates clients. md5 is common.
• auth_file: Path to the userlist file for authentication.
• pool_mode: session (as discussed above).
• max_client_conn: Maximum number of client connections PgBouncer will accept.
• default_pool_size: Number of server connections to maintain per user/database pair.
• reserve_pool_size: Additional connections PgBouncer can create if default_pool_size is exhausted, before rejecting clients.
• server_idle_timeout: How long an idle server connection stays open before being closed by PgBouncer.

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
max_client_conn = 2000 ; Max connections PgBouncer will accept from clients
default_pool_size = 50 ; Default connections to DB per user/database
reserve_pool_size = 10 ; Extra connections if default is full
server_round_robin_route = 1 ; Recommended for better distribution
server_idle_timeout = 600 ; Close idle server connections after 10 min

; Logging settings
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

3. Userlist File (/etc/pgbouncer/userlist.txt)

PgBouncer needs a list of users and their password hashes to authenticate clients. These are application users, not necessarily PostgreSQL users. It also needs a user to connect to the actual PostgreSQL server for its internal authentication.

txt