PostgreSQL Connection Pooling Under Load

---
title: "PgBouncer Transaction Mode: Pool Sizing Under Load"
published: true
description: "PgBouncer transaction pooling internals — prepared statement pitfalls, the pool sizing formula for OLTP workloads, and the metrics that warn you before saturation hits."
tags: postgresql, architecture, devops, performance
canonical_url: https://mvpfactory.co/blog/pgbouncer-transaction-mode-pool-sizing
---

What We Will Build

By the end of this article you will understand why transaction-mode PgBouncer silently breaks named prepared statements, how to calculate the right server_pool_size using queuing theory, and which two metrics to watch so you catch saturation before your users do.

---

Prerequisites

• PostgreSQL running in production (or staging under realistic load)
• PgBouncer installed and configured
• Basic familiarity with connection pooling concepts
• A JDBC, asyncpg, or pg (Node.js) application stack

---

The Problem Nobody Talks About

Here is the pattern I see on almost every scale-up engagement. The team has a missing index or a slow query — but the actual regression is connection exhaustion. PostgreSQL spawns a process per connection. At 500 concurrent connections you are managing 500 OS processes, each consuming roughly 5–10 MB of shared memory overhead. Beyond ~300 connections on a typical 8-core instance, throughput plateaus and the kernel scheduler starts thrashing.

PgBouncer exists to solve exactly this. But most teams configure it wrong.

---

Step 1 — Choose the Right Pooling Mode

Here is the minimal mental model you need:

Mode	Server connection held	Named prepared statements
Session	Full client session	Supported
Transaction	Per transaction only	Broken
Statement	Per statement	Broken

Transaction mode is the only mode that delivers real multiplexing. A pool of 50 server connections can serve hundreds of concurrent application connections because the server connection is released the moment COMMIT or ROLLBACK fires.

Use transaction mode. But read Step 2 before you deploy.

---

Step 2 — Disable Named Prepared Statements at the Driver

The docs do not make this obvious, but here is the gotcha that will save you hours.

When your ORM calls PREPARE my_stmt AS SELECT ... in one transaction and EXECUTE my_stmt in the next, PgBouncer may route the second call to a different backend process that has never seen my_stmt. You get:

ERROR: prepared statement "my_stmt" does not exist

The fix is to switch to protocol-level unnamed statements at the driver layer. Here is the minimal setup to get this working:

// HikariCP + PostgreSQL JDBC
val config = HikariConfig().apply {
    jdbcUrl = "jdbc:postgresql://localhost/mydb?prepareThreshold=0"
    // prepareThreshold=0 disables named prepared statements entirely
    maximumPoolSize = 20
}

For asyncpg set statement_cache_size=0. For Node.js pg, avoid the name field on query objects entirely. Verify the fix is working by checking that sv_login counts in SHOW STATS drop — falling numbers confirm connection reuse is active.

---

Step 3 — Size Your Pool With Math, Not Feel

Let me show you a pattern I use in every project. Most teams treat max_connections as a target. It is a ceiling, not a goal.

The formula, grounded in queuing theory (M/M/1/K adapted for database workloads):

server_pool_size = target_throughput_tps × avg_transaction_latency_s × safety_factor

For variable-latency OLTP — where p99 is 10–20× your p50 — use a safety factor of 1.5 to 2.0. A concrete example:

• Target: 2,000 TPS
• Average transaction latency: 8 ms (0.008 s)
• Safety factor: 1.5

server_pool_size = 2000 × 0.008 × 1.5 = 24

Twenty-four server connections handle 2,000 TPS with headroom. Set PostgreSQL max_connections to (server_pool_size × pgbouncer_instances) + 10 for superuser slots. I have seen 8-core Postgres instances with max_connections=500 collapse at 200 concurrent users — the process scheduler is doing more work than the database.

---

Step 4 — Instrument Two Metrics as First-Class SLIs

Run SHOW POOLS on your PgBouncer admin socket every 15 seconds and track:

• cl_waiting — clients queued for a server connection. Any sustained non-zero value beyond 5 seconds means your pool is undersized or your transactions are running long.
• sv_idle — idle server connections. If this is zero while cl_waiting is nonzero, you are saturated. If it is high while cl_waiting is zero, you are over-provisioned.
• Watch the sv_active / server_pool_size ratio — above 0.85 for more than 60 seconds is a leading indicator of imminent saturation.

Wire cl_waiting into Prometheus via pgbouncer_exporter, Datadog's Postgres integration, or a simple cron-scraped pipeline. Alert on cl_waiting > 0 sustained for more than 30 seconds. A cl_waiting alert fires 30–120 seconds before user-facing latency degrades — that is your window to scale horizontally or shed load.

---

Gotchas

• ORM auto-prepare — Hibernate, GORM, and SQLAlchemy all enable named prepared statements by default. Check your framework's docs; the JDBC URL param alone is not always enough.
• Advisory locks — Transaction mode breaks advisory locks and SET variables. If your application uses either, you need session mode for those specific connections.
• max_connections creep — Teams add PgBouncer instances over time without recalculating the ceiling. Rerun the formula whenever you scale horizontally.

---

Conclusion

Switch to transaction mode, kill named prepared statements at the driver layer, size your pool with tps × latency × 1.5, and alert on cl_waiting before your users feel it. That sequence eliminates the most common PostgreSQL scale-up failure mode I encounter in production systems.

Relevant docs: PgBouncer configuration reference · pgbouncer_exporter