PostgreSQL connection pooling: PgBouncer vs pgpool-II vs managed pools

Meta description: Transaction vs session pooling, prepared statement pitfalls, SET LOCAL leakage, and per-tenant connection patterns that serve 10k mobile users on a $50/mo database.

Tags: backend api microservices mobile architecture

---

TL;DR

Transaction-mode pooling is the only mode that scales. PgBouncer dominates for raw efficiency. pgpool-II adds complexity you rarely need. Managed pools in Supabase and Neon abstract the hard parts but introduce their own constraints. The failure modes that actually kill teams — prepared statement incompatibilities and SET LOCAL leakage — are operational traps that surface mid-scale, when fixing them is most painful.

---

Why connection pooling is not optional at scale

PostgreSQL’s process-per-connection model is elegant but expensive. Each backend process consumes roughly 5–10MB of RAM and carries significant fork overhead. At 500 concurrent connections on a $50/mo instance (typically 1–2 vCPU, 1–2GB RAM), you’re already memory-constrained before a single query runs.

A mobile application serving 10,000 concurrent users with naive connection management will attempt thousands of simultaneous database connections. Without pooling, your database falls over. With pooling done correctly, you can serve that load from a modest shared instance.

---

The three pooling modes

Before comparing tools, get the semantics right. Pooling operates in one of three modes:

Mode	Server connection held until	Supports prepared stmts	Supports session state
Session	Client disconnects	Yes	Yes
Transaction	Transaction commits	Partial (PgBouncer 1.21+)	No
Statement	Statement completes	No	No

Session mode maps one client to one server connection for the lifetime of the session. It solves nothing at scale — you just moved the bottleneck.

Transaction mode is the only configuration that lets you multiplex thousands of clients over dozens of server connections. A client holds a server connection only during an active transaction. Between transactions, that connection returns to the pool.

Statement mode is a footgun. Avoid it.

---

PgBouncer vs pgpool-II vs managed poolers

Dimension	PgBouncer	pgpool-II	Supabase Pooler	Neon Pooler
Architecture	Single-process, async	Multi-process	PgBouncer-based	Custom Rust proxy
Max throughput	~50k QPS	~10k QPS	Managed	Managed
Load balancing	No	Yes (read replicas)	No	No
Prepared stmt support	Transaction mode: PgBouncer 1.21+	Yes	Limited	Limited
Ops overhead	Low	High	Zero	Zero
Best for	High-throughput OLTP	Read-heavy with replicas	Supabase projects	Serverless/edge

pgpool-II’s extra capabilities — HA failover, query routing, load balancing — matter when you’re already running a multi-replica setup and want transparent read/write splitting. For most mobile backends, that’s not the situation. PgBouncer in transaction mode is the right call, and the simpler one.

---

The prepared statement trap

This is what most teams get wrong about transaction-mode pooling.

Prepared statements are session-scoped in PostgreSQL. In transaction mode, the server connection that handled your PREPARE statement may not be the one that handles your EXECUTE. The result is a cryptic error:

ERROR: prepared statement "s1" does not exist

Fix it in this order:

1. Disable prepared statements at the driver level. In most ORMs and drivers this is a single configuration flag.

// Exposed (Kotlin) — disable prepared statements
Database.connect(
    url = "jdbc:postgresql://localhost:5432/db?prepareThreshold=0",
    driver = "org.postgresql.Driver"
)

2. Upgrade to PgBouncer 1.21+, which introduced protocol-level prepared statement tracking. This is the cleanest fix if you control the pooler.

3. Use named prepared statements sparingly and only in session-mode connections for long-lived admin operations.

---

SET LOCAL leakage between transactions

Transaction mode has a second, less-discussed failure mode: SET LOCAL variables — intended to be transaction-scoped — can leak across client sessions if a transaction is aborted without a full rollback.

In multi-tenant systems where you use SET LOCAL app.tenant_id = '...' for row-level security, this is a security boundary failure, not just a bug.

The mitigation is strict: always wrap tenant-context setting in explicit transactions and ensure your connection pool’s server_reset_query is configured:

# pgbouncer.ini
server_reset_query = DISCARD ALL

DISCARD ALL resets session state, temporary tables, prepared statements, and advisory locks before returning a connection to the pool. It costs ~1ms per return. Pay it.

---

Per-tenant connection accounting for 10k concurrent mobile users

For mobile backends serving many tenants, naive pooling creates noisy-neighbor problems. Structure your pool allocation deliberately:

• Limit max_client_conn per application instance, not globally
• Use pool_mode=transaction with default_pool_size tuned to your database’s max_connections minus headroom for migrations and admin
• Reserve a session-mode pool (3–5 connections) for schema migrations, LISTEN/NOTIFY, and long-running reports

A 2GB RAM instance supports roughly 100–150 server-side connections safely. With PgBouncer multiplexing 10,000 clients over 80 server connections, you’re serving that mobile load with room to spare.

---

What to actually do

PgBouncer in transaction mode, prepareThreshold=0 at the driver, server_reset_query = DISCARD ALL at the pooler. Do both. Doing one and skipping the other is how teams end up debugging at 2am.

Audit your SET LOCAL usage before enabling transaction-mode pooling. Any row-level security pattern using session variables needs explicit transaction boundaries and a verified reset query. Not optional.

Managed poolers (Supabase, Neon) are reasonable starting points, but read their prepared statement documentation before you hit production. The abstraction saves time; the hidden constraints cost it back.