‫Welcome to another Q&A of the month of January 2022.
‫This is the series where we pick one of your wonderful questions that you submit here and the fundamentals
‫of database engineering.
‫Udemy course and try to answer them in depth as much as possible to my extent and experience.
‫How about we jump into it?
‫And today I said I thought we're going to pick two questions.
‫This one comes from Chris.
‫And says, Doug, about Reed, only transactions, when do you want to perform only reads in a transaction.
‫So this is effectively the question relates to read only transaction.
‫So read only transactions are what are those first?
‫Those are transactions that you.
‫Issue from the client to the database and any queries that execute in that transaction doesn't do any
‫changes to the data in the database.
‫So the benefits of this is really very minimal.
‫Right from my my own experience and just researching this topic is the first thing is it will protect
‫you as a client.
‫So let's say you have a bunch of code and you're trying to, you know, execute a transaction, but
‫you want you or you're not sure what's beneath you.
‫You're calling them a bunch of methods.
‫And these methods are executing queries.
‫But you want to make sure that you know that whatever you're about to do, this operation that the user,
‫the high level operation that the user will do, is it only reading it shouldn't do any writing.
‫So if you do, I read only transaction that will protect you in case any of these underlying method
‫that you're calling accidentally tries to, you know, try to change something, it will fail.
‫So so you'll find the bad code and stop it right there.
‫And so the other reason so this is one reason, at least from the client protection perspective.
‫The other reason is that the database level, you know, if the database know that the transaction that
‫you're about to start will never change anything, it can sometimes take shortcuts and do certain performance
‫optimizations.
‫Not all the time, but sometimes.
‫Yeah, because when you start a transaction, you can either read or you can read and write.
‫Writing is more expensive than reading, and just by that chance that you're actually modifying the
‫data, you need to obtain certain types of locks.
‫You need to aversion that data if you want to support multi version concurrency control.
‫So there is some more work that you need to do that you need to generate transaction IDs.
‫In case of Postgres, you need, you need utility.
‫So if if it if the database know in advance that you're never going to change, it can skip all that
‫stuff, right?
‫Doesn't mean that you're going to gain performance because most databases are now smart enough.
‫Like, I'll give you example in Postgres, even if you say if you start the transaction where you say
‫it's read only or not, it's by default read only until you make your first right.
‫And that is when it will become a right transaction.
‫What does that mean?
‫It means that it will issue a new transaction ID for that transaction.
‫Issuing these transaction IDs, we talked about them many times to the lecturers of this course is expensive.
‫Is going to be expensive, right?
‫Because this is how we identify that all this rule was alive from this transaction ID until this transaction
‫idea that killed it was that whether there was an update on an actual delete.
‫And so issuing this transaction, I.D. keeping them in sync, keeping the state of those transaction
‫and obviously the post process, which is vacuum needs to clean up, right?
‫Take an example.
‫If you if you have 100 million transactions and let's say 50 million of those or just read only we,
‫we only need 50 million.
‫Transaction ID is you don't need 100 million transaction ideas.
‫So as the scale read, only our is better.
‫But whether you specified or not really depends on the database that the or a small enough it knows
‫that you're now trading effectively, right?
‫But again, if if you don't if you know you're not reading or you, you're not going to write.
‫Sure, it's always a good practice to do a read only transaction just to protect you.
‫So the next question comes from Vitaly Is this Italian?
‫I believe it is.
‫You idea versus sequential primary keys, and let's be very careful, what do we mean by a primary key?
‫We talked about that.
‫That's why I want you to watch that original fundamental section that I talk about this building block,
‫right?
‫Primary key.
‫It's not just unique, it's the idea of clustering your entire table based on that key.
‫And we talked also about what a clustering of you mean.
‫So he's asking here, they're asking whether it's better to use sequential keys like one, two, three,
‫four or five integers versus you.
‫This is a very loaded question, and I can't possibly tell you, Oh, always you is sequential, always
‫you.
‫There are cases where you are very useful, you know, because it's a universally unique identifier,
‫the client can generate it like that and then just submit it.
‫And we almost guarantee that nobody and the war, we're going to have the same idiom.
‫That's the beauty here.
‫And when you do that, you don't have to incur the cost of having the database to issue a new sequence
‫ID.
‫Right.
‫Because there is a slight delay, slight, almost insignificant to issue a sequence because what how,
‫how the sequences work, right?
‫The sequence of the database level need the database need to ask, OK, what's the next idea I can generate?
‫Imagine 100 times asking the same sequence RAM.
‫These need to be cute, right?
‫Because you cannot just generate concurrent sequence ideas, it has to be in sequence, right?
‫So so if if two transaction asks for the sequence, I need the first one.
‫They have to be cute.
‫The first one will get one, the second will get two, but nobody should get the same idea.
‫Otherwise, the whole thing will be bust, right?
‫So that's why building sequences and counts are very difficult.
‫You need to obtain locks.
‫The database takes care of that stuff.
‫It does a small things.
‫You know, you can either lock, you know, and weigh a block other transactions so this waiting can
‫slow you down.
‫Know if you have a flux of things that it depends on on the way you can test this yourself and versus
‫your ID.
‫If you have, if they're called generating that, you just slam that insert and it immediately doesn't
‫need to do anything.
‫Just insert the.
‫Doesn't need to do anything, of course, it need to go and update the indexes and all that stuff,
‫but it's it doesn't have that extra auto sequence right now.
‫Obviously, databases are smart enough.
‫They they try to optimize that as much as possible.
‫But just think about them now because you it is unique and universally unique.
‫It means.
‫It is expensive.
‫Why, if you put it as a primary key?
‫Well, let's think about that for a minute.
‫How big is that unity?
‫Let me check a little bit quick.
‫So it's 128 bit, 128 bit.
‫So that's 16 byte.
‫It depends how you actually store the unity on all of these details.
‫You need to know about this, guys.
‫You cannot just assume everything will be done for you.
‫You are responsible to understand all this stuff.
‫So how is this going to start?
‫Are we going to start as a strength?
‫If it's a string, it's even worse, right?
‫Because it's going to have these dashes with it.
‫If it's a string, then how many?
‫How many of these letters do the math?
‫So if you start as a string that that unit that you your idea will have the dashes will have the characters
‫in hexadecimal and you're going to store it in, what, 36 bytes.
‫So even more so if you start this thing, it's even more expensive than storing it.
‫As a native, you use it if the if your database actually supports them.
‫So 16 by minimum, 36 if it's a string, so that takes.
‫Size, right?
‫You might say, I don't care.
‫I have all the disk storage on the war.
‫No, nobody cares about storage anymore.
‫We know we have that.
‫It's the cost of putting that -- thing in memory and scanning it.
‫It's the cost of copying this primary key and all the indexes that are secondary because secondary indexes.
‫Again, we're talking about general primary key secondary.
‫In a sense that is not post-Christmas.
‫Chris does things differently.
‫Right?
‫Secondary Index says if you have a secondary index, the value of the keys or what you index, but the
‫value of that secondary index is the primary key.
‫So if you have a primary key on you, it then would get us land there 16 Bhide or 32 36 buy in every
‫entry.
‫In the secondary index, Dow bloats the secondary indexes.
‫What happens when we both as a contender, says I don't care.
‫We have all the size of the world.
‫We have all the time in the world.
‫No.
‫If you build the secondary indexes, then scanning the B3, which we have a whole section on becomes
‫slower, right?
‫Because at a read of a page, we'll have fewer rows.
‫If you have that kind of fat keys, right, it's just it's just logic.
‫You know, if you have a one byte, if I pitch at four pages, I have four thousand bytes right versus
‫if I have 16 or I have much, much fewer roads, right?
‫So the value of an IOU becomes less and less valuable.
‫You don't get as many keys, so your you find yourself going back to the memory.
‫Going back to desk, doing a lot of I.
‫So that's the cost of you.
‫It is right.
‫Another cost of new ideas is the randomness.
‫People don't like databases, don't like random stuff at all.
‫And the reason is when you issue this, you insert specifically rhyme and reads, for that matter.
‫Right?
‫What do we do?
‫Let's talk about it.
‫If I go, Hey, pitch me this role where you idea, call blah.
‫Yeah, well, the database will do a plan, then find out that you have an index beautiful and will
‫go and will find based on the B three find page.
‫So in that page, you have not only that you do, you have a bunch of other new ideas that happens to
‫have the same order RAM because indexes are always order, right?
‫So now we have these ideas in order and then we pull the whole page.
‫That's how we do things.
‫So we have a beautiful page that we just read.
‫So let's say if someone else just read.
‫Some other you idea.
‫In that case, we're going to scan the battery and almost never.
‫We're going to hit a page that is already cached because it's not, it's not.
‫It's not an order at all.
‫Then what is the chances that they're you?
‫I'm going to read is next to the one that I just read them.
‫You might you can say something about the same thing about sequencer, but it's all it's not as common.
‫They were going to come to that.
‫So you have a lot of random reads.
‫So you're phishing pages and you only pick one row and then another one wants to do something and then
‫you pick another page that doesn't.
‫It's not.
‫It's not in the RAM, it's not in the cache as on the cache that you find yourself doing nil, which
‫are expensive.
‫So these random reads sales performance.
‫Same thing with insert.
‫When you insert something, we fetch where we want to insert it and we inserted and we keep that dirty
‫page in cache, right?
‫So that hopefully we're hoping that someone else will insert something that goes into the same dirty
‫page before we flushed.
‫2-Disc This way, how we can obviously get it better performance with with with sequential IDs.
‫Every time you construct one, two or three or four or five, you're going to pull that page where one
‫exists and the next 10 or 20 IDs and then you've got to insert them.
‫So the two comes in, Oh, we have a nice and hot and memory.
‫Write it right to drive.
‫Obviously, also right to the wall and the the the the reader reader logs.
‫Right?
‫And you also write on the page.
‫So now you don't have to go and fetch that dirty another page where that idea should exist, right?
‫So you have nice in memory, right?
‫Beautiful.
‫We love those with reads with sequential IDs.
‫You can do between right there because i o between the I.D. one and 10 is very common.
‫Oh, give me all the students between one on 10.
‫It's none of that.
‫It's not really a good query, but you get my point.
‫Sometimes the IDs you do arranges right, and the Rangers are the best thing you can do because ranges
‫are usually consequently.
‫Tucked in together, unless obviously you have gaps and right, if you have these gaps and fragmented
‫index, then that's another question, but it's probably another topic, right?
‫But that's it.
‫That's I think that's a that's basically it.
‫You idea versus a sequential primary key.
‫I think that gives you kind of an idea.
‫Right?
‫Just understand I don't have an answer just like, Oh, you always use this, always use that.
‫Play with it.
‫Try.
‫Understand.
‫That's the most important important thing.
‫Just understand the only to do anything if you know what's happening.
‫You don't need any of us.
‫You don't need help from anyone because you don't.
‫You know what's going on.
‫If you know what's going on, you don't need help from anyone.
‫You don't even stack overflow.
‫You don't need housing.
‫You don't need anyone because this is house.
‫This is how it works.
‫Once you understand how things work, right, you will follow your own path.
‫You will just find an answer.
‫This is, of course, I'm for my use case.
‫I'm going to use your IDs because, hey, I don't insert a lot and I'm fine.
‫If if we have some random reads, who cares?
‫I have a I have all the rounds in the world, right?
‫So it all depends on you just understand.
‫And that's the most important thing.
‫Hope it helps.
‫Can to see you in the next one.
‫Enjoy the course.
‫Goodbye.