PCDE Design innovative, scalable, and highly available cloud database solutions — Timed 40 Questions

Question 1

A company is migrating an on-premise PostgreSQL database to Cloud SQL. The current database has a connection pool with 200 connections and uses 64 GB RAM. According to Cloud SQL best practices, what is the maximum recommended max_connections setting?

40 minute time limit — choose an answer to begin.

Accepted Answer

4096. The formula for max_connections on Cloud SQL is RAM_MB / 16. 64 GB = 65536 MB. 65536 / 16 = 4096. However, the question asks for the maximum recommended setting based on RAM. Note: This formula is for Cloud SQL; the actual max_connections default may vary, but the formula is a best practice guide.

Answer

200

Answer

64

Answer

1024

Question 2

A company is running a financial application on Cloud Spanner and needs to ensure strong transactional consistency across regions. The application requires both high write throughput (2000 mutations/second) and read throughput (2000 reads/second). According to Spanner capacity planning, how many processing units (PUs) are needed for the combined workload?

Accepted Answer

1 processing unit (PU). Spanner capacity: write throughput 2000 mutations/second per PU, read throughput 2000 reads/second per PU. For 2000 writes/s and 2000 reads/s, you need 1 PU for writes and 1 PU for reads, but the reads and writes share the same PU. Actually, the formula is: PUs needed = max(write_throughput/2000, read_throughput/2000). For 2000 each, that's max(1,1)=1 PU. However, the question states 'high write throughput (2000 mutations/second) and read throughput (2000 reads/second)'. So 1 PU is sufficient. But note: in practice, you might need more for other factors. The question tests the calculation. The answer is 1 PU.

Answer

2 processing units (PUs)

Answer

4 processing units (PUs)

Answer

0.5 processing units (PUs)

Question 3

You are running a Cloud Spanner instance and notice that a secondary index is causing performance issues for write operations. The index includes all columns of the table. Which Spanner feature can reduce the storage and write overhead of the index?

Accepted Answer

Use the STORING clause to include only the necessary columns. Using the STORING clause in a secondary index allows you to include only the necessary columns as stored (non-key) columns, reducing the index size and write overhead compared to indexing all columns. This is more efficient than indexing every column.

Answer

Use a hash index instead of a secondary index

Answer

Drop the secondary index and rely on the primary key

Answer

Create a covering index without STORING

Question 4

You are planning a Cloud Bigtable cluster for a workload requiring 100,000 reads per second and 50,000 writes per second. The data will be stored on HDD. How many nodes are needed for the projected throughput? (Assume each node provides 10,000 QPS for reads or writes.)

Accepted Answer

10 nodes. For Cloud Bigtable, each node provides 10,000 QPS for reads or writes. The workload requires 100,000 reads per second and 50,000 writes per second. Since reads and writes are handled separately, the number of nodes needed is determined by the maximum of the two: 100,000 reads / 10,000 QPS per node = 10 nodes, and 50,000 writes / 10,000 QPS per node = 5 nodes. Therefore, 10 nodes are needed to handle the read throughput.

Answer

20 nodes

Answer

5 nodes

Answer

15 nodes

Question 5

A company is migrating a monolithic application to Google Cloud and needs to modernize the database layer. The application has both OLTP (high-volume transactions) and OLAP (complex reporting) workloads. The team wants to use a single database to simplify operations but with high performance for both. Which TWO Google Cloud database services support hybrid transactional/analytical processing (HTAP)? (Choose two.)

Accepted Answer

AlloyDB. AlloyDB (C) is correct because it is Google Cloud's PostgreSQL-compatible HTAP database, using a disaggregated storage engine with a columnar accelerator that lets it run fast OLTP transactions while serving analytical queries on the same data without impacting transactional performance. Cloud Spanner (D) is correct because it is a globally distributed, horizontally scalable relational database that supports strong consistency for high-volume OLTP and also provides analytical capabilities (including columnar secondary indexes and integration with BigQuery/Spanner Data Boost) for reporting workloads on the same database. Cloud Bigtable (A) is a wide-column NoSQL store optimized for high-throughput key-based reads/writes, not for complex SQL analytics, so it is not an HTAP relational database. BigQuery (B) is a serverless OLAP data warehouse designed for analytics, not for high-volume transactional OLTP workloads. Cloud SQL (E) is a managed MySQL/PostgreSQL/SQL Server instance suited for traditional OLTP but lacks built-in HTAP acceleration for complex analytical queries at scale.

Answer

Cloud Bigtable

Answer

BigQuery

Answer

Cloud SQL

Question 6

You are designing a Cloud Bigtable row key for a social media feed where users see posts from friends. Queries are: get posts for a user (by user_id) ordered by timestamp most recent first, and get posts for a specific topic (by topic_id) ordered by timestamp. To support both access patterns efficiently, which TWO design strategies are appropriate? (Choose two.)

Accepted Answer

Create two tables: one with row key user_id#reverse_timestamp and another with topic_id#reverse_timestamp. Option B is correct because Cloud Bigtable has no secondary indexes, so supporting both the user_id and topic_id access patterns requires separate tables whose row keys begin with the query prefix: user_id#reverse_timestamp for per-user feeds and topic_id#reverse_timestamp for per-topic feeds, with reverse timestamps enabling most-recent-first scans. Option D is correct because it describes the same denormalization strategy — duplicating each post into two tables, one keyed for the user access pattern and one keyed for the topic access pattern, which is the standard Bigtable approach for multiple read patterns. Option A is not appropriate because a row key starting with user_id#topic_id#timestamp cannot efficiently serve topic-only queries, since topic_id is not the leading component and Bigtable can only seek by row-key prefix. Option C is wrong because Cloud Bigtable does not support secondary indexes on topic_id. Option E is wrong because hashing the user_id destroys the natural ordering needed for most-recent-first retrieval and still does not support topic-based queries.

Answer

Use a single table with a row key composed of user_id#topic_id#timestamp

Answer

Create a single table and use a secondary index on topic_id

Answer

Use a row key that starts with a hash of the user_id and then includes topic_id and timestamp

Question 7

You are designing a schema for Cloud Spanner and need to model a one-to-many relationship between Customers and Orders. Which THREE features or practices should you consider? (Choose three.)

Accepted Answer

Create a secondary index on Orders.customer_id to speed up queries. Option C is correct because creating a secondary index on Orders.customer_id allows Cloud Spanner to efficiently locate all orders belonging to a given customer without scanning the entire Orders table, which is essential for one-to-many lookups. Option D is correct because the STORING clause lets the index store additional frequently queried columns (e.g., order_date or total) directly in the index, avoiding extra base-table lookups and reducing latency for common queries. Option E is correct because parent-child interleaving physically co-locates child Orders rows with their parent Customers row using the Customers primary key as a prefix, which optimizes joins and range reads for the one-to-many relationship while enabling efficient locality. Option A is not among the correct answers because Cloud Spanner foreign key constraints, while supported, are not a required design practice for modeling one-to-many relationships and add write-time overhead. Option B is not correct because monotonically increasing integer primary keys for Orders create hotspots in Cloud Spanner; instead, keys like UUIDs or bit-reversed sequences should be used to distribute writes.

Answer

Use a foreign key constraint to ensure referential integrity

Answer

Use a monotonically increasing integer as the primary key for Orders

Question 8

A company is migrating an on-premises OLTP application to Google Cloud. The application requires high concurrency (up to 5,000 simultaneous connections) and uses a relational schema with strong transactional integrity. Which database service is the MOST suitable?

Accepted Answer

Cloud SQL. Cloud SQL supports up to 4,096 connections by default (configurable) and provides full transactional integrity. For 5,000 connections, Cloud SQL can be configured with appropriate instance sizing. Spanner also supports transactions but is overkill for a single-region OLTP migration. BigQuery is for analytics, Firestore is NoSQL.

Answer

Firestore

Answer

BigQuery

Answer

Cloud Spanner

Question 9

You need to design a Spanner schema for a social media application that stores user posts. Each post has a unique ID, author ID, timestamp, and content. The primary access pattern is querying all posts for a given author in reverse chronological order. Which schema design minimizes the risk of hotspotting?

Accepted Answer

Primary key: (AuthorId, PostId) where PostId is generated using a UUID. Using a composite primary key with AuthorId as the first part distributes writes across splits. However, adding Timestamp as the second key causes monotonically increasing writes for a single author, leading to hotspotting on the last split. Instead, a random component like a UUID (as in option A) distributes writes evenly across splits, minimizing hotspot risk. Option A's UUID provides sufficient randomness to avoid sequential writes while still enabling efficient queries per author via the prefix.

Answer

Primary key: (AuthorId, HashOfPostId)

Answer

Primary key: (AuthorId, Timestamp) with Timestamp stored in descending order

Answer

Primary key: (PostId) with a secondary index on AuthorId

Question 10

You are designing a Bigtable schema for a time-series application that records sensor readings every second. Queries always filter by device ID and time range. To avoid hotspotting and ensure recent data is retrieved quickly, which row key design is MOST effective?

Accepted Answer

Row key: (hash(deviceID)#deviceID#reverseTimestamp). The row key design (hash(deviceID)#deviceID#reverseTimestamp) is most effective because it distributes writes across multiple nodes by hashing the device ID, preventing hotspotting, while still allowing efficient queries by device ID and time range. The reverse timestamp ensures that the most recent data is stored first within each device's row range, enabling fast retrieval of recent readings. This design balances write scalability and read performance for time-series data.

Answer

Row key: deviceID#timestamp

Answer

Row key: (reverseTimestamp#deviceID)

Answer

Row key: timestamp#deviceID

Question 11

A company needs to migrate its on-premises Oracle database to a fully managed relational database on Google Cloud. The application uses stored procedures and requires high availability across multiple zones. Which migration path is MOST appropriate?

Accepted Answer

Migrate to Cloud SQL for PostgreSQL and configure cross-zone high availability. Cloud SQL for PostgreSQL with cross-zone high availability is the most appropriate path because it provides a fully managed relational database service with automatic failover across zones, supports stored procedures (PL/pgSQL), and eliminates the operational burden of patching, backups, and replication management. Database Migration Service can handle the schema and data migration from Oracle to PostgreSQL, making this a viable managed migration path.

Answer

Use Database Migration Service to migrate to Cloud SQL for SQL Server

Answer

Use Bare Metal Solution to run Oracle Database on dedicated hardware

Answer

Migrate to AlloyDB for PostgreSQL with cross-region replication

Question 12

A team is designing a Spanner schema for an online gaming leaderboard. The leaderboard stores player scores and requires high write throughput. Which primary key design is BEST to avoid write hotspots?

Accepted Answer

Primary key: (HashOfPlayerId, Timestamp). Monotonically increasing keys (like score or timestamp) cause hotspotting. Using a random prefix (e.g., hash of player ID) ensures writes are distributed across splits. Player ID alone might also cause hotspotting if many players write simultaneously.

Answer

Primary key: (PlayerId, Timestamp)

Answer

Primary key: (Score, PlayerId)

Answer

Primary key: (Timestamp, PlayerId)

Question 13

You need to estimate the number of Bigtable nodes required for a workload of 50,000 reads per second (QPS) and 20,000 writes per second. Each node can handle 10,000 QPS for reads or writes. Storage is not a constraint. What is the minimum number of nodes required?

Accepted Answer

5 nodes. Bigtable node requirements are driven by the higher of read or write QPS relative to per-node capacity. With 50,000 reads/s and 20,000 writes/s, and each node handling 10,000 QPS, the read load requires 50,000 / 10,000 = 5 nodes, while writes require 20,000 / 10,000 = 2 nodes. The minimum is therefore 5 nodes, since reads are the binding constraint.

Answer

2 nodes

Answer

7 nodes

Answer

10 nodes

Question 14

A company is designing a database solution for a global social media application that requires strong consistency, high write throughput, and complex relational queries. Which TWO Google Cloud databases should they consider? (Choose 2)

Accepted Answer

Cloud Spanner. Cloud Spanner (C) is correct because it is a globally distributed, horizontally scalable relational database that provides strong external consistency (via TrueTime) and high write throughput, making it ideal for a global social media application needing relational queries. AlloyDB for PostgreSQL (E) is also correct because it is a fully managed, PostgreSQL-compatible relational database engineered for high-performance transactional workloads, supporting complex SQL queries with strong consistency and high throughput. Cloud Bigtable (A) is not suitable because it is a wide-column NoSQL store optimized for high-throughput analytical and time-series workloads, not complex relational queries or strong multi-row consistency. BigQuery (B) is an analytics data warehouse designed for OLAP queries, not transactional strong-consistency write workloads. Firestore (D) is a document NoSQL database with eventual consistency in multi-region configurations and lacks the complex relational query capabilities required here.

Answer

Cloud Bigtable

Answer

BigQuery

Answer

Firestore

Question 15

A gaming company is designing a leaderboard using Cloud Spanner. The leaderboard updates scores in real time and supports queries for top players. Which THREE strategies should they implement to achieve high performance and avoid hotspots? (Choose 3)

Accepted Answer

Use global secondary indexes with STORING clause to avoid back-to-base lookups. Option A is correct because a global secondary index with a STORING clause lets Spanner serve leaderboard queries directly from the index without a back-to-base lookup, reducing latency and read amplification for top-player queries. Option D is correct because maintaining a separate, smaller table for top scores and updating it asynchronously isolates the hot read path from the high-volume score-ingest path, so ranking queries stay fast and don't contend with every score write. Option E is correct because adding a hash prefix to the primary key distributes writes across splits, preventing the hotspot that a monotonically increasing key would create on the last split. Option B is wrong because a monotonically increasing primary key concentrates all new inserts at the end of the key space, which is exactly the hotspot pattern to avoid. Option C is wrong because a single table relying on Spanner auto-scaling does not by itself eliminate hotspots or optimize leaderboard reads, and auto-scaling addresses capacity rather than key distribution.

Answer

Use a monotonically increasing primary key for scores

Answer

Use a single table with all data and rely on Spanner auto-scaling

Question 16

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. 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 17

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 18

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 19

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 20

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