Google Professional Cloud Database Engineer exam dumps

Google Professional Cloud Database Engineer practice question 195 of 259

Professional Cloud Database Engineer. Professional level, Google Cloud. Free question with the correct answer and a full explanation.

Google Professional Cloud Database Engineer Question 195

Select 2Google Cloud Platform

You are managing a Cloud SQL database running PostgreSQL, and you notice that query performance has degraded over time. Upon investigation, you suspect table fragmentation is impacting performance. Which of the following steps should you take to assess and address table fragmentation?

  1. A

    Use the pg_stat_user_tables system view to check the dead_tuples column for a high count.

  2. B

    Run the ANALYZE command to immediately reduce fragmentation in the affected table.

  3. C

    Use the pgstattuple extension to calculate the live tuple percentage and evaluate table bloat.

  4. D

    Check the size of indexes with the pg_indexes_size() function to identify fragmentation issues.

  5. E

    Run the VACUUM FULL command to rebuild the table and remove fragmentation.

Show answer and explanation

Correct answers: A, C

Explanation

Table fragmentation in PostgreSQL can result in degraded query performance due to an increase in dead tuples and bloat. To assess fragmentation, you should monitor the dead_tuples column in the pg_stat_user_tables system view and use tools like the pgstattuple extension to calculate the live tuple percentage and table bloat. These methods allow you to determine whether fragmentation is an issue before taking corrective actions such as VACUUM FULL or ANALYZE.

  • A. Correct.

    Correct: The pg_stat_user_tables view provides information about dead tuples in a table, which is a strong indicator of table fragmentation. A high count of dead tuples suggests the need for action.

  • B. Incorrect.

    Incorrect: The ANALYZE command collects statistics for the query planner but does not reduce fragmentation or address dead tuples.

  • C. Correct.

    Correct: The pgstattuple extension can be used to assess table fragmentation by calculating the percentage of live tuples and identifying table bloat.

  • D. Incorrect.

    Incorrect: The pg_indexes_size() function measures the size of indexes but does not directly relate to assessing table fragmentation.

  • E. Incorrect.

    Incorrect: While VACUUM FULL can reduce table fragmentation, this is an action step, not a method for assessing fragmentation.

Timed practice exam

Take a Google Professional Cloud Database Engineer practice test under exam conditions

60 questions in 120 minutes, drawn from this bank, with a score report and a per-question review when you finish.

Start timed exam

More Google Professional Cloud Database Engineer practice questions

  1. 196.You are managing a Cloud SQL for PostgreSQL database that has been running for several months....
  2. 197.You are managing a Cloud SQL for PostgreSQL instance and notice that query performance has degraded...
  3. 198.You are managing a Cloud Spanner database that has been in production for over a year. Over time,...
  4. 199.You are managing a Cloud SQL instance and need to schedule automated exports of your database to a...
  5. 200.Your team manages a Cloud SQL PostgreSQL database and needs to schedule regular backups of the data...
Back to the Google Professional Cloud Database Engineer exam dumps