POSTGRESQL-ESSENTIALS practice question 12 of 15
In PostgreSQL, what does VACUUM do?
Full set: 71 questionsIn PostgreSQL's Multi-Version Concurrency Control (MVCC) architecture, when a row is updated or deleted, the old version of the row (the "dead tuple") is not immediately removed from the data files. The VACUUM command's primary purpose is to scan tables and reclaim the storage occupied by these dead tuples. This makes the space available for reuse by subsequent INSERT and UPDATE operations, preventing table and index bloat and ensuring efficient space management. It also updates the visibility map, which can speed up index-only scans.
A. VACUUM reclaims unused space but does not perform data compression. VACUUM FULL rewrites the table, which can shrink its on-disk size, but this is a side effect of rewriting, not compression.
C. Database backups are created using dedicated utilities such as pgdump, pgdumpall, or pgbasebackup, not the VACUUM command.
D. The ANALYZE command updates statistics used by the query planner. While often run with VACUUM (e.g., VACUUM ANALYZE), VACUUM's core function is storage reclamation, not plan optimization.
1. PostgreSQL 16 Documentation, Chapter 25. Routine Database Maintenance Tasks, Section 25.1.1. Recovering Disk Space.
"In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control (MVCC)... But eventually, an outdated row version is no longer of interest to any active transaction. The space it occupies must be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM."
2. PostgreSQL 16 Documentation, SQL Commands, VACUUM.
The command description explicitly states: "VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done."
3. Pavlo, A. (2022). Lecture #16: Multi-Version Concurrency Control. 15-445/645 Intro to Database Systems (Fall 2022), Carnegie Mellon University.
Slide 39, "Garbage Collection," explains the necessity of a background process in MVCC systems to remove old tuple versions that are no longer visible to any active transaction, which is the process PostgreSQL calls vacuuming.
Keep practicing
Take the free POSTGRESQL-ESSENTIALS practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other POSTGRESQL-ESSENTIALS practice questions
- Question 1What is the purpose of the autovacuum parameter in PostgreSQL?
- Question 2Which psql meta-command is used to display all roles and their attributes?
- Question 3SERIAL and IDENTITY columns can be used to add an auto-incrementing column in a Postgres …
- Question 4You are connected to a PostgreSQL database. After executing a complex query an error mess…
- Question 5Which PostgreSQL configuration parameter controls the amount of memory used for sorting o…
- Question 6You want to set the maximum time to complete client authentication to 10 seconds. Which p…
