Deep Dive into Sharding: Shard Keys and Cross-Shard Queries

When many teams decide that "the database can no longer handle the load," their first reaction is to "add sharding." But sharding has never been as simple as "split the data evenly and everything will be fine." The real challenge is not splitting the data, but querying it after the split. One critical choice determines all of this: the shard key.

This article does not explain "what sharding is." Instead, it covers the details that become painful only after a system goes live: how to choose a shard key, handle cross-shard queries, issue global IDs, and why my advice for most projects is to avoid sharding for now.

1. The Shard Key Is the Lifeblood of Sharding

A shard key is the column that determines which shard stores a row. Once selected, it largely determines every subsequent query pattern, scaling capability, and hotspot distribution in the system.

Why does choosing the wrong key mean "full scans and data skew"? Because query routing after sharding works like this:

SELECT * FROM orders WHERE user_id = 123;
-- Middleware: hash(123) % N → routes to shard 3 → queries only this shard

As long as the WHERE clause includes the shard key, the middleware can route the query precisely to one shard, at roughly the same cost as querying a single database. But if the shard key is absent, the middleware can only broadcast the SQL statement to all N shards, query each one independently, and merge the results. This is the notorious "scatter-gather" pattern: one query becomes N queries, latency multiplies by N, and the connection pool is overwhelmed.

Data skew is more subtle. If you choose an unevenly distributed key, such as sharding by city, shards for Beijing, Shanghai, Guangzhou, and Shenzhen will overflow while those for Tibet and Qinghai remain empty. The supposed "horizontal scaling" then becomes meaningless: hot shards are overwhelmed, cold shards sit idle, and adding nodes cannot solve the problem.

Remember this: choosing a shard key is not about selecting a column that can split the data. It is about selecting the column that lets most queries route precisely while distributing data evenly. This is a tradeoff with no universal answer, only the answer that best fits your application's read and write patterns.

2. Three Iron Rules for Shard-Key Design

In practice, I usually evaluate a shard key against three criteria:

1. High cardinality (even distribution). The candidate column must have enough distinct and widely distributed values to spread rows evenly across shards after hashing. Primary keys such as user_id and order_id naturally have high cardinality. Columns such as gender with only M/F, status with a handful of enum values, or country with only dozens of values are disastrous because they severely overload certain shards. A low-cardinality column must never be used alone as a shard key.

2. Query routing (the WHERE clause usually includes the shard key). An ideal shard key should appear in more than 95% of query WHERE clauses. In consumer applications, most queries request "a user's orders/messages/posts," making user_id the natural first choice. In multi-tenant SaaS, every query includes tenant_id, making it the obvious choice. Conversely, if order_id is the shard key but queries frequently need to "list orders by user," most queries will degrade into broadcasts.

3. Avoid hotspots (be careful with monotonically increasing columns). Auto-increment IDs and timestamps may look evenly distributed as shard keys, but they create serious problems: all new writes concentrate on the "last shard" because IDs and time increase monotonically. That shard remains a permanent write hotspot while the others sit idle. This is why sharding directly by timestamp is almost always wrong. You need to hash it or add a random prefix to spread the writes.

-- Bad example: shard by auto-increment id → new data always lands on the last shard
SHARD KEY: id (auto_increment)

-- Good example: shard by user_id, naturally distributing user writes
SHARD KEY: user_id

3. Common Shard-Key Choices

These are several typical choices I have encountered and used in practice:

• user_id (the default choice for consumer applications). For e-commerce orders, instant messages, and social-media posts, queries are almost always scoped to a user. user_id has both high cardinality and frequent presence in WHERE clauses. The tradeoff is that statistics spanning users, such as "yesterday's platform-wide GMV," become cross-shard aggregations that require special handling.

• tenant_id (the standard answer for multi-tenant SaaS). Each customer's data is naturally isolated, and all within-tenant queries hit a single shard. However, beware of a "very large tenant": one major customer's data volume may exceed a single shard's capacity, requiring another level of compound sharding with sub_id.

• hash(id) / hash(some_key). This provides the most even distribution but has no business semantics. Any query by a business field degrades into a broadcast. It is usually appropriate only for pure KV data that is always queried by id, such as user avatars or file metadata.

• Compound shard keys, such as tenant_id + user_id. First route by tenant to a shard group, then split again by user within that group. This is the standard technique for handling major differences in data volume between tenants.

In one sentence: follow the queries, not the data. List your ten most frequent SQL statements and identify the column they share in their WHERE clauses. That column is probably your shard key.

4. Four Cross-Shard Query Nightmares

With the right shard key, 90% of queries can route precisely. The remaining 10% of cross-shard queries, however, can make you question every architectural decision. They fall into four main categories:

1. Cross-shard JOINs (almost impossible). Shards are physically isolated, independent databases, so SQL cannot JOIN across them directly. If an "orders table sharded by user_id" must JOIN a "products table sharded by product_id," there is no obvious destination for the query. The usual response is application-level merging: query both shards separately and join the results in memory with a hash map. Alternatively, denormalize the data by storing the product name redundantly in the orders table.

2. Cross-shard aggregation (COUNT/SUM/MAX). SELECT COUNT(*) FROM orders must be broadcast to every shard. Each shard returns a count, and the application adds them together. Simple aggregations are manageable, but complex ones such as COUNT(DISTINCT user_id) are extremely difficult. You cannot simply add each shard's distinct count because the same user may exist on multiple shards. These queries usually require pre-aggregated tables or an offline data warehouse.

3. Cross-shard pagination (global LIMIT/OFFSET is hell). ORDER BY created_at LIMIT 10 OFFSET 1000 looks harmless, but obtaining global rows 1001 through 1010 after sharding requires fetching the first 1010 rows from every shard, for a total of N×1010 rows, globally merging and sorting them in the application, and only then selecting the requested page. The larger the OFFSET, the higher the cost; deep pagination can cause an OOM failure. The usual solution is cursor-based keyset pagination instead of OFFSET, or simply allow pagination only by shard key, so each user's order pagination always remains on one shard.

4. Cross-shard DISTINCT (almost unsolvable). There is no silver bullet for distributed deduplication. Usually, you must either accept an approximation with HyperLogLog or avoid the query entirely.

5. Three Main Strategies for Cross-Shard Queries

Because cross-shard queries are so painful, engineering teams generally use three techniques:

1. Denormalization (redundant storage). Copy frequently joined fields into the main table. For example, store product_name and user_name directly in the orders table to eliminate cross-shard JOINs. The cost is more expensive updates, because renaming a product requires updating every order, but it is highly effective for read-heavy, write-light workloads. This is the most commonly used technique in sharded systems.

2. CQRS (separate read and write models). Shard the write database by shard key to preserve write throughput. On the read side, use dedicated indexing services, materialized views, Elasticsearch, or a columnar store such as ClickHouse for complex queries. Each database does what it is best at, with binlog/CDC synchronizing data between them. This is the standard architecture for large systems that must both "write by user" and "query by many different dimensions."

3. Restrict cross-shard queries (API-layer constraints). The simplest and most effective option is to prohibit cross-shard queries at the product level. Require administrators to select a user before querying orders in the back-office interface. Send statistical reports through a T+1 offline data warehouse rather than querying the online database directly. Accept sharding's limitations and work around them through product design instead of fighting them head-on.

6. Globally Unique IDs: Auto-Increment Is Dead; Long Live Snowflake

The single-database era's AUTO_INCREMENT fails completely after sharding. Every shard maintains an independent counter, so primary-key collisions are inevitable. A sharded system therefore needs a globally unique ID scheme.

There are two mainstream choices:

Snowflake algorithm. A 64-bit integer = timestamp (41bit) + machine ID (10bit) + sequence number (12bit). Its values trend upward, which is friendly to B+ tree insertion; it avoids distributed collisions and generates IDs locally without network overhead, making it the de facto industry standard. Machine ID allocation must be controlled through a configuration center or ZooKeeper. Otherwise, two machines with the same ID will generate collisions. Many companies adapt Snowflake to produce 64bit strings or add business-specific prefixes.

UUID. It is fully decentralized and costs nothing to generate, but it has two major flaws: it is completely unordered, causing severe page splits during B+ tree insertion and degrading write performance, and it consumes substantial space at 128bit versus 64bit. Consequently, UUID is almost never used as a database primary key. It is more common as an application-level resource identifier, such as a file ID or session ID.

Other options include centralized schemes such as database ID-range allocation and Redis INCR. They are simple but introduce a single point of failure and a performance bottleneck. For most sharded systems, Snowflake or one of its variants is the best default choice.

7. Consistent Hashing: The Key to Dynamic Scaling

How data routes to shards also matters. The simplest method is hash(key) % N, where N is the number of shards. It is straightforward but has a fatal flaw: when N changes, almost all data must be migrated. Expanding from four shards to five requires moving about 80% of all keys, an almost unacceptable cost in production.

Consistent Hashing solves this problem. It organizes the hash space as a ring and maps both nodes and keys onto that ring. A key belongs to the first node encountered clockwise. As a result, adding or removing one node affects only the adjacent segment of keys on the ring, so approximately 1/N of the data moves instead of the entire dataset. Virtual nodes, with each physical node mapped to 100-200 virtual nodes, also help prevent data skew.

In practice, Vitess, Cassandra, Redis Cluster, and MongoDB Sharding all use consistent hashing or a variant of it. Consistent hashing is mandatory whenever a system needs dynamic scaling. However, modulo routing is sufficient if you can plan the shard count once, such as creating 1024 virtual shards initially mapped to four physical databases, and later change only the mapping without changing the shard count. This common engineering technique is called "pre-sharding."

8. Practical Advice: 90% of Projects Should Not Shard Yet

After covering all these difficult aspects of sharding, here is the cold reality: most projects do not need sharding at all.

The real capacity ceiling is much higher than you might think. A modern database server with primary-replica separation, sensible indexes, table partitioning (Partition), and Redis caching can comfortably handle terabytes of data and tens of thousands of QPS. The root causes of most "slow database" problems are bad indexes, bad SQL, N+1 queries, missing caches, and unpartitioned hot tables. All of these problems can be fixed on a single database and have nothing to do with sharding.

The cost of sharding, meanwhile, is enormous:

• Operational complexity explodes: Backup and recovery, DDL changes, Schema evolution, and cross-shard transactions are all distributed hell.
• Business constraints become rigid: You cannot add JOINs casually, change the shard key freely, or rely on cross-shard transactions.
• Once sharded, there is almost no way back: The shard key is effectively immutable, data migration is extremely difficult, and the architecture becomes locked in.

My practical advice is to exhaust the capacity of a single database first through read/write splitting, vertical separation into databases by business domain, partitioning hot tables, adding caching, and optimizing SQL and indexes. Consider sharding only when write volume or data volume genuinely exceeds the physical limits of a single database and will continue growing for the next two to three years. Even then, prioritize vertical separation by business domain, such as separate order, user, and product databases. Horizontal sharding should always come last.

Remember this: sharding is not an optimization technique; it is the fallback after conceding defeat. When you begin sharding, you are acknowledging the limits of a single relational database and must accept all the complexity of distributed systems from that point onward.

Summary

• The shard key is the lifeblood: It determines query routing and data distribution. Choose poorly, and every query is broadcast while all data becomes skewed. The three iron rules are high cardinality, frequent presence in queries, and avoidance of monotonic hotspots.
• Let queries drive the choice: Use user_id for consumer applications, tenant_id for SaaS, hash(id) for pure KV workloads, and compound keys for very large tenants.
• Cross-shard operations are a nightmare: Handle JOINs with application-level merging or denormalization, aggregation with pre-aggregated tables or a data warehouse, pagination with cursors instead of OFFSET, and DISTINCT with approximation or prohibition.
• Supporting engineering: globally unique IDs based primarily on Snowflake, consistent hashing for dynamic scaling, CQRS for separate read and write models, and pre-sharding to plan the shard count in advance.
• Practical advice: 90% of projects should not shard yet. A single database with read/write splitting, partitioning, and caching is usually sufficient. Sharding is a fallback, not an optimization, and is almost impossible to reverse once adopted.

Key takeaway: Choosing a shard key is not about selecting a column that can split the data. It is about selecting the column that lets most queries route precisely while distributing data evenly. Sharding itself is not an optimization technique; it is the fallback after conceding defeat. Before you begin, exhaust the capacity of a single database.