Shard key selection for both write scaling and query isolation?

Question

From the mongod documentation, I know that when choosing shard keys the key should always have high cardinality, so write operations are distributed to different shards (a kind of write scaling). But for query isolation, better query performance will be gained if a query is routed to a single shard server (one single mongos instance).

For write scaling, choosing a shard key which has high cardinality seems easy. But for query isolation, how can I choose a shard key which will route the query to one single shard to the maximum extent when I don't know the detailed sharding algorithm?

Answer 1

The documentation provides an answer through kind of a back-door. As you mentioned, for shards:

• For write optimization, choose a shard-key that has high cardinality.
• For query isolation, choose a shard-key that allows the mongos instance to route the query to as few shards as possible.

It goes into a few more details, which are relevant to what you're looking for.

• When attempting query isolation, always include the full shard-key in queries as it allows mongos to provide optimization. If you don't, it will query all shards.
• If your preferred shard-key is not sufficiently cardinal, adding another field that does have sufficient cardinality to create a compound key will allow more efficient write-spreading.

The easiest gain here is to include the shard-key in all queries, even if you don't need the data in the query. This allows you to get the benefits of optimization without having to rewrite your query system to include cluster metadata.

You certainly can write a query-optimizer that uses cluster metadata. It is very possible to get the number of chunks in a specific collection and even get the chunk boundaries. You'll have to track the state of chunks in your collection since they will update from time to time, but it would allow you to write queries that only hit one shard at a time.

However, that's a lot of work and the gains you get may not be worth the extra engineering effort. Only you can tell if that's an optimization that's worth it to pursue.