Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
AlloyDB HTAP Scaling with PostgreSQL Compatibility
Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
An enterprise is migrating a mission-critical transactional database from on-premises to Google Cloud. A thorough analysis of the database workload patterns and performance requirements reveals the following characteristics:
- Workload profile: Read-intensive transaction processing with frequent complex analytical queries, aggregations, and high concurrency.
- Throughput and latency targets: Must sustain over 400,000 read transactions per second (TPS) with average query latency below 1 millisecond.
- Hardware and scaling constraints: Workload requires scaling compute to 64 vCPUs; traditional open-source PostgreSQL instances hit concurrency bottlenecks beyond 16 vCPUs.
- Compatibility: Full compatibility with PostgreSQL schemas, extensions, and standard SQL query syntax is required without redesigning data models for distributed horizontal sharding.
Which Google Cloud database service best satisfies these workload characteristics?
Explanations
Cloud SQL for PostgreSQL
Cloud SQL for PostgreSQL
What Cloud SQL for PostgreSQL is
Cloud SQL for PostgreSQL is a fully managed database service delivering standard open-source PostgreSQL on managed Google Cloud compute and persistent disk infrastructure.
Why it doesn't meet the requirement
- Vertical scaling limitations: Standard open-source PostgreSQL architectures generally do not scale linearly beyond 16 vCPUs due to internal engine concurrency limits and memory lock contention.
- Throughput ceiling: Cloud SQL cannot achieve the 400,000+ read TPS with sub-millisecond average latency demanded by this scenario on a single primary instance.
- Analytical query acceleration: It lacks a native vectorized columnar engine to accelerate complex aggregation and reporting queries mixed into transactional workloads.
Key characteristics and limitations
- Coupled compute and storage: Uses standard persistent disks rather than a distributed log-structured storage engine.
- Read replicas required for scale: Scaling read operations requires adding multiple independent read replicas, increasing administrative complexity and replication lag.
- Scale-down constraints: Scaling down compute instances more than once in a three-hour window causes regular downtime events.
This answer would be correct if
The workload had moderate throughput requirements (e.g., under 20,000 TPS), fit comfortably within smaller compute sizes (16 vCPUs or fewer), and did not execute heavy analytical queries concurrently.
Additional context
While Cloud SQL supports tools like Query Insights and Gemini Cloud Assist to optimize queries and detect lock wait issues, it is architecturally bounded by traditional PostgreSQL engine performance.
Cloud Spanner
Cloud Spanner
What Cloud Spanner is
Cloud Spanner is a globally distributed, horizontally scalable NewSQL database providing external consistency (ACID compliance) across multi-region and global deployments.
Why it doesn't meet the requirement
- Sub-millisecond latency profile: Spanner is designed for high-availability distributed consensus (Paxos), typically operating with low single-digit millisecond latency rather than sub-millisecond read latency.
- Data model re-architecture: Taking full advantage of Spanner requires designing schemas around interleaved tables and careful primary key partitioning to prevent hotspotting, violating the requirement to preserve existing PostgreSQL schemas without structural redesign.
- Cost and complexity overhead: Deploying Spanner for a single-region workload requiring specialized PostgreSQL extension compatibility introduces unnecessary operational complexity.
Key characteristics and limitations
- Horizontal node scaling: Scales compute and storage horizontally across multiple processing nodes rather than pure vertical core scaling.
- Distributed transaction management: Employs TrueTime and distributed locks to ensure serializability across nodes.
- Observability tools: Provides system tables such as
SPANNER_SYS.READ_STATS_TOTAL_MINUTEto troubleshoot CPU utilization and locking delay.
This answer would be correct if
The enterprise required multi-region active-active horizontal scaling, petabyte-scale storage, and global transactional consistency across geographic regions.
Additional context
Spanner provides a PostgreSQL interface dialect, but its underlying storage engine and distributed execution mechanics are entirely different from traditional native PostgreSQL.
AlloyDB for PostgreSQL
AlloyDB for PostgreSQL
What AlloyDB for PostgreSQL is
AlloyDB for PostgreSQL is a fully managed, PostgreSQL-compatible relational database service designed for demanding enterprise transactional and mixed analytical workloads (HTAP).
How it meets the requirements
- Sub-millisecond latency and extreme throughput: Benchmark testing demonstrates that an AlloyDB instance can achieve over 467,000 read TPS on select-only benchmarks with average latencies as low as 0.547 ms.
- Linear CPU scalability: Unlike standard open-source PostgreSQL engines, which traditionally experience diminishing returns and lock bottlenecks when scaling beyond 16 vCPUs, AlloyDB decouples compute and storage while optimizing the database engine to scale linearly up to 64 vCPUs and beyond.
- Complex analytical queries: AlloyDB features an integrated columnar engine and intelligent query processing that accelerates analytical scans and joins on transactional data without requiring ETL.
- Full PostgreSQL compatibility: It provides 100% compatibility with standard PostgreSQL syntax, data types, and extensions, eliminating the need to re-architect schemas.
Key advantages and capabilities
- Disaggregated storage architecture: Storage automatically grows and scales dynamically independent of compute nodes.
- High concurrent read performance: Seamlessly processes hundreds of concurrent client connections with minimal lock contention.
- Automated caching and memory management: Optimized shared buffers allocate up to 8 GB RAM per vCPU for efficient data caching.
- Integrated machine learning: Offers built-in vector search and AI-assisted performance tuning via Gemini Cloud Assist.
Why this is optimal
AlloyDB is engineered specifically for workloads that outgrow standard relational instances but require strict PostgreSQL compliance and ultra-low read latency without the operational overhead of horizontal distributed re-sharding.
BigQuery
BigQuery
What BigQuery is
BigQuery is an enterprise, serverless, highly scalable cloud data warehouse designed specifically for analytical data processing (OLAP) and large-scale aggregations using SQL.
Why it doesn't meet the requirement
- Incompatible with OLTP workloads: BigQuery is not an operational transactional database and cannot serve as the backing data store for high-throughput transactional applications requiring sub-millisecond point queries.
- Latency profile: BigQuery query execution involves distributed query coordination and job scheduling, yielding response times measured in hundreds of milliseconds or seconds, failing the sub-millisecond latency requirement.
- High-concurrency updates: BigQuery is optimized for bulk appending and large batch mutations, not sustained point writes or high-concurrency row-level transaction rates (TPS).
Key characteristics and limitations
- Columnar storage format: Uses Capacitor storage to maximize analytical scan performance across massive datasets.
- Stateless execution engine: Allocates dynamic compute slots to process parallel table scans across petabytes of data.
- Batch and streaming ingestion: Supports streaming inserts, but does not provide standard transactional isolation for OLTP applications.
This answer would be correct if
The enterprise needed an append-only analytical warehouse or data lakehouse to run complex reporting and business intelligence across terabytes or petabytes of historical enterprise data.
Additional context
BigQuery is frequently paired alongside operational databases like AlloyDB or Cloud SQL, where operational data is replicated to BigQuery for downstream enterprise analytics.
Try 10 interactive questions like this one, free
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
- 1,353 practice questions across all 5 exam domains
- Every answer explained: why it is right, and why the others are not
- Scout, an AI tutor that answers your follow-up questions
- Readiness tracking by domain, so you know when to book the exam
Related questions
Keep the momentum going with these hand-picked practice scenarios
AdvancedProfessional Data Engineer
Cloud Spanner Columnar Engine
Analyzing data access patterns
AdvancedProfessional Data Engineer
Bigtable Row Key vs BigQuery Partitioning Clustering
Analyzing data access patterns
AdvancedProfessional Data Engineer
Cloud Composer Environment Snapshots
Running jobs in multiple regions or zones
AdvancedProfessional Data Engineer
Cloud DLP InspectTemplate and StoredInfoType
Privacy (e.g., strategies to handle personally identifiable information)
AdvancedProfessional Data Engineer
Pub/Sub Schema with BigQuery Dead-Letter Topic
Data validation
FundamentalAssociate Data Practitioner
CMEK vs CSEK vs GMEK
Identify use cases for customer-managed encryption keys (CMEK), customer-supplied encryption keys (CSEK), and Google-managed encryption keys (GMEK)
Last updated Validated