Database Connection Pooling: PgBouncer, HikariCP, and Application-Level Pooling

Why Connection Pooling Matters

PostgreSQL creates an OS process per connection (~5MB RAM). At 1000 concurrent connections, that's 5GB just for connections — before any queries run.

The Problem

App instances: 10
Threads per instance: 50
Connections needed: 500
PostgreSQL max_connections: typically 100-200
Result: connection errors under load

PgBouncer (Connection Proxy)

# /etc/pgbouncer/pgbouncer.ini

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = *

# Pool mode selection:
# session: One server conn per client session (same as direct)
# transaction: One conn per transaction (RECOMMENDED for web apps)
# statement: One conn per statement (most aggressive, breaks some features)
pool_mode = transaction

# Connection limits
max_client_conn = 2000    # Total client connections to PgBouncer
default_pool_size = 25    # Actual PostgreSQL connections per database/user
min_pool_size = 10        # Always-ready connections
reserve_pool_size = 5     # Emergency reserve connections

# Timeouts
server_idle_timeout = 600  # Close idle server connections after 10 min
client_idle_timeout = 0    # Don't disconnect idle clients

# Auth
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Install and run
apt install pgbouncer
systemctl start pgbouncer

# Connect through PgBouncer (port 6432 instead of 5432)
psql -h localhost -p 6432 -U myapp myapp

Node.js pg Pool Configuration

import { Pool } from 'pg'

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,

  // Pool sizing
  max: 10,              // Maximum connections
  min: 2,              // Minimum idle connections
  idleTimeoutMillis: 30_000,    // Remove idle connections after 30s
  connectionTimeoutMillis: 2_000, // Fail if no connection in 2s

  // Health check
  allowExitOnIdle: false,
})

pool.on('error', (err) => {
  console.error('Unexpected pool error:', err)
})

// Proper release pattern
async function query(sql: string, params?: any[]) {
  const client = await pool.connect()
  try {
    return await client.query(sql, params)
  } finally {
    client.release() // ALWAYS release, even on error
  }
}

// Or use pool.query() directly (auto-releases)
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId])

HikariCP (Java)

// application.yml (Spring Boot)
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/myapp
    username: myapp
    password: secret
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      idle-timeout: 600000       # 10 minutes
      connection-timeout: 30000  # 30 seconds
      max-lifetime: 1800000      # 30 minutes
      pool-name: MyHikariPool
      connection-test-query: SELECT 1

Pool Sizing Formula

Pool size = Tn × (Cm - 1) + 1

Where:
  Tn = number of threads in the application
  Cm = time a connection is held (computation time / I/O time)

Example:
  - 10 app threads
  - Queries take 10ms total, 5ms waiting for DB
  - Cm = 10/5 = 2
  - Pool size = 10 × (2-1) + 1 = 11

But also consider:
  - Available CPU cores on DB server
  - Formula: (CPU cores × 2) + number of spindles
  - For 4-core Postgres: 4 × 2 + 1 = 9 connections

Monitoring Pool Health

// Monitor pool metrics
setInterval(async () => {
  const { totalCount, idleCount, waitingCount } = pool
  
  console.log({
    total: totalCount,      // Active + idle connections
    idle: idleCount,        // Waiting to be used
    waiting: waitingCount,  // Requests waiting for a connection
  })
  
  if (waitingCount > 0) {
    console.warn('Pool contention! Increase pool size or optimize queries')
  }
}, 10_000)

Transaction Mode Gotchas

PgBouncer transaction mode is incompatible with:

• SET session_variable (use SET LOCAL in transaction)
• Prepared statements (use server_reset_query = DISCARD ALL)
• Advisory locks
• LISTEN/NOTIFY

For these features, use session mode or connect directly.