‫What is going on, guys?
‫My name is Hussein, and in this video, I want to talk about how database's store information in the
‫disk, and that was a question asked by one of you guys.
‫And it was a very good question because usually we take things for granted.
‫But when you really understand is you feel pity for the computer that does the job for you because.
‫Oh, my God.
‫Right.
‫You appreciate it.
‫Right?
‫When you start washing the dishes, you know how hard that job is, right?
‫Thinking dishes and especially, if you like, cooked an egg and you forgot the plate for a day and
‫then you come the next day and and those that yellow yolk thingy, I don't know what they call them.
‫English, right.
‫The yellow thing, they get stuck in the plate and you need to work really hard then.
‫And you understand how dishwashing is really hard.
‫Same thing with a computer.
‫A computer is not dishwashing per say, but kind of similar.
‫I'm rambling.
‫I know you guys.
‫The idea here is to understand how databases really store information on disk.
‫All right.
‫So it is extremely complicated thing.
‫And let's talk about a little bit this.
‫I'm not going to be adding graphics or anything.
‫Just imagine what you mean, guys.
‫See, follow my hands, because that's what I'm going to use here.
‫I'm going to make another video detailing went with virtual board and all that jazz.
‫But here's the thing, guys.
‫Imagine you have a beautiful table.
‫Has to be beautiful.
‫If it's not a beautiful, then you have a problem.
‫But if it's a table and let's say for simplicity, there is only one field and that field is an integer.
‫Thirty two bit, let's say, right there, that's what I'd bet a bet, which is for whites, not not
‫eight.
‫OK, so.
‫You have an integer right field, so and that's four bytes, so if I insert one rule, any number.
‫Between one and two billion, I guess that's the maximum 32 bit injury you can have, right?
‫It's going to take four bytes of space and we're going to talk about what space really means.
‫So if I insert over here four bytes, it's another four bytes, another four bytes, another four.
‫But how does this really is stored in disk?
‫Right.
‫And I'm going to explain this in two fashion.
‫How is stored in SSD and how is stored in HDD hard disk drive, which is these rotating thingee?
‫All right.
‫How do you like this sound effect guys on.
‫So, yeah, so we have a spinning disk and this is the and there is like a little bit different how
‫they stored information.
‫So let's start with the hard disk drive, which is a spinning disk, the hard disk drive that it is
‫obviously a platter, which is a circle.
‫Right.
‫And there is sectors, pork and each sector.
‫Present information, and each sector has a little bit of luck in it, and these are tagged and identified
‫uniquely for every single sector that is in the hard drive.
‫So you would say, OK, Blätter, number one, track number seven, sector number five and block number
‫two.
‫And that gives you exactly a block, which is a block of disk that corresponds to a number of bytes,
‫let's say five hundred and twelve bytes.
‫OK, so.
‫Go back to our logical representation off the table, if I insert four by this integer, like number
‫seven, which is four by four, right.
‫That's the beautiful thing here.
‫If you're number seven, you're going to reserve four bytes.
‫If you're insert number two million, you're going to reserve for it doesn't matter, right.
‫Because it's old zero zero one zero zero zero zero zero.
‫It's all four bytes.
‫And that four bytes will be stored in that given block.
‫OK, and.
‫Codes, that block is 512 and you start for four bytes, that kind of waste, right, you cannot jump
‫to another block and start another four bytes.
‫Right.
‫You have to insert another row.
‫And here's what the data is smart.
‫The database says, wait a second, this table is reserving this block so far and it's going to put
‫the next four by the next integer next to that four byte.
‫So it's going to be adjacent to it so they can fit in the same block and you keep inserting something
‫in something until that 512 is completely filled.
‫Right, and you might say, wait a second, how do I know if I want to pull row number one to go to
‫that block?
‫That's how the database stores stuff that their base will say.
‫OK, Roe, no.
‫There is a unique identifier for the road and it could be that primary key if that field happened to
‫be primary.
‫So that particular number points.
‫There is metadata in the database that says, hey, this I want, for example, row number one, wrong
‫number one, have some information next to it.
‫Sector seven, block eight, track three.
‫And that information is what the database need to actually go to the disk.
‫That's circle ugly mechanical drive and start spinning and reads.
‫What is it?
‫Read doesn't read four bytes, it doesn't read eight boards and reads the whole God damn block, which
‫is 512 byte of memory.
‫And you put it in memory.
‫And then when we got that, that.
‫Is slow because that's NATO, but the moment I have all this blook 512, I got a wrong number one.
‫And I got no number two as well, by for free, I got to number three and four and five and six and
‫seven until the last straw that is in the book until rule number 128, to be specific.
‫Right.
‫What if I want because 128 is the last block that he can fit.
‫Five hundred and twelve.
‫That's right.
‫So now what if I want to roll 129, for example, which happened to be the value?
‫One of one and a half million light.
‫If you want that or it's not on the block of the block, it's on the block, that's another IIO and
‫they the database knows this information is like, oh, another.
‫I'll go to that location.
‫That one twenty eight to 129 will say all black.
‫Number seven now block number eight, it's the next to it.
‫And then you pull that information back and you might say, are these two blocks nearer to each other.
‫Could be cabinet based on how fragmented your data is, but usually they are next to each other.
‫So that's how mechanical drive works in a nutshell.
‫So how this is how they are stored on desk comes to beautiful.
‫This is this is this these don't have rotating stuff.
‫They don't have sectors.
‫There is no tracks.
‫There is no all the garbage.
‫There is.
‫However, the idea of blocks still and block have a size.
‫All right.
‫512, 1024 whatever.
‫Right.
‫And this blocks of memory have pages.
‫And each page has a size, but it's the same thing, right, and instead the database.
‫Go back to the our representation, logical representation instead of the database storing that track
‫and sector where the actual stuff is for.
‫This is the it just stored the block number and the page number.
‫So.
‫All right.
‫Number one is block number 17 and page number eight.
‫Right.
‫And just pull that information when it pulled that page, let's say 512 bytes and that page, we have
‫that beautiful page we got wrong number one and two and three and four and five and six right up until
‫all 128, same thing.
‫And we didn't talk about rights per say, but I'm going to mention it in that way after a while.
‫But yeah.
‫So that's the same thing.
‫You go back and you read it exactly the same.
‫However, it's 100, 200 to 400 times faster because it's a random access.
‫Right.
‫And instead of spinning wheel, it's it's literally you tell that I controller of us is the go to that
‫particular block, that particular page and pull it.
‫And when you pull a block, you usually pull all the pages with it.
‫That so that's what happens in an SSD.
‫When you pull a block, you pull all the pages with it as well.
‫Let's go through.
‫All right.
‫I want to write to.
‫Paige, we never write to pages, right, as users, we write to Rose is like, oh, I'm going to update
‫this rule number nine to be four instead of one point five million to be two million.
‫So what what do we do here?
‫What does that mean?
‫That translates first of all, that's rule number nine.
‫So pull that role.
‫What does it mean?
‫Where is wrong?
‫Number nine or number nine in the database there is metadata says order number is block number 302 and
‫then pull that.
‫That's the first thing.
‫And then I'm going to update it.
‫And it's one point five million.
‫Update that for White to say two million updated in memory.
‫Now I'm going to write it back.
‫And here's how SSD works.
‫I'm going to talk about how this is the works and I'm going to talk about how the sector works, the
‫mechanical drive.
‫So for the SSD, when you want to write that block, first of all, you write only the pages in the
‫block, right.
‫So that change that one point five million existing in a page number one.
‫Right.
‫But if you write it, you change it to a two million.
‫You have to always create a new page.
‫That's how SSD works.
‫So you write a new page and then you flush back that block as a different page to describe.
‫And this information, reading and writing to the same location that block.
‫And as this idea is extremely Hertz, the SSD feeling.
‫Right.
‫This is these have shelf-life and this block, this block writing and reading.
‫The rewriting, to be specific, has some shelf life.
‫And this property and SSD is called endurance, literally.
‫How many times you can write to the same block before that block is dead and at that block is bed.
‫We cannot reuse it.
‫That means.
‫We just lost space in our society can take that with the hard disk drive, which is the physical thing,
‫you can literally go back and ride to the same location.
‫Right.
‫And you can write to it along time because it's just magnetic right idea.
‫So you can straight right to it without any problem, because when you write to it, you're going to
‫write an entire block back.
‫You cannot just write one bytes.
‫That's that's not how IO controls work.
‫So.
‫So when you do that, when you change the last place, you take that whole block and just flush it back
‫to disk and SSD, there is a limit to how much how much time you can ride to it.
‫But for the hard disk drive, there is no limit to how many times you can write.
‫You can write to the same block.
‫It's OK.
‫Sometimes sectors get corrupt, but that's rarely happens compared to is is the right.
‫So SSD faster reading and writing.
‫But have little short shelf life depends on their endurance, so this is the power, that hard disk
‫drive.
‫They are a little bit slower.
‫They are slow because they are literally abiding by the laws of physics.
‫Right.
‫That's not electro magnetic.
‫It's not magnetic and light and electricity.
‫Right.
‫Is just using literally physical stuff to sneak back to a location and then literally.
‫Right.
‫All these lights, one zero zero zero one zero zero writes all this information to disk.
‫Right.
‫That was a little bit of a brief introduction about how databases store information to disguise how
‫disk drives are different than SSD.
‫You really sometimes need to understand how these internal works.
‫There are papers and people taking CDs just discussing how can we squeeze as much performance from SSD
‫because those guys are the future.
‫Obviously, those those guys are not dead.
‫The mechanical drive we're still using.
‫But these are the performance stuff.
‫But we have noticed with use the data structures that we have built BTD, to be specific.
‫They kill those systems if they are not configured properly.
‫Because what do Beatriz's do better is when you keep inserting stuff.
‫Yeah.
‫And such thing as the love new inserts.
‫If you're inserting new stuff, that's the best thing you can do, because in search of new stuff,
‫you're always taking new blocks and you're writing to new blocks.
‫You're not overriding existing block.
‫So you're not reading something changing and manipulating and writing to the same block.
‫These don't like that because you can only change it so much when I say to them so much, I'm talking
‫about 500, 700, and that's how it increases the cost go up as well.
‫Ride the endurance of that as deep.
‫Right.
‫So Beatriz's when we designed this thing, we didn't we didn't.
‫We designed a way back in the 60s and 70s.
‫I wasn't born back then.
‫But those data structures didn't really think about insurgences.
‫Update is like, OK, we'll just insert stuff.
‫Right.
‫And here's where the problem of misery Belltrees and again, if it's configured incorrectly, if you
‫keep inserting in your tables.
‫You have a B3 three index, and that index has to be large first, not just one into primer keys could
‫be composed of strings and all that stuff.
‫Then as you insert the B tree, that three will try to grow.
‫Right.
‫And when it tries to win, it's about to get imbalanced.
‫The tree will shuffle itself and rebalance itself, and that's that where it kills.
‫This is the when you rebalance, what does it mean?
‫Yes, you are inserting into your logical table.
‫You're just appending data.
‫But the index is restructuring itself, right?
‫Was this.
‫Or is this right?
‫So, yeah, so the the index is updating itself, oh, this is no longer the root.
‫No, this is no, no.
‫Oh, the child or the child have too many nodes update itself.
‫So now it's this is the location it's born to do.
‫So we rebounds and seven rebalancing is nothing but updates when you update in place in SSD.
‫Oh my God.
‫That is the worst thing you can do.
‫Right.
‫And you do this multiple times and uses these gone.
‫And when I say elgan, yeah, that block is gone and that block is gone.
‫Your disk essentially gets smaller because that it can no longer use that block needs to use a new block
‫in memory and memory and desk and that can slow things down guys.
‫So that's the idea of SSD, hard disk drive and how database is storing information there.
‫People still researching this stuff.
‫So they came up with another data structure, Facebook primarily, I believe they didn't come up with
‫this data structure.
‫They said B threes are May.
‫Well, this is D, right?
‫So they came up with their own database engine.
‫They called it Dirac's DB, OK?
‫And they said we have a lower use case where we're writing stuff all the time.
‫And those three balancing batteries are killing our asses, these shelf-life and we're throwing this
‫is d like we don't like there's no tomorrow, so what do we do?
‫So they used an existing data structure called log structure merge three or l e emem right.
‫LSM trees are append only they are great for appends.
‫Right.
‫So they it's, it's, it works and as levels right.
‫I'm not going to go into details but that's essentially then it's also a few levels.
‫It works at a memory level and then level zero, level one, level three and level four.
‫And these levels are always inserts.
‫So the index, even if you build an L symmetry index base, then it always just inserts at the end.
‫And that's really nice for us.
‫Is this the love that obviously you have to have a larger as D2 if you inserting a lot of information.
‫But guys, that was a but a long video talking about how database is store information into to the desk.
‫Right.
‫I like to talk without graphics and stuff like that because I might use this as a podcast and check
‫my podcast.
‫Guys, if you're interested, if you want to listen to this while you're in a train or background or
‫you're working out, you can listen to this information without actually looking at and expecting something
‫graphical on the videos.
‫I do make other videos where I use a virtual board, but sometimes I make this more casual, chatty
‫kind of videos as well.
‫Alright, guys like this video is like it.
‫This like it.
‫If you don't like it, I'm going to see you on the next one.
‫You guys stay awesome.