PostgreSQL Connection Pooling: Part 1 – Pros & Cons
Tags: postgres, database, connection, developer
Published: 2019-10-17 16:31:48

---
title: PostgreSQL Connection Pooling: Part 1 – Pros & Cons
published: true
description: Intro to PostgreSQL Connection Pooling: Learn the pros and cons of using a connection pooler, and how it helps you manage your database connections. ScaleGrid Blog
tags: PostgreSQL, Database, Connection, Developer,
canonical_url: https://scalegrid.io/blog/postgresql-connection-pooling-part-1-pros-and-cons/
---
<link rel="canonical" href="https://scalegrid.io/blog/postgresql-connection-pooling-part-1-pros-and-cons/" />

<a href="https://scalegrid.io/blog/postgresql-connection-pooling-part-1-pros-and-cons/"><img class="size-full wp-image-4612" src="https://scalegrid.io/blog/wp-content/uploads/2019/09/PostgreSQL-Connection-Pooling-Part-1-Pros-and-Cons.jpg" alt="PostgreSQL Connection Pooling: Part 1 – Pros & Cons" width="100%" height="auto" /></a>

<p style="text-align: justify">A long time ago, in a galaxy far far away, ‘threads’ were a programming novelty rarely used and seldom trusted. In that environment, the first <a style="color: #2da964" href="https://scalegrid.io/postgresql.html" target="_blank">PostgreSQL</a> developers decided forking a process for each connection to the database is the safest choice. It would be a shame if your database crashed, after all.</p>
<p style="text-align: justify;padding-bottom: 10px">Since then, a lot of water has flown under that bridge, but the PostgreSQL community has stuck by their original decision. It is difficult to fault <a style="color: #2da964" href="https://www.postgresql.org/message-id/1098894087.31930.62.camel@localhost.localdomain" target="_blank">their argument</a> - as it's absolutely true that:</p>

<ul style="text-align: justify">
	<li style="padding-bottom: 10px">Each client having its own process prevents a poorly behaving client from crashing the entire database.</li>
	<li style="padding-bottom: 10px">On modern Linux systems, the difference in overhead between forking a process and creating a thread is much lesser than it used to be.</li>
	<li style="padding-bottom: 10px">Moving to a multithreaded architecture will require extensive rewrites.</li>
</ul>
<p style="text-align: justify;padding-bottom: 10px">However, in modern web applications, clients tend to open a lot of connections. Developers are often strongly discouraged from holding a database connection while other operations take place. “Open a connection as late as possible, close a connection as soon as possible”. But that causes a problem with PostgreSQL’s architecture - forking a process becomes expensive when transactions are very short, as the common wisdom dictates they should be. In this post, we cover the <a style="color: #2da964" href="https://scalegrid.io/blog/postgresql-connection-pooling-part-1-pros-and-cons/" target="_blank">pros and cons of PostgreSQL connection pooling</a>.</p>


<a href="https://scalegrid.io/blog/wp-content/uploads/2019/09/postgres_architecture.png"><img class="size-full wp-image-4612" src="https://scalegrid.io/blog/wp-content/uploads/2019/09/postgres_architecture.png" alt="PostgreSQL Architecture Diagram" width="944" height="466" /></a> The PostgreSQL Architecture | <a href="http://www.interdb.jp/pg/pgsql02.html" target="_blank">Source</a>
<h2 style="padding-top: 10px">The Connection Pool Architecture</h2>
<p style="text-align: justify;padding-bottom: 15px">Using a modern language library does reduce the problem somewhat - connection pooling is an essential feature of most popular database-access libraries. It ensures ‘closed’ connections are not really closed, but returned to a pool, and ‘opening’ a new connection returns the same ‘physical connection’ back, reducing the actual forking on the PostgreSQL side.</p>


<a href="https://scalegrid.io/blog/wp-content/uploads/2019/09/connection-pool-architecture.png"><img class="size-full wp-image-4618" src="https://scalegrid.io/blog/wp-content/uploads/2019/09/connection-pool-architecture.png" alt="Visual Representation of a Connection Pool" width="580" height="175" /></a> The architecture of a generic connection-pool
<p style="text-align: justify;padding: 10px 0px">However, modern web applications are rarely monolithic, and often use multiple languages and technologies. Using a connection pool in each module is hardly efficient:</p>

<ul style="text-align: justify">
	<li style="padding-bottom: 10px">Even with a relatively small number of modules, and a small pool size in each, you end up with a lot of server processes. Context-switching between them is costly.</li>
	<li style="padding-bottom: 10px">The pooling support varies widely between libraries and languages - one badly behaving pool can consume all resources and leave the database inaccessible by other modules.</li>
	<li style="padding-bottom: 10px">There is no centralized control - you cannot use measures like client-specific access limits.</li>
</ul>
<p style="text-align: justify;padding-bottom: 10px">As a result, popular middlewares have been developed for PostgreSQL. These sit between the database and the clients, sometimes on a seperate server (physical or virtual) and sometimes on the same box, and create a pool that clients can connect to. These middleware are:</p>

<ul style="text-align: justify">
	<li style="padding-bottom: 10px">Optimized for PostgreSQL and its rather unique architecture amongst modern DBMSes.</li>
	<li style="padding-bottom: 10px">Provide centralized access control for diverse clients.</li>
	<li style="padding-bottom: 10px">Allow you to reap the same rewards as client-side pools, and then some more (we will discuss these more in more detail in our next posts)!</li>
</ul>

<h2 style="padding-top: 10px">PostgreSQL Connection Pooler Cons</h2>
<p style="text-align: justify;padding-bottom: 10px">A connection pooler is an almost indispensable part of a production-ready PostgreSQL setup. While there is plenty of well-documented benefits to using a connection pooler, there <i>are</i> some arguments to be made against using one:</p>

<ul style="text-align: justify">
	<li style="padding-bottom: 10px">Introducing a middleware in the communication inevitably introduces some latency. However, when located on the same host, and factoring in the overhead of forking a connection, this is negligible in practice as we will see in the next section.</li>
	<li>A middleware becomes a single point of failure. Using a cluster at this level can resolve this issue, but that introduces added complexity to the architecture.

<a href="https://scalegrid.io/blog/wp-content/uploads/2019/09/redundancy-in-middleware.png"><img class="size-full wp-image-4621" src="https://scalegrid.io/blog/wp-content/uploads/2019/09/redundancy-in-middleware.png" alt="Redundant pgBouncer instances to prevent single point of failure" width="1400" height="792" /></a> Redundancy in middleware to avoid Single-Point-of-Failure | <a href="https://medium.com/tech-hornet/postgres-and-rails-from-0-20-million-users-part-1-aef2f73a480e" target="_blank">Source</a></li>
	<li style="padding: 10px 0px">A middleware implies extra costs. You either need an extra server (or 3), or your database server(s) must have enough resources to support a connection pooler, in addition to PostgreSQL.</li>
	<li style="padding-bottom: 10px">Sharing connections between different modules can become a security vulnerability. It is very important that we configure pgPool or PgBouncer to clean connections before they are returned to the pool.</li>
	<li>The authentication shifts from the DBMS to the connection pooler. This may not always be acceptable.

<a href="https://scalegrid.io/blog/wp-content/uploads/2019/09/PgBouncer-Authentication-Model.png"><img class="size-full wp-image-4624" src="https://scalegrid.io/blog/wp-content/uploads/2019/09/PgBouncer-Authentication-Model.png" alt="PgBouncer Authentication Model" width="1600" height="445" /></a> PgBouncer Authentication Model | <a href="https://www.cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/" target="_blank">Source</a></li>
	<li style="padding: 10px 0px">It increases the surface area for attack, unless access to the underlying database is locked down to allow access only via the connection pooler.</li>
	<li style="padding-bottom: 10px">It creates yet another component that must be maintained, fine tuned for your workload, security patched often, and upgraded as required.</li>
</ul>
<h2 style="padding-top: 10px">Should You Use a PostgreSQL Connection Pooler?</h2>
<p style="text-align: justify">However, all of these problems are well-discussed in the PostgreSQL community, and mitigation strategies ensure the pros of a connection pooler far exceed their cons. Our tests show that even a small number of clients can significantly benefit from using a connection pooler. They are well worth the added configuration and maintenance effort.</p>
<p style="text-align: justify;padding-bottom:20px">In the next post, we will discuss one of the most popular connection poolers in the PostgreSQL world - <a style="color: #2da964" href="https://pgbouncer.github.io/" target="_blank">PgBouncer</a>, followed by <a style="color: #2da964" href="https://www.pgpool.net/mediawiki/index.php/Main_Page" target="_blank">Pgpool-II</a>, and lastly a performance test comparison of these two PostgreSQL connection poolers in our final post of the series.</p>