CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is performing routine maintenance on a PostgreSQL database and notices that several large tables have significantly more disk space allocated than is used by their actual data. This is causing unnecessary I/O and performance degradation. Which PostgreSQL-specific maintenance operation should they perform to reclaim this wasted space?
- AVACUUM FULL
- BCLUSTER
- CANALYZE
- DREINDEX
Show answer & explanationAnswer & explanation
Correct answer: A. VACUUM FULL
In PostgreSQL, `VACUUM FULL` rewrites the entire table and index files to disk, reclaiming all dead space (bloat) and compacting the table. While it locks the table, it is effective for significant space reduction.
Why the other options are wrong
- B. `CLUSTER` reorders table data based on an index, which can improve query performance but does not necessarily reclaim wasted space from bloat.
- C. `ANALYZE` updates statistics for the query planner but does not reclaim disk space.
- D. `REINDEX` rebuilds indexes, which can reclaim space for indexes, but not for the table itself.
PostgreSQL VACUUM FULL
A PostgreSQL command that rewrites the entire contents of a table and its associated indexes to disk, reclaiming all dead tuples and compacting the table.
- Reclaims all wasted disk space (bloat).
- Acquires an exclusive lock on the table, blocking all other operations.
- Should be used judiciously due to downtime implications.
Memory trick: VACUUM FULL: Sucks up all the bloat.