Incident Detection in PostgreSQL: From Metric Threshold to Resolved Alert

A graph that turns red is not incident management. An email that says "CPU is high" is not incident management. Real incident detection means: a metric crosses a threshold, an incident is opened with context, the right person is notified, the incident is tracked from open to acknowledged to resolved, and duplicate alerts do not spam the team. This post walks through exactly how PG Monitoring does that.

What Triggers an Incident?

PG Monitoring generates incidents from three distinct sources, each with its own detection logic:

• Threshold rules — scalar metrics that cross configurable warning/critical thresholds. These are the bread and butter: cache hit ratio below 95%, dead tuples above 100,000, connection usage above 80%, replication lag above 300 seconds, disk free space below 10%.
• Anomaly detection — ML-based detection that flags metrics deviating from a learned baseline, even if they have not crossed a fixed threshold. A query that usually takes 50 ms suddenly averaging 800 ms is an anomaly, not a threshold breach.
• Plan regression — when PostgreSQL switches a query's execution plan (e.g., from Index Scan to Seq Scan) and the new plan is significantly slower. This is detected by comparing per-query latency baselines before and after the plan flip.

Threshold Rules: Configurable Per Organization and Per Instance

Every scalar metric the agent collects has a default warning and critical threshold. Defaults are sensible for most workloads, but you can override them per organization or per instance:

-- Example: the alert rules evaluated on every metric push
{
  cache_hit_ratio:     { warning: 95,  critical: 90,  comparator: :lt },
  dead_tuples:         { warning: 100_000, critical: 500_000, comparator: :gt },
  long_running_queries:{ warning: 5,  critical: 15, comparator: :gt },
  connection_usage:    { warning: 80, critical: 95, comparator: :gt },
  replication_lag:     { warning: 300, critical: 600, comparator: :gt },
  disk_free_pct:       { warning: 15, critical: 5,  comparator: :lt }
}

When the agent pushes a metric snapshot, the SaaS evaluates each rule against the current value. If the value crosses the warning threshold, a warning incident is opened. If it crosses critical, a critical incident is opened — or the existing warning incident is escalated to critical.

Deduplication: One Incident Per Problem, Not Per Data Point

The agent pushes metrics every 60 seconds. If cache hit ratio is below 90% for an hour, that is 60 pushes — but you should get one incident, not 60. PG Monitoring deduplicates by rule_key: before opening a new incident, it checks whether an unresolved incident for the same rule and instance already exists. If it does, the existing incident is updated (refreshed severity, latest value) rather than duplicated.

The same logic applies to long-running query alerts: if a query with PID 12345 has been running for 35 minutes and an incident already exists for it, the incident's title is refreshed to "Query running 35 min (PID 12345)" instead of opening a new one. When the query finishes, the incident is auto-resolved.

The Incident Lifecycle

Every incident follows a three-state lifecycle:

1. Open — the incident has been detected but not yet looked at. This is where it starts.
2. Acknowledged — someone on the team has seen it and taken ownership. The acknowledging user and timestamp are recorded.
3. Resolved — the underlying problem is fixed (or self-corrected). The resolving user, resolution text, and root cause are recorded. The incident's total duration (from detected_at to resolved_at) is calculated automatically.

Incidents can also be reopened if the problem comes back, and assigned to a specific team member for routing.

Real-Time Broadcast

When an incident is created or updated, it is broadcast over WebSocket to all connected clients in the same organization. If you have the dashboard open, the incident appears instantly — no page refresh. The broadcast includes the incident title, severity, instance name, and detection timestamp.

Notification Routing

Beyond the WebSocket broadcast, incidents trigger asynchronous notification delivery:

• Email — sent via NotificationDeliveryJob, enriched with AI-generated improvement suggestions when the AI analyzer is enabled.
• Slack / PagerDuty / webhooks — configurable per organization, routed by severity (e.g., critical → PagerDuty, warning → Slack).
• n8n / Grafana webhooks — external systems can also push incidents into PG Monitoring via authenticated webhooks, so alerts from other tools appear in the same incident queue.

Anomaly Detection: Beyond Fixed Thresholds

Thresholds catch "cache hit ratio is below 95%." They do not catch "this query's average execution time went from 50 ms to 400 ms" — because 400 ms might be well below any fixed threshold you would set. PG Monitoring's anomaly detector runs as a periodic Sidekiq job and uses statistical analysis (z-score, moving averages) on per-query metrics from pg_stat_statements:

• It computes a baseline of normal behavior per query fingerprint.
• It flags deviations where the z-score exceeds a configurable sensitivity (typically 3σ).
• It opens an incident with the query text, the baseline mean, the current value, and the z-score.
• Incidents are deduplicated per query fingerprint per hour — no spam.

Plan Regression: The Silent Killer

PostgreSQL's planner is good, but it is not perfect. A stale statistic, a parameter sniffing issue, or a config change can cause the planner to switch from an Index Scan to a Sequential Scan on a large table. The query text does not change — only the plan does — and suddenly a query that took 20 ms takes 800 ms. PG Monitoring's plan regression detector:

1. Tracks the average execution time per query fingerprint over time.
2. Detects when the average jumps by a significant factor (3× or more).
3. Opens a critical incident with the slowdown percentage, the before/after latency, and a suggestion to check stale statistics (run ANALYZE).

Webhook Integration: External Alerts as Incidents

PG Monitoring accepts incoming alerts from external systems via authenticated webhooks. Grafana alerting payloads and n8n workflow alerts are both supported. The webhook authenticates via a shared secret or organization-scoped API key, resolves the target instance by name or ID, and creates an incident with the external alert's title, description, and severity. This means your entire alerting surface — Postgres-native metrics, Grafana dashboards, and custom n8n workflows — funnels into one incident queue.

Why This Matters

An incident is not just an alert. It is a tracked record of a problem from detection to resolution, with severity, ownership, timeline, and root cause. When you look back at "what happened last Tuesday at 3 AM," you do not have to grep through email logs — you open the incident and see the full history: when it was detected, who acknowledged it, how long it took to resolve, and what the root cause was. That is the difference between monitoring and incident management.