PostgreSQL — alert on queries running longer than N seconds
Every week some analyst fires off a heavy SELECT without LIMIT and blocks other transactions. You want to know within a minute, not within an hour.
Recipe
#!/usr/bin/env bash
# psql one-liner: any query > 60s currently active outside vacuum/autovacuum.
LIMIT_SEC="${LIMIT_SEC:-60}"
COUNT=$(psql -tAX -c "
SELECT COUNT(*) FROM pg_stat_activity
WHERE state = 'active'
AND backend_type = 'client backend'
AND now() - query_start > interval '${LIMIT_SEC} seconds'")
[ "${COUNT:-0}" -gt 0 ] && echo "high ${COUNT}" || echo "ok"Same thing in Enterno.io
Expose the endpoint, point an Enterno monitor at it with the "ok" keyword on a 1-min interval. Pair with a heartbeat on `pg_dump` so a long-running query doesn’t silently break the backup either.
Related recipes
long_query_time = 1, slow_query_log enabled. You need to know when the slow-query rate per minute suddenly jumps (a deploy broke an index, ORM went N+1).
Detect the moment a replica falls behind the primary by more than 10 seconds.
A replica-set secondary falls behind the primary; the app will read stale data within a minute. Want an HTTP endpoint that says "ok" or "lag".