tutorial

Monitoring PgBouncer: Pool Utilization, Connection Wait Time, and Vigilmon Availability Checks

A practical guide to monitoring PgBouncer connection poolers — client and server pool utilization, connection wait time, query queue depth, admin console stats, and Vigilmon checks for pooler availability.

PgBouncer is transparent when it's working and catastrophic when it isn't. A connection pool that's 80% full will silently delay client connections; a pool that's 100% full causes timeouts that look like database errors; a PgBouncer process that has crashed silently takes your entire database-dependent application offline — even if PostgreSQL itself is running fine.

Vigilmon provides external HTTP monitoring to detect PgBouncer availability issues before your users do. This tutorial covers PgBouncer's admin console stats, health endpoint construction, connection pool metrics, and how to wire it all into Vigilmon.

PgBouncer Admin Console

PgBouncer exposes a pseudo-database named pgbouncer that accepts a small set of administrative commands. Connect to it like a regular PostgreSQL connection:

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

The most useful commands for monitoring:

-- Pool utilization: clients waiting, servers idle, servers active
SHOW POOLS;

-- Per-database statistics
SHOW DATABASES;

-- Overall server stats (total requests, bytes, wait time)
SHOW STATS;

-- Current client connections
SHOW CLIENTS;

-- Current server (PostgreSQL backend) connections
SHOW SERVERS;

-- PgBouncer configuration
SHOW CONFIG;

The key metrics from SHOW POOLS:

| Column | Meaning | |---|---| | cl_active | Clients currently executing a query | | cl_waiting | Clients waiting for a free server connection — the critical metric | | sv_active | Server connections currently in use | | sv_idle | Server connections available for use | | sv_used | Server connections returned to pool but not yet reset | | maxwait | Max time (seconds) the oldest waiting client has been waiting |

When cl_waiting > 0 and maxwait > 0, clients are queuing. When maxwait exceeds your application timeout, requests start failing.

Step 1: Build an HTTP Health Endpoint

PgBouncer's admin console uses the PostgreSQL wire protocol — not HTTP. Wrap it in an API endpoint your application exposes:

import asyncpg
import os
from fastapi import FastAPI
from fastapi.responses import JSONResponse

app = FastAPI()

PGBOUNCER_DSN = {
    "host": os.environ.get("PGBOUNCER_HOST", "127.0.0.1"),
    "port": int(os.environ.get("PGBOUNCER_PORT", 6432)),
    "user": os.environ["PGBOUNCER_ADMIN_USER"],
    "password": os.environ["PGBOUNCER_ADMIN_PASSWORD"],
    "database": "pgbouncer",
}

@app.get("/health/pgbouncer")
async def pgbouncer_health():
    try:
        conn = await asyncpg.connect(**PGBOUNCER_DSN)

        pools = [dict(r) for r in await conn.fetch("SHOW POOLS")]
        stats = [dict(r) for r in await conn.fetch("SHOW STATS")]

        await conn.close()

        # Flag pools with waiting clients
        overloaded = [
            p for p in pools
            if p.get("cl_waiting", 0) > 0 or p.get("maxwait", 0) > 5
        ]

        status = "ok" if not overloaded else "degraded"
        code = 200 if not overloaded else 503

        return JSONResponse(status_code=code, content={
            "status": status,
            "pools": pools,
            "overloaded_pools": overloaded,
            "stats": stats,
        })
    except Exception as e:
        return JSONResponse(status_code=503, content={
            "status": "down",
            "error": str(e),
        })

Lightweight Availability Check

If you don't need pool stats in the health response, use a minimal availability check instead:

@app.get("/health/pgbouncer/ping")
async def pgbouncer_ping():
    try:
        conn = await asyncpg.connect(**PGBOUNCER_DSN)
        # SHOW VERSION is the lightest possible query against the admin console
        result = await conn.fetch("SHOW VERSION")
        await conn.close()
        return JSONResponse(status_code=200, content={
            "status": "ok",
            "version": dict(result[0]).get("version", "unknown"),
        })
    except Exception as e:
        return JSONResponse(status_code=503, content={"status": "down", "error": str(e)})

Step 2: Pool Utilization Metrics

The most actionable PgBouncer metric is pool utilization: how close you are to running out of server connections. Surface it explicitly:

@app.get("/health/pgbouncer/utilization")
async def pgbouncer_utilization():
    try:
        conn = await asyncpg.connect(**PGBOUNCER_DSN)
        pools = [dict(r) for r in await conn.fetch("SHOW POOLS")]
        config = {dict(r)["key"]: dict(r)["value"] for r in await conn.fetch("SHOW CONFIG")}
        await conn.close()

        max_client_conn = int(config.get("max_client_conn", 100))
        default_pool_size = int(config.get("default_pool_size", 20))

        pool_summaries = []
        for pool in pools:
            db = pool["database"]
            user = pool["user"]
            sv_total = (
                pool.get("sv_active", 0) +
                pool.get("sv_idle", 0) +
                pool.get("sv_used", 0)
            )
            utilization_pct = (sv_total / default_pool_size * 100) if default_pool_size else 0
            pool_summaries.append({
                "database": db,
                "user": user,
                "server_connections": sv_total,
                "pool_size": default_pool_size,
                "utilization_pct": round(utilization_pct, 1),
                "clients_waiting": pool.get("cl_waiting", 0),
                "max_wait_seconds": pool.get("maxwait", 0),
            })

        critical = [p for p in pool_summaries if p["utilization_pct"] >= 90]
        warning = [p for p in pool_summaries if 70 <= p["utilization_pct"] < 90]

        status = "critical" if critical else ("warning" if warning else "ok")
        code = 503 if critical else 200

        return JSONResponse(status_code=code, content={
            "status": status,
            "max_client_conn": max_client_conn,
            "pools": pool_summaries,
            "critical_pools": critical,
            "warning_pools": warning,
        })
    except Exception as e:
        return JSONResponse(status_code=503, content={"status": "down", "error": str(e)})

Step 3: Connection Wait Time Alert

Connection wait time (maxwait) is the leading indicator of pool exhaustion. Add a dedicated endpoint that returns non-200 when wait time exceeds your SLA:

MAXWAIT_THRESHOLD_SECONDS = 2.0  # Alert if any client waits longer than 2s

@app.get("/health/pgbouncer/waittime")
async def pgbouncer_wait_time():
    try:
        conn = await asyncpg.connect(**PGBOUNCER_DSN)
        pools = [dict(r) for r in await conn.fetch("SHOW POOLS")]
        await conn.close()

        slow_pools = [
            {
                "database": p["database"],
                "maxwait": p.get("maxwait", 0),
                "cl_waiting": p.get("cl_waiting", 0),
            }
            for p in pools
            if p.get("maxwait", 0) > MAXWAIT_THRESHOLD_SECONDS
        ]

        status = "ok" if not slow_pools else "degraded"
        code = 200 if not slow_pools else 503

        return JSONResponse(status_code=code, content={
            "status": status,
            "threshold_seconds": MAXWAIT_THRESHOLD_SECONDS,
            "slow_pools": slow_pools,
        })
    except Exception as e:
        return JSONResponse(status_code=503, content={"status": "down", "error": str(e)})

Step 4: Configure Vigilmon Monitors

PgBouncer Availability Monitor

  1. Log in to vigilmon.online → Monitors → New Monitor → HTTP / HTTPS
  2. URL: https://your-api.example.com/health/pgbouncer/ping
  3. Check interval: 30 seconds (connection poolers are load-bearing; catch outages fast)
  4. Expected status: 200; response body contains: "status":"ok"
  5. Response time threshold: 2000ms
  6. Alert channel: Slack + PagerDuty (pooler down = application database access down)

Pool Utilization Monitor

  1. Monitors → New Monitor → HTTP / HTTPS
  2. URL: https://your-api.example.com/health/pgbouncer/utilization
  3. Check interval: 1 minute
  4. Expected status: 200; body contains: "status":"ok"
  5. Alert channel: Slack (trend alert — 90% utilization predicts imminent exhaustion)

Connection Wait Time Monitor

  1. Monitors → New Monitor → HTTP / HTTPS
  2. URL: https://your-api.example.com/health/pgbouncer/waittime
  3. Check interval: 1 minute
  4. Expected status: 200
  5. Alert channel: Slack + PagerDuty (clients waiting = users experiencing timeouts)

Use a naming convention to keep monitors organized:

  • [pgbouncer-primary] ping /health/pgbouncer/ping
  • [pgbouncer-primary] utilization /health/pgbouncer/utilization
  • [pgbouncer-primary] wait-time /health/pgbouncer/waittime

Step 5: pgbouncer.ini Reference for Pool Sizing

When utilization alerts fire, the fix is usually pool size configuration. Key parameters in pgbouncer.ini:

[pgbouncer]
# Maximum total client connections across all pools
max_client_conn = 1000

# Maximum server connections per pool (per database+user pair)
default_pool_size = 25

# Maximum connections to create beyond pool_size when under load
reserve_pool_size = 5
reserve_pool_timeout = 3

# Maximum time (seconds) to wait for a connection before returning an error
query_wait_timeout = 30

# Pool mode: session, transaction, or statement
pool_mode = transaction

# Admin users allowed to connect to the pgbouncer pseudo-database
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

Transaction pooling (pool_mode = transaction) allows much higher server connection reuse than session pooling, at the cost of not supporting session-level features like LISTEN/NOTIFY or prepared statements without server_reset_query.

Alert Routing Summary

| Monitor | Severity | Channel | |---|---|---| | PgBouncer process availability | P1 | Slack + PagerDuty | | Pool utilization ≥ 90% | P2 | Slack | | Connection wait time > 2s | P1 | Slack + PagerDuty |

Summary

PgBouncer sits between your application and PostgreSQL — when it fails, your application can't reach the database even if PostgreSQL is healthy. Vigilmon's external HTTP checks fill the monitoring gap:

| Layer | Vigilmon Monitor | |---|---| | Pooler process availability | HTTP monitor on /health/pgbouncer/ping | | Server connection utilization | HTTP monitor on /health/pgbouncer/utilization | | Client connection wait time | HTTP monitor on /health/pgbouncer/waittime |

Start monitoring your PgBouncer for free at vigilmon.online.

Monitor your app with Vigilmon

Free plan — 5 monitors, no credit card required. Up and running in 60 seconds.

Start free →
← Back to all articles