GCP Professional Data Engineer Practice Question

A SaaS provider is re-platforming its on-premises PostgreSQL 12 cluster for a web application. The replacement must: sustain 10× higher transactional throughput at seasonal peaks; allow data scientists to run ad-hoc analytic SQL on the same data with no OLTP impact; retain full PostgreSQL compatibility (extensions and drivers); offer automatic multi-zone high availability with near-zero-RPO failover; and minimize operations while keeping migration paths to other PostgreSQL environments open. Which managed Google Cloud service best meets these needs?

Adopt BigQuery for analytics and keep a Cloud SQL for PostgreSQL instance for transactional processing.
Migrate to a multi-zone Cloud SQL for PostgreSQL instance with several read replicas to handle analytics.
Create a regional AlloyDB for PostgreSQL cluster and enable its optional columnar engine for analytic workloads.
Re-architect on Cloud Spanner using the PostgreSQL interface to obtain global scalability and strong consistency.

Report Issue

Answer Description

AlloyDB for PostgreSQL is designed for organisations that need both very high transactional throughput and fast analytics on the same operational data (hybrid transactional/analytical processing). It delivers significantly higher TPS than standard PostgreSQL and can accelerate analytic queries through its optional columnar engine, so data scientists can run complex aggregations without affecting online workloads. AlloyDB is wire-compatible with PostgreSQL, supports common extensions, offers automatic multi-zone high availability with under-minute failover and near-zero RPO, and is fully managed, reducing operational burden.

Cloud SQL for PostgreSQL maintains compatibility but cannot match the required performance and isolates analytics only through read replicas. BigQuery plus Cloud SQL would split the workload and require building pipelines, violating the single-store requirement. Spanner with the PostgreSQL interface meets HA and performance goals but diverges from upstream PostgreSQL semantics and extensions, creating potential lock-in. Therefore, AlloyDB for PostgreSQL is the most appropriate choice.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.