Database Monitoring Alerting

Production-grade guide to database monitoring alerting covering architecture patterns, implementation strategies, testing approaches, and operational best practices for enterprise engineering teams.

Database monitoring alerting is the practice of continuously observing database health and performance, detecting anomalies and failures, and triggering notifications or automated responses when predefined thresholds or conditions are met. It matters when a database becomes the single source of truth, transactional bottleneck, or caching dependency for a system—when downtime or latency directly impacts user experience or business operations.

Detecting Failure Conditions

Database failures often manifest as unresponsive services, degraded queries, or data inconsistency. Alerting systems must detect these conditions early and precisely.

High Latency on Critical Queries

When a query is expected to complete in under 100ms but takes 500ms consistently, users experience delays. Alerting on query latency requires instrumenting the database’s query log or performance schema.

For PostgreSQL, use the pg_stat_statements extension to capture query execution metrics:

-- Enable query statistics collection
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Track the top 100 most expensive queries
SELECT 
  query,
  calls,
  total_time,
  mean_time,
  rows,
  100.0 * (mean_time / (SELECT avg(mean_time) FROM pg_stat_statements)) AS normalized_mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 100;

To alert on a query that exceeds 500ms on average, use a monitoring tool like Prometheus with a custom query:


- job_name: 'postgres-queries'
  static_configs:
    - targets: ['db.example.com:9187']
  metrics_path: '/metrics'
  scheme: 'http'

# In a script or cron job, collect and expose metrics
# Example: alerting on 'user_profile_load' query
curl -s 'http://localhost:9187/metrics' | grep 'pg_stat_statements_mean_time{query="user_profile_load"}'

Alert rule in Prometheus:

groups:
  - name: database-latency
    rules:
      - alert: HighUserProfileLoadLatency
        expr: |
          rate(pg_stat_statements_mean_time{query="user_profile_load"}[5m]) > 500
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "Average query time for user_profile_load exceeds 500ms"
          description: |
            The query 'user_profile_load' has averaged over 500ms in the last 10 minutes.
            Current mean time: {{ $value }}ms.

A common failure mode: the for duration is misinterpreted. A 10-minute for does not mean “trigger after 10 minutes,” but “the condition must be true for 10 consecutive minutes before alerting.” If the query spikes briefly and returns to normal within 9 minutes, the alert does not fire—leading to missed alerts.

High Query Load and Blocking

When the number of concurrent queries exceeds the database’s capacity, queries block on each other. Alert on pg_stat_activity for long-running or blocked queries.

SELECT 
  pid,
  query,
  state,
  now() - query_start AS duration,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > '30 seconds'::interval
ORDER BY duration DESC;

To detect blocking, track backend_xid and backend_xmin:

SELECT 
  pid,
  query,
  waiting,
  backend_xid,
  backend_xmin,
  (SELECT xid FROM pg_prepared_xacts WHERE gid = 'txn_123') AS prepared_xid
FROM pg_stat_activity
WHERE waiting = true
  AND state = 'active';

Use a monitoring script that checks for blocking every 30 seconds and sends an alert when more than five queries are waiting:

#!/bin/bash
# monitor-blocking.sh
DB_HOST="db.example.com"
DB_USER="monitor"
QUERY="
  SELECT count(*) AS waiting_count
  FROM pg_stat_activity
  WHERE waiting = true AND state = 'active'
  AND now() - query_start > '30 seconds'::interval
"

WAITING_COUNT=$(psql -h "$DB_HOST" -U "$DB_USER" -t -c "$QUERY" postgres)

if [ "$WAITING_COUNT" -gt 5 ]; then
  echo "ALERT: More than 5 queries are waiting (current: $WAITING_COUNT)" | \
    mail -s "Database Blocking Alert" [email protected]
fi

A subtle failure: waiting = true includes queries blocked on locks, but not on I/O or network. To catch all types of wait, use wait_event_type and wait_event in the alerting logic. A query may be waiting on Lock but the alert only checks waiting = true, missing the signal.

Responding to Performance Degradation

Alerts must not only detect problems but trigger actions to mitigate them.

Query Plan Regression

When a query suddenly starts using a full table scan instead of an index, performance degrades sharply. Detect this by comparing query execution plans over time.

For a given query, extract the plan using EXPLAIN and store it in a table:

CREATE TABLE query_plan_history (
  query_id TEXT,
  plan_hash BIGINT,
  plan JSONB,
  recorded_at TIMESTAMPTZ DEFAULT NOW()
);

-- Capture current plan
INSERT INTO query_plan_history (query_id, plan_hash, plan)
SELECT 
  'user_profile_load',
  (SELECT hashtext(plan::text) FROM (EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE id = 1000) AS q),
  (EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE id = 1000)
WHERE NOT EXISTS (
  SELECT 1 FROM query_plan_history 
  WHERE query_id = 'user_profile_load'
  AND plan_hash = (SELECT hashtext(plan::text) FROM (EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE id = 1000) AS q)
);

Alert when a new plan differs from the expected one:

-- Find recent plans with different structure
SELECT 
  q1.query_id,
  q1.plan_hash AS new_plan_hash,
  q1.plan AS new_plan,
  q2.plan_hash AS expected_plan_hash,
  q2.plan AS expected_plan
FROM query_plan_history q1
JOIN query_plan_history q2 ON q1.query_id = q2.query_id
WHERE q1.recorded_at > now() - '1 hour'::interval
  AND q2.recorded_at = (
    SELECT max(recorded_at) 
    FROM query_plan_history 
    WHERE query_id = q1.query_id 
      AND recorded_at < q1.recorded_at
  )
  AND q1.plan_hash != q2.plan_hash
  AND q1.plan::jsonb ? 'Plan'
  AND q2.plan::jsonb ? 'Plan'
  AND (q1.plan->'Plan'->>'Node Type') != (q2.plan->'Plan'->>'Node Type');

A script checks for plan changes every 15 minutes and sends an alert if the Node Type has changed:

import psycopg2
import json
import smtplib
from email.mime.text import MIMEText

conn = psycopg2.connect("dbname=monitor user=monitor host=db.example.com")
cur = conn.cursor()

cur.execute("""
  SELECT 
    q1.query_id,
    q1.plan->>'Plan'->>'Node Type' AS new_type,
    q2.plan->>'Plan'->>'Node Type' AS old_type
  FROM query_plan_history q1
  JOIN query_plan_history q2 ON q1.query_id = q2.query_id
  WHERE q1.recorded_at > now() - '1 hour'::interval
    AND q2.recorded_at = (
      SELECT max(recorded_at) 
      FROM query_plan_history 
      WHERE query_id = q1.query_id 
        AND recorded_at < q1.recorded_at
    )
    AND q1.plan_hash != q2.plan_hash
""")

rows = cur.fetchall()
if rows:
  msg = MIMEText("Query plan changed:\n\n")
  for row in rows:
    msg.attach(MIMEText(f"Query: {row[0]}\nOld: {row[2]}\nNew: {row[1]}\n", 'plain'))
  msg['Subject'] = 'Database Query Plan Regression'
  msg['From'] = '[email protected]'
  msg['To'] = '[email protected]'

  with smtplib.SMTP('localhost') as server:
    server.send_message(msg)

The sharp edge: hashtext(plan::text) assumes that the JSON representation is stable. If EXPLAIN (FORMAT JSON) outputs slightly different key ordering or whitespace, two identical plans are treated as different. A common fix is to normalize the JSON before hashing:

SELECT hashtext(jsonb_pretty(plan)) FROM (EXPLAIN (FORMAT JSON) ...) AS q;

Replication Lag and Failover Readiness

When a database is replicated, a lag between primary and replica can cause data inconsistency. Alert on replication lag and readiness for failover.

In PostgreSQL, use pg_stat_replication:

SELECT 
  client_addr,
  state,
  sync_state,
  pg_current_wal_lsn() - replay_lsn AS lag_bytes,
  now() - last_received_wal_timestamp AS lag_seconds,
  last_received_wal_timestamp,
  replay_lsn
FROM pg_stat_replication
WHERE state = 'streaming';

A script checks replication lag every 5 minutes:

#!/bin/bash
# check-replication-lag.sh
DB_HOST="db.example.com"
THRESHOLD=100000000  # 100MB lag
THRESHOLD_SECONDS=60

while true; do
  LAG_BYTES=$(psql -h "$DB_HOST" -U monitor -t -c "
    SELECT pg_current_wal_lsn() - replay_lsn
    FROM pg_stat_replication
    WHERE state = 'streaming'
    ORDER BY lag_bytes DESC
    LIMIT 1
  ")

  LAG_SECONDS=$(psql -h "$DB_HOST" -U monitor -t -c "
    SELECT EXTRACT(EPOCH FROM (now() - last_received_wal_timestamp))
    FROM pg_stat_replication
    WHERE state = 'streaming'
  ")

  if [ "$LAG_BYTES" -gt "$THRESHOLD" ] || [ "$LAG_SECONDS" -gt "$THRESHOLD_SECONDS" ]; then
    echo "ALERT: Replication lag exceeds threshold (bytes: $LAG_BYTES, seconds: $LAG_SECONDS)" | \
      mail -s "Replication Lag Alert" [email protected]
  fi

  sleep 300
done

Failures arise when pg_current_wal_lsn() and replay_lsn() are not in the same timeline. If the primary has switched timelines, replay_lsn may point to a WAL segment not yet applied. To detect timeline drift, compare pg_current_wal_lsn() against pg_last_wal_receive_lsn() and pg_last_wal_replay_lsn().

Automating Corrective Actions

Alerting is not just notification—it is a trigger for automation.

Automated Index Creation

When a query shows poor performance and the query plan lacks an index, trigger an index creation.

Detect missing indexes via pg_stat_user_tables and pg_index:

SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  (idx_tup_read + idx_tup_fetch) / idx_scan AS avg_rows_per_scan
FROM pg_stat_user_tables t
JOIN pg_index i ON t.relid = i.indrelid
WHERE idx_scan = 0
  AND t.schemaname = 'public'
  AND t.relname NOT IN ('users', 'orders')
  AND (idx_tup_read + idx_tup_fetch) / idx_scan > 10
ORDER BY idx_tup_read DESC;

A script runs daily and creates a new index if a query scans more than 10 rows per index scan:

import psycopg2
import json

conn = psycopg2.connect("dbname=monitor user=monitor host=db.example.com")
cur = conn.cursor()

cur.execute("""
  SELECT schemaname, tablename, column_names
  FROM (
    SELECT 
      schemaname,
      tablename,
      string_agg(attname, ',') AS column_names
    FROM pg_stat_user_tables t
    JOIN pg_index i ON t.relid = i.indrelid
    JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
    WHERE idx_scan = 0
      AND (idx_tup_read + idx_tup_fetch) / idx_scan > 10
    GROUP BY schemaname, tablename
  ) AS q
  WHERE tablename NOT IN (SELECT tablename FROM pg_indexes WHERE schemaname = 'public' AND indexname LIKE 'idx_%')
""")

for row in cur.fetchall():
  schemaname, tablename, column_names = row
  index_name = f"idx_{tablename}_{column_names.replace(',', '_')}"
  create_index = f"""
    CREATE INDEX IF NOT EXISTS {index_name}
    ON {schemaname}.{tablename} ({column_names})
    WHERE {column_names} IS NOT NULL;
  """
  cur.execute(create_index)
  conn.commit()
  print(f"Created index: {index_name}")

conn.close()

A common mistake: the script creates an index but does not update pg_stat_user_tables. The next run finds the same missing index. The fix: after creating the index, run ANALYZE on the table.

Triggering Failover on Primary Unavailability

When the primary database becomes unreachable, promote a replica to primary and update application configuration.

Detect primary unavailability via heartbeat:

SELECT 
  now() - last_hearbeat AS time_since_last_heartbeat
FROM pg_stat_activity
WHERE application_name = 'primary'
  AND state = 'idle'
  AND now() - query_start > '10 minutes'::interval;

A script pings the primary every 30 seconds:

#!/bin/bash
# failover-check.sh
PRIMARY_HOST="primary.example.com"
HEARTBEAT_INTERVAL=30

while true; do
  if ! ping -c 1 -W 2 "$PRIMARY_HOST" > /dev/null 2>&1; then
    echo "PRIMARY DOWN: $PRIMARY_HOST unreachable" | \
      mail -s "Database Primary Failover" [email protected]

    # Promote replica
    psql -h primary.example.com -U postgres -c "
      SELECT pg_promote();
    "

    # Update application config
    cat > /etc/app/config.json <<EOF
{
  "database": {
    "primary": "replica.example.com",
    "replicas": [
      "replica2.example.com"
    ]
  }
}
EOF

    # Restart services
    systemctl reload nginx
    systemctl restart app-service

    # Send confirmation
    echo "Failover completed: primary now at replica.example.com" | \
      mail -s "Failover Confirmed" [email protected]
  fi

  sleep "$HEARTBEAT_INTERVAL"
done

The critical failure: pg_promote() returns true but does not update the pg_control file. The replica becomes primary, but the old primary does not update its pg_hba.conf or postgresql.conf. The fix: after pg_promote(), run pg_reload_conf() and pg_wal_replay_pause() to ensure consistency.

Summary of Key Patterns

These patterns are not tied to a single product. They are the shared language of database monitoring alerting.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.