In a landscape where digital demand grows relentlessly, scaling the database access layer becomes critical to maintaining a smooth user experience and controlled operational costs. At Q2BSTUDIO, as a company specialized in custom software, we faced the challenge of handling thousands of concurrent connections to PostgreSQL and found PgBouncer to be the key tool to quadruple the throughput of our infrastructure. This article details our methodology, the technical decisions we made, and how any team can replicate these results while avoiding typical bottlenecks.
PgBouncer is a lightweight, open-source connection pooler for PostgreSQL. Its primary role is to reuse database connections instead of opening and closing a new one for each application request. This drastically reduces the overhead of process creation and improves responsiveness under high concurrency. However, its default configuration does not always suit intensive traffic patterns; a fine‑tuning process was required that combined deep knowledge of PostgreSQL internals with best practices for cloud AWS/Azure.
Our first step was to establish a baseline using pgbench, a benchmarking tool included with PostgreSQL. We recorded metrics such as average latency, transactions per second (TPS), and CPU usage on the database server. Initial results showed a TPS of around 1,200 under a peak of 500 simultaneous connections, with wait times exceeding 200 ms for more than 10% of transactions. It was clear that the bottleneck was not PostgreSQL’s computing capacity but connection management.
With PgBouncer installed, we adjusted key configuration parameters. We set pool_size to 50, a value that balanced resource contention without overloading the server. max_client_connections was limited to 150 to prevent the pooler from being overwhelmed by incoming requests. idle_in_session_timeout was reduced to 30 seconds to quickly release idle connections. In addition, we enabled transaction mode (pool_mode = transaction), which allows a single connection to serve multiple transactions from different sessions, maximizing reuse.
To prevent any single user from monopolising the pool, we implemented per‑user connection limits using the pgbouncer_connection_limit plugin. This plugin allows custom quotas, which proved especially useful for clients whose applications opened many connections without closing them. The combination of these adjustments raised TPS to 2,400, doubling the initial throughput.
However, to reach the 4x target we needed horizontal scaling. We deployed multiple PgBouncer nodes behind a load balancer. Each node ran an independent instance with its own connection pool, and the balancer distributed requests according to load. This architecture allowed us to handle up to 2,000 concurrent connections without significant degradation. Continuous monitoring with Prometheus and Grafana helped us detect real‑time bottlenecks, such as memory usage on nodes or latency in the balancer.
In parallel, we integrated cybersecurity practices recommended by our cybersecurity team. For example, we restricted access to PgBouncer only through the internal network and applied mutual TLS authentication. This prevented man‑in‑the‑middle attacks and ensured that only authorised services could connect. We also implemented audit logs to detect suspicious patterns, such as anomalous spikes in failed connections.
The final result was a stable throughput of 4,800 TPS under the same load conditions, with latency kept below 50 ms at the 99th percentile. Achieving this improvement not only optimised the user experience but also reduced infrastructure costs by avoiding over‑provisioning of cloud resources. The same strategy can be applied to on‑premise or hybrid environments, provided a careful analysis of traffic patterns is performed.
At Q2BSTUDIO, this experience is part of a broader approach that combines custom software development with AI, BI/Power BI and automation technologies. For instance, the AI agents we build for predictive analytics require a fast and reliable database connection layer; a scaled PgBouncer provides that foundation. Moreover, the monitoring we implemented integrates with Power BI dashboards to offer real‑time visibility into system health.
For those considering a similar transformation, we recommend starting with a controlled pilot, measuring each change with benchmarking tools. Do not underestimate the importance of network configuration and operating‑system connection limits. And of course, having a technology partner with experience in high‑performance architectures can make all the difference. At Q2BSTUDIO we offer consulting and development services that cover everything from infrastructure design to implementing cloud AWS/Azure and cybersecurity solutions.
Scaling PgBouncer to 4x throughput is not a magic trick; it is the result of applying sound engineering principles, tuning configuration with real data, and adopting a distributed architecture. Any team facing concurrency issues with PostgreSQL can replicate this success story. The key is to understand that the pooler is not a simple proxy but an active infrastructure component that must be properly sized, monitored, and protected. If you want to explore how to implement these strategies in your own environment, our team at Q2BSTUDIO is ready to advise you.
