PCDE Hotspotting Practice Question

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?

⚠ Common exam trap

Candidates often think a composite key with a hash prefix and a sortable UUID is the best solution, but this does not preserve ordering by the original integer unless the UUID is specifically generated to reflect that order. The secondary index approach directly supports ordering on the original key.

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 UUID as the primary key and create a secondary index on the original integer

Using UUID as the primary key avoids hotspotting because UUIDs are random and distribute writes evenly across splits. Creating a secondary index on the original integer key allows efficient ORDER BY queries on that column. While the secondary index may experience some hotspotting due to monotonically increasing values, Spanner's automatic split management can mitigate this, and the approach meets both requirements: hotspot avoidance on the primary key and ordering capability on the original integer.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
Keep the auto-increment key but use bit-reversal
Why it's wrong here
Bit-reversal can help distribute writes but does not guarantee hotspot avoidance and makes ordering on the original integer difficult.
✗
Use a composite primary key with a hash prefix followed by the UUID
Why it's wrong here
A composite key with a hash prefix and UUID distributes writes, but ordering is based on the UUID, not the original integer. Unless the UUID is generated to preserve the original order (e.g., via ULID), this does not support ORDER BY on the original integer as required.
✓
Use the UUID as the primary key and create a secondary index on the original integer
Why this is correct
Correct. UUID primary key avoids hotspots, and a secondary index on the original integer provides the needed ordering.
✗
Use a monotonically increasing custom ID and rely on Spanner's split management
Why it's wrong here
Monotonically increasing keys cause hotspots in Spanner even with automatic split management, so this does not avoid hotspots.

Go deeper

PCDE Hotspotting Practice Question

Why each option matters

Option-by-option breakdown

Related to this question