PgBouncer transaction mode for 50k mobile users

Meta description: Master PgBouncer transaction-mode pooling for mobile API backends — avoid prepared statement pitfalls, tune pool config, and catch pool exhaustion early.

Tags: backend api mobile microservices architecture

---

TL;DR

Transaction-mode PgBouncer can handle 50k concurrent mobile users on 4 DB cores — but only if you know the traps. Named prepared statements break silently, advisory locks disappear mid-session, and most tutorials hand you a config that works fine until your Tuesday morning traffic spike. This post covers the exact configuration, the ORM workarounds, and the monitoring queries that catch pool exhaustion before it becomes an incident.

---

Why you can’t skip connection pooling at scale

PostgreSQL creates a process per connection. At 50k concurrent mobile clients, even if only 10% are active at any moment, you’re looking at 5,000 backend processes — each consuming roughly 5–10MB of RAM. On a 4-core machine with 32GB RAM, that’s a hard ceiling you’ll hit fast.

Approach	Connections to DB	RAM overhead	4-core feasibility
Direct connections	~5,000	~25–50GB	No
PgBouncer session mode	~500	~2.5–5GB	Marginal
PgBouncer transaction mode	~50–100	~250–500MB	Yes

Transaction mode is the only path that makes this work. It also comes with sharp edges.

---

The exact PgBouncer configuration

A Tuesday morning traffic spike took our API down in under 4 minutes. The culprit was a default PgBouncer config with max_client_conn bumped and nothing else changed. After that incident, here’s what we settled on:

[pgbouncer]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 80
reserve_pool_size = 20
reserve_pool_timeout = 3
max_db_connections = 100
server_idle_timeout = 600
client_idle_timeout = 60
query_wait_timeout = 30
server_login_retry = 0.5

Why each value:

• default_pool_size = 80 — with 4 cores, PostgreSQL’s sweet spot is typically 2–4× core count for CPU-bound queries. Mobile API queries are often IO-bound, so 80 gives headroom without thrashing.
• max_client_conn = 10000 — mobile clients reconnect aggressively on app resume; this absorbs bursts without rejecting connections at the load balancer.
• reserve_pool_size = 20 — your 3am insurance. When the main pool saturates, these connections handle the surge while your alert fires.
• query_wait_timeout = 30 — fail fast rather than queue forever. Mobile clients will retry; zombie queued connections won’t serve anyone.
• client_idle_timeout = 60 — this one needs careful tuning. Setting it to 0 (disabled) lets stale connections accumulate when mobile clients background the app without closing sockets cleanly. Setting it too low (under 30s) causes excessive reconnect churn given how aggressively mobile OSes cycle network state. 60 seconds is a reasonable middle ground: it reclaims idle slots before they pile up while tolerating normal app lifecycle patterns.

---

The prepared statement problem (and how to fix it)

This is the single most common source of “works in staging, breaks in production” failures with PgBouncer transaction mode.

Named prepared statements (PREPARE stmt AS SELECT ...) are session-scoped in PostgreSQL. In transaction mode, the connection returned to the pool after each transaction may be a different physical connection next time. Your EXECUTE stmt lands on a connection that has never seen that PREPARE — and you get ERROR: prepared statement "stmt" does not exist.

ORMs are the worst offenders. Hibernate, SQLAlchemy with psycopg3, and many others use named prepared statements by default.

Fix for common ORMs:

# SQLAlchemy + psycopg3 — disable server-side prepared statement caching
engine = create_engine(
    DATABASE_URL,
    connect_args={"prepare_threshold": None}  # psycopg3 only
)

psycopg2 note: psycopg2 does not use named server-side prepared statements by default, so no special configuration is required there. The issue is specific to psycopg3 (which enables server-side caching via prepare_threshold) and drivers like asyncpg that use PREPARE explicitly.

// Exposed / JDBC — disable server-side prepared statements via JDBC URL
val url = "jdbc:postgresql://host/db?prepareThreshold=0"

The alternative is DEALLOCATE ALL after each transaction — but that adds a round trip and is difficult to enforce consistently across a shared pool.

---

Session vs transaction vs statement mode: choose your tradeoff

Feature	Session mode	Transaction mode	Statement mode
Named prepared statements	Works	Breaks	Breaks
Advisory locks	Works	Breaks	Breaks
SET variables	Persists	Lost	Lost
LISTEN/NOTIFY	Works	Breaks	Breaks
Multiplexing efficiency	Low	High	Highest
Mobile API suitability	Poor	Excellent	Avoid

Statement mode is rarely correct for anything beyond read-only analytics. Session mode is what you reach for when you can’t refactor the ORM — but it kills your multiplexing ratio. For mobile API backends with stateless request patterns, transaction mode is the right call.

---

Monitoring: catch pool exhaustion before it pages you

-- PgBouncer: run against the pgbouncer virtual database
SHOW POOLS;

-- Watch for sv_used approaching max_client_conn
-- sv_used = server connections in use
-- cl_waiting = clients waiting for a connection (this is your canary)

-- On PostgreSQL: cross-reference with pg_stat_activity
SELECT
  state,
  wait_event_type,
  wait_event,
  count(*) AS count
FROM pg_stat_activity
WHERE datname = 'your_db'
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

Set an alert when cl_waiting exceeds 50 for more than 30 seconds. That’s your leading indicator — pool exhaustion typically manifests there 3–5 minutes before error rates spike in your API metrics.

---

Before you ship

Three things that will bite you if you skip them:

1. Set prepare_threshold=0 (JDBC) or prepare_threshold=None (psycopg3) before deploying transaction mode. The prepared statement breakage is silent in staging and catastrophic under production load.

2. Size your pool at 2–4× core count as a starting point, then benchmark under realistic mobile traffic patterns (bursty, high reconnect rate) rather than steady synthetic load.

3. Alert on cl_waiting > 50 sustained for 30s. This is a more reliable early warning than connection error rates, which lag behind the actual exhaustion event by several minutes.