Google Professional Cloud Database Engineer Question 197
Select 3Google Cloud PlatformYou are managing a Cloud SQL for PostgreSQL instance and notice that query performance has degraded over time. You suspect table fragmentation might be the cause. Which of the following steps should you take to assess table fragmentation?
- A
Run the
pgstattupleextension to analyze dead tuples and free space in the table. - B
Query the
pg_stat_activityview to identify long-running queries. - C
Use the
VACUUMcommand with the VERBOSE option to review table statistics. - D
Analyze the
pg_stat_user_tablessystem catalog to check for highn_dead_tupvalues. - E
Inspect the query execution plans for specific queries using
EXPLAIN.
Show answer and explanation
Correct answers: A, C, D
Explanation
Table fragmentation in PostgreSQL can lead to performance degradation due to dead tuples and unused space. To assess fragmentation, you can use tools like pgstattuple to analyze free space and dead tuples directly or review system catalogs like pg_stat_user_tables for key metrics such as n_dead_tup. Additionally, running the VACUUM command with the VERBOSE option provides detailed statistics about table cleaning, which can reveal fragmentation issues. Other tools, like pg_stat_activity or EXPLAIN, do not address fragmentation directly.
- A. Correct.
Running the
pgstattupleextension provides detailed insights into table fragmentation, including dead tuples and free space, making it a critical step in assessing fragmentation. - B. Incorrect.
The
pg_stat_activityview shows information about active queries and sessions but does not provide insights into table fragmentation. - C. Correct.
The
VACUUMcommand with the VERBOSE option outputs detailed statistics about table cleaning, including information about dead tuples and free space, which can help assess fragmentation. - D. Correct.
The
pg_stat_user_tablessystem catalog includes metrics such asn_dead_tup(number of dead tuples) that are directly related to table fragmentation. - E. Incorrect.
Query execution plans help identify inefficient queries but do not directly assess table fragmentation.