Skip to content
Hands-on IT IT Drills

Day 12: Connection Pooling with PgBouncer

Goal: PgBouncer runs on pg1, authenticates via SCRAM through an auth_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, SQL PREPARE, 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 6432

Expected (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 tag12

So 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
EOF

userlist.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 pgbouncer

On 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 -1

Expected (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 pgbouncer

On 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,10

Expected (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 FATAL

Expected (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'
wait

Expected (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 -1

Expected:

 1GB
 1GB
FATAL:  transaction blocks not allowed in statement pooling mode

Rules for transaction mode:

  • Session settings only as SET LOCAL inside a transaction or via ALTER ROLE … SET – never a bare SET.
  • 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). SQL PREPARE/EXECUTE does 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 PGPASSWORD

Expected (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

  1. Show all pools with waiting clients. Target time: 30 s
  2. Show how many transactions PgBouncer has relayed for tag12. Target time: 30 s
  3. Reload the PgBouncer configuration without disconnecting clients. Target time: 10 s
  4. 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 PGPASSWORD

Expected (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 pgbouncer

Expected: active

sudo ss -tlnp | grep 6432

Expected: 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 6432

Expected (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_user and auth_query it 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_reuseport for several processes on one host (PgBouncer is single-threaded).
  • TLS on both sides: client_tls_sslmode/client_tls_cert_file for clients, server_tls_sslmode = verify-full to the database.
  • auth_user password from a secret store; alternatively auth_type = cert or hba with its own auth_hba_file.
  • Monitor: SHOW POOLS (cl_waiting, maxwait) via pgbouncer_exporter; a growing maxwait means the pool is too small or the queries too slow.
  • In Kubernetes, the CNPG Pooler takes over exactly this role (Day 17).