PCDE Design innovative, scalable, and highly available cloud database solutions Practice Test 2 — 15 Questions

Question 1

You are designing a Cloud SQL for PostgreSQL instance for an OLTP application. The application typically handles 500 concurrent connections and the working set is 8 GB. You estimate a buffer pool of 4 GB. What minimum memory allocation should you choose?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

15 GB. For Cloud SQL for PostgreSQL, memory allocation must account for the buffer pool (shared_buffers) plus overhead for connections, OS, and other processes. With 500 connections and an 8 GB working set, a 4 GB buffer pool is estimated, but the total memory should be at least 15 GB to accommodate connection overhead and ensure performance.

Answer

12 GB

Answer

8 GB

Answer

26 GB

Question 2

An e-commerce platform uses Cloud Spanner with a table Orders and a child table OrderItems. The primary key of Orders is (CustomerId, OrderId) where OrderId is a UUID. The primary key of OrderItems is (CustomerId, OrderId, ItemId). However, writes to OrderItems are creating hotspots. What is the most likely cause?

Accepted Answer

The leading key (CustomerId) is monotonically increasing. The hotspot is caused by the leading key (CustomerId) being monotonically increasing. In Cloud Spanner, if the first part of a primary key is monotonically increasing (e.g., a sequential customer ID), all writes for new customers will be directed to the same split, creating a hotspot. This is because Spanner splits data based on key ranges, and a monotonically increasing key causes new rows to be written to the end of the key space, overloading a single server.

Answer

Using UUID for OrderId causes random writes

Answer

The primary key is too long

Answer

The parent-child interleaving is not defined correctly

Question 3

A team is migrating a MySQL OLTP database to Cloud Spanner. The existing schema uses auto-increment primary keys. They plan to convert them to STRING columns with UUIDs. However, the application also relies on ORDER BY on the original integer key. How should they preserve ordering while avoiding hotspots in Spanner?

Accepted Answer

Use the UUID as the primary key and create a secondary index on the original integer. To preserve ORDER BY on the original integer key while avoiding hotspots, the engineer should keep the UUID as the primary key (which distributes writes) and create a secondary index on the original integer column. This allows efficient ordered queries on the integer without making it the primary key, so write traffic remains spread across splits.

Answer

Keep the auto-increment key but use bit-reversal

Answer

Use a composite primary key with a hash prefix followed by the UUID

Answer

Use a monotonically increasing custom ID and rely on Spanner's split management

Question 4

A company uses Cloud SQL for MySQL and wants to run complex analytical queries on the same data without affecting OLTP performance. They need a solution with minimal data movement and low operational overhead. Which approach should they take?

Accepted Answer

Use AlloyDB for PostgreSQL with its columnar engine. AlloyDB for PostgreSQL includes a columnar engine that automatically accelerates analytical queries by storing data in a columnar format in memory, while still supporting OLTP workloads on the same data. This provides a single database that can handle both transactional and analytical queries without affecting OLTP performance, with minimal data movement and low operational overhead. The columnar engine is a built-in feature that intelligently routes analytical queries to the columnar store, avoiding the need for separate systems or ETL. Thus, it directly addresses the requirement for complex analytics on the same data with minimal movement and low overhead.

Answer

Migrate to Cloud Spanner

Answer

Export data to BigQuery periodically

Answer

Set up a Cloud SQL read replica and run analytical queries on it

Question 5

You are designing a Bigtable schema for a messaging application where users have conversations. Each row represents a message with row key 'userID#conversationID#timestamp'. The application queries the most recent messages for a given conversation. How should you modify the row key to optimize for this query pattern?

Accepted Answer

Reverse the timestamp. To get the most recent messages, you want to scan recent rows. If timestamp is increasing, the most recent messages have the highest timestamp but at the end of the scan range. By reversing the timestamp, you make recent messages appear first in a scan. Field promotion doesn't apply here. Salting would help writes but not the read pattern. The best approach is to reverse the timestamp so that most recent messages have lexicographically smaller keys.

Answer

Promote the conversationID before userID

Answer

Store timestamp as a column instead of part of row key

Answer

Use a hash prefix of the conversationID as the first part

Question 6

You are designing a Spanner schema for a social media application. The table Posts has primary key (UserId, PostId) where PostId is a UUID. The application frequently queries all posts for a given user, ordered by timestamp descending. The current schema uses PostId as the second part of the key, which is random. How can you improve read performance for this query pattern?

Accepted Answer

Create a secondary index on (UserId, Timestamp DESC) with STORING clause. A secondary index on (UserId, Timestamp DESC) with a STORING clause lets Spanner serve the 'all posts for a user ordered by timestamp descending' query directly from the index, avoiding a full table scan and the random PostId ordering. The STORING clause includes the columns needed by the query (e.g., PostId, content) so the index is covering and no base-table lookup is required. This is the standard Spanner pattern for optimizing read patterns that differ from the primary key order.

Answer

Use a hash prefix on UserId

Answer

Use a materialized view

Answer

Change the primary key to (UserId, Timestamp, PostId)

Question 7

A media streaming company is designing a database for user recommendations. They expect high write throughput for user interactions and need to run complex analytical queries on the same data for personalization. They want a fully managed solution with minimal latency for writes. Which TWO services can be combined to meet these requirements?

Accepted Answer

Cloud Bigtable. Cloud Bigtable (D) is correct because it is a fully managed, high-throughput NoSQL wide-column database designed for low-latency writes at massive scale, making it ideal for ingesting high-volume user interaction events. BigQuery (E) is correct because it is a fully managed, serverless analytical data warehouse that can run complex SQL queries over large datasets, so combining Bigtable for fast writes with BigQuery for analytics meets both the write-throughput and personalization-query requirements. Together they fit the scenario: Bigtable handles the write-heavy operational workload with minimal latency, while BigQuery provides the analytical engine for complex personalization queries. Cloud Spanner (A) is not marked correct because, although it is fully managed and horizontally scalable, it is a strongly consistent relational database optimized for transactional workloads rather than the high-write-throughput plus separate complex-analytics combination described. Cloud SQL (B) is not marked correct because it is a managed relational database with limited horizontal write scalability and is not intended for massive write throughput or large-scale analytical processing. Firestore (C) is not marked correct because, while it is a fully managed NoSQL document database with low-latency reads/writes, it is not designed for the complex analytical queries over large datasets that personalization requires.

Answer

Cloud Spanner

Answer

Cloud SQL

Answer

Firestore

Question 8

You are designing a Spanner schema for a global inventory system. The table Products has primary key (ProductId STRING). The table Inventory has primary key (ProductId STRING, WarehouseId INT64) and is interleaved in Products. You expect high write throughput on Inventory. Which TWO design choices will help avoid hotspots?

Accepted Answer

Use UUID for ProductId. Option A is correct because using a UUID for ProductId produces non-sequential, uniformly distributed key values, so inserts into Products and its interleaved Inventory rows spread across Spanner splits instead of concentrating on the last key range. Option B is correct because adding a hash prefix of ProductId as the first key part in Inventory distributes writes across many key ranges, directly preventing hotspotting on high-write Inventory data. Option C is not appropriate here because reversing a timestamp addresses sequential timestamp keys, but this schema has no timestamp key component. Option D is wrong because a monotonically increasing sequence for ProductId creates a hot last split as new keys are always appended at the high end. Option E is not a hotspot-avoidance technique for the base table's writes; a secondary index on WarehouseId with STORING adds write amplification and does not fix the primary key distribution problem.

Answer

Reverse the timestamp in the row key

Answer

Use a monotonically increasing sequence for ProductId

Answer

Create a secondary index on WarehouseId with STORING

Question 9

A company wants to migrate a 5 TB MySQL database to Cloud Spanner with zero downtime. They need to validate schema and data consistency before switching traffic. Which THREE steps should they include in the migration plan?

Accepted Answer

Set up a Dataflow pipeline to replicate changes from MySQL to Spanner. Option D is correct because HarbourBridge is Google's open-source tool specifically designed to convert MySQL (and PostgreSQL) schemas into Cloud Spanner DDL, which is a required first step when migrating a MySQL database to Spanner. Option A is correct because a Dataflow pipeline (using the Spanner change-streams or JDBC-to-Spanner templates) enables continuous replication of ongoing changes from MySQL to Spanner, which is essential for achieving zero-downtime migration while the source remains live. Option E is correct because validating data consistency via checksums (e.g., comparing row counts and aggregate hashes between MySQL and Spanner) is the recommended way to verify data integrity before cutting over traffic. Option B is not appropriate because pgloader targets PostgreSQL, not MySQL-to-Spanner migrations. Option C is not appropriate because a Cloud SQL read replica provides fallback for MySQL, not a migration or validation mechanism for Spanner.

Answer

Use pgloader to migrate the data

Answer

Create a Cloud SQL read replica for fallback

Question 10

A startup is building a mobile app backend with high-concurrency user authentication and profile updates. They need strong consistency and ACID transactions. Which database service should they choose?

Accepted Answer

Cloud SQL. Cloud SQL is the correct choice because it provides a fully managed relational database (MySQL, PostgreSQL, SQL Server) that enforces strong consistency and full ACID transaction support, which is essential for high-concurrency authentication and profile updates. Firestore offers strong consistency for single-document reads but is a NoSQL document store with limited multi-document transaction semantics and no traditional ACID guarantees across arbitrary queries. BigQuery and Cloud Bigtable are analytics/NoSQL stores that do not provide the transactional row-level consistency required for user authentication workloads.

Answer

Firestore

Answer

BigQuery

Answer

Cloud Bigtable

Question 11

A company wants to run complex analytical queries on terabytes of sales data with sub-second query response times for dashboards. Data is updated frequently in near real-time. Which combination of services is most appropriate?

Accepted Answer

AlloyDB with columnar engine. AlloyDB with its columnar engine supports both high-speed transactions and fast analytical queries on the same data, fulfilling near-real-time analytics requirements.

Answer

Cloud Spanner with interleaved tables

Answer

Cloud SQL for MySQL with read replicas

Answer

Bigtable with aggregation queries

Question 12

An engineer is designing a Cloud Spanner table for a global user activity tracking system with high write throughput. Which primary key design is BEST to avoid hotspots?

Accepted Answer

UUID string (generated by application). A UUID string generated by the application is the best primary key design for a global user activity tracking system with high write throughput because UUIDs are randomly distributed, avoiding the hotspotting caused by monotonically increasing keys. Cloud Spanner distributes data by key ranges, so random keys spread writes evenly across splits and nodes, maximizing throughput.

Answer

Monotonically increasing integer (INT64) with auto-increment

Answer

Composite key with user_id as first part

Answer

Timestamp as primary key

Question 13

A Cloud Bigtable instance stores high-volume time-series data. Write throughput is at node capacity, but read latency spikes occasionally. The row key pattern is 'timestamp#device_id'. Which optimization should be applied first?

Accepted Answer

Reverse the row key to device_id#timestamp. The row key 'timestamp#device_id' causes sequential writes to be concentrated on a single tablet because timestamps are monotonically increasing, creating a hot spot. Reversing to 'device_id#timestamp' distributes writes across many tablets based on device_id hash prefixes, eliminating the hotspot and improving both write throughput and read latency. This is the canonical Bigtable schema design fix for time-series workloads.

Answer

Add more nodes to the cluster

Answer

Enable SSD storage

Answer

Use a single column family

Question 14

A company needs to perform real-time analytics on streaming data from IoT devices with millisecond latency for alerts, and also run complex historical analytics. Which Google Cloud database architecture supports both?

Accepted Answer

AlloyDB with columnar engine. AlloyDB with columnar engine handles real-time inserts and fast analytical queries on the same data, ideal for HTAP workloads.

Answer

Cloud Bigtable for real-time and BigQuery for analytics

Answer

Cloud SQL (read-only replica) for analytics

Answer

Cloud Spanner with interleaved tables

Question 15

An engineer is designing a Bigtable row key for global user events. They want to avoid hotspots and enable efficient queries by user_id and time range. Which row key design is best?

Accepted Answer

hash(user_id) + timestamp. Bigtable sorts rows lexicographically by row key, so a monotonically increasing key like a raw timestamp or sequential user_id creates a hotspot on a single tablet. Prepending hash(user_id) distributes writes uniformly across tablets while still allowing efficient per-user range scans when combined with the timestamp suffix. This satisfies both the anti-hotspot requirement and the query pattern of 'events for user X in time range Y'.

Answer

reverse(timestamp) + user_id

Answer

user_id + timestamp

Answer

timestamp + user_id