Connection Pooling for PostgreSQL with PgBouncer in a Dockerized Stack

If you've watched your PostgreSQL container fail under modest load, the cause is almost always the same: too many connections. PostgreSQL spawns a process per connection. At scale, you're spending more RAM and CPU on connection overhead than actual query work. PgBouncer sits in front of your database and fixes this at the infrastructure level, before application code ever gets involved.

Why PgBouncer Instead of Application-Level Pooling

Most frameworks ship with connection pools (SQLAlchemy, Django's CONN_MAX_AGE, Node's pg-pool). These pool per process. Run four Gunicorn workers and you've multiplied your connection count by four. Add horizontal scaling or serverless functions and the math deteriorates quickly.

PgBouncer operates at the network level. It holds a small pool of real database connections and reuses them across however many application connections arrive. One PgBouncer instance handling 500 incoming connections might maintain only 20 real connections to Postgres. That's your leverage.

The Three Pooling Modes

Before configuring, understand your options:

• Session pooling — A server connection persists for the full client session. Safest, least efficient. Compatible with LISTEN/NOTIFY and advisory locks.
• Transaction pooling — A server connection is held only during a transaction. Best for most web apps. Breaks prepared statements unless your client avoids them.
• Statement pooling — One connection per statement. Rarely appropriate.

For typical request/response backends (Next.js, Django, FastAPI): use transaction pooling. Just disable prepared statements in your client configuration.

Docker Compose Setup

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: myapp
      POSTGRES_USER: myapp_user
      POSTGRES_PASSWORD: strongpassword
    volumes:
      - pgdata:/var/lib/postgresql/data
    networks:
      - backend

  pgbouncer:
    image: edoburu/pgbouncer:latest
    environment:
      DATABASE_URL: "postgres://myapp_user:strongpassword@postgres:5432/myapp"
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 500
      DEFAULT_POOL_SIZE: 20
      AUTH_TYPE: scram-sha-256
    ports:
      - "5432:5432"
    depends_on:
      - postgres
    networks:
      - backend

  app:
    build: .
    environment:
      DATABASE_URL: "postgres://myapp_user:strongpassword@pgbouncer:5432/myapp"
    depends_on:
      - pgbouncer
    networks:
      - backend

networks:
  backend:

volumes:
  pgdata:

Your application connects to pgbouncer, not postgres directly. The Postgres container remains internal; PgBouncer is the single network entry point.

PgBouncer Configuration

The edoburu/pgbouncer image accepts configuration via environment variables. For more control, mount a pgbouncer.ini:

[databases]
myapp = host=postgres port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
listen_port = 5432
listen_addr = *

For scram-sha-256, generate credential hashes with psql and place them in userlist.txt. Use md5 during development if scram-sha-256 adds friction.

Client-Side: Disable Prepared Statements

Transaction pooling recycles server connections mid-session, breaking standard prepared statement protocols. Configure your client:

Node.js / pg:

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});
// pg-pool doesn't use prepared statements by default.

Prisma:

postgres://...?pgbouncer=true&connection_limit=1

SQLAlchemy:

engine = create_engine(
    DATABASE_URL,
    pool_pre_ping=True,
    connect_args={"prepare_threshold": None}
)

Django: Set CONN_MAX_AGE=0. Django's persistent connections assume session-level state that transaction pooling doesn't preserve.

Health Checks and Startup Ordering

Add a health check to ensure dependent containers wait:

pgbouncer:
  healthcheck:
    test: ["CMD", "pg_isready", "-h", "localhost", "-p", "5432"]
    interval: 5s
    timeout: 3s
    retries: 5

Add condition: service_healthy under your app's depends_on for pgbouncer. This prevents silent startup failures in CI and fresh deploys.

What to Monitor

Once running, watch:

• SHOW POOLS in PgBouncer's admin console — shows active, idle, and waiting connections per pool
• cl_waiting — if this climbs, increase default_pool_size or investigate slow queries
• Postgres pg_stat_activity — confirm actual backend count stays bounded

Connect to PgBouncer's admin interface: psql -p 5432 -U pgbouncer pgbouncer.

The Tradeoff

PgBouncer adds a network hop and one more service to operate. For low-traffic projects, it's overkill. For concurrent user load, multiple app replicas, or serverless deployments with constant connection churn—it pays immediate dividends. Integration takes under ten minutes. That's a fair exchange for real connection headroom.