‫Hello, everybody.
‫It is October 2021, and I thought it might be interesting for I to go through your fantastic questions
‫on this course introduction to database engineering as you ask great questions and kind of go through
‫some of them that I again have picked out and then answer them in details.
‫I try as much as possible to go through this question and answer them.
‫You guys ask fantastic questions, but I thought some of these questions you it's very hard to answer
‫in tax.
‫So it might be interesting to make a new lecture called Q&A and answer some of these questions in detail.
‫I'm going to if you like these kind of lectures, let me know in the common discussions.
‫I guess you can have discussions in this lecture.
‫Let me know if you want more, and maybe I'll get to do it in a couple of months about just jump into
‫it and see who try to answer some of these questions.
‫So here's a question from Constantinos.
‫I hope I pronounce your name right and on the getting started with indexing lecture on our introduction
‫to and drink or getting different results with explain, analyze high dosing and saying thank you for
‫the direct and comprehensive explanation you get every time you're welcome running, explain analyze
‫using the exact same syntax with your two queries.
‫I get different results for the first explain analyze select ID from employees where ID equal two thousand
‫I get a value of when he fetches.
‫For the latter, I get none effectively right.
‫And then so so they are effectively saying that you're I'm getting different results with every time
‫I execute non the results I get every I get, I get different timings when I execute this query twice.
‫And let's explain a little bit here.
‫What do we mean by heap features first?
‫So when you execute a query and specifically in Postgres, right?
‫And that query goes through a path that is.
‫Exclusively searching the index, which is index only scan.
‫I talked about that and one of the lectures index only scans.
‫That means that it should only go to the index structure, right?
‫Because we know that there are two structures that is the heap or the entire table worth of data.
‫And there is another data structure called the index that has subset and pointers to the heap with the
‫actual content.
‫So if you're only doing an index only scan, you should only be looking into this structure, which
‫is the index structure, right?
‫At most queries that are index scans, normal scans, they go to the index, find what they want and
‫jump exactly to the page or the block in the table that has the data and pull it up, right?
‫If you don't have an index, you have to scan the heap for the entire table.
‫Right, that's that's how basic indexing works.
‫He pitches tells us that, yeah, I did an index only scan, but I had to go to the heap to do that.
‫So this is kind of contradictory because hey, if if all the resources index X already jumping in the
‫heap, you jump Postgres keeps these things going.
‫Hence, that tells you if a row is deleted, updated or modified.
‫So you see when when I delete, for example, a row in Postgres or an insert or even updater row you,
‫we create a new version of this rule because both Clarissa's and MVC designed a multi-year concurrency
‫control, which means that we also update the index to update the entry.
‫All right, that that that points to our tuple because the index also points to the table because you
‫touch the row.
‫We have to update the index to point to the new version, right?
‫But we also also keep the old version because some other transactions are also reading, right?
‫The older running transaction need to make sure that the rule is is running and alive as a result.
‫So because of this, all of a sudden now transactions need to make sure that is the role I'm looking
‫at deleted or updated or it is or is.
‫Is it alive?
‫To answer this question, is it alive at that bits?
‫Is it alive?
‫Can I see it?
‫Is these kind of bits?
‫The hint bits are stored in the heap.
‫And as a result, you see these jumps.
‫How do you get rid of these jumps?
‫Well, two solutions.
‫If you have long running transactions, then you will definitely see these jumps because these jumps
‫means that there are transactions are very old, very long running, and they they are reading old data
‫that we cannot get trade off.
‫That means, hey, from time to time, I'm going to have to jump to the heap to at to check if this
‫rule is visible or not.
‫But most of the time, if you don't have any running transactions, that means you can freely clean
‫up these old rolls and we can freely avoid going to the heap to see if this rule is alive or not.
‫Run vacuum.
‫I talk about vacuuming and allies and all this stuff in many lectures on this course, right?
‫So if you don't vacuum after each after all the updates, then you should get consistent results as
‫of the timing, right?
‫They did mention that the timing is like point zero zero four seven millisecond versus point one seven
‫milliseconds.
‫This is expected because the timing you only have to worry about times if it's more than, I don't know,
‫ten milliseconds, right?
‫12 milliseconds.
‫Right.
‫Six milliseconds.
‫But if it's less than a millisecond, there is so many variables with the hardware, you know, then
‫you getting into really low level stuff.
‫And this is really not worth going through you.
‫Only if you seeing 100 millisecond to one.
‫That's where you actually optimize point one point two.
‫It's really not deterministic.
‫Depends on the CPU.
‫Depends on the hardware that happens.
‫And there's other processes running with Postgres, your machine probably having multiple processes
‫that is hogging your CPU.
‫Right.
‫So that's kind of answers this question.
‫Let's pick another one.
‫So this is another interesting question from Ihab Ahmed on the SIDBI practice examples lecture, right?
‫Is durability versus persistent Hyson in 29 minutes.
‫So you said that Origi's doesn't offer durability because of course it is.
‫In-memory database lives in rum if electricity goes down.
‫Data is lost and also you, you said, but it has persistence.
‫I need to know, what do you mean by that?
‫And I want to understand your bird.
‫Well, what is the difference with durability and persistence?
‫They are effectively very, very similar, right?
‫I might have confused you in that lecture, but let's try to kind of clarify this.
‫So we're going to come to write this.
‫But let's explain the concept of persistence to persist is to right to a specific location and have
‫it there, right?
‫So like if you write to a desk, it's persisted to desk, right?
‫And then that means if you turn off the electricity and you turn it back on, that that charge will
‫still be there.
‫Whether you're using magnetic work as a hard drive or a charge in case of as is the the data is there
‫that's called persistence.
‫Now durability is is a term that database uses and the idea of if I write and if I said I started the
‫transaction, I did a bunch of queries and I wrote a few things and inserted that update of the update.
‫And then I said, commit.
‫My transaction is durable if.
‫After the comment, I tell you it succeeded.
‫If after that, after a millisecond of you telling me that the transaction committed successfully,
‫I can turn the database off completely, I can unplug the computer and plug it back in and start the
‫database, and the data should be there.
‫Now, the durability has to use persistence in order to obviously have the data on right.
‫And that's that's that's basically what it is.
‫And now read this is durable.
‫But it depends on the type of durability option that you pick.
‫And I don't believe I talked about this in detail, but here is free.
‫Like you guys, the idea of IS has, I think, three formats.
‫Effectively, right there is the snapshot format where asynchronously when you issue right, these rights
‫actually, and this goes to memory first and they will commit immediately and they say here.
‫They wrote the riot was successful because it was successful writing to the RAM, to the memory, right?
‫Doesn't I say RAM, but memory is.
‫Is there a vast right?
‫Because could be virtual memory, right?
‫Could be memory on disk?
‫That's kind of a memory right at the temporal thing.
‫RAM is the random access memory, the slot Needham's.
‫And when I write that you get back, commit successfully.
‫But.
‫In that case, if you lose power, you lost your data.
‫But red is what they do with the snapshot is.
‫After a second or two, they take the entire memory and they flush it to disk.
‫They actually persisted to disk, so it is only true that you lose your data exactly after their second
‫right.
‫If the asynchronous process fails to try to disk and you lose power, then Reuters will lose your data.
‫But if you write, write, your transactions succeeded to it, writing to the RAM and then that read
‫is asynchronous process kicks in and then snapshot showed the RAM to disk, and then it failed, the
‫note read.
‫This is actually persistent and durable because hey, did lose your data.
‫If it happened in that X amount of seconds and you can configure, then you will lose your data.
‫So, yeah, it is durable, but there is an option that you can also now you this is the fastest right
‫option.
‫There are many other options and readiness that has stronger durability because that that was like a
‫weaker durability or the stronger durability option in what is called aos append only file.
‫I think I talked about it and answer here as well.
‫So the idea here is as you persist analysis, as you commit your transaction, we write it to the RAM
‫and also we write it to an append only file called this a redo log.
‫Some people call it the wall and traditional relational databases.
‫We call the wall the right to head log and only one that succeeds writing to the wall, which is the
‫Journal, which is on disk.
‫Then we actually say sexy, and this is this is effectively a log of the metadata entries itself.
‫The X.
‫The actual description of the data includes the data as well, but doesn't have the full data structure
‫of that data.
‫That makes sense.
‫Like if you're writing into an index, write the index itself.
‫It's complex, right?
‫But so the log will have an entry, said write X to the index blah, right?
‫So this is always compact, but writing for the actual indexes has to be trees and you have to update
‫many, many locations and stuff like that, right?
‫So it's not a one to one, always.
‫So that's the only file.
‫And Redis has a stronger durability, and it's it's one to one exactly identical to the to the relational
‫database.
‫So yeah, I this is actually strongly durable if you chose that option, but it's going to be slow in
‫rights because now you're writing to disk with every with every transaction.
‫Right?
‫That's why this is only fast because it's writing to memory, right?
‫But there is always a choice.
‫If you always if you took a snapshot, you can choose both it off and a snapshot as well.
‫To get the best of the award, you choose snapshot.
‫Your rights are fast, but you might lose your data might if that happened, which is very rare, that
‫it can happen.
‫If you're willing to take this chance, you can or you can have your rights being slow, but you have
‫higher durability.
‫The option is yours.
‫That's why I like this choice and release.
‫You don't have this choice in other Postgres or MRGO.
‫I don't give you this durability choice.
‫Let's go to the next question.
‫So there's another question from Tanner shared on their shared versus exclusive lock lecture the lifecycle
‫of a lock in a transaction.
‫If we update a roll during updation, the rule acquires an exclusive lock.
‫Does it have an exclusive lock until they come into the transaction?
‫So the question is what is the lifecycle of a lock, right?
‫So this difference from one database to another, but the idea is.
‫If I update one roll right, then that rule is locked exclusively.
‫Assuming the database supports roll level locking, not all database supports that.
‫Some databases, actually when you want to lock one rule, it locks the entire table because it says,
‫Hey, it's easier.
‫It's cheaper to lock the entire table instead of locking one rod because assuming you update a thousand
‫drawers to maintain thousand locks.
‫Yeah, that's what most databases do.
‫Sequels, or perhaps does that actually pose?
‫Because does it differently and maintains the lock in the table itself, like Oracle does a different
‫every database does it differently.
‫But yet locks are expensive.
‫So as you update human DNA lock and when the transaction rolls back or commit, we release that look.
‫And the reason we we obtain an exclusive lock on the row itself when you added it so that nobody else
‫actually tries to change it because we don't want the transaction to change it sometimes.
‫So I don't want another transaction to even read it right there.
‫Read this version of the roll, right?
‫The word exclusive here is a little bit harsh.
‫Exclusive means don't even allow reading it right.
‫This depends on the type of database.
‫Again, some databases, uh, prevents you from updating it, but allows you to read it in some database.
‫Some databases doesn't even have this concept like both because it has multiple versions.
‫Hey, I'm updating this zero.
‫I create a new version locked in.
‫The old one is is free to be read, right?
‫You can still read it.
‫So once you affirmatively roll back or commit the transaction, this lock has gone plain.
‫Lock management is very, very expensive effectively.
‫So, yeah, that's in a nutshell the lifecycle of a lock effectively.
‫But there are many, many types of lock up that answers that question.
‫So it's also this final question index scan versus index only scans.
‫Krishna asks What exactly is the difference between creating an index on columns directly versus including
‫none key columns, right?
‫So they're they're they're they're asking the difference between, Hey, what if I created two indexes
‫here by a create index ID on the ID field and creating another index on the name field.
‫So I have two indexes verses.
‫I create one index on the IED field and I include the name.
‫There is a huge difference between the first of all this.
‫You have two indexes, so you have two data sections, so you have to be trees, right?
‫So twice the amount of not really stored different storage, but twice the amount of data structures,
‫right?
‫And well, while the second is you creating one index and in that index is keyed on the ID.
‫But you are including with each entry, the name, the corresponding name value not to search for,
‫but to include it.
‫So that means if you search by the ID, you the B3, the index will be used to search on the.
‫And for free.
‫When you find the I.D., you can get the name for free without getting to the heap to the table itself,
‫to which the name column.
‫But if you search by the name column, this will not hit the index because the name column is not indexed
‫on the first example that you showed here when you search by the name.
‫Yet there is an index and you can search by the name right?
‫It's a it's a little bit of a different thing.
‫And there's another way where you have a composite right index, where you have one index with two values
‫where you can search by the ID and you can search by the ID and the name, but you can search by the
‫name only as well because the name is on the right side of the composite key.
‫I talked about that in the lecture.
‫All right, guys, let's end up here at just answering for questions.
‫It took us a long time.
‫Let me know if you enjoy these kind of Q&A.
‫I'm going to see you in the next one.
‫Enjoy the course.
‫Goodbye.