PostgreSQL Connection Pooling: The Complete PgBouncer Guide

Understanding the Problem

Postgres treats every connection as a separate operating system process, not a lightweight thread. Each connection consumes memory, CPU scheduling, and file descriptors. With hundreds of connections, this overhead becomes significant. Postgres defaults to a maximum of 100 connections in its configuration, and hitting that limit produces the error "too many clients already running."

It is easy to reach that limit. For example, 10 application servers each with 10 connection pools already use 100 connections before any user request arrives.

What PgBouncer Does

PgBouncer is a connection pooler. It shares a small set of real database connections across a large number of applications. Instead of every client opening and closing its own connection, they all share a fixed pool of connections that stay open and get reused. The application thinks it has many connections, but Postgres only sees the few that PgBouncer maintains.

Think of it like a nightclub bouncer. The club has capacity for 10 people. Two hundred people want in. The bouncer manages the queue, lets 10 inside at a time, and everyone else waits. When someone leaves, the next person enters.

Checking Current Postgres Configuration

Start by verifying Postgres is running:

sudo systemctl status postgresql

Check the maximum connections setting:

sudo -u postgres psql -c "SHOW max_connections;"

The default is 100.

Check how many connections are currently active:

sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

This returns 6 connections initially.

Benchmarking Without PgBouncer

Use pgbench, a built-in benchmarking tool, to simulate load. Run it with 50 concurrent clients, 5 worker threads, for 15 seconds against the bench database:

pgbench -U postgres -h localhost -c 50 -j 5 -T 15 bench

The password is postgres.

While that runs, open a second terminal and check the connection count again:

sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

The count jumps to 56 connections. The benchmark shows a TPS (transactions per second) of around 104 and an average latency of 480 milliseconds.

Configuring PgBouncer

PgBouncer is already installed. Open its configuration file:

sudo nano /etc/pgbouncer/pgbouncer.ini

Configure the Database Section

Find the [databases] section and configure the bench database entry. This tells PgBouncer where to send connections when an application asks for the bench database:

bench = host=127.0.0.1 port=5432

Use 127.0.0.1 instead of localhost because localhost can resolve to IPv6 on some machines, which might not match how Postgres is listening.

Configure the PgBouncer Section

Search for listen_addr (Ctrl+W in nano) and set it to:

listen_addr = 127.0.0.1

Check that the port is correct:

listen_port = 6432

Search for auth_type and change it to plain for simplicity:

auth_type = plain

In production, use sha256 or md5 instead.

Search for pool_mode. Remove the semicolon to uncomment it, and change the value from session to transaction:

pool_mode = transaction

Session mode holds the connection for the entire client session, which barely pools at all. Transaction mode releases the connection back to the pool as soon as a commit runs, which is what makes pooling effective.

Search for max_client_conn. Uncomment it and set it to 200:

max_client_conn = 200

This is the number of connections the application will see.

Search for default_pool_size. Uncomment it and set it to 10:

default_pool_size = 10

This is the number of real Postgres connections PgBouncer will maintain.

Search for admin_users. Uncomment it and set it to postgres:

admin_users = postgres

This allows querying PgBouncer's internal state.

Save the file (Ctrl+S) and exit (Ctrl+X).

Create the User List

PgBouncer uses a userlist.txt file to authenticate connections. Create the Postgres user entry:

echo '"postgres" "postgres"' | sudo tee /etc/pgbouncer/userlist.txt

Restart PgBouncer

Restart the service to apply the configuration:

sudo systemctl restart pgbouncer

Verify it is running:

sudo systemctl status pgbouncer

Benchmarking With PgBouncer

Run the same benchmark, but this time point it at PgBouncer on port 6432:

pgbench -U postgres -h localhost -p 6432 -c 50 -j 5 -T 15 bench

While it runs, check the connection count in the second terminal:

sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

The count is now 16 connections instead of 56. Postgres sees only 16 connections, but the application thinks it has 50. The TPS and latency numbers change only slightly with 50 clients, but the difference becomes significant with larger numbers like 500 or 2000.

PgBouncer Pool Modes

PgBouncer has three pool modes:

• Session: Returns the connection to the pool only when the client session ends. This provides almost no pooling benefit but is safe.
• Statement: Returns the connection after every single statement. This breaks multi-statement transactions and is rarely used.
• Transaction: Returns the connection after each transaction commits. This is the sweet spot and the mode most applications should use.

Monitoring PgBouncer

PgBouncer has a built-in admin database. Connect to it to see pool statistics:

SHOW POOLS;

This shows how many client connections are active, waiting, and idle, and how many real Postgres connections are in use. Monitor this in production. If you see many clients waiting, increase default_pool_size. If Postgres is struggling, decrease it.

Sizing Formulas

A simple starting formula for default_pool_size is:

CPU cores × 2 + 1

Start conservative, watch SHOW POOLS, and tune from there.

For max_client_conn, use:

pool_size × number_of_databases + a few for admin

This prevents creating too many connections for Postgres to handle comfortably.