Course contentsShow
← System Design
Lesson 254 of 1,9197. Database Scaling: ShardingFree lesson

Cross-Shard Queries and Joins

Understand why queries spanning multiple shards are expensive and how to minimize or optimize them.

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:

  1. Query every shard individually
  2. Gather results from each
  3. Merge and sort them in memory
  4. 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.