In our previous posts in this series, we spoke at length about using PgBouncer and Pgpool-II, the connection pool architecture and pros and cons of leveraging one for your PostgreSQL deployment. In our final post, we will put them head-to-head in a detailed feature comparison and compare the results of PgBouncer vs. Pgpool-II performance for your PostgreSQL hosting!

PostgreSQL Connection Pooling Series

How Do the Features Stack Up?

Let’s start by comparing PgBouncer vs. Pgpool-II features:

PGBOUNCER

PGPOOL-II

Resource consumptionIt uses only one process which makes it very lightweight. PgBouncer guarantees a small memory footprint, even when dealing with large datasets. **Winner!**If we require N parallel connections, this forks N child processes. By default, there are 32 child processes that are forked.**When are connections reused?**PgBouncer defines one pool per user+database combination. This is shared between all clients, so a pooled connection is available to all clients. **Winner!**Pgpool-II defines one process per child process. We cannot control which child process a client connects to. A client benefits from a pooled connection only if it connects to a child which has previously served a connection for this database+user combination.Pooling modesPgBouncer supports three different modes: session (connection returned to pool when client disconnects), transaction (returned to pool when client commits or rollbacks) or statement (connection returned to pool after the execution of each statement). **Winner!**Pgpool-II supports only session pooling mode - efficacy of pooling is dependent on good behavior from clients.High availabilityNot supported.PostgreSQL high availability is supported through Pgpool-II in-built watcher processes. Winner!Load balancingNot supported - PgBouncer recommends use of HAProxy for high availability and load balancing.Supports automatic load balancing - is even intelligent enough to redirect read requests to standbys, and writes to masters. Winner!Multi-cluster supportOne PgBouncer instance can front several PostgreSQL clusters (one-node or replica-sets). This can reduce the cost for middleware when using multiple PostgreSQL clusters. **Winner! **(Note - this advantage is only for specific scenarios)Pgpool-II does not have multi-cluster support.Connection controlPgBouncer allows limiting connections per-pool, per-database, per-user or per-client. **Winner!**Pgpool-II allows limiting the overall number of connections only.Connection queuePgBouncer supports queuing at the application level (i.e. PgBouncer maintains the queue). **Winner!**Pgpool-II supports queuing at kernel level - this can cause pg_bench on CentOS 6 to freeze.AuthenticationPass-through authentication is supported through PgBouncer. **Winner!**Pgpool-II does not support pass-through authentication - users and their md5 encrypted passwords must be listed in a file and manually updated every time a user updates their password.Pgpool-II does support passwordless authentication through PAM or SSL-certificates. However, these must be set up outside the PostgreSQL system, while PgBouncer can offload this to the PostgreSQL server.AdministrationPgBouncer provides a virtual database that reports various useful statistics.Pgpool-II provides a detailed administration interface, including a GUI. Winner!Host-based authenticationSupported. **Tied!**Supported. Tied!SSL supportFull support. **Tied!**Full support. Tied!Logical replicationNot supported through PgBouncer. **Tied!**Supported through Pgpool-II, but this is done by sending the write queries to all nodes, and is not generally recommended. Tied!LicenseISC - very permissive, basically allows all usage. **Tied!**Custom license - equally permissive. Tied!

The bottom line - Pgpool-II is a great tool if you need load-balancing and high availability. Connection pooling is almost a bonus you get alongside. PgBouncer does only one thing, but does it really well. If the objective is to limit the number of connections and reduce resource consumption, PgBouncer wins hands down.

It is also perfectly fine to use both PgBouncer and Pgpool-II in a chain - you can have a PgBouncer to provide connection pooling, which talks to a Pgpool-II instance that provides high availability and load balancing. This gives you the best of both worlds!

Performance Testing

While PgBouncer may seem to be the better option in theory, theory can often be misleading. So, we pitted the two connection poolers head-to-head, using the standard pgbench tool, to see which one provides better transactions per second throughput through a benchmark test. For good measure, we ran the same tests without a connection pooler too.

#database #data analysis