Sample Exam Questions

From the objective of OSS-DB Exam Silver
S2.5 Basic server administration (Purpose and usage of VACUUM and ANALYZE)

Sample Question


Choose two appropriate explanations for the difference between VACUUM and VACUUM FULL.

  1. VACUUM FULL targets all tables in the database, but VACUUM only targets specific tables that you specify.

  2. VACUUM FULL also runs ANALYZE on the table, but VACUUM does not run ANALYZE.

  3. VACUUM FULL acquires an exclusive lock on the table being processed, which conflicts with normal table read/write operations. VACUUM does not acquire an exclusive lock.

  4. Running VACUUM FULL typically reduces the size of the files that make up the table, while running VACUUM typically does not change the size of these files.

  5. VACUUM can be executed by issuing the vacuumdb command from the OS command line, but VACUUM FULL cannot be executed from the command line.

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

Answer and Explanation

In PostgreSQL, when a row in a table is updated or deleted, the space occupied by that row is not physically deleted, but instead a deleted flag is set. This is a key mechanism for realizing the multiversioning feature, but there is a problem that as table updates are repeated, the deleted space increases and the physical size of the file that make up the table becomes larger.

VACUUM is a feature to deal with this, which reclaims the deleted space and makes it reusable. In other words, the next time an INSERT or UPDATE is performed on the table, the data goes into the reclaimed space, so you can avoid increasing the size of the file that makes up the table.
Normal VACUUM only reclaims deleted space, so the physical size of the file does not decrease, but if you run VACUUM FULL, it repacks the data and creates a data file composed only of data in use, so the size of the data file after processing becomes smaller. However, during this process, it acquires an exclusive lock on the table, so read/write processing on the table is blocked.

Both VACUUM and VACUUM FULL can target only specific tables in the database or all tables. If you run with the ANALYZE option, it performs ANALYZE processing after VACUUM, but if you do not specify an option, it only performs VACUUM processing. Normally, it is desirable to run VACUUM ANALYZE as a set.

You can also run VACUUM by issuing the vacuumdb command from the OS command line. If you specify the -f option, you can run VACUUM FULL.

So the correct answers are C and D.

VACUUM is an extremely important command in PostgreSQL operation management. It may be rare to run VACUUM manually now because it is automatically run by the autovacuum feature, but you should be familiar with its principles.