How we scale PgBouncer in ClickHouse Managed Postgres

Summary

PgBouncer is single-threaded, so a single process can bottleneck connection pooling on multi-vCPU machines even when Postgres has capacity. ClickHouse Managed Postgres runs a fleet of PgBouncer processes sized to available cores, sharing one port through so_reuseport; peering forwards query-cancellation requests to the process that owns the session, while transaction pooling and per-process connection-budget splits limit Postgres connections. In tests on identical AWS EC2 c7i.4xlarge pooler instances, a 16-process fleet reached roughly 336,000 transactions per second at 256 clients, compared with 76,893 for one process, while using about 49% versus 8% in-guest CPU. The fleet's advantage emerged under concurrency, whereas the single process was slightly faster at eight clients, and the described setup ships by default with every ClickHouse Managed Postgres server.

Takeaways

1. 1

   so_reuseport lets multiple PgBouncer processes share one endpoint while the kernel distributes incoming connections across them, allowing pooling work to use multiple CPU cores.

2. 2

   PgBouncer peering forwards a query-cancellation request when it reaches a process that does not own the target session, preserving cancellation across the fleet.

3. 3

   At 256 clients, the 16-process fleet delivered 336,469 transactions per second versus 76,893 for one process, with measured pooler-box CPU of 48.9% versus 7.7%.