‫Today's topic is right, amplification, database, right, amplification, you know, guys, this is
‫one of the reasons that Ueber have moved from PostgreSQL to my school and we obviously talked about
‫that move that is not entirely approved by the community because nobody missed a lot of things in that
‫article, but it was a valid reason nevertheless.
‫So.
‫Right.
‫Amplification, if you don't know guys, it's like a phenomena where your update stadtmueller your school
‫or whatever.
‫Right.
‫You are performing logically.
‫The actual physical representation of that.
‫Right, is double or triple or maybe a magnitude more order of magnitude more than the actual logical.
‫Right.
‫So you would think like, hey, I just did a single field in a single role.
‫But you look at the disk usage at that point and you see it doing thousands and thousands and thousands
‫of kilobytes.
‫Like what what I did, I, I merely updated a 32 bit integer a what the heck happened?
‫The right amplification.
‫You can look at it from three tiers.
‫The first thing was I kind of I saw from building apps.
‫Right.
‫And I called it the application.
‫Right.
‫Amplification.
‫The second type is the data base.
‫Right.
‫Amplification and the third time is the disk right.
‫Amplification, which is the SSD.
‫Right amplification.
‫I'm going to talk about the three of them as this.
‫These are infamous for right amplification.
‫So I'm going to talk about that.
‫And then database, which is something that is hidden from you, depends really.
‫Database platform and then the application itself, which is kind of your fault as a backing engineer.
‫So let's jump onto each one of them.
‫All right.
‫So let's start with the application.
‫Right.
‫Amplification and this is maybe your end user is expecting something, but it turns out that the actual
‫work behind that API single call or update is magnificently larger.
‫For example, you're taking your to do item.
‫Hey, I just done this desk, but so to the to the developer, the front end developer in this case
‫calling that API to kind of just hey, I just deleted I just I just marked this as done right so far.
‫The developer.
‫My thing.
‫Oh this is just an update.
‫Statement maybe our own a Synagro, but a back end developer could have written this application in
‫a way so that, hey, it goes to the to do app mark that's to do as done and then goes to another table,
‫maybe inserts that raw into another archive table.
‫Hey there, Hosain.
‫Have done has washed the dishes, for example.
‫It goes into another.
‫So that's two.
‫Right.
‫And then maybe you have another table maintaining the count of the task that you have done.
‫So you your increment or your update, another table with the actual work.
‫So one single API translate to many, many rides.
‫And obviously there's this goes downstream and affects us.
‫So this is the simplest of thing you think is the single.
‫Right, but the actual API implementation, it does a lot of things and relational databases are designed
‫this way, especially if you have normalized table where you have many, many tables that you have to
‫kind of maintain and keep it consistent.
‫Right.
‫That doesn't mean obviously N Equal is infamous of that.
‫Obviously, it has its own limitation as well.
‫But now this is the first time the application.
‫Right.
‫Amplification your app claims to do one thing, but actually behind the scene is doing way, way, way,
‫way, way, way more.
‫Another famous thing is delete some people, implement, delete as as a as an actual update and an insert.
‫Some people implement delete as as a logical delete.
‫Right.
‫Where do they do more and more work at the back end.
‫So a really different.
‫So that's an application.
‫Right.
‫Let's talk about database write ID and this is whenever you hear right.
‫Amplification, it could be either the the database ID or the other one which is this is the ID.
‫So the database ID is is a property that is hidden from you as a database platform.
‫I'm going to give the example of positives because of the most infamous and it is the exact same thing
‫as my sequel.
‫But with that, my isaam storage engine, they both work almost identically in this case.
‫Oh, not not quite.
‫So let's say you have a table with six columns, write five columns, have an index indexes, each has
‫an index on them.
‫And the last one, which is the sixth column, doesn't happen in.
‫So if you execute an updated statement, you pulled a singer on the stable and then you updated one
‫of the columns, I say column one, which happened to have an index on them in PostgreSQL.
‫This is what will happen.
‫Postgres will create a brand new row.
‫That's how it works.
‫Always any update, any modification on the Tupelo's create a new Topal idea.
‫That is a terrible idea that uniquely identifies the road.
‫Yeah, it will create a new one with the new values that you have.
‫Copy, exact copy.
‫We have to update the column responding index because we choose the value.
‫So now we update the index.
‫That's something we have to do anyway.
‫So that's another.
‫Right.
‫So we inserted a new role and now we want to go to the index and change that tuple idea from pointing
‫to the older or it points to the new role.
‫And we also change the value.
‫We introduce the new value that you inserted and that tuple in that right.
‫So now it's almost two changes.
‫And you might say, what about the rest of the index?
‫These five indexes still point to the old row.
‫Polska is used to update those indexes, to point to the new tuple.
‫And that's what you didn't like back in, what, 91 or 92?
‫I don't remember.
‫And so so the right volume volume that is happening as a result was enormous, especially when you start
‫you did a single update.
‫Now you're touching all these index and you have a lot of indexes.
‫All of sudden these indexes have to be touch despite them not being changed.
‫If you think about it, they will not change.
‫But Postgres actually solved this problem.
‫I don't remember when maybe it was nine four or nine three doing something called the heap only to build
‫a hot Topal.
‫That essentially it's a trick, so, OK, it says we didn't really change the value if we didn't really
‫change a value, we don't really go to the index to update it because that's that's just wasteful.
‫So they they definitely fixed that.
‫The only Topal will create the road, the new road to topple it and then points to the old road to the
‫new one is that, hey, by the way, if anyone happened to land on the old road, tell them that there
‫is a new version of this.
‫There is a new heap, only Topal that points the only limitation here.
‫It has to be in the same page.
‫Right.
‫If the page is full, then you cannot really do that.
‫Right.
‫That's that's why it's called hipbone and that's why.
‫Well, full factor come in.
‫You have to choose the correct fill of factors.
‫It's a really complex things to understand.
‫You have to be really careful when you're building this stuff anyway.
‫So it used to do that, but no, not anymore.
‫But I wanted to give this example to right.
‫Amplification is a faint, especially with Postgres, but it could be with any really any other database.
‫Let's say you did another one single update.
‫You updated one roll with many values.
‫You changed almost all columns.
‫Right.
‫There is no way in my school, on Postgres, on Oracle, we have to do we wrote one single value, one
‫single rule, but as a result, we have to touch all the indexes.
‫That's another.
‫Right.
‫Amplification, OK, because if you look at the update segment, it's logically, it looks to me like
‫updated a single row.
‫But to the database, you actually did more and more work.
‫You did a lot a lot of physical work.
‫And when I say physical here, I mean database physical.
‫Right.
‫Because it gets really confusing when I say logical and physical, where ordinarily when I say logical
‫database.
‫Right.
‫Is the school and physical database right on essentially the wall changes the right ahead log.
‫Right.
‫All these actual changes that you have the pages.
‫Right.
‫But when it comes to this, this D, which is the next topic, that's a different beast, physical and
‫logical becomes different things right now.
‫This is the database ID, very powerful.
‫You have to really understand it.
‫Right.
‫So that's the second type of medication that goes to the final one, which is a very infamous one without
‫ID.
‫Even if you go to the wiki page, when you write random application, that's the only one that shows
‫up anyway, which is the zero ID.
‫So to talk about that, we really need to understand how is this really works.
‫So this is the guys have this idea of someone invented something called the charge strap is a technique
‫where you can store multiple level of electrons.
‫You can from zero electrons up to certain levels and the quantity of the electrons determines certain
‫values.
‫And this is the trick we used to correspond to this.
‫And these trap are put into cells like so Acel can have a lot of charge traps.
‫And as a result, one single cell can have up to the recent newer stuff up to 16 different charge levels.
‫So if you do the math right with the computer science, 16 is how many bits?
‫It's four bits.
‫Right.
‫So that means each cell can store four bits and once you charge them, you store them.
‫You can turn off electricity works.
‫It's persisted.
‫It stays for ages.
‫It does not go it does not get released.
‫Right.
‫And you can essentially read those values back.
‫And then based on the quantity of number of electrons, they'll get this.
‫Oh, this is zero zero zero zero.
‫Oh, this is zero zero zero one.
‫Oh, this is zero zero one zero.
‫This is oh.
‫Is this the one one one one based on this trick, they build the entire SSD.
‫So now they took one cell, they organized into a row and then the rose becomes the, the, the, the
‫pages.
‫Right.
‫So there is a page has many cells and many rows.
‫I so think of a page now this have a lot of these charge drives so a page can have a lot of bits stored
‫in it and you can do that.
‫And then pages are organized into blocks.
‫Right.
‫And here's the trick here.
‫Right.
‫So now you have you have control of a block that has many pages.
‫And this is what we're interested in here at this level.
‫Right.
‫And if you love to read more about it, I'm going to reference a video that absolutely offers a very
‫technical, low level thing.
‫If you're going to learn more about it.
‫This is deep.
‫It's a very good animations.
‫So I loved it.
‫So so now this is what we need to understand.
‫Block and pages.
‫However, nothing is free.
‫Obviously, here is the condition.
‫Is this D?
‫Yeah.
‫You can write stuff for us, you can write in pages.
‫Right.
‫As long as the page is.
‫It doesn't have anything right, does it have any levels kept as one of the pages you can write anything
‫you want on it, right?
‫And when you write it, we're going to store it.
‫But if there is a page that is already has some content in it and you want to update that content to
‫something else, let's say you have a bunch of rows and have values.
‫You want to update something, you have to erase this page and write it back.
‫You cannot just overwrite it, right.
‫That's impossible.
‫So what this is does when you want to update.
‫So what what it does is says, OK, let's just write the new value into a brand new page and then leave
‫the old page as a stale market, as stale so future people can clean it up if they want to use a future
‫requests.
‫And here's another limitation.
‫And this is the you cannot clean up one page at a time.
‫You have to clean a whole block.
‫And you can see some pictures here.
‫A block, a block has many pages.
‫And I think it's eight or something like that.
‫If I come with with a payload, I want to update a page.
‫I have a block or the three pages.
‫I have four pages.
‫Let's write them abcde.
‫We just write them back right there.
‫OK, and then let's follow that.
‫And you can see the second page.
‫I took this from Wikipedia resources right now and then let's follow it up with writing e f g h.
‫Right.
‫And then so that's four pages.
‫But I also want to update pages A, B, C, D, so an update what it is to do.
‫It marks those old ABCDE pages as stale and then write new four pages in the same block.
‫So now this blog is filled.
‫If you have a new rights that you want to come in and you want to use this block, you cannot use it
‫to the ISS, the drivers.
‫This is filled, this block is filled.
‫You cannot use it at all.
‫Right.
‫And as a result is just filled.
‫You cannot use that block.
‫So there is this process that's called garbage collection and it runs asynchronously.
‫It cleans up this kind of thing.
‫So so this block has four pages that is in use then.
‫And it's costing us a lot of precious page memory space.
‫Right.
‫So what we do is, hey, garbage collection, move all the active pages from this block into a brand
‫new block and then just slam that block a block shot clean, flush the whole thing, clean it up so
‫I can use it.
‫I can start using all the free spaces.
‫So now my pages have moved to the block, the second block, and they have now for around four spaces
‫that I can use to write the stuff.
‫That is why is is these do not like updates because of this thing.
‫Right.
‫You you're marking this pressure old pages as stale and then writing in the new locations every time,
‫as opposed of new, fresh, nice sequential rights.
‫Right.
‫Every time you just using new pages, you never touching the old pages.
‫So if you don't touch old pages, if you don't see old data, you're never going to mark these pages
‫as stale and you're never going to have blocks with stale pages, which is the worst thing you can do
‫if you have blocks with stale pages that have been updated, the garbage collection is going to kick
‫in and the water level is another thing that's going to kick in, which is it's going to start flushing
‫and moving in new pages to talk to a new blog and then flushing blocks.
‫All this work, all this is this is going to do as a side effect of your original, right.
‫So updates are the worst thing you can do is the SSD like SSD like is it is always like beautiful,
‫just single.
‫Right.
‫So that's why Beta is right.
‫You if you go to the databases, beta causes this kind of behavior over time on SSD, because even if
‫you're doing only inserts, Beta is still need to get updated gear.
‫Updating in place a big three pointer you're updating in place.
‫Beta is locations.
‫You're rebalancing the trees.
‫And when you do that, B or B, B plus three, when you do an update, what do you do with this is do
‫you go to that page?
‫What exists?
‫Slavitt as stale and then take that value, take the new value and write it to a new blog.
‫So now the more you do this, the more you're going to have blocks with stale data.
‫And when you have blocks with stale data garbage collection, you'll get a kick in this sky.
‫Start moving your Prussia's block to some one place, which is an expensive process.
‫It's going to happen again and again and again.
‫Eventually you will get.
‫They down very, very quickly because it's going to you're going to start using all your pages in unnecessarily,
‫let's put it this way.
‫And when you start using all the pages and as deep as this, these have this are going on with where
‫leveling where they essentially says, OK, I have this page sitting for years, nobody touch this,
‫and nobody written in that page.
‫And maybe I forgot to mention that.
‫But that page has only limited no amount of time to write, after which it just dies.
‫That page location just dies.
‫You cannot write to it maybe 2500 times or whatever.
‫There is always this is this has a lifetime.
‫All right.
‫So there will be if you without leveling, there will be pages that is very busy where you are writing
‫to it a lot, this physical location.
‫And there will be pages that are never touched.
‫So there will be unbalanced.
‫So we're levelling as this is the concept where it just really bana's this thing says, OK, hey, this
‫is a cold page from 1998.
‫Nobody touched it.
‫Let me move it to a new location and let me use that precious location to write new pages because you're
‫doing all these updates eventually.
‫All right.
‫So as the the more you're doing that, the more we're going to kick in and to start using all this pages,
‫all uses these in a balanced manner to update's always kills Orissa's.
‫The fast, doesn't I?
‫When I say that I'm talking years and years, obviously I'm not talking about bouncier.
‫Right.
‫So, yeah, that's why.
‫People came in with a log structure ministry as an append only, so that we are having nice inserts
‫tucked in together so my blocks don't have these stale pages and always have a six consecutive pages
‫that are never updated.
‫And as a result, SSD, these love this stuff is going only have pages that are never updated.
‫As a result, there are clean.
‫If you go back to old pages and start updating them as they have to move them to a new location, they
‫have this block now all of a sudden have a stale page and all of the other pages are reused.
‫So it has to move those pages to somewhere else and flush it or use it.
‫And that's a lot of work for this August.
‫So that was the right amplification from the application point of view, from the database point to
‫and from this is the so you're single to do marketing the to do app as done if you're doing that single
‫update that the front end developer, things that you're doing a single day, but you're actually touching
‫all these tables and those tables kick in the database.
‫Right.
‫Amplification, which is table has like an all seven index and in this day will have three indexes and
‫this day will have, I don't know, four, four indexes.
‫Those if you're touching those obviously values with indexes, then those corresponding indexes have
‫to be touch.
‫And here's another one right in Postgres.
‫There is this concept of toast tables where a string, if there is a long string, it never stored in
‫line.
‫Right.
‫Is not another table.
‫That's a toast table.
‫So that table need to be updated.
‫So the right amplification just triples.
‫If it is just like order of magnitude, there's a lot of work.
‫And then now all these rights, which some of them are updated, some of them are insert.
‫It translates to SSD, right, amplification, ad, the physical disc level.
‫So all these pages that you have to update and then move the new page, mark the old pages still move
‫the create a new page and then have this is the point, the logical page pointer to the new page physical
‫page, because that doesn't have the physical and the logical as it is as the concept.
‫I know guys, it's a very complex topic, but I just wanted to make a video roar like that.
‫It's a very, very important topic to understand.
‫And then we're going to have to make a new and more more videos about this and maybe I'll go make a
‫practical video discussing this in details.
‫This was a right amplification.
‫Just keep an eye on it.
‫It's really critical.
‫By.