A Postgres backend without connection pooling falls over at modest scale. The pooler is mandatory infrastructure. This post is the working playbook.

The problem

Each Postgres connection costs:

  • ~10 MB RAM on the server.
  • Backend process slot.
  • TLS handshake on connect.
  • ~3ms cold connect.

With 1000 app processes connecting directly: 10 GB RAM just for idle connections. Postgres falls over.

PgBouncer transaction mode

# pgbouncer.ini
pool_mode = transaction
max_client_conn = 10000        # how many app processes can connect
default_pool_size = 25          # how many backend connections per database

App connects to PgBouncer; PgBouncer multiplexes onto 25 actual Postgres connections. 10000 clients → 25 backend connections.

Performance: imperceptible overhead per query.

Caveats of transaction mode

The connection only belongs to the client for the duration of a transaction. After commit, it’s free for any other client. Things break:

  • LISTEN / NOTIFY: doesn’t work — listener might not be on the connection that the NOTIFY arrives.
  • SET LOCAL: confined to the transaction; that’s fine.
  • SET (without LOCAL): leaks to other clients. Bug.
  • Prepared statements (server-side): need protocol-level support.
  • Advisory locks at session level: don’t work.

For most CRUD apps: none of these matter. For apps that depend on them: use session pooling (one connection per client) or work around.

Modern alternatives

Supavisor

Elixir-based; built by Supabase. Postgres-protocol-aware; handles prepared statements correctly even in transaction mode.

pgcat

Rust-based; multi-tenant aware; supports load balancing across replicas. Newer; gaining adoption.

Hyperdrive (Cloudflare)

Serverless connection pooling for Cloudflare Workers. Pools at the edge to your Postgres origin. Workers + Postgres without the per-isolate connection cost.

For Cloudflare Workers + D1 , Hyperdrive is what makes Postgres usable.

RDS Proxy

AWS-managed; pools to RDS Postgres / Aurora. Transparent. Pricey vs self-hosted PgBouncer.

Read replica routing

Pair pooling with replica routing:

write_pool = create_pool(primary_dsn, max_size=10)
read_pool = create_pool(replica_dsn, max_size=20)

async def list_orders():
    async with read_pool.acquire() as conn:
        return await conn.fetch("...")

async def create_order(...):
    async with write_pool.acquire() as conn:
        return await conn.execute("...")

For SQLAlchemy, bind_routing handles this. For raw drivers, explicit pools.

Pool sizing

Per-process pool: small (10–25). Total connections to Postgres: bounded.

total_connections = num_processes × per_process_pool_size

For Postgres max_connections of 200 minus headroom for admins / backups (~50): app pool budget ~150. With 100 processes × 25/process = 2500 → too many. Insert PgBouncer.

Common mistakes

1. Per-request new connection

Without a pool, every request pays TLS handshake. Latency dies.

2. Pool too large per-process

50 connections per process × 200 processes = 10000 connections. Postgres gives up. Keep per-process small; multiplex via PgBouncer.

3. Ignoring pg_stat_activity

Watch active vs idle connections. Idle in transaction → connection held by app, blocking work. Investigate.

4. Mixing session + transaction modes

PgBouncer can be configured per-database. Don’t mix client expectations.

5. No connection timeouts

A bad connection holds forever. Set idle_in_transaction_session_timeout Postgres-side and statement timeouts at the app.

What I’d ship today

For a new Postgres-backed app:

  • PgBouncer in transaction mode in front of Postgres.
  • App pool size 10–25 per process.
  • Read replica pool for read-heavy paths.
  • Statement timeouts at the app.
  • idle_in_transaction_session_timeout at Postgres.

For serverless: Hyperdrive (Cloudflare) / Neon’s pooled endpoint / RDS Proxy.

Read this next

If you want my PgBouncer config + read-replica routing, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .