Custom Alert - Last AutoVacuum - 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. 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 monitor when AutoVacuum last ran, 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: Last Vacuum

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

Notification Text: Empty

SQL Statement:

select schemaname ||'.'||relname, extract(hour FROM now()-last_autovacuum) from pg_stat_user_tables
where last_autovacuum is not null;

Execute Against: Monitored Instance

Units: Hours

High Threshold: Min 72, Max <blank> 

Medium Threshold: Min 24, Max 72

Low Threshold: Min 8, Max 24