From the objective of OSS-DB Exam Silver
- Operation and Management - Basic operation management work (concept and action of automatic vacuum)
Sample Question
1.11
Choose two accurate statements about automatic vacuum.
- Vacuum is automatically executed at regular intervals.
- Vacuum is automatically executed when the system load is low.
- Vacuuming is automatically performed on the table where a large amount of data was inserted, updated, or deleted.
- Vacuuming is executed automatically to avoid looping transaction ID.
- Since the timing at which the vacuum is executed can not be predicted, it is not executed by default.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2019/03/04
Answer and Explanation
PostgreSQL adopts a write-once data structure, when data is deleted, a deleted flag is attached to the data, and the data is not physically deleted. UPDATE also internally executes DELETE and INSERT, old data remains, and when left alone, the amount of physical data increases.
The vacuum (VACUUM) function reclaims the area of this deleted data. The automatic vacuum executes the vacuum function.
When executing the automatic vacuum, each table in the database is inspected and vacuum is automatically executed when changes such as insertion, update, and deletion have been made over specific thresholds. In order to avoid the transaction ID circulation problem, the vacuum is automatically executed even if the table's relfrozenid value is larger than autovacuum_freeze_max_age.
Execution of automatic vacuum depends on the amount of data update only, so it has nothing to do with the elapsed time or system load.
Execution of automatic vacuum is not mandatory, but it is strongly recommended and it’s execution is enabled by default. Parameters are also set to appropriate values by default.
Therefore, the correct answers are C and D.