PgBouncer Connection Pooling on Hong Kong VPS: Scale PostgreSQL (2026)

PostgreSQL creates a dedicated OS process per connection — at 500 connections, that’s 500 processes consuming 20–100 MB of RAM each. PgBouncer solves this by maintaining a small pool of actual PostgreSQL connections and multiplexing thousands of application connections through them, dramatically reducing memory usage and enabling PostgreSQL to handle far more concurrent application connections than its native architecture allows. This is essential on any Hong Kong VPS running a multi-worker application stack.

When You Need PgBouncer

Signs you need PgBouncer: PostgreSQL logs showing “too many connections”; application errors about connection pool exhaustion; RAM pressure on the VPS from many idle PostgreSQL worker processes.

Step 1: Install PgBouncer

<code">apt update && apt install -y pgbouncer

# Check version
pgbouncer --version

# PgBouncer config location
ls /etc/pgbouncer/

Step 2: Configure PgBouncer

<code">cat > /etc/pgbouncer/pgbouncer.ini << 'EOF'
[databases]
# Format: app_db = host=localhost port=5432 dbname=actual_db_name
myapp = host=127.0.0.1 port=5432 dbname=myapp_production
myapp_readonly = host=127.0.0.1 port=5432 dbname=myapp_production
analytics = host=127.0.0.1 port=5432 dbname=analytics_db

[pgbouncer]
# Network settings
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /var/run/postgresql

# Authentication
auth_type = hba
auth_file = /etc/pgbouncer/userlist.txt

# Pooling mode — critical choice (see below)
pool_mode = transaction

# Pool sizes
default_pool_size = 20        # Connections to PostgreSQL per database-user pair
min_pool_size = 2             # Always keep 2 connections warm
max_client_conn = 1000        # Max application connections PgBouncer accepts
reserve_pool_size = 5         # Extra connections for emergencies
reserve_pool_timeout = 5

# Timeouts
server_idle_timeout = 600     # Close unused server connections after 10 minutes
client_idle_timeout = 0       # Keep client connections indefinitely (0 = disable)
server_connect_timeout = 15
query_timeout = 0             # No query timeout at pool level

# Logging
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
log_connections = 0           # Reduce log noise in production
log_disconnections = 0
log_stats_interval = 60       # Log pool stats every minute

# Admin interface
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats
EOF

The Three Pool Modes

Use transaction mode for most applications. Avoid it only if your application uses: SET parameters that persist, prepared statements (use prepared_statement_cache_queries=0 in your app), or LISTEN/NOTIFY (use a dedicated session-mode connection for those).

Step 3: Create User Authentication File

<code"># PgBouncer needs a userlist.txt with MD5 or SCRAM hashed passwords
# Get the hashed password from PostgreSQL:
sudo -u postgres psql -t -c \
  "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow WHERE usename='myapp_user';"

# Output looks like:
# "myapp_user" "SCRAM-SHA-256$4096:..."

# Put this in userlist.txt:
cat > /etc/pgbouncer/userlist.txt << 'EOF' "myapp_user" "SCRAM-SHA-256$4096:BASE64SALT$BASE64STOREDKEY:BASE64SERVERKEY" "pgbouncer_admin" "SCRAM-SHA-256$..." EOF # Create HBA auth file cat > /etc/pgbouncer/pg_hba.conf << 'EOF'
# Allow local connections
local   all             all                     trust
host    all             all     127.0.0.1/32    scram-sha-256
EOF

chown postgres:postgres /etc/pgbouncer/userlist.txt
chmod 640 /etc/pgbouncer/userlist.txt

Step 4: Start PgBouncer

<code">systemctl enable --now pgbouncer
systemctl status pgbouncer

# Test connection via PgBouncer (port 6432 instead of 5432)
psql -h 127.0.0.1 -p 6432 -U myapp_user myapp

# Should connect successfully and work like a normal PostgreSQL connection

Step 5: Update Application Connection Strings

<code"># Change port from 5432 (PostgreSQL) to 6432 (PgBouncer)
# Node.js:
DATABASE_URL=postgresql://myapp_user:Password!@localhost:6432/myapp

# Django:
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': '127.0.0.1',
        'PORT': '6432',    # PgBouncer port
        'NAME': 'myapp',
        'USER': 'myapp_user',
        'PASSWORD': 'Password!',
        'CONN_MAX_AGE': 0,   # Let PgBouncer manage pooling, not Django
    }
}

# Rails database.yml:
production:
  adapter: postgresql
  host: 127.0.0.1
  port: 6432    # PgBouncer
  pool: 5       # Per-worker pool — PgBouncer multiplexes these

Disable App-Level Connection Pooling

<code"># When using PgBouncer in transaction mode:
# Set your application's pool size to 1-2 per worker
# PgBouncer handles the actual multiplexing

# Node.js pg Pool:
const pool = new Pool({
  host: '127.0.0.1',
  port: 6432,
  max: 2,          // 2 connections per Node.js process (not 20)
  // PgBouncer will handle the actual PostgreSQL connections
})

Step 6: Monitor PgBouncer

<code"># Connect to PgBouncer admin interface
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

-- Show pool statistics
SHOW POOLS;
-- cl_active: client connections actively using a server connection
-- cl_waiting: clients waiting for a server connection (bad if high)
-- sv_active: server connections actively serving a client
-- sv_idle: server connections sitting idle (good — available for next request)

-- Show all databases
SHOW DATABASES;

-- Show server connections
SHOW SERVERS;

-- Show client connections
SHOW CLIENTS;

-- Live statistics
SHOW STATS;
-- total_requests: cumulative queries handled
-- avg_req: average requests per second
-- avg_wait: average wait time for a connection

<code"># Shell monitoring command
watch -n 5 'psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW POOLS;" 2>/dev/null'

Step 7: Prometheus Metrics for PgBouncer

<code"># pgbouncer_exporter — exposes PgBouncer stats as Prometheus metrics
wget https://github.com/prometheus-community/pgbouncer_exporter/releases/latest/download/pgbouncer_exporter_linux_amd64.tar.gz
tar xzf pgbouncer_exporter_linux_amd64.tar.gz -C /usr/local/bin/

cat > /etc/systemd/system/pgbouncer-exporter.service << 'EOF'
[Unit]
Description=PgBouncer Prometheus Exporter
After=pgbouncer.service

[Service]
ExecStart=/usr/local/bin/pgbouncer_exporter \
  --pgBouncer.connectionString="postgres://pgbouncer_stats@localhost:6432/pgbouncer"
Restart=always

[Install]
WantedBy=multi-user.target
EOF

systemctl enable --now pgbouncer-exporter

# Grafana dashboard: search for "PgBouncer" at grafana.com/dashboards
# Import dashboard ID 9127 for PgBouncer overview

Step 8: Common Issues and Fixes

Memory Comparison: With and Without PgBouncer

Conclusion

PgBouncer on a Hong Kong VPS is a lightweight but high-impact addition to any PostgreSQL-backed application — reducing active database connections by 5–10× and reclaiming hundreds of megabytes of RAM that PostgreSQL would otherwise consume maintaining idle connections. For applications with multiple PM2/Gunicorn workers, PgBouncer in transaction mode is close to mandatory: it enables the VPS to handle burst traffic without running into PostgreSQL’s connection limits.

Scale your database: Browse Server.HK Hong Kong VPS plans — PgBouncer itself uses under 10 MB of RAM; the memory savings it creates on your existing plan can be significant.