Pool Modes and Sizing

Understand session, transaction, and statement pooling in pgbouncer — their trade-offs, caveats, and how to size the pool for your Postgres.

Most teams bolt pgbouncer in front of Postgres and leave it in session mode. This course shows you exactly what each pool mode does to your server connections and why the choice matters. We build a two-service stand — Postgres with a deliberate connection cap and pgbouncer in front — and walk every mode hands-on: how server connections are assigned in session mode, how transaction mode multiplexes thirty clients through five server slots, and why statement mode breaks ordinary transactions. Then we face the caveats that catch engineers off guard: session-level SET parameters that vanish after a reconnect, prepared statements that disappear between transactions, and temporary tables that evaporate mid-session. We finish with the sizing knobs — default_pool_size, max_client_conn, reserve_pool_size — and the pgbouncer admin console commands you reach for whenever something looks wrong.

What you'll build

• Explain what session, transaction, and statement pooling each do to server connections
• Switch pool_mode and reload pgbouncer without dropping connections
• Explain why SET, prepared statements, and temp tables can break in transaction pooling
• Configure max_client_conn, default_pool_size, and reserve_pool_size
• Read SHOW POOLS, SHOW STATS, and SHOW CLIENTS to diagnose a saturated pool
• Describe the correct app → pgbouncer → Postgres connection architecture

Contents

1. Two services, one course
2. Two services, declared
3. Cap the connections on purpose
4. Authentication the simple way
5. The three knobs that matter most
6. The table we run queries against
7. Deterministic data, every time
8. Start the stand
9. Connect directly to Postgres
10. Load the schema and seed the data
11. Confirm the data is there
12. Session pooling: one client, one server slot
13. Session state lives in the connection
14. SET survives in session mode
15. Spawn multiple clients at once
16. Five unique backends, five reused
17. SHOW POOLS: the heartbeat of the pooler
18. Reading a quiet pool
19. One line changes everything
20. Reload without dropping connections
21. Confirm the new mode
22. A simple load script
23. Ten clients, five server connections
24. Many clients, few backends
25. The caveat that surprises most teams
26. SET in a single connection — works fine
27. Within one connection, SET holds
28. A new connection gets the default
29. Prepared statements and transaction pooling
30. Temporary tables are session objects
31. A checklist to read before you migrate
32. Statement mode — the most aggressive option
33. Reload into statement mode
34. Multi-statement transactions are forbidden
35. Back to transaction mode
36. How to size a pool
37. Double the pool, add a reserve
38. Apply the sizing change
39. More server connections, fewer waiters
40. SHOW STATS — throughput at a glance
41. SHOW CLIENTS — inspect individual connections
42. Inspect active client connections
43. The server side: pg_stat_activity
44. The anti-pattern: connecting directly at scale
45. The right architecture
46. SHOW CONFIG — read the live configuration
47. The production checklist
48. The stand at rest