Background: When PostgreSQL Starts Struggling
After migrating a 100GB database from MySQL to PostgreSQL — which took 3 days of planning and 1 day of execution — the system ran smoothly for about 2 weeks before strange symptoms appeared. The number of concurrent connections kept climbing with each new app instance deployment, and API response times crept up to 800ms–1s during peak hours. Occasionally the app would return a too many connections error outright.
Checking pg_stat_activity revealed hundreds of idle connections — doing nothing, just sitting there waiting for a new query. The problem was clear: every connection to PostgreSQL is a separate OS process, consuming real RAM and CPU. 180 idle connections meant 180 processes running with nothing to do.
Unlike MySQL’s thread-based model, PostgreSQL spawns a dedicated OS process for each connection — known as the process-per-connection model. Scale out with more app instances? Each instance maintains its own connection pool, and the total connection count grows exponentially with no one in control.
Connection pooling at the application layer (HikariCP, psycopg2 pool) only solves the problem within a single process. To consolidate connections from multiple instances, you need a proxy layer sitting between the app and the database. That’s exactly what PgBouncer does.
How PgBouncer Works
PgBouncer is a lightweight proxy — consuming around 1–2MB of RAM — that sits between your app and PostgreSQL. It accepts connections from multiple app instances and reuses a small pool of real connections to the PostgreSQL server. The app thinks it’s connecting directly to PostgreSQL, but it’s actually talking to PgBouncer.
PgBouncer has 3 pool modes:
- session mode: Holds one server connection for the entire client session — the safest but least efficient
- transaction mode: Holds a server connection only for the duration of a single transaction — most efficient, suitable for the majority of modern apps
- statement mode: Extremely aggressive, holds the connection for only a single statement — not compatible with multi-statement transactions
In production I use transaction mode. With 500 client connections to PgBouncer, only ~25 real connections to PostgreSQL are needed. That’s the whole point of connection pooling.
Installing PgBouncer
On Ubuntu/Debian:
sudo apt update
sudo apt install -y pgbouncer
# Check version
pgbouncer --version
# PgBouncer 1.21.0After installation, PgBouncer won’t start yet because it needs to be configured first. The config files live in /etc/pgbouncer/.
Detailed Configuration
The pgbouncer.ini File
Edit /etc/pgbouncer/pgbouncer.ini:
[databases]
; Alias that the app connects to = real connection string to PostgreSQL
myapp_db = host=127.0.0.1 port=5432 dbname=myapp_production
[pgbouncer]
; PgBouncer listening address
listen_port = 6432
listen_addr = 127.0.0.1
; Authentication
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; Pool mode
pool_mode = transaction
; Client-side: connections from the app into PgBouncer
max_client_conn = 1000
; Server-side: real connections to PostgreSQL (per database-user pair)
default_pool_size = 25
; Reserve pool when main pool is full
reserve_pool_size = 5
reserve_pool_timeout = 3
; Close idle server connections after 10 minutes
server_idle_timeout = 600
client_idle_timeout = 0
; Log
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
; Admin access
admin_users = pgbouncer_admin
stats_users = pgbouncer_statsKey parameters to understand:
default_pool_size = 25: total server connections to PostgreSQL per database-user pair. This number depends onmax_connectionsin PostgreSQL and how many databases you’re pooling. In my case, PostgreSQL is configured withmax_connections = 100and I use 2 databases — so 25 per pool is a reasonable fit.max_client_conn = 1000: the number of clients that can connect to PgBouncer — a large number here is fine since PgBouncer is extremely lightweight (each client uses only a few KB).server_idle_timeout = 600: avoids holding server connections idle longer than necessary.
The userlist.txt File
Retrieve the password hash from PostgreSQL and add it to the auth file:
# Get the password hash for the user
sudo -u postgres psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename='myapp_user';"Create the file /etc/pgbouncer/userlist.txt:
"myapp_user" "md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
"pgbouncer_admin" "md5yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy"Set the correct permissions so PgBouncer can read it:
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 640 /etc/pgbouncer/userlist.txtStarting and Connecting
sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
sudo systemctl status pgbouncerThe app now connects to port 6432 instead of 5432. With Python:
import psycopg2
# Before: connect directly to PostgreSQL on port 5432
# conn = psycopg2.connect("host=localhost port=5432 dbname=myapp_production user=myapp_user")
# After: connect through PgBouncer on port 6432
conn = psycopg2.connect(
host="localhost",
port=6432, # PgBouncer port
dbname="myapp_db", # Alias defined in the [databases] section
user="myapp_user",
password="your_password"
)Caveats When Using Transaction Mode
Transaction mode does not support certain session-level PostgreSQL features:
SET/RESETsession variablesLISTEN/NOTIFY- Session-level advisory locks
- Prepared statements (requires additional configuration or setting
server_reset_query_always = 1)
A standard Django/FastAPI app? Usually no issues at all. But if your code explicitly uses prepared statements, test thoroughly before going to production — or switch to session mode instead.
Monitoring & Verification
PgBouncer Admin Console
Connect to the admin console:
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncerUseful commands:
-- Show pool status
SHOW POOLS;
-- Show request/s and latency stats
SHOW STATS;
-- List server connections (real connections to PostgreSQL)
SHOW SERVERS;
-- Reload config without restarting
RELOAD;The output of SHOW POOLS looks like this:
database | user | cl_active | cl_waiting | sv_active | sv_idle | maxwait
-----------+------------+-----------+------------+-----------+---------+---------
myapp_db | myapp_user | 45 | 0 | 20 | 5 | 0The most important column to watch is cl_waiting. A consistently non-zero value is a signal that the pool is under strain — clients are queuing up to wait. The fix: increase default_pool_size and raise max_connections in PostgreSQL accordingly.
Checking from the PostgreSQL Side
-- Count real connections from PgBouncer into PostgreSQL
SELECT count(*), state
FROM pg_stat_activity
WHERE usename = 'myapp_user'
GROUP BY state;
-- Check current max_connections setting
SHOW max_connections;Simple Alert Script
#!/bin/bash
# /opt/scripts/check-pgbouncer.sh
# Run via cron every 5 minutes: */5 * * * * /opt/scripts/check-pgbouncer.sh
WAITING=$(psql -h 127.0.0.1 -p 6432 -U pgbouncer_stats pgbouncer \
-t -c "SHOW POOLS;" 2>/dev/null | awk -F'|' '{sum += $4} END {print int(sum)}')
if [ "${WAITING:-0}" -gt 10 ]; then
echo "ALERT: PgBouncer has $WAITING clients waiting for a connection!" | \
mail -s "[PgBouncer] Pool exhausted" [email protected]
fiReal-World Results After 6 Months
Concrete numbers after deploying PgBouncer:
- Server connections to PostgreSQL dropped from ~180 to a stable ~25
- PostgreSQL RAM usage decreased by roughly 15% due to fewer processes
- API response time during peak hours stabilized below 300ms (previously 800ms–1s)
- No more
too many connectionserrors
The key takeaway after 6 months in production: PostgreSQL is extremely powerful, but its connection model requires more active management than MySQL. PgBouncer isn’t a silver bullet — slow queries still need to be optimized at the query level. But for connection overhead at the infrastructure layer, this is the clearest solution available.
If your PostgreSQL is seeing an abnormal spike in connections or your app is reporting timeout errors — add PgBouncer before considering a server upgrade. It usually resolves the problem without touching any hardware.
