Traditional database monitoring is often metric-first: CPU, memory, I/O, connections, and dashboards. Those are essential, but they can miss what teams actually care about day to day: data conditions that indicate customer impact or operational risk. Query-based monitoring flips the approach by turning SQL checks into alerts—so you get notified when a meaningful state appears in PostgreSQL, not just when a graph looks “high.”
This guide shows how to monitor PostgreSQL with query alerts using read-only SQL, practical thresholds, and techniques to reduce noise. The examples work whether you run checks from a server-side scheduler, CI job, cron, or a mobile-friendly alerting workflow.
What “query alerts” mean for PostgreSQL monitoring
A query alert is a small, targeted SQL statement that returns a value (or rows) indicating risk. You run it on a schedule and alert when the result crosses a condition (e.g., “count > 0” or “value > 10 minutes”).
- Metric monitoring answers: “Is the database under stress?”
- Query alerting answers: “Is something happening that requires action?”
Rule of thumb: If you can describe an incident in one sentence, you can usually express the detection as a SQL query.
Design principles for low-noise PostgreSQL alerts
Most alert fatigue comes from alerts that aren’t actionable. Before writing SQL, decide what “actionable” means for your team.
1) Prefer symptom-based checks over raw utilization
For example, rather than “connections > 80%,” alert on “connections are saturating and queries are queuing.” This aligns with user impact.
2) Define severity and a response path
Each alert should map to a runbook step. If you don’t know what you’d do when it fires, refine it.
3) Add timing controls
- Debounce: require the condition to be true for N consecutive runs.
- Cooldown: once fired, suppress repeats for a period.
- Auto-resolve: notify recovery when the condition clears.
4) Use least privilege and read-only queries
Create a dedicated role that can read system catalogs and the specific schemas needed. Avoid superuser access for alert checks.
Core alert categories (and why they matter)
Start with four categories that catch many real incidents:
- Availability symptoms: long-running queries, lock contention, connection saturation.
- Replication health: lag, stalled replay, failing slots.
- Data freshness: pipelines not updating, jobs failing, stale tables.
- Capacity growth: table/index bloat signals, disk trend, unexpected growth.
A practical “starter set” of PostgreSQL query alerts
The SQL below is intended to be read-only. Adjust thresholds to your workload and SLOs.
Alert 1: Long-running queries (customer-impacting latency)
SELECT
pid,
now() - query_start AS runtime,
usename,
datname,
state,
wait_event_type,
wait_event,
left(query, 200) AS sample
FROM pg_stat_activity
WHERE state <> 'idle'
AND query_start IS NOT NULL
AND now() - query_start > interval '5 minutes'
ORDER BY runtime DESC;Alert condition: row count > 0 (or runtime > 15 minutes for warning/critical split).
Alert 2: Lock contention (blocked queries)
SELECT
blocked.pid AS blocked_pid,
now() - blocked.query_start AS blocked_for,
blocking.pid AS blocking_pid,
left(blocked.query, 120) AS blocked_query,
left(blocking.query, 120) AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked
ON blocked.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid <> blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking
ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
AND now() - blocked.query_start > interval '1 minute'
ORDER BY blocked_for DESC;Alert condition: any blocked query > 60s; escalate if > 5m or many blocked PIDs.
Alert 3: Connection pressure (approaching max_connections)
SELECT
(SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
count(*) FILTER (WHERE state <> 'idle') AS active,
count(*) AS total,
round(100.0 * count(*) / (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 1) AS pct_used
FROM pg_stat_activity;Alert condition: pct_used > 85% for 2–3 consecutive checks, or active spikes beyond a known baseline.
Alert 4: Replication lag (streaming replicas behind)
This one differs for primary vs replica. On the primary, check connected replicas:
SELECT
application_name,
client_addr,
state,
write_lag,
flush_lag,
replay_lag
FROM pg_stat_replication
WHERE state <> 'streaming'
OR replay_lag > interval '30 seconds'
OR flush_lag > interval '30 seconds'
OR write_lag > interval '30 seconds';Alert condition: non-streaming replicas, or lag above your RPO/RTO needs.
Alert 5: Stale “data freshness” table (pipelines not updating)
For operational tables that should be updated frequently, monitor max timestamp:
SELECT
now() - max(updated_at) AS staleness
FROM analytics.daily_rollups;Alert condition: staleness > expected interval + buffer (e.g., > 2 hours).
Alert 6: Failed scheduled jobs (application-level failures)
If you store job status in Postgres, query for failures:
SELECT
job_name,
count(*) AS failures_last_hour
FROM ops.job_runs
WHERE status = 'failed'
AND started_at > now() - interval '1 hour'
GROUP BY job_name
HAVING count(*) >= 3
ORDER BY failures_last_hour DESC;Alert condition: failures exceed a threshold, or any failure for “must-succeed” jobs.
Alert 7: Bloat indicator (dead tuples growing)
Full bloat estimation can be complex; a pragmatic early signal is dead tuples and autovacuum activity.
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
last_autovacuum,
last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000
AND (100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0)) > 20
ORDER BY dead_pct DESC, n_dead_tup DESC;Alert condition: dead_pct > 20% and n_dead_tup above a meaningful size for your tables.
Recommended thresholds (starting points)
Thresholds depend on workload, but the table below provides conservative defaults to iterate from.
| Risk | Query signal | Suggested alert condition | Typical action |
|---|---|---|---|
| Slow incident unfolding | Long-running queries | > 5m (warn), > 15m (critical) | Inspect query, indexes, locks, cancel if safe |
| Request pile-up | Blocked sessions | Any blocked > 60s | Find blocker, resolve contention, review transactions |
| Connection outage risk | pct of max_connections | > 85% sustained | Check pooler, leaks, spikes, scale if needed |
| Stale reads / DR risk | Replication lag | > 30s (or your RPO) | Investigate I/O, network, replay, slots |
| Pipeline silently broken | Staleness of key tables | > expected + buffer | Restart jobs, fix upstream source, backfill |
How to schedule checks without hammering production
Query alerts should be lightweight. A few guidelines keep monitoring safe:
- Prefer system views like
pg_stat_activity,pg_stat_replication, andpg_stat_user_tables. - Use selective filters (time windows, thresholds) so you don’t return huge result sets.
- Stagger intervals: run lock/latency checks every 1–2 minutes, bloat checks every few hours, freshness checks every 5–15 minutes.
- Set statement timeouts for the monitoring role to prevent accidental heavy queries.
Example: setting timeouts for a monitoring role
ALTER ROLE monitor_user SET statement_timeout = '5s';
ALTER ROLE monitor_user SET lock_timeout = '1s';
ALTER ROLE monitor_user SET idle_in_transaction_session_timeout = '10s';Security checklist for query-based monitoring
Because alerts often run from outside your VPC (or from a developer device), treat connectivity as part of the design.
- Read-only role: grant only what’s required (views + specific schemas).
- Network controls: allowlist IPs, require VPN, or use a secure proxy/bastion.
- TLS everywhere: verify certificates; avoid opportunistic encryption.
- Secrets management: rotate passwords/keys; avoid hardcoding in scripts.
- Auditability: log connections and query execution sources if feasible.
Turning results into actionable notifications
When a query returns rows, the alert message should include just enough context to act immediately:
- What happened: “3 queries running > 15m”
- Where: cluster/DB name
- Top offenders: PID, runtime, wait event, truncated SQL
- Next step: link to runbook or a short instruction
Also consider deduplication keys (e.g., “blocking_pid”) so the same root cause doesn’t page repeatedly.
Common pitfalls (and how to avoid them)
- Alerting on normal batch windows: add time-of-day suppression or separate thresholds for business hours.
- Using heavyweight queries on large tables: avoid full scans; prefer stats views or indexed timestamps.
- No owner for the alert: every alert should have a clear on-call destination.
- Ignoring “recovery” signals: auto-resolve reduces noise and builds confidence.
Putting it together: a minimal alert set to start this week
If you’re starting from scratch, deploy these four first:
- Long-running queries > 15 minutes (critical)
- Blocked queries > 60 seconds (warning) and > 5 minutes (critical)
- Replication lag > your RPO (critical)
- Data freshness staleness on 1–3 key tables (warning/critical)
Once those are stable, expand into connection pressure and bloat indicators. The goal is not “more alerts,” but faster detection with fewer false positives.
Conclusion
To monitor PostgreSQL with query alerts effectively, focus on a small set of read-only checks tied to real operational outcomes: latency, lock contention, replication health, and data freshness. Add timing controls (debounce/cooldown), keep queries lightweight, and lock down access with least privilege and TLS. If you prefer receiving these SQL-driven signals on mobile alongside email or push notifications, an iOS workflow such as Query Alert can help centralize scheduled checks without turning monitoring into another dashboard.