Vacuum alert

Job details

Name:	Vacuum alert
Platform:	Postgres
Category:	Capacity
Description:	Checks the size of the log file.
Long description:	This job checks if vacuum has been performed recently.
Version:	3.0
Default schedule:	10 7 * *
Requires engine install:	No
Compatibility tag:	.[type=‘instance’ & databasetype=‘postgres’]/.[newer_than_ninetwo = ‘1′]

Parameters

Name	Default value	Description
ignore_databases	10	The list of databases to be ignored (separated by comma).
warning_threshold	10	Will give a warning if the number of hours since last vacuum exceeds this limit.
alarm_threshold	10	Will give an alarm if the number of hours since last vacuum exceeds this limit.
ignore_schemas	10	Comma separated list of schemas to ignore (format [database_name].[schema_name]).
min_table_size	10	Minimum table size (in KB) to be checked if vacuumed.
min_DML_operations	10	Minimum number of DML operations (insert, update and delete) on a table to be checked if vacuumed.

Job Summary

Purpose: The purpose of this job is to monitor and alert if the vacuum operation on PostgreSQL tables has not been performed within specified thresholds.
Why: This job is important to ensure that the PostgreSQL database’s performance and storage efficiency are not degraded due to outdated table data. Vacuum operations are critical for reclaiming storage and maintaining the efficiency of database operations.
Manual checking: You can check this manually in the database by issuing these SQL commands:

SELECT pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum FROM pg_stat_all_tables WHERE pg_stat_all_tables.last_vacuum < now() - interval 'desired period' OR pg_stat_all_tables.last_autovacuum < now() - interval 'desired period';

Details of the Monitoring Task

The job is configured to check against PostgreSQL instances specifically (Postgres versions newer than 9.2).
It filters and ignores specified databases and schemas from the check based on configured parameters.
The job provides customizable thresholds for warning and alarm alerts based on the elapsed time since the last vacuum, taking into consideration the minimum table size and DML (insert, update, delete) operations count thresholds.
It generates a list of tables which have not been vacuumed within the specified threshold times, categorizing the output into warnings and alarms based on severity.

Notifications and Output

A status flag indicating the outcome of the check (0 for ok, 1 for warning, 2 for alarm).
Lists databases checked, specific tables that have gone beyond warning or alarm thresholds, and any errors encountered during the check.
Output tables in the monitoring tool will show the last vacuum times and highlight tables requiring attention.

Implementation Specifics

The job runs on a default schedule (parsed from XML but typically defined within the system settings), and has a designated timeout period to ensure performance stability.
Reports and presentation details are configured to provide a clear and actionable output, displaying the essential vacuum status of tables and databases that meet specified criteria.

The set-up and operation of this monitoring job are crucial for maintaining the health of PostgreSQL databases, particularly in environments with regular bulk data updates, ensuring ongoing performance and storage optimizations through timely vacuum operations.