PCDOE Design and Plan Database Solutions Practice Test 3 — 15 Questions

Question 1

A financial services company runs a high-frequency trading application that requires strong consistency, horizontal scalability, and low-latency transactions across multiple regions. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is a globally distributed, strongly consistent, horizontally scalable relational database service designed for mission-critical applications like trading. Cloud SQL is not multi-region, Bigtable does not support SQL/ACID, and Firestore is not relational.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

Firestore

Question 2

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?

Accepted Answer

15 GB. Cloud SQL PostgreSQL recommends max_connections = RAM_MB/16, but this is a soft limit. More importantly, memory must accommodate the working set and buffer pool. With 500 connections, 8 GB working set + 4 GB buffer pool = 12 GB, but PostgreSQL also needs overhead. A safe minimum is 15 GB, but the smallest Cloud SQL tier with >12 GB is 15 GB (e.g., db-custom-2-15360). However, among the options, 15 GB is the only viable choice. Note: max_connections formula suggests 15GB RAM gives ~960 connections, which covers 500.

Answer

12 GB

Answer

8 GB

Answer

26 GB

Question 3

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. Hotspots occur when writes are concentrated on a small range of keys. Since OrderId is a UUID, it's already random. However, using CustomerId as the first part of the primary key can cause hotspots if certain customers place many orders. But more commonly, if OrderItems uses the same CustomerId and OrderId, and many items are inserted for the same order, they will be interleaved and written sequentially. Still, the hotspot is due to the leading key CustomerId being monotonically increasing if customers are assigned IDs sequentially. The best answer is that the primary key design leads to concentrated writes because CustomerId is not distributed well. However, the question likely expects that the primary key design is correct (UUID) but the hotspot might be due to not using a hash prefix. Actually, in Spanner, the first key part should be distributed. If CustomerId is sequential (e.g., auto-increment), it causes hotspots. So the cause is a monotonically increasing leading key. The correct answer should point to the leading key being monotonically increasing.

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 4

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 a composite primary key with a hash prefix followed by the UUID. To avoid hotspots, the leading part of the primary key should be a hash prefix. To preserve ordering, you can use a composite key where the first part is a hash bucket (e.g., mod 100) and the second part is the UUID. This distributes writes while allowing ordering by the UUID (though not strictly sequential). Another option is to use a monotonically increasing key but with a salt, but that still may cause hotspots. The best practice is to use a hash prefix. The correct answer is to prepend a hash of the UUID as the first key column.

Answer

Keep the auto-increment key but use bit-reversal

Answer

Use the UUID as the primary key and create a secondary index on the original integer

Answer

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

Question 5

A startup needs a database for a global user base with low-latency reads and writes, strong consistency, and the ability to scale horizontally without downtime. They anticipate variable traffic. Which Google Cloud database service meets these requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner provides global distribution, strong consistency, horizontal scaling, and no downtime for schema changes or scaling. Cloud SQL is not global, Bigtable does not have strong consistency, and Firestore is not global with strong consistency for multi-region.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Firestore

Question 6

You are running Cloud Bigtable for time-series analytics. Each row represents a metric and uses a row key of format 'metricID#timestamp' (e.g., 'cpu_usage#2023-08-01T00:00:00Z'). You notice that writes are concentrated on a small number of nodes. What is the most effective way to distribute writes more evenly?

Accepted Answer

Add a hash prefix of the metricID to the row key. The row key design is poor because metricID is a limited set (e.g., cpu_usage) and timestamp is increasing, so all writes for a metric go to a single tablet. Salting by prepending a hash of the metricID (or using a field promotion with a hash prefix) distributes writes across tablets. Reversing timestamp helps with reads but not write distribution. Using a different column family does not affect row key distribution. Increasing nodes only helps if data is distributed, but the hotspot will remain.

Answer

Use a different column family for each metric

Answer

Increase the number of nodes

Answer

Reverse the timestamp in the row key

Question 7

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 is a PostgreSQL-compatible database that includes a columnar engine for analytical queries, providing HTAP capabilities with minimal performance impact on OLTP. BigQuery requires exporting data, which adds latency and overhead. Read replicas still run on MySQL engines optimized for OLTP. Spanner is overkill and requires migration. AlloyDB is the best fit for HTAP.

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 8

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 9

A company is migrating a large on-premise Hadoop workload to Google Cloud. The data is stored in HBase and consists of time-series logs with 10 PB of data. They need a fully managed NoSQL solution with high throughput and low latency. Which migration path should they choose?

Accepted Answer

Use Cloud Bigtable and the HBase client. Cloud Bigtable is the fully managed, scalable NoSQL database that is compatible with HBase API, making migration straightforward. Cloud Spanner is relational, not the best for time-series logs. BigQuery is for analytics, not real-time. Firestore is document-based and not suited for petabyte-scale logs.

Answer

Migrate HBase tables to Cloud Spanner using Dataflow

Answer

Export data to BigQuery using Storage Transfer Service

Answer

Re-architect as Firestore collections

Question 10

Which database service should you use if your workload requires complex JOINs, ACID transactions, and you want to avoid operational overhead of managing patching and backups?

Accepted Answer

Cloud SQL. Cloud SQL is a fully managed relational database service for MySQL, PostgreSQL, and SQL Server that handles patching, backups, and replication. AlloyDB also is managed, but Cloud SQL is more common for standard OLTP. Firestore and Bigtable are NoSQL.

Answer

Cloud Bigtable

Answer

Firestore

Answer

Cloud Storage

Question 11

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. To efficiently query posts for a user in descending order of timestamp, you need the timestamp to be part of the primary key after UserId. However, using PostId (UUID) as the second part doesn't help ordering. You can add a timestamp column and create a secondary index with descending order, but that adds write overhead. Another approach is to change the primary key to (UserId, Timestamp, PostId) and use a separate mechanism to avoid hotspots (e.g., hash prefix on Timestamp). But the simplest improvement is to use a secondary index on (UserId, Timestamp DESC). The question asks to improve read performance; a secondary index with storing clause can provide good performance. The best answer is to create a secondary index on UserId and Timestamp with STORING to include other columns.

Answer

Use a hash prefix on UserId

Answer

Use a materialized view

Answer

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

Question 12

A company runs an e-commerce website on Cloud SQL. They want to scale read traffic without impacting write performance and need high availability across zones. Which configuration should they use?

Accepted Answer

Use a regional Cloud SQL instance with automatic failover and add read replicas. Cloud SQL offers read replicas for scaling read traffic and regional (multi-zone) instances for high availability. Using a regional instance with automatic failover provides HA; adding read replicas offloads reads. A cross-region replica adds latency, and a single zone with increased tier does not provide HA.

Answer

Create a cross-region replica and use it for reads

Answer

Increase the machine tier of the existing instance

Answer

Migrate to Cloud Spanner for automatic read scaling

Question 13

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. You can use Cloud Bigtable for high-throughput write ingestion of user interactions, and then export to BigQuery for analytics. Alternatively, Bigtable can be used with Cloud Dataflow for streaming analytics, but the question asks for databases. Another option is AlloyDB with its columnar engine for HTAP, but that may not handle the extreme write throughput of Bigtable. The best combination is Bigtable for writes and BigQuery for analytics. Firestore is not suitable for high write throughput. Spanner could be used but is more expensive and not as fast for writes as Bigtable for this use case.

Answer

Cloud Spanner

Answer

Cloud SQL

Answer

Firestore

Question 14

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. To avoid hotspots in Spanner, avoid monotonically increasing keys. ProductId should be a UUID or hash. Also, using a hash prefix on ProductId as the first part of the key can help distribute writes. Interleaving is fine. Secondary indexes with STORING are for reads. Reverse timestamps are for Bigtable. The correct choices are using UUID and adding a hash prefix.

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 15

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. Zero-downtime migration to Spanner typically involves using Strangler Fig pattern: replicate writes from MySQL to Spanner, validate data, then cutover. Key steps: 1. Export schema and convert to Spanner DDL (using HarbourBridge). 2. Set up live migration using Dataflow for continuous replication. 3. Validate data consistency (e.g., using checksums). Taking an export snapshot is fine but not for zero-downtime; using pgloader is for PostgreSQL; creating a Cloud SQL read replica is not directly relevant.

Answer

Use pgloader to migrate the data

Answer

Create a Cloud SQL read replica for fallback