‫What is going on, guys, my name is Hussein, and as some of you might know, I'm still in vacation
‫and I don't have my regular office and my camera, so I apologize for the audio quality.
‫But I thought I'll make a quick video for the Q&A lecture of the month of December.
‫I started doing this back in October.
‫And you guys seem to enjoy this kind of interaction.
‫Right where I pick a question from the introduction to database and joining Udemy course and elaborate
‫on that.
‫So as you know, I almost answer every single question that you guys ask in the anime section Q&A and
‫but usually, you know, when you when you type things and it's really hard to, you know, I am a horrible
‫person.
‫I like to talk more than I type and my, you know, my writing isn't that great to begin with, but
‫I thought, I'll just make a video to elaborate on one of these answers, and we'll pick one or one
‫or two questions, then elaborate on them.
‫I've always thought so.
‫I thought, I'll start with this question from Shivam.
‫Why will any update and post Chris updates all indexes corresponding to that table?
‫This sort of phrase the question of the brand and explain that one.
‫What exactly is that?
‫So this is referring to the must watch video.
‫A lecturer, actually, of how tables and indexes are a start to this, I really, really recommend
‫you watch this before really starting the course, because this is the fundamental things of the building
‫block that you need to understand and build upon yourself.
‫Once you understand these basics, it's like understanding math.
‫Once you adjust to how ad addition works, you know how multiplication will work.
‫And once you understand how multiplication works, division just comes and naturally, everything is
‫derived from the thing that comes before it.
‫Right?
‫You don't memorize right things usually like in math.
‫You never memorize an equation.
‫You know how it works and you derive it right?
‫You can solve anything by understanding the basic things.
‫That's why we need to do the same thing with any domain.
‫Really, engineering is one of them, right?
‫We tend to memorize.
‫We tend to say, Oh, it's the way it is or my school or or MongoDB is faster is just the way it is.
‫No, it's just at the end of the day.
‫It's just basic building things, building blocks at once.
‫You understand these basic things you can.
‫You don't need anyone.
‫You don't need me anymore.
‫You don't need anyone to tell you, Why is this better than this?
‫You make the decision because at the end of the day, you ask, Is it OK?
‫Why is this OK?
‫How does this work?
‫You know how it works?
‫Because at the end of the day, everything is all comes to databases.
‫And we try to be smart in databases and start, OK, what is happening really right?
‫When you're issuing a sequel state, once you understand what is exactly is happening?
‫You must go to another database and ask the same question, how is this database system doing compared
‫to PostgreSQL?
‫How was mostly called, though?
‫It wasn't because at the end of the day, they have to solve a basic problem, right?
‫How to optimize Io your writing, reading or writing to disk?
‫You write it to memory.
‫You have to flush the disk you you want to solve the durability problem.
‫At the end of the day, it's all, you know, first principles that everybody need to adhere to.
‫There is no exception.
‫And the problem is marketing gets into the way Oh, this is like in memory, whatever the right or this
‫is that faster or this is on the cloud something you know, it just jargon gets into the wing, gets
‫confusing.
‫So that other way, let's get in to the first question, which is why will any update impulse Chris
‫updates indexes correspond to the table?
‫So this is something I mentioned in the in this lecture, and we do need to understand the indexes are
‫there for a reason, right?
‫Indexes are there so that we avoid searching millions and millions and millions of things.
‫Whether these things or documents rows, blobs, who cares, right?
‫Indexes allow us to, you know, take shortcuts.
‫Think of an index like this.
‫You're going to a secretary office.
‫Oh, find me a company, zebra.
‫Right?
‫And there are a lot of, you know, entries and find me.
‫The phone number of this company is called Zipporah, right?
‫You can take the phone book and search from A to Z.
‫But an index says, OK, zebra cells with a Z.
‫Let's only search the ZS or the ZS if you're from the UK.
‫And that's basically what an index is.
‫It just narrows the search space.
‫So like, oh, while researching a b, c d and all that stuff, search only those things you need.
‫That was exactly what the index does.
‫It just smartly, based on the column that you indexed builds a data structure, right, which needs
‫to be stored, needs to be put in memory.
‫Need to be issued.
‫So you still need to manage this thing.
‫It's not free.
‫Nothing is free.
‫Right?
‫So you build that.
‫They just like you and you search it first and then that data sector, that index will tell you, Oh,
‫go to this page and pull this role.
‫It's it's there.
‫It's 100 percent there or some indexes are, you know, proper probabilistic.
‫If I tell you, I I, it's it's it's definitely not there, but it may be there.
‫And that's that's also a useful so-called blue filters that are very useful stuff.
‫So, yeah, so once you understand the indexes or data structures that need to be stored, that need
‫to be maintained because the moment you update your your source tables with the data, you need to keep
‫that index up to date.
‫Right?
‫It's like adding a new echo.
‫Right?
‫And you can't any phone number, you need to have the index right there with the letter have that point
‫to the, you know, the right entry.
‫It's the same thing.
‫Welcome to the question.
‫The question is when you impose Christmas is specifically in politics again, those don't get don't
‫get caught up with definitions or implementation details.
‫Just understand how how each database does stuff.
‫You know, it's just it's this the way they chose to do this, right?
‫All right.
‫Choose a specific design and they stuck with it.
‫It has pros and cons, right?
‫And I'm not going to tell you the pros and cons because you forgot to figure it out yourself with logic
‫and.
‫The index, as I said, the index is.
‫Need to point back to the table, right, which page and which role they need to read, right?
‫And when you do that, what do we point to exactly?
‫We need some sort of identification of the road, right?
‫This is what is called Tibble ID and Postgres at least every year.
‫And so that has a unique tuple ID.
‫Right?
‫And if you updated and specifically in Postgres, you get a new Tupelo ID.
‫Right?
‫But the road is the same logically.
‫So to you or the reader, it's the same road, but it's a different tuple does doesn't make sense.
‫So one logical row could have many, many tuple entries, and each tuple has a unique identifier.
‫That's unique identifier is what the index stores.
‫And this is what effectively does the reading.
‫All right.
‫So where are we?
‫We talked about.
‫Having index data structures that are kind of separated from this large pool of raw data where the table
‫exist, where the documents exist, and these are multiple indexes, you know, because you can create
‫an index on Field A that will create a unique index data structure that will have X amount of bytes
‫that would be stored in this that will need to be fetched and need to be put in memory that will need
‫to be queried.
‫Read.
‫And you can have many, many, many of these indexes, right based on your table.
‫Right.
‫So let's say you have three indexes each for each column.
‫Column A. Have an index.
‫Column B have an index.
‫Column C has an index which points back to the table.
‫Now this is a design choice that the post Christine made.
‫When you search an index, right, you found the value that you're looking for, the value is within
‫us right now.
‫What does the value contain in the index, right?
‫Because you're searching the keys of the index, the value because it's a key value almost right concept,
‫right?
‫The value of the element is basically the tuple idea that we talked about.
‫Right.
‫Means that the tuple I.D., the ephemeral thing that keeps changing is stored on all the indexes.
‫So now think about it, if I update my role, right?
‫It's a unique role and I made an update to a single field there and new to pull it will be created based
‫on Bosca's design.
‫Again, this is only Bosworth.
‫Some other databases do a completely different design.
‫So and this is what I wanted to keep.
‫An open mind is like, Oh, this is not the way or the right way or the wrong way.
‫This is just how it's done.
‫You can come up with something to challenge any of the stuff that is being done today and completely
‫revolutionize database engineering, right?
‫And not on a fluffy way.
‫Like we do it today.
‫Unfortunately, I don't think the fluff these days, nothing is actually legit that is being discussed.
‫So now when I update my roll, call them a rhyme, what do we need to do right?
‫First of all.
‫We changed that content of column, and we didn't touch B or C, right?
‫What happens in post poses create.
‫Exact copy of that tuple raid as a new tuple.
‫You have a new tuple ID, a brand new RAM, but it's effectively logically to you as a user.
‫It's the same row you just updated it, right?
‫So now there is a new tuple idea.
‫We updated Field A, which has an index DNC we don't touch.
‫But here's the problem.
‫If you actually look in the Index B and the index, see.
‫Right, and the index, all of them have entries which point to the old tuple.
‫Right.
‫Because each table again, each double has a unique identifier.
‫So all of these guys are pointing to the old one.
‫Right.
‫So regardless of this old indexes.
‫Need to know about this new to idea.
‫I feel like I talked about this, and I apologize if I did, but I guess it doesn't hurt and I repeat
‫it right.
‫So all these indexes need the knowledge of the news to pull it right, because if someone start searching,
‫let's say, Index C, which we didn't touch for a value that points to that role, they will find the
‫old to pull I.D. and they will get at the old value.
‫Yeah, see, it wasn't touched.
‫But what if they said they'd select a from their bill where sequel law the law was pointed to the old
‫double idea?
‫So now they will get the old value of it, not the one we updated.
‫So regardless, we need to update those indexes right to have the new tuple idea and.
‫And that's technically a problem in ample space, and that's why Ober complained one of the reasons
‫Obama complained about Bush scares about this because now, hey, I updated the column a.
‫Why are you touching indexes B and C?
‫So if you have a lot of indexes right, then what are you going to do is you will end up touching and
‫I say touching.
‫You're actually physically updating, doing I overdoing the rights to these data structures.
‫That's why I make you.
‫I want you just think these are things that are not in the air.
‫These needs to be maintained and the maintenance of this is costly.
‫Right.
‫So you have a lot of all of this indexes then becomes a problem, but post-crisis.
‫I believe that's another cautions.
‫I called into something called the heart optimization
‫equity heap only to and said, All right, we really didn't touch B or C.
‫We only touched a so does have an index if it doesn't have an index.
‫We have to update index, Hey, guys.
‫You change the representation of the row for Column A. There is an index on air.
‫We need to load the index a have the new value of that right because then the B three has to be reshuffled.
‫The B three have to be updated.
‫The keys have to be put into place.
‫We need to do all of this stuff regardless.
‫This is an update to that, a feel which has an index.
‫So you have to update it, feel it.
‫But the performance optimization that the post-arrest team did is like what B and C shouldn't really
‫need to be touched technically because they were they weren't touched, right?
‫Why are you updating them?
‫And that's what's called the heart optimization, which is right here.
‫I'm up against it on my face.
‫It's right here.
‫Hot optimization heap on the table, which effectively keeps the old tuple I.D. in the index right,
‫but updates the heap table, which we are already doing because we updated the roll right.
‫So then we do a pointer in the heap that the old tuple ID actually points to the new tuple I.D. in the
‫hip itself, so that if someone came to the old Apple ID, we let them know her.
‫Hey, by the way, if you want the latest representation of this double, it's has been updated, is
‫not it?
‫There is a new one here and this is done in the same page again, or to this larger, very important
‫to watch this lecture to know what the difference between a page and a hole and all that jazz, right?
‫So the page have many rows and and each row has many tables and this case of PostgreSQL.
‫So now you will have these multiple representation.
‫So it is.
‫They inserted some metadata in the page that says, Oh, tuple ID ID law is actually there is a new
‫version of it.
‫Go and jump to that some metadata.
‫Keep in mind only if the new updated top all happened to be in the same age.
‫As the old couple.
‫So does that mean because it has a fixed size, it's eight Keane, Paul Scholes at 16 in my squad.
‫So the moment the moment you update a row in both squares, we create a new tuple idea.
‫If there is a space left in that age, we're going to insert the new Triple ID in the page itself,
‫which is, as the bureau does, the best thing you can do.
‫Because now if I fit the page all the top of the it is in the speech, the moment you start jumping
‫to another page, that's where you need more oil.
‫Again, it really depends above the disc size and and and the block sizes of the operating system file
‫systems.
‫It's a little bit more complicated than that, but just think of its kind of logically right.
‫If things isn't the same page, then it's more efficient collectively.
‫So a lot of divas couldn't figure out something called the fitting factor.
‫How do you type this, Phil factor Google that do you want to know more about it?
‫And Phil Spector is is a page configuration that says, OK, so this page until 80 percent full, this
‫variant is 50 percent, right?
‫When you start, when you create a new page, only fill it and 50 percent leave.
‫What I understand is leave space for updates and DVDs so that we can use it for later, but instead
‫fill it up until X amount of percentage 80 percent, 70 percent, whatever you configure and then leave
‫it empty.
‫And then when you do that, what will happen is that page will leave some empty space in it for future
‫updates so that tables that have been updated.
‫Gets placed in the same page.
‫Right?
‫They will be reused, and that's that's basically the power of two factors very critical.
‫Very, very critical configuration.
‫When you become a DBA, that's just one of the things you have to know, right?
‫It's like there's configuring this based on the work load is completely something.
‫Yeah, yeah.
‫You gets paid to hand over fist for understanding these low level things.
‫Right?
‫Configuring things really can break or make your architecture.
‫All right.
‫So that's that's basically it.
‫So he points to only works of the tuple is in the same page.
‫If it's not in the same page, all bets are off.
‫We'll get up to the indexes regardless of what we're updating.
‫So that's basically why we're updating this indexes.
‫What's going on?
‫I guess this also answers this question, which is kind of similar.
‫Someone had a similar question here, which is like, I got a I have some confusion regarding the heart
‫approach for that identification problem.
‫And I basically I just answered basically this kind of question as well.
‫And so I guess I didn't talk about write amplification, you know, write amplification is the idea
‫of what we talked about.
‫You do a single object as a user.
‫This is there is a lecture called The Right Amplification, and this course by the other guys took it
‫up, so I didn't object.
‫I touched Field A in the.
‫That's all I did as a user select.
‫I did an update statement and I just pushed this thing set.
‫I just updated that single walk into anything that only that column.
‫I touched it.
‫But the the right that you think you did have an amplification side effects, because that's not really
‫the only thing that you have done technically there, you have indexes and plays, you have pages that
‫need to be sorted and shuffle.
‫If you have a clustered index, that's another story that might be that clustering that needs to be
‫done.
‫So we can all be indexes that needs to be updated regardless of this process, that those indexes might
‫have a three rebalancing, which will need more rights as a result, which results in an amplification
‫of right.
‫So you're doing a single thing.
‫But if you're looking at your own desk, you see a lot of a lot of rights, right?
‫Albers.
‫So this specifically when they were doing replication, which I have a whole section on, they had a
‫data replication.
‫They said, Wait a second, why is the network bandwidth overflowing?
‫We didn't do anything is just a single update.
‫They've seen a flood of rights.
‫Right?
‫So that's when they say, OK, well, we did the other day, but we have 20 indexes that have been updated,
‫right?
‫And Bosca's over where it was, was on an old version of Pulseless that didn't support keep people and
‫little pools, which is hot, which they saw this amplification.
‫As a result, bogus steam try to simplify the heap only eatable problem that that amplification by doing
‫the heart optimization they did that many other optimizations as well.
‫All right, guys.
‫That's it for me today.
‫I'm going to send the next one.
‫Hope you enjoyed this lecture?
‫Talk to you later.
‫Goodbye.