This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Alert - Long Running PostgreSQL queries

Description

It's important to identify and investigate if there are any long running queries in your PostgreSQL database(s).  These long running queries may interfere in overall database performance and could be the result of a stuck background process.

This alert utilizes pg_stat_activity to monitor all non "autovacuum" related queries and notifies you if there are queries running longer than the defined thresholds.

Alert Definition

To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:

Alert Name: Long running queries

Execution Interval: 10 minutes -- You may want to change the interval based on your database workload.  

Notification Text: Empty

SQL Statement:

SELECT pid ||' : '|| query, extract(hours from now() - pg_stat_activity.query_start) AS duration
FROM pg_stat_activity
WHERE query not like 'autovacuum:%' and query_start is not null
and extract(hours from now() - pg_stat_activity.query_start) > 5.5

Units: Hours

High Threshold: Min 24, Max <blank> 

Medium Threshold: Min 12, Max 24

Low Threshold: Min 6, Max 12