Custom Alert - Dead tuples percentage - pg_stat_user_tables


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. 

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: 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

SQL Statement:

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

Units: %

High Threshold: Min 95, Max <blank> 

Medium Threshold: Min 85, Max 95

Low Threshold: Min 75, Max 85