> I’m often alone on this. Engineers look at complex systems with many interesting parts and think “wow, a lot of system design is happening here!” In fact, a complex system usually reflects an absence of good design.
For any job-hunters, it's important you forget this during interviews.
In the past I've made the mistake of trying to convey this in system design interviews.
Some hypothetical startup app
> Interviewer: "Well what about backpressure?"
>"That's not really worth considering for this amount of QPS"
> Interviewer: "Why wouldn't you use a queue here instead of a cron job?"
> "I don't think it's necessary for what this app is, but here's the tradeoffs."
> Interviewer: "How would you choose between sql and nosql db?"
> "Doesn't matter much. Whatever the team has most expertise in"
These are not the answers they're looking for. You want to fill the whiteboard with boxes and arrows until it looks like you've got Kubernetes managing your Kubernetes.
(For context, I've conducted hundreds of system design interviews and trained a dozen other people on how to do them at my company. Other interviewers may do things differently or care about other things, but I think what I'm saying here isn't too far off normal.)
I think three things about what you're saying:
1. The answers you're giving don't provide a lot of signal (the queue one being the exception). The question that's implicitly being asked is not just what you would choose, but why you would choose it. What factors would drive you to a particular decision? What are you thinking about when you provide an answer? You're not really verbalizing your considerations here.
A good interviewer will pry at you to get the signal they need to make a decision. So if you say that back-pressure isn't worth worrying about here, they'll ask you when it would be, and what you'd do in that situation. But not all interviewers are good interviewers, and sometimes they'll just say "I wasn't able to get much information out of the candidate" and the absence of a yes is a no. As an interviewee, you want to make the interviewer's job easy, not hard.
2. Even if the interviewer is good and does pry the information out of you, they're probably going to write down something like "the candidate was able to explain sensibly why they'd choose a particular technology, but it took a lot of prodding and prying to get the information out of them -- communications are a negative." As an interviewee, you want to communicate all the information your interviewer is looking for proactively, not grudgingly and reluctantly. (This is also true when you're not interviewing.)
3. I pretty much just disagree on that SQL/NoSQL answer. Team expertise is one factor, but those technologies have significant differences; depending on what you need to do, one of them might be way better than the other for a particular scenario. Your answer there is just going to get dinged for indicating that you don't have experience in enough scenarios to recognize this.
+1 on the signal. A great candidate won't need you to pry further than asking about backpressure, they'll explain WHY it's not necessary for the qps, what qps it would start becoming necessary, and how they would build it into their design down the line if the service takes off.
One of the things I tell people preparing for system design interviews is the more senior you are, the more you need to drive the interview yourself, knowing when to go deep, what to go deep on, and how to give the most signal to the interviewer.
What you're describing is how the interview process _is_ disconnected from the actual needs, and how it's good to literally "play the game" to get in.
But on the other side, that kind of interview process is itself also a signal candidates might take to avoid playing the game, knowing that most (not all, of course) companies probing for the wrong signals during the interview process are indicative of how they do function as a whole.
(been in both types of companies, and in both sides of the table)
OP has stated that the system design interview exists as a way to performatively answer a set of questions that don't relate to actually being a good system designer. In your response, you have claimed that their proposed truthful answers don't give a lot of 'signal' and that you would prefer candidates to engage with the performative nature of the process. This is true - but is not an argument against the OP's claim, which is that reciting a set of facts about system design in an interview != being a good system designer.
This goes back to "interviews go both ways". All those answers you gave are very reasonable and if I was your interviewer I'd pass you with flying colors. On the other hand if you're interviewing at a place that doesn't pass you with flying colors for those responses, that really says more about them than it does about you and may not be a great place to work.
But to your point, many times one interviews for a job they don't really have the luxury of getting rejections and need to land somewhere fast so they can keep paying the mortgage. So while yes interviewing is a two way street, there's still quite a bit of calibration to make sure you land on the other person's side of the street so to speak.
If I was your interviewer, I would: respect your answers a lot, not be able to check off anything on my rubric, try to explain this in the debrief, get told we have to stick to the rubric to counter bias, and then watch while they pass on you for someone who decided to play architecture jenga instead. I would potentially even consider emailing you to apologize later, then not do it because I'd probably get in trouble for exposing us to liability or something because apologizing can be construed as admission of guilt.
If I were the interviewer, I'd try to adjust the problem statement with some hypotheticals to tease out their depth of knowledge:
> "That's not really worth considering for this amount of QPS"
"What if Michael Jackson dies and your (search|news|celebrity gossip) service gets a spike in traffic way beyond the design parameters? How would you anticipate and mitigate such an event?"
(Extra points if the answer is not necessarily backpressure but they start talking about DDoS mitigation, outlier detection, caching or serving static results from extremely-common queries, spinning up new capacity to adjust to traffic spikes, blackholing traffic to protect the overall service, etc.)
> Interviewer: "Why wouldn't you use a queue here instead of a cron job?" "I don't think it's necessary for what this app is, but here's the tradeoffs."
"What if you have a subset of customers that demand faster responses than a cron job can provide?"
(And then that can become a discussion about splitting off traffic based on requirements, whether it's even worth adding the logic to split traffic vs. just using a queue for everyone, perhaps making direct API requests without either a queue or cron job for requests from just those customers, relying on the fact that they are not numerous or these requests are infrequent to trade capacity for latency, etc.)
> How would you choose between sql and nosql db?"
I would've expected the candidate to at least be able to talk about indexing, tradeoffs of joining in the DB vs. in the application, schema migrations and upgrades, creating separation between data-at-rest vs. data-in-flight, etc. If they can't do that and just handwave away as "whatever the team is most comfortable with", that's a legit hole in their knowledge. Usually you ask system design interviews of senior candidates that will be deciding on architecture and, if not hiring out the team directly, providing input to senior managers who will be hiring, so you can swap out the team nearly as easily as swapping out the architecture.
This is awful advice. Simple and elegant design does not start with dismissing potential problems.
Those questions are all prompts to have a discussion in lieu of tech trivia hour. Those responses do not demonstrate wisdom, they reveal a lack of maturity. It's not the interviewers fault you refuse to be interviewed.
I agree, the responses give the vibe of "your questions are dumb and I'm too smart to waste the effort to engage with them." If you don't want the job, then don't interview!
Yes, and this is exactly why LinkedIn-driven development exists in the first place. Listing a million technologies looks much more impressive on paper to recruiters than describing how you managed to only use a modular monolith and a single Postgres instance to make everything work.
As well as the “two-way street” point made in a sibling comment, I feel like a good interviewer would say “this is great, I would keep it simple too, but I am testing your knowledge of $thing right now.” If the person won’t stop talking about the wrong thing, that’s a bad sign of course.
Do you _want_ to work in these places? In my experience, if they expect you to run kube using kube in the interview, thats exactly what they do in their ststems as well.
These ARE the answers we are looking for. As the system design interview (I’ve done hundreds) I want you to start with these answers then we can layer on complexity if you’ve solved the problem and there’s time left to go into navel gazing mode.
Seeing the panic slowly build in mid-level engineers’ eyes as it dawns on them that not every problem can be solved by caching is pretty fun too. “Ok cool you’ve cached it there, now how do you fill the cache without running into the same performance issue?”
> I want you to start with these answers then we can layer on complexity if you’ve solved the problem and there’s time left to go into navel gazing mode
Exactly. Part of the interview is explaining when and why these techniques are necessary as part of demonstrating your understanding.
If the candidate gives non-answers like “I don’t think it matters because you’re a startup” or “I’d just use whatever database I’m comfortable with” that’s not demonstrating knowledge at all. That’s dismissing the question in a way that leaves the interviewer thinking you don’t have that knowledge, or you don’t take their problems seriously enough to put thought into them. There is a type of candidate who applies to startups because they think nothing matters and they can YOLO anything together for a few years before moving on to the next job, and those are just as bad as the super over-engineering candidates.
The interview is your chance to show you know the topics and when to apply them, not the time to argue that the startup shouldn’t care about such matters.
> I want you to start with these answers then we can layer on complexity if you’ve solved the problem and there’s time left to go into navel gazing mode.
Do you tell people this explicitly? If so, good on you; if not, please start! I think one of the biggest problems with interviews these days is misaligned expectations, particularly interviewees coming in assuming that what's desired is immediate evidence that they're so experienced in solving FAANG-scale problems that it's their default mode.
This also happens because plenty of candidates learn the buzzwords and patterns without understanding the trade-offs and nuances. With a competent enough interviewer, the shallowness of knowledge can be revealed immediately.
You're equating simplicity of the design with simplicity of the problem.
It's good not to over engineer, over engineering can be a cause of unneeded complexity, but when complexity is warranted the ability to solve for it simply is also needed.
More importantly though, you haven't explained or rationalized why?
It's not needed for this QPS? Oh ya? Why not? What's your magic threshold? When would it be needed? How do you plan for the team to know that time is approaching? If it's needed later how would you retrofit it? Is that going to be a simple addition? How do you know the max QPS won't be too high and that traffic won't be spiky? What if a surprise incident occurred that caused the system to overload, how would your design, without backpressure, handle that, how would you mitigate and recover?
In system design there's no real right answer, as an interviewer you're looking for the candidate to demonstrate their ability to identify the point of concerns, reason through the possibilities, explain their decisions and trade offs, and so on.
I recently had an interview like this. Felt like half the answers I gave were of the form, “You can do scaling/sharding/partitioning thing X here, but once again, for an internal app I’d try really hard to avoid doing any of that”. If you’re interviewing with capable, experienced developers, they’ll appreciate that answer (at least, I got the offer on this one!)
It’s like people crave complexity because it makes them, indispensable? Like if you’re the only one who knows how the billing reconciliation service works, they couldn’t possibly fire you?
They will.
Being pragmatic is something I look for in engineers. So long as they understand where to draw the line (and use a queue instead of cron). However that’s usually several years away at this point and them being able to say “You don’t need that, all you need is…” is welcome. Then again, that’s probably why I got fired. :shrug:
I believe the reason is far more mundane: Complex systems are more interesting, with all the shiny knobs and levers and mysterious thingamabobs. Developers have a tendency to get nerd-sniped by interesting problems, and picking overly complex solutions to solve them at an abstract level scratches that itch very succinctly. In my experience, senior engineers learn to control this urge, and staff engineers can accurately decide when to break the rule and the complexity is warranted.
> I’ve been in software for 20 years and it’s the first time I hear “back pressure”. Am I too old already?
I first wrote code 50 years ago (I am 63yo) so yes, imo we are too old, but ...
It is worth noting that systems concepts/techniques often have analogues aka different names and histories in different fields and subfields.
If I were to "explain" back pressure to an ordinary person I might model my analogy to the logic of this ~classic joke:
Bob: Let's go to Trendio(TM) for dinner tonight!?
Carol: Oh, nobody goes there anymore, it's too crowded!
Also, often a modern take-this-for-granted concept may be seen as an outgrowth of previous problems or solutions.
For example back pressure is conceptually adjacent to the clever~hack/design of random backoff in Ethernet.
Or if talking to a math geek or traffic planner you might relate it to ~modern understanding of congestion including oddities like possibly removing roads/routes to ~paradoxically improve traffic flow.
We are deep in the Information Age barreling towards Singularities, so none of us, young or old, see and understand but a tiny fraction of where we've been, are, or might be going.
Cue Calvin & Hobbes cartoon of us racing downhill in a fragile box.
Perhaps, as others have essentially suggested, merging your mind with an ~AI will help (albeit temporarily, imo). I prefer to think of us/greybeards as potentially Wise, yet, paradoxically, clueless.
Beginner's Mind, with likely no time/future for Mastery, is still potentially pleasant, and I would argue useful for Debugging.
Obviously this modern AI tsunami is phase shifting us all into debug~mode anyway, eh?
Backpressure occurs at many levels, even down to a single machine doing something. If you ever have a producer and a consumer interacting and the consumer can’t consume as fast as the producer can produce, you need some way to have the producer pause or slow down until the consumer catches up. That’s back pressure.
Services, systems, and/or databases eventually provide back pressure when they fail or get overloaded. The idea is to design in back pressure to let the system degrade gracefully rather than fail chaotically.
(but worry yea not, just like someone said of another term: "Dependency Injection" is a 25-dollar term for a 5-cent concept, something is similar for this term. )
> > "That's not really worth considering for this amount of QPS"
There is a good way and a bad way to communicate this in interviews.
If an interviewer is asking about back pressure, they’re prompting you to demonstrate your knowledge of back pressure and how and when it would be applied. Treating it as an opening to debate the validity of the question feels like dodging the question or attempting to be contrarian. Explaining when and where you would choose to add back pressure would be good, but then you should go on to answer the question.
This question hits close to home for me because I was once working at a small startup that was dealing with a unique problem where back pressure really was the correct way to manage one of our problems, but we had a number of candidates do exactly what you did: Scoff at the idea that such a topic would be relevant at a startup.
If we’ve been dealing with a problem for months and a candidate comes in and confidently tells us that problem isn’t something we would experience and dismisses our question, that’s not a positive signal.
> > Interviewer: "How would you choose between sql and nosql db?"
> > "Doesn't matter much. Whatever the team has most expertise in"
This is basically a softball question. Again, if you provide a non-answer or try to dismiss the question it feels like you’re either dodging the topic or trying to be contrarian. It’s also a warning sign to the interviewer that you might gravitate toward what’s easy for you instead of right for the project.
This one also resonates with me because I spent years of my life making MongoDB do things that would have been trivial if earlier developers had used something like SQLite instead. The reason they chose MongoDB? Because the team was familiar with it. It was hell to be locked into years of legacy code built around the wrong tool for the job because some early employees thought it didn’t matter “because startup”
As an interviewer, let me give some advice: If an interviewer asks a question, you should answer the question. Anything that feels like changing the subject, dodging the question, or arguing the merits of the question feels like the candidate either doesn’t understand the topic or wants to waste time by debating the question.
It can be very valuable to explain when and why a topic would become necessary, right before you explain it. Instead of “this application has low QPS and therefore I will not answer your question” (not literally what you said, but how it comes across) you could instead explain how the need for back pressure could be avoided first by scaling servers appropriately and then go on to answer the question that was asked.
Re: SQL vs NoSQL my take is that one should always start with SQL and get good at SQL, then if and when you ever find yourself with a need to scale that you can't meet in any way other than to use a NoSQL, then switch to NoSQL. Nine times out of ten you'll never need to switch.
Not only theory crafting during interviews but a lot of real life design is driven by what's known as resume driven development. The worst part - some of that is later presented at large conferences as successful and go-to solutions.
One time I was working in a body leasing company and our team was hired by bigco for an internal project. Two months earlier an internal employee was tasked to research the project and develop a prototype. When we started all major set pieces were written in stone. Month later said employee left. When we later checked the job listing he likely applied to our tech stack mirrored that to a letter. He got free training, a resume and a new job. We were stuck with these decisions for 3 years.
Another time a local branch of another bigco was trying to carve out a major piece of internal cake. Head-of was hired, team was quickly ramped up and they started cooking their foothold. Then a series of major power shifts happened couple levels above our pay grade and another branch came out with competitive strategy. We had a 2 days long internal brainstorm involving 50 people to come up with arguments and strategies how to defend our approach. We bet on blue, they were selling red. Life's were at stake. And many truly believed that blue was the way to go, and red was a recipe for disaster. Two days later we had a rock solid presentation that was trashing red approach. But if course most of these decisions are not made by nerds and middle mgmt do eventually the company placed their bet in red and the whole dept became redundant.
No one likes to lose their jobs, so our blue head-of quickly turned his cloak and the team became an outsourcing provider for the winning team.
What makes this story particularly funny is the fact that the head-of immediately started campaign of conference presentations where he sweard that all his life he believed that red was the future that will eventually trump blue, and any competition that is still using blue is destined to fail in short future.
You don't need to entirely forget this. I've made a habit of regularly seeking out job opportunities and interviewing even when I'm entirely happy with my job, which is to say I've done a ton of these kinds of interviews (on both sides of the table).
Unless the initial question requirements are insane (build Twitter at Twitter scale), I start with the smallest, dumbest thing that will work. Usually that's a single machine/VM talking to a database (or even just SQLite!). Compute and storage are so fast these days that you could comfortably run your fledgling service on a Raspberry Pi, even serving three or four-digit QPS depending on the workload.
Of course, you still have to "play the game" in the interview, so make sure to be clear about how you'd change this as requirements changed (higher QPS, more features, etc)
I think the right tack, if you're going to dismiss something as needlessly complex, is to call out the circumstances that would make it necessary and then describe what you'd do under those conditions.
"Backpressure? I don't think you'll have enough traffic to make backpressure necessary. The mode of failure here is that you run out of queue space and start dropping messages, and it's not a big deal if some messages get dropped here. But if we do decide that dropped messages are causing problems, and if it starts becoming a regular occurrence (we'll set up observability), here's how the producer can poll the queue size and return an error to the user under heavy load.
Yes, and then you get the job there and regret it bc they’ll have either have an over engineered Rube Goldberg contraption or they have system envy bc they’ve read about this architecture in blogs and THINK they need K8s and they still fix all their problems.
Tools that reduce the barrier to entry to creating things make it easier to solve problems with less scale to pay for the overhead. Generative AI is among these tools, but so are low code platforms, so is React, so is AWS, heck, so is the power grid. But in recent times generative AI is a big leap forward.
We’re at the start of another cycle of a lot of niche products followed by the rise of big Acme megacorps who conquer them all economies of scale that compete on margin. It comes just as we’re at the tail end of this cycle with tech as we knew it for the last 50 or so years.
I don't think they're completely not looking for that entire type of answer, but those examples are pretty dry and don't really go into the reasoning for your opinion, which is probably what they're worried about. Whenever you say something isn't worth considering or doesn't seem necessary, you should be explaining exactly why you think that, and exactly where it would be worth considering or seem necessary, because otherwise you just look like someone who simply doesn't care about whatever kind of scalability they're asking about.
You can always say “Since we’ve got only x QPS, I’m going to do A. If we had say y QPS, I’d do B but that would impact the rest of the design. Let me know if you anticipate growth to y and I can show you how I’d do it”
The point of an interview is to lay bare one’s thought process entirely so that the interviewer has full awareness of the person you are. And to likewise extract that from the interviewer. Getting or transmitting less information is just underutilizing the time. Interviewers are also flawed and may not be good enough at extracting the information from you.
If you’re an ideal decision maker, you will likely out-skill the majority of interviewers. You’re being hired to make their org succeed. So just do that.
I think people who describe system designs frequently fail to demarcate the space they’re operating in, so subsequent engineers cannot determine whether the original designer failed to consider something or whether the original designer considered and dismissed something. The point is to be able to express this concisely.
IMHO, doing it well means that not only do you get it right but you send the information down through time so that subsequent observers understand why and also get it right consequently.
Who does this? Why make something 10x as complicated as it needs to be, when you could just use the simple thing and get 10x as far? It's not like there's not enough work to do.
Real companies do. The moment you deploy one line of code, it's legacy. It goes from there. Soon you have to build systems that interface with other systems you'd rather were better architected and designed, except you have to deal with them as they are. Then your product becomes one of these, and with no need to maintain or expand it for a long time, it rots a bit, and now someone has to pick it up or interface with it, and your product made things more complex, and the complexity can't be magic wand waved away.
In some ways it's worse. There are also project review interviews. "We had a Rails/Django/whatever monolith that was backed by Postgres and we didn't need a SPA" makes for a less impressive session with many companies. This creates a lot of incentive to overcomplicate/"future proof" things for resume building.
This. There is also really no easy way of telling how an interviewer is thinking. One interviewer thought not having a warehouse in the design was a mistake, and the other one though having a caching solution made things too complex. It is completely a hit or miss with interviews
If you know that those are not the answers they are looking for, you can reasonably pass by modifying the answer only slightly, while still getting your point across.
If you can't, you might be getting interviewed by people you do not what to work with and you should want to know that.
Except these are the people in your way of getting that job that could be potentially life/career changing for you financially or otherwise. In this market or depending on your situation that would be hard to ignore.
True, but people generally don't want to get evicted or have their utilities turned off either. If you need a job you need a job, and the numbers out of Cali's job market puts a lot of tech people in a position where they might not have the luxury of waiting for the "right fit". As always, YMMV and the world is a big place, everyone's different, yadda yadda.
If you want to get top dollar at a FAANG you will need to go through these type of system design interviews. You could say you shouldn’t work for a FAANG which is fair, but FAANG pays top dollar.
What a great article. It's always a treat to read this sort of take.
I have some remarks though. Taken from the article:
> Avoid having five different services all write to the same table. Instead, have four of them send API requests (or emit events) to the first service, and keep the writing logic in that one service.
This is not so cut-and-dry. The trade offs are far from obvious or acceptable.
If the five services access the database then you are designing a distributed system where the interface being consumed is the database, which you do not need to design or implement, and already supports authorization and access controls out of the box, and you have out-of-the-box support for transactions and custom queries. On the other hand, if you design one service as a high-level interface over a database then you need to implement and manage your own custom interface with your own custom access controls and constrains, and you need to design and implement yourself how to handle transactions and compensation strategies.
And what exactly do you buy yourself? More failure modes and a higher micro services tax?
Additionally, having five services accessing the same database is a code smell. Odds are that database fused together two or three separate databases. This happens a lot, as most services grow by accretion and adding one more table to a database gets far less resistance than proposing creating an entire new persistence service. And is it possible that those five separate services are actually just one or two services?
> the interface being consumed is the database, which you do not need to design or implement
You absolutely should design and implement it, exactly because it is now your interface. In fact, it will add more constraints to your design, because now you have different consumers and potentially writers all competing for the same resource with potentially different access patterns. Plus the maintenance overhead that migrations of such shared tables come with. And eventually you might have data in this table that are only needed for some of the services, so you now need to implement views and access controls at the DB level.
Ideally, if you have a chance to implement it, an API is cleaner and more flexible. The problem in most cases is simply business pushing for faster features which often leads to quick hacks including just giving direct access to some DB table from another service, because the alternative would take more time, and we don't have time, we want features, now.
But I agree with your thoughts in the last paragraph. It happens very often that people don't want to undertake the effort of a whole new design or redesign to match the evolving requirements and just patch it by adding a new table to an existing DB, then another,...
> In fact, it will add more constraints to your design, because now you have different consumers and potentially writers all competing for the same resource with potentially different access patterns. Plus the maintenance overhead that migrations of such shared tables come with. And eventually you might have data in this table that are only needed for some of the services, so you now need to implement views and access controls at the DB level.
PostgreSQL, to name one example, can handle every one of these challenges.
> Plus the maintenance overhead that migrations of such shared tables come with.
Moving your data types from SQL into another language solves exactly 0 migration problems.
Every migration you can hide with that abstraction language you can also hide in SQL. Databases can express exactly the same behaviors as your application code.
APIs can be evolved much more easily than shared database schemas. Having worked with many instances of each kind of system, I think this outweighs all of the other considerations, and I don't think I'll ever again design a system with multiple services accessing the same database schema.
It was maybe a good idea if you were a small company in the early 2000s, when databases were well-understood and services weren't. After that era, I haven't seen a single example of a system where it wasn't a mistake for multiple services to access the same database schema (not counting systems where the read and write path were architecturally distinct components of the same service.)
I've implement an interesting service 15 years ago. Recently I've heard of it.
So this service was basically an "universal integration service". Company wanted to share some data, and they wanted to implement it in an universal way. So basically I've implemented SOAP web service which received request with SQL text and responded with list of rows. This service was surprisingly popular and used a lot.
I was smart enough, so I built a limited SQL syntax parser and UI, so administrator could just set up tables and columns they wanted to share for this specific client. SQL query was limited in a sense that it worked only with one table, simple set of columns and some limited conditions (that I bothered to implement).
The reason I've heard about it few months ago is that they shared with me, that they caught a malicious guy, who worked at some company integrating with this system and he tried to do SQL attack. They noticed errors in the logs and caught him.
Their database is pretty much done and frozen, regarding to schema. They hardly evolve it. So this service turned out ot be pretty backwards-compatible. And simple changes, of course, could be supported with view, if necessary.
The goal is to minimize what needs changing when things need changing.
When you need to alter the datastore, usually for product or scalability, you have to orchestrate all access to that datastore.
Ergo: one only one thing using the datastore means less orchestration.
At work, we just updated a datastore. We had to move some tables to their own db. 3 years later, 40+ teams have updated their access. This was a product need. If this was a scale issue, the product would just have died sans some as of yet imagined solution.
> Additionally, having five services accessing the same database is a code smell.
Counterpoint (assuming by database you mean database cluster, not a schema): having a separate physical DB for each service means that for most places, your reliability has now gone from N to N^M.
From which perspective? If a service is up, but is unable to do anything since another service is down, what good does it do other than increase some metrics on some dashboard. (Note that we are specifically talking about coupled services since the implication is writing to a single db being split up into multiple dbs - a distributed monolith).
I think the author meant, in a general way, it’s better to avoid simultaneous writes from different services, because this is an easy way to introduce race conditions.
>And what exactly do you buy yourself? More failure modes and a higher micro services tax?
Nice boxes in the architectural diagram. Each box is handed to a different team and then, when engineers from those teams don't talk to each other, the system doesn't suddenly fail in an unexpected way.
At amzn a decision from atop was made that nobody would ever write in shared dynamo db tables. A team
would own and provide APIs. That massively improved reliability and velocity.
> When querying the database, query the database. It’s almost always more efficient to get the database to do the work than to do it yourself. For instance, if you need data from multiple tables, JOIN them instead of making separate queries and stitching them together in-memory.
Oh yes! Never do a join in the application code! But also: use views! (and stored procedures if you can). A view is an abstraction about the underlying data, it's functional by nature, unlikely to break for random reasons in the future, and if done well the underlying SQL code is surprisingly readable and easy to reason about.
Writing raw SQL views/queries per MVC view in SSR arrangements is one of the most elegant and performant ways to build complex web products. Let the RDBMS do the heavy lifting with the data. There are optimizations in play you can't even recall (because there's so many) if you're using something old and enterprisey like MSSQL or Oracle. The web server should be able to directly interpolate sql result sets into corresponding <table>s, etc. without having to round trip for each row or perform additional in memory join operations.
The typical ORM implementation is the exact opposite of this - one strict object model that must be used everywhere. It's about as inflexible as you can get.
Most ORMs will happily let you map stored procedures and views to a class, you can have as many models as you want. So your point doesn't really make sense.
The author's said nothing about ORMs. It feels like you're trying to post a personal beef about ORMs that's entirely against the "pragmatic" software design engineering the author's opining. Using ORMs to massively reduce your boiler-plate CRUD code, then using raw SQL (or raw SQL + ORM doing the column mapping) for everything else is a pragmatic design choice.
You might not like them, but using ORMs for CRUD saves a ton of boilerplate, error-prone, code. Yes, you can footgun yourself. But that's what being a senior developer is all about, using the tools you have pragmatically and not foot gunning yourself.
And it's just looking for the patterns, if you see a massive ORM query, you're probably seeing a code smell. A query that should be in raw SQL.
You can write reusable plain functions as abstractions, returning QuerySets that allow further filters being chained onto the query, before the actual SQL is materialized and sent to the database.
The result of this doesn’t have to match the original object models you defined, it’s still possible to be flexible with group bys resulting in dictionaries.
Even in the article the solution wasn’t to abandon the ORM in favor of raw SQL but knowing how to write the code so it doesn’t have to run 100 extra queries when it doesn’t need to.
> Particularly if you’re using an ORM, beware accidentally making queries in an inner loop. That’s an easy way to turn a select id, name from table to a select id from table and a hundred select name from table where id = ?.
If your ORM is going to the DB per row you're using it wrong. N+1 queries are a performance killer. They are easy to spot in any modern APM.
Rails makes this easy to avoid. Using `find_each` batches the queries (by 1,000 records at a time by default).
Reading through the comment section on this has been interesting. Either lots of people using half baked ORMs, people who have little experience with an ORM, or both.
>The typical ORM implementation is the exact opposite of this - one strict object model that must be used everywhere. It's about as inflexible as you can get.
I can't respond to the "typical" part as most of my experience is using EF Core, but it's far from inflexible.
Most of my read-heavy, search queries are views I've hand written that integrate with EF core. This allows me to get the benefit of raw SQL, but also be able to use LINQ to do sorting/paging/filtering.
In particular, have you have to do testing, security (eg. row level security), manage migrations, change management (eg. for SOC2 or other security frameworks), cache offloads (Redis, and friends), support for microservices, etc.
Comments like this give me a vibe of young developers trying out Supabase for the first time feeling like that approach can scale indefinitely.
C#’s Linq based ORMs have always been - type safe built into the OS feature -> run time generation of an agnostic expression tree -> database provider converts it into SQL. It does database joins (unless you do something stupid like get out of IQuery land).
Stored procedures seem like a win but the big problem is that while I could write the rest of the software in a very nice modern language like Rust, or more practically in C# since my team all know C# if I write a stored procedure it will be in Transact-SQL because that's the only choice.
T-SQL was not a good programming language last century when it was vaguely current, and so no I do not want to write any significant amount of code in T-SQL. For my sins I maintain a piece of software with huge T-SQL procedures (multi-page elaborations by somebody who really, really like this stuff) and they're a nightmare. The tooling doesn't really believe in version control, the diagnostics when you make a mistake are either non-existent or C++ style useless spew.
We hire a lot of very junior developers. People who still need to be told not to comment out code in release, that variable numbers are for humans to read not machines, that sort of thing. We're not quite hiring physicists to write software (I have done that at a startup) but it's close. However, none of the poor "My first program" code I see in a merge request by a new hire is anywhere close to as unreadable as the T-SQL we already own and maintain.
I've only once tried to use stored procedures in mysql and it was almost impossible to debug back then. Very painful. Average devs already have issues being smart with their databases and stored procedures would add to that.
Stored procedures also add another risk. You have to keep them in sync with code, making releases more error prone. So you have to add extra layers of complexity to manage versioning.
I can see the advantage of extreme performance/efficiency gains, but it should be really big to be justified.
I worked at a place with just such a system. Half the application code was baked into sprocs, no version control and hidden knock on effects everywhere.
There was _one guy_ who maintained it and understood how it worked. He was very smart but central to the company’s operations. So having messy stuff makes it brittle/hard to change in more ways than one and
I disagree. In modern highly scalable architectures I’d prefer doing joins in the layer front of the database (backend).
The “backend” scales much easier than the database. Loading data by simple indexes, eg. user_id, and joining it on the backend, keeps the db fast. Spinning up another backend instance is easy - unlike db instance.
If you think, your joins must happen in db, because data too big to be loaded to memory on backend, restructure it, so it’s possible.
Bonus points for moving joins to the frontend. This makes data highly cacheable - fast to load, as you need to load less data and frees up resources on server side.
High Scale is so subjective here, I'd hazard a guess that 99% of businesses are not at the scale where they need to worry about scaling larger than a single Postgres or MySQL instance can handle.
My manufacturing data is hundreds of GB to a few TB in size per instance and I am talking about hot data, that is actively queried. It is not possible to restructure and it is a terrible idea to do joins in the front end. Not every app is tiny.
Unless all your tables have the same width - or you’re doing weird things with constants in your SELECTs - you can’t UNION the various queries, so they’re sequential. You could parallelize those I suppose, but now you’re adding more complexity.
If you want a KV store, use a KV store. If you want an RDBMS, then use its features. They haven’t changed much in the last 50 years for a reason.
Let's say you run a webshop and have two tables, one for orders with 5 fields, one for customers, with 20 fields.
Let's say you have 10k customers, and 1m orders.
A query performing a full join on this and getting all the data would result in 25 million fields transmitted, while 2 separate queries and a client side manual join would be just 5m for orders, and 200k for customers.
If you need all the orders and all the customers sure.
But usually you need some of the orders and you need the customer info associated with them. Often the set of orders you’re interested in might even be filtered by attributes of the customers they belong to.
The decision of whether to normalize our results of a database query into separate sets of orders and customers, or to return a single joined dataset of orders with customer data attached, is completely orthogonal to the decision of whether to join data in the database.
One way to think about 1-to-many relationships is to think in the other way, "many-to-one". You don't join the orders to the customers, you join the customers to the orders (enrich the orders with customer information).
It's very natural to want customer information when querying an order, and if you have a view like orders_with_customer_info, you get that with zero effort when querying that view by order id.
You also get consolidated data (orders by customer) by doing
select count(*), sum(amount) from orders_with_customer_info group by customer_id
I think it's ok to have this rule as a first approximation, but like all design rules you should understand it well enough to know when to break it.
I worked on an application which joined across lots of tables, which made a few dozen records balloon to many thousands of result rows, with huge redundancy in the results. Think of something like a single conceptual result having details A, B, C from one table, X, Y from another table, and 1, 2, 3 from another table. Instead of having 8 result rows (or 9 if you include the top level one from the main table) you have 18 (AX1, AX2, AX3, AY1, ...). It gets exponentially worse with more tables.
We moved to separate queries for the different tables. Importantly, we were able to filter them all on the same condition, so we were not making multiple queries to child tables when there were lots of top-level results.
The result was much faster because the extra network overhead was overshadowed by the saving in query processing and quantity of data returned. And the application code was actually simpler, because it was a pain to pick out unique child results from the big JOIN. It was literally a win in every respect with no downsides.
(Later, we just stuffed all the data into a single JSONB in a single table, which was even better. But even that is an example of breaking the old normalisation rule.)
> which made a few dozen records balloon to many thousands of result rows
That doesn't really sound like a place where data is actually conceptually joined. I expect, as it is something commonly attempted, that you were abusing joins to try and work around the n+1 problem. As a corollary to the above, you also shouldn't de-join in application code.
That reminds me of many cases of adhering to database normalisation rules even in views and queries, even in a case where you should break it. Aggregation functions like postgres's array_agg and jsonb_agg are incredibly powerful at preventing the number of rows from ballooning in situations like those
I think it's more like avoid doing a "limiting" join in the application, ie where the join is used to limit the output to a subset or similar.
As a somewhat contrived example since I just got out of bed, if your software has a function that needs all the invoice items from invoices from this year which invoice address country is a given value, use a join rather than loading all invoices, invoice addresses and invoice items and performing the filtering on the client side.
Though as you point out, if you just need to load a given record along with details, prefer fetching detail rows independently instead of making a Cartesian behemoth.
I have a very strict rule about any system I design or that I’m over against using stored procedures.
When I use to interview to be a developer at a company, it was always an automatic no for me if a company kept business logic in stored procedures and had a separate team of “database developers”.
As far as not doing joins in code, while I agree for the most part. GitHub itself has a rule against joining tables using sql that belong to different domains.
Microservice achitecture promotes splitting data cross multiple databases making it impossible to do proper DB JOINs from application code.
Then companies buy a solution to aggregate all the different databases in a single "data-lake" (or whatever buzzword is hot right now) so you can do OLAP queries. Without consistency guarantees of course.
And I am not saying this is never the _right_ solution, but it should almost never be the _first_ solution
Not sure I agree. First of all it can be more performant. Say you fetch 1000 records. And we need to join on a table where these 1000 records just got 2 different foreign keys. Instead of joing in db and fetching a lot more data we can do two queries and join in app instead. Secondly, makes it easier to cache data. Lets say the thing we joing with almost never changes (like some country info) we can cache that and just join it with the data from the db.
Not saying this should always be the case, but sometimes it is the right call.
But as a counterpoint to that, (a) the database has its own caching built in, which you don't have to implement, and (b) the database knows when to invalidate its cache.
To quote Douglas Adams: "The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
Likewise, if you cache a piece of data in your application because you assume that it won't change, that just makes it likely that if and when it does change, you'll have bugs. Moving the cache to the database layer so that it can be properly invalidated fixes this.
It's true that an application-side join can still be more performant if the DB cache isn't good enough, but IMO you should only take that step after actually profiling your queries.
I feel like the biggest question to ask is: how expensive is it exactly, how often do you need to do it, and how important is the speed of it?
If you have some complex queries on every page load with a huge number of users, put it in the DB as much as possible.
If you need to iterate over a bunch of records and do something based on some combination of values, and it's for a weekly reporting thing, I'd much rather see 3 nested foreach loops with a lot of early exits to skip the things you don't care about than a multi-kb SQL-statement that took two days to develop and nobody every dares to touch again because it's hard to handle.
You should be careful with how much you lean into “doing it in the database” as well with how you implement it. Lest, you get the situation where your application inserts as one value and it gets saved completely different.
I'm not sure if this is what you mean, but I think a big thing missing from the article is how you should isolate you business logic.
A great software design will separate all business logic into its own layer. That might be a distinct project, module, or namespace, depending on what your language supports. Keep business logic out of SQL and out of web server code (controllers, web helpers, middleware, etc.).
Then you're treating SQL as the data store it is designed to be. When you embed application logic in SQL, you're hiding core functionality in a place where most developers won't expect to find it. This approach also creates tight coupling between your application and your database provider, making it hard to switch as needs change/the application grows.
There are definitely examples of when you want to do joins in the application.
For example, you may want to (or have the option to) vertically partition your database, or use different data stores. The app layer is usually stateless and can scale perpetually, but the database might be a bottleneck.
Joining in the database over the application is a great default. But I wouldn't say "never join in the application code".
I came here to say an exactly opposite things. There were a few instances where a relatively heavy join would not perform well, no matter what I tried. And it was faster to load/stitch data together with goroutines. So I just opted to doing it that way.
Also SQL is easy, but figuring out what's up with indexes and planner is not.
> Paradoxically, good design is self-effacing: bad design is often more impressive than good.
Rings very true. Engineers are rated based on the "complexity" of the work they do. This system seems to encourage over-engineered solutions to all problems.
I don't think there is enough appreciation for KISS - which I first learned about as an undergrad 20 years ago.
This is unfortunately true. People love complex solutions, and suggesting a simple one usually comes across as incompetent, while the reality is, simple solutions are easy to manage, which ensures the success of the project as a whole.
Sure, there are problems that are inherently complex and require complex solutions. But most likely yours isn't one of them, most likely you have a basic web app.
One of the smartest engineers I've encountered in my 27 year career advised me to strive to do "the simplest thing that could possibly work" - not just to get unblocked on something new, but as a guiding principle. It resonated (and goes beyond "KISS", for me), and IME is real wisdom.
Every now and then, I try to go through our codebase and write up the parts that we rarely think about – these are usually the cases where we made good decisions early on.
> Schema design should be flexible, because once you have thousands or millions of records, it can be an enormous pain to change the schema. However, if you make it too flexible (e.g. by sticking everything in a “value” JSON column, or using “keys” and “values” tables to track arbitrary data) you load a ton of complexity into the application code (and likely buy some very awkward performance constraints). Drawing the line here is a judgment call and depends on specifics, but in general I aim to have my tables be human-readable: you should be able to go through the database schema and get a rough idea of what the application is storing and why.
I’m surprised that the drawbacks of EAV or just using JSON in your relational database don’t get called out more.
I’d very much rather have like 20 tables with clear purpose than seeing that colleagues have once more created a “classifier” mechanism and are using polymorphic links (without actual foreign keys, columns like “section” and “entity_id”) and are treating it as a grab bag of stuff. One that you also need to read the application code a bunch to even hope to understand.
Whenever I see that, I want to change careers. I get that EAV has its use cases, but in most other cases fuck EAV.
It’s right up there with N+1 issues, complex dynamically generated SQL when views would suffice and also storing audit data in the same DB and it inevitably having functionality written against it, your audit data becoming a part of the business logic. Oh and also shared database instances and not having the ability to easily bootstrap your own, oh and also working with Oracle in general. And also putting things that’d be better off in the app inside of the DB and vice versa.
There are so many ways to decrease your quality of life when it comes to storing and accessing data.
There's a great book SQL Antipatterns, by Bill Karwin where this specific antipattern is discussed and criticized.
That said, sometimes when I realize there's no way for me to come up even with a rough schema (say, some settings object that is returned to the frontend), I use JSONB columns in Postgres. As a rule of thumb, however, if something can be normalized, it should be, since, after all, that's still a relational database despite all the JSON(B) conveniences and optimizations in Postgres.
Typically you want your audit/log data to be immutable and kept in an append only data store.
Whether that's a typical relational DB or something more specialized (like a log shipping solution) that's up to you, but usually it would be separate from the main DB.
If you need some functionality that depends on events that have taken place, you probably want to store information about those events in the main data store (but only what's needed for that functionality, not a list of all mutations done to a table like audit data might include).
In general, it's nice to have such a clear boundary of where the business domain ends and where the aux. stuff to help you keep it running goes - your logs and audit data, analytics and metrics, tracing spans and so on.
Edit: as a critique of my own arguments here, I will admit that doing the above can introduce some complexity and that in simpler systems it might be overkill. But I've seen what happens when everything is just in one huge DB instance, where about 90% of the overall schema size is literally due to records in those audit tables and everyone is surprised why opening the "History" tab for a record takes a while (and anything else that references said history, e.g. visibility of additional records), and it's not great either.
I was also onboard with GP’s comment until I got to this part.
Audit data in the same DB is great, because it can be written transactionally for relatively cheap (multi table updates, triggers, actual transactions with multiple writes, etc).
After that, sure, ship it elsewhere and prune the audit tables if you like. But having the audit writes go directly to Kafka or whatnot is a pain because it requires your client logic to a) have a distributed publish-event transaction (which can work in this case more easily than distributed transactions in general with careful use of idempotency keys, read back, or transactional outboxes, but it’s complicated and requires everyone writing to auditable tables to play along), and b) reduces your reliability because now the audit store or its message queue needs to be online for every write as well as your database.
And there’s plenty of good reasons for business logic to use (only for reads) audit data. What else would business logic do if an audit table existed and there was a business need to e.g. show customers a change history for something? Build another redundant audit system instead?
> Avoid having five different services all write to the same table. Instead, have four of them send API requests (or emit events) to the first service, and keep the writing logic in that one service.
The ideal solution: Avoid having five different services all write to the same table.
If five different services have to write to the same table, there is a major overlap of logic too. Are the five services really different or one would suffice?
Taking practical realities into consideration, we can do what the author says. However, we risk implementing a lot of orchestration logic. We introduce a whole new layer of problems. Is that time not better spent refactoring the services: either give them their own DB tables or merge them into one servic?
An entire post about "good system design" that completely fixates on the solution domain, and doesn't talk about the problem domain at all. The hardest part of system design is the interface that the system presents to users. That determines how they will use it and what they can use it for.
A software system trades problems for different problems. e.g. We will manage your TODO list, provide consistency, durability, security, better than you could do yourself. But in order to get these benefits you have to understand our model, we have TODOs, users, lists, permissions, etc.
Decisions about the interface (what problems the system presents to the users) are the most consequential, and the most costly to get wrong. If you aren't spending most of your time arguing about the interface, then you are wasting your time arguing about things that are comparatively easier to change later. Literally everything else about the system can be changed without bothering the users.
It can be kind of horrifying at times. A couple of years ago I was interviewing for work and ended up talking to a big liquor distributor - their challenge was dealing with a bunch of text files over FTP, and it was comically bad, they had a $300k annual spend on AWS, Kubernetes, the works. And they could have done the whole thing on a single EC2 instance with a couple of shell scripts. Needless to say I was laughed out of the room.
Readit News
For any job-hunters, it's important you forget this during interviews.
In the past I've made the mistake of trying to convey this in system design interviews.
Some hypothetical startup app
> Interviewer: "Well what about backpressure?"
>"That's not really worth considering for this amount of QPS"
> Interviewer: "Why wouldn't you use a queue here instead of a cron job?"
> "I don't think it's necessary for what this app is, but here's the tradeoffs."
> Interviewer: "How would you choose between sql and nosql db?"
> "Doesn't matter much. Whatever the team has most expertise in"
These are not the answers they're looking for. You want to fill the whiteboard with boxes and arrows until it looks like you've got Kubernetes managing your Kubernetes.
I think three things about what you're saying:
1. The answers you're giving don't provide a lot of signal (the queue one being the exception). The question that's implicitly being asked is not just what you would choose, but why you would choose it. What factors would drive you to a particular decision? What are you thinking about when you provide an answer? You're not really verbalizing your considerations here.
A good interviewer will pry at you to get the signal they need to make a decision. So if you say that back-pressure isn't worth worrying about here, they'll ask you when it would be, and what you'd do in that situation. But not all interviewers are good interviewers, and sometimes they'll just say "I wasn't able to get much information out of the candidate" and the absence of a yes is a no. As an interviewee, you want to make the interviewer's job easy, not hard.
2. Even if the interviewer is good and does pry the information out of you, they're probably going to write down something like "the candidate was able to explain sensibly why they'd choose a particular technology, but it took a lot of prodding and prying to get the information out of them -- communications are a negative." As an interviewee, you want to communicate all the information your interviewer is looking for proactively, not grudgingly and reluctantly. (This is also true when you're not interviewing.)
3. I pretty much just disagree on that SQL/NoSQL answer. Team expertise is one factor, but those technologies have significant differences; depending on what you need to do, one of them might be way better than the other for a particular scenario. Your answer there is just going to get dinged for indicating that you don't have experience in enough scenarios to recognize this.
One of the things I tell people preparing for system design interviews is the more senior you are, the more you need to drive the interview yourself, knowing when to go deep, what to go deep on, and how to give the most signal to the interviewer.
But on the other side, that kind of interview process is itself also a signal candidates might take to avoid playing the game, knowing that most (not all, of course) companies probing for the wrong signals during the interview process are indicative of how they do function as a whole.
(been in both types of companies, and in both sides of the table)
But to your point, many times one interviews for a job they don't really have the luxury of getting rejections and need to land somewhere fast so they can keep paying the mortgage. So while yes interviewing is a two way street, there's still quite a bit of calibration to make sure you land on the other person's side of the street so to speak.
> "That's not really worth considering for this amount of QPS"
"What if Michael Jackson dies and your (search|news|celebrity gossip) service gets a spike in traffic way beyond the design parameters? How would you anticipate and mitigate such an event?"
(Extra points if the answer is not necessarily backpressure but they start talking about DDoS mitigation, outlier detection, caching or serving static results from extremely-common queries, spinning up new capacity to adjust to traffic spikes, blackholing traffic to protect the overall service, etc.)
> Interviewer: "Why wouldn't you use a queue here instead of a cron job?" "I don't think it's necessary for what this app is, but here's the tradeoffs."
"What if you have a subset of customers that demand faster responses than a cron job can provide?"
(And then that can become a discussion about splitting off traffic based on requirements, whether it's even worth adding the logic to split traffic vs. just using a queue for everyone, perhaps making direct API requests without either a queue or cron job for requests from just those customers, relying on the fact that they are not numerous or these requests are infrequent to trade capacity for latency, etc.)
> How would you choose between sql and nosql db?"
I would've expected the candidate to at least be able to talk about indexing, tradeoffs of joining in the DB vs. in the application, schema migrations and upgrades, creating separation between data-at-rest vs. data-in-flight, etc. If they can't do that and just handwave away as "whatever the team is most comfortable with", that's a legit hole in their knowledge. Usually you ask system design interviews of senior candidates that will be deciding on architecture and, if not hiring out the team directly, providing input to senior managers who will be hiring, so you can swap out the team nearly as easily as swapping out the architecture.
If a really good "tech" engineer ruled out all the places that are bad at interviewing, they would probably be unemployed.
You have to look past bad interviewing practice, to some degree.
> there's still quite a bit of calibration to make sure you land on the other person's side of the street so to speak.
Exactly. But if they try to Leetcode you, you have to decide whether you have any self-respect at all, or you're all just playing house together.
Those questions are all prompts to have a discussion in lieu of tech trivia hour. Those responses do not demonstrate wisdom, they reveal a lack of maturity. It's not the interviewers fault you refuse to be interviewed.
These ARE the answers we are looking for. As the system design interview (I’ve done hundreds) I want you to start with these answers then we can layer on complexity if you’ve solved the problem and there’s time left to go into navel gazing mode.
Seeing the panic slowly build in mid-level engineers’ eyes as it dawns on them that not every problem can be solved by caching is pretty fun too. “Ok cool you’ve cached it there, now how do you fill the cache without running into the same performance issue?”
Exactly. Part of the interview is explaining when and why these techniques are necessary as part of demonstrating your understanding.
If the candidate gives non-answers like “I don’t think it matters because you’re a startup” or “I’d just use whatever database I’m comfortable with” that’s not demonstrating knowledge at all. That’s dismissing the question in a way that leaves the interviewer thinking you don’t have that knowledge, or you don’t take their problems seriously enough to put thought into them. There is a type of candidate who applies to startups because they think nothing matters and they can YOLO anything together for a few years before moving on to the next job, and those are just as bad as the super over-engineering candidates.
The interview is your chance to show you know the topics and when to apply them, not the time to argue that the startup shouldn’t care about such matters.
Do you tell people this explicitly? If so, good on you; if not, please start! I think one of the biggest problems with interviews these days is misaligned expectations, particularly interviewees coming in assuming that what's desired is immediate evidence that they're so experienced in solving FAANG-scale problems that it's their default mode.
It's good not to over engineer, over engineering can be a cause of unneeded complexity, but when complexity is warranted the ability to solve for it simply is also needed.
More importantly though, you haven't explained or rationalized why?
It's not needed for this QPS? Oh ya? Why not? What's your magic threshold? When would it be needed? How do you plan for the team to know that time is approaching? If it's needed later how would you retrofit it? Is that going to be a simple addition? How do you know the max QPS won't be too high and that traffic won't be spiky? What if a surprise incident occurred that caused the system to overload, how would your design, without backpressure, handle that, how would you mitigate and recover?
In system design there's no real right answer, as an interviewer you're looking for the candidate to demonstrate their ability to identify the point of concerns, reason through the possibilities, explain their decisions and trade offs, and so on.
It’s like people crave complexity because it makes them, indispensable? Like if you’re the only one who knows how the billing reconciliation service works, they couldn’t possibly fire you?
They will.
Being pragmatic is something I look for in engineers. So long as they understand where to draw the line (and use a queue instead of cron). However that’s usually several years away at this point and them being able to say “You don’t need that, all you need is…” is welcome. Then again, that’s probably why I got fired. :shrug:
I first wrote code 50 years ago (I am 63yo) so yes, imo we are too old, but ...
It is worth noting that systems concepts/techniques often have analogues aka different names and histories in different fields and subfields.
If I were to "explain" back pressure to an ordinary person I might model my analogy to the logic of this ~classic joke:
Bob: Let's go to Trendio(TM) for dinner tonight!? Carol: Oh, nobody goes there anymore, it's too crowded!
Also, often a modern take-this-for-granted concept may be seen as an outgrowth of previous problems or solutions.
For example back pressure is conceptually adjacent to the clever~hack/design of random backoff in Ethernet.
Or if talking to a math geek or traffic planner you might relate it to ~modern understanding of congestion including oddities like possibly removing roads/routes to ~paradoxically improve traffic flow.
We are deep in the Information Age barreling towards Singularities, so none of us, young or old, see and understand but a tiny fraction of where we've been, are, or might be going.
Cue Calvin & Hobbes cartoon of us racing downhill in a fragile box.
Perhaps, as others have essentially suggested, merging your mind with an ~AI will help (albeit temporarily, imo). I prefer to think of us/greybeards as potentially Wise, yet, paradoxically, clueless.
Beginner's Mind, with likely no time/future for Mastery, is still potentially pleasant, and I would argue useful for Debugging.
Obviously this modern AI tsunami is phase shifting us all into debug~mode anyway, eh?
It's the opposite, as you get older you will feel this more and more.
But the keep the concept in your mind in case you have to distribute some problem. It's a central one.
Though you might be familiar with other terms that effectively mean the same thing, like counter pressure
(but worry yea not, just like someone said of another term: "Dependency Injection" is a 25-dollar term for a 5-cent concept, something is similar for this term. )
> > "That's not really worth considering for this amount of QPS"
There is a good way and a bad way to communicate this in interviews.
If an interviewer is asking about back pressure, they’re prompting you to demonstrate your knowledge of back pressure and how and when it would be applied. Treating it as an opening to debate the validity of the question feels like dodging the question or attempting to be contrarian. Explaining when and where you would choose to add back pressure would be good, but then you should go on to answer the question.
This question hits close to home for me because I was once working at a small startup that was dealing with a unique problem where back pressure really was the correct way to manage one of our problems, but we had a number of candidates do exactly what you did: Scoff at the idea that such a topic would be relevant at a startup.
If we’ve been dealing with a problem for months and a candidate comes in and confidently tells us that problem isn’t something we would experience and dismisses our question, that’s not a positive signal.
> > Interviewer: "How would you choose between sql and nosql db?"
> > "Doesn't matter much. Whatever the team has most expertise in"
This is basically a softball question. Again, if you provide a non-answer or try to dismiss the question it feels like you’re either dodging the topic or trying to be contrarian. It’s also a warning sign to the interviewer that you might gravitate toward what’s easy for you instead of right for the project.
This one also resonates with me because I spent years of my life making MongoDB do things that would have been trivial if earlier developers had used something like SQLite instead. The reason they chose MongoDB? Because the team was familiar with it. It was hell to be locked into years of legacy code built around the wrong tool for the job because some early employees thought it didn’t matter “because startup”
As an interviewer, let me give some advice: If an interviewer asks a question, you should answer the question. Anything that feels like changing the subject, dodging the question, or arguing the merits of the question feels like the candidate either doesn’t understand the topic or wants to waste time by debating the question.
It can be very valuable to explain when and why a topic would become necessary, right before you explain it. Instead of “this application has low QPS and therefore I will not answer your question” (not literally what you said, but how it comes across) you could instead explain how the need for back pressure could be avoided first by scaling servers appropriately and then go on to answer the question that was asked.
One time I was working in a body leasing company and our team was hired by bigco for an internal project. Two months earlier an internal employee was tasked to research the project and develop a prototype. When we started all major set pieces were written in stone. Month later said employee left. When we later checked the job listing he likely applied to our tech stack mirrored that to a letter. He got free training, a resume and a new job. We were stuck with these decisions for 3 years.
Another time a local branch of another bigco was trying to carve out a major piece of internal cake. Head-of was hired, team was quickly ramped up and they started cooking their foothold. Then a series of major power shifts happened couple levels above our pay grade and another branch came out with competitive strategy. We had a 2 days long internal brainstorm involving 50 people to come up with arguments and strategies how to defend our approach. We bet on blue, they were selling red. Life's were at stake. And many truly believed that blue was the way to go, and red was a recipe for disaster. Two days later we had a rock solid presentation that was trashing red approach. But if course most of these decisions are not made by nerds and middle mgmt do eventually the company placed their bet in red and the whole dept became redundant. No one likes to lose their jobs, so our blue head-of quickly turned his cloak and the team became an outsourcing provider for the winning team. What makes this story particularly funny is the fact that the head-of immediately started campaign of conference presentations where he sweard that all his life he believed that red was the future that will eventually trump blue, and any competition that is still using blue is destined to fail in short future.
Unless the initial question requirements are insane (build Twitter at Twitter scale), I start with the smallest, dumbest thing that will work. Usually that's a single machine/VM talking to a database (or even just SQLite!). Compute and storage are so fast these days that you could comfortably run your fledgling service on a Raspberry Pi, even serving three or four-digit QPS depending on the workload.
Of course, you still have to "play the game" in the interview, so make sure to be clear about how you'd change this as requirements changed (higher QPS, more features, etc)
"Backpressure? I don't think you'll have enough traffic to make backpressure necessary. The mode of failure here is that you run out of queue space and start dropping messages, and it's not a big deal if some messages get dropped here. But if we do decide that dropped messages are causing problems, and if it starts becoming a regular occurrence (we'll set up observability), here's how the producer can poll the queue size and return an error to the user under heavy load.
We’re at the start of another cycle of a lot of niche products followed by the rise of big Acme megacorps who conquer them all economies of scale that compete on margin. It comes just as we’re at the tail end of this cycle with tech as we knew it for the last 50 or so years.
The point of an interview is to lay bare one’s thought process entirely so that the interviewer has full awareness of the person you are. And to likewise extract that from the interviewer. Getting or transmitting less information is just underutilizing the time. Interviewers are also flawed and may not be good enough at extracting the information from you.
If you’re an ideal decision maker, you will likely out-skill the majority of interviewers. You’re being hired to make their org succeed. So just do that.
I think people who describe system designs frequently fail to demarcate the space they’re operating in, so subsequent engineers cannot determine whether the original designer failed to consider something or whether the original designer considered and dismissed something. The point is to be able to express this concisely.
IMHO, doing it well means that not only do you get it right but you send the information down through time so that subsequent observers understand why and also get it right consequently.
Real companies do. The moment you deploy one line of code, it's legacy. It goes from there. Soon you have to build systems that interface with other systems you'd rather were better architected and designed, except you have to deal with them as they are. Then your product becomes one of these, and with no need to maintain or expand it for a long time, it rots a bit, and now someone has to pick it up or interface with it, and your product made things more complex, and the complexity can't be magic wand waved away.
If you can't, you might be getting interviewed by people you do not what to work with and you should want to know that.
That becomes obvious when you start bootstrapping an HA cluster with multiple control plane nodes.
K8s is not for the faint of heart…or rational system designers ;)
People ask for fizzbuzz in parallel not because it's practical.
Your answers are completely valid but you have to communicate to the interviewer that you considered the possibilities and the tradeoffs.
If the interviewer needs to "forcefully" extract from you the logic behind your design choices than a lot of times that's enough to fail you.
Dismissive answers that assume they are needlessly over complicating things tells them exactly what they need to know
> SQL and NoSQL don’t matter much
Database is literally the most important architectural decision possible, next to the application programming language.
(Prove me wrong)
I have some remarks though. Taken from the article:
> Avoid having five different services all write to the same table. Instead, have four of them send API requests (or emit events) to the first service, and keep the writing logic in that one service.
This is not so cut-and-dry. The trade offs are far from obvious or acceptable.
If the five services access the database then you are designing a distributed system where the interface being consumed is the database, which you do not need to design or implement, and already supports authorization and access controls out of the box, and you have out-of-the-box support for transactions and custom queries. On the other hand, if you design one service as a high-level interface over a database then you need to implement and manage your own custom interface with your own custom access controls and constrains, and you need to design and implement yourself how to handle transactions and compensation strategies.
And what exactly do you buy yourself? More failure modes and a higher micro services tax?
Additionally, having five services accessing the same database is a code smell. Odds are that database fused together two or three separate databases. This happens a lot, as most services grow by accretion and adding one more table to a database gets far less resistance than proposing creating an entire new persistence service. And is it possible that those five separate services are actually just one or two services?
You absolutely should design and implement it, exactly because it is now your interface. In fact, it will add more constraints to your design, because now you have different consumers and potentially writers all competing for the same resource with potentially different access patterns. Plus the maintenance overhead that migrations of such shared tables come with. And eventually you might have data in this table that are only needed for some of the services, so you now need to implement views and access controls at the DB level.
Ideally, if you have a chance to implement it, an API is cleaner and more flexible. The problem in most cases is simply business pushing for faster features which often leads to quick hacks including just giving direct access to some DB table from another service, because the alternative would take more time, and we don't have time, we want features, now.
But I agree with your thoughts in the last paragraph. It happens very often that people don't want to undertake the effort of a whole new design or redesign to match the evolving requirements and just patch it by adding a new table to an existing DB, then another,...
PostgreSQL, to name one example, can handle every one of these challenges.
Moving your data types from SQL into another language solves exactly 0 migration problems.
Every migration you can hide with that abstraction language you can also hide in SQL. Databases can express exactly the same behaviors as your application code.
APIs can be evolved much more easily than shared database schemas. Having worked with many instances of each kind of system, I think this outweighs all of the other considerations, and I don't think I'll ever again design a system with multiple services accessing the same database schema.
It was maybe a good idea if you were a small company in the early 2000s, when databases were well-understood and services weren't. After that era, I haven't seen a single example of a system where it wasn't a mistake for multiple services to access the same database schema (not counting systems where the read and write path were architecturally distinct components of the same service.)
So this service was basically an "universal integration service". Company wanted to share some data, and they wanted to implement it in an universal way. So basically I've implemented SOAP web service which received request with SQL text and responded with list of rows. This service was surprisingly popular and used a lot.
I was smart enough, so I built a limited SQL syntax parser and UI, so administrator could just set up tables and columns they wanted to share for this specific client. SQL query was limited in a sense that it worked only with one table, simple set of columns and some limited conditions (that I bothered to implement).
The reason I've heard about it few months ago is that they shared with me, that they caught a malicious guy, who worked at some company integrating with this system and he tried to do SQL attack. They noticed errors in the logs and caught him.
Their database is pretty much done and frozen, regarding to schema. They hardly evolve it. So this service turned out ot be pretty backwards-compatible. And simple changes, of course, could be supported with view, if necessary.
When you need to alter the datastore, usually for product or scalability, you have to orchestrate all access to that datastore.
Ergo: one only one thing using the datastore means less orchestration.
At work, we just updated a datastore. We had to move some tables to their own db. 3 years later, 40+ teams have updated their access. This was a product need. If this was a scale issue, the product would just have died sans some as of yet imagined solution.
Deleted Comment
Counterpoint (assuming by database you mean database cluster, not a schema): having a separate physical DB for each service means that for most places, your reliability has now gone from N to N^M.
Nice boxes in the architectural diagram. Each box is handed to a different team and then, when engineers from those teams don't talk to each other, the system doesn't suddenly fail in an unexpected way.
Oh yes! Never do a join in the application code! But also: use views! (and stored procedures if you can). A view is an abstraction about the underlying data, it's functional by nature, unlikely to break for random reasons in the future, and if done well the underlying SQL code is surprisingly readable and easy to reason about.
Writing raw SQL views/queries per MVC view in SSR arrangements is one of the most elegant and performant ways to build complex web products. Let the RDBMS do the heavy lifting with the data. There are optimizations in play you can't even recall (because there's so many) if you're using something old and enterprisey like MSSQL or Oracle. The web server should be able to directly interpolate sql result sets into corresponding <table>s, etc. without having to round trip for each row or perform additional in memory join operations.
The typical ORM implementation is the exact opposite of this - one strict object model that must be used everywhere. It's about as inflexible as you can get.
The author's said nothing about ORMs. It feels like you're trying to post a personal beef about ORMs that's entirely against the "pragmatic" software design engineering the author's opining. Using ORMs to massively reduce your boiler-plate CRUD code, then using raw SQL (or raw SQL + ORM doing the column mapping) for everything else is a pragmatic design choice.
You might not like them, but using ORMs for CRUD saves a ton of boilerplate, error-prone, code. Yes, you can footgun yourself. But that's what being a senior developer is all about, using the tools you have pragmatically and not foot gunning yourself.
And it's just looking for the patterns, if you see a massive ORM query, you're probably seeing a code smell. A query that should be in raw SQL.
You can write reusable plain functions as abstractions, returning QuerySets that allow further filters being chained onto the query, before the actual SQL is materialized and sent to the database.
The result of this doesn’t have to match the original object models you defined, it’s still possible to be flexible with group bys resulting in dictionaries.
> Particularly if you’re using an ORM, beware accidentally making queries in an inner loop. That’s an easy way to turn a select id, name from table to a select id from table and a hundred select name from table where id = ?.
Rails makes this easy to avoid. Using `find_each` batches the queries (by 1,000 records at a time by default).
Reading through the comment section on this has been interesting. Either lots of people using half baked ORMs, people who have little experience with an ORM, or both.
I can't respond to the "typical" part as most of my experience is using EF Core, but it's far from inflexible.
Most of my read-heavy, search queries are views I've hand written that integrate with EF core. This allows me to get the benefit of raw SQL, but also be able to use LINQ to do sorting/paging/filtering.
In particular, have you have to do testing, security (eg. row level security), manage migrations, change management (eg. for SOC2 or other security frameworks), cache offloads (Redis, and friends), support for microservices, etc.
Comments like this give me a vibe of young developers trying out Supabase for the first time feeling like that approach can scale indefinitely.
T-SQL was not a good programming language last century when it was vaguely current, and so no I do not want to write any significant amount of code in T-SQL. For my sins I maintain a piece of software with huge T-SQL procedures (multi-page elaborations by somebody who really, really like this stuff) and they're a nightmare. The tooling doesn't really believe in version control, the diagnostics when you make a mistake are either non-existent or C++ style useless spew.
We hire a lot of very junior developers. People who still need to be told not to comment out code in release, that variable numbers are for humans to read not machines, that sort of thing. We're not quite hiring physicists to write software (I have done that at a startup) but it's close. However, none of the poor "My first program" code I see in a merge request by a new hire is anywhere close to as unreadable as the T-SQL we already own and maintain.
Stored procedures also add another risk. You have to keep them in sync with code, making releases more error prone. So you have to add extra layers of complexity to manage versioning.
I can see the advantage of extreme performance/efficiency gains, but it should be really big to be justified.
There was _one guy_ who maintained it and understood how it worked. He was very smart but central to the company’s operations. So having messy stuff makes it brittle/hard to change in more ways than one and
The “backend” scales much easier than the database. Loading data by simple indexes, eg. user_id, and joining it on the backend, keeps the db fast. Spinning up another backend instance is easy - unlike db instance.
If you think, your joins must happen in db, because data too big to be loaded to memory on backend, restructure it, so it’s possible.
Bonus points for moving joins to the frontend. This makes data highly cacheable - fast to load, as you need to load less data and frees up resources on server side.
If you want a KV store, use a KV store. If you want an RDBMS, then use its features. They haven’t changed much in the last 50 years for a reason.
Let's say you run a webshop and have two tables, one for orders with 5 fields, one for customers, with 20 fields.
Let's say you have 10k customers, and 1m orders.
A query performing a full join on this and getting all the data would result in 25 million fields transmitted, while 2 separate queries and a client side manual join would be just 5m for orders, and 200k for customers.
But usually you need some of the orders and you need the customer info associated with them. Often the set of orders you’re interested in might even be filtered by attributes of the customers they belong to.
The decision of whether to normalize our results of a database query into separate sets of orders and customers, or to return a single joined dataset of orders with customer data attached, is completely orthogonal to the decision of whether to join data in the database.
It's very natural to want customer information when querying an order, and if you have a view like orders_with_customer_info, you get that with zero effort when querying that view by order id.
You also get consolidated data (orders by customer) by doing
which I think is pretty straightforward.Deleted Comment
I worked on an application which joined across lots of tables, which made a few dozen records balloon to many thousands of result rows, with huge redundancy in the results. Think of something like a single conceptual result having details A, B, C from one table, X, Y from another table, and 1, 2, 3 from another table. Instead of having 8 result rows (or 9 if you include the top level one from the main table) you have 18 (AX1, AX2, AX3, AY1, ...). It gets exponentially worse with more tables.
We moved to separate queries for the different tables. Importantly, we were able to filter them all on the same condition, so we were not making multiple queries to child tables when there were lots of top-level results.
The result was much faster because the extra network overhead was overshadowed by the saving in query processing and quantity of data returned. And the application code was actually simpler, because it was a pain to pick out unique child results from the big JOIN. It was literally a win in every respect with no downsides.
(Later, we just stuffed all the data into a single JSONB in a single table, which was even better. But even that is an example of breaking the old normalisation rule.)
That doesn't really sound like a place where data is actually conceptually joined. I expect, as it is something commonly attempted, that you were abusing joins to try and work around the n+1 problem. As a corollary to the above, you also shouldn't de-join in application code.
As a somewhat contrived example since I just got out of bed, if your software has a function that needs all the invoice items from invoices from this year which invoice address country is a given value, use a join rather than loading all invoices, invoice addresses and invoice items and performing the filtering on the client side.
Though as you point out, if you just need to load a given record along with details, prefer fetching detail rows independently instead of making a Cartesian behemoth.
When I use to interview to be a developer at a company, it was always an automatic no for me if a company kept business logic in stored procedures and had a separate team of “database developers”.
As far as not doing joins in code, while I agree for the most part. GitHub itself has a rule against joining tables using sql that belong to different domains.
https://github.blog/engineering/infrastructure/partitioning-...
Then companies buy a solution to aggregate all the different databases in a single "data-lake" (or whatever buzzword is hot right now) so you can do OLAP queries. Without consistency guarantees of course.
And I am not saying this is never the _right_ solution, but it should almost never be the _first_ solution
Not saying this should always be the case, but sometimes it is the right call.
To quote Douglas Adams: "The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
Likewise, if you cache a piece of data in your application because you assume that it won't change, that just makes it likely that if and when it does change, you'll have bugs. Moving the cache to the database layer so that it can be properly invalidated fixes this.
It's true that an application-side join can still be more performant if the DB cache isn't good enough, but IMO you should only take that step after actually profiling your queries.
If you have some complex queries on every page load with a huge number of users, put it in the DB as much as possible.
If you need to iterate over a bunch of records and do something based on some combination of values, and it's for a weekly reporting thing, I'd much rather see 3 nested foreach loops with a lot of early exits to skip the things you don't care about than a multi-kb SQL-statement that took two days to develop and nobody every dares to touch again because it's hard to handle.
A great software design will separate all business logic into its own layer. That might be a distinct project, module, or namespace, depending on what your language supports. Keep business logic out of SQL and out of web server code (controllers, web helpers, middleware, etc.).
Then you're treating SQL as the data store it is designed to be. When you embed application logic in SQL, you're hiding core functionality in a place where most developers won't expect to find it. This approach also creates tight coupling between your application and your database provider, making it hard to switch as needs change/the application grows.
For example, you may want to (or have the option to) vertically partition your database, or use different data stores. The app layer is usually stateless and can scale perpetually, but the database might be a bottleneck.
Joining in the database over the application is a great default. But I wouldn't say "never join in the application code".
Deleted Comment
Depending on the ecosystem the code base adopts a good orm might be a better choice to do joins.
Elaborate?
Also SQL is easy, but figuring out what's up with indexes and planner is not.
Rings very true. Engineers are rated based on the "complexity" of the work they do. This system seems to encourage over-engineered solutions to all problems.
I don't think there is enough appreciation for KISS - which I first learned about as an undergrad 20 years ago.
Sure, there are problems that are inherently complex and require complex solutions. But most likely yours isn't one of them, most likely you have a basic web app.
I’m surprised that the drawbacks of EAV or just using JSON in your relational database don’t get called out more.
I’d very much rather have like 20 tables with clear purpose than seeing that colleagues have once more created a “classifier” mechanism and are using polymorphic links (without actual foreign keys, columns like “section” and “entity_id”) and are treating it as a grab bag of stuff. One that you also need to read the application code a bunch to even hope to understand.
Whenever I see that, I want to change careers. I get that EAV has its use cases, but in most other cases fuck EAV.
It’s right up there with N+1 issues, complex dynamically generated SQL when views would suffice and also storing audit data in the same DB and it inevitably having functionality written against it, your audit data becoming a part of the business logic. Oh and also shared database instances and not having the ability to easily bootstrap your own, oh and also working with Oracle in general. And also putting things that’d be better off in the app inside of the DB and vice versa.
There are so many ways to decrease your quality of life when it comes to storing and accessing data.
That said, sometimes when I realize there's no way for me to come up even with a rough schema (say, some settings object that is returned to the frontend), I use JSONB columns in Postgres. As a rule of thumb, however, if something can be normalized, it should be, since, after all, that's still a relational database despite all the JSON(B) conveniences and optimizations in Postgres.
What's the "proper" way to do this? Separate DB? Separate data store?
Whether that's a typical relational DB or something more specialized (like a log shipping solution) that's up to you, but usually it would be separate from the main DB.
If you need some functionality that depends on events that have taken place, you probably want to store information about those events in the main data store (but only what's needed for that functionality, not a list of all mutations done to a table like audit data might include).
In general, it's nice to have such a clear boundary of where the business domain ends and where the aux. stuff to help you keep it running goes - your logs and audit data, analytics and metrics, tracing spans and so on.
Edit: as a critique of my own arguments here, I will admit that doing the above can introduce some complexity and that in simpler systems it might be overkill. But I've seen what happens when everything is just in one huge DB instance, where about 90% of the overall schema size is literally due to records in those audit tables and everyone is surprised why opening the "History" tab for a record takes a while (and anything else that references said history, e.g. visibility of additional records), and it's not great either.
Audit data in the same DB is great, because it can be written transactionally for relatively cheap (multi table updates, triggers, actual transactions with multiple writes, etc).
After that, sure, ship it elsewhere and prune the audit tables if you like. But having the audit writes go directly to Kafka or whatnot is a pain because it requires your client logic to a) have a distributed publish-event transaction (which can work in this case more easily than distributed transactions in general with careful use of idempotency keys, read back, or transactional outboxes, but it’s complicated and requires everyone writing to auditable tables to play along), and b) reduces your reliability because now the audit store or its message queue needs to be online for every write as well as your database.
And there’s plenty of good reasons for business logic to use (only for reads) audit data. What else would business logic do if an audit table existed and there was a business need to e.g. show customers a change history for something? Build another redundant audit system instead?
The ideal solution: Avoid having five different services all write to the same table.
If five different services have to write to the same table, there is a major overlap of logic too. Are the five services really different or one would suffice?
Taking practical realities into consideration, we can do what the author says. However, we risk implementing a lot of orchestration logic. We introduce a whole new layer of problems. Is that time not better spent refactoring the services: either give them their own DB tables or merge them into one servic?
A software system trades problems for different problems. e.g. We will manage your TODO list, provide consistency, durability, security, better than you could do yourself. But in order to get these benefits you have to understand our model, we have TODOs, users, lists, permissions, etc.
Decisions about the interface (what problems the system presents to the users) are the most consequential, and the most costly to get wrong. If you aren't spending most of your time arguing about the interface, then you are wasting your time arguing about things that are comparatively easier to change later. Literally everything else about the system can be changed without bothering the users.