PgBouncer is a lightweight PostgreSQL connection pooler that solves one of PostgreSQL’s most common scalability problems: the high cost of creating new database connections. Unlike MySQL, PostgreSQL spawns a new OS process for each client connection, which consumes approximately 5–10 MB of memory and requires significant CPU time to establish. Web applications that open hundreds of short-lived connections will exhaust the max_connections limit or overwhelm the server with connection overhead. PgBouncer sits between the application and PostgreSQL, maintaining a small pool of persistent database connections and multiplexing many application connections onto them. This eliminates the connection overhead entirely and allows applications to use thousands of logical connections while PostgreSQL only sees a small pool of actual connections. This guide covers installing and configuring PgBouncer in session pooling and transaction pooling modes on RHEL 9.

Prerequisites

  • PostgreSQL 16 running on RHEL 9

Step 1 — Install PgBouncer

dnf install -y pgbouncer
pgbouncer --version

Step 2 — Configure pgbouncer.ini

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Pool mode: transaction (recommended for most web apps)
# session mode: required for SET, PREPARE, advisory locks
pool_mode = transaction

# Connection pool settings
default_pool_size = 20        # Connections to PostgreSQL per database/user pair
min_pool_size = 5             # Keep 5 connections warm
reserve_pool_size = 5         # Emergency reserve connections
max_client_conn = 200         # Maximum application-side connections
reserve_pool_timeout = 5

server_idle_timeout = 600
client_idle_timeout = 0
log_connections = 0
log_disconnections = 0

Step 3 — Create the PgBouncer Auth File

# Generate the SCRAM-SHA-256 password hash from PostgreSQL
# Connect to psql and get the hash:
psql -U postgres -c "SELECT usename, passwd FROM pg_shadow WHERE usename = 'myappuser';"

# /etc/pgbouncer/userlist.txt format:
# "username" "SCRAM-SHA-256$..." (the exact hash from pg_shadow)
"myappuser" "SCRAM-SHA-256$4096:..."

chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
chmod 600 /etc/pgbouncer/userlist.txt

Step 4 — Start PgBouncer

systemctl enable --now pgbouncer
systemctl status pgbouncer

# Test connection through PgBouncer (port 6432, not 5432)
psql -h 127.0.0.1 -p 6432 -U myappuser -d mydb -c "SELECT current_database();"

Step 5 — Monitor PgBouncer

# Connect to the pgbouncer admin database
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

-- Show pool status
SHOW POOLS;

-- Active columns:
-- cl_active: client connections currently executing a query
-- cl_waiting: clients waiting for a server connection
-- sv_active: server (PostgreSQL) connections in use
-- sv_idle: idle pooled server connections

-- Show database stats
SHOW STATS;
-- Shows total queries, total time, avg query time

Step 6 — Update Application Connection String

# Application connects to PgBouncer (port 6432) instead of PostgreSQL (port 5432)
# Python (psycopg2):
import psycopg2
conn = psycopg2.connect(
    host='127.0.0.1',
    port=6432,     # PgBouncer port
    database='mydb',
    user='myappuser',
    password='secret'
)

# PHP (PDO):
$pdo = new PDO('pgsql:host=127.0.0.1;port=6432;dbname=mydb', 'myappuser', 'secret');

Conclusion

PgBouncer in transaction pool mode on RHEL 9 dramatically reduces PostgreSQL’s connection overhead, enabling high-concurrency web applications to share a small pool of persistent database connections. Transaction mode is recommended for stateless web applications; use session mode if your application uses SET commands, prepared statements with DEALLOCATE, or advisory locks that must persist across queries. Monitor cl_waiting in SHOW POOLS — sustained non-zero values indicate the pool size needs to be increased.

Next steps: How to Install PostgreSQL 16 on RHEL 9, How to Configure PostgreSQL SSL on RHEL 9, and How to Monitor PostgreSQL with Prometheus on RHEL 9.