Design innovative, scalable, and highly available cloud database solutions →mediumMultiple ChoiceObjective-mapped
PCDE Practice Question: Design innovative, scalable, and highly available cloud database solutions
A company wants to migrate their on-premises PostgreSQL database to Cloud SQL. The database currently runs mixed workloads: OLTP with heavy writes and occasional complex analytical queries. They want to avoid performance impact on the transactional workload. Which approach should they take?
⚠ Common exam trap
Candidates often mistakenly believe that scheduling analytical queries during off-peak hours suffices, but this still shares resources with the primary. Also, there is no such feature as an 'analytics tier' in Cloud SQL; workload isolation is achieved via read replicas.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Create a read replica of the Cloud SQL instance and run analytical queries against the replica.
To avoid performance impact on transactional workloads, a read replica of the Cloud SQL instance should be created and analytical queries directed to it. Read replicas in Cloud SQL are separate instances that replicate data from the primary asynchronously, providing isolation so that heavy analytical queries don't compete for resources with OLTP operations. This is a recommended best practice for workload separation in Cloud SQL for PostgreSQL.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Migrate to Cloud Spanner to handle both workloads using its analytics interface.
Why it's wrong here
Migrating to Cloud Spanner is unnecessary and complex; Cloud Spanner is a distributed database for horizontally scalable transactional workloads, not a direct migration path for PostgreSQL. It does not directly solve the problem of isolating analytical queries from transactional ones for a single PostgreSQL instance.
- ✗
Use Cloud SQL with the 'analytics' tier enabled.
Why it's wrong here
Cloud SQL does not offer a dedicated 'analytics' tier. Instance types are general-purpose or memory-optimized, but none isolate analytical queries at the resource level. Using read replicas is the recommended method.
- ✓
Create a read replica of the Cloud SQL instance and run analytical queries against the replica.
Why this is correct
Creating a read replica is the recommended approach to offload analytical queries. The replica handles read-only traffic, including analytics, without impacting the primary instance's OLTP performance.
- ✗
Use the same Cloud SQL instance but schedule analytical queries during off-peak hours.
Why it's wrong here
Scheduling during off-peak hours may reduce some impact but still shares resources with the primary, potentially causing contention. It does not guarantee isolation and is not the best practice.
Go deeper
Related to this question
About these practice questions
This PCDE question is part of Courseiva's 1,446-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on PCDE
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. Your company is deploying a new application on Google Cloud and needs to choose a database solution. The application requires strong transactional consistency, complex SQL queries, and the ability to scale horizontally for read-heavy workloads. Which database service should you recommend?
medium- ✓ A.Cloud Spanner
- B.BigQuery
- C.Cloud SQL
- D.Cloud Firestore
Why A: Cloud Spanner is the correct choice because it provides strong transactional consistency (ACID) across globally distributed nodes, supports complex SQL queries with standard SQL syntax, and offers horizontal scaling for read-heavy workloads through automatic sharding and read replicas. Unlike other options, Spanner uniquely combines these three requirements—strong consistency, SQL, and horizontal scaling—in a single managed service.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PCDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PCDE exam.