Connection Pooling

Overview

Connection pooling maintains a set of pre-established database connections that are lent to application threads and returned after use. Establishing a TCP connection and authenticating to a database takes 10-100ms; reusing an existing connection takes microseconds. Pools have a fixed maximum size that must match the database server's max_connections configuration. PgBouncer, Sequelize pool, pg module pool, and ActiveRecord connection pool all implement this pattern.

Origin

Connection pooling became necessary as web applications scaled beyond single-thread models. Java's JDBC Connection Pool (Apache DBCP, 2001) was an early implementation. PgBouncer (Marko Kreen, 2007) became the standard PostgreSQL connection pooler, supporting session, transaction, and statement modes. AWS RDS Proxy (2020) and PlanetScale Vitess built pooling into managed database services.

Examples

import { Pool, PoolConfig } from 'pg';

const poolConfig: PoolConfig = {
  connectionString: process.env.DATABASE_URL,
  max: 20,              // Maximum pool size; match to DB max_connections / app instances
  min: 2,               // Minimum idle connections kept warm
  idleTimeoutMillis: 30000,  // Release idle connections after 30s
  connectionTimeoutMillis: 5000, // Fail fast if pool is exhausted
  allowExitOnIdle: false, // Keep the process alive (production default)
};

export const pool = new Pool(poolConfig);

pool.on('error', (err) => {
  console.error('Idle client error:', err.stack);
  process.exit(1);
});

// Always release connections back to the pool
export async function withConnection<T>(
  fn: (client: import('pg').PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    return await fn(client);
  } finally {
    client.release(); // Returns connection to pool; ALWAYS release
  }
}

// Pool metrics for monitoring
setInterval(() => {
  console.log({
    totalCount: pool.totalCount,
    idleCount: pool.idleCount,
    waitingCount: pool.waitingCount,
  });
}, 60000);

# /etc/pgbouncer/pgbouncer.ini
# PgBouncer sits between the app and PostgreSQL
# Transaction mode: connection returned to pool after each transaction
# Allows many more app connections than PostgreSQL max_connections

# [databases]
# production = host=postgres.internal port=5432 dbname=app_production

# [pgbouncer]
# listen_addr = 0.0.0.0
# listen_port = 5432
# auth_type = scram-sha-256
# auth_file = /etc/pgbouncer/userlist.txt

# Pool sizing:
# pool_mode = transaction          # Return connection after each txn
# max_client_conn = 1000           # Max simultaneous app connections
# default_pool_size = 25           # Connections to PostgreSQL per database
# min_pool_size = 5
# reserve_pool_size = 5            # Emergency connections
# reserve_pool_timeout = 5

# Rails database.yml pointing to PgBouncer:
# production:
#   adapter: postgresql
#   host: pgbouncer.internal
#   port: 5432
#   database: production
#   pool: 5          # Rails pool per Puma thread; total = threads * pool
#   checkout_timeout: 5
#   prepared_statements: false  # REQUIRED for transaction mode pooling

Use Cases

• 01Web applications with multiple Puma workers/threads or Node.js cluster processes where each process needs database access but total connections must stay within PostgreSQL limits
• 02Serverless functions (Lambda, Cloud Run) where many concurrent invocations would each open a connection without pooling, exhausting max_connections
• 03Microservice architectures where many services connect to a shared database; PgBouncer aggregates connections from all services
• 04High-concurrency APIs where connection establishment latency would add 50-100ms to response time without pooling

When Not to Use

• //Do not use PgBouncer in transaction mode with applications that use PostgreSQL advisory locks, LISTEN/NOTIFY, or SET LOCAL for session variables; these are connection-scoped and break in transaction mode
• //Do not set pool size larger than max_connections / number_of_app_instances; the excess creates connection queue wait rather than faster processing
• //Do not rely on connection pooling to fix slow queries; a pool of 20 connections to a query that takes 5 seconds still bottlenecks at 4 requests/second

Technical Notes

• PostgreSQL max_connections (default 100) limits total simultaneous client connections. Each connection uses ~5-10MB of RAM on the server. RDS instance types impose lower limits; db.t3.micro has max_connections ~87
• PgBouncer session mode: one PostgreSQL connection per client session (same as without pooling). Transaction mode: connection returned to pool after COMMIT/ROLLBACK; allows N:1 multiplexing. Statement mode: connection returned after each statement; incompatible with multi-statement transactions
• Sequel (Ruby ORM) and Sequel::ConnectionPool expose pool_timeout (wait duration before error), max_connections, and pool_sleep_time. The Sequel logger at level :debug logs connection checkout/release events for diagnosing pool exhaustion
• AWS RDS Proxy handles connection pooling transparently and also provides IAM authentication, automatic failover connection rerouting, and connection multiplexing without PgBouncer management overhead