Query Optimization: The Hidden Cost of Cross-Shard Joins

In a sharded database, the "Scatter-Gather" query is the silent performance killer. When you perform a join on columns that aren't the shard key, your proxy has to broadcast the query to every single shard.

1. The P99 Tail Latency

If you have 100 shards, your query is only as fast as the slowest shard. A single server running a backup will stall your entire web request.

2. Solution: Global Secondary Indexes (GSI)

Instead of searching across shards, maintain a central index.

• The Cost: Writes are now slower (you must update the GSI).
• The Gain: Reads are always routed to exactly one shard.

3. Denormalization (The Senior Approach)

Duplicate the data you need for the join directly into the target table. Yes, it costs more disk, but it turns a cross-shard join into a local row-read.