A 5 years+ tech lead said they shard a database to scale but then he failed to answer this question

Never talk about something you do not fully understand during an interview

Another interview story.

Q: “Introduce yourself and the recent project you are working on, as detail as possible”.

A: “Sure, I’m …, working at an internet company, a public-facing platform that serves millions of requests per minute..blabla.. my team is a search module, and we provide search services to other teams.”

Q: “Ok, what technology is used in your team, and what is your scope, any challenges you have been facing, and how have you solved them”.

A: “blabla… by the way we scaled our database (MySQL) by sharding. it is fast and scalable”.

Q: “Interesting, why go for sharding?”

A: “It is a big table. data keep increasing in a single table, search becomes slow as it grows, we need them to be split into different machines. so the search is faster and scales horizontally”

Q: “Okay. how do you shard it, range-based or key based? If key, what is the sharding key?”

A: “We use hash, sharding key is xx.”

Q: “Could you tell me how to query the recent 500th to 1000th updated records and order by modified date? ”

A: “….” # after 3 minutes

Q: “You have to query all databases and order by modified (must be indexed well) and limit 500 offsets 500 then merge in memory right?” I tried to continue the conversation.

A: “No we have our own algorithm to do it more efficiently…”

He drew something on the whiteboard Id [0,5000]-machineA, Id[5001, 10000]-machineB, … then I reminded him “you told me your sharding key is hash right 😄?”, he stopped and spent a few minutes thinking, didn’t come up any more answer.

Then we discussed something else, and the interview finished in another 3 mins.

To this topic, there are much more difficult questions ahead in my mind:

• When you add a new machine, do you need re-shard? and how did you handle it?
• How do you handle schema changes?
• How do you join tables? Does your code have no sub-query? and what if you have to use it? Is there an alternative approach?