From the objective of OSS-DB Exam Gold
- Advanced Server Administration - Commands for database administration
Sample Question
1.04
Choose the one statement that is NOT an accurate description of the CLUSTER command.
-
The CLUSTER command physically reorganizes the table data and may improve read performance.
-
Reads on tables for which the CLUSTER command is running will be held back.
-
The CLUSTER command cannot be executed on a table for which a PRIMARY KEY does not exist.
-
Increasing the value of maintenance_work_mem may improve the performance of the CLUSTER command.
-
CLUSTER command may reduce table and index size.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2025/05/29
Answer and Explanation
[Option A]
Tables clustered using the CLUSTER command are physically reorganized based on the index information of the table. This means that tuples with similar values are stored on the same data page, potentially reducing disk I/O, especially when retrieving data by specifying a range.
[Option B]
The CLUSTER command requires an exclusive lock on the target table, causing other processes to wait until the operation is complete.
[Option C]
The CLUSTER command reorganizes the table based on a single index. While the CLUSTER command cannot be executed on a table without any indexes, the target index does not necessarily need to be a primary key index.
[Option D]
maintenance_work_mem specifies the maximum amount of memory used by maintenance operations such as VACUUM, CREATE INDEX, REINDEX, and CLUSTER. The default value (16 MB) is quite small; increasing this value can significantly improve the performance of the CLUSTER command.
[Option E]
The CLUSTER command may reduce table and index size since unused space is deleted and REINDEX is also performed at the time of clustering.
Therefore, option C is the inaccurate explanation.
Refer to the official documentation for more details.
https://www.postgresql.org/docs/10/sql-cluster.html
The correct answer is C.