I have designed a few systems, but my issue with the system design interview is that this is not how it works. There is never a blank page in real life like it is in one of these interviews, and the stuff that's actually on the page matters more than the stuff you're "supposed" to say in these sessions.
Yes, column-oriented databases absolutely do work better for OLAP use cases, but is it better enough for the specific use case to be worth introducing a new database technology into the organization, or would a new database within the existing managed psql instance be good enough for now? Those detailed organizational questions usually matter more in the first few iterations of systems than "principled" architectural concerns.
The useful kind of design is: What is the next best iteration of this system? Maybe with an appendix at the end discussing some ideas for the iteration after that. Sometimes that next iteration is actually the first iteration of the system, in which case you should definitely not be drawing 20 boxes with different components of how it will fit together, you should be looking for the simplest possible thing that could work.
One of the fun things at big successful companies is that there are actually a lot of systems that are quite a few iterations in, with a stable baseline of usage properties. With these, it actually can make sense to draw a bunch of boxes with different components targeting different well-known pain points in a way that avoids trading off important existing capabilities. But again, that's exactly the opposite of a blank page, and no amount of digging into the interviewer's toy system design question can get deep enough for that.
All of my answers to these questions - which have always been very well-received - have been over-engineered solutions that I'd never actually pursue in a real job. But interviewers aren't really prepared for questions like "what frameworks are already being used and familiar to most teams at the company?" followed by "since we already have familiarity with postgresql+rails+react, we should set up a new non-replicated but periodically backed up database in that database instance, start with a few tables with some reasonable relationships, use activerecord and its migrations, and implement the front-end in react, then we should launch it and see what bottlenecks and pain points arise".
I get it, these interviews are trying to see if you have the knowledge and chops to solve those pain points and bottlenecks as they arise, but I'm sorry, "do an up-front design of a huge fancy system" just doesn't answer that question.
I was asked how I would replace an large scale but inadequate logging infrastructure and I stared with "In a way that minimally disrupts everything currently in place for monitoring and alerting".
I'm not sure how well that answer played out but it's still the correct answer.
Yes, column-oriented databases absolutely do work better for OLAP use cases, but is it better enough for the specific use case to be worth introducing a new database technology into the organization, or would a new database within the existing managed psql instance be good enough for now? Those detailed organizational questions usually matter more in the first few iterations of systems than "principled" architectural concerns.
The useful kind of design is: What is the next best iteration of this system? Maybe with an appendix at the end discussing some ideas for the iteration after that. Sometimes that next iteration is actually the first iteration of the system, in which case you should definitely not be drawing 20 boxes with different components of how it will fit together, you should be looking for the simplest possible thing that could work.
One of the fun things at big successful companies is that there are actually a lot of systems that are quite a few iterations in, with a stable baseline of usage properties. With these, it actually can make sense to draw a bunch of boxes with different components targeting different well-known pain points in a way that avoids trading off important existing capabilities. But again, that's exactly the opposite of a blank page, and no amount of digging into the interviewer's toy system design question can get deep enough for that.
All of my answers to these questions - which have always been very well-received - have been over-engineered solutions that I'd never actually pursue in a real job. But interviewers aren't really prepared for questions like "what frameworks are already being used and familiar to most teams at the company?" followed by "since we already have familiarity with postgresql+rails+react, we should set up a new non-replicated but periodically backed up database in that database instance, start with a few tables with some reasonable relationships, use activerecord and its migrations, and implement the front-end in react, then we should launch it and see what bottlenecks and pain points arise".
I get it, these interviews are trying to see if you have the knowledge and chops to solve those pain points and bottlenecks as they arise, but I'm sorry, "do an up-front design of a huge fancy system" just doesn't answer that question.