---
title: "PgBouncer Transaction Mode for 50k Concurrent Mobile Users"
published: true
description: "Master PgBouncer transaction-mode pooling for mobile API backends — avoid prepared statement pitfalls, tune pool config, and catch pool exhaustion before it pages you at 3am."
tags: postgresql, api, mobile, architecture
canonical_url: https://mvpfactory.co/blog/pgbouncer-transaction-mode-mobile-scale
---
## What We Are Building
By the end of this tutorial, you will have a production-ready PgBouncer configuration that handles 50k concurrent mobile users on 4 DB cores, with ORM patches applied, and monitoring queries that catch pool exhaustion 3–5 minutes before your error rates spike.
## Prerequisites
- PostgreSQL running (any recent version)
- PgBouncer installed (`apt install pgbouncer` or equivalent)
- Basic familiarity with connection strings and INI config files
- An ORM in your stack — Kotlin with Exposed/JDBC, or Python with SQLAlchemy
---
## Why Connection Pooling Is Non-Negotiable at This Scale
PostgreSQL creates one OS process per connection. At 50k concurrent mobile clients — even if only 10% are active — you are looking at 5,000 backend processes consuming 5–10MB of RAM each. On a 4-core machine with 32GB RAM, that ceiling arrives 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 — let me walk you through every one of them.
---
## Step 1: 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. Here is what we settled on after that incident:
ini
[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
Here is why each value matters:
- **`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 serve nobody.
- **`client_idle_timeout = 60`** — The docs do not mention this, but setting it to `0` lets stale connections accumulate when mobile clients background the app without closing sockets cleanly. Under 30s causes excessive reconnect churn given how aggressively mobile OSes cycle network state. 60 seconds is the middle ground.
---
## Step 2: Fix the Prepared Statement Problem
Here is the gotcha that will save you hours.
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 physically different connection next time. Your `EXECUTE stmt` lands on a connection that has never seen that `PREPARE` — and you get:
plaintext
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. This breaks silently in staging and catastrophically under production load.
**Fix for Python (SQLAlchemy + psycopg3):**
python
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. The issue is specific to psycopg3 and drivers like asyncpg that use `PREPARE` explicitly.
**Fix for Kotlin (Exposed / JDBC):**
kotlin
val url = "jdbc:postgresql://host/db?prepareThreshold=0"
Let me show you a pattern I use in every project — apply this at the connection pool level so it is impossible to miss in code review.
---
## Step 3: Understand the Mode Tradeoffs
| 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 |
Session mode is what you reach for when you cannot refactor the ORM — but it kills your multiplexing ratio. Statement mode is rarely correct for anything beyond read-only analytics. For stateless mobile API backends, transaction mode is the right call.
---
## Step 4: Monitor Before It Pages You
Here is the minimal setup to get early warning on pool exhaustion.
**Against the PgBouncer virtual database:**
sql
-- Watch sv_used approaching max_client_conn
-- cl_waiting is your canary metric
SHOW POOLS;
**Against PostgreSQL directly:**
sql
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. Pool exhaustion typically appears there 3–5 minutes before error rates spike in your API metrics — that window is your incident prevention.
---
## Gotchas
1. **Prepared statements are silent in staging.** Your test database has low concurrency, so connections are rarely reused mid-session. You will not see `prepared statement does not exist` until production load forces multiplexing. Apply `prepareThreshold=0` before you deploy, not after.
2. **Advisory locks disappear.** If your application uses `pg_advisory_lock()` for distributed locking, transaction mode will break it — the lock releases when the connection returns to the pool, not when your application logic expects. Use a Redis-based lock or refactor to explicit locking within a single transaction.
3. **`SET` variable state is lost.** If you set `search_path` or session-level configuration variables, those are gone after each transaction. Set them at the database or role level instead.
4. **Pool sizing under mobile traffic is not steady-state.** Mobile clients burst hard on app resume — size and benchmark under that pattern, not synthetic constant load. Start at 2–4× core count and tune from real traffic data.
---
## Conclusion
Transaction-mode PgBouncer is the difference between a 4-core database server handling 50k mobile users and one that falls over Tuesday morning. The configuration is straightforward once you know the defaults that will hurt you — `client_idle_timeout`, pool sizing, and prepared statement caching are the three places where most tutorials leave you exposed.
Apply `prepareThreshold=0` at the driver level, alert on `cl_waiting > 50`, and you have a setup that handles traffic spikes without a 3am page.
**Relevant docs:**
- [PgBouncer configuration reference](https://www.pgbouncer.org/config.html)
- [psycopg3 prepare_threshold](https://www.psycopg.org/psycopg3/docs/api/connections.html)
- [PostgreSQL pg_stat_activity](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW)
Top comments (0)