Scaling PgBouncer to 4x Throughput

In the context of ClickHouse Managed Postgres, a significant performance hurdle is the architecture of PgBouncer. By design, PgBouncer is single-threaded.

This means a single process is restricted to one CPU core, regardless of how many cores the underlying hardware provides. On a machine with 16 vCPUs, one core handles all connection pooling while the other fifteen remain idle. Consequently, the pooler becomes a bottleneck, capping throughput long before the Postgres database itself reaches its limit.

The Solution: A PgBouncer Fleet

To overcome this, ClickHouse Managed Postgres employs a fleet of PgBouncer processes, with the fleet size scaled proportionally to the available CPU cores.

How it Works

The architecture relies on a specific socket option: so_reuseport.

1. Every process in the fleet binds to the same port.
2. The OS kernel handles the load balancing of incoming connections across these processes.
3. Clients connect to a single endpoint, remaining unaware that multiple PgBouncer instances are operating behind the scenes.

This approach is the recommended method in PgBouncer's own documentation for utilizing multi-core systems.

---

Addressing the Technical Challenges

While the fleet approach solves the CPU bottleneck, it introduces two primary complications: Query Cancellation and Connection Budgeting.

1. The Query Cancellation Problem

In Postgres, a request to cancel a query arrives via a new connection containing a cancel key, separate from the connection executing the query.

• The Risk: Because of so_reuseport, the kernel might route this new cancellation request to a different PgBouncer process than the one managing the original session.
• The Result: The cancel request is ignored because the process doesn't recognize the query. $\rightarrow$ The Solution: The processes are peered. If a cancel request lands on the "wrong" process, it is forwarded to the process that actually owns the session.

2. Managing the Connection Budget

Since pooling is configured in transaction mode, server connections are released to the pool immediately after a transaction commits. To prevent oversubscribing the Postgres backend, the connection limits must be partitioned:

$\text{Process Limit} = \frac{\text{Total Budget}}{\text{Number of Processes}}$

Specifically, the max_client_conn and max_db_connections settings are divided across the fleet. Without this, a single process would hit its limit and throw:

FATAL: no more connections allowed (max_client_conn)

---

Real-World Performance Benchmarks

To validate this, tests were conducted on identical AWS EC2 c7i.4xlarge instances (16 vCPUs). The setup involved a dedicated box for the pooler, one for Postgres, and one for the load generator using pgbench (select-only, transaction-pooled mode).

The Comparison

We compared a single-process setup against a 16-process fleet, ramping clients from 8 to 256.

Analysis of Results

• Throughput: The single process peaked at $\approx 87\text{k}$ TPS and then degraded as contention increased. The fleet climbed to $\approx 336\text{k}$ TPS—roughly a 4x increase.
• CPU Utilization:
  • Single Process: pidstat showed one core pinned at $\sim 97\%$, while the overall 16-vCPU box stayed under $10\%$ utilization.
  • Fleet: Work was distributed across $\sim 8$ cores, with the box reaching $\sim 52\%$ utilization.
• External Metrics: AWS CloudWatch confirmed this gap, showing the single-process instance at $\sim 16\%$ CPU utilization versus $\sim 60\%$ for the fleet.

Observation: At very low connection counts, the single process is slightly faster because there is no need for parallelization and fleet connections are too sparse. However, under real-world concurrency, the single core becomes a "wall."

---

Final Takeaways

A standard PgBouncer setup is sufficient until the pooler—rather than the database—becomes the bottleneck. By implementing a fleet strategy, you can transform the pooler from a bottleneck back into simple plumbing.

Implementation Checklist

• Scale process count to match CPU cores.
• Enable so_reuseport for port sharing.
• Configure process peering for query cancellation.
• Divide max_client_conn and max_db_connections across the fleet.

This architecture is enabled by default for every ClickHouse Managed Postgres server.

---

Ready to scale? ClickHouse + Postgres provides a unified data stack for high-scale applications. Get started with ClickHouse Cloud today and receive $300 in free credits.