PgBouncer: from 45K to 210K queries per second

I wanted to see how much throughput I could gain by adding PgBouncer processes when one process filled a CPU core and PostgreSQL still had capacity.

I kept the combined PostgreSQL connection budget fixed at 128. Otherwise, adding PgBouncer processes could also add more database connections, making it harder to tell where the improvement came from.

I ran 1, 2, 4, and 8 PgBouncer processes on the same port using SO_REUSEPORT.

One process stopped at 45K queries per second while PostgreSQL was only at 22% CPU. Eight processes reached 210K queries per second. By then, PostgreSQL was at 97% CPU and had become the next bottleneck.

Setup

I ran the experiment on AWS EC2 in us-east-1. All measured hosts were in the same availability zone.

The software and workload configuration was:

The load generator issued prepared point lookups:

SELECT id, tenant_id, created_at, event_type
FROM events
WHERE id = $1;

The table and indexes were warmed before the measured runs. PostgreSQL reported a 100% buffer-hit ratio and no additional block reads during the main measurements, so storage was not limiting this workload.

Each PgBouncer process was pinned to a separate CPU and listened on port 6432. I divided the pool budget across the active processes:

Copying a pool size of 128 into eight configurations would create a theoretical budget of 1,024 PostgreSQL connections. Any throughput increase would then mix proxy parallelism with higher database concurrency.

The maximum observed PostgreSQL connection count stayed at 137 in every main configuration. That included the 128 application connections plus monitoring and administration connections.

Results with 256 clients

This was the result with 256 clients:

With one process, PgBouncer used one complete CPU core while PostgreSQL used 22% of its host CPU. The API load generator was around 5% CPU, CPU steal was negligible, and the database was serving the workload from memory. The full PgBouncer core was the first clear limit.

With two processes, throughput increased by 1.79x. Both processes used a full core, and PostgreSQL was still at 44% CPU.

Four processes reached 153K queries per second, 3.40x the single-process result. All four PgBouncer cores were busy, but PostgreSQL had reached 87% CPU. The system was already moving away from a proxy-only bottleneck.

At eight processes, throughput reached 210K queries per second. PostgreSQL was now at 97% CPU, and the PgBouncer processes used 6.27 cores in total rather than eight. The remaining PgBouncer capacity could not stay busy because the database had become saturated.

Adding processes helped most while PostgreSQL had spare CPU. The gains tapered as the database approached its limit.

Increasing the client count

I repeated the same process-count sweep with 1,024 persistent clients.

The throughput ceiling barely changed. Eight processes completed 209,779 queries per second with 256 clients and 208,621 with 1,024 clients.

The additional clients mostly increased waiting time. With one process, p99 rose from 8.19 ms at 256 clients to 33.12 ms at 1,024 clients while throughput fell slightly. The process was already using its core, so a larger client queue could not create more capacity.

These were closed-loop saturation runs. Each client sent its next query after the previous query completed. That model is useful for finding the throughput ceiling, but it does not compare latency at one fixed offered rate. I treat the p99 values as observed saturation behavior rather than a production latency claim.

Direct PostgreSQL baseline

I ran the same point-query workload directly against PostgreSQL to check how much database capacity existed without PgBouncer in the path.

These were single baseline runs, and the direct path is not identical to the PgBouncer path. The proxy adds another network hop, transaction pooling, and TLS on both sides. Even with that caveat, the baseline shows that PostgreSQL had much more point-query capacity than the 45K queries per second delivered through one PgBouncer process.

In my PostgreSQL bulk-ingestion benchmark, client round trips and per-row writes accounted for much of the delay. That workload needed a different change.

TLS cost on one process

I separated client-side and server-side TLS while keeping one PgBouncer process. These were single runs.

The PgBouncer process used a full core in every configuration. TLS changed how many queries that core could complete. Server-side TLS had the larger cost in this setup, and enabling TLS on both sides reduced throughput by about 35% compared with the no-TLS run.

Each TLS configuration had only one run. I'd repeat this test on the target hardware before using these percentages to plan capacity.

Shared and isolated export traffic

The mixed test combined API traffic at 8,000 requests per second with four concurrent export streams. Both topologies used four PgBouncer processes and the same total PostgreSQL pool budget.

In the shared topology, API and export connections could use all four processes. In the isolated topology, three processes handled API traffic and one process was reserved for exports.

Isolation did not help at this offered API rate. The API workload was far below its available capacity, so there was little contention to protect it from. Meanwhile, the isolated export process filled one core and stopped around 582 MiB/s. The shared export connections were distributed across several processes and reached about 992 MiB/s.

At this API rate, reserving a process for exports reduced the CPU they could use without improving API latency. A busier API workload could produce a different result.

Cancellation requires peering

Multiple processes sharing one port also affect PostgreSQL query cancellation. I sent 100 cancellation attempts through three configurations:

Without peering, 23% of the cancellations succeeded. That is consistent with a cancellation request occasionally reaching the process that owned the original client connection. After enabling PgBouncer peering, all 100 attempts succeeded.

I'd configure peering before using this multi-process setup. Without it, most of the cancellation attempts in this test failed.

Measurement notes

The main scaling results use the median of three randomized repeats. Throughput was stable compared with the size of the process-count effect. The 256-client, two-process configuration had the highest throughput coefficient of variation at about 3.2%; the other main throughput groups stayed below 3%.

Tail latency varied more than throughput in several configurations, which is another reason not to present the p99 values as precise production SLO measurements.

The CPU figures in this post were calculated over the actual 60-second load interval. Including idle collector time before and after the load would understate the CPU used by a saturated process.

Limits of this experiment

This was a memory-resident point-query workload that saturated one PgBouncer process before PostgreSQL. A workload limited by disk, locks, or database CPU may need a different change. The 4.65x speedup and eight-process configuration are specific to this test.

The latency figures describe closed-loop saturation, and the TLS and isolation runs cover the configurations above. They don't establish production latency targets or rule out isolation for other workloads.

Code

The infrastructure, load generator, experiment runner, raw results, and analysis code are available on GitHub ↗.

The repository includes Docker Compose for local testing, Terraform and Ansible for the AWS environment, the Rust load generator, all experiment matrices, raw per-run metrics, validation, charts, and summary tables.

What I'd check on another system

I'd start with CPU usage per process. If one PgBouncer process fills a core while PostgreSQL has spare capacity, I'd test multiple processes on one port. I'd divide the connection budget across them and enable peering for cancellation.

In this test, that took throughput from 45K to 210K queries per second. By then, PostgreSQL was using 97% CPU, so adding more proxy capacity was no longer the next step.