PgBouncer scaling: Real load tradeoffs and lessons learned

100,000 QPS: My first PgBouncer wall

PgBouncer, often presented as a simple drop-in solution, became our primary bottleneck at 100,000 queries per second. We deployed it years prior, assuming its pool_mode = session configuration was sufficient. This mode simplifies application logic by guaranteeing a dedicated server connection for the duration of a client session, effectively hiding connection churn. For lower traffic, this assumption held.

As our transaction volume grew, Postgres itself showed headroom. CPU usage was stable, disk I/O was manageable, and query latencies remained low directly on the database. However, application-side latencies began to spike unpredictably. Connections started timing out, and error rates climbed. Our monitoring showed a clear divergence: Postgres was fine, but the applications were struggling to connect.

The PgBouncer logs quickly revealed the problem. Lines like waiting for server connection dominated the output. A SHOW POOLS command run against PgBouncer’s admin console confirmed our fears:

SHOW POOLS;

database   user   cl_active   cl_waiting   sv_active   sv_idle   sv_used   sv_login   max_wait   pool_mode
-------- -------- --------- ---------- --------- ------- ------- -------- -------- ---------
app_db     app_user     500        800         50        0        0        0       12000 session

The cl_waiting column, showing 800 clients queued, was the smoking gun. Our sv_active connections were maxed out at 50, even though Postgres could easily handle hundreds more. The session pooling mode, while convenient, held server connections open even when the client was briefly idle. This meant 50 application clients could monopolize the entire pool, blocking hundreds of others.

The tradeoff was stark: application code simplicity came at the cost of severe connection contention and throughput limits. We had designed our application to open a database connection at the start of a request and hold it until the request finished, a pattern perfectly compatible with session mode. This approach, however, failed spectacularly under high concurrency. We faced a choice: either drastically reduce our application’s connection holding time or switch to a different pooling mode. The immediate, paralyzing bottleneck PgBouncer introduced demanded a radical rethinking of our connection pooling strategy beyond the default.

PgBouncer’s hidden bottlenecks

PgBouncer often gets deployed as a simple connection multiplexer, but its internal architecture hides several scaling limitations that surfaced during our high-throughput tests. We initially assumed it would just handle more clients; the reality was more complex.

Our first bottleneck appeared with connection modes. We started with session pooling, which keeps a server connection open for the entire client session. This offers no real connection reuse between client sessions; throughput was limited to active client sessions as server connections only returned to the pool upon client disconnect.

Switching to transaction pooling drastically improved backend connection reuse. PgBouncer releases the server connection to the pool after each transaction completes, providing true pooling. Many short-lived client transactions can then share fewer backend connections. The tradeoff: applications cannot rely on session-specific state persisting across transactions. Commands like SET search_path = ... or PREPARE must be re-issued or handled carefully. Our application had to adapt.

statement pooling offers the highest reuse, releasing the server connection after every statement. This mode is useful only for applications that run single statements and never rely on any session state at all. We found it too restrictive for most of our services.

Beyond connection modes, PgBouncer’s single-process, event-driven design means a single CPU core can become a bottleneck. While it handles many idle connections efficiently, processing high volumes of active queries can max out a core. We observed this on systems with many cores but limited per-core performance.

File descriptor limits were another common trap. Each client and server connection consumes a file descriptor. With max_client_conn at 10,000 and default_pool_size at 100, PgBouncer needs over 10,100 descriptors. The default ulimit -n (often 1024) is quickly exceeded. We had to raise this system-wide and in PgBouncer’s service definition.

A common misconfiguration was server_reset_query. In transaction mode, session variables (e.g., SET application_name) persist on the backend connection. Without a server_reset_query to clear this state, subsequent clients might inherit incorrect settings. Our pgbouncer.ini now includes server_reset_query = RESET ALL; DISCARD ALL; to ensure a clean state for every new transaction.

; pgbouncer.ini snippet
[databases]
mydb = host=db.example.com port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
server_reset_query = RESET ALL; DISCARD ALL;

Another pitfall was the interplay between max_client_conn and default_pool_size. Setting max_client_conn very high, say 20,000, while default_pool_size was only 500, meant 19,500 clients could be waiting for a backend connection. This created long queues and apparent application slowness, even if the database itself had capacity. We learned to size default_pool_size based on actual backend capacity and expected concurrent active transactions, not just the total number of application instances.

Optimizing PgBouncer: Beyond the defaults

PgBouncer’s default configuration quickly becomes a bottleneck under significant client load. I found that achieving high throughput required moving past the standard settings, directly addressing connection pooling, kernel limits, and a deeper understanding of internal metrics.

My initial deployments showed cl_waiting counts soaring even with modest client concurrency. The pool_size parameter, often left at its default of 10, was the first limit. Increasing pool_size to 100 per database on our primary PgBouncer instance allowed 10x more client queries to be processed concurrently by the PostgreSQL backend. This change, however, directly increased the active connection count on the PostgreSQL server, demanding more memory and CPU there. We also set reserve_pool_size to 5 to handle connection spikes without immediately rejecting clients.

; pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb pool_size=100 reserve_pool_size=5

[pgbouncer]
max_client_conn = 10000
listen_backlog = 4096
server_lifetime = 3600
server_idle_timeout = 600

The max_client_conn setting, which defaults to 100, needed a substantial increase to 10000 to accommodate our application’s connection patterns. Without this, PgBouncer would simply drop new client connections once the limit was reached. Similarly, listen_backlog, which controls the queue size for pending client connections, required an increase to 4096 to prevent connection rejections during sudden load spikes.

Kernel-level adjustments were also necessary. The default net.core.somaxconn value of 128 on Linux often proved insufficient for a listen_backlog of 4096. We changed this to match:

sysctl -w net.core.somaxconn=4096

This prevents the kernel from silently dropping connections before PgBouncer can accept them. We also increased the open file descriptor limit (ulimit -n) for the PgBouncer process to 65536, as each client and server connection consumes a file descriptor.

Monitoring PgBouncer’s SHOW STATS and SHOW POOLS output was key to identifying these bottlenecks. A high cl_waiting count indicated insufficient pool_size, while client_connections nearing max_client_conn signaled a need to increase that limit. On the PostgreSQL side, pg_stat_activity showed the actual connection load, allowing us to balance PgBouncer’s pool sizes against the database’s capacity. These tuning efforts transformed PgBouncer from a point of contention into a stable, high-throughput connection multiplexer, but required a deeper understanding of its interaction with both the application and the operating system.

The price of performance: Tradeoffs in scaling

Scaling PgBouncer for high throughput never comes free. We accepted specific compromises across our stack to achieve our targets, primarily impacting latency, resource consumption, and operational complexity.

Our primary goal was to offload connection churn from PostgreSQL. This meant adopting pool_mode = transaction. While this mode significantly improved backend database stability under heavy load, it introduced a measurable latency overhead. Each transaction now incurred the cost of acquiring and releasing a connection from PgBouncer’s pool. For our high-volume API calls, this translated to an increase of 1-2ms in P99 response times. We traded individual transaction speed for overall system throughput and PostgreSQL resource stability.

Running PgBouncer instances requires dedicated compute resources. We provisioned m5.large EC2 instances for each PgBouncer node, separate from our application and database servers. These instances consumed CPU for connection management and memory to hold connection states, especially with thousands of active client connections. This added to our infrastructure budget. We accepted the increased cloud spend on these intermediary nodes as a necessary cost to protect the PostgreSQL primary from CPU spikes related to new connection establishment.

Introducing PgBouncer added a new layer to our observability and incident response. We now monitor PgBouncer’s internal state using commands like SHOW STATS;, feeding metrics into Prometheus.

SHOW STATS;

databasename|total_xact_count|total_query_count|total_xact_time|total_query_time|avg_xact_time|avg_query_time
------------+------------------+-----------------+---------------+----------------+-------------+--------------
mydb        |123M              |456M             |1234s          |5678s           |0.5ms        |1.2ms

Configuration tuning, like max_client_conn and default_pool_size, became an ongoing task, directly impacting application behavior. Troubleshooting connection issues now involved checking PgBouncer logs and metrics alongside application and database logs. This added complexity to our incident resolution playbooks. The initial setup involved deploying multiple PgBouncer instances behind an internal load balancer to ensure availability. This architectural decision complicated deployment pipelines and required additional automation for configuration synchronization. We accepted this operational overhead to gain the connection pooling benefits.

The gains in PostgreSQL stability and application throughput justified these costs. The system now handles bursts of 50,000 active client connections, a scenario that previously overwhelmed our database.

PgBouncer’s place in our high-throughput stack

PgBouncer is not a universal solution for database connection management; its value in our high-throughput stack is sharply defined by specific use cases. We employ it as an essential component only for services that exhibit a high volume of short-lived, transactional queries, primarily operating with pool_mode = transaction. This configuration drastically reduces the connection overhead on our PostgreSQL backend, allowing the database to focus its resources on query execution rather than connection establishment and teardown.

The primary tradeoff with transaction pooling is the loss of session state across transactions. Features like prepared statements, advisory locks, or temporary tables, which rely on a persistent session context, become problematic or outright unusable. For example, a client attempting PREPARE my_query AS SELECT 1; followed by EXECUTE my_query; might find my_query unavailable in the subsequent transaction if the connection was returned to the pool and re-assigned. This forces application developers to write stateless database interactions, which aligns well with microservice principles but requires strict discipline.

We configure PgBouncer instances with parameters like max_client_conn = 1000 and default_pool_size = 20 per database, tuned to match the expected client load and backend capacity. This ensures that while many application instances can connect to PgBouncer, the actual number of concurrent connections hitting PostgreSQL remains predictable and manageable.

; pgbouncer.ini snippet
[databases]
my_app_db = host=db.example.com port=5432 dbname=my_app_db

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
server_idle_timeout = 60

Deploying PgBouncer introduces an additional network hop and a potential point of failure if not managed with high availability. This extra latency, though minimal (typically <1ms), is a cost that must be weighed against the benefits of reduced backend load. For applications that maintain long-lived connections or rely heavily on session-specific features, PgBouncer’s overhead and restrictions often outweigh its pooling advantages.

My position is clear: PgBouncer is a targeted optimization tool. It is indispensable for scaling stateless, high-concurrency workloads where database connection churn is the bottleneck. However, it is detrimental for applications requiring complex session semantics or where the added layer of indirection complicates debugging more than it improves performance. We reserve its use for services explicitly designed to operate within the constraints of transaction pooling, where its impact on throughput is significant and measurable.