postgres_table_last_autovacuum_time.md 2.5 KB
Understand the alert
This alert is related to the PostgreSQL database and checks the time since the last autovacuum operation occurred on a specific table. If you receive this alert, it means that the table has not been vacuumed by the autovacuum daemon for more than a week (7 days).
What is autovacuum in PostgreSQL?
Autovacuum is a feature in PostgreSQL that automates the maintenance of the database by reclaiming storage, optimizing the performance of the database, and updating statistics. It operates on individual tables and performs the following tasks:
- Reclaims storage occupied by dead rows and updates the Free Space Map.
- Optimizes the performance by updating statistics and executing the
ANALYZEcommand. - Removes dead rows and updates the visibility map in order to reduce the need for vacuuming.
Troubleshoot the alert
- Check the autovacuum status
To check if the autovacuum daemon is running for the PostgreSQL instance, run the following SQL command:
SHOW autovacuum;
If the result is "off", then the autovacuum is disabled for the PostgreSQL instance. You can enable it by modifying the postgresql.conf configuration file and setting autovacuum = on.
- Verify table-specific autovacuum settings
Sometimes, autovacuum settings might be altered for individual tables. To check the autovacuum settings for the specific table mentioned in the alert, run the following SQL command:
SELECT relname, reloptions FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relname = '<table_name>' AND nspname = '<schema_name>';
Look for any custom autovacuum_* settings in the reloptions column and adjust them accordingly to allow the autovacuum daemon to run on the table.
- Monitor the PostgreSQL logs
Inspect the PostgreSQL logs for any error messages or unusual behavior related to autovacuum. The log file location depends on your PostgreSQL installation and configuration.
- Manually vacuum the table
If the autovacuum daemon has not run for a long time on the table, you can manually vacuum the table to reclaim storage and update statistics. To perform a manual vacuum, run the following SQL command:
VACUUM (VERBOSE, ANALYZE) <schema_name>.<table_name>;