PostgreSQL alerting is the difference between “we’ll find out in the morning” and “we fixed it before users noticed.” The challenge isn’t running checks—it’s designing alerts that are actionable, low-noise, and safe to run continuously.
This guide focuses on query-driven PostgreSQL alerting: turning small, read-only SQL checks into reliable signals you can send to email, on-call, chat, or push notifications. You’ll learn what to alert on, how to write the queries, and how to reduce alert fatigue with simple techniques like hysteresis, time windows, and deduping.
What “good” PostgreSQL alerting looks like
An alert is useful when it answers three questions immediately:
- What broke? (symptom + scope)
- How bad is it? (severity and current value vs baseline/threshold)
- What should I do next? (a likely cause or runbook pointer)
In practice, high-quality PostgreSQL alerting follows a few rules:
- Prefer symptoms over causes. Alert on user impact (errors, latency, failed jobs) before internal metrics.
- Use time windows. Spikes happen; persistent problems matter.
- Keep checks read-only and least-privilege. Monitoring shouldn’t require write access.
- Include context in the payload. The alert should carry the top offenders, not just “threshold exceeded.”
Choosing an alerting approach (and when SQL queries win)
There are three common ways to implement PostgreSQL alerting. Each has trade-offs:
| Approach | How it works | Pros | Cons |
|---|---|---|---|
| Query-based polling | Run read-only SQL at an interval; alert on results | Simple, portable, works for KPIs and internals | Detection latency depends on schedule |
| LISTEN/NOTIFY | App subscribes to notifications emitted by DB | Near real-time, low overhead | Requires DB-side emitters; can be missed if not persisted |
| Triggers / audit tables | Write events on data change; alert off event stream | Precise change detection, rich payloads | Extra writes; needs schema changes and governance |
For many teams, query-based PostgreSQL alerting is the fastest path to value because it can cover both operational signals (replication lag, blocked queries) and business signals (failed payments, stuck orders) without changing application code.
Alert category #1: Availability & connection health
Start with checks that confirm the database is reachable and responsive. These should be cheap and run frequently.
1) “Can I connect and run a trivial query?”
SELECT 1;Alert if it fails, times out, or exceeds a strict latency threshold (for example, > 1–2 seconds). Keep this separate from heavier checks to avoid false negatives caused by long-running monitoring queries.
2) Connection saturation (approaching max_connections)
SELECT
COUNT(*) AS current_connections,
(SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
ROUND(100.0 * COUNT(*) / (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 2) AS pct_used
FROM pg_stat_activity;Alerting pattern: warn at 75–85%, critical at 90–95%, and require persistence (for example, 3 checks in a row). Include top states (active/idle in transaction) to speed triage.
Alert category #2: Replication lag (one of the best early warnings)
If you run streaming replication, lag can precede read-scaling failures, stale reads, or painful failovers. PostgreSQL alerting here is straightforward and highly actionable.
Primary: measure lag per replica
SELECT
application_name,
client_addr,
state,
EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS seconds_since_last_replay
FROM pg_stat_replication;Note: On many setups, you’ll instead compute byte lag using LSN differences (more reliable for busy systems). Example:
SELECT
application_name,
client_addr,
state,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_lag
FROM pg_stat_replication;Alerting pattern:
- Warning if lag > X for Y minutes (example: > 30s for 5 minutes).
- Critical if lag > X’ for Y’ minutes (example: > 5 minutes for 5 minutes).
- Suppress alerts during planned maintenance windows.
Alert category #3: Blocking, lock contention, and stuck transactions
Locks are a classic source of “the database is up but the app is down.” Good PostgreSQL alerting identifies both the blocked queries and the blockers.
Detect blocked queries and their blockers
SELECT
now() AS observed_at,
a.pid AS blocked_pid,
a.usename AS blocked_user,
a.application_name AS blocked_app,
age(now(), a.query_start) AS blocked_for,
left(a.query, 120) AS blocked_query,
b.pid AS blocking_pid,
b.usename AS blocking_user,
age(now(), b.query_start) AS blocking_for,
left(b.query, 120) AS blocking_query
FROM pg_stat_activity a
JOIN pg_stat_activity b
ON b.pid = ANY(pg_blocking_pids(a.pid))
WHERE a.wait_event_type = 'Lock'
ORDER BY blocked_for DESC
LIMIT 20;Alerting pattern: trigger only when blocked_for exceeds a threshold (for example, 60–120 seconds) and the blocked query count is above a minimum. Include the top rows in the alert payload.
Idle in transaction (silent killer)
SELECT
pid,
usename,
application_name,
client_addr,
age(now(), xact_start) AS xact_age,
state,
left(query, 120) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND xact_start IS NOT NULL
AND now() - xact_start > interval '5 minutes'
ORDER BY xact_age DESC
LIMIT 20;Alert on persistence (for example, > 5 minutes for 2–3 consecutive checks). This is a strong indicator of application-side transaction handling bugs.
Alert category #4: Query performance regressions (without noisy APM)
You can build effective PostgreSQL alerting around slow query volume using extensions like pg_stat_statements. The goal is not “any slow query,” but “a meaningful change that indicates impact.”
Top mean time queries (requires pg_stat_statements)
SELECT
queryid,
calls,
ROUND(mean_exec_time::numeric, 2) AS mean_ms,
ROUND(total_exec_time::numeric, 2) AS total_ms,
left(query, 120) AS sample
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 10;Better alert: compare the last interval to a baseline. If your tooling can store previous values, alert when mean_ms increases by (say) 2–3x and calls are above a minimum, which filters out rare one-offs.
Tip: “Slowest query right now” is often less actionable than “a query that got 3x slower and runs 5,000 times an hour.”
Alert category #5: Job failures and business KPIs (where SQL shines)
The most valuable alerts are often business-facing: stuck pipelines, failed billing, unprocessed events. If the source of truth is PostgreSQL, query-based alerting is usually the cleanest approach.
Example: background jobs failing repeatedly
SELECT
queue,
COUNT(*) AS failures_last_15m
FROM job_runs
WHERE status = 'failed'
AND finished_at >= now() - interval '15 minutes'
GROUP BY queue
HAVING COUNT(*) >= 10
ORDER BY failures_last_15m DESC;Alerting pattern: add a second condition to avoid noise, such as “failure rate > 5% and total attempts > 200” within the window.
Example: “stuck” records (orders not progressing)
SELECT
COUNT(*) AS stuck_orders
FROM orders
WHERE status = 'processing'
AND updated_at < now() - interval '20 minutes';This is classic PostgreSQL alerting: one query, clear threshold, immediate operational meaning, and a straightforward runbook.
Designing low-noise alert rules (the part that matters)
Most alert fatigue comes from predictable issues: flapping thresholds, duplicated pages, and alerts without context. Use these techniques to keep signals clean.
Use persistence (N-of-M)
Instead of alerting on a single breach, require persistence:
- N consecutive checks (e.g., 3 in a row)
- N out of M checks (e.g., 5 out of 10)
Add hysteresis
Use different thresholds for firing vs clearing. Example:
- Fire at replication lag > 60s
- Clear only when lag < 20s for 10 minutes
Deduplicate by dimension
For PostgreSQL alerting, dedupe on the dimension that matches ownership:
- Replication: per replica
application_name - Jobs: per queue
- Locks: per blocked_pid or per database
Always include “top offenders”
If an alert says “locks are happening,” it’s incomplete. Attach the top 5 blockers, the oldest blocked duration, and the impacted application_name if possible.
Security and safety for query-based monitoring
PostgreSQL alerting queries run frequently—treat them as production traffic. Keep them safe:
- Read-only user: grant only what you need (often
pg_monitorplus explicit schema grants for KPI tables). - Statement timeouts: prevent monitoring queries from becoming the incident.
- TLS everywhere: encrypt connections and validate certificates.
- Avoid sensitive data: alert on aggregates (counts, rates), not PII rows.
Where possible, test each query with an EXPLAIN plan and keep monitoring queries indexed (especially KPI checks).
PostgreSQL alerting checklist (copy/paste)
- Define the symptom and the owner (who will fix it?).
- Write a read-only SQL query returning a small result set.
- Add a time window and persistence to prevent flapping.
- Include context columns (top blockers, top queues, top queries).
- Set severity thresholds and hysteresis (fire vs clear).
- Validate performance (timeouts, indexes, EXPLAIN).
- Run a game day: confirm the alert is actionable with a runbook.
Putting it into practice
You can implement these patterns with any scheduler or monitoring system that can run SQL and send notifications. If you prefer an iOS-first workflow for on-the-go triage, tools like Query Alert can turn your PostgreSQL queries into smart alerts with push and email while keeping checks read-only.