What is PgBouncer?
PgBouncer is a lightweight connection pooler for PostgreSQL that sits between your application and database server. It maintains a pool of reusable database connections, dramatically reducing connection overhead and allowing applications to handle thousands of concurrent clients with just a handful of actual database connections. Written in C, PgBouncer has minimal memory footprint and excellent performance characteristics.
Unlike heavyweight solutions, PgBouncer focuses solely on connection pooling without trying to understand SQL semantics. This makes it fast, reliable, and compatible with any PostgreSQL client or framework. It's particularly valuable for web applications, microservices, and any high-concurrency workload where connection management is critical.
PgBouncer Performance Calculator
Memory Usage: 1100KB
Avg Latency: 5ms
Connection Reuse: 99%
PgBouncer Pooling Modes
Session Pooling
Safest mode - connection assigned per client session.
• Prepared statements work
• Lower pooling efficiency
• Best for existing applications
Transaction Pooling
Balanced mode - connection returned after each transaction.
• Most PostgreSQL features work
• Some prepared statement limits
• Recommended for most apps
Statement Pooling
Highest efficiency - connection returned after each query.
• No transactions across queries
• No prepared statements
• Best for simple workloads
PgBouncer Configuration
pgbouncer.ini Configuration
[databases]
myapp = host=localhost port=5432 dbname=myapp user=dbuser
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
max_db_connections = 100
max_user_connections = 100
# Connection limits
server_round_robin = 1
ignore_startup_parameters = extra_float_digits
# Logging
admin_users = pgbouncer
stats_users = pgbouncer
log_connections = 1
log_disconnections = 1
# Timeouts
server_connect_timeout = 15
query_timeout = 0
query_wait_timeout = 120
# Authentication
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
# Performance tuning
tcp_keepalive = 1
tcp_keepcnt = 3
tcp_keepidle = 600
tcp_keepintvl = 30Docker Deployment
version: '3.8'
services:
postgres:
image: postgres:15
environment:
POSTGRES_DB: myapp
POSTGRES_USER: dbuser
POSTGRES_PASSWORD: dbpass
volumes:
- postgres_data:/var/lib/postgresql/data
pgbouncer:
image: pgbouncer/pgbouncer:latest
environment:
DATABASES_HOST: postgres
DATABASES_PORT: 5432
DATABASES_USER: dbuser
DATABASES_PASSWORD: dbpass
DATABASES_DBNAME: myapp
POOL_MODE: transaction
MAX_CLIENT_CONN: 1000
DEFAULT_POOL_SIZE: 25
ports:
- "6432:5432"
depends_on:
- postgres
app:
build: .
environment:
DATABASE_URL: postgresql://dbuser:dbpass@pgbouncer:5432/myapp
depends_on:
- pgbouncer
volumes:
postgres_data:Real-World PgBouncer Implementations
Heroku
Uses PgBouncer to manage connections for thousands of applications on their PostgreSQL service.
- • Connection pooling for multi-tenant apps
- • Automatic failover and load balancing
- • Custom monitoring and alerting
- • 10,000+ concurrent connections per instance
Leverages PgBouncer to manage database connections for their high-traffic photo sharing platform.
- • Transaction pooling for web requests
- • Reduced connection overhead by 90%
- • Enhanced database stability during traffic spikes
- • Integration with Django applications
GitLab
Implements PgBouncer for connection pooling in their DevOps platform serving millions of users.
- • Multi-database connection management
- • Session pooling for Rails applications
- • Monitoring with Prometheus integration
- • High availability configuration
Discourse
Uses PgBouncer to efficiently manage database connections for their forum platform.
- • Connection pooling for Ruby on Rails
- • Reduced database server load
- • Better handling of traffic bursts
- • Simplified scaling architecture
PgBouncer Best Practices
✅ Do
- • Start with transaction pooling for most applications
- • Monitor connection pool utilization regularly
- • Set appropriate timeouts for your workload
- • Use authentication files instead of inline passwords
- • Enable logging for troubleshooting
- • Test failover scenarios thoroughly
❌ Don't
- • Use statement pooling with transaction-heavy apps
- • Set pool sizes too low for high-concurrency workloads
- • Ignore connection timeout configurations
- • Mix different application types in same pool
- • Forget to secure admin interface access
- • Skip monitoring pool health and performance