SQL Alerting: Patterns, Queries, and Tools That Actually Work

SQL alerting turns raw database facts into timely, actionable notifications. Whether you’re tracking failed payments, stuck jobs, or a sudden spike in sign-ups, the right alerting approach helps you respond fast without drowning in noise. This guide walks through proven patterns for sql alerting, practical query examples for PostgreSQL and MySQL, event-driven alternatives, and hard-won best practices for performance and security.

What Is SQL Alerting and Why It Matters

SQL alerting is the practice of running database queries on a schedule or reacting to database events to detect important conditions, then notifying people or systems. Done well, it creates a safety net for product quality, revenue, data integrity, and security.

• Product health: Detect empty search results, broken feature flags, or API error bursts.
• Revenue protection: Flag payment declines, cart abandonment, or fulfillment backlogs.
• Data quality: Catch missing records, null explosions, or delayed pipelines.
• Security: Alert on suspicious logins, privilege changes, or unexpected data access patterns.

Core Patterns for SQL Alerting

1) Polling Queries on a Schedule

Polling periodically runs read-only queries and triggers a notification when results cross a threshold. It’s simple, reliable, and works across most databases.

• Pros: Easy to implement, predictable, database-agnostic.
• Cons: Near-real-time requires short intervals (load trade-offs), potential duplicate alerts without deduplication.

2) Event-Driven Signals

Event-driven alerting reduces latency and load by reacting to changes as they happen.

• PostgreSQL NOTIFY/LISTEN: Use triggers to send lightweight messages on insert/update.
• MySQL change data capture (CDC): Consume binlog changes via tools like Debezium, then evaluate alert conditions in code.
• Triggers: Emit rows into an alert_events table or enqueue messages on updates.

3) Hybrid Approaches

Combine fast event-driven hints with scheduled validation queries. For example, an event can raise a candidate alert, while a scheduled query confirms the condition still holds to reduce flakiness.

Comparing SQL Alerting Approaches

Approach	Latency	Complexity	Load	Best For
Polling (cron/scheduler)	Seconds–minutes	Low	Query-dependent	Broad conditions, simple setup
Postgres NOTIFY/LISTEN	Sub-second	Medium	Very low	Row-level change alerts
CDC (binlog/WAL via Debezium)	Sub-second–seconds	High	External infra	Streaming analytics, complex rules
Trigger to alert table	Sub-second	Medium	Very low	Auditable alert events

Designing Robust Alert Queries

Effective sql alerting relies on crisp conditions, appropriate thresholds, and cheap execution plans.

Example 1: Error Bursts in the Last 5 Minutes

PostgreSQL:

SELECT count(*) AS errors_5m
FROM app_logs
WHERE level = 'error'
  AND created_at >= now() - interval '5 minutes';

Alert when errors_5m > 200 or when the rate is 3 standard deviations above baseline:

WITH recent AS (
  SELECT count(*) AS c
  FROM app_logs
  WHERE level = 'error'
    AND created_at >= now() - interval '5 minutes'
), hist AS (
  SELECT avg(cnt) AS baseline, stddev_samp(cnt) AS sigma
  FROM (
    SELECT date_trunc('hour', created_at) AS bucket, count(*) AS cnt
    FROM app_logs
    WHERE level = 'error'
      AND created_at >= now() - interval '14 days'
    GROUP BY 1
  ) t
)
SELECT c, baseline, sigma, (c - baseline)/NULLIF(sigma, 1) AS z
FROM recent, hist
WHERE (c - baseline)/NULLIF(sigma, 1) >= 3;

Example 2: Stuck Jobs Older Than 10 Minutes

MySQL:

SELECT COUNT(*) AS stuck_jobs
FROM jobs
WHERE status = 'processing'
  AND updated_at <= NOW() - INTERVAL 10 MINUTE;

Example 3: Missing Heartbeat (Service Down)

PostgreSQL:

SELECT 1
FROM heartbeats
WHERE service = 'billing-worker'
  AND last_seen_at >= now() - interval '3 minutes';

Alert if the query returns zero rows.

Example 4: High Payment Declines

MySQL:

SELECT COUNT(*) AS declines_15m
FROM payments
WHERE status = 'declined'
  AND created_at >= NOW() - INTERVAL 15 MINUTE;

Alert when the count exceeds a threshold, or compute a rate vs. all payments in the same window.

Alert only on conditions users care about and engineering can fix. Everything else is noise.

Event-Driven SQL Alerting: Quick Starts

PostgreSQL NOTIFY/LISTEN with a Trigger

-- 1) Trigger function
CREATE OR REPLACE FUNCTION notify_new_order() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('orders_channel', NEW.id::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2) Trigger on insert
CREATE TRIGGER orders_notify
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION notify_new_order();

Minimal Python listener:

import os, json, time, psycopg2
from psycopg2.extras import RealDictCursor

conn = psycopg2.connect(os.environ['PG_DSN'])
conn.set_session(autocommit=True)
cur = conn.cursor()
cur.execute("LISTEN orders_channel;")

while True:
    conn.poll()
    while conn.notifies:
        n = conn.notifies.pop(0)
        order_id = int(n.payload)
        # Apply alert logic (e.g., high-value order)
        # send_notification(...)
    time.sleep(0.2)

MySQL CDC via Debezium (Conceptual)

1. Enable binlog and row-based replication on MySQL.
2. Run Debezium to stream changes to Kafka or Redpanda.
3. Consume the topic in your service; evaluate alert rules and notify.

For lighter setups, a simple trigger that inserts into an alert_events table plus a small worker that polls that table can be sufficient.

Delivery Channels and Rate Limiting

Channel	Strengths	Use When	Notes
Push notification	Fast, interruptive	Critical, time-sensitive	Respect quiet hours, batch non-critical
Email	Asynchronous, easy audit trail	Summaries, non-urgent	Use digesting to reduce noise
Webhook	Automates responses	Integrations and runbooks	Sign requests; retry with backoff
Chat (Slack/Teams)	Team visibility	Incident channels	Threaded updates, emoji ACKs

Security and Performance Best Practices

• Least privilege: Use read-only users with access limited to necessary tables and views.
• Parameterize queries: Prevent injection and enable plan reuse.
• Index for the predicate: Ensure predicates on time/status columns are supported by indexes; confirm with EXPLAIN/EXPLAIN ANALYZE.
• Use narrow windows: Query only recent time ranges (e.g., last 5–15 minutes).
• Precompute: Materialized views or summary tables reduce cost for heavy rules.
• Backoff and jitter: If the database is under load, increase intervals and add randomness to avoid thundering herds.
• Secure connections: TLS for in-transit encryption, rotate credentials, store secrets securely.
• Deduplicate and suppress: Key alerts by a stable identifier and add cooldown periods.

Polling Implementation Patterns

Python example with dedup and cooldown:

import os, time, json, hashlib, mysql.connector

QUERY = """
SELECT COUNT(*) AS stuck_jobs
FROM jobs
WHERE status = 'processing'
  AND updated_at <= NOW() - INTERVAL 10 MINUTE;
"""

cooldown_seconds = 600
last_sent = {}

conn = mysql.connector.connect(**{
    'host': os.environ['DB_HOST'],
    'user': os.environ['DB_RO_USER'],
    'password': os.environ['DB_RO_PASS'],
    'database': os.environ['DB_NAME'],
    'ssl_disabled': False
})

while True:
    cur = conn.cursor(dictionary=True)
    cur.execute(QUERY)
    row = cur.fetchone()
    count = row['stuck_jobs']

    if count >= 5:
        key = hashlib.sha1(QUERY.encode()).hexdigest()
        now = time.time()
        if last_sent.get(key, 0) <= now - cooldown_seconds:
            # send_notification(f"Stuck jobs: {count}")
            last_sent[key] = now
    time.sleep(60)

Testing and Observability for SQL Alerting

• Unit-test queries: Use fixtures to ensure the query returns exactly what you expect for edge cases.
• Synthetic events: Insert controlled rows to verify end-to-end notifications.
• Coverage: Track which rules fired in the last week; remove stale ones.
• Runbooks: Link alerts to a short remediation guide; include relevant charts and dashboards.
• Monitoring the monitor: Alert when your alerting pipeline fails (no runs, no heartbeats).

Common Pitfalls and How to Fix Them

• Duplicate alerts: Add a primary key for the alert signature and a cooldown window; only send on state change.
• Missed alerts: Use indexes, avoid long table scans, and lower your schedule interval judiciously. For events, add durable queues.
• Slow queries: Limit time ranges, pre-aggregate, and inspect plans. Consider partial indexes on hot predicates.
• Alert fatigue: Start with high-severity rules; add trend-based rules later with smart thresholds.
• Timezone/DST issues: Store timestamps in UTC; schedule using UTC; display in local time only for humans.

A Quick SQL Alerting Checklist

• Define the user-impacting condition and desired response time.
• Choose polling, event-driven, or hybrid based on latency and complexity needs.
• Write read-only, index-friendly queries with narrow time windows.
• Add deduplication keys, cooldowns, and severity levels.
• Secure connections and credentials; use least privilege.
• Test with synthetic data; add runbooks and metrics.
• Review and prune rules monthly to keep signal high.

Putting It All Together

Great sql alerting is a balancing act: fast enough to matter, focused enough to cut through the noise, and efficient enough to run all day without hurting your database. Start with a few high-signal rules—like error bursts, stuck jobs, and payment declines—then layer in event-driven patterns where latency or scale demands it. Keep security tight, measure false positives, and iterate on thresholds as your traffic and data evolve.

If you prefer to receive database change notifications on your phone with minimal setup, an iOS app such as Query Alert can connect to PostgreSQL, MySQL, and MongoDB using read-only queries and send push or email alerts on a schedule.

Whichever route you choose, treat alerting as a product: design for your users, verify with tests, and keep improving. The result is faster response times, calmer on-call shifts, and a more resilient system.