Skip to main content
This lesson explains one of the hardest problems in sharded systems: combining related rows that live on different shards. These operations—cross-shard joins—can be slow or even unsupported by some distributed databases, so schema and system design must minimize them. Why cross-shard joins are hard
  • In a single database instance, joins are local: the database reads both tables and performs the join in-memory or on-disk on the same machine.
  • Once data is sharded, the rows to join may live on different machines. Joining them can require moving rows across the network, coordinating partial results, and merging them, which increases latency and load.
  • Some databases forbid cross-shard joins, others implement them at significant cost. Therefore, designing to avoid frequent cross-shard joins is a common performance and operational goal.
A stylized diagram labeled "Joins" showing two database tables — "chirps" (id, user_id, text) and "users" (id, name) — inside a cylinder labeled "database." A dashed arrow illustrates a join that matches user "Chris" to the chirp "Who else is on #Chirp?".
Common strategies to avoid cross-shard joins
Design for locality: put frequently-joined data on the same shard, replicate small static lookup tables, and apply targeted denormalization where it reduces cross-shard traffic. These patterns trade simplicity and storage for lower read latency.
Co-location
  • Co-location places related entities on the same shard. For example, if Chris’s user profile is stored on Shard 3, then Chris’s chirps and their likes are also stored on Shard 3.
  • This enables the database to read all relevant rows from one machine and perform local joins, giving fast reads and low cross-shard traffic.
  • Co-location often uses the same shard key (or a shard key derived from the primary entity) for related tables.
Denormalization
  • Co-location reduces cross-shard joins but doesn’t eliminate them in all cases. Example: Leo (on Shard 0) likes Chris’s post (on Shard 3). When showing Chris’s post, the likes list on Shard 3 needs Leo’s display name, which is stored on Shard 0.
  • Denormalization duplicates just the fields needed for reads — e.g., storing a small copy of Leo’s name alongside the like row on Shard 3 — so reads are local and fast.
  • Denormalization is selective: store only the frequently-accessed attributes needed to avoid a cross-shard lookup.
A dark-themed diagram titled "Denormalization" showing example database tables (Users, Chirps, Likes) with sample rows and a labeled shard, alongside a mobile app profile screen displaying a user's chirps. Arrows connect the shard/database to the app to illustrate data flow.
Denormalization improves read performance but introduces duplication. Updates to the original record (for example, Leo changing his name) require propagating changes to all duplicated copies. Typical solutions include synchronous updates, asynchronous background jobs, or accepting eventual consistency — each approach carries trade-offs in complexity and correctness.
Replicating small reference tables
  • Keep a full copy of small, mostly-static reference tables (for example, country_codes) on every shard. This lets each shard answer lookups locally without network hops.
  • This strategy is inexpensive for small tables and avoids the complexity of denormalizing many attributes.
  • It’s only appropriate for small tables that rarely change.
A dark-themed diagram titled "Small Tables" showing a small country_codes table (codes like IN, US, GB mapped to country names) replicated across four database shards labeled Shard 0–3.
When cross-shard joins are unavoidable
  • Some queries inherently require data from many users or from entities spread across shards—examples include global leaderboards, wide analytical queries, or searches across non-sharded attributes.
  • In these cases the application or a coordinating service must fetch rows from multiple shards and merge or join them. This is inherently slower than a local join.
  • A practical approach is to accept occasional slower queries and optimize for the common fast-path reads.
An infographic titled "Joins Across Shards" showing an application box at the top connecting via dashed lines to four cylindrical database shards labeled Shard 0–3. The rightmost shard (Shard 3) is highlighted with a dashed outline and a red dashed connection from the app.
Operational considerations
  • Building a full sharding stack (hash ring, bucket map, resharding/migration tools, routing layer) is complex and operationally heavy.
  • Many modern distributed databases (and managed services) already implement these components and provide features that simplify co-location, routing, and migrations.
  • Choose a database that aligns with your operational needs and sharding strategy: some databases offer transparent cross-shard queries, others encourage or automate co-location and denormalization patterns.
A stylized diagram of a distributed database showing a large yellow-outlined cylinder with four panels labeled "Hash Ring," "Bucket Map," "Migration," and "Routing Layer." Each panel contains simple icons (a ring with nodes, a bucket table, stacked disks, and a key→shard arrow) illustrating sharding and routing concepts.
Comparison of strategies Recommendations (practical rules)
  • Prefer co-location for objects with frequent joins (e.g., user → user_posts → likes).
  • Denormalize only the minimal attributes needed to avoid common cross-shard reads.
  • Replicate small static tables to every shard rather than denormalizing them into many rows.
  • Accept and isolate expensive cross-shard queries (run them in background jobs or on dedicated analytic clusters).
  • Select a database whose sharding semantics and operational properties match your consistency, latency, and resharding needs.
Further reading and references In summary: aim to keep frequently-joined data colocated, replicate small static tables, denormalize selectively to avoid cross-shard lookups, and plan for the occasional unavoidable cross-shard join. Choose a database and operational approach that align with your application’s consistency and latency requirements.

Watch Video