Introduction
PgBouncer is an essential tool for anyone using PostgreSQL at scale, but it has a major limitation: it's single-threaded. In a world where multi-core servers are standard, leveraging all available processing power is crucial. Yet, on a server with 16 vCPUs, only one core is used for connection pooling, leaving the others almost untouched.
At ClickHouse Managed Postgres, we found a way to circumvent this limitation, enabling us to quadruple PgBouncer's throughput. This article will show you how we achieved this feat.
The Single-Threading Problem
PgBouncer, by design, uses a single thread per process. This means only one core is utilized, even if several are available. On a typical server, this creates a bottleneck long before PostgreSQL reaches its maximum capacity.
On a server with 16 vCPUs, only a fraction of resources is exploited, thus limiting the number of concurrent connections and overall throughput. The question is: how can we effectively use all those unused cores?
The Architectural Solution
The key lies in running multiple PgBouncer processes in parallel. Each process is bound to the same port using the so_reuseport option, allowing the kernel to distribute incoming connections across different processes. This means that even though PgBouncer is single-threaded, each process can be assigned to a different core, thus maximizing resource utilization.
The Challenges of Query Cancellation
One of the main challenges of this approach is handling query cancellations. In PostgreSQL, a cancel request arrives on a new connection with a cancel key, separate from the connection running the query. With so_reuseport, the kernel can assign this new connection to a different process than the one handling the initial session. This means the cancellation might never reach the target query.
To solve this problem, we implemented a mechanism that ensures cancellations are handled correctly, making sure each process can communicate effectively to re-route cancellation requests to the appropriate place.
Results and Performance
Through this architecture, we managed to quadruple PgBouncer's throughput. By distributing connections across multiple processes and utilizing the full processing power of modern servers, we optimized our connection management efficiency.
Not only does this improve performance, but it also reduces response times for end-users, thus enhancing the overall experience.
Conclusion
With this solution, ClickHouse Managed Postgres demonstrated that it's possible to overcome PgBouncer's inherent limitations to achieve maximum efficiency. If you're looking to enhance your PostgreSQL database performance, consider adopting a similar approach.
Let's discuss your project in 15 minutes.