PostgreSQL connection pooling at scale: PgBouncer deep dive

Meta description: Master PgBouncer transaction pooling, fix prepared statement errors, and right-size your pool with a proven formula to stop connection exhaustion.

Tags: backend microservices architecture api devops

---

TL;DR

Transaction-mode pooling in PgBouncer breaks PostgreSQL prepared statements by design. Don’t disable pooling to work around it. Understand why it happens, size your pools with the (core_count * 2) + effective_spindle_count formula, and choose the right pooler for your workload. Most connection storms are self-inflicted through misconfiguration, not load.

---

The problem nobody talks about until production burns

PostgreSQL’s default max_connections = 100 will crash a medium-traffic API before it ever hits CPU limits. A mobile backend sees a traffic spike, Postgres hits its connection ceiling, new connections queue, timeouts cascade, and your on-call engineer is paged at 2am.

The math is brutal: each PostgreSQL connection consumes approximately 5–10 MB of RAM. At 500 open connections, you’re burning up to 5 GB in connection overhead before a single query runs — more than most workloads spend on actual query execution.

Here’s the architecture that prevents it.

---

PgBouncer pooling modes: choosing your tradeoff

PgBouncer offers three pooling modes, and your choice has hard consequences.

Mode	Connection Reuse	Prepared Statements	Session Features	Use Case
Session	Per client session	✅ Supported	✅ Full support	Legacy apps, long sessions
Transaction	Per transaction	❌ Broken	⚠️ Limited	High-concurrency APIs
Statement	Per statement	❌ Broken	❌ No support	Rarely recommended

For modern microservices and mobile backends, transaction mode is what you want. It lets dozens of application threads share a much smaller pool of actual Postgres connections. But here’s what most teams get wrong: they enable transaction mode and then leave ORM-generated prepared statements in place.

Why prepared statements break in transaction mode

PostgreSQL scopes prepared statements to a session. In transaction mode, your application’s logical session maps to different physical backend connections across transactions. When your ORM executes EXECUTE stmt_abc, that statement was prepared on backend connection #3, but PgBouncer hands you connection #7. Postgres returns ERROR: prepared statement "stmt_abc" does not exist.

The fix is to force simple query protocol at the driver level, not to adjust server-side plan caching.

For Java/Kotlin with HikariCP + JDBC (pgjdbc), use preferQueryMode=simple:

val config = HikariConfig().apply {
    jdbcUrl = "jdbc:postgresql://pgbouncer:5432/mydb?preferQueryMode=simple"
    maximumPoolSize = 10
}

For Python with asyncpg:

conn = await asyncpg.connect(
    "postgresql://user:pass@pgbouncer:5432/db",
    prepared_statement_cache_size=0
)

For libpq-based drivers (e.g., psycopg2), use the simple query protocol via the pgbouncer=1 DSN hint or configure your driver to avoid the extended query protocol:

# psycopg2: disable server-side binding
conn = psycopg2.connect(dsn, options="-c standard_conforming_strings=on")
cur = conn.cursor()
cur.execute("SET plan_cache_mode = force_generic_plan")  # server-side only
# For full safety, use psycopg3 with prepared=False per statement

The root fix is always at the driver level: prevent the extended query protocol from issuing Parse/Bind commands that create named server-side prepared statements.

---

The pool size formula that actually works

This formula, popularized by the HikariCP team (Brett Wooldridge and Brian Will), has held up across production deployments:

pool_size = (core_count * 2) + effective_spindle_count

For a 4-core server with SSDs (effective_spindle_count ≈ 1 for solid-state storage):

pool_size = (4 * 2) + 1 = 9

This feels too small. Teams almost always push back. But the reasoning is sound: PostgreSQL is I/O bound on reads and CPU bound on complex queries. Beyond a certain connection count, you’re not adding parallelism — you’re adding context-switching overhead and lock contention. A pool of 9–10 connections on a 4-core machine will outperform a pool of 100 under sustained load. The number that feels wrong is usually right.

pgbouncer.ini:

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 9
min_pool_size = 2
reserve_pool_size = 2
reserve_pool_timeout = 3
server_idle_timeout = 600

---

pgpool-II vs PgBouncer vs Odyssey

Feature	PgBouncer	pgpool-II	Odyssey
Primary purpose	Connection pooling	Pooling + HA + load balancing	Connection pooling
Transaction mode	✅ Excellent	✅ Supported	✅ Excellent
Read replica routing	❌ No	✅ Built-in	⚠️ Limited
Protocol overhead	Very low	Moderate	Low
Production maturity	Battle-tested	Battle-tested	Growing
Complexity	Low	High	Medium

Use PgBouncer when you need lightweight, high-performance pooling with minimal operational surface area. Use pgpool-II when you need read/write splitting across replicas and can absorb the configuration complexity — it’s powerful, but it takes real work to operate. Odyssey (from Yandex) is worth evaluating at very high connection counts; its multi-threaded model handles scale that PgBouncer’s single-threaded architecture can’t match above ~50k clients.

---

The configuration mistakes that cause connection storms

1. server_reset_query misconfigured in transaction mode. In session mode, PgBouncer runs DISCARD ALL between clients. In transaction mode this is skipped, but if you’ve set a custom server_reset_query, it will fire and break your assumptions about session state.

2. max_client_conn set lower than application thread count. Your application has 200 threads, but max_client_conn = 100. The other 100 threads block, timeout, and retry, creating a retry storm that amplifies load exactly when you can least afford it.

3. No connect_timeout on the application side. Without a client-side timeout, blocked connections queue indefinitely. Set connect_timeout=3000ms at the connection pool level, not just at the Postgres level.

---

What to do first

If you’re running transaction mode with any ORM that uses prepared statements, fix the driver configuration before anything else. Audit your ORM settings. Use preferQueryMode=simple for pgjdbc, prepared_statement_cache_size=0 for asyncpg, or the equivalent for your stack. Do this before the migration, not after the errors start.

Then run the formula. Start at (core_count * 2) + effective_spindle_count (use 1 for SSDs) and load test. You’ll almost certainly find the sweet spot is smaller than your current max_connections setting. That’s not a bug in the formula.

Finally, treat PgBouncer as infrastructure rather than an afterthought. Deploy it as a sidecar or dedicated service with monitoring on SHOW POOLS and SHOW STATS. Pool saturation should trigger alerts before clients ever see errors.