Google Professional Cloud Database Engineer Question 189
Single answerGoogle Cloud PlatformYou are managing a Cloud SQL for PostgreSQL instance that supports a critical application. To improve database performance, you need to reorganize and remove fragmentation of tables without causing significant downtime. Which of the following actions should you take?
- A
Use the
VACUUMcommand with theFULLoption to reclaim space and analyze table statistics. - B
Run the
VACUUM ANALYZEcommand to optimize table access patterns and update query planner statistics. - C
Export the database data, drop the fragmented tables, and reimport the data into newly created tables.
- D
Enable a read replica temporarily, perform maintenance on the primary instance, and promote the read replica.
Show answer and explanation
Correct answer: B
Explanation
The VACUUM ANALYZE command is the most appropriate option for performing database maintenance on a Cloud SQL for PostgreSQL instance while minimizing downtime. It reorganizes tables, optimizes query planner statistics, and avoids locking tables, making it ideal for a critical application requiring high availability.
- A. Incorrect.
The
VACUUM FULLcommand reclaims space but locks the table for the duration of the operation, causing significant downtime. This is not suitable for a critical application requiring minimal disruption. - B. Correct.
The
VACUUM ANALYZEcommand optimizes table access and updates query planner statistics without causing table locks, making it suitable for minimizing downtime during database maintenance. - C. Incorrect.
Exporting the data, dropping tables, and reimporting is a manual and time-consuming process, leading to significant downtime. It is not an efficient maintenance solution.
- D. Incorrect.
Using a read replica for maintenance might solve some issues, but it does not address table fragmentation or optimize performance directly. This approach is inappropriate for the described scenario.