Design innovative, scalable, and highly available cloud database solutions →hardMultiple ChoiceObjective-mapped
PCDE Practice Question: Design innovative, scalable, and highly available cloud database solutions
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?
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
✓
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 analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use a hash prefix on UserId
Why it's wrong here
Hash prefix doesn't help ordering by timestamp.
- ✓
Create a secondary index on (UserId, Timestamp DESC) with STORING clause
Why this is correct
This index supports the query pattern efficiently without changing the primary key.
- ✗
Use a materialized view
Why it's wrong here
Spanner does not support materialized views.
- ✗
Change the primary key to (UserId, Timestamp, PostId)
Why it's wrong here
Timestamp as part of primary key can cause hotspots due to monotonic increase.
Go deeper
Related to this question
About these practice questions
One of 1,446 original PCDE practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way 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 team is designing a Cloud Spanner schema for a global social media application. The table 'Posts' has a primary key of (UserId, PostId) where PostId is a UUID. They notice write hotspots on the server with monotonically increasing UserId values. What is the most effective schema design change to distribute writes evenly?
hard- ✓ A.Add a hash prefix to the UserId to create a composite primary key like (HashUserId, UserId, PostId)
- B.Create a secondary index on PostId
- C.Place PostId first in the primary key
- D.Use a monotonically increasing integer for PostId instead of UUID
Why A: Using a hash prefix on the first part of the primary key (e.g., hash of UserId) helps distribute writes across splits, avoiding hotspots. Using a UUID for PostId is good but UserId ordering still causes hotspots. Adding a timestamp as a second part doesn't help. Interleaving with User is fine but doesn't fix the hotspot issue.
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.