PgBouncer Tuning and Connection Storms

Black Friday. A real connection storm arrives. PgBouncer is confirmed absent on this server — find out exactly what PostgreSQL itself does, and does not do, when far too many connections arrive at once.

PgBouncer sits between an application and PostgreSQL specifically to decouple how many connections the application wants to open from how many PostgreSQL is actually configured to accept — queuing excess connection requests until a pooled server connection frees up, rather than letting PostgreSQL itself reject them outright. Checked directly on this server, PgBouncer is not installed and cannot be — no network access to fetch it, no compiler to build it from source, the same confirmed-absent pattern already found for pg_cron, pg_partman, and pgBadger elsewhere in this course.\n\nThat absence is itself the lesson's real subject: without a pooler in front of it, this lab reproduces exactly what PostgreSQL does on its own when far more connections arrive than max_connections allows — not a queue, not a graceful wait, but an immediate, real rejection error for every connection attempt beyond the limit. Understanding pool_mode conceptually (session, transaction, statement) explains what a pooler would have done instead; reproducing the real rejection directly shows exactly what is lost without one.

PgBouncer, Checked Directly

which pgbouncer
find / -iname '*pgbouncer*' 2>/dev/null

Both return nothing — genuinely absent, the same confirmed-absent pattern as pg_cron and pgBadger elsewhere in this course. There is no network access to install it and no compiler to build it from source on this VM.

pool_mode, Understood Conceptually

Since PgBouncer cannot run here, its three pool modes are worth understanding directly rather than through a live demo:

session pooling: a client keeps the same PostgreSQL connection for its entire session — safest, but scales no better than connecting directly.
transaction pooling: a PostgreSQL connection is only reserved for the duration of one transaction, then returned to the pool — the most common real-world mode, but breaks session-level state like prepared statements and SET that is not transaction-scoped.
statement pooling: a connection is returned after every single statement — the most aggressive pooling, but incompatible with multi-statement transactions entirely.

The Real Storm, Without a Pooler

SHOW max_connections;  -- 6

# 8 connections attempted at once, with 6 already in use:
for i in $(seq 1 8); do (psql -U postgres -d beer_db -c "SELECT pg_sleep(3);" &) ; done

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  sorry, too many clients already

A genuine, immediate rejection — not a queue, not a wait. PostgreSQL itself has no mechanism to hold an excess connection attempt and try again once room frees up; it simply refuses the connection outright the instant the limit is exceeded.

What a Pooler Would Have Done Differently

With PgBouncer (or any real pooler) in front of PostgreSQL, this same storm of 8 simultaneous requests would have been queued against a much smaller pool of real PostgreSQL connections — query_wait_timeout would decide how long a queued request waits before failing, rather than every excess request failing immediately and unconditionally the moment the limit is touched. The absence of that queue is the entire, real difference this lab just demonstrated directly.

Connection pooling (conceptual)

A layer sitting between an application and a database that maintains a smaller, steady set of real database connections and shares them across a much larger number of application-side requests — queuing excess requests briefly rather than forcing the database itself to accept (or reject) every single one directly. PgBouncer is the most common PostgreSQL-specific implementation, confirmed absent and unavailable on this particular server.

"sorry, too many clients already"

PostgreSQL's own real, immediate rejection error when a new connection attempt would exceed max_connections. There is no built-in queuing behavior for this situation — the connection is refused outright, instantly, which is exactly the gap a connection pooler exists to fill by queuing excess requests against a smaller, steady pool instead.

🔍 Confirm PgBouncer Is Genuinely Absent

Check directly whether PgBouncer is available on this server, rather than assuming either way.

which pgbouncer 2>&1; find / -iname '*pgbouncer*' 2>/dev/null; echo DONE

student@lab:~$ which pgbouncer 2>&1; find / -iname '*pgbouncer*' 2>/dev/null; echo DONE DONE

📊 Check This Server's Real Connection Ceiling

Check the real max_connections value and how many connections are already in use, before the storm arrives.

psql -U postgres -d beer_db -c "SHOW max_connections;" -c "SELECT count(*) FROM pg_stat_activity;"

student@lab:~$ psql -U postgres -d beer_db -c "SHOW max_connections;" -c "SELECT count(*) FROM pg_stat_activity;" SET max_connections ------------------ 6 (1 row) count ------- 6 (1 row)

🌊 Reproduce a Genuine Connection Storm

Attempt 8 simultaneous connections against a server with very little headroom left, and read the real rejection error.

for i in $(seq 1 8); do (psql -U postgres -d beer_db -c "SELECT pg_sleep(3);" > /tmp/conn_$i.txt 2>&1 &) ; done; sleep 4; cat /tmp/conn_*.txt

student@lab:~$ for i in $(seq 1 8); do (psql -U postgres -d beer_db -c "SELECT pg_sleep(3);" > /tmp/conn_$i.txt 2>&1 &) ; done; sleep 4; cat /tmp/conn_*.txt psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: sorry, too many clients already

🤔 Understand What a Pooler Would Have Done Instead

Read the real, cumulative connection state and reason through what queuing (rather than rejecting) would have looked like here.

psql -U postgres -d beer_db -c "SELECT state, count(*) FROM pg_stat_activity WHERE state IS NOT NULL GROUP BY state;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT state, count(*) FROM pg_stat_activity WHERE state IS NOT NULL GROUP BY state;" SET state | count --------+------- active | 2 (1 row)

Lab 3.6.4 complete. Connection storms, without a pooler, genuinely reproduced:\n\n\n PgBouncer confirmed absent : ✅ checked directly, not assumed\n Real connection ceiling checked : ✅ 6 total, 6 already in use\n Genuine connection storm reproduced : ✅ real "too many clients" error\n Pooler alternative understood : ✅ queuing vs immediate rejection\n

Enable JavaScript to run the live terminal and track your progress.