Connection Poolers at Scale

Scenario

Example company: 14 microservices on Kubernetes plus a fleet of serverless functions all talk to one Postgres primary (max_connections = 500). Each pod opens a pool of 20. During a traffic spike the autoscaler adds pods.

What you see in production

At 3× normal traffic:

FATAL:  remaining connection slots are reserved for non-replication superuser connections
FATAL:  sorry, too many clients already

Database memory climbs with thousands of mostly idle backend processes, and latency rises before CPU is saturated.

The naive code

Every process opens its own pool sized for its own peak:

# 14 services × up to 30 pods × pool 20  =  8,400 potential connections
env:
  - name: DB_POOL_SIZE
    value: "20"

// a new client per invocation; thousands of concurrent invocations = thousands of connections
export const handler = async () => {
  const prisma = new PrismaClient();
  return prisma.order.findMany({ take: 10 });
};

Reproduce it

Compute the connection demand with direct pools vs a transaction-mode pooler that multiplexes clients onto a small server pool:

services = {"api": 30, "checkout": 20, "search": 10, "workers": 15, "admin": 4}   # pods at peak
POOL_PER_POD = 20
MAX_CONNECTIONS = 500
direct = sum(pods * POOL_PER_POD for pods in services.values())
print(f"direct pools: {direct} connections needed vs max_connections={MAX_CONNECTIONS} "
      f"-> {'FAILS' if direct > MAX_CONNECTIONS else 'ok'}")

active_ratio = 0.05        # share of time a client is actually inside a transaction
server_pool = 120          # PgBouncer default_pool_size × pools
needed = direct * active_ratio
print(f"pooler: {direct} client connections share {server_pool} server connections; "
      f"~{needed:.0f} busy at once -> {'ok' if needed <= server_pool else 'queueing'}")

direct pools: 1580 connections needed vs max_connections=500 -> FAILS
pooler: 1580 client connections share 120 server connections; ~79 busy at once -> ok

Root cause

Each Postgres connection is a process with its own memory; thousands of them waste RAM and add contention even when idle. Per-pod pools multiply with replicas and autoscaling, so total demand grows with deployment size rather than with real database work. Serverless platforms make it worse: every concurrent invocation can be a new connection.

The fix

• Put a pooler between apps and the database: PgBouncer (transaction mode), RDS Proxy / Cloud SQL connectors, ProxySQL for MySQL.
• Give each service a connection budget and keep pod pools small (5–10), relying on the pooler.
• Reuse clients across serverless invocations (module scope) and point them at the proxy.

[databases]
app = host=10.0.0.5 dbname=app
[pgbouncer]
pool_mode = transaction
max_client_conn = 10000      ; cheap: client connections are just sockets
default_pool_size = 40       ; real server connections per (db, user)
reserve_pool_size = 10
server_idle_timeout = 60

// one client per container, reused across invocations; connect through the proxy/pooler
const prisma = new PrismaClient({ datasourceUrl: process.env.DATABASE_URL });   // …?pgbouncer=true&connection_limit=1
export const handler = async () => prisma.order.findMany({ take: 10 });

engine = create_engine(
    "postgresql+psycopg://app@pgbouncer:6432/app",
    pool_size=5, max_overflow=5, pool_pre_ping=True,
    connect_args={"prepare_threshold": None},   # psycopg 3: disable server-side prepares for transaction pooling
)

// application.yaml
// spring.datasource.url: jdbc:postgresql://pgbouncer:6432/app?prepareThreshold=0
// spring.datasource.hikari.maximum-pool-size: 8

Trade-offs

• Transaction pooling breaks session state (see Connection Pools and Transactions) — audit for SET, session advisory locks, LISTEN, temp tables and prepared statements.
• The pooler is a new hop and a new single point of failure; run it redundantly (several instances or sidecars).
• Poolers fix connection count, not slow queries — a queue at the pooler still means the database is the bottleneck.

Detect it in production

• SELECT count(*), state FROM pg_stat_activity GROUP BY state; versus max_connections.
• PgBouncer SHOW POOLS; — cl_waiting > 0 for long means server connections are exhausted.
• Track connections per service; alert when any service exceeds its budget.

Key points

• Per-pod pools multiply with scale; the database pays per connection.
• A transaction-mode pooler multiplexes thousands of clients onto a small server pool.
• Budget connections per service; reuse clients in serverless functions.