‫What's going on, guys?
‫My name is Hosain, and I wanted to do a quick video to discuss this topic, which is very interesting
‫to me and I think.
‫Anyone who works with databases should really understand the underlining data structure of how databases
‫store stuff, because it will really become really handy when you scale, it will really become handy
‫when you design your backend application, how your queries actually access the path of your path,
‫and then you're pulling the data how how the plans are actually makes decision.
‫And all of this really comes down to indexes.
‫And this is really I want to discuss how specifically just postscripts and how.
‫My sequel specifically, A.B., as a database engine in my school stores, their indexes, before we
‫jump into that, we need to understand the basic architecture of tables.
‫So if I create a beautiful table on my database, what does that mean?
‫A table has well, it has columns.
‫And then and each column will have a specific data type, which an integer string and no dates.
‫Jason, I heard this is a good data type these days, and you guys convince me that's actually a good
‫idea, too, to store Jason as a as a just a table will have multiple columns and each column will have
‫a specific size and that decide the row size and that Rossides will become either dynamic or static
‫based on how you define it.
‫Right.
‫Morcha vs. just normal text and so on.
‫And then the table is is huge.
‫Data structure is big and usually stored in disk.
‫And unless you can put it in memory, which is awesome for fast access queries, but usually it's in
‫disk because it's a blob huge of just blob.
‫Right.
‫So now it comes to indexes index and I mean a variable index.
‫Check out this video right here to learn more about them.
‫But an index is is basically a date and other data structure different than the table that tells us
‫where exactly the rules I'm searching for are.
‫Right.
‫And then the easiest way I understand the indexes is like, you know, these alphabetical dividers,
‫binder dividers, so that A, B, C, D until Z, right.
‫And then you're searching for specific like company-owned phone number.
‫So if you're searching for a company Xebra, I don't know those companies or you will jump to the Z
‫divider and then start searching for that.
‫So you have a smaller subset.
‫So what the index does in this case is actually stores a pointer to the first row in where the XYZ starts,
‫essentially.
‫So that's essentially how the index is always like a pointer.
‫So the row, the table, the big stuff.
‫And there is the other data structure that you can have a quick and access index and it points to that.
‫Not every database does that.
‫So.
‫How about we start with PostgreSQL, every index you create in Postgres.
‫Does exactly that.
‫So if you create an index on column A is going to create a data structure for you, that beautiful data
‫structure and each leaf, each entry will be pointing directly to that table directly to to a to a unique
‫identifier and the and the in the table.
‫That's not the primary key necessarily.
‫That's just a role that is most of the time it's invisible.
‫And Postgres, I think it's called that Topal idea.
‫So that's that's the data structure.
‫So if you're at an index point to the role, you might say that was the problem with that?
‫Not really, no.
‫No problem.
‫But pay attention to that and another index, because what what is does do it also still points to the
‫table.
‫Directly.
‫You see a pattern here, right?
‫The more indexes you create, all of these are actually put into the table, which is pretty neat.
‫So and that's what we that makes sense, right?
‫Right.
‫I'm searching something in this index.
‫I find it.
‫I jump to the table, I pull.
‫If I need to jump to the table, that's what's called index only scan versus an index scan, right.
‫And if I only need to jump the table, I know where to find my data.
‫So index is imposter's all point to the table directly.
‫My second and specifically A.B., in my second, because my uncle has other database engines that he
‫can swizzle in can change, which is something I love in my cycle.
‫And Maria, DBI, something you don't see in other databases.
‫We're going to swizzle the Davis engine of a table to be something completely different.
‫My Assim is a is a different database engine that it works.
‫Exactly almost like like Postgres.
‫It points directly to the table.
‫Each index we created posted a table and E.V., which is the default MySQL engine doesn't do that.
‫There is always a primary key in the table.
‫And when you heard that primarily if you don't A.B. or may seek to create one for you and then that
‫primary key index points to that table.
‫Right.
‫But any secondary index you create or any other index you create does not point to the table directly,
‫it points to the primary key value which corresponds to the table.
‫Multiple Hoppes.
‫There is a design, careful design choice for both, and let's let's explore both of them so we know.
‫Progress, every index points to the table directly.
‫Michael.
‫Every index points to the primary key and the primary key is the only index that points the table.
‫So if I did an update and both databases.
‫And I.
‫Updated Ororo.
‫Right, or deleted or.
‫In postcrisis, I deleted her.
‫All right.
‫And that road doesn't exist anymore.
‫I have to tell index number one, index number two, index number three in December form, December
‫six, that that road doesn't exist because guess what?
‫All of them actually are aware of our role and we are aware of the stable.
‫So there are dependencies.
‫So false because when you did this, it actually goes on update all indexes.
‫The mean it's slower or bad is just that's what it does, right?
‫And my Michael, when you update.
‫I wrote a literal.
‫Just update the primary.
‫Right, because that's that's the only thing we need to do, because these guys will still point to
‫the to the to the to the primary in.
‫And that said, they they don't have they are not aware that our role has been deleted.
‫Right.
‫So that's as long as you don't touch a specific value, like for for example, if you if you updated
‫another column in our role that.
‫The index, which you know, on which this is a created need to be aware, right, and that is in that
‫case, you will need to update that index anyway, but only the index that you actually touched for
‫the columns you touched.
‫So that's that's how things work in general.
‫So.
‫Hey, guys, saying from editing and I forgot to mention one thing in my school, remember, every index
‫points to the primary key and primary points of the table.
‫So if you did a lot of updates on the primary key, guess what will happen?
‫Every single index will need to be updated.
‫So.
‫Really careful while updating primary keys in my school, so that's another kind of fun thing to be
‫aware of.
‫I'm not going to say limitation.
‫But just something to be aware of.
‫Back to video.
‫And updates my sexual versus progress, you can see that it's a little bit less scattered.
‫Reads, however, if I read.
‫In in in in Postgres, using an index.
‫I'm going to find the value I want, and if I ask for a road that doesn't exist in an index, it's not
‫including the next I have to jump to the table and I can jump very quickly because I immediately look
‫at the other table.
‫I have the road that points to the table directly and I read it.
‫So Rieser and Postgres are extremely fast.
‫Does not mean that.
‫Reads on my sequel's laws, just just understand how they all things work, right?
‫So now if I'm reading a particular table in my school and I'm using a specific index and I found what
‫I am looking for, I cannot jump.
‫And I need another value to select from the table.
‫I cannot jump to the table directly.
‫I have to jump to the primary index and then from the primary key index.
‫I get the roar and then I jump to that there is an extra hop, so raids are a little bit tiny, bit
‫slower.
‫Sometimes it's unnoticeable, but just understand, if you're doing this a lot, I understand what's
‫going on.
‫So if you're but but the beauty of this, if you're selecting the primary here a lot in my school,
‫that's really beneficial because the work is almost always included with every index.
‫So.
‫That choice of what becomes the primary key and A.B. becomes really critical and it can boost your performance
‫in a in a in an amazing manner while impulse reads our fast right.
‫Because and any Ahepe value pulls like pulling from the heap or pulling from the table also pretty,
‫extremely good because you're pulling to the table directly.
‫I'm obviously doing it over, overdoing it, overdoing everything is just basically slower.
‫But that's just very something that is very interesting to understand when you when you deal with these
‫things.
‫The suppose was updating a lot of fros or deleting a lot of rows might trigger a lot of update to a
‫lot of indexes.
‫So just pay attention to that when you do that.
‫And that's it, that's what I wanted to talk about.
‫Obviously, I want to make this video short.
‫What do you guys think?
‫There is always a choice between Posterous and Moissac on any level.
‫The database doesn't mean postcrisis bad.
‫Doesn't mean my cycle is as good, doesn't mean anything.
‫It really depends on what you're trying to do.
‫But I believe one of their main concerns of moving from Posterous to to miscall is exactly that.
‫They had tons of indexes.
‫I was surprised, like, why do you have this much indexes, like too much indexes, too many indexes,
‫and as a result, touching Rose if they touch a lot of rose Abdeh.
‫A lot of rose.
‫It just just thrashed all the indexes because you stopped it all lenders, you need to tell everybody
‫that points to the table know that something has changed, right?
‫Especially if you update it.
‫If you insert the update, these these things are all scatting everywhere and postcrisis.
‫Like it's a little bit different when it comes to updating versus deleting the they keep the roads alive
‫for NBCC reasons.
‫All right, guys.
‫Kind of gave them a very video shot I'm going to see on the next one.
‫What do you think about these two beautiful databases?
‫What do you prefer?
‫I mean, in that comment section below, I'm going to see.
‫And that's what I say.
‫Good bye.