Scaling PgBouncer Beyond the Single-Threaded Limit

PostgreSQL relies on a process-per-connection architecture. Every client connection forks a dedicated operating system process. This design is simple and isolated, but it scales poorly. Each process consumes between 5MB and 400MB of resident set size (RSS) memory, often exacerbated by glibc's memory allocator retaining freed heap memory rather than returning it to the OS.

Beyond memory consumption, high connection counts degrade database performance through internal lock contention. On every transaction start, Postgres must scan its internal snapshot mechanism, the ProcArray, to determine multi-version concurrency control (MVCC) visibility. This scan time grows with the connection count. Benchmarks show that scaling from 50 to 1,000 direct connections can degrade throughput from 18,000 transactions per second (TPS) to just 2,500 TPS, a 7x performance drop.

To mitigate this, developers use PgBouncer, a lightweight connection pooler that multiplexes thousands of client connections onto a small pool of heavy Postgres processes. However, PgBouncer has its own scaling wall: it is single-threaded.

The Single-Threaded Bottleneck

PgBouncer runs a single-threaded event loop. It uses libevent (which maps to epoll on Linux) to handle thousands of concurrent sockets without thread-switching overhead. This works exceptionally well until the throughput or encryption overhead saturates that single CPU core.

In modern production environments, SSL/TLS encryption is standard. Profiling a saturated PgBouncer instance often reveals that the process spends most of its CPU time copying data in and out of SSL buffers rather than routing queries. Once that single core hits 100% utilization, application query latency spikes, even if the underlying Postgres database is completely idle.

If you deploy PgBouncer on a modern multi-core virtual machine, such as a 16-vCPU instance, a single PgBouncer process will pin one core at 97% while the other 15 cores sit idle. The overall system utilization remains under 10%, yet the pooler bottlenecks your entire application.

Multi-Process Scaling with SO_REUSEPORT

To utilize all available CPU cores, you must run multiple PgBouncer processes. Since version 1.12, PgBouncer has supported the Linux socket option SO_REUSEPORT. This option allows multiple independent processes to bind to the exact same TCP port.

When multiple PgBouncer processes listen on the same port, the Linux kernel load-balances incoming TCP connections across the active processes. To the application, there is still only a single endpoint (typically port 6432), but behind the scenes, a fleet of PgBouncer processes shares the load.

flowchart TD
    App[Application Clients] -->|Port 6432| Kernel[Linux Kernel SO_REUSEPORT]
    Kernel -->|Load Balance| PB1[PgBouncer Process 1]
    Kernel -->|Load Balance| PB2[PgBouncer Process 2]
    Kernel -->|Load Balance| PB3[PgBouncer Process 3]
    PB1 --> PG[PostgreSQL Database]
    PB2 --> PG
    PB3 --> PG

This architecture allows throughput to scale linearly with the number of cores allocated to the PgBouncer fleet. However, running independent poolers on a shared port introduces a critical failure mode: broken query cancellations.

The Query Cancellation Catch

When an application issues a query cancellation request in Postgres, it does not send the cancel signal over the existing connection. Instead, the client opens a brand-new TCP connection containing a secret cancel key and the backend process ID (PID) of the target query.

Under a single PgBouncer architecture, this works fine. The cancel request lands on the same PgBouncer process, which matches the key and forwards the cancel signal to the database.

With SO_REUSEPORT enabled, the kernel treats the cancel request as a new connection and is free to route it to any PgBouncer process in the fleet. If Process B receives a cancellation request for a query running on a session held by Process A, Process B has no record of that session. The cancellation request is ignored, and the long-running query continues to execute on the database, blocking resources.

To fix this, you must configure PgBouncer peering. Peering allows the individual PgBouncer processes to communicate with one another over a Unix domain socket or a private TCP port. When Process B receives a cancel request that it does not own, it checks its peer table, identifies that Process A owns the session, and forwards the cancellation request to Process A.

The Developer's Playbook: Configuration and Math

Implementing a multi-process PgBouncer fleet requires careful configuration. You cannot simply run 16 instances with your old single-instance configuration file, or you will oversubscribe your database connections.

1. Splitting the Connection Budget

PgBouncer enforces limits on client connections (max_client_conn) and database connections (max_db_connections). When running a fleet of size $N$, you must divide these limits by $N$ across your configuration files.

If your Postgres instance has max_connections = 400, and you run a fleet of 16 PgBouncer processes, each process must be configured with:

# pgbouncer.ini (Process 1 of 16)
max_db_connections = 25
max_client_conn = 312

If you fail to divide these limits, and all 16 processes attempt to scale up to 400 database connections during a traffic spike, they will attempt to open 6,400 connections to Postgres. This will exhaust the database's connection limit, causing fatal connection errors.

2. Configuring Peering

To enable peering, you must define a peer section in your PgBouncer configuration. Each process must know the address of its peers. This is typically managed via templated configuration files or environment variables in containerized environments.

[peers]
pgbouncer_1 = host=127.0.0.1 port=6433
pgbouncer_2 = host=127.0.0.1 port=6434
pgbouncer_3 = host=127.0.0.1 port=6435

3. Performance Comparison

When properly configured, the performance difference is stark. In benchmark tests running on an AWS c7i.4xlarge (16 vCPUs) against a dedicated Postgres instance, a single PgBouncer process peaks at roughly 87,000 TPS under a load of 64 clients. Beyond that, CPU contention on the single core causes throughput to degrade to 77,000 TPS at 256 clients.

By contrast, a fleet of 16 PgBouncer processes utilizing SO_REUSEPORT and peering scales to over 336,000 TPS at 256 clients, a 4x increase in throughput that fully utilizes the available hardware.

xychart-beta
    title "PgBouncer Throughput: Single vs. 16-Process Fleet (TPS)"
    x-axis [8, 32, 64, 128, 256]
    y-axis "Transactions Per Second" 0 --> 350000
    line [8910, 54203, 86570, 83463, 76893]
    line [6450, 64244, 219439, 320547, 336469]

At very low concurrency (e.g., 8 clients), the single-process configuration is slightly faster because it avoids the minor overhead of kernel-level load balancing and connection distribution. However, as concurrency scales, the single-process bottleneck becomes a liability.

When to Transition

You do not need a multi-process PgBouncer fleet on day one. A single instance can comfortably handle up to 10,000 idle connections and roughly 1,000 active connections depending on query size and SSL overhead.

However, you should plan to migrate to a multi-process architecture when you observe any of the following symptoms:

• PgBouncer's CPU usage consistently hits 100% on a single core.
• Application query wait times increase while the underlying Postgres CPU and disk I/O remain low.
• There is a mismatch between active connections reported by PgBouncer's internal console and those reported by Postgres's pg_stat_activity view.

By deploying a multi-process fleet, sharing a port via SO_REUSEPORT, and linking the processes with peering, you eliminate the pooler as a single-threaded bottleneck and allow your database infrastructure to scale with your hardware.