Google Professional Cloud Database Engineer Question 196
Single answerGoogle Cloud PlatformYou are managing a Cloud SQL for PostgreSQL database that has been running for several months. Users have reported performance degradation for a frequently accessed table. You suspect the table might be fragmented. How can you assess table fragmentation in Cloud SQL for PostgreSQL?
- A
Run the VACUUM ANALYZE command and check the pg_stat_user_tables view for dead tuples.
- B
Use the EXPLAIN command to analyze the query execution plan for the table.
- C
Query the pg_stat_activity view to identify long-running queries on the table.
- D
Inspect the pg_stat_user_tables view and look for a high value in the 'n_dead_tup' column.
Show answer and explanation
Correct answer: D
Explanation
To assess table fragmentation in Cloud SQL for PostgreSQL, the 'n_dead_tup' column in the pg_stat_user_tables view is a key indicator. A high number of dead tuples signifies that the table is fragmented, which may lead to performance issues. Regularly monitoring this metric helps identify when table maintenance, such as VACUUM or autovacuum tuning, is required.
- A. Incorrect.
While VACUUM ANALYZE can help maintain table statistics and remove dead tuples, it does not directly provide information about fragmentation.
- B. Incorrect.
The EXPLAIN command is used to analyze query execution plans, but it does not provide insights into table fragmentation or dead tuples.
- C. Incorrect.
The pg_stat_activity view shows currently active queries and their durations but does not provide details regarding table fragmentation.
- D. Correct.
The pg_stat_user_tables view contains the 'n_dead_tup' column, which indicates the number of dead tuples (fragmentation) in the table. A high value suggests that the table is fragmented and needs maintenance.