---
title: "PostgreSQL Connection Pooling: PgBouncer vs pgpool-II vs Managed Pools"
published: true
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: postgresql, api, architecture, mobile
canonical_url: https://blog.mvpfactory.co/postgresql-connection-pooling-pgbouncer-pgpool-managed
---
## What You Will Learn
By the end of this walkthrough, you will understand why transaction-mode pooling is the only mode that scales, when to choose PgBouncer over pgpool-II, and the two operational traps — prepared statement incompatibilities and `SET LOCAL` leakage — that surface mid-scale, when fixing them is most painful. We will also cover the per-tenant connection accounting pattern that lets you serve 10,000 concurrent mobile users from a modest shared instance.
## Prerequisites
- A running PostgreSQL instance (local or hosted)
- Basic familiarity with connection strings and database drivers
- Optional: PgBouncer installed, or a Supabase/Neon account for managed pooling
---
## Why Pooling Is Not Optional
PostgreSQL's process-per-connection model is elegant but expensive. Each backend process consumes roughly 5–10 MB of RAM. At 500 concurrent connections on a $50/mo instance — typically 1–2 vCPU, 1–2 GB RAM — you are already memory-constrained before a single query runs.
A mobile app 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.
---
## Step 1: Understand the Three Pooling Modes
Before comparing tools, get the semantics right.
| Mode | Connection held until | Prepared stmts | 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 one that multiplexes thousands of clients over dozens of server connections.** Statement mode is a footgun. Avoid it.
---
## Step 2: Pick Your Pooler
| 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 |
| Ops overhead | Low | High | Zero | Zero |
pgpool-II's extras — HA failover, query routing, load balancing — matter when you are already running a multi-replica setup. For most mobile backends, that is not the situation. PgBouncer in transaction mode is the right call, and the simpler one.
---
## Step 3: Fix the Prepared Statement Trap
Here is the gotcha that will save you hours.
Prepared statements are session-scoped in PostgreSQL. In transaction mode, the server connection that handled your `PREPARE` may not handle your `EXECUTE`. The result:
ERROR: prepared statement "s1" does not exist
Fix it in this order. First, disable prepared statements at the driver level:
kotlin
// Exposed (Kotlin) — disable prepared statements
Database.connect(
url = "jdbc:postgresql://localhost:5432/db?prepareThreshold=0",
driver = "org.postgresql.Driver"
)
Second, upgrade to PgBouncer 1.21+, which introduced protocol-level prepared statement tracking. This is the cleanest fix if you control the pooler. The docs do not make this dependency obvious — but missing it is how teams end up debugging at 2 AM.
---
## Step 4: Seal the SET LOCAL Leak
Transaction mode has a second, less-discussed failure mode. `SET LOCAL` variables — intended to be transaction-scoped — can leak across client sessions when a transaction aborts without a full rollback.
In multi-tenant systems using `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 configure your pooler's reset query:
ini
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 ~1 ms per return. Pay it.
---
## Step 5: Account for Per-Tenant Connections
Let me show you a pattern I use in every project serving many tenants.
A 2 GB RAM instance supports roughly 100–150 server-side connections safely. Structure your pool allocation deliberately:
- Set `pool_mode=transaction` with `default_pool_size` tuned to `max_connections` minus headroom for migrations and admin
- Limit `max_client_conn` per application instance, not globally
- Reserve a session-mode pool (3–5 connections) for schema migrations, `LISTEN/NOTIFY`, and long-running reports
With PgBouncer multiplexing 10,000 clients over 80 server connections, you are serving that mobile load with room to spare.
---
## Gotchas
- **Doing one fix and skipping the other:** `prepareThreshold=0` at the driver AND `server_reset_query = DISCARD ALL` at the pooler. Both. Always.
- **Managed poolers hiding constraints:** Supabase and Neon abstract the hard parts but have their own prepared statement limitations. Read their docs before you hit production.
- **Auditing `SET LOCAL` too late:** Audit your row-level security patterns before enabling transaction mode, not after.
---
## Conclusion
PgBouncer in transaction mode is the right default for mobile backends. Configure `prepareThreshold=0` at the driver, `DISCARD ALL` at the pooler, and structure your pool allocation with per-tenant noisy-neighbor problems in mind. Managed poolers are reasonable starting points — the abstraction saves time, but hidden constraints will cost it back if you skip the documentation.
The failure modes covered here surface mid-scale. Front-load the understanding and you will not be debugging session leakage in production.
Top comments (0)