PgBouncer for Production Applications

🚨 The Problem

While working on our production system, I noticed something concerning in PostgreSQL:

SELECT count(*) FROM pg_stat_activity;

Result: 1176 connections (active - 17, idle - rest)

This created:

• Unnecessary memory usage (huge server high cost)
• Risk of hitting connection limits on the database (performance going down)
• Inefficient database utilization

Headache right?

💬 Wonder why this happens?

In microservices/Kubernetes environments:

• Each pod creates its own DB connections
• Scaling pods = scaling connections
• Idle connections stay open

And this leads to high connection count without actual load.

💡 The Solution: PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL.

Instead of: App Pods → PostgreSQL

We use: App Pods → PgBouncer → PostgreSQL

Deployment Options

Option 1: Deploy it as a sidecar container on the production workload — but it's pointless to run as many containers as your production application pods.

Option 2 (chosen): Deploy it as a shared PgBouncer pool (centralized deployment). Deploy once and use it across multiple projects.

PgBouncer Deployment

apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgbouncer-pool
spec:
  replicas: 2
  template:
    spec:
      containers:
        - name: pgbouncer
          image: edoburu/pgbouncer:v1.25.1
          ports:
            - containerPort: 6432

Service

apiVersion: v1
kind: Service
metadata:
  name: pgbouncer
spec:
  ports:
    - port: 6432

PgBouncer Configuration

Add this as a Kubernetes Secret and attach it to the PgBouncer deployment:

[databases]
<db-name> = host=<db-host> port=5432 pool_size=25 user=<user> password=<password>

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Connection pool configuration
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
admin_users = postgres

; Server connection settings
server_lifetime = 3600
server_idle_timeout = 600
server_connect_timeout = 15

; Performance tuning
max_db_connections = 50
max_user_connections = 50
ignore_startup_parameters = extra_float_digits

; Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

🔑 Updated Connection String

Your application database connection string would now point to PgBouncer:

postgres://user:pass@pgbouncer:6432/<db-name>

🔭 Observability

To understand PgBouncer behavior, use:

SHOW POOLS;

Key insight: Client connections >> Server connections — which means pooling is working.

📉 Before vs After

🔭 Further Monitoring

• Set up a Prometheus exporter for PgBouncer metrics
• Create Grafana dashboards to visualise pool behaviour
• Tune pool sizes based on real traffic patterns

If you're working on similar scaling problems or DevOps challenges, feel free to reach out or share your thoughts.