Many teams still rely on cron jobs and ad-hoc “polling scripts” to watch production data: a shell script runs every few minutes, queries the database, compares results to the last run, and emails someone if a threshold is crossed. It works—until it doesn’t. As systems grow, real time alerts instead of polling scripts becomes less of a nice-to-have and more of a reliability requirement.
This guide explains why polling breaks down, what “real-time” really means for databases, and practical patterns to build low-noise, secure alerts for SQL and NoSQL systems. You’ll see approaches that range from improved scheduled queries (still polling, but safer and smarter) to event-driven change data capture (CDC) and database-native notifications.
Why polling scripts fail in real production
Polling is deceptively simple: run a query every N minutes and check what changed. The failure modes show up later, usually during an incident.
- Blind spots and lag: if you poll every 5 minutes, your alert is “real-time” only within a 5-minute window. Shorten the interval and you increase load and costs.
- Duplicate or missed alerts: scripts often compare “current count” to “previous count.” Restarts, clock skew, or partial failures cause repeated alerts or missed deltas.
- Operational drift: a script depends on a server, credentials, a runtime, network access, and a maintainer. Over months, it becomes tribal knowledge.
- Unsafe query patterns: poorly indexed queries running frequently can harm the primary database (especially when developers “just add one more check”).
- Noisy notifications: “if count > 0 then alert” turns into a spam cannon without rate limiting, dedupe, and thresholds.
The goal isn’t to ban polling entirely. It’s to adopt patterns that get you closer to real time alerts instead of polling scripts while keeping the monitoring approach production-safe.
What “real-time alerts” means for database monitoring
In practice, “real-time” database alerting falls into three buckets:
- Scheduled queries with state (near-real-time): run read-only checks frequently, but do it safely with watermarks, idempotency, and dedupe.
- Database-native notifications (event-driven): use built-in mechanisms like PostgreSQL
LISTEN/NOTIFYor triggers to emit events. - Change Data Capture (CDC) (streaming): read the database’s change log (e.g., WAL/binlog/oplog) and react to changes with a pipeline.
Which one you choose depends on alert urgency, team size, infrastructure maturity, and how much engineering effort you want to invest.
Comparison: polling scripts vs alerting patterns
| Approach | Latency | Operational complexity | Risk to DB | Best for |
|---|---|---|---|---|
| Ad-hoc polling scripts | Minutes+ | Low initially, high over time | Medium–High | Quick experiments |
| Scheduled read-only queries with watermark | Seconds–minutes | Low–Medium | Low (if indexed) | Threshold alerts, anomaly checks |
| Postgres LISTEN/NOTIFY or triggers | Sub-second–seconds | Medium | Low–Medium | Event alerts on specific writes |
| CDC (Debezium, DMS, etc.) | Near real-time | High | Low (reads logs) | High-scale event processing |
Pattern 1: “Smarter polling” with watermarks (often the best first step)
If you want real time alerts instead of polling scripts without building a streaming stack, start by making scheduled queries robust. The key is a watermark: store “the last seen” value (timestamp, ID, or version) and only look for rows newer than that.
Example: detect new failed logins (PostgreSQL)
Bad (naive polling): counts can flap and you can’t dedupe easily.
-- Every minute
SELECT COUNT(*)
FROM auth_log
WHERE success = false
AND created_at > NOW() - INTERVAL '1 minute';Better (watermark-based): track the newest id or created_at you’ve already alerted on.
-- Use an indexed, monotonic column for watermarking (id or created_at)
SELECT id, user_id, ip, created_at
FROM auth_log
WHERE success = false
AND id > :last_seen_id
ORDER BY id ASC
LIMIT 500;With this approach, your alerting system can:
- Send one notification per batch (e.g., “23 failed logins since last check”)
- Store the new
last_seen_idafter successful processing - Retry safely without double-alerting (idempotency)
Design checklist for safe scheduled queries
- Read-only credentials and least privilege (no writes, no schema changes).
- Index the filter: make sure
(success, id)or(created_at)matches your WHERE clause. - Bounded results: use
LIMITand alert on “too many events” rather than fetching millions. - Time-box queries: set statement timeouts if supported.
- Dedupe and rate limit: avoid waking up a team for every single row.
This is still “polling,” but it’s dramatically more reliable than free-form scripts—and it can achieve near-real-time behavior with short intervals and low database impact.
Pattern 2: Event-driven alerts with PostgreSQL LISTEN/NOTIFY
PostgreSQL supports NOTIFY messages that clients can subscribe to with LISTEN. This enables a push-style flow: a write occurs, a notification is emitted, and your alert processor reacts immediately.
Use LISTEN/NOTIFY for lightweight event hints—then query the database for details. Don’t pack your whole payload into the notification.
Example: notify on “payment failed” inserts
1) Create a trigger function:
CREATE OR REPLACE FUNCTION notify_payment_failed()
RETURNS trigger AS $$
BEGIN
IF NEW.status = 'failed' THEN
PERFORM pg_notify('payments_failed', NEW.id::text);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;2) Attach it to the table:
CREATE TRIGGER payments_failed_notify
AFTER INSERT OR UPDATE ON payments
FOR EACH ROW
EXECUTE FUNCTION notify_payment_failed();3) Your worker subscribes:
LISTEN payments_failed;When it receives an ID, it can fetch the record with a read-only query and send a notification. The advantage over polling is immediate awareness and reduced repeated scanning.
Tradeoffs: triggers add logic to the write path, and you need to ensure the notification consumer is reliable. Many teams combine this with a periodic “backstop” query (watermark-based) to catch anything missed during downtime.
Pattern 3: CDC for true “streaming” alerts (binlog/WAL/oplog)
CDC tools read database change logs and turn row-level changes into an event stream. You can then implement alert rules as stream processors.
- PostgreSQL: logical replication / WAL decoding
- MySQL: binlog-based CDC
- MongoDB: change streams (backed by the oplog)
When CDC is worth it:
- You need alerts within seconds at high write volume
- You want auditability and replay (reprocess events from a checkpoint)
- You’re already running Kafka/Kinesis/PubSub and stream processors
When it’s overkill:
- You only need a handful of threshold-based alerts
- The organization isn’t ready to operate streaming infrastructure
Reducing noise: alert rules that don’t annoy people
Replacing scripts is only half the story. The other half is making alerts actionable. A reliable system should include:
1) Clear severity and routing
- Info: “daily summary,” no paging
- Warn: “metric trending badly,” during business hours
- Critical: “customer impact likely,” immediate push/page
2) Hysteresis and windows
Instead of “alert when value > threshold,” use “alert when value > threshold for 3 consecutive checks” or “error rate > 2% over 10 minutes.” This prevents flapping.
3) Dedupe keys
For event-based alerts, dedupe by a stable identifier (e.g., order_id, payment_id). For aggregated alerts, dedupe by rule + time window.
Security baseline for database alerting
Any monitoring system that touches production data should be designed like a production service:
- Encrypted connections (TLS) and certificate validation where possible.
- Read-only access and separate credentials from application services.
- Query allowlists (or at least strict review) for any recurring check.
- Secrets management (no passwords in scripts or repos).
- Audit trails: log what checks ran, when, and how long they took.
These practices matter even more when you move toward near-real-time checks, because frequency amplifies both load and risk.
SQL and NoSQL examples of “alertable” conditions
Here are conditions that map well to real-time or near-real-time alerts without heavy infrastructure:
- Threshold breaches: “pending jobs > 10,000” or “disk usage > 80%”
- New critical rows: failed payments, chargebacks, account lockouts
- Stuck workflows: orders in “processing” > 30 minutes
- Data quality checks: null rates, uniqueness violations, out-of-range values
- Security signals: repeated failed logins, access from unusual IP ranges
MongoDB example: new high-severity incidents
If you don’t want to wire change streams yet, a watermark query can still work well if you have a monotonic field:
// Pseudocode
find({ severity: "high", _id: { $gt: lastSeenId } })
.sort({ _id: 1 })
.limit(500)For more “real-time,” MongoDB change streams can push events, but they require careful handling of resume tokens and connectivity.
How to choose: a practical decision flow
- If you need alerts within minutes and want minimal complexity: use scheduled read-only queries with watermarks, indexes, and dedupe.
- If you need alerts within seconds on a small set of write events: use Postgres LISTEN/NOTIFY or narrowly scoped triggers.
- If you need high-scale, replayable alerts across many tables/collections: adopt CDC and treat alerts as stream processing.
Most teams start with “smarter polling,” then evolve the highest-value alerts to event-driven patterns.
Conclusion: moving toward real-time without rebuilding everything
Switching to real time alerts instead of polling scripts is less about chasing zero latency and more about building a monitoring system that is reliable, low-noise, and safe for production. Start by replacing brittle scripts with watermark-based checks, add dedupe and alert windows, and only move to triggers or CDC when you truly need event-driven behavior.
If you prefer running these checks from a mobile-friendly workflow, some teams use an iOS app like Query Alert to turn read-only queries into scheduled smart notifications with encrypted connections.