The PostgreSQL Connection Problem

PostgreSQL is a process-based database system. Every time an application opens a connection, PostgreSQL forks a new OS process. This architecture provides excellent isolation and stability, but introduces two major problems under high load:

  1. Memory Exhaustion: Each idle connection consumes 5-10 MB of RAM. 1,000 idle connections can waste 10GB of RAM.
  2. CPU Thrashing: High concurrent connections lead to excessive CPU context switching, degrading overall query performance.

The Solution: PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL. It sits between your application and the database, accepting thousands of client connections but only maintaining a small, efficient pool of real connections to PostgreSQL.

When a client wants to run a query, PgBouncer temporarily assigns them a connection from the pool. Once the transaction is done, the connection goes back to the pool for the next client.

Prerequisites

  • A Linux server running PostgreSQL (12+).
  • Root or sudo access.
  • Basic understanding of PostgreSQL authentication.

Step-by-Step Setup

1. Install PgBouncer

Ubuntu/Debian:

sudo apt update
sudo apt install pgbouncer

CentOS/RHEL:

sudo dnf install pgbouncer

2. Configure Authentication (userlist.txt)

PgBouncer needs to know the database passwords to authenticate clients before passing them to PostgreSQL.

First, extract the hashed passwords from PostgreSQL:

sudo -u postgres psql -Atq -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow;" > /tmp/userlist.txt
sudo mv /tmp/userlist.txt /etc/pgbouncer/userlist.txt
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 600 /etc/pgbouncer/userlist.txt

Note: If using SCRAM-SHA-256 (default in PG 14+), you must enable auth_type = scram-sha-256 in PgBouncer.

3. Configure pgbouncer.ini

Edit the main configuration file at /etc/pgbouncer/pgbouncer.ini:

[databases]
# Format: [db_name_in_pgbouncer] = host=[host] port=[port] dbname=[actual_db]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = *
listen_port = 6432

# Authentication
auth_type = md5  # Or scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Pooling Mode
# 'transaction' is recommended for most web applications
pool_mode = transaction

# Connection Limits
# How many real connections PgBouncer makes to PostgreSQL
default_pool_size = 20

# How many client connections PgBouncer will accept total
max_client_conn = 2000

# Other tuning
reserve_pool_size = 5
reserve_pool_timeout = 5
server_idle_timeout = 600

4. Adjust PostgreSQL Config (postgresql.conf)

Ensure PostgreSQL is configured to allow PgBouncer to connect:

# In postgresql.conf
max_connections = 100  # Leave this relatively low, let PgBouncer handle the thousands

Configure /etc/postgresql/15/main/pg_hba.conf to accept connections from PgBouncer (usually localhost/127.0.0.1 if installed on the same server).

5. Start PgBouncer

sudo systemctl enable pgbouncer
sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer

6. Connect the Application

Change your application’s database connection string to point to PgBouncer (port 6432) instead of PostgreSQL (port 5432).

  • Old: postgresql://dbuser:dbpass@127.0.0.1:5432/mydb
  • New: postgresql://dbuser:dbpass@127.0.0.1:6432/mydb

Pooling Modes Explained

PgBouncer supports three pooling modes (pool_mode):

  1. Session Mode: A server connection is tied to the client for their entire session. Safest, but negates most pooling benefits. Use only if your app uses prepared statements with connection-level state.
  2. Transaction Mode (Recommended): A server connection is assigned to a client only during a transaction (BEGIN to COMMIT). Best for web apps (PHP, Node.js, Python, Ruby). Warning: Do not use session-level features like PREPARE, SET, or LISTEN/NOTIFY in this mode.
  3. Statement Mode: Connection is returned to the pool after every individual query. Multi-statement transactions are not allowed. Rarely used.

PgBouncer Admin Console

PgBouncer has a built-in virtual database for administration. Connect to it to view statistics:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

Useful commands inside the console:

  • SHOW POOLS; (View active pools and connections)
  • SHOW STATS; (Query rates, network traffic)
  • RELOAD; (Reload pgbouncer.ini and userlist.txt without dropping connections)

Summary

  • Use PgBouncer to prevent PostgreSQL memory exhaustion from excessive idle connections.
  • Set pool_mode = transaction for optimal web application performance.
  • Keep max_connections in PostgreSQL relatively low (e.g., 100), and let PgBouncer’s max_client_conn handle the thousands of application connections.
  • Ensure authentication hashes in userlist.txt match your PostgreSQL setup (MD5 vs SCRAM).