Google Cloud Professional Cloud Database Engineer — Question 166
Your e-learning platform runs on a Cloud SQL for PostgreSQL instance (16 VCPUs, 60 GB memory and 1TB SSD) serving users in North America. Your analytics team runs complex reporting queries that often consume 80% of CPU resources, causing slow response times for student transactions during peak hours. Current workload includes 8,000 transactions per second with 60% reads and 40% writes. The reporting queries involve JOIN operations across multiple large tables with millions of rows requiring highly efficient analytical processing. The platform also experiences sudden spikes in analytical reporting demand, requiring an elastic scaling of read capacity. You need to improve the query performance for your analytics team to run their reports efficiently without impacting transactional users. You also need to plan for future traffic growth. What should you do?
Answer options
- A. Upgrade to Cloud SAL for PostgreSQL Enterprise Edition and route transaction and analytical queries to the primary instance.
- B. Migrate to AlloyDB for PostgreSQL and upgrade the machine type of the primary instance to 32 vCPU. Create materialized views for common analytical queries.
- C. Migrate to AlloyDB for PostgreSQL with a 16 vCPU primary instance and enable columnar engine for analytical workloads, using read pools for query isolation.
- D. Upgrade the Cloud SQL for PostgreSQL instance to db-n1-highmem-32 and implement connection pooling with PgBouncer to handle increased load.
Correct answer: C
Explanation
Option C is correct because migrating to AlloyDB for PostgreSQL and enabling the columnar engine specifically supports analytical workloads, which improves query performance and allows for better isolation of read queries. The read pools further enhance this by managing the workload effectively. The other options either do not address the need for analytical processing optimization or do not provide the necessary scalability and efficiency for the reporting demands.