1 Million Postgres Connections

Scaling PostgreSQL to handle 1 million concurrent clients is impossible directly, but with proper architecture, pooling, and load balancing, it can be achieved safely.

This article explains a practical setup using:

• PostgreSQL primary and replicas
• PgBouncer connection poolers
• Traefik TCP load balancer
• Docker Swarm orchestration

---

Server Specifications

For a production-grade setup:

Component	Count	VM/Host	CPU	RAM	Storage
PostgreSQL Primary	1	VM1	16	128 GB	NVMe 1 TB RAID-10
PostgreSQL Replica	2	VM1 + VM2	16	128 GB	NVMe 1 TB RAID-10
PgBouncer (Docker Swarm)	20–100	VM3 (Manager)	8	64 GB	Shared network
Docker Swarm Manager	1	VM3	8	64 GB	For orchestration

info

PostgreSQL sees only the sum of all PgBouncer backend connections, not the 1 million clients.

---

PostgreSQL Configuration

In each PostgreSQL container:

postgresql.conf

max_connections = 500             # realistic
shared_buffers = 25GB             # ~25% of RAM
work_mem = 64MB
maintenance_work_mem = 2GB

Memory calculation:

• Shared buffers: 25 GB
• Per-connection memory: 500 × (64 MB + 10 MB overhead) ≈ 37 GB
• Maintenance: 2 GB
• OS/container overhead: ~5 GB

Total per PG container

~69 GB → fits in 128 GB RAM VMs.

---

PgBouncer Configuration

PgBouncer is used to pool millions of clients while keeping PostgreSQL backend connections low.

docker-compose.yml

version: '3.8'
services:
  pg_bouncer:
    image: edoburu/pgbouncer
    deploy:
      replicas: 20                 # scale horizontally
      restart_policy:
        condition: on-failure
      resources:
        limits:
          cpus: '1.0'
          memory: 2G
    environment:
      DATABASE_URL: "postgres://pg_user:pg_password@pg_primary:5432/yourdb"
      POOL_MODE: transaction
      DEFAULT_POOL_SIZE: 25
      MAX_CLIENT_CONN: 50000
    networks:
      - pg_net
    labels:
      - "traefik.enable=true"
      - "traefik.tcp.routers.pg_bouncer.rule=HostSNI(`*`)"
      - "traefik.tcp.routers.pg_bouncer.entrypoints=pg"
      - "traefik.tcp.services.pg_bouncer.loadbalancer.server.port=6432"

networks:
  pg_net:
    driver: overlay

Key points:

• default_pool_size = 25 → each PgBouncer instance opens 25 backend connections to PostgreSQL.

• max_client_conn = 50,000 → each PgBouncer can accept up to 50k client connections.

• 20 PgBouncer instances → 1 million clients served:

  20 × 50,000 = 1,000,000 clients

• PostgreSQL sees only 20 × 25 = 500 backend connections → matches max_connections.

---

Traefik Load Balancer

Traefik distributes client connections across PgBouncer replicas:

traefik.yml

entryPoints:
  pg:
    address: ":6432"

providers:
  docker: {}

• Acts as a TCP load balancer in front of PgBouncer replicas.
• Ensures clients are evenly distributed.
• Supports horizontal scaling: add more PgBouncer instances, and Traefik automatically routes traffic.

---

Architecture Overview

• PgBouncer queues clients internally.
• PostgreSQL sees only the sum of backend connections.
• Transaction pooling allows short-lived connections → efficient for millions of clients.

---

Practical Considerations

Important

Always ensure sum(all PgBouncer default_pool_size) ≤ PostgreSQL max_connections.

1. Max connections: Always ensure the sum of all PgBouncer pool sizes doesn't exceed PostgreSQL's max_connections.
2. Scaling: Add PgBouncer replicas and adjust pool sizes for high client counts.
3. Monitoring: Watch PG resource usage and PgBouncer queue lengths.
4. Networking: High open file limits (ulimit -n) and TCP tuning required on all VMs.
5. Persistence: NVMe RAID-10 recommended for PG data and WAL logs.

---

Summary

• 1 million clients are supported using PgBouncer horizontal scaling + load balancing via Traefik.
• PostgreSQL handles only hundreds of real connections → safe and efficient.
• Docker Swarm orchestrates PgBouncer replicas and PostgreSQL containers.
• This architecture is production-ready, scalable, and horizontally extensible.

Next Steps

• Monitor your PgBouncer instances with tools like pgBouncer stats
• Set up alerting for connection pool saturation
• Consider read replicas for read-heavy workloads