Cross-Shard Queries and Joins
You'll learn why queries that touch multiple shards become expensive and how to avoid or optimize them.
When you shard a database, you split data across multiple servers for scalability. But what happens when you need data from multiple shards in a single query? That's when things get complicated.
The Problem
Imagine you've sharded users by user_id. User 100 lives on Shard A, User 200 on Shard B. Now you want to find "all users who ordered in the last week" — but orders live across all shards. Your application must:
- Query every shard individually
- Gather results from each
- Merge and sort them in memory
- Apply limits/pagination after combining
This is fundamentally different from a single-database query where the database engine handles everything efficiently. Now your application becomes the query coordinator, and network latency multiplies with each shard involved.
Why Cross-Shard Joins Are Worse
Joins are even more painful. If you need to join users (sharded by user_id) with orders (sharded by order_id), there's no single location where both pieces live together. You'd need to fetch data from multiple shards, transfer it over the network, and perform the join in application memory — slow and resource-intensive.
Minimizing Cross-Shard Operations
Choose your shard key wisely (as covered in earlier lessons). Co-locate related data that's queried together. If users and their orders are always accessed together, shard both by user_id so they live on the same shard.
For unavoidable cross-shard queries, consider caching aggregated results, maintaining denormalized copies, or pre-computing common queries in a separate analytics database.
Key Takeaway: Cross-shard queries force your application to coordinate multi-server operations, making them slow and expensive — design your shard key to keep related data together whenever possible.