Question 1473

Seventy One Percent

You are picking one shard key for a messaging backend. This is the measured query mix:

share of reads query predicate
62% last 50 messages of a conversation conversation_id = ? order by sent_at desc
24% a recipient's unread count recipient_id = ? and read_at IS NULL
9% search inside one conversation conversation_id = ? and body match
5% nightly moderation sweep sent_at between ? and ?

Writes are one row per message. Which key should the table be hash-sharded on?

A

sent_at as a range key, because it puts the moderation sweep and the conversation ordering on the same physical layout

B

conversation_id, which keeps 71 percent of reads on a single shard; the unread count moves to a small per-recipient counter table sharded on recipient_id

C

recipient_id, because the unread count is the only query a user actually waits on and the other three tolerate a fan-out

D

message_id, because it is the only key with perfectly uniform cardinality, and uniform load is the point of hash sharding