Amigoscode · 13:17 · https://www.youtube.com/watch?v=pEzrn6PIxRo
0:00 Hey, what's going on guys? In this video, I want to talk to you about 13 ideas behind every single database that you as an engineer should know. And remember, the goal is to become an engineer. Especially in the era of AI, we've seen that we just give stuff to Clude or any of these other models and we let them pretty much do everything. And it shouldn't really be like that, okay? Because these concepts, they do come in interviews.
0:25 And this is the uh differential factor between someone that gets hired and someone who doesn't. Okay. So to begin with I want to talk to you about tables and relationships. So let's start small. And you'll be surprised of how many people still struggle with this. So the relationship we're mainly talking about relational databases because that's the most used database type. You have tables and within each table you have rows. So these are records and then columns are the fields.
0:55 So here users and then you have a couple of columns ID, name, email and then orders ID and then here you have what is known as the foreign key. So this is what connects this table in here to this one. Okay. Now very important. Okay. So with relationships you have uh something known as uh onetoone relationship. This is one to one mapping. You also have uh one to many and then many to many. Okay. So uh people usually get confused with many to many and we have uh you know content around this within the academy to make sure that you fully understand this.
1:31 The other thing is so once you start talking um about relationships you're going to have to understand joints. Now joins really make sense once you normalize data. Okay. So database normalization very important concept that you should also know but essentially so what a join allows you to do is to combine two or more tables into a result in here. Okay. So we have inner joins in here which um uh only grabs the matching records. So in this case we have users and then we also have orders.
2:09 So the user ID for Alice in here. So appears in here and here and then we have Bob. So Bob also which means that we're going to bundle everything from here. Okay. So from these two where there's a match uh also we have uh the left so left join. So this basically gets the results from the left table and then the ones where where there isn't a match then it comes back with sorry someone just joined the academy and I got scared.
2:41 So um yeah so basically uh when it's null in here it means that there is no match and then you can use the wear clause to filter. So maybe you want the left or you want uh sorry the right side instead. So very important. Now normalization. Okay. So this allows you to store each fact once instead of repeating information, right? So in this case, for example, if you have orders and then you have uh order one and then email, you can see that it appears a couple times in here.
3:09 So what you do is you extract this into two tables. So one which is the customer, okay, and then you can basically have the email which is now um in one place. And then you have orders and then here you have the ID. Okay. And what this means is that now you can combine these two by using uh foreign keys. So database normalization very important. First normal form, second normal form, third normal form, uh fourth nor form as well. Very important.
3:40 And you'll be surprised that a lot of people don't talk about these things anymore just because of AI. Cool. The other thing is indexes. So indexes allows us to speed up the uh queries um in our database. So if you imagine that you're looking for this element within your uh table and there is no index then it means that you're going to have to read every single row. So if you have a million records, this going to be O of N.
4:07 Uh and the best case scenario is O of one where the element is right at the start but uh that's the you know best case scenario but that will never happen right so usually in databases you have millions of records. So how do you improve the performance right and that is by using indexes.
4:29 So indexes these are uh separate data structures right and sometimes they come up in interviews what is the benefit of using indexes versus non-indexes the different types of indexes available to us so on and so forth right but when you use an index you go from uh of n to o log n and um yeah so this is how um queries they they they are fast u this actually should be used wherever you have for example the where you know and query is where you you you know the the very frequent queries uh where you have the where and then we have the column and then whatever value that is.
5:06 Um so again so this is a complete separate topic on its own but I just thought maybe let me just uh uh touch on this as well. Then we have um transactions. So this is very important. So you group several rights so that they succeed together or not at all. Right? So again here so whenever you begin a transaction you can imagine that you are you know de debiting one account and then crediting the other account.
5:35 So if this transaction fails, you don't want to credit this uh account and vice versa. Right? So if you don't have transactions, maybe you debit a customer 100 and then this in here fails. So customer B or account B never receives the money and equally account A doesn't have any money at all. Right? So very very important. So um yeah so basically you want to always use transactions and uh it's all or nothing and then um you know you commit so uh this has to do with acid in here which is very important and these are characteristics of transactions again so this um comes from what we just spoke so acid atomicity all rights happen or none do consistency so rules and constraints always hold then we have isolation so concurrent transactions don't corrupt each other and And then we have durability.
6:29 So once uh committed it survives a crash. Cool. The other thing that comes a lot in interviews is locking, right? So we have pessimistic locking versus optimistic locking, right? So you can imagine where you have two transactions and um the first transaction. So for pessimistic, so locks the row first. So it locks this row and then B tries to access this same row. Imagine a seat. then it has to wait. Okay. And then we have optimistic log.
7:02 So this uh usually uh is done with um versioning and essentially um a acquires a log. It commits and the version goes from v1 to v2 and then b also the same. So the version is stale rejected and then tries. Right? So um basically if the versions don't match then um it gets rejected. So again very important pessimistic versus optimistic look comes a lot in interviews. All right now moving on to this one which is quite interesting n plus one problem.
7:34 So to visualize this, so imagine that you select 10 rows from your database and then for each uh row that you have. So for each user, you didn't select orders for example. So each user has orders. Now for one you for 10 users, right? So you're going to have to um select extra data, right? So you select and then within a loop, you go through each loop and then you basically get more data from it. Now this here is very dangerous and um there's ways that you can solve this and again it comes a lot in interviews.
8:11 So what you could do is you could just basically use joins in here and if we're talking more about spring security we could use join fetch and other other techniques and this is the best way of tackling this because it's just a one round trip and it's constant. Next we have connection pooling. So the application itself right it never connects directly to your database but instead through connection pooling in here. So uh each thread in here so when there's a a request basically it borrows
8:43 it borrows a connection and then it utilizes and then it puts it back to the pool so that the next available thread is able to utilize it. Okay. So uh open a connection um is expensive as we all know and a pool keeps a fixed set of open um and hands them out. So requests borrow and return instead of paying the uh setup cost every single time. Very very important in here. Right. So um uh and also actually you cap the total connections that you have to the database.
9:20 Uh so very important um stuff that you should definitely be aware of and uh again for us because we use sometimes spring boot and all of these frameworks we don't even get to see this. Okay, but as soon as you go to production these are things that you need to be aware of. Uh then we have caching in here. So uh keeps hot results in fast memory and uh you know quite quite popular these days when it comes to system design for you to introduce caching.
9:48 So uh whenever you make a request if there's a hit then it returns the result immediately right so very fast because this sits uh in memory and not um on on the disk otherwise it goes and it fetches from the database and then it stores into the cache and then it returns back the result. Again very very important concept. The other thing that we also need to be aware are migrations. So usually um you know when working within a team of engineers the database evolves people are adding tables removing etc doing a bunch of things to your schema.
10:26 So um basically how do you manage schema changes over time well is through um you know version scripts essentially and they're kept in git like your normal source code. So initially you start with with version one users for example that only has ID and name right then MI evolve to email then maybe you have another table which is orders in here and um you can see that now the schema went from v1 to v3 and uh we're able to track the evolution of our database and um yeah much easier to work with instead of having one giant file and um yeah that that doesn't work to be honest.
11:10 Cool. Um the other thing that we need to be aware or two more things. So one is replication in here. So uh this keeps live copy of your database, right? So rightes go to the primary here and then the uh primary replicates data across and then these right here. So these replicas they allow other applications to read from it. Okay. So what this does it removes uh pressure on the primary in here where reads go to replicas. Okay.
11:45 So this is more on system design and I'll be covering uh this stuff as well. Then we have sharding in here. So sharding is a concept of so uh basically if the data doesn't uh fit in one database then you basically shard right? So you split the data across multiple databases and then um you know the challenge is when you want to select the data right. So which shard do you go to? So um then this is this is often used by um a sharding key right or the router using a a a hashing uh technique.
12:17 And bear in mind that if you introduce sharding, right? So it doesn't mean that you introduce sharding that you're going to you're not going to have to deal with something else which is not the case. Okay? So if you introduce something, just bear in mind that you're going to have to deal with other things and um yeah, it's always a trade-off and this is what uh comes up a lot in system design interviews. All right, so I think this is pretty much it.
12:44 If you have any questions, feel free to let me know. And uh yeah, if you want to learn more about these things, um we have a private community where we teach you how to become an engineer. So if you're looking for jobs or if you are a junior developer, uh trying to become a mid-level or mid-level to senior or if you are a senior trying to you know get more money or apply for other companies. Uh these are some of the things that we teach within the academy. Cool.
13:11 This is pretty much it and I'll see you on the next one.