PostgreSQL Connection Pooling: Part 4 – PgBouncer vs. Pgpool-II
Tags: postgres, database, sql, performance
Published: 2020-07-29 16:35:17

---
title: PostgreSQL Connection Pooling: Part 4 – PgBouncer vs. Pgpool-II
published: true
description: Compare PgBouncer vs. Pgpool-II to see which PostgreSQL connection pooler offers the best features and analyze the throughput performance benchmark - ScaleGrid
tags: PostgreSQL, Database, SQL, Performance,
//cover_image: https://scalegrid.io/blog/wp-content/uploads/2020/07/PostgreSQL-Connection-Pooling-Part-4-PgBouncer-vs-Pgpool-II.jpg
---
<link rel="canonical" href="https://scalegrid.io/blog/postgresql-connection-pooling-part-4-pgbouncer-vs-pgpool/" />
<a href="https://scalegrid.io/blog/postgresql-connection-pooling-part-4-pgbouncer-vs-pgpool/"><img class="alignnone size-full wp-image-5736 fr-fic fr-dib" src="https://scalegrid.io/blog/wp-content/uploads/2020/07/PostgreSQL-Connection-Pooling-Part-4-PgBouncer-vs-Pgpool-II.jpg" alt="PostgreSQL Connection Pooling: Part 4 – PgBouncer vs. Pgpool-II" width="100%" height="auto"></a>
<p style="text-align: justify; padding-bottom: 20px;">In our previous posts in this series, we spoke at length about using <a style="color: #2da964;" href="https://www.pgbouncer.org/" target="_blank">PgBouncer</a> and <a style="color: #2da964;" href="https://www.pgpool.net/" target="_blank">Pgpool-II</a>, 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 <a style="color: #2da964;" href="https://scalegrid.io/blog/postgresql-connection-pooling-part-4-pgbouncer-vs-pgpool/" target="_blank">PgBouncer vs. Pgpool-II</a> performance for your <a style="color: #2da964;" href="https://scalegrid.io/postgresql.html" target="_blank">PostgreSQL hosting</a>!</p>

<table style="border: solid 20px #def5fe;" width="100%">
<tbody>
<tr>
<th style="padding: 20px 30px 20px 30px;">
<h2 style="padding-bottom: 10px;">PostgreSQL Connection Pooling Series</h2>
<ul>
	<li style="padding-bottom: 10px;"><a style="color: #2da964; text-decoration: underline;" href="https://scalegrid.io/blog/postgresql-connection-pooling-part-1-pros-and-cons/" target="_blank">Part 1 - Pros &amp; Cons</a></li>
	<li style="padding-bottom: 10px;"><a style="color: #2da964; text-decoration: underline;" href="https://scalegrid.io/blog/postgresql-connection-pooling-part-2-pgbouncer/" target="_blank">Part 2 - PgBouncer</a></li>
	<li style="padding-bottom: 10px;"><a style="color: #2da964; text-decoration: underline;" href="https://scalegrid.io/blog/postgresql-connection-pooling-part-3-pgpool-ii/" target="_blank">Part 3 - Pgpool-II</a></li>
	<li style="padding-bottom: 10px;"><a style="color: #2da964; text-decoration: underline;" href="https://scalegrid.io/blog/postgresql-connection-pooling-part-4-pgbouncer-vs-pgpool" target="_blank">Part 4 - PgBouncer vs. Pgpool-II</a></li>
</ul>
</th>
</tr>
</tbody>
</table>
<h2 style="padding-top: 15px;">How do the features stack up?</h2>
<p style="text-align: justify;">Let’s start by comparing PgBouncer vs. Pgpool-II features:</p>

<table width="100%">
<tbody>
<tr>
<th style="width: 24%;"></th>
<th style="width: 38%; background-color: #def5fe; padding: 10px;">
<h3>PgBouncer</h3>
</th>
<th style="width: 38%; background-color: #def5fe; padding: 10px;">
<h3>Pgpool-II</h3>
</th>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>Resource consumption</b></td>
<td style="border: 3px solid #2da964; padding: 6px 10px;">It uses only one process which makes it very lightweight. PgBouncer guarantees a small memory footprint, even when dealing with large datasets. <span style="color: #2da964;"><b>Winner!</b></span></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">If we require N parallel connections, this forks N child processes. By default, there are 32 child processes that are forked.</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>When are connections reused?</b></td>
<td style="border: 3px solid #2da964; padding: 6px 10px;">PgBouncer defines one pool per user+database combination. This is shared between all clients, so a pooled connection is available to all clients. <span style="color: #2da964;"><b>Winner!</b></span></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">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.</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>Pooling modes</b></td>
<td style="border: 3px solid #2da964; padding: 6px 10px;">PgBouncer 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). <span style="color: #2da964;"><b>Winner!</b></span></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">Pgpool-II supports only session pooling mode - efficacy of pooling is dependent on good behavior from clients.</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>High availability</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">Not supported.</td>
<td style="border: 3px solid #2da964; padding: 6px 10px;"><a href="https://scalegrid.io/blog/managing-high-availability-in-postgresql-part-1/" target="_blank">PostgreSQL high availability</a> is supported through Pgpool-II in-built watcher processes. <span style="color: #2da964;"><b>Winner!</b></span></td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>Load balancing</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">Not supported - PgBouncer recommends use of HAProxy for high availability and load balancing.</td>
<td style="border: 3px solid #2da964; padding: 6px 10px;">Supports automatic load balancing - is even intelligent enough to redirect read requests to standbys, and writes to masters. <span style="color: #2da964;"><b>Winner!</b></span></td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>Multi-cluster support</b></td>
<td style="border: 3px solid #2da964; padding: 6px 10px;">One PgBouncer instance can front several PostgreSQL clusters (one-node or replica-sets). This can reduce the cost for middleware when using multiple PostgreSQL clusters. <span style="color: #2da964;"><b>Winner! </b></span>(Note - this advantage is only for specific scenarios)</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">Pgpool-II does not have multi-cluster support.</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>Connection control</b></td>
<td style="border: 3px solid #2da964; padding: 6px 10px;">PgBouncer allows limiting connections per-pool, per-database, per-user or per-client. <span style="color: #2da964;"><b>Winner!</b></span></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">Pgpool-II allows limiting the overall number of connections only.</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>Connection queue</b></td>
<td style="border: 3px solid #2da964; padding: 6px 10px;">PgBouncer supports queuing at the application level (i.e. PgBouncer maintains the queue). <span style="color: #2da964;"><b>Winner!</b></span></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">Pgpool-II supports queuing at kernel level - this can cause pg_bench on CentOS 6 to freeze.</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>Authentication</b></td>
<td style="border: 3px solid #2da964; padding: 6px 10px;">Pass-through authentication is supported through PgBouncer. <span style="color: #2da964;"><b>Winner!</b></span></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">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.</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>Administration</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">PgBouncer provides a virtual database that reports various useful statistics.</td>
<td style="border: 3px solid #2da964; padding: 6px 10px;">Pgpool-II provides a detailed administration interface, including a GUI. <span style="color: #2da964;"><b>Winner!</b></span></td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>Host-based authentication</b></td>
<td style="border: 3px solid #def5fe; padding: 6px 10px;">Supported. <span style="color: #2da964;"><b>Tied!</b></span></td>
<td style="border: 3px solid #def5fe; padding: 6px 10px;">Supported. <span style="color: #2da964;"><b>Tied!</b></span></td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>SSL support</b></td>
<td style="border: 3px solid #def5fe; padding: 6px 10px;">Full support. <span style="color: #2da964;"><b>Tied!</b></span></td>
<td style="border: 3px solid #def5fe; padding: 6px 10px;">Full support. <span style="color: #2da964;"><b>Tied!</b></span></td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>Logical replication</b></td>
<td style="border: 3px solid #def5fe; padding: 6px 10px;">Not supported through PgBouncer. <span style="color: #2da964;"><b>Tied!</b></span></td>
<td style="border: 3px solid #def5fe; padding: 6px 10px;">Supported through Pgpool-II, but this is done by sending the write queries to all nodes, and is not generally recommended. <span style="color: #2da964;"><b>Tied!</b></span></td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>License</b></td>
<td style="border: 3px solid #def5fe; padding: 6px 10px;">ISC - very permissive, basically allows all usage. <span style="color: #2da964;"><b>Tied!</b></span></td>
<td style="border: 3px solid #def5fe; padding: 6px 10px;">Custom license - equally permissive. <span style="color: #2da964;"><b>Tied!</b></span></td>
</tr>
</tbody>
</table>
<p style="text-align: justify; padding-top: 20px;">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.</p>
<p style="text-align: justify; padding-bottom: 10px;">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!</p>
<p style="text-align: justify;"><a href="https://scalegrid.io/blog/wp-content/uploads/2020/07/PgBouncer-with-Pgpool-II-Connection-Pooling-Diagram.jpg"><img class="alignnone size-full wp-image-5736" src="https://scalegrid.io/blog/wp-content/uploads/2020/07/PgBouncer-with-Pgpool-II-Connection-Pooling-Diagram.jpg" alt="Using PgBouncer with Pgpool-II - Connection Pooling Diagram" width="100%" height="auto" /></a></p>

<h2 style="padding-top: 15px;">Performance Testing</h2>
<p style="text-align: justify;">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 <a style="color: #2da964;" href="https://www.postgresql.org/docs/10/pgbench.html" target="_blank">pgbench</a> 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.</p>

<h3 style="padding-top: 15px;">Testing Conditions</h3>
<p style="text-align: justify;">All of the PostgreSQL benchmark tests were run under the following conditions:</p>

<ol style="text-align: justify;">
	<li>Initialized pgbench using a scale factor of 100.</li>
	<li>Disabled auto-vacuuming on the PostgreSQL instance to prevent interference.</li>
	<li>No other workload was working at the time.</li>
	<li>Used the default pgbench script to run the tests.</li>
	<li>Used default settings for both PgBouncer and Pgpool-II, <i>except max_children</i>*. All PostgreSQL limits were also set to their defaults.</li>
	<li>All tests ran as a single thread, on a single-CPU, 2-core machine, for a duration of 5 minutes.</li>
	<li>Forced pgbench to create a new connection for each transaction using the -C option. This emulates modern web application workloads and is the whole reason to use a pooler!</li>
</ol>
<p style="text-align: justify;">We ran each iteration for 5 minutes to ensure any noise averaged out. Here is how the middleware was installed:</p>

<ul>
	<li style="text-align: justify;">For PgBouncer, we installed it on the same box as the PostgreSQL server(s). This is the configuration we use in our <a style="color: #2da964;" href="https://scalegrid.io/postgresql.html" target="_blank">managed PostgreSQL clusters</a>. Since PgBouncer is a very light-weight process, installing it on the box has no impact on overall performance.</li>
	<li style="text-align: justify;">For Pgpool-II, we tested both when the Pgpool-II instance was installed on the same machine as PostgreSQL (on box column), and when it was installed on a different machine (off box column). As expected, the performance is much better when Pgpool-II is off the box as it doesn’t have to compete with the PostgreSQL server for resources.</li>
</ul>
<h3>Throughput Benchmark</h3>
<p style="text-align: justify;">Here are the transactions per second (TPS) results for each scenario across a range of number of clients:</p>

<table width="100%">
<tbody>
<tr>
<th style="width: 16%; background-color: #def5fe; padding: 10px;"><b>Number of clients</b></th>
<th style="width: 21%; background-color: #def5fe; padding: 10px;"><b>Without pooling</b></th>
<th style="width: 21%; background-color: #def5fe; padding: 10px;"><b>PgBouncer</b></th>
<th style="width: 21%; background-color: #def5fe; padding: 10px;"><b>Pgpool-II </b>(on box)</th>
<th style="width: 21%; background-color: #def5fe; padding: 10px;"><b>Pgpool-II </b>(off box)</th>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>10</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">16.96</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">26.86</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">15.52</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">18.22</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>20</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">16.97</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">27.19</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">15.67</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">18.28</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>40</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">16.73</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">26.77</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">15.33</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">18.3</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>80</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">16.75</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">26.64</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">15.53</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">18.13</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>100</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">16.51</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">26.73</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">15.66</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">18.45</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>200</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">Connections aborted.</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">26.93</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">Connections aborted when max-children &gt; 200, pgbench hangs at max-children value if &lt;= 100.</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">Connections aborted when max-children &gt; 200, pgbench hangs at max-children value if &lt;= 100.</td>
</tr>
</tbody>
</table>
<p style="text-align: justify; padding-top: 20px;">Pgpool-II hangs when pg_bench is run with more clients than max_children. So, we increased the max_children to match the number of clients for each test run.</p>
<p style="text-align: justify;">If we calculate the percentage increase in TPS when using a connection pooler, here’s what we get:</p>

<table width="100%">
<tbody>
<tr>
<th style="width: 16%; background-color: #def5fe; padding: 10px;"><b>Number of clients</b></th>
<th style="width: 28%; background-color: #def5fe; padding: 10px;"><b>PgBouncer</b></th>
<th style="width: 28%; background-color: #def5fe; padding: 10px;"><b>Pgpool-II (on box)</b></th>
<th style="width: 28%; background-color: #def5fe; padding: 10px;"><b>Pgpool-II (off box)</b></th>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>10</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">58.37%</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">-8.49%</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">7.43%</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>20</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">60.22%</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">-7.66%</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">7.72%</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>40</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">60.01%</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">-8.37%</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">9.38%</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>80</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">59.04%</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">-7.28%</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">8.24%</td>
</tr>
<tr>
<td style="border: 1px solid #eee; padding: 6px 10px; background: #f9f9f9;"><b>100</b></td>
<td style="border: 1px solid #eee; padding: 6px 10px;">61.90%</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">-5.15%</td>
<td style="border: 1px solid #eee; padding: 6px 10px;">11.75%</td>
</tr>
</tbody>
</table>
<p style="text-align: justify; padding-top: 15px;">* Improvement algorithm = (with pooler - without)/without</p>

<h2 style="padding-top: 15px;">Final Words</h2>
<p style="text-align: justify;">As you can see from the performance test results, a well-configured connection and well-suited connection pooler can drastically increase the transaction throughput, even with a fairly small number of clients.  Connection poolers are especially useful for their queuing support - when the number of clients exceeds the max-clients supported by PostgreSQL server, PgBouncer is still able to maintain the transaction rate, whereas direct connections to PostgreSQL are aborted.</p>
<p style="text-align: justify;">However, a badly configured connection pooler can actually <i>reduce </i>the performance as we saw with the Pgpool-II setup here. Part of the problem is, using Pgpool-II <i>doubles </i>the number of processes running on the same server - we <i>must </i>run Pgpool-II on a separate server to get a good performance. But even then, PgBouncer manages to provide better performance for these relatively small numbers of clients.</p>
<p style="text-align: justify;">Also, note the test here was actually perfectly crafted for Pgpool-II - since when N &gt; 32, the number of clients and number of children processes were the same, and hence, each reconnection was guaranteed to find a cached process. Even then, PgBouncer was the faster alternative.</p>
<p style="text-align: justify; padding-bottom: 20px;">So, our testing indicates PgBouncer is the far better choice for connection pooling. But, it's important to remember that while a connection pooler is absolutely mandatory for most realistic workloads, whether you gain more by using a client-side pool or middleware such as PgBouncer depends on your application. Patterns of data access would play a role, as would the latencies involved based on your architecture. We recommend testing your workload against both, and then decide on the best course of action - there is no better alternative to experimentation!</p>