What Problem Does Connection Pooling Solve in PostgreSQL?

Let’s first understand what connection pooling means.

A database connection is a communication channel between a database client and the PostgreSQL server.

Connection pooling means maintaining a set of database connections that can be reused.

But why do we need to reuse connections in the first place?

To understand that, we first need to understand what happens when PostgreSQL starts and when a client connects to it.

What Happens When PostgreSQL Starts?

When the PostgreSQL server starts, it starts a supervisor process, historically called the postmaster.

The supervisor process waits for clients to connect to the PostgreSQL server, which listens on port 5432 by default.

The important point is that this process is already running before any client connects.

What Happens When a Client Connects?

When a database client establishes a connection, PostgreSQL creates a separate backend process to handle that connection.

The client can then send multiple queries through the same connection, and the same backend process handles those queries.

The backend process lives for the lifetime of the connection. When the connection is closed, the backend process terminates.

The Two Problems Connection Pooling Solves

Problem 1: Creating Connections Repeatedly Is Expensive

Creating a PostgreSQL connection is not free.

The client and server need to establish the connection, exchange startup information, perform authentication, and potentially negotiate SSL. PostgreSQL also creates a backend process for the connection.

If an application repeatedly creates a new connection, runs a query, and then closes the connection, it has to pay that setup cost again and again.

A connection pool lets database connections be reused instead of repeatedly creating and destroying them.

Problem 2: Too Many Connections Are Also a Problem

PostgreSQL cannot accept an unlimited number of concurrent connections.

The max_connections setting controls how many connections PostgreSQL will allow at the same time. Once the available connection slots are exhausted, new connection attempts are rejected.

But problems can appear even before that limit is reached.

Each PostgreSQL connection has a backend process associated with it, and all of those processes ultimately compete for finite resources such as CPU, memory, disk I/O, and locks.

As the number of active connections increases, there comes a point where adding more connections no longer increases useful throughput. Instead, the additional connections can increase contention and latency. In other words, more database connections do not automatically mean more database capacity.

A connection pool limits how many database connections an application can keep open at the same time.