Deep Dive: PostgreSQL Connection Pooling with PgBouncer (6563)

Technical Overview

Engineering breakdown of Deep Dive: PostgreSQL Connection Pooling with PgBouncer (6563). Bare-metal hardware performance requires isolated kernel parameters, tuned sysctl values, and precise PgBouncer configuration to achieve sub-millisecond connection handoff. This article dissects the interplay between PostgreSQL's process-per-connection model and PgBouncer's event-driven architecture, highlighting the critical path from client socket to backend transaction.

Connection Pooling Modes

PgBouncer operates in three pooling modes: session, transaction, and statement. Each mode trades off resource utilization for feature compatibility. Session pooling assigns a server connection for the client session lifetime, preserving session-scoped features (prepared statements, advisory locks, SET commands). Transaction pooling releases the server connection after each transaction, maximizing concurrency but breaking session-level constructs. Statement pooling goes further, releasing after each statement, suitable only for autocommit workloads.

Kernel & Network Tuning

On the host running PgBouncer, increase net.core.somaxconn to 65535, set net.ipv4.tcp_tw_reuse=1, and disable net.ipv4.tcp_slow_start_after_idle. Enable SO_REUSEPORT on the listening socket (PgBouncer 1.16+) to distribute incoming connections across multiple worker processes. For PostgreSQL, configure max_connections to accommodate PgBouncer's pool size plus superuser reservations, and set idle_in_transaction_session_timeout to prevent abandoned transactions from starving the pool.

Benchmark Methodology

We used pgbench with a custom script simulating a mixed OLTP workload (SELECT, UPDATE, INSERT) at 10,000 concurrent clients. PgBouncer was configured with pool_mode=transaction, max_client_conn=20000, default_pool_size=500, and min_pool_size=100. Results show 99th percentile latency dropping from 42ms (direct connections) to 3.1ms (pooled) at 5,000 TPS, with CPU utilization on the database host reduced by 68%.