---
title: 'System Design Mock Interview: Design Yelp with a Meta Staff Engineer'
source: 'https://youtube.com/watch?v=yz1jtze4qr8'
video_id: 'yz1jtze4qr8'
date: 2026-08-04
duration_sec: 2289
---

# System Design Mock Interview: Design Yelp with a Meta Staff Engineer

> Source: [System Design Mock Interview: Design Yelp with a Meta Staff Engineer](https://youtube.com/watch?v=yz1jtze4qr8)

## Summary

In this video, a Meta staff engineer conducts a live system design mock interview for a Yelp-like local business review service, walking through the standard framework of requirements, core entities, APIs, high-level design, and deep dives. The session covers key decisions around availability vs. consistency, API design, handling concurrent reviews, and optimizing search with PostGIS and full-text search.

### Key Points

- **Introduction and Setup** [00:02] — The engineer introduces a different format: walking through common system design interview questions live, selecting Yelp as the problem and choosing the senior level for follow-up questions.
- **Functional Requirements** [00:43] — Users can search for businesses by name, location (lat/long), and category; view a business and its reviews; and leave a review with a 1-5 star rating and optional text. Scale: 100M DAU, 10M businesses, one review per user per business.
- **Framework Introduction** [01:38] — The engineer uses a common delivery framework: requirements, core entities, API, data flow, and deep dives, guiding the interview step-by-step.
- **Non-Functional Requirements** [02:07] — Prioritizes high availability over strong consistency, as stale data (e.g., reviews) is acceptable. Latency target: <500ms for search. Scalability to 100M businesses is critical. Compliance is important but not unique to Yelp.
- **Core Entities** [05:31] — Identifies core entities: Business, Review, and User, with foreign keys between them. Emphasizes simplicity and foundational thinking.
- **API Design** [06:36] — Designs REST APIs: GET /businesses?category=&lat=&lon=&name= for search, GET /businesses/{id} for details, POST /businesses/{id}/reviews for creating reviews. Uses plural nouns, path parameters for required IDs, and query parameters for optional filters.
- **Pagination Feedback** [12:17] — Adds pagination (page, limit) to search and review endpoints to handle large result sets, and splits business details and reviews into separate endpoints for lazy loading.
- **High-Level Design: Search** [13:53] — Client → API Gateway → Business Service → Business DB. Simple SQL query initially, but acknowledges inefficiency for 2D location queries; will optimize later.
- **High-Level Design: View Business** [17:30] — Client → API Gateway → Business Service → Business DB, querying business and review tables by business ID. Suggests adding rating info to business table for search/filter.
- **High-Level Design: Leave Review** [19:49] — Separate Review Service for independent scaling. Writes to primary DB (Postgres) directly. Justifies single DB due to small data size and direct relationship.
- **Feedback on Authentication** [21:34] — Engineer misses authentication in the leave review flow; interviewer feedback highlights need for API gateway authentication and validation of rating range.
- **Deep Dive: Average Rating Calculation** [22:48] — Rejects message queues and cron jobs as overkill; uses a transaction to add review and update num_ratings and avg_rating on the fly, given low write volume (~5 reviews/sec).
- **Deep Dive: Concurrency Control** [27:10] — Addresses race conditions with row locking (pessimistic) or optimistic concurrency control using version fields (avg_rating, num_ratings) to detect conflicts and retry.
- **Deep Dive: One Review Per User** [29:34] — Emphasizes putting constraints at the persistence layer: a composite unique key on (user_id, business_id) in the database, not just application logic.
- **Deep Dive: Search Optimization** [32:51] — Avoids Elasticsearch to reduce complexity; uses Postgres extensions: PostGIS for geospatial indexing and full-text search for name/category, achieving <500ms latency.
- **Results and Wrap-up** [36:51] — Scored 95/100, above average (90) for senior-level Yelp. Encourages viewers to practice on hello.com/practice and provides whiteboard link.

### Conclusion

The video demonstrates a complete system design interview for Yelp, emphasizing trade-offs between availability and consistency, proper API design, and pragmatic solutions like using Postgres extensions over introducing new technologies. The engineer's 95/100 score shows the effectiveness of the structured framework and clear communication.

## Transcript

unique, a little different this go round and that would be walking through a the common system design interview questions and just let you guys sort of watch over my shoulder as I talk out loud and walk through how I would solve
some of these common interview questions. And so I'm here, you know, we have obviously plenty to choose from. I actually didn't decide in advance which one of these I want to do. Uh, let's go ahead and let's go ahead
and do Yelp. I haven't thought about Yelp in a while. Of course, I still the problem, so it's maybe a little unrealistic in that way, but it's one that I'm I'm less familiar with at least. So, let's do this one. Um, we're
going to design a local business review site like Yelp. And I'm going to select my level as senior so that we can get some good follow-up questions here. Um, that the functional requirements are going to be that a user should be able
to search for businesses by name, location, which would be latitude and longitude and category. Makes sense. Um, users should also be able to view a business and their reviews. So, I'm assuming we search, we click on a
and its reviews. And then when I'm on that page, it says here I should be able which will include a 1 to five star rating as well as optional text. So, pretty straightforward. uh a simple, you know, business review site. From the
scale perspective, looks like we have 100 million daily active users and 10 million businesses. And then each user can only leave one review per business. Yeah, makes sense. Okay. Uh we're going to walk through the
common delivery framework of course. So requirements, core entities, API, data For anyone watching this isn't familiar, this is how we recommend you approach this is just going to guide us step by step through each of these. All right,
let's do it. This should be fun. Um, okay. Starting off, what are the nonfunctional requirements of the system? So of course the non-functional requirements, these are statements about the qualities of the system. So the
functional requirements which were given to us are user should be able to statements. They're like the features of the system. And so it says here the qualities that we should consider cap theorem environment constraints
scalability latency durability. So these are some of the common ones here. Okay. So in the case of Yelp, let me zoom in to make it easier for you guys. Um want high availability or strong
strong consistency would mean in this case. If we had strong consistency, then it means that if a user were to update the description or something of a business, say in Germany, and I'm here in the United States reading from a
in the United States reading from a replica, then I should be getting errors um until that data has fully propagated, right? Um that doesn't seem necessary to totally reasonable that somebody updates the description and it takes me a couple
business with the old description. Or another example is somebody adds a new review. On my side, I see the old reviews. I don't yet see the new review seconds, minutes, whatever. That seems totally fine to me. I would way rather
reviews, even if they're a little bit stale, than making sure that they are, you know, perfectly consistent across our system and across the globe. So, I'm going to say that availability in this case, maybe let me be more specific. So,
high availability, you guys are going to see how bad of a typist I am on the spot. High availability is much greater than strong consistency here. Uh what constraints. This would be like on mobile devices and things. I don't see
anything relevant there. Scalability. Well, they told us the scale. So yes, Well, they told us the scale. So yes, our system needs to scale to 100 million businesses. That's definitely going to be an interesting part of the design. Um
non-functionals. Let me kind of break it aside here. uh these are going to inform what we're going to talk about later, what we're going to focus on. So that's interviews. So we're going to talk about scale at some point I'm sure. Um
latency. So latency is a good one. If we look at our functional requirements, the have a system design interview that requires search over tons of rows, um,
parameters like a location, well, we know that usually is going to take a optimizations. And so, it's a good thing but it's not enough to just say low latency. I want low latency search.
specific problem. And I'm going to quantify it, too. I want it to be less than 500 milliseconds. So, that's going to be important to me. Um, coming back, compliance. To be honest, these are important.
They're important for every single product, but they're not uniquely interesting to Yelp. Um, so I'm not going to write them down here. I don't want them to distract me later on. So, I think I feel good with this list, but
let's let's find out uh what the system thinks, if this is enough or if it wants us to add something else. So, it's analyzing that feedback right now. It's looking at the whiteboard. We nailed it. Okay, so great answer. We correctly got
them. It seems like that's everything that it wanted. Um, so that's great. Let's keep moving. Next up is our core entities. So the core entities here, system. They're basically the tables that you're going to have in your
database. Um, they're the things that are p persisted in your database and are exchanged via your API. And so it's really useful to think about these early in the design. And it's it's just a simple list, right? And so this is this
is super easy up front, but it gets you thinking about the right things. And so for us, the core entities of Yelp, let's see here. Obviously, we have a business b I spell it right? Business. Uh there's
There's going to be a foreign key between those two. Uh and then guess we So similarly a foreign key between those. Um
I think I feel good about that. There's nothing nothing I think I would add. So, let's see. These core entities should be simple. You're not spending a lot of your core entities, but they are really important for you to have that
foundational thinking. Cool. We nailed it. Uh, great answer. Anything it's recommending? No. Sweet. Okay. So, we are on a roll. I'm perfect so far. Let's see if that continues. Long may continue. Um, okay. Next question here.
What are the external REST APIs that your system will expose in order to your system will expose in order to satisfy the functional requirements? All right, so whenever we get to the API section, we're just going to go one by
one through our functional requirements and making sure that we have an A API or sometimes multiple APIs in order to satisfy them. Let me zoom in for you guys. So our first one is that users should be able to search for businesses
by name, location, and category. So let's create an API endpoint for that. this is going to be a git. I'm going to use REST here. You're almost always going to use REST to be honest. Um, obviously maybe not every time, but if
you're not sure, REST is a a great place to start. So, I'm going to use a REST searching for? I'm searching for businesses. Now, you'll note here that I made this plural. This is proper REST semantics. You have plural nouns as your
resource names. So, I'm searching. I'm getting What am I getting? I'm getting businesses. And then we probably want some query parameters here because we talked about a couple of things. We said that there was category. So we're going
to be searching on some category. Uh we're also going to have a location which we said was going to be a latitude and a longitude. And then like a name, the name of the business. And so I'm I'm using this syntax just to denote that we
would have something there, you know, like this would be category of food, restaurant, whatever, right? But I'm just putting that in there as a as a placeholder. Um, and then what would this thing return? Well, this thing is
going to return a list of businesses. Maybe even more accurately, would we business information in our search results? Probably not. If you've used
that require search, they show you just like a little bit of information. Like name of the business, the image, and a short description, right? It's not going to tell us all the things that are in the business table. So, I use TypeScript
notation to do this, but you can do this however you want really. But, we're going to return partial businesses. This will keep our payload small um so that it's it's even quicker. Okay, feel good about that. There's our there's our
search endpoint there. Now, going back to our functional requirements, users should be able to view businesses and their reviews. So, let's come back to their reviews. So, let's come back to our API. The next one is view businesses
and their reviews. And so again, this is going to be a get request because we're getting resources. And what are we getting? Well, you might be tempted to say like, well, actually, let me let me not do that yet. We're
getting a business. So, we're getting businesses, plural noun again, right? Um, and then we need the ID of the business. And so you'll see here this In reality, maybe it's something like this, right? Colon ID says that we put
the ID there. And this is what's called a path parameter. Whereas this was a query parameter. These are optional. That's why we used query parameters. This is mandatory. You can't view a business without an ID. So you might
have been tempted to do something like this, like could this work? Yeah, of course it could work. Um, it's just not proper restful semantics. And you might have an interviewer who cares. And so what you should think about is that if
this parameter is required, I'm going to use a path parameter. If it's optional, so in our case, we want to get the business for this given ID. And what's it going to return? It's going to return the business. And it should also return
the business. And it should also return those list of reviews, right? Um, okay. So two of our three done. The next one is the user should be able to leave is the user should be able to leave reviews on a business. And so we can say
post a review. You can tell my use of verb there is intentional because our endpoint is now a post endpoint because we're creating a resource. And what resource are we creating? Um well, we're creating a review resource. But we're
creating a review on a business. And so watch this. The proper restful semantics here is that I have a business. I need to know which business. And then I have reviews plural. And so I'm creating a review for the business with this ID.
This is how we uh you know actually don't know the the proper restful terminology for this but like kind of stacking or chaining our resource names like this um so that there's this direct relationship. So I need to know which
business and its ID and the resource that I'm creating is the reviews and then we should outline our post body. What's in here? What happens when we post this? What's in the body that we send in the request? Um what did we say?
to five. That's going to be mandatory. And then we're going to have that optional text review like, "Wow, I loved it. It was so great." Right? That sort of thing. Um, okay, let me just look at it. Yeah, I
return the review itself. That's common. It could just return a 200 if it's successful or of course a different message if it's not. And maybe one other thing that I'll call out is that like this one
needs authentication. Needs you need to be authenticated. This is kind of a fun thing to call out in the interview also. So, these ones, I non-logged users search for businesses is probably good for my business. But,
to be logged in. So, I'm just going to label it like that. Um, okay. Enough of me ranting. Let's see how we did here. Did we go three for three or have we made our first mistake? Time is going to tell.
But we have some suggestions here. Consider adding pageionation parameters like page and limit to your search endpoint since the business searches. mistake by me. This could return millions of resources. And so we should
course, right? So maybe I'm going to have a page and then maybe I'm going to have a limit uh you know like how many per page. So let's add that. That's good feedback. The view business endpoints might benefit from pageionation for the
reviews in particular. Totally. Popular reviews could have hundreds impractical to load at once. Yeah, of course. Let's see what happens if I I learn more there see what happens if I I learn more there too.
have all the reviews. Yeah. Okay. Um, actually is that I'm going to do this. I'm going to do businesses slash id
reviews. So I'm going two endpoints here and they're going to be called for my front end in parallel. But that way I'm going to put pageionation on this one so other one. And this is going to be nice too because you can think about like
lazy loading. We're going to load the business details right away. And then below it there could be some skeleton waiting to load the actual uh reviews as little bit longer. So, let me try again
know that I already got it perfect, but uh I want to just get no more uh I want to just get no more suggestions there.
Uh next up. So, we did the requirements, we did the core entities, we did the APIs. Now, let's go over to that highle design. And so the first question in our highle design, we map these one to one to our functional requirements and to
our API endpoints. And so that first one is how will users be able to search for is how will users be able to search for a business? Um, so let's get in here and let's do some do some fun drawing. This is my favorite part getting to draw
here on the whiteboard. So I'm going to have a client. I'm going to add an API premature. My API gateway's responsibility is to take incoming to the right micros service. I know that I'm going to have more than one service
I'm only going to have one. So I'm just going to put it there anyway to save me the drawing later on. Um make these birectional. So I'm going to API gateway and then I'm going to have a
and then I'm going to have a business crud service. actually let me just have a business service because specifically it's handling search right now. Um the creation of businesses and all that is
functional requirements. And I'm going to need some business DB. And so what's in our business DB? Uh we're going to have a business. It's
going to have a name. It's going to have a description. It's going to have an address. It's missing? It's probably going to have an S3 link. A bit out of scope there, but
for any images. Um, of course, we'll have reviews, but we'll bring that in later. So, there's some my basic stuff. Now, when a user wants to search, we beginning here, it's just our highle design. So, maybe all that we do is we
just query our our database directly here. So we just have like some select here. So we just have like some select star from business where category. Oh well that teaches me that I was missing something. Category
something. Category you know equals whatever location but things right so we can have a simple query there. Um all right let me let me try to explain this really briefly. All right. So, in order to search for
businesses, the client is going to make a request to the API gateway with our API endpoint to search. The gateway is going to route that to our business service. Our business service is then going to formulate a query where that
business database for those three parameters that we cared about, the category, the name, and the latitude and longitude. Now, importantly, this isn't very efficient yet. Of course, the more rows that we have and having this
two-dimensional query even with indexing on location isn't going to be optimal. Um, but we'll come back later and and make it better as we go. This is a simple design for now. That should satisfy this question.
I was a little verbose there maybe, but you know, when you're on the spot, what are you going to do? So, let's see if it liked it enough. Anyway, so user searching for businesses, just a simple highle design.
Great. Offer letter incoming. Uh, consider adding rating information to your business table. You'll want to search and filter for that. Fair enough. Yeah. Um, so I probably should have a Let me move this over. I should probably
That's something that would be important in the search results. Something we in the search results. Something we might even talk about. Um, okay. Let's keep moving. I like it. Um, how will users be able to view a
business details and its reviews? Well, the first thing is that we need to have a review table in the first place. So, I'm going to have a review. A review is going to have an ID. It's going to have a user ID. It's going to have the
business ID. It's going to have the rating and it's going to have the text optionally. And if I've done that, then obviously have a user table. That user else in the user table I don't care about. My interviewer doesn't care
about. We all know hashed password and some account settings and stuff. All good. Um, this one's actually straightforward though. We don't need to change anything because we're just going to query now just a different query,
right? So from businesses where business ID equals whatever that ID is. Um, and business table. So let me let me let it know that.
Um, so for users to be able to view business details and its reviews, the client is going to make a git request with the business ID. The API gateway will route that to my business service, which will then construct a query of my
there that are relevant, the business table and the review table respectively. And so this query will join the reviews of a given business ID with the information for the business all for the ID that was in my parameter of my API
request. and we'll return that full payload back to the client. Um, yeah. Now, the reason I was pausing there is because I remembered in our API we split those up. Um, I think it'll still be
happy with this because it was happy enough with it with my pageionation, but the end. Technically speaking, I should have had those as two different API have had those as two different API requests. Um,
reviews came in. That's pretty important. So, let me just add that for later. Created at And what else did it say here? Consider showing the join on the
whiteboard. Nah, screw that. Talking about it, I think, was fine in an writing these SQL queries. This was me just being more illustrative for you just being more illustrative for you guys who are following along. Um, okay.
Next question. How will users be able to leave reviews on businesses? Cool. So, now we have that API post request. I'm going to have that go to a different review service. The reason being here that there's a it can scale
independently. It has a different pattern. There's far fewer reviews being written than there are businesses being searched for and viewed. And so later horizontally a lot while keeping fewer
justification for having them separate. And then I am going to just write directly to this database, the review table. So review post comes in, we'll write to the database. Maybe I'll call this primary DB now because it's not
just reviews. You might be asking why do I have this as one database and like shouldn't there be two and microservices writing to the same one and no that's a that's a bunch of hoopla. um does it make sense in certain cases
for isolation and for fall tolerance and uh yeah of course but in this case there's a direct relationship between my businesses and my reviews and there's only 10 million businesses there's not a lot of data so why would I split these
into two different databases to do a cross database join when I can just let probably made a decision here that this is just a SQL database I'm going to go with Postgress so I'll label it um okay let's let it know and in order to create
review users are going to issue the post request from the client which is going to let us know the business ID that they want to review as well as within the post body of course the rating and the optional text. The API gateway will
route this post request to our review service which will then create a new row in our primary database for this given review. Easy enough, right? Let's get that feedback. I have a
feeling this one's going to be fine. But then we got our our deep dives which then we got our our deep dives which should be a lot more fun. I'm on the right track. A crap. I blew it. Huh? What did I do?
myself because earlier I talked about how I needed authentication, right?
that the rating falls within the accepted range. It's honestly valid. I accepted range. It's honestly valid. I was going a little too easy, huh? record it again because I don't want to sit here and make you guys listen to me
feedback. I explicitly talked about authentication in my APIs. So, for me to not bring it up here is isn't great. I would need to say that my API gateway is authentication in order to ensure that I can actually post a review. Um, and then
this this validation seems reasonable. Yeah, I mean, we definitely would want sure that our ratings are between one and five, that it's mandatory. Um, fair enough. Okay, I'm no longer perfect. What are you going to do? Huh? Uh, all
right. Our first deep dive question. So, how would we efficiently calculate and update the average rating for businesses to ensure that it's readily available in search results and still accurate up to the minute? Okay, so we added this
average rating here, right? But how do we keep this up to date is the main question. Um, well, let's talk about this. There's there's a couple ways that answer this different ways in interviews, too. And a real common way
that candidates like to do this is they like to throw down like a message cue. here and say all reviews come into a message queue and then we have some worker that aggregates periodically or some consumer that aggregates and
updates the the average rating periodically. Um personally I think that's overkill and we can do some math maybe to prove that. Like if I have 10 million businesses and each business realistically has like honestly there's
probably not no more than uh five a day. So that's 5 million uh 5 million reviews So that's 5 million uh 5 million reviews and then we divide that by 100,000 seconds in a day. We're talking about a few a second. So I'm not too worried
about this. So there's no reason that I'd be worried about buffering it in a candidates do a lot is that like they'll have a cron job here and they'll just run a cron job daily to update these reviews. Actually, I think that this is
pretty realistic. Um there's like realistic trade-offs here in that do I realistic trade-offs here in that do I really want my um average rating user refreshes their page and the average rating changed? Is that a good
experience or do we want to just update it once every night? Well, the question told us up to the minute. So, I think this isn't going to fly, at least for our use case. Um, so what I would do, and I'm not going to overthink this, is
that I'm just going to say numbum ratings, right? And then every time a new review comes in, when I write that review, I'm going to do this in a transaction. So, my transaction is going to ensure that
this all happens in SQL at once. Um, and if anything fails, the whole thing rolls back. But I'm basically going to add the review to the table. Add the review to the review table. And then I'm going to update the num ratings. And then I'm
rating. And that's just some math, right? I'm just going to take the current average plus whatever my new one is divided by whatever my num ratings is plus one, right? For the new one. So it's going to basically look something
like that. Um, cool. I've explained that to you guys. Now, let me explain it to it really quickly. Um, so I I think that we don't need to Um, so I I think that we don't need to do overkill here in that anything like a
unnecessary because we only have a couple reviews that come in a second and handle that many writes and I can just do this directly on the fly via a in my review service is going to
construct a transaction to my database which is going to do three things. It's going to add that new review row, but then it's also going to take the current average ratings, add whatever our new average is to it or take our current
average ratings, multiply it by the total number of ratings that we have, um add our new our new rating that just came in and then divide it by the new number of total ratings. So basically the old num ratings plus one. And then
that new numbum ratings again all via transaction. This will make sure that anytime somebody posts a new review, um, the review gets added, so too does the the review gets added, so too does the average rating.
than I would like to be, but what are you going to do? This is all one take. Nothing's practiced here. The system liked it. Great. We crushed it. You got transaction. I could be more explicit about how database transactions assure
atomicity when updating both the review table. My interviewer understands that. would handle potential race conditions if multiple users submit reviews the same business. That's why I have a transaction. Um it
can handle multiple transactions. That shouldn't be an issue. So fair enough. cares about, but uh I feel good about it. Next up, we got a follow-up question here. How would my sister handle this?
Will multiple users submit reviews for the same business simultaneously? What mechanism would you put in place to ensure data consistency? Honestly, it's a good followup question. I was kind of just dismissive of this a
moment ago, but yeah, we definitely need some form of locking here. Um, so let me talk to you guys before I talk to it. Basically, the the issue here is that I can have multiple transactions going on at the same time. Um, but transaction B
that comes just after transaction A might still be reading the old average rating and the old num ratings because the transaction A hasn't committed those new values yet. And so transaction B would overwrite the values of transition
A potentially. Um, and that's not good. And so what we can do here is that we can either use row locking. We can lock the business while we do this update. Uh, or we can use optimistic concurrency control. Optimistic concurrency control
transaction take note of the current average rating and num ratings and at the end of the transaction make sure that it hasn't changed. If it changed somebody kind of got in the way um and we should roll back and try it again. So
either of those will work let me save that to it. Uh so there's two things that we can do within our transaction. We can use row locking or pessimistic locking where we lock the business row. Importantly, we're just locking one row
in this case um at the start of the transaction and then release that lock at the end of the transaction. This way we ensure that there's no conflicts when users are updating reviews at the same time. Alternatively, especially since we
here, we could use optimistic concurrency control where our version quote unquote would be the average rating and the numbum ratings. And so of the transaction and then at the end of the transaction we'll confirm that
confirm that nobody uh conflicted with us and we can commit the transaction. Otherwise, we would roll back and retry.
it thinks. I don't want to get another another not perfect here. I don't think another not perfect here. I don't think my ego can take it. Woohoo. Okay, it likes it. We showed awareness of system characteristics by noting optimistic
concurrency control. pessimistic was good. All right, it likes it. Uh what do we got next? How would you modify your system to ensure that a user can only
leave one review per business? Right, that was one of our constraints. Uh a user can only review one review per business. So the easy answer here would application logic. Maybe actually the
worst thing we could do is put some code on our client where our client doesn't allow you to submit a review unless you um haven't posted one to that business should have that logic on our client,
somebody can still just issue an API request, right, and bypass your client. So, that's not good. So, let's work our way right. We could put it in the review service, put some logic here to basically say, first check if the user
has already created a review for this business. If they did, throw an error. business. If they did, throw an error. If not, um, then then proceed. Um, and that's okay. But let's provide a hypothetical like that's one line of
happens if the company continues to grow? There's now a new service that for some reason writes reviews. They don't know that you had that line of code and maybe like you just acquired another company. um and that company is
onto your system. I don't know where or some data engineer is running a back fill. Any of these things, they don't know you had a line of code there. And so it's not actually working right. And so the key to to questions like this is
that you want to work your way right and put that constraint as close to the database or the persistence layer as possible. And so what we can do is that we can actually just put that constraint directly on the database. And so in this
case, we can have a unique key on the user ID and the business ID. And this a review with a user ID or business ID that already exists, it'll get an error. Uh let me let it know that. So what we can do here is we can just
create uh or we can ensure within our database that user ID and business IDs constraint on the user ID and the business ID columns respectively. And to write a review for the same user ID or the same business ID will fail at the
database level. We can propagate that error back to them. Now, realistically, logic both in the review service and the client so that users never get to this point. Ideally, in the client, you can't even submit a review. But of course,
people can bypass the client. Um, and so we want to put this constraint on our persistence layer as close to the data as possible.
Okay, let's see how that went. Can we finish strong here? I hope so. No. Good. Come on now. I correctly Good. Come on now. I correctly identified database constraint.
It says, I well, maybe I wasn't very clear to be honest, but I thought it was implied. I don't want a separate unique constraint on user ID and business ID. Obviously, I want a composite unique constraint on the two of them. I thought
I said that, but maybe I wasn't very clear. duplicate reviews. Yeah, give them an error or something. I'll be clear there. All right, fair enough. They got me. Um,
I thought I said it. What are you going to do? All right, we got another deep dive here. How can you improve search to handle complex queries? So this is back to where we were earlier with this, right? And so what we can do is we can
do a couple things. A common thing that people will see is that elastic search here. Elastic search is a search optimized database. And so it it's a definition of document store. And what
it does is it allows us to build things like inverted indexes on the name. In category. And then also geospatial indexes on things like latitude and longitude. This is going to make things really quick. So we could do this um and
we can have our searches instead go to elastic search. But then we introduce a things need to remain consistent with one another. And so you would introduce oftentimes what's called CDC. Um, I shouldn't say it's always called CDC,
but oftent times this is what you would do to keep these two things consistent. And that's that, uh, this introduces a change stream off of your database. It puts it into some message queue, you'd have some lightweight consumer, and then
it makes the updates to elastic search. And so a business updates, a new review is added, the the change stream, you know, will automatically kick that event into a CFKA or something, then the worker can process it and update elastic
search. So this is a a common strategy. It would absolutely work. I'm not going overkill. I don't want my engineering team to have to learn a new technology team to have to learn a new technology and all of these things. Um instead,
uh Postgress has some extensions. It actually supports full text search out of the box. So that's good. And then it has PostJS, the Postgis extension, which allows us to do geospatial indexing. And so I'm
going to enable this. Um, and then all of our queries are going to look a They're going to use the full text query semantics on name and they're going to use the geospatial queries for latitude and longitude, but it's going to ensure
that we have the proper indexes on these columns that are more or less efficient we get that less than 500 millisecond latency. So let me say that. Um, now one
introduce elastic search or search optimized database, but this would mean primary database in elastic search consistent, which would be fine. We could do this via CDC. Um, but I don't want my engineering team to have to
learn a new technology. And so instead, Postgress has the PostGIS extension, which will allow us to build geospatial indexes on the latitude and longitude of ahead and I'm going to do that. I'm going to enable that extension. And then
Postgress also supports full text search. And so now my searches will use the inverted indexes supported by or the jin indexes supported by full text search as well as the inverted indexes by postJS. And this should make my query
super efficient in less than 500 milliseconds without needing the consistency issues of introducing another data store. another data store. Let's see.
What do you guys think? You think I got it? This uh what are we at here? 36 minutes, one take. Great answer. Come on now. now. Come on now.
Could elaborate on my specific indexing strategy. Full text search. I could definitely if my interviewer asked about it, I'd be happy to go into more detail. It doesn't want to give me a follow-up question for that though.
question for that though. Multiple criteria. follow up, I would definitely talk about it. Um,
okay. Yeah, let's complete the practice. All right, let's take a look at my results here. So, I got a 95 out of 100 on my overall score. Not perfect. We saw those two mistakes I made. What are you going to do? Uh, pure average. So, other
people that chose senior who did Yelp, um, uh, they got a 90% on average. Pretty great. Good job to everybody. And let's see, you can see my deep dive and lacking a little bit here, but I nailed requirements, core entities, and API.
got my whiteboard down here. I'll uh I'll link this whiteboard in the description so you guys have it afterwards. Um, sweet. Okay, I guess of course you can try these yourself. So, if you come over to hello.com/practice,
you can see we have plenty of these easies, mediums, hards, there's a long common system design interview questions. People give really fantastic enjoy doing them. I think they're a lot of fun and a fun way to study system
design for your interviews. So, check it out. Give it a go. Let me know what you think. I hope this video was useful. I know this was a different style um but I me to record anyway. I did it in one take and um I had a fun time doing it.
So, questions, thoughts, put them in the comments. Um, I'll talk to you guys soon and of course, good luck with your upcoming interviews.