Google Professional Cloud Database Engineer Question 193
Select 2Google Cloud PlatformYou are managing a Cloud SQL for PostgreSQL instance that has started experiencing slower query performance due to increased database size and fragmentation. To ensure optimal performance, which actions should you take as part of database maintenance?
- A
Perform a VACUUM operation to reclaim storage and reduce table fragmentation.
- B
Increase the CPU and memory allocation of the Cloud SQL instance.
- C
Analyze the database to refresh query planner statistics using the ANALYZE command.
- D
Restart the Cloud SQL instance to clear its cache and reload configurations.
- E
Regularly back up the database to protect against data loss.
Show answer and explanation
Correct answers: A, C
Explanation
To maintain optimal performance in a Cloud SQL for PostgreSQL instance, you should focus on database-specific maintenance tasks like performing VACUUM to minimize fragmentation and running ANALYZE to update query planner statistics. These actions directly address performance degradation due to database growth and changes. While increasing resources or restarting the instance might help in some cases, they are not targeted solutions for fragmentation or outdated statistics.
- A. Correct.
Performing a VACUUM operation is critical for PostgreSQL databases, as it helps reclaim storage from deleted rows and reduces table fragmentation, which can improve query performance.
- B. Incorrect.
Increasing resource allocation may help with general performance issues but does not directly address database fragmentation or query planner inefficiencies in this context.
- C. Correct.
Running the ANALYZE command is essential for updating the query planner's statistics, enabling it to make better decisions for query optimization, especially after changes to the database.
- D. Incorrect.
Restarting the Cloud SQL instance does not directly address query performance issues caused by database fragmentation or outdated statistics, and is generally not required unless there are specific configuration changes.
- E. Incorrect.
Regular backups are important for disaster recovery but do not improve query performance or address fragmentation and outdated statistics.