In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multi-version concurrency control (MVCC) - the row version must not be deleted while it is still potentially visible to other transactions. But eventually, an outdated or deleted row version is no longer of interest to any transaction (Dead Tuple). The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM.
This alert will utilize the pg_stat_user_tables to monitor the percentage of dead tuples, providing valuable insight into the health of your PostgreSQL vacuuming processes.
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: Dead tuples percentage - pg_stat_user_tables
Execution Interval: 30 minutes -- You may want to change the interval based on your database workload.
Notification Text: Empty
select schemaname ||'.'||relname, round(CAST((float4(n_dead_tup)/float4(n_live_tup))*100 as numeric),2) FROM pg_stat_user_tables WHERE n_live_tup > 0
Execute Against: Monitored Instance
High Threshold: Min 95, Max <blank>
Medium Threshold: Min 85, Max 95
Low Threshold: Min 75, Max 85