How to Set Up Query Alerts Without Polling: Event‑Driven Options

If you’ve ever scheduled a job to run every minute to check for database changes, you’ve felt the limits of polling: wasted cycles, delayed alerts, and noisy queries that compete with production traffic. The good news is you can set up query alerts without polling by switching to event‑driven patterns that push changes to your alerting pipeline in real time.

This guide explains the core approaches across PostgreSQL, MySQL, and MongoDB, shows how to wire them into alert rules, and includes concise code samples to get you into production without burning time or CPU.

Why move beyond polling?

• Lower latency: Push changes immediately instead of waiting for the next interval.
• Less load: Eliminate repetitive full‑table scans and reduce contention.
• Higher fidelity: Capture every change, including rapid updates that polling can miss.
• Simpler alert logic: Evaluate rules on the exact change event, not on re-queries of current state.

Event‑driven alerts trade scheduled checks for streams of changes, producing faster signals with fewer resources.

Core ways to set up query alerts without polling

There are three dominant patterns for push‑based change detection:

1. Database-native notifications (e.g., PostgreSQL LISTEN/NOTIFY)
2. Change Data Capture (CDC) via logs (e.g., MySQL binlog, Postgres logical decoding, Debezium)
3. Built-in change streams (e.g., MongoDB change streams)

PostgreSQL LISTEN/NOTIFY with triggers

Best for: Lightweight, low-latency alerts on specific tables or conditions where you control the schema.

Define a trigger that emits a notification on relevant changes. A listener process subscribes and evaluates alert rules.

-- 1) Create a trigger function that publishes a compact payload
CREATE OR REPLACE FUNCTION notify_big_orders() RETURNS trigger AS $$
DECLARE
  payload JSON;
BEGIN
  IF TG_OP = 'INSERT' THEN
    IF NEW.status = 'PAID' AND NEW.total_amount >= 10000 THEN
      payload := json_build_object(
        'op', TG_OP,
        'order_id', NEW.id,
        'customer_id', NEW.customer_id,
        'total', NEW.total_amount,
        'ts', now()
      );
      PERFORM pg_notify('orders_channel', payload::text);
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2) Attach the trigger to your orders table
CREATE TRIGGER trg_notify_big_orders
AFTER INSERT ON public.orders
FOR EACH ROW EXECUTE FUNCTION notify_big_orders();

Then, listen from your application:

# Python example using psycopg2
import json, select, psycopg2

conn = psycopg2.connect(dsn="postgresql://app@db/prod")
conn.set_session(autocommit=True)
cur = conn.cursor()
cur.execute("LISTEN orders_channel;")
print("Listening for big order events...")

while True:
    if select.select([conn], [], [], 60) == ([], [], []):
        continue
    conn.poll()
    while conn.notifies:
        notify = conn.notifies.pop(0)
        event = json.loads(notify.payload)
        # Route to your alerting handler
        print(f"ALERT: High-value order {event['order_id']} for ${event['total']}")

Notes: NOTIFY payloads are limited (typically around 8KB per message), so send compact JSON. If you need the full row, pass an ID and re‑query by primary key in the listener.

PostgreSQL CDC with logical decoding

Best for: System‑wide, durable change streams with replay, or when you cannot add triggers everywhere.

Use logical replication slots with a connector like Debezium to stream inserts/updates/deletes to Kafka or directly to a sink.

{
  "name": "inventory-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "db",
    "database.port": "5432",
    "database.user": "repl",
    "database.password": "***",
    "database.dbname": "prod",
    "slot.name": "alerts_slot",
    "publication.autocreate.mode": "filtered",
    "table.include.list": "public.orders",
    "tombstones.on.delete": "false",
    "topic.prefix": "cdc"
  }
}

From there, a small consumer evaluates rules and sends alerts. CDC gives you durability, ordering per key, and recovery after downtime.

MySQL binlog CDC (Debezium or Maxwell)

Best for: MySQL workloads where you want near real‑time alerts without touching application queries.

Enable row‑based binlog and stream changes:

-- my.cnf
server_id=1001
log_bin=mysql-bin
binlog_format=ROW
binlog_row_image=FULL

Debezium connector example:

{
  "name": "mysql-orders",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "database.hostname": "mysql",
    "database.port": "3306",
    "database.user": "repl",
    "database.password": "***",
    "database.server.id": "12345",
    "database.include.list": "appdb",
    "table.include.list": "appdb.orders",
    "include.schema.changes": "false",
    "topic.prefix": "cdc"
  }
}

With Maxwell’s Daemon, you can output JSON to Kafka or a webhook, then run your alert logic downstream.

MongoDB change streams

Best for: Document updates where you want fine‑grained filters, no external connector, and minimal setup on Atlas or self‑hosted MongoDB (replica set/sharded cluster required).

// Node.js example with official driver
const { MongoClient } = require('mongodb');

(async () => {
  const client = new MongoClient(process.env.MONGO_URI);
  await client.connect();
  const coll = client.db('app').collection('orders');

  const pipeline = [
    { $match: { 'operationType': { $in: ['insert', 'update'] } } },
    { $match: { $or: [
      { 'fullDocument.status': 'PAID', 'fullDocument.total': { $gte: 10000 } },
      { 'updateDescription.updatedFields.status': 'PAID' }
    ] } }
  ];

  const changeStream = coll.watch(pipeline, { fullDocument: 'updateLookup' });
  console.log('Watching orders for high-value PAID updates...');

  changeStream.on('change', evt => {
    const doc = evt.fullDocument;
    // Send alert
    console.log(`ALERT: Order ${doc._id} total ${doc.total}`);
  });
})();

Change streams handle backpressure and resume tokens, helping you recover from transient failures.

Routing events into alerts

Once you have a change stream, you’ll evaluate conditions and notify specific channels. Treat alerts like a small rules engine with safeguards.

• Rule inputs: the change payload plus any reference data fetched by ID.
• Deduplication: prevent repeated alerts for the same entity within a window.
• Throttling: cap the rate per rule/channel to prevent floods.
• Enrichment: attach customer names, account tiers, or risk scores for context.
• Routing: map rule severity to destinations (push, email, incident tool).

// Pseudocode for rule evaluation
function handleChange(event) {
  const { entityId, fields } = normalize(event);
  if (isDuplicate(entityId, 'high_value_paid', 24*3600)) return;

  if (fields.status === 'PAID' && fields.total >= 10000) {
    throttle('high_value_paid', 30); // seconds
    notify({
      title: 'High-value order paid',
      body: `Order ${entityId} total $${fields.total}`,
      severity: 'high',
      channels: ['push', 'email']
    });
  }
}

Comparison of push methods

Method	Databases	Latency	Setup complexity	Pros	Cons
LISTEN/NOTIFY + triggers	PostgreSQL	Very low	Low	Simple, minimal infra	Manual triggers, payload size limit
Logical decoding (CDC)	PostgreSQL	Low	Medium	Durable stream, replay	Connector management
Binlog CDC	MySQL	Low	Medium	No schema changes required	Requires binlog config, connector
Change streams	MongoDB	Low	Low	First‑class API, resume tokens	Replica set needed

Security, performance, and reliability tips

• Least privilege: use read‑only roles or replication users; for triggers, keep logic minimal and avoid external calls.
• Backpressure: queue events (e.g., Kafka, SQS) to avoid dropping alerts during spikes.
• Idempotency: dedupe by natural key (order_id + day) to withstand retries.
• Exactly‑once is hard: aim for at‑least‑once delivery with dedupe, and document acceptable duplicates.
• Observability: log rule hits, alert counts, and end‑to‑end latency; export metrics for SLOs.
• Schema changes: implement compatibility mode (e.g., ignore unknown fields) to keep the pipeline resilient.

Deployment options

• Serverless: Route CDC events to a function (AWS Lambda, Cloud Run, Azure Functions) that evaluates rules and sends notifications. Pros: easy scaling and pay‑as‑you‑go.
• Containers: Run Debezium/Maxwell plus a small rule engine service. Pros: full control, portable.
• Managed: Use cloud CDC (e.g., Amazon DMS, Cloud SQL CDC) and a managed queue; connect to your alert processor.

A simple end‑to‑end blueprint

1. Identify events: Which tables/collections and which transitions matter? Example: orders from CREATED → PAID with total ≥ 10k.
2. Choose a change source:
   • Postgres with full control: LISTEN/NOTIFY + trigger.
   • Cross‑service scale or replay needs: CDC connector.
   • MongoDB: change streams.
3. Normalize events: Produce compact JSON with IDs, changed fields, and timestamps.
4. Evaluate rules: Apply predicates, dedupe, and throttling in a stateless consumer.
5. Route alerts: Map severity to push, email, or incident channels; add on‑call context.
6. Measure and harden: Add metrics, retries with DLQs, and canary test alerts.

From polling to push: a migration checklist

• List current polling queries and their cadence.
• Translate each to a precise “change event” (row inserted, field transitioned, threshold crossed).
• Pick the minimal push mechanism that covers your needs.
• Run both in parallel for a week; log differences.
• Close gaps (e.g., missing edge cases, out‑of‑order handling).
• Decommission polling and keep a lightweight fallback for emergencies.

Common pitfalls and how to avoid them

• Alert storms on backfills: When migrating or reprocessing data, tag events with a source and suppress alerts from bulk jobs.
• Trigger sprawl: Centralize trigger functions and naming; emit minimal payloads and rely on fetch‑by‑ID in listeners.
• Connector lag: Monitor CDC lag and provision enough CPU/IO; use partitions and batch sizes tuned to your workload.
• Time‑based rules: For windows like “per day,” use canonical UTC timestamps and a consistent hashing key for dedupe.

FAQ: choosing the right approach

Q: I only need two alerts on one table in Postgres. Should I use CDC?
A: Start with LISTEN/NOTIFY + triggers—simpler and very low latency.

Q: I need durable streams and recovery after downtime.
A: Use CDC (logical decoding for Postgres, binlog for MySQL) and a queue with consumer checkpoints.

Q: I’m on MongoDB Atlas.
A: Use change streams with a $match pipeline to limit events to those relevant to your rules.

Summary

Polling is easy to start and expensive to run. By adopting push‑based patterns—PostgreSQL LISTEN/NOTIFY, logical decoding CDC, MySQL binlog CDC, or MongoDB change streams—you can set up query alerts without polling, cut load, and deliver faster, more reliable notifications. Start with the smallest mechanism that meets your needs, add guardrails like dedupe and throttling, and invest in observability so alerts remain useful as your data changes.

Want a mobile-friendly way to receive push and email notifications from your database changes? The iOS app Query Alert can connect to PostgreSQL, MongoDB, and MySQL with secure, read‑only access and turn your rules into actionable alerts.