Connection Pooling with PgBouncer
Hit this database's real connection ceiling head-on, then learn the tool built to absorb exactly that storm
This training image has no network access at all — no DNS, no route out, no package manager, nothing to compile with. PgBouncer is not installed, and there is no way to install it here. Rather than skip the lesson, this lab does what every other tooling gap in this course has done: check first, say so plainly, and then get as close to the real thing as this VM genuinely allows.\n\nWhat this VM absolutely can do is reproduce the actual problem PgBouncer exists to solve. This cluster's max_connections is set to a genuinely tight 6 — you are about to fire more real connections at it than it can hold, and watch PostgreSQL itself refuse the excess with a real FATAL error. Then you will use ALTER ROLE ... CONNECTION LIMIT, a real, live, built-in partial mitigation, before reading exactly the pgbouncer.ini a real server would use to solve this properly.
Why Every PostgreSQL Connection Is Expensive
Each PostgreSQL connection is a full OS process, with its own memory for query execution, sorting, and caching — nothing like a lightweight thread. max_connections is a hard ceiling on how many of these can exist at once, and every connection past it is refused outright, not queued.
The Real, Built-In Partial Mitigation
ALTER ROLE app_user CONNECTION LIMIT 2;
This caps one specific role's concurrent connections, independent of PgBouncer or any other tool — a real, live safeguard against one misbehaving application component consuming every available slot and starving everything else.
What a Real pgbouncer.ini Looks Like
[databases]
beer_db = host=127.0.0.1 port=5432 dbname=beer_db
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 5
Applications connect to PgBouncer's own port (6432 here, not PostgreSQL's 5432) exactly as if it were PostgreSQL itself. Behind that single listening port, PgBouncer maintains a small, fixed pool of real PostgreSQL connections (default_pool_size) and multiplexes up to max_client_conn client connections across them.
Three Pool Modes, One Sharp Edge
| Mode | A server connection is returned to the pool... | Prepared statements |
|------|--------------------------------------------------|----------------------|
| session | when the client disconnects | Work normally |
| transaction | the instant each transaction commits | Break — a statement prepared on one server connection may not exist on the next one the same client gets handed |
| statement | after every single statement | Breaks multi-statement transactions entirely |
transaction mode is what makes PgBouncer's pooling dramatically more efficient than session mode — and it is exactly what breaks a client library that silently prepares statements under the hood, unless that library is explicitly told to stop.
Confirming Pool Health (On a Real Server)
psql -p 6432 pgbouncer -c "SHOW POOLS;"
Connecting to PgBouncer's own special pgbouncer admin database (not a real PostgreSQL database at all) shows exactly how many client connections are being served through how many server connections — the live proof that pooling is actually working.
pool_mode: session vs transaction vs statement
Determines when PgBouncer returns a real server connection back to its pool for another client to use. session mode is the safest and least efficient (one real connection per client connection, same as no pooling). transaction mode is the most common production choice, returning the connection the instant a transaction ends — which is also exactly what breaks client-side prepared statements. statement mode is the most aggressive and breaks any multi-statement transaction outright.
ALTER ROLE ... CONNECTION LIMIT
A per-role cap on concurrent connections, enforced directly by PostgreSQL itself with no external tool required. It does not multiplex connections the way PgBouncer does — it simply refuses a role's connection attempts past the limit — but it is a genuine, immediately available safeguard against one role's runaway connection count starving every other role on the same cluster.
📐 Check the Real Ceiling
Confirm this cluster's actual max_connections before doing anything else.
psql -U postgres -d beer_db -c "SHOW max_connections;"student@lab:~$ psql -U postgres -d beer_db -c "SHOW max_connections;" SET max_connections ------------------ 6 (1 row)
🔎 Confirm PgBouncer Is Not Here
Check for pgbouncer before assuming it is available — the same discipline as every other tooling gap in this course.
which pgbouncerpgbouncer --versionstudent@lab:~$ which pgbouncer student@lab:~$ pgbouncer --version -sh: pgbouncer: not found
⛈️ Cause a Real Connection Storm
Fire 8 concurrent connections at a database that can only hold 6, and watch PostgreSQL refuse the excess.
for i in $(seq 1 8); do psql -U postgres -d beer_db -c "SELECT pg_sleep(5);" > /tmp/storm_$i.log 2>&1 & donesleep 1psql -U postgres -d beer_db -c "SELECT count(*) FROM pg_stat_activity;"student@lab:~$ for i in $(seq 1 8); do psql -U postgres -d beer_db -c "SELECT pg_sleep(5);" > /tmp/storm_$i.log 2>&1 & done student@lab:~$ sleep 1 student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM pg_stat_activity;" psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: sorry, too many clients already
🛡️ Apply the Real, Built-In Mitigation
Wait for the storm's connections to finish (each was only a 5-second sleep), then create a role with a hard connection limit and confirm PostgreSQL itself enforces it, no external tool required.
sleep 6psql -U postgres -d beer_db -c "CREATE ROLE app_user LOGIN PASSWORD 'x' CONNECTION LIMIT 2;" -c "GRANT CONNECT ON DATABASE beer_db TO app_user;"psql -U app_user -d beer_db -c "SELECT pg_sleep(5);" > /tmp/c1.log 2>&1 &psql -U app_user -d beer_db -c "SELECT pg_sleep(5);" > /tmp/c2.log 2>&1 &sleep 1psql -U app_user -d beer_db -c "SELECT 1;"student@lab:~$ sleep 6 student@lab:~$ psql -U postgres -d beer_db -c "CREATE ROLE app_user LOGIN PASSWORD 'x' CONNECTION LIMIT 2;" -c "GRANT CONNECT ON DATABASE beer_db TO app_user;" SET CREATE ROLE GRANT student@lab:~$ psql -U app_user -d beer_db -c "SELECT pg_sleep(5);" > /tmp/c1.log 2>&1 & [1] 180 student@lab:~$ psql -U app_user -d beer_db -c "SELECT pg_sleep(5);" > /tmp/c2.log 2>&1 & [2] 181 student@lab:~$ sleep 1 student@lab:~$ psql -U app_user -d beer_db -c "SELECT 1;" psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: too many connections for role "app_user"
📖 Read a Real pgbouncer.ini
Read the configuration a real server would use for this exact database, and identify pool_mode, max_client_conn, and default_pool_size.
echo '[pgbouncer]' > /tmp/pgbouncer.iniecho 'listen_port = 6432' >> /tmp/pgbouncer.iniecho 'pool_mode = transaction' >> /tmp/pgbouncer.iniecho 'max_client_conn = 500' >> /tmp/pgbouncer.iniecho 'default_pool_size = 5' >> /tmp/pgbouncer.inigrep -E 'pool_mode|max_client_conn|default_pool_size' /tmp/pgbouncer.inistudent@lab:~$ echo '[pgbouncer]' > /tmp/pgbouncer.ini student@lab:~$ echo 'listen_port = 6432' >> /tmp/pgbouncer.ini student@lab:~$ echo 'pool_mode = transaction' >> /tmp/pgbouncer.ini student@lab:~$ echo 'max_client_conn = 500' >> /tmp/pgbouncer.ini student@lab:~$ echo 'default_pool_size = 5' >> /tmp/pgbouncer.ini student@lab:~$ grep -E 'pool_mode|max_client_conn|default_pool_size' /tmp/pgbouncer.ini pool_mode = transaction max_client_conn = 500 default_pool_size = 5
Lab 2.6.3 complete. The connection storm PgBouncer solves is no longer theoretical — you caused it directly, and applied the one real mitigation available without it:\n\n\n PgBouncer absence confirmed : ✅ checked, not assumed — no network to install it\n Connection storm reproduced : ✅ real FATAL error, this lab's own check refused\n ALTER ROLE CONNECTION LIMIT : ✅ genuine, live, built-in mitigation applied\n pgbouncer.ini read correctly : ✅ pool_mode, max_client_conn, default_pool_size\n
Enable JavaScript to run the live terminal and track your progress.