Database monitoring often fails in one of two ways: it’s either too noisy to be useful, or it’s too risky to be allowed. The most sustainable approach is to design monitoring that is both actionable and safe—and that usually starts with read only queries. When your alerts are driven by queries that cannot mutate data, you reduce blast radius, simplify security reviews, and make it easier to grant access across teams.
This article explains how to implement monitoring and alerting based on read only queries for SQL and NoSQL databases, how to provision least-privilege access, and how to avoid common query patterns that accidentally degrade performance or leak sensitive information.
Why read only queries are the safest foundation for alerts
Monitoring systems frequently need database access, but “access” shouldn’t mean “ability to change production data.” Read only queries support a least-privilege model: your monitoring can observe state, compute KPIs, and detect anomalies without ever being able to write, delete, or modify anything.
Benefits you get immediately:
- Reduced risk: no accidental updates, deletes, or schema changes from automation.
- Clear audit story: security teams can approve access faster when it’s read-only.
- Safer operational behavior: you can rotate credentials and scope roles without breaking app write paths.
- More portable alerting: the same “query-driven checks” approach works across PostgreSQL, MySQL, and MongoDB.
What “read-only” really means (and what it doesn’t)
“Read-only” is not just “a SELECT statement.” In practice, read only queries are defined by permissions (what the user can do) and behavior (what the query causes the database to do).
Permission-level read-only
This is the most important part: the monitoring credential should not have privileges to INSERT, UPDATE, DELETE, CREATE, ALTER, or run admin functions. In many environments, you’ll also restrict reading of certain schemas/collections and block access to secrets tables.
Behavior-level read-only
A query can be “read-only” yet still be disruptive. Examples include long-running scans on massive tables, locking reads that block writes, or analytics queries that saturate I/O. Your alerting queries should be designed to be predictable, bounded, and indexed.
Least-privilege roles: a practical blueprint
The most reliable pattern is to create a dedicated “monitoring” principal per environment (prod/staging) with narrowly scoped permissions. The exact commands differ by database, but the principles are consistent.
| Database | Recommended monitoring privileges | Common privileges to avoid |
|---|---|---|
| PostgreSQL | CONNECT on DB; USAGE on schema; SELECT on specific tables/views; SELECT on vetted views for sensitive data | SUPERUSER, CREATEROLE, CREATE on schema, membership in elevated roles, access to pg_read_all_data unless necessary |
| MySQL | SELECT on specific databases/tables; optional SHOW VIEW if needed | GRANT OPTION, SUPER, FILE, CREATE, DROP, ALTER |
| MongoDB | Built-in read role on specific DBs/collections; use views/aggregation pipelines carefully |
readWrite, admin roles, user management, cluster-wide privileges |
Tip: Prefer granting access to views (SQL) or materialized summaries where appropriate, rather than raw tables that may contain PII. This keeps alert queries stable even as schemas evolve, and it minimizes accidental data exposure.
Designing alert queries that stay fast and low-noise
Alerts driven by queries should answer one question: “Is something worth attention right now?” The best read only queries are therefore:
- Small: return one row, one number, or a short list.
- Indexed: filter by indexed columns and time windows.
- Bounded: cap results and avoid full scans.
- Stable: depend on explicit columns, not
SELECT *.
Pattern 1: Threshold checks (simple and robust)
Good for: backlog size, error counts, payment failures, queue depth.
-- PostgreSQL / MySQL example: failed payments in the last 15 minutes
SELECT COUNT(*) AS failed_payments
FROM payments
WHERE status = 'failed'
AND created_at >= NOW() - INTERVAL '15 minutes';Alert rule example: notify if failed_payments > 10. This is easy to explain, easy to tune, and typically fast with an index on (status, created_at).
Pattern 2: Change detection without triggers
Good for: detecting new high-severity incidents, new VIP signups, new suspicious events. Use a “latest seen” timestamp or ID and compare it to the current state.
-- PostgreSQL example: newest critical incident time
SELECT MAX(created_at) AS latest_critical_incident
FROM incidents
WHERE severity = 'critical';You can store the last observed value outside the database (in your alerting system) and notify when it advances. This avoids triggers, avoids write access, and keeps the database logic simple.
Pattern 3: Ratio and error-budget style queries
Good for: monitoring quality without alert fatigue. Ratios often reflect user impact more accurately than raw counts.
-- MySQL example: error rate over last 10 minutes
SELECT
SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) / COUNT(*) AS error_rate
FROM api_requests
WHERE created_at >= NOW() - INTERVAL 10 MINUTE;Alert rule example: notify if error_rate > 0.02 for two consecutive runs (simple deduping that reduces flapping).
Read-only monitoring in MongoDB: aggregation done safely
MongoDB monitoring often relies on aggregation pipelines. These are read-only by nature, but still need to be bounded and index-friendly.
// MongoDB: count suspicious logins in the last 5 minutes
db.logins.aggregate([
{ $match: { riskScore: { $gte: 90 }, createdAt: { $gte: new Date(Date.now() - 5*60*1000) } } },
{ $count: "suspiciousLogins" }
]);Make sure createdAt and any high-selectivity fields (like riskScore or userId) are indexed; otherwise, even a “read-only” pipeline can become an expensive scan.
Performance and safety pitfalls (even with read only queries)
Read only queries reduce data risk, but you still need to avoid operational risk. These issues are common in real systems:
- Unbounded time windows: querying “all time” metrics every minute will eventually become too slow.
- Missing indexes: alert queries should be treated like production endpoints—profile them and add indexes.
- Locking reads: avoid high isolation levels or query hints that can block writers.
- Cardinality explosions: avoid grouping on high-cardinality keys (e.g., session IDs) unless necessary.
- Fetching too much data: alerts should not page through thousands of rows; compute summaries.
Rule of thumb: an alert query should complete in a small, predictable time budget and return minimal data—ideally a single scalar value.
Making read-only alerts actionable (not just informational)
Even perfectly safe read only queries can still create noisy alerts if they aren’t tied to decisions. To keep alerts actionable:
- Define the operator action: what should someone do when it fires?
- Add context fields: return a short “top offenders” list (limited) to speed triage.
- Use multi-window logic: require the condition to hold for 2–3 checks to avoid transient spikes.
- Segment severity: warning vs critical thresholds reduce on-call fatigue.
Example: adding context safely
Instead of dumping raw rows, return a small, bounded set of identifiers.
-- PostgreSQL example: top 5 endpoints causing 500s in last 10 minutes
SELECT endpoint, COUNT(*) AS errors
FROM api_requests
WHERE status_code >= 500
AND created_at >= NOW() - INTERVAL '10 minutes'
GROUP BY endpoint
ORDER BY errors DESC
LIMIT 5;This stays read-only, stays bounded, and turns “something is wrong” into “these endpoints are likely involved.”
Security checklist for query-based monitoring
- Separate credentials per environment (prod vs staging) and rotate regularly.
- Grant SELECT only on required objects; prefer views over base tables for sensitive domains.
- Encrypt in transit (TLS) and validate certificates when possible.
- Store secrets securely (keychain, secret manager); never hardcode in apps or scripts.
- Log and audit monitoring access: which principal ran which query and when.
- Rate-limit and schedule checks to avoid thundering herds (especially across many databases).
Choosing intervals and schedules: when “real-time” isn’t necessary
A common mistake is checking everything every minute. Use intervals that match the business impact:
- 1–2 minutes: critical incidents, payment failures, infrastructure errors.
- 5–15 minutes: backlog growth, anomalous signups, suspicious activity trends.
- Hourly/daily: SLA summaries, reporting gaps, slow drift metrics.
For expensive queries, consider pre-aggregations (summary tables updated by your application or ETL), then alert from those summaries using read only queries. This keeps the monitoring user safely read-only while shifting compute to controlled jobs.
Putting it all together
When you build monitoring around read only queries, you gain a security posture that scales: database owners can approve access with confidence, developers can iterate on alert logic without fear of mutating data, and operators can trust that alerting won’t become a hidden source of production load.
If you want to run these query checks from a mobile workflow (with push/email notifications) while keeping the same least-privilege model, tools like Query Alert follow this query-to-alert approach—just make sure your queries remain bounded, indexed, and truly read-only from a permissions standpoint.