Schema Design for Sharding: Avoid Cross-Shard Joins (2026)

Choosing the right primary key is the single most important decision you’ll make when designing a sharded database, and it’s often the reason cross-shard joins become inevitable.

Let’s see how this plays out in a real-world scenario. Imagine we’re building an e-commerce platform. We have users and orders.

-- User Table
CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

-- Order Table
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    order_date TIMESTAMP NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL
);

If we shard our database based on user_id, all orders for a given user will live on the same shard. This is great for querying a user’s orders: SELECT * FROM orders WHERE user_id = 12345;. This query hits only one shard.

But what if we need to find all users who placed an order on a specific date, say 2023-10-27? The query would look like this:

SELECT u.user_id, u.username
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.order_date = '2023-10-27';

If users and orders are sharded independently on user_id, this JOIN becomes a cross-shard operation. The database has to scatter the query to every shard, ask each shard for orders on that date, collect the user_ids from those orders, and then, for each user_id, go to the users table (potentially on a different shard) to retrieve the username. This is incredibly inefficient.

The problem arises because the JOIN condition (orders.user_id = users.user_id) is not the sharding key for both tables.

The Solution: Co-location

The fundamental principle to avoid cross-shard joins is co-location. This means ensuring that related data, which you’ll frequently join, resides on the same shard.

How to Achieve Co-location:

1. Sharding Key Alignment: The most straightforward way is to use the same sharding key for all tables that will be joined. In our e-commerce example, if both users and orders are sharded by user_id, then all orders for a given user_id will always be on the same shard as that user’s users record.

   • Diagnosis: Inspect your sharding strategy. If users is sharded on user_id and orders is sharded on order_id (or some other independent key), you have a problem.
   • Fix: Re-shard your orders table to use user_id as its sharding key.
   • Why it works: When orders and users share the same sharding key, any query joining them on that key will only need to access the shard corresponding to that specific user_id.

2. Composite Sharding Keys (Carefully): Sometimes, you might need to join on multiple fields, or your primary join field isn’t the natural primary key for all tables. You can use composite sharding keys. For instance, if you have customers and transactions, and you often query transactions by customer_id and transaction_type, you might shard on (customer_id, transaction_type).

   • Diagnosis: If your join involves fields that, when combined, uniquely identify the data you want to co-locate.
   • Fix: Define a composite sharding key that includes all fields used in frequent, critical joins. For example, SHARD BY HASH(customer_id, transaction_type).
   • Why it works: This ensures that all transactions of a specific type for a given customer land on the same shard.

3. Denormalization (When Co-location Isn’t Enough): If co-location is impossible or impractical due to complex relationships or varying access patterns, you might duplicate data. For example, if you need to display the user’s username directly in the orders table for quick display without a join, you could add a username column to orders.

   • Diagnosis: When a join is unavoidable and co-location is technically infeasible or extremely complex to maintain.
   • Fix: Add redundant columns to tables. For example, add username to the orders table. You’ll need a mechanism (e.g., triggers, background jobs) to keep this denormalized data consistent.
   • Why it works: Eliminates the need for a join by embedding the necessary data directly.

4. Application-Level Joins (Last Resort): If the database cannot be structured for co-location, your application can perform the join. Fetch data from one shard, then issue subsequent queries to other shards based on the results.

   • Diagnosis: When database-level sharding and denormalization are not options, and cross-shard joins are unavoidable.
   • Fix: Write application code that fetches data from multiple shards and performs the join in memory or through multiple database calls.
   • Why it works: Puts the complexity of managing cross-shard data into the application layer. This is generally the least performant and most complex to maintain.

5. Materialized Views or Pre-aggregated Data: For complex aggregations or reporting queries that span sharded data, pre-compute the results.

   • Diagnosis: For analytical or reporting queries where real-time results aren’t strictly necessary, and the join/aggregation is computationally expensive.
   • Fix: Create a separate table or materialized view that stores the aggregated or joined data. Populate it periodically via batch jobs.
   • Why it works: Pre-calculating the result means queries against the materialized view are fast, as they don’t involve complex joins or aggregations across shards.

6. Consider the "Hot Shard" Problem: If your sharding key is something like timestamp or user_id and you have a very active user or a surge of activity at a particular time, that shard can become a bottleneck. A good sharding strategy balances load. Sometimes, a composite key or a globally unique ID that’s then mapped to a shard can help distribute load better.

   • Diagnosis: Monitoring reveals one or a few shards are consistently under much higher load than others.
   • Fix: Re-evaluate your sharding key. Consider a composite key that distributes load more evenly, or use a technique like hashing a UUID to determine the shard, which tends to distribute data more randomly.
   • Why it works: A more uniform distribution of data and query load across shards prevents individual shards from becoming performance bottlenecks.

The core idea is to ensure that any query that needs to combine data from two tables does so on a shard that contains all the necessary data for that join. This usually means making sure the sharding key for both tables is the same, or at least related in a way that guarantees co-location.

If you successfully co-locate all your frequently joined tables on user_id, the next problem you’ll likely encounter is how to handle queries that need to join tables sharded on different keys, or how to manage schema evolution in a sharded environment.