---
title: "PostgreSQL Connection Pooling at Scale: PgBouncer Transaction Mode Deep Dive"
published: true
description: "Master PgBouncer transaction pooling, fix prepared statement errors, and right-size your pool with a proven formula to stop connection exhaustion in high-concurrency backends."
tags: postgresql, architecture, devops, api
canonical_url: https://mvpfactory.co/blog/pgbouncer-transaction-pooling-deep-dive
---
## What We Will Cover
By the end of this workshop you will understand why PostgreSQL's default `max_connections = 100` will kill a medium-traffic API before it ever touches CPU limits, how PgBouncer's transaction mode works and why it breaks prepared statements by design, and how to size your pool correctly the first time using a formula that holds up in production.
Let me show you a pattern I use in every backend project that expects real traffic.
---
## Prerequisites
- A running PostgreSQL instance (13+)
- PgBouncer installed (`apt install pgbouncer` or equivalent)
- Basic familiarity with connection pooling concepts
- Your application driver ready: pgjdbc (Kotlin/Java), asyncpg (Python), or psycopg2/3
---
## The Problem That Paged Me at 2am
Each PostgreSQL connection consumes roughly 5–10 MB of RAM. At 500 open connections you are burning up to 5 GB in overhead before a single query runs. A traffic spike hits, Postgres reaches its connection ceiling, new connections queue, timeouts cascade. You know the rest.
The fix is transaction-mode pooling in PgBouncer. Here is the minimal setup to get this working.
---
## Step 1 — Pick the Right Pooling Mode
PgBouncer offers three modes. For modern APIs and mobile backends, transaction mode is what you want.
| Mode | Prepared Statements | Use Case |
|---|---|---|
| Session | ✅ Supported | Legacy apps, long sessions |
| Transaction | ❌ Broken by design | High-concurrency APIs |
| Statement | ❌ Broken | Rarely recommended |
Transaction mode lets dozens of application threads share a much smaller pool of actual Postgres connections. The tradeoff is prepared statements — and this is where most teams burn time.
---
## Step 2 — Fix Prepared Statements at the Driver Level
PostgreSQL scopes prepared statements to a session. In transaction mode, your logical session maps to *different* physical backend connections across transactions. When your ORM runs `EXECUTE stmt_abc`, that statement was prepared on connection #3, but PgBouncer handed you connection #7. Postgres returns `ERROR: prepared statement "stmt_abc" does not exist`.
The docs do not make this obvious, but the fix is always at the driver level — prevent the extended query protocol from issuing `Parse`/`Bind` commands that create named server-side prepared statements.
**Kotlin/Java with HikariCP + pgjdbc:**
kotlin
val config = HikariConfig().apply {
jdbcUrl = "jdbc:postgresql://pgbouncer:5432/mydb?preferQueryMode=simple"
maximumPoolSize = 10
}
**Python with asyncpg:**
python
conn = await asyncpg.connect(
"postgresql://user:pass@pgbouncer:5432/db",
prepared_statement_cache_size=0
)
**psycopg2 (libpq-based):**
python
For full safety, use psycopg3 with prepared=False per statement
conn = psycopg2.connect(dsn, options="-c standard_conforming_strings=on")
Do this before any migration, not after the errors start appearing.
---
## Step 3 — Size Your Pool With the Formula That Actually Works
This formula from the HikariCP team has held up across production deployments:
python
pool_size = (core_count * 2) + effective_spindle_count
For a 4-core server with SSDs (`effective_spindle_count ≈ 1`):
python
pool_size = (4 * 2) + 1 = 9
This feels too small. Every team pushes back. Here is the gotcha that will save you hours of misguided tuning: PostgreSQL is I/O bound on reads and CPU bound on complex queries. Beyond a threshold, you are not adding parallelism — you are adding context-switching overhead and lock contention. A pool of 9–10 on a 4-core machine outperforms a pool of 100 under sustained load.
Your `pgbouncer.ini`:
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
---
## Step 4 — Choose the Right Pooler for Your Scale
| Feature | PgBouncer | pgpool-II | Odyssey |
|---|---|---|---|
| Transaction mode | ✅ Excellent | ✅ Supported | ✅ Excellent |
| Read replica routing | ❌ No | ✅ Built-in | ⚠️ Limited |
| Protocol overhead | Very low | Moderate | Low |
| Operational complexity | Low | High | Medium |
Use PgBouncer for lightweight, high-performance pooling with minimal operational surface area. Use pgpool-II when you need read/write splitting and can absorb the configuration work. Odyssey from Yandex is worth evaluating at very high connection counts — its multi-threaded model handles scale that PgBouncer's single-threaded architecture cannot match above ~50k clients.
---
## Gotchas
**`server_reset_query` in transaction mode.** In session mode, PgBouncer runs `DISCARD ALL` between clients. In transaction mode this is skipped — but a custom `server_reset_query` will still fire and break your session state assumptions.
**`max_client_conn` lower than your thread count.** If your application has 200 threads but `max_client_conn = 100`, the remaining 100 threads block, timeout, and retry. That retry storm amplifies load exactly when you can least afford it.
**No client-side `connect_timeout`.** Without it, blocked connections queue indefinitely. Set `connect_timeout=3000ms` at the pool level, not just at the Postgres level.
---
## Conclusion
Most connection storms are self-inflicted through misconfiguration, not load. Audit your ORM's prepared statement behavior first, apply the pool size formula, then deploy PgBouncer with monitoring on `SHOW POOLS` and `SHOW STATS`. Pool saturation should trigger alerts before clients ever see errors.
**Resources:**
- [PgBouncer documentation](https://www.pgbouncer.org/config.html)
- [HikariCP pool sizing analysis](https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing)
- [pgjdbc `preferQueryMode` reference](https://jdbc.postgresql.org/documentation/use/)
Top comments (0)