Production databases already have dashboards, logs, and on-call runbooks. What they often don’t have is a fast, low-friction way for a developer or analyst to turn a single SQL query into a reliable alert that works from a phone. That’s where an iOS tool for monitoring Postgres queries becomes practical: you define a query, define what “bad” looks like, and get notified when reality crosses that line.
This guide focuses on query-driven monitoring (not full APM). You’ll learn patterns that make Postgres query alerts accurate, secure, and low-noise—plus example SQL you can adapt to your schema.
When query-based alerts beat dashboards
Dashboards are great for exploration, but alerts need crisp conditions. Query-based monitoring is ideal when:
- The signal is business-specific (e.g., “payment retries > 20 in 10 minutes”).
- You need coverage for edge cases that generic metrics don’t expose.
- You want a lightweight setup without agents, exporters, or a full observability stack.
- You already trust SQL as the source of truth for operational state.
Rule of thumb: if you can express the condition in one deterministic SQL query, you can usually alert on it reliably—provided you handle scheduling, thresholds, and deduplication.
Three approaches to Postgres alert signals (and when to use each)
Not all Postgres “alerts” are equal. Some should be event-driven (triggers/CDC), others can be polled. Here’s a practical comparison.
| Approach | How it works | Best for | Trade-offs |
|---|---|---|---|
| Polling with read-only SQL | Run a query on a schedule; evaluate result | Counts, thresholds, missing rows, backlogs | Not truly real-time; needs noise control |
| System views (e.g., pg_stat_*) | Query Postgres internal stats | Lock waits, slow queries, replication lag | Requires correct extensions/permissions; stats reset |
| Triggers / CDC / LISTEN-NOTIFY | Database emits events on changes | Row-level change alerts, near real-time pipelines | More moving parts; write-path overhead; harder to secure |
For an iOS tool for monitoring Postgres queries, polling read-only SQL is the most common starting point: it’s predictable, safe, and works across hosted Postgres providers.
Core design: turn a query into an alert condition
A robust alert is more than “query returns something.” Define four pieces:
- Query: returns a single scalar (or a small row) you can evaluate.
- Predicate: e.g., value > 0, value > threshold, value changed since last check.
- Cadence: how often to run it, aligned to the phenomenon’s timescale.
- Deduplication: how to avoid repeating the same alert over and over.
Prefer scalar outputs
Alerts are easiest to reason about when the query returns one number. Instead of returning 200 rows of “failed jobs,” return a count of failed jobs in a window.
-- Count failures in last 10 minutes
SELECT COUNT(*) AS failures
FROM job_runs
WHERE status = 'failed'
AND finished_at >= NOW() - INTERVAL '10 minutes';Then alert on failures >= 1 (or a higher threshold if failures are expected).
Noise reduction: the difference between useful and ignored alerts
The biggest failure mode of query alerts is “it worked once, then everyone muted it.” Use these techniques to keep alerts meaningful:
1) Use time windows, not lifetime totals
Lifetime totals only increase; they don’t indicate urgency. Use windows (10 minutes, 1 hour, 1 day) and align them with your check interval.
2) Add hysteresis or consecutive checks
Instead of alerting on a single spike, alert when a condition persists for N checks. If your tool supports it, require “2 out of 3” failures before notifying.
3) Track state (delta alerts)
Some alerts should fire only when the value changes meaningfully. You can implement delta logic by storing the previous value (client-side), or by querying “new since last run” using a timestamp watermark.
-- New stuck tasks since the last 15 minutes
SELECT COUNT(*) AS newly_stuck
FROM tasks
WHERE status = 'processing'
AND updated_at < NOW() - INTERVAL '15 minutes'
AND updated_at >= NOW() - INTERVAL '30 minutes';4) Clamp with percentiles, not averages (when possible)
If you’re alerting on latency, averages hide outliers. If you have a latency column, consider percentile-based queries.
-- p95 latency over last 15 minutes
SELECT
percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95_ms
FROM api_calls
WHERE created_at >= NOW() - INTERVAL '15 minutes';Security essentials for mobile query monitoring
Mobile monitoring can be safe, but only if you treat it like production access. Follow these principles:
- Use a dedicated read-only role with the least privileges required.
- Restrict to a single database and schema where practical.
- Enforce TLS and verify certificates when possible.
- Prefer allowlisted IPs/VPN for database access; if not feasible, use provider-native secure endpoints.
- Avoid selecting sensitive columns (emails, tokens, PII). Aggregate instead.
Example: minimal read-only role (Postgres)
-- Create a dedicated monitoring user
CREATE ROLE monitor_ro LOGIN PASSWORD 'use-a-strong-secret';
-- Only connect to the target DB
GRANT CONNECT ON DATABASE app_db TO monitor_ro;
\c app_db
-- Read-only access to a specific schema
GRANT USAGE ON SCHEMA public TO monitor_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO monitor_ro;
-- Ensure future tables remain readable
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO monitor_ro;If your alerts need only a few tables, grant SELECT on those tables only. For views, grant SELECT on the view and ensure underlying permissions are correct.
High-value Postgres query alerts you can implement quickly
Below are patterns that translate well into scheduled checks on a phone, and they’re generally stable across Postgres versions.
Backlog growth (queues, outbox tables, async jobs)
-- Pending jobs older than 5 minutes
SELECT COUNT(*) AS pending_old
FROM jobs
WHERE status = 'pending'
AND created_at < NOW() - INTERVAL '5 minutes';Alert idea: notify when pending_old exceeds a threshold, or grows steadily across checks.
Missing “heartbeat” rows (silent failures)
-- Has the scheduler checked in recently?
SELECT CASE
WHEN MAX(checked_in_at) >= NOW() - INTERVAL '3 minutes' THEN 0
ELSE 1
END AS scheduler_down
FROM scheduler_heartbeats;Alert idea: notify when scheduler_down = 1 for two consecutive checks.
Error rate (events table or request logs)
-- Error rate in last 10 minutes
SELECT
ROUND(100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2)
AS error_rate_pct
FROM http_requests
WHERE created_at >= NOW() - INTERVAL '10 minutes';Alert idea: error rate > 2% AND total requests > some minimum volume to avoid low-traffic noise.
Lock contention (lightweight check)
-- Number of sessions waiting on a lock
SELECT COUNT(*) AS lock_waiters
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';Alert idea: lock_waiters > 0 for multiple checks, or lock_waiters >= 5 immediately.
Slow query regression using pg_stat_statements (if enabled)
If you have pg_stat_statements, you can alert on a sudden jump in mean execution time for key queries. Keep the query simple and focus on known statements.
-- Find top mean-time statements in the last stats window
SELECT
queryid,
ROUND(mean_exec_time::numeric, 2) AS mean_ms,
calls
FROM pg_stat_statements
WHERE calls >= 50
ORDER BY mean_exec_time DESC
LIMIT 10;Alert idea: alert when a tracked query’s mean_ms exceeds its baseline by X% for N checks. (Baselines are often managed outside Postgres.)
Scheduling: choosing an interval that matches the risk
Alert cadence should reflect both urgency and variance:
- 1–2 minutes: outages, stuck queues, payment failures (high urgency).
- 5–15 minutes: error rate shifts, backlog trends, replication lag checks.
- Hourly/daily: data quality (missing records, drift, anomalies).
Running too frequently increases cost and noise. Running too slowly turns alerts into postmortems. If you’re unsure, start at 5 minutes and adjust based on incident experience.
Implementation checklist for a reliable iOS monitoring setup
- Make queries deterministic: avoid non-deterministic ordering and large result sets.
- Bound the workload: add time windows and indexed predicates.
- Return small results: scalar values, or a single row.
- Define clear thresholds: absolute (count) or relative (rate/percent change).
- Prevent repeats: dedupe by “only alert on transition to bad” or “cooldown period.”
- Secure access: read-only role, TLS, and minimal privileges.
- Document the intent: include a short description of what the alert means and what to check first.
Common pitfalls (and how to avoid them)
Alerting on unindexed scans
If your query scans millions of rows every minute, you’ll create load and maybe trigger the very incident you’re monitoring. Add indexes that match your filter (e.g., (status, created_at)) and keep windows tight.
Alerting on “expected” errors
Some errors are normal (retries, timeouts, cancellations). Alert on rates, spikes, or sustained degradation rather than raw counts.
Using production credentials
Never reuse application write credentials. Use a dedicated read-only account and scope it tightly.
Putting it together: a simple alert spec template
If you’re documenting alerts for your team, a consistent template reduces confusion during incidents:
- Name: “Pending jobs older than 5 minutes”
- SQL: (store query)
- Condition: pending_old > 50
- Interval: every 5 minutes
- Deduping: notify on transition to bad; cooldown 30 minutes
- First checks: worker health, queue depth, recent deploys
Conclusion
A well-designed iOS tool for monitoring Postgres queries doesn’t replace your observability stack—it fills the gap between “I can write the SQL” and “I want to know immediately when this condition happens.” By focusing on scalar queries, bounded windows, clear thresholds, and strong read-only security, you can create alerts that are actionable instead of noisy.
If you’re looking for an iOS app that implements this query-to-alert workflow with secure, read-only connections and mobile notifications, Query Alert is one option to evaluate.