Cross-Shard Query Degradation

A query that cannot be answered from one shard forces the application to scatter the query to every shard, gather the partial results, and merge them itself. Latency becomes the slowest of N shards rather than one lookup, connection pool usage becomes N slots instead of one, and the throughput benefit sharding bought for single-shard queries does not apply to this query pattern at all.

Horizontal sharding routes a query to exactly one shard when the query's predicate includes the shard key: O(1) lookup regardless of total data size, independent of shard count. A query that omits the shard key, or that needs to combine results that live on different shards, cannot be routed that way. The application (or a sharding proxy layer) must scatter the query to all N shards, gather each shard's partial result, and merge them in application memory. This is scatter-gather, and it is the read-side mirror of what hot_shard_unbalanced_writes covers on the write side.

Tail latency is max-of-N, not O(1). The fan-out issues N queries in parallel, so the request's latency is bounded below by the slowest of the N shard responses, not the average. As N grows, the odds that at least one shard is having a slow moment (a GC pause, a competing query, a warm cache miss) rise with it, so cross-shard query tail latency gets worse as the cluster scales out, which is the opposite of what sharding is supposed to buy you. At 10 shards this is a minor tax; at 1,000 shards, 1,000 simultaneous connections commonly exceed connection pool limits before the query even returns.

Local versus global secondary indexes. A local secondary index is built per shard on that shard's own data; it is cheap to maintain (no cross-shard coordination) but a query on the indexed attribute still has to fan out to every shard, because each shard only knows about its own slice of the index. A global secondary index maintains a single index structure covering the whole dataset, usually stored and partitioned separately from the primary data; it answers the same query with a single lookup, but every write to the indexed attribute must now also update the global index, which adds write latency and consistency lag between the primary data and the index. Choosing local versus global secondary indexing is choosing which side, reads or writes, absorbs the cross-shard cost.

Merge cost is not just concatenation. A plain union of N result sets is cheap, but a query with ORDER BY and LIMIT (a top-N query) requires each shard to return its own top-N candidates, and the application must then merge those N pre-sorted lists and take the true top-N: a k-way merge, not a concatenation. A COUNT or SUM aggregate must sum N partial aggregates, which is cheap, but a DISTINCT or a JOIN across shards can require holding much larger intermediate sets in application memory before the final result is known, and this merge step is serial even though the shard queries ran in parallel.

Triggers in practice: admin or reporting queries that aggregate across all users ("total revenue today"), searches on an attribute that is not the shard key ("find users named John Smith"), and relationship traversals that follow a foreign key onto a different shard.