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 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?
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
✓
Use the STORING clause to include only the necessary columns
The STORING clause in Spanner allows you to include additional columns in the index without storing them in the index, reducing write overhead. This is used in 'covering indexes' but the STORING clause specifically stores the column in the index? Actually, STORING stores the column in the index so that queries don't need to read the base table. However, writing to the table requires updating the index, and if the index includes all columns, it's essentially a copy. To reduce overhead, you can use the STORING clause to only store necessary columns. The question asks to 'reduce the storage and write overhead' — using STORING with only needed columns reduces the index size, thus reducing write overhead. Alternatively, you could use a filtered index (partial index) but Spanner does not support filtered indexes. The correct answer is to use the STORING clause with only the columns needed.
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 index instead of a secondary index
Why it's wrong here
Spanner does not support hash indexes.
- ✓
Use the STORING clause to include only the necessary columns
Why this is correct
STORING clause allows you to define which columns are stored in the index, reducing size and write overhead.
- ✗
Drop the secondary index and rely on the primary key
Why it's wrong here
Dropping the index may affect query performance.
- ✗
Create a covering index without STORING
Why it's wrong here
Without STORING, the index still stores the primary key; to include other columns you must use STORING.
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
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 team is using Cloud Spanner with a primary key of UUID v4 values. They notice that read performance is suboptimal for range scans over a subset of keys. Which index strategy improves range scan performance?
hard- A.Add a hash prefix to the primary key
- ✓ B.Create a secondary index using INTERLEAVE IN the base table
- C.Remove the UUID primary key and use a sequential key
- D.Create a secondary index without interleaving (global index)
Why B: A secondary index with an interleaved parent stores index entries in the same tablet as the base table rows, reducing cross-node fan-out for range scans. A hash prefix would help for point lookups but not range scans. Storing the index in a separate table (global index) increases latency. Dropping the index makes range scans worse.
Variation 2. Your Cloud Spanner database has a table with a secondary index that is used for range queries. You notice that the index queries are slow because they require back-and-forth between the index and the base table. How can you optimize the index to reduce this overhead?
hard- ✓ A.Use the STORING clause to include frequently queried columns in the index.
- B.Convert the table to use hash-prefixed keys.
- C.Create a new interleaved table and move the index data there.
- D.Use the spanner_interleave_in_parent option when creating the index.
Why A: In Cloud Spanner, you can store additional columns from the base table in the secondary index using the STORING clause. This allows the index to satisfy queries without accessing the base table (covering index). INTERLEAVE IN PARENT for the index can also improve locality if the index is interleaved with a parent table. The question asks to reduce overhead of back-and-forth, which is solved by storing columns in the index.
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.