PCDE Design innovative, scalable, and highly available cloud database solutions — 10 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?

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

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