PostgreSQL Connection Pooling: Part 2 – PgBouncer
Tags: postgres, database, connection, pgbouncer
Published: 2019-12-19 21:26:18

---
title: PostgreSQL Connection Pooling: Part 2 – PgBouncer
published: true
description: PgBouncer is the most popular PostgreSQL connection pooler. In this post, we explain how PgBouncer works, its pros and cons, and how to setup. ScaleGrid Blog
tags: PostgreSQL, Database, Connection, PgBouncer,
canonical_url: https://scalegrid.io/blog/postgresql-connection-pooling-part-2-pgbouncer/
---
<link rel="canonical" href="https://scalegrid.io/blog/postgresql-connection-pooling-part-2-pgbouncer/" />

<a title="PostgreSQL Connection Pooling: Part 2 – PgBouncer" href="https://scalegrid.io/blog/postgresql-connection-pooling-part-2-pgbouncer/" target="_blank" style="color:#2da964"><img src="https://scalegrid.io/blog/wp-content/uploads/2019/12/PostgreSQL-Connection-Pooling-Part-2-PgBouncer-ScaleGrid-Blog.jpg" width="100%" height="auto"></a>

<p style="text-align: justify;">When it comes to connection pooling in the PostgreSQL world, <a title="PgBouncer" href="https://pgbouncer.github.io" target="_blank" style="color:#2da964">PgBouncer</a> is probably the most popular option. It's a very simple utility that does exactly one thing - it sits between the database and the clients and speaks the PostgreSQL protocol, emulating a PostgreSQL server. A client connects to PgBouncer with the exact same syntax it would use when connecting directly to PostgreSQL - PgBouncer is essentially invisible.</p>
<p style="text-align: justify;">PgBouncer is supported by almost every <a title="PostgreSQL hosting" href="https://scalegrid.io/postgresql.html" target="_blank" style="color:#2da964">PostgreSQL DBaaS</a> vendor, and widely used across the community. In this blog post, we'll explain <a title="PostgreSQL Connection Pooling: Part 2 – PgBouncer" href="https://scalegrid.io/blog/postgresql-connection-pooling-part-2-pgbouncer/" target="_blank" style="color:#2da964">how PgBouncer works</a>, the pros and cons of using it, and how to setup the connection pooler. If you'd like to know more about connection pooling in general, or wondering if it's right for your deployment, check out our <a title="PostgreSQL Connection Pooling: Part 1 – Pros &amp; Cons" href="https://scalegrid.io/blog/postgresql-connection-pooling-part-1-pros-and-cons/" target="_blank" style="color:#2da964">PostgreSQL Connection Pooling: Part 1 - Pros &amp; Cons</a> post.</p>


<h2 style="padding-top:15px">How Does PgBouncer Work?</h2>
<p style="text-align: justify;">When PgBouncer receives a client connection, it first performs authentication on behalf of the PostgreSQL server. PgBouncer supports all the authentication mechanisms that PostgreSQL server supports, including a host-based-access configuration (note: we cannot route replication connections through PgBouncer). If a password is provided, the authentication can be done in two ways:</p>

<ol style="text-align: justify;">
	<li style="padding:6px 0px">PgBouncer first checks the userslist.txt file - this file specifies a set of (username, md5 encrypted passwords) tuples. If the username exists in this file, the password is matched against the given value. No connection to PostgreSQL server is made.</li>
	<li style="padding:6px 0px">If passthrough authentication is setup, and the user is not found in the userslist.txt file, PgBouncer searches for an auth_query. It connects to PostgreSQL as a predefined user (whose password must be present in the userslist.txt file) and executes the auth-query to find the user’s password and matches it to the provided value.</li>
</ol>
<p style="text-align: justify;">Once the authentication succeeds:</p>

<ol style="text-align: justify;">
	<li style="padding:6px 0px">PgBouncer checks for a cached connection, with the same username+database combination.</li>
	<li style="padding:6px 0px">If a cached connection is found, it returns the connection to the client.</li>
	<li style="padding:6px 0px">If a cached connection is not found, it creates a new connection, provided creating a new connection does not:

<ul type="a">
	<li style="padding:6px 0px">Increase the number of connections to &gt; pool_size</li>
	<li style="padding:6px 0px">Increase the number of connections from the client to &gt; max_client_connections</li>
	<li style="padding:6px 0px">Increase the number of connections to the database to &gt; max_db_connections</li>
	<li style="padding:6px 0px">Increase the number of connections from the user to &gt; max_user_connections</li>
</ul></li>
	<li style="padding:6px 0px">All of these values can be defined in the PgBouncer settings.</li>
	<li style="padding:6px 0px">If creating a new connection would violate any of the settings, PgBouncer queues the connection until a new one can be created, except if it violates the max_client_connections restriction.
<table style="margin:10px 0px;">
  <tr>
    <td style="background-color:#f5f8fa; padding:15px;"><i>Note - The timing of post-authentication steps differ slightly based on PgBouncer mode. Under transaction or statement pooling mode, the post-authentication steps are executed only when the client starts executing a transaction/statement. We discuss more about the pooling modes below.</i></td>
  </tr>
</table></li>
	<li style="padding:6px 0px">If it violates the max_client_connections restriction, it aborts the connection.</li>
</ol>


<a href="https://scalegrid.io/blog/wp-content/uploads/2019/12/pgboucer-2-1.png"><img class="size-full wp-image-5005" src="https://scalegrid.io/blog/wp-content/uploads/2019/12/pgboucer-2-1.png" alt="PgBouncer Architecture Diagram" width="985" height="489" /></a> pgBouncer Architecture | <a href="https://postgresdba.wordpress.com/category/pgbouncer/">Source</a>
<p style="text-align: justify;padding-top:10px">Based on the pooling mode, PgBouncer waits for an opportunity to return the connection back to the database:</p>

<ul style="text-align: justify;">
	<li style="padding:6px 0px">In session pooling mode, a connection is returned to the pool only when a client closes the session.</li>
	<li style="padding:6px 0px">In transaction pooling mode, a connection is returned to the pool only when a client completes a transaction (typically either a rollback or a commit is executed). As a result, session-based features are not supported in this mode. There is no guarantee two transactions ran on the same client PgBouncer connection will run on the same PgBouncer server connection.</li>
	<li style="padding:6px 0px">In statement pooling mode, a connection is returned to the pool as soon as a statement is executed. Here, autocommit is always on.</li>
</ul>
<p style="text-align: justify;">Before returning the connection back to the database, PgBouncer runs a reset query to strip it of all session information - this makes it safe to share connections between clients. It is possible to configure this query based on the needs of the application.</p>
<p style="text-align: justify;">The transaction pooling mode is used most often, though the session pooling mode might be useful for particular workloads. You can read more about PgBouncer on their <a href="https://wiki.postgresql.org/wiki/PgBouncer#Feature_matrix_for_pooling_modes" target="_blank" style="color:#2da964">Wiki page</a>.</p>

<h2 style="padding-top:15px">Why Choose PgBouncer?</h2>

<p style="text-align: justify;">There are many reasons PgBouncer is the most popular choice when it comes to connection pooling in PostgreSQL. Here are some of the best features and pros PgBouncer offers:</p>
<ul style="text-align: justify;">
<li style="padding:6px 0px"><strong>Pooling Modes</strong> - By giving users the power to decide when a connection is returned to the pool, PgBouncer is able to support a vast range of use cases. And, since this setup is at a pool level, you could use transaction mode (better performance) for your usual database connections, and session mode only when you require features like <a href="https://www.postgresql.org/docs/9.3/sql-prepare.html" target="_blank" style="color:#2da964">prepared statements</a>!</li>
<li style="padding:6px 0px"><strong>Easy Setup & Use</strong> - PgBouncer is one of the easiest PostgreSQL connection poolers to setup out of the box, and it also requires no client-side code changes. </li>
<li style="padding:6px 0px"><strong>Passthrough Authentication</strong> - PgBouncer is one of the few "middleware" connection poolers that can securely authenticate a user without having access to their passwords (in plaintext or encrypted form). This makes PgBouncer more secure and much easier to maintain - you need not update PgBouncer every time a user updates their password.</li>
<li style="padding:6px 0px"><strong>Lightweight</strong> - It is a single process, and all commands from the client and responses from the server passthrough PgBouncer without any processing. So, it does not need to ‘see’ the entire contents at once, and hence, maintains a very small memory footprint. </li>
<li style="padding:6px 0px"><strong>Scalability & Performance</strong> - As we will discuss in more detail in the final part of our series, PgBouncer can significantly improve the transactions per second that your PostgreSQL server can support, and it scales very well to a very large number of clients. </li>
</ul>

<h2 style="padding-top:15px">What Doesn't PgBouncer Do?</h2>
<p style="text-align: justify;">PgBouncer, while a great connection pooler, does not support automated load balancing or <a title="PostgreSQL high availability" href="https://scalegrid.io/blog/managing-high-availability-in-postgresql-part-3/" target="_blank" style="color:#2da964">high-availability</a>. It recommends using other common linux tools like <a title="HAProxy" href="http://www.haproxy.org/" target="_blank" style="color:#2da964">HAProxy</a> to create an architecture which does support these features.</p>
<p style="text-align: justify;">Take a look at the sample PostgreSQL architecture for load-balanced reads below:</p>


<a href="https://scalegrid.io/blog/wp-content/uploads/2019/12/pgbouncer-2-2.png"><img class="size-full wp-image-5008" src="https://scalegrid.io/blog/wp-content/uploads/2019/12/pgbouncer-2-2.png" alt="PgBouncer Architecture to support load balanced reads" width="495" height="466" /></a> PgBouncer architecture for load balanced reads | <a href="https://dba.stackexchange.com/questions/56559/postgresql-high-availability-scalability-using-haproxy-and-pgbouncer">Source</a>
<p style="text-align: justify;">Note - The master node (that all these slaves would be replicating from) is not shown in the diagram.</p>

<h2 style="padding-top:15px">How To Set Up PgBouncer</h2>
<p style="text-align: justify;">If you have a ScaleGrid <a href="https://scalegrid.io/postgresql.html" target="_blank" style="color:#2da964">PostgreSQL deployment</a>, you can <a href="https://help.scalegrid.io/docs/managing-pgbouncer" target="_blank" style="color:#2da964">set up PgBouncer</a> in a few clicks. Go to the details view of your PostgreSQL cluster and click on the PgBouncer icon. Once you select “Enable PgBouncer” you will be presented with configuration options to customize your pooling mode and pool size - you can accept the defaults (don’t worry, you can change them anytime with no downtime), and click Enable!</p>
<p style="padding:15px 0px"><a href="https://scalegrid.io/blog/wp-content/uploads/2019/12/PostgreSQL-Enable-PgBouncer.gif"><img class="alignnone size-full wp-image-5013" src="https://scalegrid.io/blog/wp-content/uploads/2019/12/PostgreSQL-Enable-PgBouncer.gif" alt="Enable PgBouncer - PostgreSQL Hosting at ScaleGrid DBaaS" width="600" height="314" /></a></p>
<p style="text-align: justify;">And that’s it! You are good to go.</p>
<p style="text-align: justify;">If you have a non-ScaleGrid deployment, PgBouncer is distributed as part of the <a title="PostgreSQL Repository" href="https://download.postgresql.org/pub/repos/" target="_blank" style="color:#2da964">PostgreSQL repository</a> and can be installed using the respective package managers. For more detailed instructions, or to build from source, you can follow the instructions from their <a href="https://pgbouncer.github.io/install.html" target="_blank" style="color:#2da964">blog</a>.</p>
<p style="text-align: justify;">Once installed, PgBouncer only requires you to set up a few configuration parameters to get up and running:</p>

<ol style="text-align: justify;">
	<li style="padding:6px 0px">A list of (username, md5 encrypted password) to authenticate clients or a <a title="How to setup passthrough authentication for pgBouncer" href="https://hunleyd.github.io/posts/pgBouncer-and-auth-pass-thru/" target="_blank" style="color:#2da964">passthrough authentication</a> setup for a more secure deployment.</li>
	<li style="padding:6px 0px">Interfaces/IP:ports to listen for incoming connections.</li>
	<li style="padding:6px 0px">Pool definitions. A ‘pool’ is a name that clients use as a database-name when connecting to PgBouncer - it can be mapped to a full connection string (host, port, dbname and user). The simplest definition is of the form:
<code>* = host=</code>
This will create dynamic pools for each dbname+user combination, and connect to the defined host using the port, dbname and username provided by the user.</li>
</ol>
<p style="text-align: justify;">And that is it! You can be up and running very quickly with PgBouncer. However, there are many more settings which must be tuned for any production distribution - those are beyond the scope of this blog post, but you can read more about them in this <a href="https://pgbouncer.github.io/config.html" target="_blank" style="color:#2da964">PgBouncer configurations overview</a>.</p>

<p style="text-align: justify; padding-bottom:20px;">PgBouncer, however, is not the only option for PostgreSQL connection pooling - in our next post, we will discuss Pgpool-II, which is probably the main competitor to PgBouncer. Stay tuned for our fourth post in this four-part series where we compare PgBouncer vs. Pgpool-II.</p>