Database monitoring often fails for one of two reasons: it’s too noisy (endless notifications, no signal) or too risky (monitoring tools that need broad permissions). Read only monitoring is a practical middle path: you observe what matters with SELECT-only (or equivalent) access, then turn those observations into actionable alerts—without triggers, write privileges, or invasive agents.
This approach fits modern teams where production data is sensitive, environments are segmented, and audits matter. Whether you’re watching order failures, detecting suspicious logins, tracking queue backlogs, or measuring freshness of ingestion jobs, read-only monitoring lets you build dependable alerting while honoring least privilege.
What “read only monitoring” means (and what it doesn’t)
Read only monitoring is the practice of connecting to a database using credentials that can only read the minimum data needed to evaluate health or business rules. The monitoring system runs predefined queries on a schedule (or on-demand) and triggers notifications when results cross a threshold or change state.
- It is: least-privilege access, periodic checks, and alert logic based on query results.
- It isn’t: installing agents that can alter system settings, adding triggers, or granting broad admin roles.
- It can include: read access to system views/metrics (where supported) or a dedicated read-only replica.
Why read-only beats “quick” monitoring hacks in production
Many teams start with ad-hoc scripts, cron jobs, or trigger-based alerting. Those work—until they don’t. Read-only monitoring is attractive because it’s safer to approve, simpler to audit, and easier to operate across different database engines.
| Approach | Pros | Cons / Risk | Best for |
|---|---|---|---|
| DB triggers + notification table | Near-real-time, precise | Writes in production; can break deploys; hard to rollback; adds coupling | Highly controlled schemas with strong DBA support |
| Application logs/metrics only | No DB access needed | May miss data-quality issues; relies on app code paths | Service health, throughput, infra monitoring |
| Polling scripts with broad DB roles | Fast to implement | Permission creep; secret sprawl; brittle ops | Short-lived experiments |
| Read only monitoring (scheduled queries) | Least privilege; audit-friendly; portable across SQL/NoSQL | Not instant; needs dedupe/state handling | Production-safe alerting and business-rule monitoring |
Designing a safe read-only monitoring role
The biggest win comes from permissions discipline. A read-only monitoring role should be:
- Read-only: no INSERT/UPDATE/DELETE, no DDL, no ownership changes.
- Scoped: access only to the specific tables/views needed for alerts.
- Time-bounded and rotated: credentials rotated regularly; use short-lived tokens where possible.
- Observable: query logs enabled (within your privacy constraints) so you can audit what the monitor is reading.
PostgreSQL example: minimal privileges for a monitoring role
Below is a typical pattern: create a role, grant connect, grant usage on schema, then grant SELECT on only what you need.
-- Create a dedicated role/user
CREATE ROLE monitor_ro NOINHERIT;
CREATE USER monitor_app WITH PASSWORD 'use-a-secret-manager';
GRANT monitor_ro TO monitor_app;
-- Allow connection
GRANT CONNECT ON DATABASE prod TO monitor_ro;
-- Allow schema usage
GRANT USAGE ON SCHEMA public TO monitor_ro;
-- Grant SELECT on specific tables/views
GRANT SELECT ON TABLE orders, payments, auth_events TO monitor_ro;
-- Optional: future-proof only if you really want it
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO monitor_ro;Tip: For sensitive columns (e.g., emails, tokens), prefer views that expose only what monitoring needs (counts, timestamps, statuses). That keeps read-only truly minimal.
MongoDB example: read-only access at the database level
In MongoDB, prefer built-in roles like read scoped to a single database, or use custom roles that limit collection access if needed.
// In the admin database (example)
use admin
db.createUser({
user: "monitor_ro",
pwd: "use-a-secret-manager",
roles: [ { role: "read", db: "production" } ]
})Alert queries: turn business rules into low-noise signals
Good monitoring queries share three traits:
- Stable: resistant to small data fluctuations.
- Actionable: points to an owner and a next step.
- Cheap: uses indexes and bounded time windows.
Pattern 1: threshold alerts (backlog, failures, stale data)
Threshold alerts are the simplest and often the most effective.
Example (Postgres): alert if payments failing in the last 15 minutes exceed 20
SELECT COUNT(*) AS failed_payments
FROM payments
WHERE status = 'failed'
AND created_at >= NOW() - INTERVAL '15 minutes';Alert rule: notify when failed_payments > 20. To reduce noise, add a second condition such as “for 2 consecutive runs” or compare against a baseline (see Pattern 3).
Pattern 2: state-change alerts (new incidents, recovered incidents)
State-change monitoring is where scheduled queries shine. The query returns a set of “open incidents” (rows/documents that match a condition). Your alerting layer compares the current result set to the previous run to detect new matches (incident started) and missing matches (incident resolved).
Example (MySQL): orders stuck in processing for more than 10 minutes
SELECT id, user_id, created_at
FROM orders
WHERE status = 'processing'
AND created_at < NOW() - INTERVAL 10 MINUTE
ORDER BY created_at ASC
LIMIT 200;Operationally, you alert on new stuck order IDs and optionally send a “recovered” message when the set returns to empty.
Pattern 3: anomaly-style alerts (deviation from baseline)
When raw thresholds don’t work (traffic varies by hour/day), compare current counts to a baseline. You can compute a simple baseline directly in SQL without ML infrastructure.
Example (Postgres): login failures are 3× higher than the same time yesterday
WITH current AS (
SELECT COUNT(*) AS c
FROM auth_events
WHERE event = 'login_failed'
AND created_at >= NOW() - INTERVAL '10 minutes'
), yesterday AS (
SELECT COUNT(*) AS y
FROM auth_events
WHERE event = 'login_failed'
AND created_at >= (NOW() - INTERVAL '1 day' - INTERVAL '10 minutes')
AND created_at < (NOW() - INTERVAL '1 day')
)
SELECT c, y,
CASE WHEN y = 0 THEN NULL ELSE (c::numeric / y) END AS ratio
FROM current, yesterday;Alert rule: notify when ratio >= 3 and c >= 20 (a minimum count gate prevents division-by-small-number noise).
Scheduling and frequency: how often should checks run?
The best interval is driven by impact and cost:
- Critical incidents (payment failures, auth spikes): every 1–5 minutes.
- Operational health (queue depth, ingestion freshness): every 5–15 minutes.
- Governance (permission drift, data retention checks): hourly or daily.
Remember: faster checks can increase load. Keep queries indexed, time-bounded, and returning small result sets (counts, IDs, timestamps). For high-traffic systems, run read-only monitoring against a read replica when available.
Noise control: dedupe, severity, and “tell me only what changed”
Most alert fatigue comes from repeating the same message. With read only monitoring, you can aggressively reduce noise using these tactics:
- Deduplicate by key: alert once per incident ID (e.g., order ID, job name, host).
- Require persistence: only alert if a condition is true for N consecutive runs.
- Use severity tiers: warning at a lower threshold; critical at a higher one.
- Send recovery notifications: close the loop when the condition clears.
Rule of thumb: If an alert doesn’t suggest a next action (who should do what), it’s not an alert—it’s a metric.
Security checklist for production-safe read-only monitoring
Read-only privileges are necessary, but not sufficient. A secure setup also includes:
- Encrypted connections (TLS) to prevent credential/data exposure in transit.
- Secret management (no hard-coded passwords in scripts or CI logs).
- Network scoping: allowlist monitoring clients; use VPN or private networking.
- Row/column minimization: query aggregates and IDs, not PII.
- Query governance: treat alert queries like code—reviewed, versioned, and tested.
Practical examples for SQL and NoSQL monitoring queries
Freshness check: “is the pipeline still writing?” (works well with read-only)
Example (Postgres): alert if no events in the last 10 minutes
SELECT NOW() - MAX(created_at) AS lag
FROM events;Alert rule: notify when lag > INTERVAL '10 minutes'. This is lightweight, indexed by time, and avoids scanning.
MongoDB backlog check with aggregation
Example (MongoDB): count pending jobs older than 5 minutes
db.jobs.aggregate([
{ $match: { status: "pending", createdAt: { $lt: new Date(Date.now() - 5*60*1000) } } },
{ $count: "pending_old" }
])Alert rule: notify when pending_old crosses a threshold that reflects real operational risk (e.g., > 500).
Operationalizing read-only monitoring across multiple databases
Many teams monitor more than one datastore: a transactional Postgres, an analytics warehouse, and a MongoDB service. To keep read only monitoring manageable:
- Standardize alert outputs: each query returns either a single number, or a small set of keys (IDs) plus context.
- Name alerts consistently: include system + symptom (e.g., billing.payment_failures_spike).
- Document runbooks: attach “how to investigate” steps near each alert.
- Separate credentials per system: one compromise shouldn’t unlock everything.
Common pitfalls (and how to avoid them)
- Unbounded queries: avoid full-table scans; always bound by time or limit rows.
- Monitoring PII: alerts should usually contain counts and identifiers, not personal data.
- Relying on exact counts: distributed systems can be bursty; use gates and persistence windows.
- No recovery signal: add “resolved” notifications or dashboards so teams trust the system.
Putting it together: a simple implementation blueprint
If you’re starting from scratch, this sequence works well:
- Pick 5 high-impact conditions (failures, backlog, freshness, auth anomalies, stuck states).
- Create a dedicated read-only role per database, scoped to needed tables/views.
- Write bounded queries that return small results (counts or IDs).
- Decide intervals and add dedupe + persistence rules.
- Ship notifications to the channel your team actually uses (email/push/chat), and add runbooks.
Once the basics work, iterate on quality: tune thresholds, add baselines, and remove alerts that aren’t actionable.
Note: If you prefer managing these checks from a mobile workflow while keeping credentials encrypted and queries read-only, tools like Query Alert can help centralize scheduled query alerts without turning monitoring into a custom engineering project.