STANDARDwalkthrough
Metadata Sharding
The constraint: we need to store 100 billion file records and serve reads at 10 ms p99. A single MySQL instance maxes out at roughly 1 billion rows before query performance degrades. Metadata sharding distributes file records across 16 MySQL shards using consistent hashing on user_id.
Sharding by file_id would distribute writes evenly but force every user-scoped query to scatter-gather across all 16 shards, a 16x read amplification that kills p99 latency. Each shard holds roughly 6 billion file records () at 200 bytes per record = 120 GB per shard, comfortably fitting in RAM for index caching.
“We chose user_id as the shard key (not file_id) because the dominant access pattern is 'list my files' and 'sync my changes,' both scoped to a single user.”
Implication: user-scoped queries hit exactly one shard with sub-millisecond routing. Cross-user queries (file sharing) require scatter-gather across shards, but these are 100x less frequent than user-scoped queries.
Amazon uses a similar approach for S3 metadata: partition by bucket owner to keep the hot path local. Trade-off: user_id sharding creates potential hot spots for power users with millions of files.
We mitigate this by splitting hot users across sub-shards when their file count exceeds 10 million. What if the interviewer asks: 'Why not DynamoDB instead of sharded MySQL?' DynamoDB would eliminate manual shard management, but we need ACID transactions for atomic file renames and moves (updating parent_folder_id and name in one operation).
DynamoDB's transaction support is limited to 25 items per transaction.