Million-row SQL

So there I am chilling in my workspace, waiting for my interview to start. I was interviewing for a senior full stack engineer role. I knew there would be some questions related to system design. What could go wrong? I know basic stuffs, I self host my projects bare metal with K8s! I have decent networking knowledge. I can answer how to scale a system, how to make it fault tolerant, how to make it secure etc. Or so I thought!! The thing is, the last time I gave a proper interview was years ago, as a junior engineer.

Throughout my career I only got to work with early stage projects. These projects don't have millions of users or that much volume in data. So, when my interviewer asked me how I would design a system that can handle 40 millions of rows in a database, I was stuck. The funny part is, I know the answer theoretically. I know you need have solid indexing & logical partitioning. Sharding your database should be the one of the last resort. That's it! I just know these. Heck, I don't even know how to implement these. I never faced with any situations that closely resembles this. Seeing my struggle, the interviewer asked me to design a system that'll have millions of requests. How would I handle the servers? These should've been easy, right? I know how to scale a system. I know how to make it fault tolerant. I know how to make it secure. But I didn't!! Oh, what an embarrassment! I was so excited by this opportunity. It was a organization that I've been following for some times.