0Pricing
SQL Academy · Lesson

Connection Pooling: PgBouncer

Run PgBouncer in transaction-pooling mode, size pools correctly, and avoid the prepared-statement trap.

Connection Pooling: PgBouncer is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Pool Connections?

Every Postgres connection is a separate OS process — memory and CPU heavy. Apps that open and close connections per request quickly exhaust the server. Pooling reuses a small set of long-lived connections.

Per-Connection Cost

A typical Postgres backend uses 5–10 MB of RAM. 500 connections = ~5 GB RAM just for backends. PgBouncer can multiplex 1000s of clients onto 50 backend connections.

Three Pooling Modes

  • Session — client gets a dedicated connection for the whole session
  • Transaction — client gets a connection per transaction
  • Statement — connection per statement (rarely used)

Transaction Mode (Recommended)

Each transaction gets a server connection. The same client may use different connections across transactions.

# pgbouncer.ini
[databases]
mydb = host=primary port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
listen_port = 6432
max_client_conn = 1000
default_pool_size = 50

Transaction Mode Caveats

Features that require session state break:

  • Prepared statements (need same backend across calls)
  • SET LOCAL ... (per-transaction is fine; SET ... persisting across is not)
  • LISTEN / NOTIFY (need persistent connection)
  • Cursors that outlive a transaction

Session Mode

Use when transaction mode breaks your app — but you get fewer concurrent clients.

Sizing Pools

Rule of thumb: default_pool_size ≈ vCPU count × 2 + spindles. Too high = context switching kills throughput; too low = waiting on pool.

PgBouncer in Front of HAProxy

Typical production stack:

App → HAProxy (read/write routing) → PgBouncer (pooling) → Postgres

Prepared Statements in Transaction Mode

Recent PgBouncer (1.21+) supports protocol-level prepared statements in transaction mode. Older versions: use simple queries or session mode.

Monitoring PgBouncer

Connect to the admin db (special database):

psql -p 6432 -U pgbouncer pgbouncer

-- Commands:
SHOW STATS;
SHOW POOLS;
SHOW CLIENTS;
SHOW SERVERS;

Alternatives

  • pgpool-II — pooling + load balancing + query rewriting
  • Odyssey — Yandex's pooler, multi-threaded
  • Driver-side pools — usually combined with PgBouncer for cluster-wide pooling

Application Pool + PgBouncer

Apps typically run their own connection pool (HikariCP, pgxpool) AND go through PgBouncer. Two-layer pooling — app pool keeps connections to the bouncer; bouncer multiplexes them onto Postgres.

Recap

PgBouncer is essential at any nontrivial scale.

  • Transaction mode is the standard
  • Watch out for prepared statements / SET / LISTEN
  • Pool size ~ vCPU × 2
  • Monitor with SHOW POOLS

Quick Check

Which PgBouncer mode multiplexes the most clients on the fewest server connections?

Frequently asked questions

Is the “Connection Pooling: PgBouncer” lesson free?

Yes — the full text of “Connection Pooling: PgBouncer” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Connection Pooling: PgBouncer”?

Run PgBouncer in transaction-pooling mode, size pools correctly, and avoid the prepared-statement trap. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Connection Pooling: PgBouncer” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. pg_stat_statements: Top Queries
  2. pgBadger for Log Analysis
  3. Connection Pooling: PgBouncer
  4. Capacity Planning and Bloat Audits
← Back to SQL Academy