Database connection poolers are useful tools that enable organizations to deliver high-performing applications by efficiently utilizing Postgres. Implementing a high-quality connection pooler is a frequent request of clients using Postgres, and while there may only be a few solutions equipped to meet their needs, determining the best choice isn’t always straightforward. Two of the most well-known database connection poolers are Pgpool-II and PgBouncer. Each option adds functionality to a database’s connection facilities that can be used natively, but each tool includes its own particular strengths and weaknesses.
What to Expect From a Pooling Solution
In typical scenarios, PgBouncer executes pooling correctly “out of the box,” whereas Pgpool-II requires fine-tuning of certain parameters for ideal performance and functionality. Both PgBouncer and Pgpool-II can bring down connections and reconnections to Postgres. For application platforms lacking this functionality natively, a connection pooler adds such capabilities directly into the application layer to enable efficient database connections. To picture a proper implementation of a connection pooler, imagine a client with one master database and five standby databases. For this example, the workload is mostly read-only and requires significant resources. PgBouncer and Pgpool-II have the capability to manage thousands of connections, and even when less than 100 are in use, it can keep track of the other 900 while maintaining high performance.
The Right Tool for the Right Job
In an ideal PgBouncer scenario, consider an application landscape where lots of small pieces of applications make connections to Postgres. Only small amounts of the applications are used at any given time, and applications open connections that aren’t always active or in use. PgBouncer is a good match for this case because it excels at pooling down from 1,000 database connections to 100, which can save serious resources in Postgres. Pgpool-II is often implemented by organizations because of its added capabilities, but that doesn’t necessarily make Pgpool-II the ideal choice for all use cases. Many perceive Pgpool-II as an end-all solution, but in reality, PgBouncer is often a better solution for scenarios where bringing down database connections is key.
Deploy Solutions That Solve
The wrong implementation of Pgpool-II can lead to scenarios in which databases become marred with performance problems. In this case, the Pgpool-II “solution” often fares worse than the initial problem. Likewise, PgBouncer may be an efficient, stripped connection pooler, but it lacks functionalities that might be compelling to your typical scenarios, like being able to run read-only queries on standby.
Before you make the move to implementing a connection pooler, run through database connection situations and read-only query scenarios to determine whether you need read-only functionality or would reap greater benefits from pooling down connections.
For bringing down database connections, PgBouncer is the most powerful solution — at the expense of leveraging standby resources. If you require both scenarios, Pgpool-II might be the better alternative, but proper configuration, tuning, and testing will be required.
When choosing a connection pooler, there is no silver bullet: Deciding between Pgpool-II and PgBouncer depends entirely on what your organization is trying to do. Although Pgpool-II and PgBouncer can be used in conjunction, stacking both solutions adds extra latency, creating a circumstance where you are likely worse off than if you had implemented just one of the two. Organizations must ultimately choose the solution that makes the most overall sense.
The Bottom Line?
Spend some time upfront to consider the exact circumstances in which a connection pooler will be implemented and your organization can spare itself from related headaches down the road. For additional help with fine tuning Postgres, connect with EnterpriseDB’s Postgres experts to ensure your databases run at their maximum efficiency.