Database Connection Pooling: PgBouncer, HikariCP, and Sizing Your Pool Correctly

Database connection exhaustion is one of the most common production failures in web applications. The symptom: everything works fine in development and staging, then the app is hammered with traffic and suddenly every request times out with "too many connections" or "connection refused." By the time the alert fires, users are already experiencing errors.

Connection pooling prevents this by managing a finite set of database connections efficiently across many concurrent application processes. This guide covers how poolers work, how to configure them, and — critically — how to size the pool correctly for your workload.

---

Why Connections Are Expensive

A PostgreSQL connection is not a lightweight object. Each connection:

• Starts a new backend process in PostgreSQL (fork, ~5MB memory)
• Maintains its own transaction state, prepared statement cache, and session variables
• Consumes a file descriptor on both the client and server

PostgreSQL's max_connections default is 100. RDS instances set it based on instance RAM (roughly 1 connection per 10MB RAM). An r6g.large (16GB RAM) allows ~1,600 connections — sounds like a lot until you have 10 ECS tasks each with a 200-connection pool.

The math that catches teams off guard:

10 API pods × 200 pool size = 2,000 connections
= exceeds max_connections on any instance class

And that's before accounting for read replicas, migration tools, monitoring agents, and admin connections.

---

The Pooler Options

---

PgBouncer: The PostgreSQL Standard

PgBouncer is a lightweight proxy that sits between your application and PostgreSQL. Applications connect to PgBouncer; PgBouncer maintains a smaller pool of real connections to Postgres.

Pool modes — the most important decision:

Transaction mode is almost always what you want. It allows PgBouncer to serve N clients with a much smaller server connection pool because connections are only held while a transaction is active.

PgBouncer configuration (pgbouncer.ini):

[databases]
; Application connects to "myapp" on port 5432 of PgBouncer
; PgBouncer connects to actual PostgreSQL
myapp = host=postgres.internal port=5432 dbname=myapp

[pgbouncer]
listen_port = 5432
listen_addr = 0.0.0.0

; Authentication
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Pool mode
pool_mode = transaction

; The max connections PgBouncer will open to PostgreSQL
server_pool_size = 25        ; Per database/user pair
max_client_conn = 10000      ; Max app connections to PgBouncer

; Connection lifecycle
server_idle_timeout = 600    ; Close idle server connections after 10 min
client_idle_timeout = 0      ; Don't close idle client connections
server_lifetime = 3600       ; Recycle server connections every hour
server_connect_timeout = 15  ; Connection timeout

; Tuning
reserve_pool_size = 5        ; Extra connections for high-demand moments
reserve_pool_timeout = 3     ; Seconds to wait before using reserve pool

; Monitoring
stats_period = 60
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

admin_users = pgbouncer_admin

Docker Compose with PgBouncer sidecar:

services:
  api:
    image: myapp/api:latest
    environment:
      # App connects to PgBouncer, not directly to Postgres
      DATABASE_URL: postgresql://user:pass@pgbouncer:5432/myapp
    depends_on:
      - pgbouncer

  pgbouncer:
    image: edoburu/pgbouncer:latest
    environment:
      DB_USER: myapp_user
      DB_PASSWORD: ${DB_PASSWORD}
      DB_HOST: postgres.rds.amazonaws.com
      DB_NAME: myapp
      POOL_MODE: transaction
      SERVER_POOL_SIZE: 25
      MAX_CLIENT_CONN: 1000
      AUTH_TYPE: scram-sha-256
    ports:
      - "5432:5432"

---

Sizing the Pool: The Formula

The most common mistake: setting pool size to "a big number just in case." This causes connection exhaustion at the Postgres level.

Optimal pool size formula (from PostgreSQL documentation):

pool_size = (number_of_cores * 2) + effective_spindle_count

For cloud databases without physical spindles, use:

pool_size = number_of_vCPUs * 2

An r6g.2xlarge RDS (8 vCPUs) → optimal pool size ≈ 16 connections.

This seems surprisingly small, but it's backed by queuing theory: a larger pool doesn't increase throughput once CPU is saturated — it increases latency because queries queue longer waiting for a saturated CPU rather than running immediately.

Real-world example:

RDS r6g.2xlarge (8 vCPU)
Optimal max connections used by app: ~20 (2x vCPUs + 4 buffer)
PgBouncer server pool: 20 connections to Postgres
PgBouncer client connections: up to 5,000

50 API pods × 20 client connections each = 1,000 client connections
→ All served through PgBouncer's 20 Postgres connections
→ Postgres sees 20 connections, not 1,000

---

Application-Level Pool (Node.js with pg)

Even with PgBouncer, your application needs its own pool for connections to PgBouncer:

// lib/db.ts
import { Pool, PoolClient } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                    // Max connections per pod to PgBouncer
  min: 2,                     // Keep 2 connections warm
  idleTimeoutMillis: 30_000,  // Release idle connections after 30s
  connectionTimeoutMillis: 5_000,  // Timeout if no connection available in 5s
  statement_timeout: 30_000,  // Kill queries running > 30s
  query_timeout: 30_000,
});

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

// Expose query function with automatic connection management
export async function query<T>(
  text: string,
  params?: unknown[]
): Promise<T[]> {
  const start = Date.now();
  const res = await pool.query(text, params);
  const duration = Date.now() - start;

  if (duration > 1000) {
    console.warn('Slow query detected:', { text, duration, rows: res.rowCount });
  }

  return res.rows;
}

// For transactions — acquire connection explicitly
export async function withTransaction<T>(
  fn: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

// Graceful shutdown
export async function closePool(): Promise<void> {
  await pool.end();
}

---

Prisma Pool Configuration

Prisma uses its own internal connection pool (based on connection_limit):

// schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  // Connection pool settings via URL parameters:
  // ?connection_limit=5&pool_timeout=10&connect_timeout=10
}

Or in the connection string:

DATABASE_URL=postgresql://user:pass@pgbouncer:5432/myapp?connection_limit=5&pool_timeout=10

Warning for serverless: In Lambda or Vercel Functions, each invocation creates a new Prisma client unless you implement client sharing. With 1,000 concurrent Lambda invocations each holding 5 connections = 5,000 connections, which breaks any database.

// lib/prisma.ts — singleton for serverless
import { PrismaClient } from '@prisma/client';

declare global {
  // eslint-disable-next-line no-var
  var __prisma: PrismaClient | undefined;
}

export const db =
  global.__prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development' ? ['query', 'warn', 'error'] : ['error'],
  });

if (process.env.NODE_ENV !== 'production') {
  global.__prisma = db;  // Reuse across hot reloads in development
}

For true serverless at scale, use RDS Proxy (AWS) or Supabase (which includes PgBouncer) rather than direct Prisma connections.

---

Monitoring Pool Health

-- Active connections by application
SELECT 
  application_name,
  state,
  COUNT(*) AS connection_count,
  MAX(EXTRACT(EPOCH FROM (NOW() - state_change))) AS max_idle_seconds
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY application_name, state
ORDER BY connection_count DESC;

-- Connections near the limit
SELECT 
  COUNT(*) AS total,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max,
  ROUND(COUNT(*) * 100.0 / (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 1) AS pct_used
FROM pg_stat_activity;

-- Long-running queries (candidates for timeout tuning)
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - pg_stat_activity.query_start > interval '5 seconds'
ORDER BY duration DESC;

Alert thresholds:

• Connection usage > 80% of max_connections → warning
• Connection usage > 90% → critical
• Any query running > 30 seconds → warning
• PgBouncer client wait time p99 > 100ms → investigate pool size

---

Cost Impact

PgBouncer in transaction mode typically allows you to use a smaller RDS instance class (half the cost) because you're not wasting server connections on idle clients.

---

Working With Viprasol

Connection pool misconfiguration is one of the most common causes of production incidents we diagnose in client systems — often invisible in testing, catastrophic under real load. We review and configure connection pooling as part of database performance audits.

→ Talk to our database team about your database architecture.

---

See Also

• PostgreSQL Performance — query optimization and indexing
• PostgreSQL vs MongoDB — database selection
• Redis Use Cases — caching to reduce database load
• Serverless Architecture — connection pooling challenges in serverless
• Web Development Services — backend engineering and database work