PgBouncer — Lightweight Connection Pooler for PostgreSQL

Introduction

PgBouncer is the standard connection pooler for PostgreSQL deployments. PostgreSQL creates a new process for each connection, which is expensive at scale. PgBouncer maintains a pool of reusable server connections and maps incoming client connections to them, dramatically reducing resource usage and connection latency.

What PgBouncer Does

• Pools PostgreSQL connections to reduce per-connection process overhead
• Supports three pool modes: session, transaction, and statement-level
• Handles thousands of client connections with minimal memory (about 2KB per connection)
• Provides online restart and configuration reload without dropping clients
• Exposes an admin console for live monitoring and pool management

Architecture Overview

PgBouncer is a single-threaded event-loop process using libevent. Client connections are accepted and authenticated, then mapped to server connections from a per-database pool. In transaction mode, a server connection is assigned only for the duration of a transaction, then returned to the pool. The lightweight design means a single PgBouncer instance uses very little CPU and memory even with thousands of concurrent clients.

Self-Hosting & Configuration

• Install via package manager or compile from source (pure C, minimal dependencies)
• Configure databases section to map logical names to actual PostgreSQL hosts
• Set pool_mode to transaction for most web applications (best multiplexing)
• Tune default_pool_size based on your PostgreSQL max_connections setting
• Use auth_type = hba or md5 to authenticate clients against PostgreSQL

Key Features

• Extremely lightweight: 2KB per connection vs PostgreSQL's ~10MB per process
• Transaction-mode pooling maximizes connection reuse for web workloads
• Online restart preserves client connections during upgrades
• DNS-based host resolution for failover and load balancing
• Compatible with all PostgreSQL client libraries transparently

Comparison with Similar Tools

• Pgpool-II — More features (replication, load balancing) but heavier and more complex
• Odyssey — Multi-threaded pooler from Yandex for higher throughput on many cores
• Supavisor — Elixir-based multi-tenant pooler for serverless; PgBouncer is simpler
• Built-in pooling (application side) — Per-app pools waste connections; PgBouncer centralizes
• Amazon RDS Proxy — Managed equivalent for AWS; PgBouncer is self-hosted and free

FAQ

Q: Which pool mode should I use? A: Transaction mode for most web apps. Session mode if your app uses prepared statements or session-level features. Statement mode only for simple autocommit workloads.

Q: Can PgBouncer handle TLS? A: Yes, it supports TLS on both client-facing and server-facing connections.

Q: Does it work with prepared statements? A: In transaction mode, prepared statements require PgBouncer 1.21+ with prepared_statement tracking enabled.

Q: How many connections can a single PgBouncer handle? A: Easily 10,000+ client connections depending on workload. It is single-threaded, so CPU-bound at very high throughput you might run multiple instances.

Sources

• https://github.com/pgbouncer/pgbouncer
• https://www.pgbouncer.org/config.html