PostgreSQL Connection Pooling with PgBouncer

Discover how PgBouncer connection pooling dramatically improves PostgreSQL performance by efficiently managing database connections and reducing resource overhead.

Overview

Connection pooling is essential for high-performance PostgreSQL deployments. PgBouncer is a lightweight, high-performance connection pooler that sits between your application and PostgreSQL, reusing database connections and dramatically improving scalability.

Performance boost: PgBouncer can reduce connection overhead by 90% and support thousands of client connections with minimal resource usage.

Why Connection Pooling Matters

The Connection Problem

PostgreSQL creates a new backend process for each connection, which:

• Consumes memory (each backend uses 5-10 MB)
• Increases overhead for connection establishment
• Limits scalability (max_connections typically 100-400)
• Degrades performance under high connection counts

How PgBouncer Solves This

PgBouncer multiplexes client connections to a smaller pool of server connections:

• Lightweight: Uses ~2KB per client connection
• Fast: Sub-millisecond connection switching
• Scalable: Supports thousands of client connections
• Efficient: Reuses server connections across clients

PgBouncer Architecture

Connection Flow

[Application] → [PgBouncer] → [PostgreSQL]
  (1000s)         (Pool)         (10-50)

How it works:

1. Applications connect to PgBouncer (port 6432)
2. PgBouncer maintains connection pool to PostgreSQL
3. Client requests are routed through pooled connections
4. Connections are released back to pool when idle

Pooling Modes

PgBouncer offers three pooling modes with different trade-offs:

1. Session Pooling

pool_mode = session

Behavior:

• Connection assigned to client for entire session
• Released only when client disconnects
• Most PostgreSQL features supported

Use case: Default mode, safest option, supports all PostgreSQL features

2. Transaction Pooling

pool_mode = transaction

Behavior:

• Connection assigned only for duration of transaction
• Released after COMMIT/ROLLBACK
• Maximum connection reuse

Use case: High-traffic applications, best performance

Limitation: Session-level features (prepared statements, temp tables) not preserved between transactions.

3. Statement Pooling

pool_mode = statement

Behavior:

• Connection assigned for single statement
• Released immediately after query completion
• Most aggressive reuse

Use case: Rarely used, very limited functionality

Warning: Multi-statement transactions NOT supported. Use with extreme caution!

Installation and Setup

Install PgBouncer

Ubuntu/Debian

sudo apt update
sudo apt install pgbouncer

CentOS/RHEL

sudo yum install pgbouncer

From Source

git clone https://github.com/pgbouncer/pgbouncer.git
cd pgbouncer
./configure
make
sudo make install

Verify Installation

pgbouncer --version

Configuration

1. Create Configuration File

Edit /etc/pgbouncer/pgbouncer.ini:

[databases]
; Database connection strings
mydb = host=localhost port=5432 dbname=mydb
production = host=10.0.1.50 port=5432 dbname=production
; Wildcard entry (connect to any database on this host)
* = host=localhost port=5432

[pgbouncer]
; Listen on all interfaces
listen_addr = *
listen_port = 6432

; Authentication
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Connection pooling mode
pool_mode = transaction

; Pool size configuration
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 5

; Maximum connections
max_client_conn = 1000
max_db_connections = 100
max_user_connections = 100

; Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Admin access
admin_users = postgres
stats_users = postgres

; Timeouts
server_idle_timeout = 600
server_lifetime = 3600
server_connect_timeout = 15
query_timeout = 0
client_idle_timeout = 0

2. Create User Authentication File

Create /etc/pgbouncer/userlist.txt:

"postgres" "md5<password_hash>"
"appuser" "md5<password_hash>"

Generate password hashes:

# Generate MD5 hash for password
echo -n "password123postgres" | md5sum
# Result: abc123... (use "md5abc123...")

Or use PostgreSQL to generate hash:

-- In PostgreSQL
SELECT 'md5' || md5('password' || 'username');

3. Set Permissions

sudo chown pgbouncer:pgbouncer /etc/pgbouncer/pgbouncer.ini
sudo chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
sudo chmod 640 /etc/pgbouncer/userlist.txt

4. Start PgBouncer

# Start service
sudo systemctl start pgbouncer

# Enable on boot
sudo systemctl enable pgbouncer

# Check status
sudo systemctl status pgbouncer

Configuration Parameters Explained

Pool Sizing

# Connections per database
default_pool_size = 25

# Minimum idle connections maintained
min_pool_size = 10

# Emergency reserve connections
reserve_pool_size = 5

Formula for sizing:

pool_size = (max_concurrent_queries / avg_query_time) * safety_margin

Example:

• 100 queries/second
• 0.1 second avg query time
• 2x safety margin
• pool_size = (100 / 10) * 2 = 20

Connection Limits

# Maximum client connections total
max_client_conn = 1000

# Maximum server connections total
max_db_connections = 100

# Maximum connections per user
max_user_connections = 100

Timeouts

# Close idle server connections after (seconds)
server_idle_timeout = 600

# Reconnect server connection after (seconds)
server_lifetime = 3600

# Server connection timeout (seconds)
server_connect_timeout = 15

# Query timeout (0 = disabled)
query_timeout = 0

# Close idle client connections after (0 = disabled)
client_idle_timeout = 0

Connecting Applications to PgBouncer

Connection String Changes

Before (Direct PostgreSQL):

postgresql://user:pass@localhost:5432/mydb

After (Through PgBouncer):

postgresql://user:pass@localhost:6432/mydb

Python (psycopg2)

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=6432,  # PgBouncer port
    database="mydb",
    user="appuser",
    password="password"
)

Node.js (pg)

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 6432,  // PgBouncer port
  database: 'mydb',
  user: 'appuser',
  password: 'password'
});

Java (JDBC)

String url = "jdbc:postgresql://localhost:6432/mydb";
Connection conn = DriverManager.getConnection(url, "appuser", "password");

Monitoring and Administration

Admin Console

Connect to PgBouncer admin console:

psql -h localhost -p 6432 -U postgres pgbouncer

Essential Admin Commands

-- Show pool status
SHOW POOLS;

-- Show active connections
SHOW CLIENTS;

-- Show server connections
SHOW SERVERS;

-- Show database configuration
SHOW DATABASES;

-- Show statistics
SHOW STATS;

-- Show configuration
SHOW CONFIG;

-- Reload configuration
RELOAD;

-- Pause all connections
PAUSE;

-- Resume connections
RESUME;

-- Gracefully shutdown
SHUTDOWN;

-- Kill specific connection
KILL <database>/<user>;

Key Metrics to Monitor

-- Pool utilization
SHOW POOLS;

Important columns:

• cl_active: Active client connections
• cl_waiting: Clients waiting for connection
• sv_active: Active server connections
• sv_idle: Idle server connections
• sv_used: Recently used connections
• maxwait: Max wait time (should be low)

-- Connection statistics
SHOW STATS;

Key metrics:

• total_xact_count: Total transactions
• total_query_count: Total queries
• total_received: Bytes received
• total_sent: Bytes sent
• total_xact_time: Total transaction time
• avg_xact_time: Average transaction time

Performance Tuning

🚀 Optimization Tips

1. Right-Size Connection Pool

# Start conservative
default_pool_size = 20

# Monitor cl_waiting in SHOW POOLS
# If frequently > 0, increase pool size
# If sv_idle > sv_active, decrease pool size

2. Use Transaction Pooling

# Best performance for stateless applications
pool_mode = transaction

Compatibility note: Ensure your application doesn't rely on session-level features (temp tables, prepared statements, advisory locks).

3. Optimize Timeouts

# Close idle server connections faster
server_idle_timeout = 300  # 5 minutes

# Recycle connections periodically
server_lifetime = 1800  # 30 minutes

# Aggressive query timeout (prevent runaway queries)
query_timeout = 30  # 30 seconds

4. Enable DNS Caching

# Reduce DNS lookup overhead
dns_max_ttl = 600
dns_nxdomain_ttl = 600
dns_zone_check_period = 60

5. Adjust OS Limits

# Increase file descriptor limit
sudo vim /etc/security/limits.conf

Add:

pgbouncer soft nofile 10000
pgbouncer hard nofile 10000

Best Practices

✅ Essential Guidelines

1. Use transaction pooling for best performance (if compatible)
2. Monitor pool utilization regularly
3. Size pools conservatively (start small, scale up)
4. Set appropriate timeouts to prevent resource exhaustion
5. Enable connection logging during troubleshooting
6. Use separate pools for different workloads
7. Secure userlist.txt with proper permissions

Application Best Practices

• Close connections promptly: Don't hold connections idle
• Use short transactions: Release connections quickly
• Avoid session state: Don't rely on temp tables, prepared statements (in transaction mode)
• Handle connection errors: Implement retry logic
• Connection pooling in app: Layer PgBouncer with application-level pooling for optimal performance

Troubleshooting

Issue 1: "no more connections allowed"

Cause: max_client_conn reached

Solution:

# Increase client connection limit
max_client_conn = 2000

Issue 2: Clients Waiting for Connections

Symptoms: cl_waiting > 0 in SHOW POOLS

Solutions:

# Increase pool size
default_pool_size = 50

# Or increase database connection limit
max_db_connections = 200

Issue 3: Authentication Failures

Cause: Incorrect userlist.txt or auth_type

Solution:

# Regenerate password hash
SELECT 'md5' || md5('password' || 'username');

# Update userlist.txt
echo '"username" "md5hash"' >> /etc/pgbouncer/userlist.txt

# Reload configuration
psql -p 6432 -U postgres pgbouncer -c "RELOAD;"

Issue 4: Prepared Statement Errors

Error: "prepared statement does not exist"

Cause: Using session-level prepared statements with transaction pooling

Solution:

# Switch to session pooling
pool_mode = session

# Or modify application to use inline queries

Advanced Configuration

Multiple Database Pools

[databases]
db1 = host=server1 pool_size=30 pool_mode=transaction
db2 = host=server2 pool_size=50 pool_mode=session
readonly = host=replica pool_size=20 pool_mode=transaction

Load Balancing Across Replicas

[databases]
; Round-robin across replicas (requires external load balancer)
mydb_read = host=replica1,replica2,replica3 port=5432

SSL/TLS Configuration

[pgbouncer]
client_tls_sslmode = require
client_tls_ca_file = /etc/ssl/certs/ca-bundle.crt
client_tls_cert_file = /etc/ssl/certs/pgbouncer.crt
client_tls_key_file = /etc/ssl/private/pgbouncer.key

server_tls_sslmode = verify-full
server_tls_ca_file = /etc/ssl/certs/ca-bundle.crt

Conclusion

PgBouncer is an essential tool for scaling PostgreSQL applications. By efficiently pooling connections, it dramatically reduces overhead, improves performance, and enables applications to support thousands of concurrent users.

Quick Decision Guide

Use PgBouncer when:

• Supporting > 100 concurrent connections
• Connection churn is high
• Application doesn't implement connection pooling
• Need to support thousands of clients
• Running out of PostgreSQL max_connections

Pool mode selection:

• Session: Safest, supports all PostgreSQL features
• Transaction: Best performance, most common
• Statement: Rarely used, very limited

Remember: Start with conservative pool sizes and monitor metrics. Tune based on actual workload patterns!

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in PostgreSQL, SQL optimization, and enterprise database solutions.

Related Articles

• Database Performance Tuning Best Practices
• Cloud Database Migration Strategies
• High Availability Database Setup

Need help with PostgreSQL optimization? Contact us for expert database consulting.