WIUNIX ACADEMY
PgBouncer transaction pooling: prepared statements, session traps and queue diagnosis
Page 2 of 3. Review the earlier steps and prerequisites before continuing.
YOUR SYSTEM. YOUR STEPS.
Where are you working?
Choose your OS and version. The prerequisites, commands and troubleshooting follow your environment.
USE YOUR OWN VALUES
Configure this tutorial
Set the example values for your environment; related explanations and copy panels update together. Never enter passwords, private keys or tokens. Values stay in this tab and are not sent to the server or share links. Check actual DNS, certificates and network policy before running commands.
Different IP addresses, domains or ports? Open the settings below, change the values and select “Apply to tutorial”, then copy the updated commands.
Set values for my environment5 editable values
Size server connections across all pools
The default pool size is per database/user pair, not a global connection cap. Add reserve pools and direct maintenance connections, then leave capacity below PostgreSQL max_connections for administration and other applications. Measure active backend work, queue wait and transaction duration before growing the pool. A long idle-in-transaction client occupies a backend; more server connections may amplify CPU and I/O contention instead of increasing throughput.
LET’S BUILD
Back up and edit only the approved staging mapping
On the PgBouncer host, retain a private before copy and open the existing config. Existing backup must cause a stop. Keep authentication, TLS, listeners, database mapping and user overrides intact. The following panel is a settings fragment for review, never a replacement config. Applying a global pool_mode affects every mapping without its own override; use a dedicated staging instance or explicitly scoped per-database mapping.
Run as the existing authorized owner of the configuration; do not change its ownership or permissions to make editing work.
Staging PgBouncer host
test ! -e '/etc/pgbouncer/pgbouncer.ini.before-transaction' || exit 1
(umask 077; cp --no-preserve=mode -- '/etc/pgbouncer/pgbouncer.ini' '/etc/pgbouncer/pgbouncer.ini.before-transaction') || exit 1
nano '/etc/pgbouncer/pgbouncer.ini'A recoverable before copy exists; only the intended scope is edited.
Reviewed settings fragment
In the dedicated staging instance, merge these keys into the existing pgbouncer section. A nonzero cache budget enables protocol statement tracking. Do not create a duplicate section or assume this fragment overrides an explicit per-user or per-database setting. Retain enough PostgreSQL capacity as calculated above.
[pgbouncer]
pool_mode = transaction
max_prepared_statements = 100
default_pool_size = 10LET’S BUILD
Reload and confirm effective settings
Use the existing admin connection. RELOAD does not mean every established application session instantly receives a clean lifecycle. Coordinate application pool recycling through its owning deployment procedure if required, without dropping production users. Inspect active settings and pools after reload; errors in the private PgBouncer log must be resolved before driver tests.
Staging administration workstation
/usr/lib/postgresql/18/bin/psql -X -v ON_ERROR_STOP=1 'service=academy_pool_admin' -c 'RELOAD;' -c 'SHOW CONFIG;' -c 'SHOW DATABASES;' -c 'SHOW POOLS;'The chosen mapping is transaction-pooled with nonzero preparation tracking.
LET’S BUILD
Exercise protocol preparation with the actual driver
This read-only Psycopg3 example uses an already provisioned client environment; do not install packages on the server for it. It forces protocol preparation and prints backend identity after each transaction. A fixed PID is not proof of a defect: low contention may reuse one backend. Follow this smoke test with concurrent application-specific transactions that exercise real parameter types, connection recycling and error recovery. Do not use psql SQL PREPARE as a substitute for a driver test.
The supplied recipe specifically requires Psycopg3.2+, PgBouncer1.22+ and client libpq17+; generic PgBouncer1.21 protocol tracking is not sufficient for this supported Psycopg path. Stop on an older combination.
Provisioned application test client
python3 - <<'PY'
import psycopg
from psycopg import pq
print("Psycopg", psycopg.__version__, "libpq", pq.version())
assert pq.version() >= 170000, "This recipe requires libpq17+"
with psycopg.connect(service='academy_pool_app', autocommit=True) as c:
for n in range(20):
with c.transaction():
row = c.execute('SELECT pg_backend_pid(), %s::integer + 1', (n,), prepare=True).fetchone()
assert row[1] == n + 1
print(row)
PYAll results are correct without missing/duplicate prepared-statement errors. This is a smoke test, not full application acceptance.
LET’S BUILD
Read queueing separately from database execution
Collect these views during a representative staging load. Rising cl_waiting with all servers active means demand exceeds currently available pool service; inspect long transactions and database saturation before increasing connections. More clients alone do not mean more database work. Compare rates and wait duration over an interval, not one instantaneous screenshot. Prepared-statement reparse after DDL or changed result types needs driver/schema rollout coordination; use the documented reconnect lifecycle in a staging window rather than clearing all production sessions.
Staging administration workstation
/usr/lib/postgresql/18/bin/psql -X 'service=academy_pool_admin' -c 'SHOW POOLS;' -c 'SHOW STATS;' -c 'SHOW CLIENTS;' -c 'SHOW SERVERS;'Queue wait, active servers and transaction duration support a specific bottleneck hypothesis.