Google Professional Cloud Database Engineer Question 194
Select 3Google Cloud PlatformYou are managing a Cloud SQL PostgreSQL database for a high-traffic e-commerce platform. Over time, you notice degraded query performance on a frequently accessed table storing order data. You suspect table fragmentation as the cause. Which of the following actions can help you accurately assess the level of fragmentation affecting this table?
- A
Run the 'ANALYZE VERBOSE' command on the table and review the output for dead tuples.
- B
Query the 'pg_stat_all_tables' system view to check the 'n_dead_tup' column for the table.
- C
Use the 'VACUUM FULL' command to rebuild the table and automatically remove all fragmentation.
- D
Inspect the 'pg_table_size' function to compare the logical size of the table to its physical storage size.
- E
Check the 'pg_stat_user_indexes' system view to analyze index usage and identify bloated indexes.
Show answer and explanation
Correct answers: A, B, D
Explanation
To assess table fragmentation in a PostgreSQL database, you need to focus on identifying dead tuples and comparing the table's logical and physical sizes. 'ANALYZE VERBOSE' and querying 'pg_stat_all_tables' are effective ways to identify dead tuples, while 'pg_table_size' can help detect storage inefficiencies caused by fragmentation. Corrective actions like 'VACUUM FULL' and index-specific diagnostics are not part of the assessment process but can be performed after fragmentation is confirmed.
- A. Correct.
Running 'ANALYZE VERBOSE' provides details about the table, including dead tuples, which are a direct indicator of fragmentation. This is a valid action to assess fragmentation.
- B. Correct.
Querying the 'pg_stat_all_tables' system view and reviewing the 'n_dead_tup' (number of dead tuples) column is a recommended way to assess fragmentation levels in PostgreSQL tables.
- C. Incorrect.
'VACUUM FULL' removes fragmentation and rebuilds the table but does not specifically help in assessing the level of fragmentation. It is more of a corrective action, not a diagnostic step.
- D. Correct.
The 'pg_table_size' function can help you compare logical and physical table sizes, which can indicate fragmentation if the physical size is disproportionately large compared to the logical size.
- E. Incorrect.
The 'pg_stat_user_indexes' system view is useful for analyzing index usage but does not provide direct insight into table fragmentation. It is more relevant for evaluating index performance.