Sample Exam Questions

From the objective of OSS-DB Exam Silver
S2.5 Basic server administration (Autovacuum concepts and behavior)

Sample Question

2.15

Choose three appropriate explanations for the difference between vacuum (VACUUM) and autovacuum.

 
  1. VACUUM only reclaims unneeded space unless specified by an option, whereas autovacuum both reclaims unneeded space and analyzes (ANALYZE) tables.

  2. While a list of target tables must be specified for VACUUM, autovacuum automatically targets tables with more than a certain amount of inserted, updated, or deleted data.

  3. VACUUM can target temporary tables, but autovacuum does not.

  4. VACUUM can reduce the file size by specifying options, but autovacuum has no equivalent feature.

  5. ACUUM can be made to run automatically on a regular basis, for example, using an OS feature. Autovacuum runs automatically when the PostgreSQL server is running and the transaction volume is low, so there is no need to configure OS features.

※This sample exam is different from those that appear in the actual OSS-DB Exam.
2024/05/07

Answer and Explanation

PostgreSQL databases adopt an append-only structure, which enables features such as multiversioning. As a result, when data is updated or deleted, the space that becomes unnecessary is not automatically reused. If left unattended, the disk space occupied by the table will continue to grow. Executing VACUUM reclaims the unnecessary space and makes it reusable. This means that the space can be used for subsequent INSERTs and UPDATEs (if possible), preventing further consumption of disk space. Autovacuum, which automatically executes VACUUM, is enabled by default and is usually properly configured. Therefore, the need to manually execute VACUUM has become less frequent. However, it is important to understand the underlying principles.

Now, let's look at the options one by one.
VACUUM  only reclaims unnecessary space unless an option is specified, but if the ANALYZE option is specified, it also performs table analysis. On the other hand, autovacuum performs both reclamation and analysis of unnecessary space.
A list of target tables can be specified for VACUUM, but if not specified, all tables that can be vacuumed with the privileges of the executing user will be targeted. Autovacuum performs reclamation and analysis processing on all tables where the amount of change due to insertions, updates, and deletions exceeds a certain threshold.
Temporary tables cannot be targeted by autovacuum because they can only be viewed from the session that created them. However, they can be targeted when executing VACUUM manually.
While a normal VACUUM only makes unnecessary space reusable and does not reduce the size of the files that make up the table, running VACUUM with the FULL option reduces the file size because it repacks the data in the table. However, there are side effects, such as table locking, so avoid using VACUUM FULL unless absolutely necessary. Autovacuum does not have the capability of VACUUM FULL.
It is also possible to schedule VACUUM to run using an OS feature (e.g., Linux cron). In the case of autovacuum, a process called the autovacuum launcher resides from which the autovacuum worker process is started at regular intervals. It is this worker process that actually does the VACUUM. Autovacuum is started at regular intervals, regardless of the amount of transactions, but the interval can be adjusted by configuration parameters.

So the correct answers are A, C, and D.