PostgreSQL Connection Pooling Under Pressure: PgBouncer Transaction Mode, Prepared Statement Workarounds, and the Pool Sizing Formula That Actually Works for Multi-Tenant SaaS

TL;DR

Transaction-mode PgBouncer is the standard for multi-tenant SaaS, but it silently breaks prepared statements and introduces session pinning traps that will crater your p99 latency under load. The fix is a combination of DEALLOCATE ALL, the right pool sizing formula (hint: it’s not max_connections / num_services), and monitoring queries that catch saturation before your customers do. I compare PgBouncer, pgcat, and Supavisor below with real numbers.

---

Why this matters now

B2B SaaS is reaccelerating. Twilio jumped from single-digit growth to 20%+. Datadog crossed a $1 billion quarter. Atlassian hit 32% growth. When your tenant count doubles in a year, the first thing that buckles isn’t your application code. It’s your database connection layer.

I’ve built enough multi-tenant platforms to know: connection pooling is the most under-monitored piece of infrastructure right up until it becomes the most urgent fire.

The prepared statement problem

PgBouncer in transaction mode multiplexes connections: when your transaction ends, that server connection returns to the pool and may serve a completely different client next. Good. That’s what you want. Here’s what most teams get wrong: prepared statements are bound to a server-side session, not a transaction.

Client A prepares stmt_1 on Connection 5. Connection 5 gets reassigned to Client B. Client B knows nothing about stmt_1. Meanwhile, Client A’s next EXECUTE stmt_1 lands on Connection 7, where it was never prepared. Chaos.

The fixes

Approach	Mechanism	Tradeoff
DEALLOCATE ALL on checkout	Reset prepared statements when a connection is assigned	Adds ~0.5ms per transaction, eliminates stale state
Disable prepared statements (driver-level)	Use simple query protocol instead of extended	Marginal CPU increase on Postgres (~3-7%), but fully safe
pgcat / Supavisor protocol-aware pooling	Pooler intercepts and replays PARSE messages	Zero client-side changes, but newer and less proven in the wild

For most teams, disabling prepared statements at the driver level is the pragmatic choice:

// HikariCP + JDBC config for PgBouncer transaction mode
HikariConfig().apply {
    jdbcUrl = "jdbc:postgresql://pgbouncer:6432/mydb"
    addDataProperty("prepareThreshold", "0")  // disables server-side prepared statements
    addDataProperty("preparedStatementCacheQueries", "0")
}

The ~5% CPU overhead on Postgres is a rounding error compared to cascading failures from stale prepared statement handles at 2 AM.

The pool sizing formula that actually works

The PostgreSQL wiki publishes a well-known formula:

optimal_connections = ((core_count * 2) + effective_spindle_count)

For a modern 8-core cloud instance with SSDs (effective spindle count ≈ 1):

(8 * 2) + 1 = 17 connections

Most teams set max_connections = 200 by default and wonder why performance degrades. Beyond the optimal count, you’re paying for context switching, lock contention, and cache thrashing. More connections doesn’t mean more throughput. It means less.

Adjusting for multi-tenant SaaS

For multi-tenant workloads, I layer in a service multiplier:

pgbouncer_default_pool_size = optimal_connections / num_services
reserve_pool = ceil(optimal_connections * 0.15)

For 4 services sharing a 17-connection Postgres:

Parameter	Value
default_pool_size	4 per service
reserve_pool_size	3
max_db_connections	17
max_client_conn	1000

This lets PgBouncer queue up to 1,000 application connections while never overwhelming Postgres beyond 17 real server connections.

Session pinning traps

Transaction mode has another quiet failure mode: session pinning. These operations force PgBouncer to pin a client to a single server connection, which defeats multiplexing entirely:

• SET statement_timeout = ... (use SET LOCAL inside a transaction instead)
• LISTEN / NOTIFY (move to a dedicated non-pooled connection)
• Advisory locks (pg_advisory_lock — use pg_advisory_xact_lock for transaction-scoped variants)
• Temporary tables (create and drop within the same transaction)

One SET statement outside a transaction block can pin a connection for the lifetime of the client session. No warning, no error. I’ve seen this single mistake reduce effective pool capacity by 60% in production. Took hours to find because everything looked fine in the application logs.

Monitoring queries that save you

Run these against PgBouncer’s admin console before your p99 tells you something is wrong:

-- Connections waiting for a server connection (saturation signal)
SHOW POOLS;
-- Watch: cl_waiting > 0 sustained = pool exhaustion incoming

-- Average wait time per pool
SHOW STATS;
-- Watch: avg_wait_time > 50ms = time to scale or optimize

When cl_waiting stays above zero for more than 30 seconds, you’re already degraded. Alert on it.

PgBouncer vs pgcat vs Supavisor

Feature	PgBouncer	pgcat	Supavisor
Maturity	15+ years in production	~2 years, production at Instacart	~2 years, production at Supabase
Protocol-aware prepared stmts	No	Yes	Yes
Multi-threaded	No (single-threaded)	Yes (Rust, Tokio)	Yes (Elixir/BEAM)
Sharding support	No	Yes	No
Throughput (single instance)	~15K TPS	~25K TPS	~20K TPS
Operational complexity	Low	Medium	Medium

PgBouncer is the safe default. It’s boring, and boring is good for infrastructure. If you need prepared statement transparency or you’re hitting single-threaded throughput limits, pgcat is worth evaluating. I’d want at least a few weeks of shadow traffic against it before cutting over, though.

What to do with all this

1. Set prepareThreshold=0 at the driver level when using PgBouncer transaction mode. The CPU tradeoff is negligible.
2. Size your pool using the formula, not gut feel. (cores * 2) + 1 for Postgres max_connections, then divide across services for PgBouncer default_pool_size. Overprovisioning connections actively hurts throughput.
3. Alert on cl_waiting > 0 sustained for 30+ seconds. This is the earliest signal of pool saturation, well before latency percentiles start climbing.