PgBouncer transaction mode: pool sizing under load

Meta description: PgBouncer transaction pooling internals — prepared statement pitfalls, the pool sizing formula for OLTP workloads, and the metrics that warn you before saturation hits.

Tags: backend architecture microservices api devops

---

TL;DR

Transaction-mode pooling in PgBouncer dramatically increases connection efficiency but silently breaks named prepared statements. The right pool size is not max_connections / num_app_servers — it follows a queuing-theory formula tied to your workload’s latency distribution. Watch cl_waiting and sv_idle in SHOW POOLS before your users feel it.

---

Why connection pooling still bites teams in 2026

In my experience building production systems, the single most common PostgreSQL performance regression during scale-up is not a missing index or a slow query — it’s connection exhaustion. PostgreSQL spawns a process per connection. At 500 concurrent connections, you’re managing 500 OS processes, each consuming roughly 5–10 MB of shared memory overhead. Beyond ~300 connections on a typical 8-core instance, throughput plateaus and latency spikes as the kernel scheduler thrashes.

PgBouncer exists to solve exactly this. But most teams configure it wrong.

---

Session vs. transaction mode: the trade-off table

PgBouncer offers three pooling modes:

Mode	Server connection held	Prepared statements	Advisory locks	SET variables
Session	Full client session	Full support	Supported	Supported
Transaction	Per transaction only	Named: broken	Broken	Broken
Statement	Per statement	Broken	Broken	Broken

Transaction mode is the only mode that delivers meaningful multiplexing — a pool of 50 server connections can serve hundreds of concurrent application connections, because the server connection is released the moment COMMIT or ROLLBACK fires. Session mode barely helps if your transactions are long-lived.

The catch: named prepared statements do not survive transaction-mode pooling. When your ORM calls PREPARE my_stmt AS SELECT ... in one transaction and EXECUTE my_stmt in the next, PgBouncer may route the second call to a different backend process that has never seen my_stmt. Result: ERROR: prepared statement "my_stmt" does not exist.

The fix

Disable server-side prepared statements at the driver level and use protocol-level unnamed statements instead:

// HikariCP + PostgreSQL JDBC
val config = HikariConfig().apply {
    jdbcUrl = "jdbc:postgresql://localhost/mydb?prepareThreshold=0"
    // prepareThreshold=0 disables named prepared statements entirely
    maximumPoolSize = 20
}

For Node.js with pg, pass { statement_timeout: ... } and avoid pg’s name field on queries. For SQLAlchemy, set use_native_hstore=False and configure the dialect accordingly.

---

The pool sizing formula

Most teams treat max_connections as a target. It’s a ceiling, not a goal.

The formula grounded in queuing theory (M/M/1/K model adapted for database workloads):

server_pool_size = (target_throughput_tps × avg_transaction_latency_s) × safety_factor

For variable-latency OLTP — where p99 latency is 10–20× your p50 — use a safety factor of 1.5 to 2.0. Example:

• Target: 2,000 TPS
• Average transaction latency: 8 ms (0.008 s)
• Safety factor: 1.5

server_pool_size = 2000 × 0.008 × 1.5 = 24

Twenty-four server connections handle 2,000 TPS with headroom. Your max_connections in PostgreSQL should be (server_pool_size × pgbouncer_instances) + 10 for superuser slots. Not 500. I’ve seen teams run 8-core Postgres instances with max_connections=500 and wonder why performance collapses at 200 concurrent users — the process scheduler is doing more work than the database is.

---

Monitoring signals that precede saturation

Run SHOW POOLS on your PgBouncer admin socket every 15 seconds and track:

• cl_waiting — clients queued waiting for a server connection. Any non-zero value sustained beyond 5 seconds means your pool is undersized or your transactions are too slow.
• sv_idle — server connections sitting idle. If this is consistently zero while cl_waiting is nonzero, you’re saturated. If sv_idle is high while cl_waiting is zero, you’re over-provisioned and wasting PostgreSQL resources.
• sv_active / server_pool_size ratio above 0.85 for more than 60 seconds is a leading indicator of imminent saturation.

Wire these into your observability stack (Prometheus via pgbouncer_exporter, Datadog’s Postgres integration, or a simple cron-scraped metrics pipeline) and alert on cl_waiting > 0 for more than 30 seconds.

---

What to do now

1. Switch to transaction mode and disable named prepared statements at the driver layer. Set prepareThreshold=0 (JDBC), statement_cache_size=0 (asyncpg), or the equivalent for your stack. Verify with SHOW STATS that sv_login counts drop — that confirms reuse is working.

2. Size your pool with the queuing formula, not by feel. Measure your actual p50 transaction latency under load, plug it into tps × latency_s × 1.5, and set server_pool_size accordingly. Keep max_connections 20% above that ceiling across all PgBouncer instances.

3. Instrument cl_waiting and sv_idle as first-class SLIs. A cl_waiting alert firing before user-facing latency degrades gives you a 30–120 second window to act — scale horizontally, kill long-running transactions, or shed load — before your p99 becomes your p50.