Connection Pools and Transactions

Scenario

Example app: a product API with 8 pods, each running a pool of 10 connections (80 total) to a Postgres server configured with max_connections = 100. On Black Friday, traffic triples and a recommendation service it calls slows to 2 s.

What you see in production

Error logs fill with pool timeouts while the database itself looks calm:

SQLAlchemy: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00
Java/Hikari: HikariPool-1 - Connection is not available, request timed out after 30000ms.

Someone raises the pool size to 50 per pod — and now Postgres rejects connections with FATAL: sorry, too many clients already.

The naive code

The request opens a session (checking out a connection) at the start and calls a slow service while still holding it:

def product_page(request, product_id):
    with transaction.atomic():                          # connection checked out…
        product = Product.objects.get(pk=product_id)
        recs = recommendations.fetch(product_id)        # …held during a 2 s HTTP call
        Product.objects.filter(pk=product_id).update(views=F("views") + 1)
    return render(request, "product.html", {"product": product, "recs": recs})

@Transactional                // with spring.jpa.open-in-view=true the connection is even held
public ProductPage page(long id) {           // until the view finishes rendering
    Product p = products.findById(id).orElseThrow();
    List<Rec> recs = recommendationClient.fetch(id);   // slow HTTP call holding a connection
    return new ProductPage(p, recs);
}

return DB::transaction(function () use ($id) {
    $product = Product::findOrFail($id);
    $recs = Http::timeout(5)->get("http://recs/products/{$id}")->json();   // holds the connection
    return view('product', compact('product', 'recs'));
});

const page = await prisma.$transaction(async (tx) => {
  const product = await tx.product.findUniqueOrThrow({ where: { id } });
  const recs = await fetch(`http://recs/products/${id}`).then((r) => r.json());   // held connection
  return { product, recs };
});

Reproduce it

A pool of 10 connections, 40 requests per second, each holding its connection for 5 ms of SQL — plus, in the bad version, a 2 s HTTP call:

import heapq

def run(hold_seconds, pool_size=10, rps=40, seconds=5, pool_timeout=1.0):
    free_at = [0.0] * pool_size          # when each connection becomes free
    heapq.heapify(free_at)
    timeouts = served = 0
    for i in range(rps * seconds):
        arrive = i / rps
        earliest = free_at[0]
        start = max(arrive, earliest)
        if start - arrive > pool_timeout:
            timeouts += 1                # waited too long for a connection
            continue
        heapq.heapreplace(free_at, start + hold_seconds)
        served += 1
    return served, timeouts

for label, hold in [("SQL only (5 ms)", 0.005), ("SQL + 2 s HTTP call", 2.005)]:
    served, timeouts = run(hold)
    print(f"{label:22} served={served:3}  pool timeouts={timeouts:3}  max throughput ~{10 / hold:,.0f} req/s")

SQL only (5 ms)        served=200  pool timeouts=  0  max throughput ~2,000 req/s
SQL + 2 s HTTP call    served= 30  pool timeouts=170  max throughput ~5 req/s

Root cause

A pool can serve at most pool_size / hold_time requests per second. Holding a connection during non-database work (HTTP calls, template rendering with lazy loading, open-in-view) multiplies the hold time by 100× or more, so the pool runs dry long before the database is busy. Raising the pool size moves the bottleneck to the server: every Postgres connection is a process with its own memory, and more concurrent queries than CPU cores mostly adds contention.

The fix

1. Hold connections only for database work — fetch, release, then call other services.
2. Size pools from the database's capacity, not from traffic: total connections across all instances ≤ what the server handles well. The HikariCP pool-sizing guide's starting point is connections ≈ (core_count × 2) + effective_spindle_count for the whole database, divided among the instances.
3. Put a pooler in front (PgBouncer in transaction mode, RDS Proxy, ProxySQL for MySQL) when you have many app instances or serverless functions.

def product_page(request, product_id):
    product = Product.objects.get(pk=product_id)                       # short query, autocommit
    Product.objects.filter(pk=product_id).update(views=F("views") + 1)
    recs = recommendations.fetch(product_id, timeout=0.5)              # no connection held
    return render(request, "product.html", {"product": product, "recs": recs})
# settings: CONN_MAX_AGE=60, and in front of Postgres: PgBouncer (pool_mode = transaction)

// application.properties
// spring.jpa.open-in-view=false
// spring.datasource.hikari.maximum-pool-size=10
// spring.datasource.hikari.connection-timeout=2000     # fail fast instead of queueing 30 s
public ProductPage page(long id) {
    Product p = productService.load(id);                       // @Transactional(readOnly = true), short
    List<Rec> recs = recommendationClient.fetch(id);            // outside any transaction
    return new ProductPage(p, recs);
}

$product = Product::findOrFail($id);                       // no transaction needed for a read
$recs = Http::timeout(1)->get("http://recs/products/{$id}")->json();
// php-fpm: pm.max_children × number of servers must fit within the DB's connection budget

const product = await prisma.product.findUniqueOrThrow({ where: { id } });
const recs = await fetch(`http://recs/products/${id}`, { signal: AbortSignal.timeout(500) }).then((r) => r.json());
// DATABASE_URL=...?connection_limit=10&pool_timeout=2  (and pgbouncer=true behind PgBouncer)

Trade-offs

• PgBouncer transaction mode gives a connection to a client only for the duration of a transaction, so 1,000 app connections can share 50 server connections. It breaks session state: SET without LOCAL, session advisory locks, LISTEN, and (in older versions) server-side prepared statements. Check what your driver and ORM rely on.
• Short pool timeouts (1–2 s) turn overload into fast, visible errors instead of 30 s hangs — combine them with load shedding.
• A dedicated small pool for slow, low-priority work (reports, exports) stops it from starving user requests (the bulkhead pattern).

Detect it in production

• Pool metrics: active, idle, pending/waiting threads and acquisition time (Hikari exposes these via Micrometer; SQLAlchemy via pool events).
• Postgres: SELECT state, count(*) FROM pg_stat_activity GROUP BY state; — many idle in transaction sessions mean the app holds connections without using them.
• Alert when pending connection requests > 0 for more than a minute.

Key points

• Pool throughput = pool size ÷ hold time; cut the hold time first.
• Never hold a connection (or transaction) across external calls; disable open-session-in-view.
• Size pools from database capacity, and share connections with a pooler when instances multiply.
• Know which session features your pooler mode breaks.