Shard key impact on uniqueness and cross-shard lookups

WHAT THIS TESTS The interviewer probes whether you understand that sharding trades single-node guarantees for scale, and whether you can route a query that does not include the shard key.

A GOOD ANSWER COVERS A shard key decides which shard a row lives on. Database-enforced uniqueness and foreign keys operate per shard, so a UNIQUE constraint on email only guarantees uniqueness within one shard, and two shards could both hold the same email. Likewise a foreign key cannot reference a parent row on another shard. To enforce global email uniqueness you maintain a dedicated lookup table or service keyed by email that maps to user_id, and you write to it as the source of truth.

For reads, since users is sharded by user_id, a query by email does not know the shard. Two options: scatter-gather, broadcasting the query to every shard and merging results, which is simple but scales poorly as shard count grows; or a secondary index, an email-to-shard or email-to-user_id mapping you keep updated on writes, letting you resolve the shard in one hop, at the cost of maintaining that index consistently.

COMMON WRONG ANSWERS Assuming a global UNIQUE index works transparently across shards. Believing foreign keys span shards. Defaulting to scatter-gather for every secondary lookup without considering an index. Forgetting the consistency burden of a secondary mapping.

LIKELY FOLLOW-UPS How do you keep the email index consistent with user writes? How does choice of shard key affect hotspots? When is scatter-gather acceptable? How do you generate globally unique IDs?

ONE CONCRETE EXAMPLE Login by email first queries an email-to-user_id index table, gets user_id, hashes it to the correct shard, and fetches the user in a single targeted read, avoiding a broadcast to all shards on every login.