We scaled PgBouncer to 4x throughput

In the fast-paced world of financial technology (FinTech), performance isn't just a nice-to-have – it's a critical differentiator. Slow transaction processing, laggy reporting, or unreliable data access can directly impact user experience, trading efficiency, and ultimately, the bottom line. At [Your Company Name], we experienced this firsthand as our platform grew and the load on our PostgreSQL databases increased. We heavily rely on PostgreSQL for its robustness, data integrity, and advanced features. However, managing a large number of concurrent connections to the database became a significant bottleneck. This led us to dive deep into optimizing our PostgreSQL connection pooling strategy, specifically focusing on PgBouncer. This article details how we scaled PgBouncer to achieve a 4x increase in throughput, the challenges we faced, and the lessons we learned.

§The Problem: PostgreSQL Connection Limits & the Need for Pooling

PostgreSQL, like many relational databases, has a limit on the number of concurrent connections it can handle efficiently. Each connection consumes resources – memory, CPU, and file descriptors – on the database server. As the number of users and applications accessing the database grows, hitting this connection limit can cause serious problems:

• Connection Refusals: New connection attempts are rejected, leading to application errors.
• Performance Degradation: The database server becomes overloaded, slowing down all queries.
• Increased Latency: Transaction times increase, impacting the user experience.

Our financial platform, handling a growing volume of real-time trades and complex financial calculations, was starting to feel these effects. We were reaching the point where adding more PostgreSQL instances wasn’t a sustainable solution. The overhead of managing a larger cluster of databases, coupled with the complexity of data synchronization, outweighed the benefits. We needed a smarter approach.

That's where PgBouncer came in.

§What is PgBouncer and Why Did We Choose It?

PgBouncer is a lightweight connection pooler for PostgreSQL. It sits between your applications and the database server, managing a pool of connections. Instead of each application establishing its own direct connection to PostgreSQL, applications connect to PgBouncer, which then reuses existing connections to the database.

§Here’s how it works:

1. An application requests a connection to the database through PgBouncer.
2. PgBouncer checks if an idle connection is available in the pool.
3. If a connection is available, PgBouncer reuses it.
4. If no idle connection is available and the pool isn’t full, PgBouncer creates a new connection.
5. If the pool is full, the application waits for an idle connection to become available (or times out).

§We chose PgBouncer for several reasons:

• Lightweight: Minimal overhead compared to other connection pooling solutions.
• Easy to Configure: Relatively simple to set up and manage.
• Cost-Effective: Open-source and free to use.
• Performance Benefits: Significant reduction in connection overhead and improved response times.
• Transaction Pooling: PgBouncer's transaction pooling mode offers substantial advantages in environments with short-lived transactions, common in financial applications.

§Our Initial PgBouncer Configuration and Baseline Performance

We started with a standard PgBouncer configuration, using the default settings and a reasonably sized connection pool. This provided some initial improvements, but we quickly realized that the default configuration was far from optimal for our specific workload. We used a single PgBouncer instance in front of our primary PostgreSQL database cluster.

§Our baseline measurements showed:

• Throughput: 1000 transactions per second (TPS)
• Average Latency: 200ms
• Connection Pool Size: 100 connections
• Database CPU Utilization: 70%

While these numbers weren't bad, they were limiting our growth. We needed to push the boundaries and unlock PgBouncer's full potential. We used tools like pgbench and custom monitoring scripts to establish our baseline and track improvements. You can find good benchmarking resources online like https://example.com/ to help you understand performance metrics.

§Scaling PgBouncer: The Optimization Journey

Achieving a 4x throughput increase required a systematic approach. We focused on several key areas:

§1. Tuning pgbouncer.ini Configuration

The pgbouncer.ini file is the heart of PgBouncer configuration. We experimented with numerous parameters, carefully monitoring the impact on performance. Here are some of the most important settings we adjusted:

• pool_mode: We switched from session to transaction pooling. Transaction pooling is ideal for short-lived transactions, reusing connections for multiple transactions within the same application session. This significantly reduced connection overhead.
• max_client_conn: We increased the maximum number of client connections to 500. Carefully monitoring database resource usage is crucial when increasing this value.
• default_pool_size: We increased the default pool size per database/user to 50. This provided a larger pool of readily available connections.
• server_idle_timeout: Decreased the server idle timeout to 30 seconds. This released unused connections more quickly, optimizing pool utilization.
• client_idle_timeout: Decreased the client idle timeout to 10 seconds. This ensures that connections are returned to the pool promptly when no longer needed.
• recv_timeout & send_timeout: Fine-tuned these parameters to prevent connection stalls.

§2. Hardware Optimization & Resource Allocation

We realized that our PgBouncer instance was running on under-provisioned hardware. We migrated it to a larger virtual machine with:

• More CPU: Increased from 4 cores to 8 cores.
• More Memory: Increased from 8GB to 32GB.
• Faster Storage: Upgraded to SSD storage for improved I/O performance.

This provided PgBouncer with the resources it needed to handle a larger connection pool and higher throughput.

§3. Connection Management in Applications

While PgBouncer handles the connection pooling, our applications played a role in optimizing connection usage. We:

• Implemented Connection Reuse: Ensured that applications were reusing connections whenever possible instead of creating new ones for each transaction.
• Proper Connection Handling: Implemented robust error handling to ensure that connections were always properly closed and returned to the pool.
• Optimized Queries: Reviewed and optimized slow-running queries to reduce database load and improve response times. Using tools to profile queries is essential.

§4. Monitoring & Alerting

Continuous monitoring is crucial for maintaining optimal performance. We implemented a comprehensive monitoring system that tracked:

• PgBouncer Connection Pool Statistics: Number of active connections, idle connections, waiting clients.
• Database CPU Utilization: Identifying bottlenecks on the database server.
• Query Latency: Monitoring the performance of individual queries.
• Error Rates: Detecting and resolving connection errors.

We set up alerts to notify us of any performance degradation or potential issues. Tools like Prometheus and Grafana were instrumental in visualizing our metrics.

§Results: 4x Throughput Achieved!

After implementing these optimizations, we re-ran our benchmarks. The results were dramatic:

§| Metric | Before Optimization | After Optimization |

|--------------------------|----------------------|---------------------| | Throughput (TPS) | 1000 | 4000 | | Average Latency (ms) | 200 | 50 | | Connection Pool Size | 100 | 500 | | Database CPU Utilization | 70% | 60% |

We successfully increased throughput by 4x while simultaneously reducing database CPU utilization! The lower latency also resulted in a significantly improved user experience. This demonstrated the power of effectively tuned connection pooling.

§Lessons Learned

§Scaling PgBouncer for our financial platform taught us several valuable lessons:

• Configuration Matters: The default PgBouncer configuration is a starting point, not an end goal. Thorough testing and tuning are essential.
• Hardware Plays a Role: Ensure that PgBouncer has sufficient resources to handle the expected load.
• Application Collaboration: Optimize application code to reuse connections effectively.
• Monitoring is Key: Continuous monitoring is crucial for identifying and resolving performance issues.
• Transaction Pooling is Powerful: For short-lived transactions, transaction pooling provides significant performance benefits.

§Future Considerations

§We are continuing to explore further optimizations, including:

• PgBouncer Clustering: Implementing a PgBouncer cluster for high availability and scalability.
• Automated Configuration Management: Using tools like Ansible to automate PgBouncer configuration and deployment.
• Advanced Monitoring: Integrating machine learning algorithms to predict and prevent performance issues.

§Disclaimer

Please note that some links in this article are affiliate links. This means that if you click on the link and make a purchase, we may receive a small commission at no extra cost to you. This helps us to continue providing valuable content. We only recommend products and services that we believe in and that are relevant to our audience.

§Image suggestions:

• A diagram illustrating how PgBouncer sits between applications and the PostgreSQL database. (
• A graph showing the throughput improvement before and after optimization. (
• A screenshot of a PgBouncer monitoring dashboard. (
• An image of a server rack symbolizing the hardware upgrades. (
• An image of code snippets demonstrating connection reuse in an application. (