Without PgBouncer:
- 1000 connections = ~10GB RAM
- Connection time: 30ms average
- Max sustainable connections: ~500

With PgBouncer:
- 1000 connections = ~2MB RAM
- Connection time: <1ms
- Max sustainable connections: 10,000+

┌─────────────────┐
│   Applications  │
│  (Port 6432)    │
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│    PgBouncer    │  ← Connection Pooler
│  - Auth: SCRAM  │
│  - Pool: 25/DB  │
│  - Max: 1000    │
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│   PostgreSQL    │  ← Database Server
│   (Port 5432)   │
│  - Max: 100     │
└─────────────────┘

# Salt Master Configuration
/etc/salt/master.d/
├── fileserver.conf    # Git fileserver backend
└── gitfs.conf         # Git repository configuration

# Salt Minion (on database server)
/etc/salt/minion.d/
└── minion.conf        # Master connection

salt/
├── pillar/
│   ├── common.sls            # Configuration data
│   └── top.sls               # Pillar targeting
├── salt-states/
│   ├── top.sls               # State targeting
│   ├── firewall/
│   │   └── init.sls          # Firewall rules
│   ├── grains/
│   │   └── init.sls          # Role assignment
│   └── pgbouncer/
│       ├── init.sls          # Main state file
│       ├── pgbouncer.ini.j2  # Config template
│       └── pgbouncer.service # Systemd unit

# Set server roles based on hostname patterns
set_roles_grain:
  grains.present:
    - name: roles
    - value:
      {% set hostname = grains.get('id', '') | lower %}
      {% set roles = [] %}

      # Database role
      {% if 'dbsrv' in hostname or hostname.startswith('db-') or 'database' in hostname or 'postgres' in hostname %}
        {% do roles.append('database') %}
      {% endif %}

      # Add more role patterns as needed

      {{ roles }}
    - force: True

# PostgreSQL configuration
postgresql:
  port: 5432
  firewall_allowed_ips:
    - 10.0.1.10/32 # App server 1
    - 10.0.1.11/32 # App server 2
    - 10.0.2.0/24 # Kubernetes pod network
    - 203.0.113.50/32 # Admin workstation

# PgBouncer configuration
pgbouncer:
  # Network settings
  listen_port: 6432
  listen_addr: "*"

  # Authentication (must match PostgreSQL)
  auth_type: scram-sha-256

  # Pooling strategy
  pool_mode: transaction # transaction | session | statement

  # Connection limits
  max_db_connections: 100 # Max to PostgreSQL per database
  max_client_conn: 1000 # Max client connections
  default_pool_size: 25 # Default pool per user+db
  min_pool_size: 5 # Minimum pooled connections
  reserve_pool_size: 5 # Reserve for immediate use
  reserve_pool_timeout: 5 # Wait time for connection (seconds)

  # Timeouts
  server_idle_timeout: 600 # Close idle server conn (10 min)
  server_lifetime: 3600 # Max server conn lifetime (1 hour)
  client_idle_timeout: 0 # No client timeout
  query_timeout: 0 # No query timeout
  query_wait_timeout: 120 # Max login time (2 minutes)

  # Admin access
  admin_users: postgres
  stats_users: postgres

  # Firewall (optional - defaults to PostgreSQL IPs)
  # firewall_allowed_ips:
  #   - 10.0.1.10/32
  #   - 10.0.1.11/32

base:
  "*":
    - common

# PgBouncer connection pooler for PostgreSQL
# Provides connection pooling to reduce connection overhead

# Install PgBouncer package
pgbouncer-package:
  pkg.installed:
    - name: pgbouncer

# Create system user for PgBouncer
pgbouncer-user:
  user.present:
    - name: pgbouncer
    - system: True
    - home: /var/lib/pgbouncer
    - shell: /bin/false
    - createhome: False
    - require:
        - pkg: pgbouncer-package

# Create configuration directory
pgbouncer-config-dir:
  file.directory:
    - name: /etc/pgbouncer
    - user: pgbouncer
    - group: pgbouncer
    - mode: "0750"
    - require:
        - user: pgbouncer-user

# Create log directory
pgbouncer-log-dir:
  file.directory:
    - name: /var/log/pgbouncer
    - user: pgbouncer
    - group: pgbouncer
    - mode: "0755"
    - require:
        - user: pgbouncer-user

# Create runtime directory
pgbouncer-runtime-dir:
  file.directory:
    - name: /var/run/pgbouncer
    - user: pgbouncer
    - group: pgbouncer
    - mode: "0755"
    - require:
        - user: pgbouncer-user

# Persist runtime directory across reboots
pgbouncer-tmpfiles-config:
  file.managed:
    - name: /etc/tmpfiles.d/pgbouncer.conf
    - contents: |
        # PgBouncer runtime directory
        d /var/run/pgbouncer 0755 pgbouncer pgbouncer -
    - user: root
    - group: root
    - mode: "0644"
    - require:
        - user: pgbouncer-user

# Deploy PgBouncer configuration
pgbouncer-config:
  file.managed:
    - name: /etc/pgbouncer/pgbouncer.ini
    - source: salt://pgbouncer/pgbouncer.ini.j2
    - template: jinja
    - user: pgbouncer
    - group: pgbouncer
    - mode: "0640"
    - require:
        - file: pgbouncer-config-dir

# Generate userlist from PostgreSQL
pgbouncer-generate-userlist:
  cmd.run:
    - name: |
        cat > /etc/pgbouncer/userlist.txt << 'EOF'
        ;; PgBouncer userlist.txt - Generated automatically
        ;; Format: "username" "password_hash"

        EOF
        # Extract password hashes from PostgreSQL
        sudo -u postgres psql -t -A -c "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolpassword IS NOT NULL;" >> /etc/pgbouncer/userlist.txt
        chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
        chmod 640 /etc/pgbouncer/userlist.txt
    - require:
        - file: pgbouncer-config-dir
    - unless: test -f /etc/pgbouncer/userlist.txt

# Update userlist when passwords change
pgbouncer-update-userlist:
  cmd.run:
    - name: |
        sudo -u postgres psql -t -A -c "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolpassword IS NOT NULL;" > /tmp/userlist_new.txt
        cat > /etc/pgbouncer/userlist.txt << 'EOF'
        ;; PgBouncer userlist.txt - Generated automatically
        ;; Format: "username" "password_hash"

        EOF
        cat /tmp/userlist_new.txt >> /etc/pgbouncer/userlist.txt
        rm -f /tmp/userlist_new.txt
        chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
        chmod 640 /etc/pgbouncer/userlist.txt
    - onlyif: test -f /etc/pgbouncer/userlist.txt
    - require:
        - cmd: pgbouncer-generate-userlist

# Deploy systemd service file
pgbouncer-service-file:
  file.managed:
    - name: /etc/systemd/system/pgbouncer.service
    - source: salt://pgbouncer/pgbouncer.service
    - user: root
    - group: root
    - mode: "0644"
    - require:
        - pkg: pgbouncer-package

# Reload systemd when service changes
pgbouncer-systemd-reload:
  cmd.run:
    - name: systemctl daemon-reload
    - onchanges:
        - file: pgbouncer-service-file

# Enable and start PgBouncer service
pgbouncer-service:
  service.running:
    - name: pgbouncer
    - enable: True
    - require:
        - file: pgbouncer-config
        - file: pgbouncer-runtime-dir
        - file: pgbouncer-tmpfiles-config
        - cmd: pgbouncer-generate-userlist
        - file: pgbouncer-service-file
        - cmd: pgbouncer-systemd-reload
    - watch:
        - file: pgbouncer-config
        - cmd: pgbouncer-update-userlist

;; PgBouncer configuration file
;; Generated by Salt - DO NOT EDIT MANUALLY

[databases]
; Database definitions
; Format: dbname = host=hostname port=port dbname=dbname

; Default database (postgres)
postgres = host=127.0.0.1 port={{ pillar.get('postgresql', {}).get('port', 5432) }} dbname=postgres

; Wildcard to allow any database connection through PgBouncer
; Applications can connect to any database that exists in PostgreSQL
* = host=127.0.0.1 port={{ pillar.get('postgresql', {}).get('port', 5432) }}

[pgbouncer]
;;;
;;; Administrative settings
;;;

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

;;;
;;; Where to wait for clients
;;;

; IP address or * to listen on all interfaces
listen_addr = {{ pillar.get('pgbouncer', {}).get('listen_addr', '*') }}
listen_port = {{ pillar.get('pgbouncer', {}).get('listen_port', 6432) }}

; Unix socket is also used for local connections
unix_socket_dir = /var/run/pgbouncer

;;;
;;; Authentication settings
;;;

; Authentication type: plain, md5, scram-sha-256, or trust
auth_type = {{ pillar.get('pgbouncer', {}).get('auth_type', 'scram-sha-256') }}
auth_file = /etc/pgbouncer/userlist.txt

;;;
;;; Connection pooling settings
;;;

; Pooling mode: session, transaction, or statement
; session - client stays connected for entire session
; transaction - client can use different connections (recommended)
; statement - most aggressive, connections returned after each statement
pool_mode = {{ pillar.get('pgbouncer', {}).get('pool_mode', 'transaction') }}

; Maximum number of server connections to allow per database
max_db_connections = {{ pillar.get('pgbouncer', {}).get('max_db_connections', 100) }}

; Maximum number of client connections to allow
max_client_conn = {{ pillar.get('pgbouncer', {}).get('max_client_conn', 1000) }}

; Default pool size (connections per user+database pair)
default_pool_size = {{ pillar.get('pgbouncer', {}).get('default_pool_size', 25) }}

; Minimum number of pooled connections
min_pool_size = {{ pillar.get('pgbouncer', {}).get('min_pool_size', 5) }}

; How long to keep released connections available for immediate re-use
reserve_pool_size = {{ pillar.get('pgbouncer', {}).get('reserve_pool_size', 5) }}

; Maximum time a client connection can wait for a server connection (seconds)
reserve_pool_timeout = {{ pillar.get('pgbouncer', {}).get('reserve_pool_timeout', 5) }}

;;;
;;; Connection limits
;;;

; Close server connection if it has been idle for this many seconds
server_idle_timeout = {{ pillar.get('pgbouncer', {}).get('server_idle_timeout', 600) }}

; Close server connection if it has been connected for this many seconds
server_lifetime = {{ pillar.get('pgbouncer', {}).get('server_lifetime', 3600) }}

; Close client connection if it has been idle for this many seconds
client_idle_timeout = {{ pillar.get('pgbouncer', {}).get('client_idle_timeout', 0) }}

; Cancel currently running query if client has been waiting too long
query_timeout = {{ pillar.get('pgbouncer', {}).get('query_timeout', 0) }}

; Close client connection if login takes more than this many seconds
query_wait_timeout = {{ pillar.get('pgbouncer', {}).get('query_wait_timeout', 120) }}

;;;
;;; TLS settings (optional)
;;;

; TLS is disabled by default
; Uncomment and configure if you need TLS
;client_tls_sslmode = prefer
;client_tls_ca_file = /etc/pgbouncer/root.crt
;client_tls_cert_file = /etc/pgbouncer/server.crt
;client_tls_key_file = /etc/pgbouncer/server.key

;;;
;;; Logging
;;;

; Log level: error, warning, info, debug
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Log successful logins
verbose = 0

;;;
;;; Console access control
;;;

; Users allowed to access admin console
admin_users = {{ pillar.get('pgbouncer', {}).get('admin_users', 'postgres') }}

; Users allowed to view statistics but not change settings
stats_users = {{ pillar.get('pgbouncer', {}).get('stats_users', 'postgres') }}

;;;
;;; Connection sanity checks, timeouts
;;;

; Check server_check_query on connect and after check_delay seconds
server_check_query = SELECT 1
server_check_delay = 30

; Use application_name to identify connections
application_name_add_host = 1

[Unit]
Description=PgBouncer - PostgreSQL connection pooler
Documentation=man:pgbouncer(1)
After=network.target postgresql.service
Wants=postgresql.service

[Service]
Type=forking
User=pgbouncer
Group=pgbouncer
PIDFile=/var/run/pgbouncer/pgbouncer.pid

# Create runtime directory (ExecStartPre runs as root with + prefix)
RuntimeDirectory=pgbouncer
RuntimeDirectoryMode=0755
ExecStartPre=+/usr/bin/mkdir -p /var/run/pgbouncer
ExecStartPre=+/usr/bin/chown pgbouncer:pgbouncer /var/run/pgbouncer
ExecStartPre=+/usr/bin/chmod 0755 /var/run/pgbouncer

# PgBouncer configuration
ExecStart=/usr/bin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID

# Security settings
NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=strict
ProtectHome=true
ReadWritePaths=/var/log/pgbouncer /var/run/pgbouncer

# Resource limits
LimitNOFILE=65536

# Restart policy
Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target

# Database: restrict PgBouncer port to whitelisted IPs
{% set pgbouncer_allowed_ips = pillar.get('pgbouncer', {}).get('firewall_allowed_ips', pg_allowed_ips) %}
{% set pgbouncer_port = pillar.get('pgbouncer', {}).get('listen_port', 6432) %}

{% if pgbouncer_allowed_ips %}
# Create firewall rules for each whitelisted IP for PgBouncer port
{% for ip in pgbouncer_allowed_ips %}
pgbouncer-{{ pgbouncer_port }}-{{ ip | replace('.', '-') | replace('/', '-') }}:
  cmd.run:
    - name: firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="{{ ip }}" port port="{{ pgbouncer_port }}" protocol="tcp" accept'
    - unless: firewall-cmd --list-rich-rules | grep -q "{{ pgbouncer_port }}.*{{ ip | replace('/', '\/') }}"
    - require:
      - service: firewalld-service
{% endfor %}

# Reload firewall after adding PgBouncer rules
firewall-reload-after-pgbouncer-rules:
  cmd.run:
    - name: firewall-cmd --reload
    - onchanges:
{% for ip in pgbouncer_allowed_ips %}
      - cmd: pgbouncer-{{ pgbouncer_port }}-{{ ip | replace('.', '-') | replace('/', '-') }}
{% endfor %}
{% else %}
# INFO: PgBouncer will use same IPs as PostgreSQL by default
pgbouncer-using-postgresql-ips:
  test.configurable_test_state:
    - name: INFO - PgBouncer using PostgreSQL allowed IPs
    - changes: False
    - result: True
    - comment: |
        INFO: No separate IPs configured in pillar.pgbouncer.firewall_allowed_ips
        PgBouncer port {{ pgbouncer_port }} uses same IPs as PostgreSQL.
        To configure different IPs for PgBouncer, add them to pillar/common.sls
{% endif %}
{% endif %}

base:
  "*":
    - grains # Sets roles based on hostname
    - hardening # SSH hardening, system updates
    - firewall # Firewall rules (role-based)
    - users # User management

  # Database servers (role: database)
  "roles:database":
    - match: grain
    - pgbouncer # PostgreSQL connection pooler

# Add all files to git
git add salt/

# Commit changes
git commit -m "Add PgBouncer configuration with SaltStack

- PgBouncer state for installation and configuration
- Systemd service with proper PID tracking
- Firewall rules for port 6432
- Pillar configuration for connection pooling
- Automatic user synchronization from PostgreSQL"

# Push to repository
git push origin main

# Update gitfs cache to pull latest changes
sudo salt-run fileserver.update

# Verify pgbouncer state is available
sudo salt-run fileserver.file_list | grep pgbouncer

# Refresh pillar data
sudo salt 'db*' saltutil.refresh_pillar

# Test what will be applied (dry run)
sudo salt 'db*' state.apply test=True

# Apply pgbouncer state
sudo salt 'db*' state.apply pgbouncer

# Or apply all states
sudo salt 'db*' state.apply

# Verify pgbouncer is running
sudo salt 'db*' cmd.run 'systemctl status pgbouncer'

# Expected output:
# ● pgbouncer.service - PgBouncer - PostgreSQL connection pooler
#      Loaded: loaded (/etc/systemd/system/pgbouncer.service; enabled)
#      Active: active (running) since...

# Check if PgBouncer is listening
sudo salt 'db*' cmd.run 'ss -tlnp | grep 6432'

# Expected output:
# LISTEN 0      128          0.0.0.0:6432       0.0.0.0:*    users:(("pgbouncer",pid=XXXXX,fd=7))
# LISTEN 0      128             [::]:6432          [::]:*    users:(("pgbouncer",pid=XXXXX,fd=8))

# Verify firewall rules
sudo salt 'db*' cmd.run 'firewall-cmd --list-rich-rules | grep 6432'

# Should show rules for each allowed IP

# Connect to PgBouncer
psql -h db-server.example.com -p 6432 -U your_user -d your_database

# Check connection info
SELECT version();

# Exit
\q

# Connect as admin user
psql -h db-server.example.com -p 6432 -U postgres -d pgbouncer

# View pool statistics
SHOW POOLS;

# View database statistics
SHOW DATABASES;

# View client statistics
SHOW CLIENTS;

# View server statistics
SHOW SERVERS;

# View configuration
SHOW CONFIG;

# View PgBouncer logs
sudo salt 'db*' cmd.run 'tail -50 /var/log/pgbouncer/pgbouncer.log'

# Monitor in real-time
sudo salt 'db*' cmd.run 'tail -f /var/log/pgbouncer/pgbouncer.log'

# In pillar/common.sls
pgbouncer:
  auth_type: scram-sha-256 # More secure than md5

# On PostgreSQL server, set secure passwords
sudo -u postgres psql
ALTER USER username WITH PASSWORD 'strong_random_password';

# PgBouncer will automatically sync the password hash

# Restrict to specific IPs only
postgresql:
  firewall_allowed_ips:
    - 10.0.1.10/32 # Specific app server
    - 10.0.2.0/24 # Kubernetes pod network
    # Never use 0.0.0.0/0 in production!

# In pgbouncer.ini.j2
client_tls_sslmode = require
client_tls_ca_file = /etc/pgbouncer/ca.crt
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_key_file = /etc/pgbouncer/server.key

# Check configuration file permissions
ls -la /etc/pgbouncer/
# pgbouncer.ini should be 0640 (owner: pgbouncer)
# userlist.txt should be 0640 (owner: pgbouncer)

# Check log directory
ls -la /var/log/pgbouncer/
# Should be owned by pgbouncer:pgbouncer

# Check SELinux status
getenforce

# Allow PgBouncer to bind to port 6432
semanage port -a -t postgresql_port_t -p tcp 6432

# Or create custom SELinux policy if needed

Optimal pool size = (Core count × 2) + effective_spindle_count
For 8-core server with SSD: (8 × 2) + 1 = 17

Conservative starting point: 25
Maximum recommended: 100

pgbouncer:
  pool_mode: transaction

pgbouncer:
  pool_mode: session

pgbouncer:
  pool_mode: statement

pgbouncer:
  # Server timeouts
  server_idle_timeout: 600 # 10 minutes - closes idle connections
  server_lifetime: 3600 # 1 hour - recycles old connections

  # Client timeouts
  client_idle_timeout: 0 # Never timeout idle clients
  query_timeout: 0 # No query timeout (let PostgreSQL handle)
  query_wait_timeout: 120 # 2 minutes - max time to wait for connection

-- Connect to pgbouncer admin console
psql -h localhost -p 6432 -U postgres -d pgbouncer

-- Pool utilization
SHOW POOLS;
-- Watch: cl_active, sv_active, sv_idle

-- Wait time (should be near 0)
SHOW STATS;
-- Watch: avg_wait_time, max_wait_time

-- Configuration audit
SHOW CONFIG;

# Check detailed logs
journalctl -u pgbouncer -n 100 --no-pager

# Test configuration syntax
sudo -u pgbouncer /usr/bin/pgbouncer -v /etc/pgbouncer/pgbouncer.ini

# Check runtime directory exists
ls -la /var/run/pgbouncer/

# Manually create if missing
sudo mkdir -p /var/run/pgbouncer
sudo chown pgbouncer:pgbouncer /var/run/pgbouncer

# Verify userlist.txt exists and has content
cat /etc/pgbouncer/userlist.txt

# Should show password hashes for users
# "username" "SCRAM-SHA-256$..."

# Regenerate userlist
sudo -u postgres psql -t -A -c \
  "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' \
   FROM pg_authid WHERE rolpassword IS NOT NULL;" \
  > /tmp/userlist.txt

sudo mv /tmp/userlist.txt /etc/pgbouncer/userlist.txt
sudo chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
sudo chmod 640 /etc/pgbouncer/userlist.txt

# Reload PgBouncer
sudo systemctl reload pgbouncer

# Check firewall rules
firewall-cmd --list-rich-rules | grep 6432

# Test from client
telnet db-server.example.com 6432

# Check connection limits
sudo -u postgres psql -h localhost -p 6432 -d pgbouncer
SHOW CONFIG;
-- Look at max_client_conn, max_db_connections

# Increase pool size
# Edit pillar/common.sls
pgbouncer:
  default_pool_size: 50  # Increase from 25
  max_db_connections: 200  # Increase from 100

# Apply changes
sudo salt 'db*' state.apply pgbouncer

# Prometheus exporter configuration example
pgbouncer_exporter:
  metrics:
    - cl_active # Active client connections
    - sv_active # Active server connections
    - sv_idle # Idle server connections
    - avg_wait_time # Average wait for connection
    - total_wait_time # Total time clients waited

alerts:
  - name: PgBouncerHighWaitTime
    condition: avg_wait_time > 100ms
    severity: warning

  - name: PgBouncerPoolSaturation
    condition: sv_active / max_db_connections > 0.9
    severity: critical

# Review logs for errors
sudo grep ERROR /var/log/pgbouncer/pgbouncer.log

# Check statistics
psql -h localhost -p 6432 -U postgres -d pgbouncer -c "SHOW STATS;"

# Verify all expected databases are accessible

# Review and update firewall rules
sudo firewall-cmd --list-rich-rules | grep 6432

# Audit user access
sudo cat /etc/pgbouncer/userlist.txt

# Test failover procedure

# Always use Salt for changes
# 1. Update pillar/common.sls
# 2. Commit to git
# 3. Apply via Salt

sudo salt 'db*' state.apply pgbouncer


# PgBouncer will reload automatically via watch directive

-- On PostgreSQL
CREATE USER newuser WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE your_db TO newuser;

-- PgBouncer will sync automatically on next state.apply
-- Or manually trigger update:
sudo salt 'db*' cmd.run "bash -c 'sudo -u postgres psql -t -A -c \"SELECT ...\" > /etc/pgbouncer/userlist.txt && systemctl reload pgbouncer'"

# Pillar configuration
pgbouncer_nodes:
  - db-primary.example.com
  - db-replica-1.example.com
  - db-replica-2.example.com

# Point PgBouncer to local PostgreSQL
[databases]
* = host=127.0.0.1 port=5432

# Application health check endpoint
curl -f http://db-server:9127/metrics || exit 1

# Or simple connection test
psql -h localhost -p 6432 -U health_check -d postgres -c "SELECT 1" > /dev/null

# Configuration is in git, but also backup runtime state
tar -czf pgbouncer-backup-$(date +%Y%m%d).tar.gz \
  /etc/pgbouncer/ \
  /etc/systemd/system/pgbouncer.service \
  /etc/tmpfiles.d/pgbouncer.conf

# PgBouncer state is idempotent
# To rebuild from scratch:
sudo salt 'db-new-server' state.apply pgbouncer

# Verifies deployment
sudo salt 'db-new-server' cmd.run 'systemctl status pgbouncer'

# Monitor CPU usage
sudo salt 'db*' cmd.run 'top -b -n 1 | grep pgbouncer'

# If pgbouncer CPU > 80%, consider:
# 1. Multiple PgBouncer instances (different ports)
# 2. Horizontal scaling (more servers)

┌─────────────┐     ┌─────────────┐
│   App #1    │     │   App #2    │
│ + PgBouncer │     │ + PgBouncer │
└──────┬──────┘     └──────┬──────┘
       │                   │
       └────────┬──────────┘
                ▼
       ┌─────────────────┐
       │   PostgreSQL    │
       │   (Port 5432)   │
       └─────────────────┘

┌─────────┐
│   HAProxy/    │
│   Nginx       │
└───────┬───────┘
        │
    ┌───┴───┬───────┐
    ▼       ▼       ▼
┌────────┐ ┌────────┐ ┌────────┐
│PgBouncer│ │PgBouncer│ │PgBouncer│
│  #1     │ │  #2     │ │  #3     │
└────┬───┘ └────┬───┘ └────┬───┘
     └──────────┼──────────┘
                ▼
         ┌─────────────┐
         │ PostgreSQL  │
         └─────────────┘

# Deploy PgBouncer without changing applications
sudo salt 'db*' state.apply pgbouncer

# Both ports available:
# - PostgreSQL: 5432 (existing)
# - PgBouncer: 6432 (new)

# Update application connection strings
# Old: postgresql://db-server:5432/mydb
# New: postgresql://db-server:6432/mydb

# Test with single application instance
# Monitor logs for issues

# After all applications migrated:
# - Update firewall to restrict 5432 (admin only)
# - Keep 6432 open for applications

# Pillar update
postgresql:
  firewall_allowed_ips:
    - 10.0.0.100/32  # Admin workstation only

pgbouncer:
  firewall_allowed_ips:
    - 10.0.1.0/24    # Application servers

Scenario: 1000 concurrent connections
- PostgreSQL RAM: 10GB (10MB × 1000)
- Connection overhead: 30ms average
- Max sustainable: ~500 connections
- Database server: 32GB RAM, $400/month

Scenario: 1000 concurrent connections
- PgBouncer RAM: 2MB (2KB × 1000)
- PostgreSQL RAM: 1GB (10MB × 100 pooled)
- Connection overhead: <1ms
- Max sustainable: 10,000+ connections
- Database server: 16GB RAM, $200/month
- Savings: $200/month = $2,400/year

Setup time: 4 hours
Annual savings: $2,400
Payback period: < 1 week
Performance improvement: 30x faster connections
Scalability: 20x more connections