Background: Why PgBouncer Needs Monitoring Too
During a flash sale last year, our PostgreSQL cluster nearly went down even though host CPU usage barely reached 35%. The culprit turned out to be an unexpected bottleneck: PgBouncer.
Every connection to PostgreSQL is a separate backend process on Linux, consuming roughly 5 to 10 MB of RAM. When microservices scale up to 300 pods, connection counts can easily hit the max_connections ceiling (typically set to 200–300). As a result, the database runs out of memory and crashes. PgBouncer solves this problem neatly. Using Transaction Pooling, thousands of clients can share a backend pool consisting of just 40–50 actual connections.
However, this lifesaver can still sink. When the backend database slows down, pooled connections exhaust rapidly. Subsequent incoming queries are forced to queue up waiting for an open slot. If this queue balloons for even a few tens of seconds, API Gateways will immediately start throwing 504 Gateway Timeouts. The database hasn’t crashed, yet the application is completely paralyzed.
To catch incidents before users report them, there are three metrics you must continuously monitor:
- Client Waiting Connections: The number of queries currently stuck in the pooler queue.
- Query Max Wait Time: The longest duration a query has waited before being picked up by a backend.
- Pool Utilization: The ratio of active backend connections relative to configured limits.
Installing pgbouncer_exporter
PgBouncer exposes operational data through a virtual database named pgbouncer. You can quickly inspect these metrics using the SHOW POOLS; and SHOW STATS; commands. To allow Prometheus to scrape this data, we use the Go binary pgbouncer_exporter from the Prometheus Community.
First, download the appropriate binary onto the server running PgBouncer:
# Download the pgbouncer_exporter binary
wget https://github.com/prometheus-community/pgbouncer_exporter/releases/download/v0.8.0/pgbouncer_exporter-0.8.0.linux-amd64.tar.gz
tar -xvf pgbouncer_exporter-0.8.0.linux-amd64.tar.gz
sudo mv pgbouncer_exporter-0.8.0.linux-amd64/pgbouncer_exporter /usr/local/bin/
# Create a system user to run the service
sudo useradd --no-create-home --shell /bin/false pgbouncer_exporterNext, create a systemd service file to manage the background process:
sudo tee /etc/systemd/system/pgbouncer_exporter.service << 'EOF'
[Unit]
Description=PgBouncer Prometheus Exporter
After=network.target pgbouncer.service
[Service]
Type=simple
User=pgbouncer_exporter
EnvironmentFile=/etc/default/pgbouncer_exporter
ExecStart=/usr/local/bin/pgbouncer_exporter $PGBOUNCER_EXPORTER_OPTS
Restart=on-failure
RestartSec=5
[Install]
WantedBy=multi-user.target
EOFConfiguring Connection and Prometheus Scraping
PgBouncer must grant read permissions to the exporter via its admin console. Open the configuration file /etc/pgbouncer/pgbouncer.ini:
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
# Declare admin and stats users
admin_users = postgres
stats_users = pgbouncer_exporterDefine the password for the pgbouncer_exporter account in /etc/pgbouncer/userlist.txt:
"pgbouncer_exporter" "ExporterSecurePassword123"Reload the PgBouncer configuration without dropping existing connections:
sudo systemctl reload pgbouncerProvide the connection string to the exporter via the environment file /etc/default/pgbouncer_exporter:
sudo tee /etc/default/pgbouncer_exporter << 'EOF'
PGBOUNCER_EXPORTER_OPTS="--pgBouncer.connectionString=postgres://pgbouncer_exporter:[email protected]:6432/pgbouncer?sslmode=disable --web.listen-address=:9127"
EOFStart the exporter and verify the metrics output directly from port 9127:
sudo systemctl daemon-reload
sudo systemctl enable --now pgbouncer_exporter
curl -s http://localhost:9127/metrics | grep pgbouncer_pools_server_active_connectionsOnce curl returns metrics with a 200 status code, add the scrape job configuration to prometheus.yml:
scrape_configs:
- job_name: 'pgbouncer'
scrape_interval: 10s
scrape_timeout: 5s
static_configs:
- targets: ['10.10.20.15:9127']
labels:
environment: 'production'
cluster: 'pg-core'Core Metrics and Battle-Tested Tips
Ingesting data into Prometheus is only half the battle. You need to know exactly which metrics truly reflect system health.
1. Queue Monitoring: Client Waiting & Maxwait
These are the most sensitive indicators of pool congestion. Under normal conditions, both metrics should always remain at 0.
# Number of clients waiting for a backend slot
sum by (database) (pgbouncer_pools_client_waiting_connections)
# Longest time a query has waited in the pool (seconds)
max by (database) (pgbouncer_pools_maxwait_seconds)If client_waiting exceeds 5 and maxwait climbs above 2 seconds, your system is in jeopardy. Either the pool size is too small, or slow queries on the backend database are holding locks across multiple tables.
2. Measuring Pool Saturation
PgBouncer allocates connections to PostgreSQL lazily. Initially, it only opens a few connections, gradually increasing them with load up to the default_pool_size limit.
Consequently, calculating the utilization percentage based on active / (active + idle) is prone to false alarms during low-traffic periods. A more reliable approach is to track the raw number of active server connections relative to the configured ceiling:
# Number of backend connections actively executing queries
sum by (database) (pgbouncer_pools_server_active_connections)When this count edges close to the pool size limit (for example, 45/50 connections) during peak hours, it is time to slightly bump the pool size or optimize how long transactions are held in application code.
3. Alerting Best Practices: Avoiding the Alert Fatigue Trap
When we first ran this in production, I set up alerts to ping Slack immediately whenever client_waiting > 0. That was a big mistake. Every time a 5-second periodic cronjob ran or Kubernetes spun up new pods, the metric would spike to 1 and immediately drop back to 0. Slack got spammed with hundreds of noise alerts daily. On-call engineers grew fatigued and eventually began ignoring real incidents.
The golden rule: only alert when an anomaly persists long enough. Here is our standardized alert ruleset:
groups:
- name: pgbouncer_alerts
rules:
- alert: PgBouncerHighClientWaiting
expr: sum by (database) (pgbouncer_pools_client_waiting_connections) > 5
for: 1m
labels:
severity: critical
annotations:
summary: "PgBouncer queue congested on database {{ $labels.database }}"
description: "There are {{ $value }} clients waiting for connections for over 1 minute. Check pool size or slow queries."
- alert: PgBouncerHighQueryWaitTime
expr: max by (database) (pgbouncer_pools_maxwait_seconds) > 2
for: 30s
labels:
severity: critical
annotations:
summary: "Query delay on PgBouncer pool {{ $labels.database }}"
description: "Query wait time exceeded 2 seconds continuously for 30 seconds."
- alert: PgBouncerPoolSaturation
expr: sum by (database) (pgbouncer_pools_server_active_connections) >= 45
for: 3m
labels:
severity: warning
annotations:
summary: "PgBouncer pool near capacity on {{ $labels.database }}"
description: "Active backend connections remained near maximum capacity (50) for over 3 minutes."
On Grafana, you can readily import community dashboard ID 11905 or 14118. Place your PgBouncer dashboard side-by-side with your host infrastructure dashboard (CPU, Disk I/O). When an issue strikes, you’ll immediately know whether the bottleneck stems from connection starvation or database disk IOPS saturation.
