Vaccum tables
Job details
| Name: |
Vaccum tables |
| Platform: |
Postgres |
| Category: |
Maintenance |
| Description: |
dbWatch engine framework job (for internal use only). Used for patching or upgrading of dbWatch engine framework. |
| Long description: |
This job runs VACUUM statement on every database and reclaims storage occupied by dead tuples. It’s necessary to do VACUUM periodically, especially on frequently-updated tables. |
| Version: |
2.2 |
| Default schedule: |
10 6 * * |
| Requires engine install: |
No |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘postgres’]/.[newer_than_ninetwo = ‘1′] |
Parameters
| Name |
Default value |
Description |
| ignore_databases |
postgres_full_backup.tar |
The list of databases to be ignored (separated by comma). |
Job Summary
- Purpose: The purpose of this job is to run the VACUUM SQL command across all eligible databases to reclaim storage by removing dead tuples. This ensures optimum database performance and prevents storage bloating.
- Why: This job is essential as it helps in maintaining database efficiency in PostgreSQL systems, especially critical for frequently updated tables. Regular vacuuming of the database helps in reclaiming the space occupied by tuples that have been deleted or made obsolete by updates, which PostgreSQL does not automatically reclaim.
- Manual checking: You can manually check and perform a VACUUM on the database using the following SQL command:
VACUUM;
Details of Execution
- The job is designed specifically for instances where the database type is PostgreSQL and versions newer than 9.2.
- It includes an express installation feature, suggesting that it can be quickly deployed with minimal configuration.
- Automatically, this task will ignore certain databases (configurable) to avoid accidental execution on critical or system databases.
Database Execution Process
- A list of databases to be ignored during the vacuum process is maintained.
- The system queries all databases skipping template databases and any that match the names in the ignore list.
- For each database that is not on the ignore list, the VACUUM command is executed.
- This job keeps track of which databases have been vacuumed and provides a message output summarizing the activity.
- Any errors encountered during the process are caught and reported as part of the job’s status.
Reporting and Output
- The job generates a report that lists details of the vacuum operations performed on each database.
- The report includes information about the databases that were ignored during the vacuuming process.
- A default schedule is set to trigger this job daily at 6:10 AM server time.
Configuration and Compatibility
- “Vacuum garbage collector” is compatible with PostgreSQL instances confirming to the specified compatibility requirements.
- Scheduled tasks related to this job can be configured or overridden as per specific maintenance windows recommended for the environment.
Job Properties
| Property Name |
Description |
| Name |
Vacuum garbage collector |
| Version |
2.2 |
| Group |
com.dbwatch.job |
| Artifact ID |
postgres_job_noschema_vacuum_tables |
| Category |
Maintenance |
| Entity Type |
Scheduled Task |
| Installability |
Express Install |
| Default Schedule |
Every day at 6:10 AM |
With its specific design tailored for PostgreSQL environments, this monitoring job plays a crucial role in ensuring that the database system remains efficient and does not suffer from performance degradation due to bloated storage.