Day 12: Connection Pooling with PgBouncer
Goal: PgBouncer runs on
pg1, authenticates via SCRAM through anauth_query, funnels many clients onto a few server connections, and you know the pitfalls of transaction mode from your own experience. · Duration: approx. 60 min · Prerequisite: Day 3
Briefing
- Every PostgreSQL connection is its own process with login, memory and cache warm-up. Hundreds of clients with short queries waste most of that.
- PgBouncer accepts client connections and distributes them over a small pool of real server connections.
pool_mode:session(server connection for the whole session),transaction(only for one transaction – the normal case),statement(for one statement).- In transaction mode the server connection is no longer yours between
two transactions: session state (
SET, SQLPREPARE, advisory locks) travels with it.
pg2 (clients, 50x pgbench) pg1
-------------------------> :6432 PgBouncer --10 conn.--> :5432 PostgreSQL
port 6432, SCRAM auth_query via tag12_pgbouncer
All commands run on pg1 unless pg2 is
stated.
Drill 1: Install PgBouncer from PGDG
apt-cache madison pgbouncer
sudo apt-get install -y pgbouncer
pgbouncer --version | head -1
systemctl is-active pgbouncer
sudo ss -tlnp | grep 6432Expected (abridged):
pgbouncer | 1.26.1-1.pgdg24.04+1 | https://apt.postgresql.org/pub/repos/apt noble-pg...
pgbouncer | 1.26.0-4.pgdg24.04+1 | https://apt.postgresql.org/pub/repos/apt noble-pg...
pgbouncer | 1.26.0-1.pgdg24.04+1 | https://apt.postgresql.org/pub/repos/apt noble-pg...
pgbouncer | 1.22.0-1build4 | http://archive.ubuntu.com/ubuntu noble/universe amd64 ...
PgBouncer 1.26.1
active
LISTEN 0 128 127.0.0.1:6432 0.0.0.0:* users:(("pgbouncer",pid=20417,fd=9))
LISTEN 0 128 [::1]:6432 [::]:* users:(("pgbouncer",pid=20417,fd=8))
Ubuntu itself ships 1.22; PGDG delivers the current 1.26 with all bug
fixes since then. The service runs as postgres,
configuration in /etc/pgbouncer, log in
/var/log/postgresql/pgbouncer.log.
Save your progress? Sign up for free
Drill 2: Roles, database and auth_query
One application role, one test database and one role just for PgBouncer. All passwords are lab values:
psql -c "CREATE ROLE tag12_app LOGIN PASSWORD 'lab-tag12'" \
-c "CREATE ROLE tag12_pgbouncer LOGIN PASSWORD 'lab-tag12-bouncer'"
createdb -O tag12_app tag12
PGPASSWORD=lab-tag12 pgbench -h 127.0.0.1 -U tag12_app -i -s 10 -q tag12So that not every application role has to be listed in a file for
PgBouncer, it queries the SCRAM hashes from PostgreSQL via
auth_query. Only a superuser may read
pg_authid – hence a SECURITY DEFINER function
that returns exactly one row and may only be called by
tag12_pgbouncer:
psql -d tag12 <<'EOF'
CREATE SCHEMA pgbouncer AUTHORIZATION tag12_pgbouncer;
CREATE FUNCTION pgbouncer.get_auth(p_usename text)
RETURNS TABLE (username text, password text)
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS
$$ SELECT rolname::text, rolpassword::text FROM pg_authid
WHERE rolname = p_usename AND rolcanlogin $$;
REVOKE ALL ON FUNCTION pgbouncer.get_auth(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(text) TO tag12_pgbouncer;
EOF
psql -d tag12 -Atc "SELECT username, left(password, 20)
FROM pgbouncer.get_auth('tag12_app')"Expected:
CREATE SCHEMA
CREATE FUNCTION
REVOKE
GRANT
tag12_app|SCRAM-SHA-256$4096:X
The function must exist in every database PgBouncer serves – the
auth_query runs in the target database. Now the
configuration. [databases] is deliberately placed at the
end so entries can easily be appended later:
sudo cp -a /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.orig
sudo -u postgres tee /etc/pgbouncer/pgbouncer.ini >/dev/null <<'EOF'
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_user = tag12_pgbouncer
auth_query = SELECT username, password FROM pgbouncer.get_auth($1)
admin_users = tag12_pgbouncer
pool_mode = transaction
max_client_conn = 500
default_pool_size = 10
[databases]
tag12 = host=127.0.0.1 port=5432 dbname=tag12
EOFuserlist.txt contains only auth_user. The
obvious choice would be its SCRAM hash from pg_authid –
let’s try it:
psql -Atc "SELECT format('\"%s\" \"%s\"', rolname, rolpassword) FROM pg_authid
WHERE rolname = 'tag12_pgbouncer'" \
| sudo -u postgres tee /etc/pgbouncer/userlist.txt >/dev/null
sudo systemctl restart pgbouncerOn pg2:
export PGPASSWORD=lab-tag12
B="host=172.16.0.182 port=6432 dbname=tag12 user=tag12_app"
psql "$B" -Atc 'SELECT current_user, inet_client_addr(), inet_server_port()'Expected:
psql: error: connection to server at "172.16.0.182", port 6432 failed: FATAL: server
login failed: wrong password type
The log on pg1 explains it:
sudo grep 'cannot do SCRAM' /var/log/postgresql/pgbouncer.log | tail -1Expected (abridged):
... ERROR S-0x5e17e82e3c80: tag12/[email protected]:5432 cannot do SCRAM
authentication: password is SCRAM secret but client authentication did not provide
SCRAM keys
With a stored SCRAM hash, PgBouncer can only log in to the server if
a client has previously brought the keys along via SCRAM. But
auth_user logs in on its own before there are any clients –
it needs its plaintext password. The file belongs to
postgres and is not readable by other users (640):
echo '"tag12_pgbouncer" "lab-tag12-bouncer"' \
| sudo -u postgres tee /etc/pgbouncer/userlist.txt
sudo systemctl reload pgbouncerOn pg2 – after a failure PgBouncer remembers the
error for up to 15 s (server_login_retry), so wait
briefly:
psql "$B" -Atc 'SELECT current_user, inet_client_addr(), inet_server_port()'Expected:
tag12_app|127.0.0.1|5432
To PostgreSQL, tag12_app comes from
127.0.0.1 – only PgBouncer sees the client IP of
pg2. pg_hba.conf didn’t need to be touched for
this: the line for 127.0.0.1 with
scram-sha-256 already existed.
Save your progress? Sign up for free
Drill 3: Admin console
The virtual database pgbouncer understands
SHOW commands (not SQL):
export PGPASSWORD=lab-tag12-bouncer
A="host=127.0.0.1 port=6432 dbname=pgbouncer user=tag12_pgbouncer"
psql "$A" -Atc 'SHOW CONFIG' \
| grep -E '^(pool_mode|max_prepared_statements|server_reset_query)\|'
psql "$A" -c 'SHOW POOLS' | cut -d'|' -f1-4,7,10Expected (abridged):
max_prepared_statements|200|200|yes
pool_mode|transaction|session|yes
server_reset_query|DISCARD ALL|DISCARD ALL|yes
database | user | cl_active | cl_waiting | sv_active | sv_idle
------------+-----------------+-----------+------------+-----------+---------
pgbouncer | pgbouncer | 1 | 0 | 0 | 0
tag12 | tag12_app | 0 | 0 | 0 | 1
tag12 | tag12_pgbouncer | 0 | 0 | 0 | 1
SHOW CONFIG shows the current value, the default and
whether it can be changed via reload. A pool belongs to a pair of
database and role; cl_ are clients, sv_ server
connections. Other useful commands: SHOW CLIENTS,
SHOW SERVERS, SHOW STATS, RELOAD,
PAUSE/RESUME (for maintenance),
RECONNECT.
Save your progress? Sign up for free
Drill 4: max_client_conn versus default_pool_size
Two limits: max_client_conn (500) limits the clients at
PgBouncer, default_pool_size (10) the server connections
per pool. The sum of all pools must stay below PostgreSQL’s
max_connections. For comparison, we limit
tag12_app directly on the server to 20 connections:
psql -c 'ALTER ROLE tag12_app CONNECTION LIMIT 20'
PGPASSWORD=lab-tag12 pgbench -h 127.0.0.1 -p 5432 -U tag12_app -c 50 -j 4 -T 15 tag12 \
2>&1 | grep -m1 FATALExpected (abridged):
pgbench: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: too
many connections for role "tag12_app"
The same 50 clients from pg2 through PgBouncer:
pgbench -h 172.16.0.182 -p 6432 -U tag12_app -c 50 -j 4 -T 30 tag12 \
| grep -E 'failed|latency average|^tps'While the test is running, on pg1:
psql "$A" -c 'SHOW POOLS' | cut -d'|' -f1-4,7,10 | grep -E 'database|tag12_app'
psql -Atc "SELECT count(*) FROM pg_stat_activity WHERE usename = 'tag12_app'"Expected (abridged):
database | user | cl_active | cl_waiting | sv_active | sv_idle
tag12 | tag12_app | 10 | 40 | 10 | 0
10
And on pg2 at the end:
number of failed transactions: 0 (0.000%)
latency average = 92.426 ms
tps = 540.973176 (without initial connection time)
50 clients, 10 server processes, 40 wait briefly in the queue – and not a single error. That’s exactly what the pooler is for: the database works efficiently with few processes, and the queue lives in cheap PgBouncer.
Save your progress? Sign up for free
Drill 5: Load test with connection setup
PgBouncer helps most with applications that reconnect for every
request (PHP, scripts, serverless). pgbench -C simulates
that – once directly, once through PgBouncer, both times from
pg1 over TCP:
export PGPASSWORD=lab-tag12
pgbench -h 127.0.0.1 -p 5432 -U tag12_app -C -S -c 20 -j 4 -T 20 tag12 \
| grep -E 'average connection|^tps'
pgbench -h 127.0.0.1 -p 6432 -U tag12_app -C -S -c 20 -j 4 -T 20 tag12 \
| grep -E 'average connection|^tps'Expected (values vary with load):
average connection time = 65.787 ms
tps = 60.556349 (including reconnection times)
average connection time = 44.870 ms
tps = 88.845360 (including reconnection times)
Through PgBouncer, the process start and the second login at the
server go away; the server connection is already warm. SCRAM eats the
large remainder: 4096 hash rounds per login, which PgBouncer has to
verify too. Without -C (the connection stays open),
pgbench measures only the queries – then direct and pooled
are close together.
Save your progress? Sign up for free
Drill 6: Pitfalls in transaction mode
Three pool modes side by side: an entry in [databases]
may override the mode. Append and reload:
cat <<'EOF' | sudo -u postgres tee -a /etc/pgbouncer/pgbouncer.ini
tag12_session = host=127.0.0.1 port=5432 dbname=tag12 pool_mode=session
tag12_stmt = host=127.0.0.1 port=5432 dbname=tag12 pool_mode=statement
EOF
sudo systemctl reload pgbouncer
PGPASSWORD=lab-tag12-bouncer psql "$A" -c 'SHOW DATABASES' | cut -d'|' -f1,6,10 \
| grep -E 'name|tag12'Expected:
name | pool_size | pool_mode
tag12 | 10 |
tag12_session | 10 | session
tag12_stmt | 10 | statement
Empty means: global value (transaction). Now on
pg2 – load runs in the background so the server
connections really switch:
pgbench -h 172.16.0.182 -p 6432 -U tag12_app -S -c 30 -j 4 -T 15 tag12 \
>/dev/null 2>&1 &
psql "$B" -c "SET work_mem = '1GB'" -c 'SHOW work_mem' -c 'SHOW work_mem' \
-c 'SHOW work_mem' | grep -E 'MB|GB'
psql "$B" -c 'PREPARE q(int) AS SELECT abalance FROM pgbench_accounts WHERE aid = $1' \
-c 'EXECUTE q(1)' -c 'EXECUTE q(2)' -c 'EXECUTE q(3)' 2>&1 \
| grep -E 'PREPARE|ERROR|^ +0'
waitExpected (pattern varies):
1GB
4MB
4MB
PREPARE
ERROR: prepared statement "q" does not exist
ERROR: prepared statement "q" does not exist
0
Each -c is its own transaction and lands on some server
connection. Worse than the lost SET: the connection with
work_mem = 1GB is now passed on to other clients – in
transaction mode PgBouncer resets nothing
(server_reset_query applies only in session mode). The
cross-check with session:
psql "host=172.16.0.182 port=6432 dbname=tag12_session user=tag12_app" \
-c "SET work_mem = '1GB'" -c 'SHOW work_mem' -c 'SHOW work_mem' | grep -E 'MB|GB'
psql "host=172.16.0.182 port=6432 dbname=tag12_stmt user=tag12_app" \
-c 'BEGIN' -c 'SELECT 1' 2>&1 | head -1Expected:
1GB
1GB
FATAL: transaction blocks not allowed in statement pooling mode
Rules for transaction mode:
- Session settings only as
SET LOCALinside a transaction or viaALTER ROLE … SET– never a bareSET. - Prepared statements only at the protocol level (drivers like pgx,
JDBC, psycopg 3): PgBouncer tracks them itself since 1.21 when
max_prepared_statements> 0 (default 200). SQLPREPARE/EXECUTEdoes not work. - No
LISTEN, no session advisory locks, no temporary tables across transaction boundaries.
pgbench -M prepared provides the proof for
protocol-level prepared statements:
pgbench -h 172.16.0.182 -p 6432 -U tag12_app -M prepared -S -c 20 -j 4 -T 10 tag12 \
| grep -E 'failed|^tps'
unset PGPASSWORDExpected (abridged):
number of failed transactions: 0 (0.000%)
tps = 4471.762379 (without initial connection time)
On pg1, dispose of the polluted connection –
RECONNECT closes the pool’s server connections after their
current transaction:
PGPASSWORD=lab-tag12-bouncer psql "$A" -c 'RECONNECT tag12'Expected: RECONNECT
Save your progress? Sign up for free
Exercise: Speed Drills
- Show all pools with waiting clients. Target time: 30 s
- Show how many transactions PgBouncer has relayed for
tag12. Target time: 30 s - Reload the PgBouncer configuration without disconnecting clients. Target time: 10 s
- Find the last login error in the PgBouncer log. Target time: 30 s
Solution
export PGPASSWORD=lab-tag12-bouncer
psql "$A" -c 'SHOW POOLS' | cut -d'|' -f1-4
psql "$A" -c 'SHOW STATS' | cut -d'|' -f1,3 | grep -E 'database|tag12 '
sudo systemctl reload pgbouncer
sudo grep -E 'failed|error' /var/log/postgresql/pgbouncer.log | tail -1
unset PGPASSWORDExpected (abridged):
database | total_xact_count
tag12 | 248814
systemctl reload sends SIGHUP, just like
RELOAD in the admin console.
Save your progress? Sign up for free
Checkpoint
systemctl is-active pgbouncerExpected: active
sudo ss -tlnp | grep 6432Expected: 0.0.0.0:6432 and
[::]:6432.
PGPASSWORD=lab-tag12-bouncer psql "$A" -Atc 'SHOW CONFIG' | grep '^pool_mode|'Expected:
pool_mode|transaction|session|yes
On pg2:
PGPASSWORD=lab-tag12 psql "$B" -Atc 'SELECT inet_client_addr()'Expected: 127.0.0.1 – PostgreSQL sees
PgBouncer, not pg2.
Challenge
Shrink the pool for new server connections to 5 – without a restart and without changing the file – and prove under a load of 30 clients that never more than 5 server connections are active.
Hint: The admin console knows SET for
configuration parameters.
Solution
PGPASSWORD=lab-tag12-bouncer psql "$A" -c 'SET default_pool_size = 5'On pg2:
PGPASSWORD=lab-tag12 pgbench -h 172.16.0.182 -p 6432 -U tag12_app -S -c 30 -j 4 \
-T 10 tag12 >/dev/null 2>&1 &On pg1, while the test is running:
PGPASSWORD=lab-tag12-bouncer psql "$A" -c 'SHOW POOLS' | cut -d'|' -f1-4,7,10 \
| grep -E 'database|tag12_app'Expected:
database | user | cl_active | cl_waiting | sv_active | sv_idle
tag12 | tag12_app | 9 | 21 | 5 | 0
SET in the admin console applies until the next restart
or RELOAD – then the file counts again. Permanently:
pool_size=5 on the database entry or
default_pool_size in pgbouncer.ini.
Save your progress? Sign up for free
Cleanup
PgBouncer back to the package state (only localhost, no
databases), test data and roles gone:
sudo cp -a /etc/pgbouncer/pgbouncer.ini.orig /etc/pgbouncer/pgbouncer.ini
sudo -u postgres truncate -s 0 /etc/pgbouncer/userlist.txt
sudo rm /etc/pgbouncer/pgbouncer.ini.orig
sudo systemctl restart pgbouncer
dropdb tag12
psql -c 'DROP ROLE tag12_app' -c 'DROP ROLE tag12_pgbouncer'
sudo ss -tlnp | grep 6432Expected (abridged):
DROP ROLE
DROP ROLE
LISTEN 0 128 127.0.0.1:6432 0.0.0.0:* users:(("pgbouncer",pid=...,fd=9))
LISTEN 0 128 [::1]:6432 [::]:* users:(("pgbouncer",pid=...,fd=8))
Interview Note
- “Why not just set max_connections to 2000?” – Every connection is a process with its own memory; many active processes fight over CPU and locks. Throughput rises up to roughly a few connections per CPU core, after that it drops. A pooler keeps the number small.
- “Session or transaction mode?” – Transaction for web applications
with short transactions; session if the application needs session state
(
SET,LISTEN, advisory locks, temporary tables). - “How does PgBouncer authenticate against PostgreSQL?” – With
auth_userandauth_queryit fetches the SCRAM hashes; the client login via SCRAM supplies the keys it uses to log in to the server.
Enterprise Note
- PgBouncer close to the application (sidecar, separate service) or in
front of the database; several instances behind a load balancer,
so_reuseportfor several processes on one host (PgBouncer is single-threaded). - TLS on both sides:
client_tls_sslmode/client_tls_cert_filefor clients,server_tls_sslmode = verify-fullto the database. auth_userpassword from a secret store; alternativelyauth_type = certorhbawith its ownauth_hba_file.- Monitor:
SHOW POOLS(cl_waiting,maxwait) viapgbouncer_exporter; a growingmaxwaitmeans the pool is too small or the queries too slow. - In Kubernetes, the CNPG
Poolertakes over exactly this role (Day 17).