Design innovative, scalable, and highly available cloud database solutions →mediumMultiple ChoiceObjective-mapped
PCDE Practice Question: Design innovative, scalable, and highly available cloud database solutions
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?
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
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 analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Using UUID for OrderId causes random writes
Why it's wrong here
UUIDs help distribute writes, not cause hotspots.
- ✗
The primary key is too long
Why it's wrong here
Key length does not cause hotspots.
- ✗
The parent-child interleaving is not defined correctly
Why it's wrong here
Interleaving is correct; it does not cause hotspots.
- ✓
The leading key (CustomerId) is monotonically increasing
Why this is correct
Monotonically increasing leading keys cause writes to concentrate on one tablet, creating hotspots.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PCDE question from scratch — 1,446 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
2 more ways this is tested on PCDE
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. A company uses Cloud Spanner with a schema that has a table 'Orders' with primary key (CustomerId, OrderDate, OrderId). They notice hotspots on a specific customer. Which schema change would best distribute load?
medium- A.Use a secondary index on CustomerId.
- B.Split the table into multiple tables per region.
- ✓ C.Add a hash of CustomerId as a prefix to the primary key.
- D.Change primary key to OrderId only.
Why C: Hotspots occur due to concentrated traffic on a single key range, such as a specific customer's orders. Adding a hash of CustomerId as a prefix to the primary key (Option C) distributes writes across multiple splits, alleviating the hotspot. Option A (secondary index) improves read performance but does not affect write distribution. Option B (splitting by region) adds complexity without directly addressing the key-ordering issue. Option D (OrderId only) loses the natural ordering and may still cause hotspots on the most recent OrderId.
Variation 2. Your company runs a global e-commerce platform on Google Cloud Spanner. The database schema includes an 'Orders' table with primary key (OrderId, CustomerId) and an 'OrderItems' table with primary key (OrderId, CustomerId, ItemId), interleaved in parent Orders on delete cascade. During peak shopping hours, you notice that queries retrieving all items for a specific order are performing full table scans on the OrderItems table, leading to increased latency and higher CPU utilization. The queries use the OrderId as the filter condition. The database administrators have already checked that the query plans show table scans instead of using the interleaved index. You are tasked with resolving this performance issue. Which of the following actions should you take?
easy- ✓ A.Remove CustomerId from the Orders primary key (making it just OrderId) and update OrderItems to have primary key (OrderId, ItemId), maintaining interleaving.
- B.Change the primary key of Orders to (OrderId, CustomerId) and update OrderItems accordingly.
- C.Create a secondary index on OrderItems(OrderId).
- D.Increase the number of Spanner nodes to improve throughput.
Why A: The interleaved index in Cloud Spanner requires that the parent table's primary key columns be a prefix of the child table's primary key. With the original schema, queries filtering only on OrderId cannot use the interleaved index because CustomerId is missing from the filter, forcing a full table scan. By removing CustomerId from the primary key of Orders and OrderItems, OrderId becomes the leading column, allowing the interleaved index to be used for efficient point lookups.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PCDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PCDE exam.