PgBouncer Setup and Configuration: Connection Pooling for PostgreSQL

Quick Answer

• What it does: PgBouncer is a lightweight connection pooler for PostgreSQL that reduces database connection overhead by reusing server connections across many client connections.
• First checks: Verify PostgreSQL max_connections is set to 2-3x your PgBouncer default_pool_size; ensure auth_type and auth_file are configured before starting the service.
• Minimal configuration: Install via sudo apt-get install pgbouncer, then configure /etc/pgbouncer/pgbouncer.ini with listen_addr = 127.0.0.1, listen_port = 6432, auth_type = scram-sha-256, auth_file = /etc/pgbouncer/userlist.txt, and pool_mode = transaction.
• Quick start command: After configuration, run sudo systemctl start pgbouncer and connect with psql -h 127.0.0.1 -p 6432 -U your_user your_db.
• Version boundary: PgBouncer 1.8+ supports scram-sha-256 authentication; PostgreSQL 10+ required for full scram-sha-256 support.

What Problem It Solves

PostgreSQL allocates a separate OS process for each connection, making high numbers of concurrent connections expensive. Applications with many short-lived connections (typical in web frameworks like Django, Rails, or Node.js) can quickly exhaust PostgreSQL's max_connections limit, causing FATAL: remaining connection slots are reserved for non-replication superuser connections errors.

PgBouncer sits between your application and PostgreSQL, maintaining a small pool of persistent server connections and multiplexing many client connections across them. This reduces connection overhead, improves response times, and protects PostgreSQL from connection spikes.

Installation and Quick Start

Install PgBouncer on Debian/Ubuntu:

BASH
sudo apt-get update
sudo apt-get install pgbouncer

After installation, the default configuration file is at /etc/pgbouncer/pgbouncer.ini and the authentication file at /etc/pgbouncer/userlist.txt.

Minimal Working Configuration

Create a minimal /etc/pgbouncer/pgbouncer.ini:

INI
[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
server_idle_timeout = 600

Create the authentication file /etc/pgbouncer/userlist.txt:

"your_user" "your_password"

The password must be the plaintext password or a SCRAM-SHA-256 hash. Generate a SCRAM hash with:

BASH
psql -c "SELECT pgp_sym_encrypt('your_password', 'pgbouncer')"  # Not this
# Instead, use the PostgreSQL scram secret directly:
psql -c "SELECT rolpassword FROM pg_authid WHERE rolname='your_user';"

Then start PgBouncer:

BASH
sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

Test the connection:

BASH
psql -h 127.0.0.1 -p 6432 -U your_user your_db

Parameters and Environment Variables

Root Cause Analysis

PgBouncer's core mechanism is connection multiplexing. When a client connects, PgBouncer assigns it a server connection from the pool. In transaction mode (the most common and recommended), the server connection is returned to the pool after each transaction commits or rolls back. This means 1000 client connections can share just 25 server connections.

The critical trade-off: session-level state (temporary tables, SET statements, prepared statements, LISTEN/NOTIFY) does not persist across transactions in transaction mode. When a client issues a SET timezone = 'UTC' and then starts a new transaction, the next transaction may run on a different server connection that hasn't applied that SET.

Common Errors and Fixes

FATAL: remaining connection slots are reserved for non-replication superuser connections

Cause: PostgreSQL has reached its max_connections limit. PgBouncer's server connections plus any direct connections have exhausted the pool.

Solution:

1. Check SHOW POOLS in PgBouncer admin console for cl_waiting > 0.
2. Increase default_pool_size in PgBouncer config.
3. Increase PostgreSQL max_connections (set to 2-3x default_pool_size).
4. Reduce max_client_conn if too many clients are overwhelming the pool.

SQL
-- Connect to PgBouncer admin console
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer
-- Then run:
SHOW POOLS;

ERROR: prepared statement "..." already exists (or does not exist)

Cause: Server-side prepared statements are session-level. In transaction mode, the prepared statement is lost when the transaction ends, and the next transaction on a different server connection doesn't have it.

Solution:

• Disable prepared statements in your application driver:
  • psycopg2 (Python): Set prepare_threshold=None in connection parameters.
  • node-postgres: Set { prepare: false } in query config.
  • JDBC: Set prepareThreshold=0 in connection URL.
• Or switch to session mode if prepared statements are essential.

ERROR: SET command cannot be used in transaction mode

Cause: SET statements (e.g., SET timezone, SET search_path) are session-level and don't persist across transactions in transaction mode.

Solution:

• Execute SET statements inside a transaction block.
• Configure server_reset_query in PgBouncer to reset session state on connection reuse.
• If your application heavily depends on session-level settings, use session mode instead.

Connection refused: connect to 127.0.0.1 port 6432 failed

Cause: PgBouncer is not running or not listening on the expected address/port.

Solution:

1. Check service status: systemctl status pgbouncer
2. Verify listen_addr and listen_port in config.
3. Check firewall rules: sudo ufw status or iptables -L
4. Inspect logs: tail -f /var/log/pgbouncer/pgbouncer.log

Production Notes and Security Checks

Security Hardening

1. Authentication: Always use auth_type = scram-sha-256. Avoid plain or md5 as they are less secure.
2. File permissions: Restrict access to userlist.txt:

   BASH
   sudo chmod 600 /etc/pgbouncer/userlist.txt
   sudo chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
   

3. Network binding: Set listen_addr = 127.0.0.1 for local-only access, or bind to a private network IP. Never expose PgBouncer to the public internet.
4. SSL: Enable SSL for both client-to-PgBouncer and PgBouncer-to-PostgreSQL connections.

Resource Limits

• max_client_conn too high can exhaust PgBouncer's memory. Each client connection uses approximately 2-4 KB.
• default_pool_size too high reduces the benefit of pooling. Start with 20-50 and monitor.
• Monitor SHOW STATS for avg_wait_time — if it's consistently above 0, increase pool size.

Monitoring

Connect to the PgBouncer admin console:

BASH
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

Key commands:

• SHOW POOLS — Check cl_waiting (clients waiting for a connection) and sv_idle (idle server connections).
• SHOW STATS — Check avg_query_time and avg_wait_time.
• SHOW CLIENTS — View connected clients.
• SHOW SERVERS — View server connections.

High Availability

PgBouncer itself is a single point of failure. For production:

• Deploy multiple PgBouncer instances behind a load balancer (HAProxy, Nginx).
• Use Kubernetes with multiple PgBouncer replicas and a Service for load balancing.
• Consider PgBouncer as a Sidecar container in Kubernetes for per-pod pooling.

Comparison With Alternatives

When to choose PgBouncer: You need a fast, simple, resource-efficient connection pooler for high-concurrency applications. Ideal for stateless APIs, microservices, and containerized environments.

When to choose pgpool-II: You need built-in load balancing, automatic failover, or advanced query routing alongside connection pooling.

FAQ

Q: My application uses Django ORM, which PgBouncer mode should I choose?

A: Use transaction mode. Django's ORM automatically commits transactions at the end of each HTTP request, making it a good fit. However: 1) Django's SET statements (like SET timezone) must be inside transactions. 2) If you use session-level features like temporary tables or django.db.connection for raw SQL, consider session mode. 3) Set CONN_MAX_AGE to 0 or a low value in Django's DATABASES config to prevent long-lived connections.

Q: How do I monitor PgBouncer performance and pool status?

A: Connect to the admin console with psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer. Key commands: SHOW POOLS (check cl_waiting and sv_idle), SHOW STATS (check avg_query_time and avg_wait_time), SHOW CLIENTS, and SHOW SERVERS. Integrate these metrics into Prometheus/Grafana using the PgBouncer Exporter, and set alerts for cl_waiting > 0 for more than 5 minutes.

Q: How should I deploy PgBouncer in Kubernetes?

A: Recommended approaches: 1) Sidecar pattern: Deploy PgBouncer as a sidecar container in the same pod as your application, allowing localhost connections. 2) Standalone Service: Deploy as a Deployment or StatefulSet with a ClusterIP Service. Use ConfigMaps for configuration and Secrets for authentication files. Set resource limits (CPU/memory) and configure max_client_conn to prevent overload. For high availability, run multiple replicas behind a Service. Use the PgBouncer Exporter to export metrics to Prometheus.