Part 2 · 2 chapters · ~12 min

Connection and Concurrency Scaling

The connection problem stated formally as memory times connections, pooling architectures in-app, sidecar and proxy, transaction, session and statement pooling and what breaks in each, serverless connection storms, and admission control, queueing and load shedding at the database edge.

The connection problem and pooling architectures

code

why connections, not CPU, run out first
Postgres backend memory ≈ 5-10 MB baseline + work_mem per sort/hash node in flight
500 connections × 10 MB = 5 GB before a single query does real work
and the database can only run ~ (cores × 2) queries usefully at once: the rest wait on CPU or locks

Little's law: L = λ × W
  1,000 queries/s × 5 ms average = 5 queries in flight on average  → a pool of ~10-20 is enough
  the same load at 200 ms (a slow query) = 200 in flight            → the pool drains, everyone queues

POOLING ARCHITECTURES

in-app pools, sidecar poolers and central proxies

swipe the figure sideways, or tap expand for full screen

1/5

in-app pools

Every process keeps its own small pool. Simple and fast (no extra hop), but total connections = processes × pool size, which grows with every pod.

simple, no extra hoptotal grows with every replica

Pooling modes and what breaks

mode	breaks	workaround
transaction	session SET, session advisory locks, LISTEN/NOTIFY, temp tables across transactions, WITH HOLD cursors, (older poolers) named prepared statements	SET LOCAL, transaction-level advisory locks, a direct connection for LISTEN, protocol-level prepared statement support
session	nothing, but little multiplexing	use for admin tools and migrations
statement	multi-statement transactions	only for autocommit workloads

The same ideas apply to MySQL: ProxySQL multiplexes and also routes reads to replicas and rewrites queries; MySQL's thread pool plugin limits concurrently executing statements inside the server.