Scatter-Gather vs. Lookup Shards for Global Secondary Indexing

Routing Strategy Comparison

For clusters scaling beyond ten tablets where predictable tail latency is the priority, lookup shards are the superior choice. Scatter-gather routing suffers from the "long tail" effect, where the total response time is dictated by the slowest performing shard in the cluster. As the shard count increases, the probability of encountering a slow node (due to GC pauses, network jitter, or resource contention) increases linearly, causing p99 latency to degrade.

Performance Trade-offs

While lookup shards stabilize read latency, they introduce a write penalty. The decision to move away from scatter-gather depends on the ratio of reads to writes and the specific impact of write amplification.

Metric	Scatter-Gather	Lookup Shard
Read Latency	Increases with shard count	Constant (Targeted)
Write Overhead	Minimal (Primary only)	High (Primary + Index)
Cluster Load	High CPU/Network broadcast	Localized to index shard

The Write Volume Threshold

There is no universal numeric threshold for write volume, as it depends on your hardware and the size of the secondary index. However, the overhead of a lookup shard typically outweighs its benefits when the write amplification causes the index shard to become the primary bottleneck for the entire system.

You have reached this threshold when:

• The IOPS or CPU utilization of the lookup shard exceeds 80% while primary shards remain underutilized.
• The latency added by the dual-write (or asynchronous index update) exceeds the latency saved by avoiding the broadcast.
• Write throughput drops significantly due to locking contention on the secondary index table.

Implementation and Verification

To determine the optimal path for your specific workload, perform the following scoped tests:

1. Tail Latency Baseline: Measure p99 latency for secondary key queries. Incrementally increase shard count from 10 to 20 and observe the slope of latency increase.
2. Write Saturation Test: Implement a lookup table and monitor the vttablet metrics for the index shard. Compare the maximum sustainable writes per second against the scatter-gather baseline.
3. Network Analysis: Monitor packets per request at the VTGate level to quantify the reduction in broadcast traffic.

Diagnostic Requirement: To refine this recommendation, please provide the current Read:Write ratio for the secondary key queries. If the workload is write-heavy (e.g., >10:1 writes to reads), the write amplification of a lookup shard may be prohibitive regardless of shard count.