Cross-Shard Queries

The Inevitable Question

 sharded by user_id. then someone asks:

 "top 10 trips by fare this month"      ← no user_id filter!
 "count active users by city"
 "all trips delayed > 15 min today"

 no shard key → EVERY shard must be asked:
 
 [coordinator] ──► shard-1 ─┐
               ──► shard-2 ─┼─► merge/sort/aggregate ──► answer
               ──► shard-N ─┘
 
 SCATTER-GATHER: latency = slowest shard; load = all shards;
 cost grows WITH cluster size — the anti-scaling property.

Why Scatter-Gather Is a Design Smell

 quantified pain:

 32 shards × query = 32 concurrent heavy scans per user question
 - tail latency: one straggler shard holds the answer hostage
 - capacity: dashboards × analysts = constant fleet-wide load
 - hot-shard interaction amplifies (slowest shard = hottest usually)

 scatter-gather for INTERACTIVE paths is architecture debt.
 for rare admin/debug use: acceptable. know the difference.

Pattern 1: Route to Analytics Instead

 analytical questions belong in analytical stores:

 [shards] ──CDC──► [warehouse: snowflake/bigquery/clickhouse]
                          │
              "top 10 by fare this month?" → columnar scan, sub-second

 rules of thumb:
 - interactive + cross-shard + aggregations → warehouse/read-model
 - latency budget seconds+ → definitely warehouse
 - DON'T make OLTP shards serve OLAP shapes. ever.

 this is polyglot persistence applied to queries.

Pattern 2: Precomputed Read Models

 maintain answers incrementally as writes happen:

 city_stats table updated per trip-write (CDC or app-level):
   {city, day, trip_count, revenue, top_routes...}
 cross-shard question → single read-model lookup ✓

 = materialized views via streaming pipelines.
 staleness bounded by pipeline lag (seconds typical).
 THE standard production answer for live cross-shard aggregates.

Pattern 3: Bounded Scatter-Gather

 when you MUST scatter-gather interactively, bound it:

 - PARALLEL with per-shard timeouts: slowest shard's data
   marked incomplete rather than blocking forever
 - TOP-K pushdown: each shard returns ITS top 10;
   coordinator merges 32×10 → true top 10 (heap merge)
   instead of shipping everything
 - PREDICATE pushdown: filters evaluated shard-side,
   minimal bytes cross the network
 - cache the aggregate with short TTL (dashboards accept 30s)

 these make it SURVIVABLE, never GOOD.

Decision Table

Cross-shard need	Answer
Live dashboard aggregates	Streaming read models
Deep analytics/ad-hoc	Warehouse
Rare admin lookup	Bounded scatter-gather
Global uniqueness check	Id-design (embed shard) not queries

Interview Framing

Every sharding design earns ONE guaranteed follow-up: “now show me top users.” Scored reflexes: name scatter-gather costs immediately, route analytics to warehouse, propose CDC-built read models for live aggregates, demonstrate top-k pushdown knowledge if pressed. Designs that pre-declared an ANALYTICS PATH in their diagram simply point at it — that’s the senior move: answered before asked.