Postgres Monitoring with SQL: Metrics, Queries, and Alert Rules

Reliable postgres monitoring is less about dashboards and more about catching the few conditions that reliably predict customer impact: saturation (CPU/IO/locks), runaway connections, replication lag, storage/bloat surprises, and job backlogs. The most portable way to detect these conditions—across cloud providers and self-managed setups—is to turn SQL queries into scheduled checks and alert only when the result indicates action is needed.

This guide focuses on practical, query-first postgres monitoring: what to measure, the exact SQL to run, recommended thresholds, and how to design low-noise alerts that wake you up only when there’s a clear next step.

Why query-first postgres monitoring works

PostgreSQL already exposes the signals you need via system catalogs and views like pg_stat_activity, pg_stat_database, and pg_stat_replication. Query-based monitoring has a few advantages:

• Portability: the same queries work across environments and vendors.
• Precision: you can tailor checks to your schema, workload, and business SLAs.
• Security: monitoring can be strictly read-only with least-privilege roles.
• Actionability: each alert can include the exact rows needed for triage.

Principle: If an alert doesn’t suggest a concrete action (kill a session, increase a limit, run VACUUM, investigate a queue), it’s probably a dashboard metric—not an alert.

Key postgres monitoring metrics (and when to alert)

Many teams track dozens of metrics. For alerting, you usually need fewer than ten. The table below lists high-signal metrics, what they mean, and what a typical first-pass alert threshold might look like. Adjust based on your baseline and risk tolerance.

Area	Metric	Why it matters	Starter alert rule
Availability	Can connect + simple query succeeds	Confirms the DB is reachable and responsive	Failing for 2 consecutive checks
Connections	Used connections vs max	Connection exhaustion causes app downtime	> 80–90% for 5–10 min
Locking	Blocked queries duration	Long blocks cascade into timeouts	Any blocked > 60–120s
Performance	Long-running queries	Runaway queries can starve resources	Any non-idle > 5–15 min
Replication	Replica lag (bytes/time)	Lag breaks read-scaling and failovers	Lag > X seconds for 5–10 min
Storage	DB or table growth rate	Unexpected growth leads to outages/cost	Growth > baseline by N%
Maintenance	Autovacuum not keeping up (dead tuples)	Bloat increases IO and slows queries	Dead tuple ratio > threshold

Practical SQL checks for postgres monitoring

Below are copy-paste queries you can schedule at intervals (for example, every 1–5 minutes for availability/locks, and every 15–60 minutes for bloat/growth). Each example includes what to alert on and what to include in the alert payload.

1) Basic availability: “can I run SQL?”

This is the simplest and often the most valuable check, especially when network policies, DNS, or credentials rotate.

SELECT 1 AS ok;

Alert when: query fails twice in a row (to avoid flapping).
Include: error message, target host/DB name.

2) Connection saturation and top offenders

Connection exhaustion is a common root cause of incidents. Monitor how close you are to max_connections and which users/apps are contributing.

WITH limits AS (
  SELECT setting::int AS max_conn
  FROM pg_settings
  WHERE name = 'max_connections'
)
SELECT
  (SELECT max_conn FROM limits) AS max_connections,
  COUNT(*) AS used_connections,
  ROUND(COUNT(*) * 100.0 / (SELECT max_conn FROM limits), 1) AS used_pct
FROM pg_stat_activity;

To identify sources:

SELECT usename, application_name, client_addr, state, COUNT(*) AS connections
FROM pg_stat_activity
GROUP BY 1,2,3,4
ORDER BY connections DESC
LIMIT 20;

Alert when: used_pct > 85 sustained for 5 minutes.
Include: top 5 groups (user/app) by connection count.

3) Long-running queries (runaway work)

Long queries are not always bad, but they’re often correlated with lock contention, vacuum starvation, and customer timeouts.

SELECT
  pid,
  usename,
  application_name,
  now() - query_start AS runtime,
  state,
  wait_event_type,
  wait_event,
  LEFT(query, 200) AS query_snippet
FROM pg_stat_activity
WHERE state <> 'idle'
  AND now() - query_start > interval '10 minutes'
ORDER BY runtime DESC;

Alert when: any query runtime exceeds 10–15 minutes (tune by workload).
Include: PID, runtime, wait info, and a safe query snippet (truncate to reduce sensitive exposure).

4) Blocking locks: “who is blocking whom?”

Blocked queries are often more actionable than generic latency metrics because they reveal the culprit session.

SELECT
  blocked.pid     AS blocked_pid,
  blocked.usename AS blocked_user,
  now() - blocked.query_start AS blocked_for,
  blocker.pid     AS blocker_pid,
  blocker.usename AS blocker_user,
  now() - blocker.query_start AS blocker_runtime,
  LEFT(blocked.query, 120) AS blocked_query,
  LEFT(blocker.query, 120) AS blocker_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocker
  ON blocker.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE now() - blocked.query_start > interval '60 seconds'
ORDER BY blocked_for DESC;

Alert when: any blocked query exceeds 60–120 seconds.
Include: blocked PID, blocker PID, and both query snippets for fast triage.

5) Replication lag (physical streaming replicas)

If you rely on replicas for reads or failover, replication lag is one of the most important postgres monitoring alerts.

SELECT
  application_name,
  client_addr,
  state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS byte_lag
FROM pg_stat_replication
ORDER BY byte_lag DESC;

Alert when: lag exceeds a byte threshold for 5–10 minutes (choose based on typical WAL rate). If your environment exposes time-based lag, alert on seconds as well.
Include: replica name/address, state, and lag value.

6) Table bloat signals (dead tuples ratio)

Bloat is tricky to measure perfectly without extensions, but you can get strong signals using dead tuples. High dead tuple ratios often mean autovacuum is underprovisioned or blocked.

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE (n_live_tup + n_dead_tup) > 10000
ORDER BY dead_pct DESC
LIMIT 20;

Alert when: a critical table’s dead_pct stays above (say) 20–30% for hours, or spikes sharply versus its baseline.
Include: top tables by dead_pct and approximate tuple counts.

7) Growth watch: biggest tables and databases

Storage incidents often come from a single table growing unexpectedly (logs, event tables, orphaned blobs). Check size leaders regularly.

SELECT
  n.nspname AS schema,
  c.relname AS table,
  pg_total_relation_size(c.oid) AS bytes,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY bytes DESC
LIMIT 20;

Alert when: a top table grows > N% day-over-day or exceeds a hard cap you set per table.
Include: table name, size, and (if you store it) previous size for context.

Designing low-noise postgres monitoring alerts

The difference between “useful” and “ignored” alerts is usually in rule design. Apply these patterns:

• Use sustained conditions: alert on “breaches for 2–3 consecutive checks” to avoid spikes.
• Alert on symptoms with owners: blocked sessions, connection exhaustion, replication lag—each has an obvious team to page.
• Include triage data: PIDs, usernames, application_name, and a short query snippet reduce time-to-fix.
• Prefer percent + absolute thresholds: e.g., connections > 85% and > 200 used, depending on environment size.
• Deduplicate by key: for locks, group by blocker PID so you don’t spam per blocked session.

Example: a clear, actionable alert payload

If your alert system supports templates, aim to deliver an alert that looks like a mini runbook:

Title: Postgres blocking lock > 120s on prod-db

Blocked for: 00:03:40
Blocked PID: 18422 (app_user, api)
Blocker PID: 17701 (migration_user, psql)
Wait: Lock / relation

Blocked query: UPDATE orders SET ...
Blocker query: ALTER TABLE orders ...

Scheduling: how often should checks run?

Frequency depends on how quickly a condition becomes customer-visible:

• Every 1–2 minutes: availability, connection saturation, blocking locks.
• Every 5 minutes: long-running queries, replication lag.
• Every 30–60 minutes: bloat signals, size leaders, growth comparisons.

For noisy environments, consider two-tier monitoring: fast checks that only alert on severe thresholds, and slower checks that create informational tickets or summaries.

Security for query-based postgres monitoring (read-only, least privilege)

Monitoring is a privileged activity if done carelessly. Good postgres monitoring setups use a dedicated role with minimal permissions:

1. Create a read-only login role used only for monitoring.
2. Grant access only to required views (many pg_stat_* views are accessible, but some fields may require additional privileges depending on configuration).
3. Do not store full SQL texts in alerts if they may contain sensitive literals; truncate and/or parameterize where possible.
4. Use TLS-encrypted connections and rotate credentials regularly.

A typical starting point (adjust for your policies) might look like:

-- Run as an admin role
CREATE ROLE monitor_user LOGIN PASSWORD '...';

-- Allow read access where needed
GRANT pg_read_all_stats TO monitor_user;
GRANT pg_read_all_settings TO monitor_user;

-- Optional: restrict to specific databases via separate users if required

Note: Built-in roles such as pg_read_all_stats may not be available or may behave differently in some managed environments; validate in staging and follow your provider’s guidance.

A quick checklist for your postgres monitoring rollout

• Start with 6–8 alerts max (availability, connections, blocking, long queries, replication lag, storage growth).
• Set thresholds from reality: observe a week of baselines before tightening rules.
• Include context in every alert: top offenders, PIDs, sizes, lag values.
• Verify on-call usability: can someone act in under 5 minutes with the alert alone?
• Review quarterly: remove alerts that never lead to action.

Where to run these checks

You can execute scheduled checks from many places: cron jobs, CI runners, serverless functions, or a monitoring tool that supports query scheduling and notifications. If you prefer to keep it lightweight and mobile-friendly, an iOS tool like Query Alert can run read-only database queries on a schedule and notify you when results cross a threshold.

Done well, query-driven postgres monitoring gives you fast detection, low noise, and alerts that include the exact evidence you need to fix the issue—not just a chart that tells you something is wrong.