How to Set Up Automated PostgreSQL Health Monitoring and Ping Discord Webhooks

Direct Answer: What Are the Critical PostgreSQL Health Checks?

A production-grade PostgreSQL monitoring daemon must continuously audit seven vital system metrics:

1. Connection Pool Saturation: Active vs max connections (alerting when usage exceeds 80%).
2. Buffer Cache Hit Ratio: Memory efficiency (alerting when reads fall below 99.0%).
3. Deadlock & Lock Wait Duration: Queries blocked for >30 seconds by exclusive locks.
4. Long-Running Transactions: Active queries running for >60 seconds preventing autovacuum.
5. Transaction ID Wraparound Margin: Age of oldest unfrozen XID approaching 2 billion limits.
6. Dead Tuple Bloat: Proportion of dead tuples across high-churn tables exceeding 20%.
7. Disk Storage & WAL Space: Volume capacity on tablespace partitions exceeding 85%.

Diagnostic SQL: Connection Utilization

SELECT 
  count(*) AS total_connections,
  count(*) FILTER (WHERE state = 'active') AS active_connections,
  count(*) FILTER (WHERE state = 'idle') AS idle_connections,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_transaction,
  current_setting('max_connections')::int AS max_connections,
  round((count(*)::numeric / current_setting('max_connections')::numeric) * 100, 2) AS usage_percent
FROM pg_stat_activity;

Diagnostic SQL: Global Cache Hit Ratio

SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  round((sum(heap_blks_hit)::numeric / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0)::numeric) * 100, 2) as cache_hit_ratio
FROM pg_statio_user_tables;

Diagnostic SQL: Detecting Blocked Queries

SELECT 
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_statement,
  blocking_activity.query AS current_statement_in_blocking_process,
  round(extract(epoch from now() - blocked_activity.query_start)::numeric, 1) as wait_duration_seconds
FROM  pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Diagnostic SQL: XID Wraparound Margin

SELECT 
  datname,
  age(datfrozenxid) as xid_age,
  2147483648 - age(datfrozenxid) as remaining_tx_until_freeze,
  round((age(datfrozenxid)::numeric / 2147483648::numeric) * 100, 2) as xid_consumption_percent
FROM pg_database
WHERE datallowconn
ORDER BY 2 DESC;

Complete Python Daemon Script (pg_health_monitor.py)

import os
import sys
import time
import requests
import psycopg2
from psycopg2.extras import RealDictCursor

DISCORD_WEBHOOK_URL = os.getenv("DISCORD_WEBHOOK_URL")
PG_HOST = os.getenv("PG_HOST", "localhost")
PG_PORT = os.getenv("PG_PORT", "5432")
PG_DB = os.getenv("PG_DB", "postgres")
PG_USER = os.getenv("PG_USER", "postgres")
PG_PASS = os.getenv("PG_PASS", "")

def send_discord_alert(title, description, fields, severity="WARNING"):
    colors = {
        "INFO": 3066993,      # Emerald Green (#2ECC71)
        "WARNING": 16753920,  # Amber Yellow (#FFA500)
        "CRITICAL": 15158332  # Crimson Red (#E74C3C)
    }
    
    payload = {
        "username": "Postgres Health Sentinel",
        "avatar_url": "https://zelobiz.com/wp-content/uploads/postgres-sentinel-icon.png",
        "embeds": [{
            "title": f"[{severity}] {title}",
            "description": description,
            "color": colors.get(severity, 16753920),
            "fields": fields,
            "footer": {"text": f"Cluster: {PG_HOST}:{PG_PORT}/{PG_DB} | Zelobiz Operations Sentinel"},
            "timestamp": time.strftime("%Y-%m-%dT%H:%M:%SZ", time.gmtime())
        }]
    }
    
    try:
        r = requests.post(DISCORD_WEBHOOK_URL, json=payload, timeout=10)
        r.raise_for_status()
    except Exception as e:
        print(f"Failed to transmit Discord webhook: {e}", file=sys.stderr)

def run_diagnostics():
    conn = None
    try:
        conn = psycopg2.connect(
            host=PG_HOST, port=PG_PORT, dbname=PG_DB,
            user=PG_USER, password=PG_PASS, connect_timeout=5
        )
        cur = conn.cursor(cursor_factory=RealDictCursor)

        # 1. Connection Saturation Check
        cur.execute("""
            SELECT 
                count(*) AS total,
                count(*) FILTER (WHERE state = 'active') AS active,
                count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx,
                current_setting('max_connections')::int AS max_conns
            FROM pg_stat_activity;
        """)
        conns = cur.fetchone()
        usage_pct = (conns['total'] / conns['max_conns']) * 100

        if usage_pct >= 90:
            send_discord_alert(
                "CRITICAL: Connection Pool Exhaustion Imminent",
                f"PostgreSQL connection usage has reached **{usage_pct:.1f}%** of total capacity.",
                [
                    {"name": "Total Connections", "value": str(conns['total']), "inline": True},
                    {"name": "Max Connections", "value": str(conns['max_conns']), "inline": True},
                    {"name": "Idle in Transaction", "value": str(conns['idle_in_tx']), "inline": True}
                ],
                severity="CRITICAL"
            )
        elif usage_pct >= 75:
            send_discord_alert(
                "WARNING: High Connection Pool Saturation",
                f"PostgreSQL connection pool usage is currently at **{usage_pct:.1f}%**.",
                [
                    {"name": "Total Connections", "value": str(conns['total']), "inline": True},
                    {"name": "Max Connections", "value": str(conns['max_conns']), "inline": True}
                ],
                severity="WARNING"
            )

        # 2. Buffer Cache Hit Ratio Check
        cur.execute("""
            SELECT 
                round((sum(heap_blks_hit)::numeric / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0)::numeric) * 100, 2) AS ratio
            FROM pg_statio_user_tables;
        """)
        cache = cur.fetchone()
        if cache and cache['ratio'] is not None and float(cache['ratio']) < 95.0:
            send_discord_alert(
                "WARNING: Buffer Cache Degradation",
                f"Cache Hit Ratio has dropped to **{cache['ratio']}%**. Disk I/O latency is impacting query performance.",
                [{"name": "Recommended Action", "value": "Increase shared_buffers or tune working memory (work_mem)."}],
                severity="WARNING"
            )

        # 3. Blocked Locks / Deadlock Waiters
        cur.execute("""
            SELECT count(*) AS blocked_count
            FROM pg_catalog.pg_locks blocked_locks
            WHERE NOT blocked_locks.granted;
        """)
        locks = cur.fetchone()
        if locks['blocked_count'] > 0:
            send_discord_alert(
                "CRITICAL: Exclusive Lock Contention Detected",
                f"Found **{locks['blocked_count']}** queries currently blocked waiting for database locks.",
                [{"name": "Immediate Action", "value": "Inspect pg_stat_activity to terminate blocking transaction PID."}],
                severity="CRITICAL"
            )

        cur.close()
    except Exception as err:
        send_discord_alert(
            "CRITICAL: Database Unreachable",
            f"Failed to connect to PostgreSQL instance at {PG_HOST}:{PG_PORT}.\nError: `{err}`",
            [{"name": "Status", "value": "Database Down or Network Partitioned"}],
            severity="CRITICAL"
        )
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    run_diagnostics()

Platform-Specific Telemetry Nuances

• AWS RDS & Aurora PostgreSQL: AWS CloudWatch provides storage and CPU metrics, but connection metrics are delayed by 60 seconds. Our Python script queries pg_stat_activity directly, catching connection spikes before AWS alarms fire.
• Supabase (Managed pgvector & Postgres): Supabase utilizes PgBouncer connection pooling in transaction mode by default. Always point your health monitor script to the direct session port (usually port 5432 or direct connection string) rather than the transaction pooler port (6543) to ensure catalog access functions properly.
• Neon Serverless Postgres: Neon autosuspends compute instances after periods of inactivity. Ensure your health monitor script runs during production hours or configure Neon’s keep-alive parameters if continuous monitoring is required.

Diagnostic SQL: Table Bloat & Dead Tuple Ratio

SELECT 
  schemaname,
  relname as table_name,
  n_live_tup,
  n_dead_tup,
  round((n_dead_tup::numeric / nullif(n_dead_tup + n_live_tup, 0)::numeric) * 100, 2) as dead_tuple_ratio,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE (n_dead_tup + n_live_tup) > 10000
ORDER BY dead_tuple_ratio DESC
LIMIT 10;

Diagnostic SQL: Top 5 Queries by Total Execution Time

SELECT 
  round(total_exec_time::numeric, 2) as total_time_ms,
  calls,
  round(mean_exec_time::numeric, 2) as mean_time_ms,
  round((total_exec_time / sum(total_exec_time) OVER ()) * 100, 2) as percentage_cpu_share,
  substring(query, 1, 120) as query_fingerprint
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Diagnostic SQL: WAL Archive Health Check

SELECT 
  archived_count,
  last_archived_wal,
  last_archived_time,
  failed_count,
  last_failed_wal,
  last_failed_time
FROM pg_stat_archiver;

Connection Pooler Optimization Guidelines

• PgBouncer Transaction Pooling Mode: Configure PgBouncer in pool_mode = transaction. Under this topology, client connections only lease a physical database socket for the exact duration of a SQL transaction, releasing it instantly back to the pool once committed. This allows 50 physical PostgreSQL connections to serve over 5,000 concurrent web clients seamlessly.
• Supavisor (Elixir/Erlang BEAM Pooling): Supavisor provides modern multi-tenant connection pooling with zero cold-start delay. When monitoring Supavisor clusters, track both client-side pool queue latency and database-side backend allocation to detect pool exhaustion before requests timeout.
• Ideal max_connections Formula: An optimal starting formula for raw PostgreSQL connections is: ((core_count * 2) + effective_spindle_count). On an 8-core server with NVMe SSDs, maintaining max_connections = 50 behind PgBouncer delivers 3x higher transaction throughput than allowing 500 unpooled connections.