Lab 25: Put a pool in front of PostgreSQL and measure what it costs you

Objective

A connection pool is usually introduced as a performance improvement. It is not one, and this lab measures that directly: at a concurrency the server can handle by itself, the pooled path was nearly half the throughput of the direct path.

What the pool does is let the application run at a concurrency the server cannot handle at all. You will see the direct connection fail outright at 100 clients — with a message that names the real limit — while the pool serves the same 100 clients through 10 server connections without noticing.

Both facts matter. A pool deployed for the first reason is a disappointment. A pool deployed for the second is the difference between an application that works and one that does not.

Architecture

flowchart LR
    C["100 application clients"] --> B["PgBouncer 1.24.1\nport 6432\npool_mode = transaction\nmax_client_conn = 500\ndefault_pool_size = 10"]
    B --> P["PostgreSQL 18.6\nport 5432\nmax_connections = 100"]
    C2["100 clients, direct"] -.->|fails| P
    B --> A["admin console\nSHOW POOLS / STATS / CONFIG"]

Requirements

• A PostgreSQL 18 primary reachable over the network, with a pg_hba.conf rule for the pool’s address.
• A second host or container for PgBouncer.
• A role whose SCRAM verifier you can read from pg_authid, which requires superuser.

Scenario

An application with 40 worker processes on each of 6 hosts opens a connection per worker. The database has max_connections = 100. The application team’s proposal is to raise max_connections to 500.

Tasks

Task 1 — Install and configure

LAB="$HOME/rbpg-lab-25"
mkdir -p "$LAB"

docker exec rbpg-pool bash -c 'apt-get update -qq && apt-get install -y -qq pgbouncer'
docker exec rbpg-pool pgbouncer --version

$ pgbouncer --version

PgBouncer 1.24.1
libevent 2.1.12-stable

PG_IP=$(docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' rbpg-lab01)

docker exec rbpg-pool bash -c "mkdir -p /etc/pgbouncer /var/log/pgbouncer /var/run/pgbouncer
cat > /etc/pgbouncer/pgbouncer.ini <<EOF
[databases]
lab25 = host=$PG_IP port=5432 dbname=lab25

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 10
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
admin_users = postgres
stats_users = postgres
EOF"

The two numbers that define the pool are max_client_conn = 500 — how many clients may connect to PgBouncer — and default_pool_size = 10 — how many connections PgBouncer opens to PostgreSQL per database and user pair. The ratio between them is the multiplexing factor.

Task 2 — The auth file

PgBouncer authenticates clients itself, so it needs the password verifiers:

SCRAM=$(docker exec -u postgres rbpg-lab01 psql -X -tAc \
  "SELECT rolpassword FROM pg_authid WHERE rolname='app';")
docker exec rbpg-pool bash -c "printf '\"app\" \"%s\"\n' '$SCRAM' > /etc/pgbouncer/userlist.txt"
docker exec rbpg-pool bash -c "cut -c1-40 /etc/pgbouncer/userlist.txt"

$ read rolpassword from pg_authid and write it into userlist.txt

"app" "SCRAM-SHA-256$4096:oaboZ+mbtUzpYY

Task 3 — Start it, and meet the first error

docker exec -d rbpg-pool pgbouncer /etc/pgbouncer/pgbouncer.ini
sleep 2
docker exec rbpg-pool tail -2 /var/log/pgbouncer/pgbouncer.log

$ start pgbouncer as root and read the log

2026-08-28 06:30:48.563 UTC [5189] FATAL PgBouncer should not run as root

docker exec rbpg-pool chown -R postgres:postgres /etc/pgbouncer /var/log/pgbouncer /var/run/pgbouncer
docker exec -d -u postgres rbpg-pool pgbouncer /etc/pgbouncer/pgbouncer.ini
sleep 3

docker exec -e PGPASSWORD=app-not-a-real-secret rbpg-pool \
  psql -X -h 127.0.0.1 -p 6432 -U app -d lab25 \
  -c "SELECT 'through the pool' AS via, current_user, inet_server_port() AS backend_port;"

$ connect to PgBouncer and ask the backend which port it is on

       via        | current_user | backend_port 
------------------+--------------+--------------
through the pool | app          |         5432
(1 row)

inet_server_port() returns 5432 — the query really did reach PostgreSQL. The client connected to 6432 and never knew.

Task 4 — The multiplexing

POOL_IP=$(docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' rbpg-pool)

docker exec -d rbpg-lab01 bash -c "PGPASSWORD=app-not-a-real-secret \
  /usr/lib/postgresql/18/bin/pgbench -h $POOL_IP -p 6432 -U app \
  -c 100 -j 8 -T 30 -M extended --no-vacuum lab25"
sleep 12

docker exec -u postgres rbpg-lab01 psql -X -d lab25 -c "
  SELECT count(*) AS backend_connections,
         count(*) FILTER (WHERE state='active') AS active
  FROM pg_stat_activity WHERE datname='lab25' AND backend_type='client backend';"

docker exec -e PGPASSWORD=pgpass-not-a-real-secret rbpg-pool \
  psql -X -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer -c "SHOW POOLS;" \
  | tee "$LAB/multiplexing.txt"

$ run 100 pgbench clients through the pool, then count from both sides

 backend_connections | active 
---------------------+--------
                11 |      4
(1 row)

database  |   user    | cl_active | cl_waiting | ...
-----------+-----------+-----------+------------+----
lab25     | app       |        10 |         90 | ...

This is the entire value proposition in two numbers. cl_active = 10, cl_waiting = 90, and the backend has 11 connections total.

Ninety clients are waiting, and that is not a problem — each transaction takes under a millisecond, so a client’s wait for a server connection is measured in microseconds. The pool is doing exactly what it is for: converting a hundred mostly-idle connections into ten busy ones.

Task 5 — What happens without the pool

docker exec -u postgres rbpg-lab01 psql -X -c "SHOW max_connections;"

docker exec -e PGPASSWORD=app-not-a-real-secret rbpg-lab01 \
  /usr/lib/postgresql/18/bin/pgbench -h 127.0.0.1 -U app \
  -c 100 -j 8 -T 15 -M extended --no-vacuum lab25 | tee "$LAB/exhaustion.txt"

$ 100 pgbench clients straight to PostgreSQL

 max_connections 
-----------------
100
(1 row)

pgbench (18.6 (Debian 18.6-1.pgdg13+2))
pgbench: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute
pgbench: error: could not create connection for client 48
pgbench: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

Client 48 of 100 could not connect. The message names the real mechanism: max_connections is 100, but the last few slots are held back by reserved_connections and superuser_reserved_connections so that an administrator can still get in when the application has exhausted the rest.

That reservation is why “we have max_connections = 100, so 100 clients should work” is wrong, and why an application sized exactly to max_connections fails.

The pool at the same concurrency:

$ the same 100 clients through PgBouncer

number of clients: 100
number of failed transactions: 0 (0.000%)
tps = 2600.354581 (without initial connection time)

Task 6 — And what the pool costs

Drop to a concurrency the server can handle directly, and compare:

{
echo "DIRECT, 80 clients:"
docker exec -e PGPASSWORD=app-not-a-real-secret rbpg-lab01 \
  /usr/lib/postgresql/18/bin/pgbench -h 127.0.0.1 -U app \
  -c 80 -j 8 -T 15 -M extended --no-vacuum lab25 | grep -E "^tps|failed"
echo "POOLED, 80 clients:"
docker exec -e PGPASSWORD=app-not-a-real-secret rbpg-lab01 \
  /usr/lib/postgresql/18/bin/pgbench -h "$POOL_IP" -p 6432 -U app \
  -c 80 -j 8 -T 15 -M extended --no-vacuum lab25 | grep -E "^tps|failed"
} | tee "$LAB/throughput.txt"

$ 80 pgbench clients, direct then pooled

DIRECT, 80 clients:
number of failed transactions: 0 (0.000%)
tps = 5124.587229 (without initial connection time)

POOLED, 80 clients:
number of failed transactions: 0 (0.000%)
tps = 2627.246685 (without initial connection time)

5124 tps direct against 2627 tps pooled. The pool halved throughput.

Task 7 — What transaction pooling breaks

docker exec -e PGPASSWORD=pgpass-not-a-real-secret rbpg-pool \
  psql -X -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer -c "SHOW CONFIG;" \
  | grep -E "pool_mode|max_client_conn|default_pool_size|server_reset_query|server_lifetime|server_idle_timeout|query_wait_timeout"

$ SHOW CONFIG from the admin console, filtered

 default_pool_size           | 10
max_client_conn             | 500
pool_mode                   | transaction
query_wait_timeout          | 120
server_idle_timeout         | 600
server_lifetime             | 3600
server_reset_query          | DISCARD ALL

server_reset_query = DISCARD ALL is the whole story of what transaction pooling breaks. Between transactions, PgBouncer runs DISCARD ALL on the server connection before handing it to the next client, which throws away temporary tables, prepared statements, cursors, session SET values, LISTEN registrations and session-level advisory locks.

Anything your application expects to survive from one transaction to the next will not.

Task 8 — The statistics worth graphing

docker exec -e PGPASSWORD=pgpass-not-a-real-secret rbpg-pool \
  psql -X -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer -c "SHOW STATS;"

$ SHOW STATS from the admin console

 database  | total_server_assignment_count | total_xact_count | total_query_count | total_received | ...
-----------+-------------------------------+------------------+-------------------+----------------+----
lab25     |                        208296 |           208296 |           1458000 |      161174526 | ...
pgbouncer |                             0 |                7 |                 7 |              0 | ...

total_server_assignment_count equals total_xact_count at 208,296 — one server assignment per transaction, which is exactly what transaction pooling means. Against total_query_count of 1,458,000, that is seven queries per transaction sharing one server connection.

The three things to alert on:

• cl_waiting from SHOW POOLS persistently above zero. Clients are queueing for a server connection, and default_pool_size is too small.
• maxwait from SHOW POOLS above a second. How long the longest-waiting client has been waiting right now.
• query_wait_timeout errors in the log — the pool giving up on a client that waited too long, which surfaces in the application as a connection error rather than a slow query.

Validation

test -s "$LAB/multiplexing.txt" && echo "OK multiplexing"
test -s "$LAB/exhaustion.txt"   && echo "OK exhaustion"
test -s "$LAB/throughput.txt"   && echo "OK throughput"

grep -q "remaining connection slots" "$LAB/exhaustion.txt" && echo "OK exhaustion reproduced"

# The check that proves multiplexing:
docker exec -u postgres rbpg-lab01 psql -X -d lab25 -c \
  "SELECT count(*) AS backend_connections FROM pg_stat_activity
   WHERE datname='lab25' AND backend_type='client backend';"

Questions to answer without looking anything up:

1. max_connections = 100 and exactly 100 clients try to connect. What happens and why?
2. At 80 clients the pool was slower than a direct connection. Should you remove it?
3. SHOW POOLS reports cl_waiting = 90 and maxwait = 0. Is that a problem?
4. Your application sets search_path once at connection time and then runs queries. What happens behind a transaction pool?
5. A connection sitting idle in the pool is blocking other clients on an advisory lock. How did it get there?

Expected Outcome

You have deployed a pool, served 100 clients through 10 server connections, reproduced the failure the pool prevents, and measured the throughput it costs when that failure would not have happened.

The decision it supports:

• Client count above what max_connections can hold, transactions short, connections mostly idle — deploy a pool in transaction mode and audit the application for session state.
• Client count comfortably inside max_connections — a pool makes things slower and adds a component that can fail. Do not add one just because it is standard practice.
• Never solve it by raising max_connections into the hundreds. That makes the server slower at everything, and the memory arithmetic from Lab 14 applies to every one of those connections.

Troubleshooting

PgBouncer will not start: auth_file errors. The file must contain the role name and its verifier in the exact quoted form PgBouncer expects, one per line. Generate it from pg_shadow rather than typing it, and check the file’s permissions — it holds credentials.

ERROR: password authentication failed through the pool but not directly. The verifier in the auth file does not match the role’s current one, usually because the password was rotated on the server and not in the pool. This is the coupling a pool introduces: the credential now lives in two places.

Client count rises and max_client_conn is reached. That bound is on client-side connections, separate from default_pool_size on the server side. Both need sizing; the first is usually much larger.

Server connections do not multiplex. pool_mode is session, which holds a server connection for the life of the client connection. transaction mode is what produces the multiplexing this lab measures.

The application breaks in transaction mode. Expected, and it is Task 7. Session state does not survive: prepared statements, SET at session scope, LISTEN/NOTIFY, advisory locks held across transactions, and WITH HOLD cursors. Audit the application before switching modes.

Throughput dropped after adding the pool. Measured here, and it is the honest result: a direct connection was nearly twice as fast at 80 clients (5,124 against 2,627 tps). A pool shapes capacity; it is not a speed optimisation, and adding one to a cluster that was not connection- bound makes things slower and adds a component that can fail.

Cleanup

docker exec rbpg-pool pkill pgbouncer
docker rm -f rbpg-pool
docker exec -u postgres rbpg-lab01 psql -X -c "DROP DATABASE IF EXISTS lab25;"
docker exec -u postgres rbpg-lab01 psql -X -c "DROP ROLE IF EXISTS app;"
docker exec rbpg-lab01 sed -i '/lab25hba/d' /etc/postgresql/18/main/pg_hba.conf
docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM RESET listen_addresses;"
docker exec rbpg-lab01 pg_ctlcluster 18 main restart

Production notes

• Deploy a pool when the client count exceeds what max_connections can hold with acceptable memory, transactions are short, and connections are mostly idle. Those three conditions together, not one of them.
• Do not add a pool to a cluster that is comfortably inside max_connections. It costs throughput and adds a failure domain in front of the database.
• Never respond to connection exhaustion by raising max_connections into the hundreds. Every connection is a backend with its own memory, and the work_mem arithmetic applies to all of them.
• Audit the application for session state before choosing transaction mode. The breakages are silent in testing and specific in production.
• A pool is a second place credentials live. Rotation now has two steps, and forgetting the second one is an outage.
• Graph the pool’s own statistics — client and server connection counts, maxwait — alongside the database’s. maxwait climbing is the pool telling you default_pool_size is too small.

What You Learned

• A pool multiplexes many client connections onto few server connections, and that is the whole of what it does.
• Transaction mode is what produces the multiplexing; session mode holds a server connection for the client’s lifetime.
• Transaction mode breaks session state — prepared statements, session SET, LISTEN/NOTIFY, advisory locks, WITH HOLD cursors.
• A pool is not a speed tool. Measured here it was nearly twice as slow as a direct connection at 80 clients.
• It is a capacity-shaping tool, and it earns its place when the client count would otherwise exceed what the server can hold.
• It introduces a second credential store and a component that can fail in front of the database.