‫In this episode, we're going to discuss all about nulls in database system, if you're interested.
‫Stay tuned.
‫How I always start with the obvious question.
‫What is a null?
‫NULL is it's a very interesting thing because it is a representation of something that is missing.
‫Its the value and skill standard hates when you call null a value because it's not really a value.
‫There is no value there, but it is.
‫And now we do refer, whether you like it or not, we do say no value.
‫Hey, I'm going to set my variable as no hey I'm going to query for all my columns that are not all
‫my rules that I.
‫No, no, you we do say that.
‫Right.
‫But it's a philosophical question.
‫It says essentially that a presentation, whether things exist or not and it's absolutely useful.
‫We we need this third bit that tells us that, hey, this is not one or zero or seven or nineteen ninety
‫one January 3rd or the string.
‫Hello, Ward.
‫This is actually there is nothing stored here.
‫We want something, nothing stored here.
‫But how do you not store something while you're actually you have to store something to tell the database
‫that is there that there isn't anything stored there.
‫That's why we're going to talk about the next topic is no persistent then the persisting of nulls in
‫the database.
‫And that's what I'm going to talk about here, by the way, because it's only with the with regard to
‫the database itself, I'm not going to talk about the programming language aspect of it or we're talking
‫about the databases here, the beautiful database.
‫So when you have a field that is integer 32 bit and you store the value zero in that and.
‫That is going to take 30 to bit slot on Ururoa on desk right next door, a lot of other columns if you
‫have it right in your page, Evangelion own desk.
‫If you store, however, a value of two billion in that column, that also takes 32 bit, no matter
‫how large the value is, is going to always going to take 32 bit.
‫And that's a very, you might say Hosain frickin dollar.
‫Of course we know this.
‫What are you telling us?
‫But I want you to I want I want to emphasize this point, because no matter what value story, you regardless,
‫you going to take 32 precious bits on desk, right?
‫I'm not really saying this because I'm worried about space, because in twenty one, this space is not
‫an issue, right?
‫I'm worried about other things we're going to talk about later.
‫OK, so 32 bit, but now we have 32, but you can store up to two billion, write negative and positive.
‫Depends on the order the integers signed or not.
‫But what if I want to store null that I want the database to say, hey, there isn't anything stored
‫there.
‫So what do we do to store this?
‫The database, actually, if you think about it, cannot use the value minus one to indicate that,
‫hey, this is a null no minus one could be a legit value that you as a developer put it, can use any
‫single value in these 32 bit to indicate that this is not so what it does, it actually adds more structures
‫to the row to indicate that that column can be no.
‫And if it's actually null, it sits that bet to one or zero.
‫So Postgres does a differently SQL Servers, does a differently Oracle does it differently.
‫But I'm going to speak from my Bosco's experience because that's what I that database I'm trying to
‫focus my energy on, although these are oh that abysses I work with daily basis.
‫Postgres have this idea of a bit a no bitmap that adds in front of every arrow in the page.
‫It's essentially I think it starts with one by so eight bits.
‫All right.
‫That tells OK for each, for each bit, each bit represent a column.
‫So if you have eight columns, this, this byte is therefore free for you.
‫So if the first bit is set, that means that the the first column is null.
‫The second one is said this is the second column is set and so on.
‫Right.
‫There's a bitmap.
‫So we added one.
‫But if you have more than eight fields, Postgres start adding an increment of eight bytes.
‫Yikes.
‫I know that's a you might say yikes a lot.
‫So if you have like nine or sixty four or sixty three fields then.
‫Postgres adds another eight bites, which is 60 for better, represent those sixty four columns, essentially,
‫and you get the idea every time you go up 128, it adds another I bite another bite, but it's an incremental
‫of a bite.
‫So we need metadata to represent these calls.
‫And you might say, I will.
‫I'm not going to use those anymore.
‫We're adding storage to so I'm going to set all my field as not now.
‫Hold your horses that think about it a little bit.
‫Think about it.
‫Yeah.
‫How expensive this thing is.
‫Compare what you're trying to do.
‫Yeah.
‫Like take take up to one thousand twenty four columns if you have that.
‫How many.
‫What's the overhead.
‫Seventy two bytes.
‫I didn't pause the video to calculate that at all.
‫Yeah.
‫So you can see that there is a cost associated with having some fields as nullable.
‫Right.
‫But look at the flip side of the equation.
‫If you actually persist Noles in those columns.
‫What does that mean postscripts or any other database doesn't really need that 32 bit in case of an
‫integer or that much string bytes corrected in case of a string or 64, but in case of doubles, certain
‫flow of numbers, it doesn't need that anymore.
‫So you just saved actually values.
‫So it knows in this case can be better than storing defaults of zero that you think is the equivalent
‫of no.
‫So some people do that.
‫Some say there's nothing stored there.
‫Let's put minus one in that.
‫That is actually Kostia.
‫I don't know if that is award costly, more costly than storing nulls because Nulls will just sit that
‫single bet for that particular column and we don't have to use that space anymore.
‫We just shivved some spaces now that we understood.
‫Now persistance.
‫How the heck?
‫Yeah, we add a little bit here, but it's almost like a fixed cause given the values that is giving
‫us on the back end on the on the actual Rozz are now we understand no persistent.
‫We understand what null is when nulls are actually naughty or bad.
‫And I have a list here that I came up from my research Sunshine.
‫So the Noles semantics are almost always inconsistent.
‫But you think I wrote this somewhere?
‫If you think you understand nulls, that means you really don't understand it because it will always
‫disappoint you, does it?
‫Is it is designed to trick you these about?
‫Because I'm going to give you some examples.
‫So, for example, let's give an example.
‫If you do a select star from Test the table, that the rose that will be counted are pretty much all
‫the rolls widget.
‫With all of that, some of the fields are null or some other fields are not null.
‫However, if you do select count and you provide the column.
‫Then.
‫Based on the equal standard, it is designed to actually ignore nulls for you, which is absolutely
‫freaky.
‫If you if you alternate between select column or select one and select star and select a column, you
‫get different results.
‫You're quoting the same exact workloads, changing the account.
‫You get different results based on their roles, whether the roles have analyzed them or not.
‫So that's a weird behavior that you just need to watch out.
‫But if you if you are aware of it, then I understand how those work.
‫Stop insulting me.
‫I was like, OK, well, I know Rosie, of course, 100 percent.
‫Yes, keep using it, but just.
‫Yeah, just keep that in mind.
‫Another thing about Knowles's something.
‫All of this, if you do, you cannot compare Nel's together.
‫You can do hey, where field T is equal to null because the best on this equals non null is not really
‫a value.
‫So you can say you cannot put it as a function, you cannot compare.
‫So we invented this new thing that's called is null.
‫So if you do, you have to do where T is null and that's going to give you room where T is not essential.
‫And alternatively you can say, hey, where T is not null, where you're going to get all the rows that
‫are that have values in them that have not as initially null in them.
‫However, if you use a completely different semantic, let's say, where t n and you put another secure
‫statement that happened to return null, that actually returns nothing for you, even if there are nulls
‫in T.
‫Yeah.
‫Oh yeah.
‫Yes.
‫So don't rely on nulls being consistent all the time.
‫They are nasty when it comes to.
‫They are not.
‫No, no, no.
‫Very naughty.
‫And obviously even if you do t where t not in null that will give you also nothing doesn't matter.
‫This statement doesn't mean anything.
‫So that's, that's some Godchaux when it comes to n few of the gotchas.
‫Right.
‫Another thing you have to be careful with is when you have queries that have predicate filters based
‫on nulls like this where T is equal null or T as null.
‫To be specific, make sure that the database that you use in actually support indexes with nulls in
‫them.
‫Not all databases support that.
‫Bosco's does.
‫Oracle doesn't.
‫By default, KLA-Tencor doesn't.
‫By default, you can do some sort of manipulation to make no Oracle understand and and trick Oracle
‫to use the index in case of null.
‫I'm assuming, obviously, that the that the the field that you're acquiring has an index on it.
‫But by default, Oracle, if you do Oracle where T is no right.
‫And T has an index, Oracle will not use the index because it doesn't store nulls in the index.
‫You can trick it.
‫However, postscripts since 83, I believe started supporting nulls in the index.
‫Just pay attention to that.
‫Don't take things for granted when you do a query, understand what it does.
‫All right.
‫So let's jump.
‫So we we took the naughty list of nulls.
‫How about we jump into that good and useful things?
‫No, no, no.
‫One of the things that are useful is a hey, hey, there's a birthday call.
‫I don't wish to disclose my birthday.
‫What are you purchases that you bought this one one nineteen hundreds.
‫The epic date, the person's minus one.
‫You can put those random values because I don't know some some someone might be born in nineteen hundred
‫and still alive and then maybe he when he was or she ought to use your system and they can't because
‫you treated nineteen hundreds one one nineteen hundreds as a as an old value.
‫So no you cannot do that.
‫So you want to store and an null and a birth date.
‫Right.
‫Field or a date field in general.
‫Right.
‫You want to stress persistent actual.
‫Not so.
‫Yeah.
‫Another that is like hey.
‫Not that I don't have value.
‫This thing is not applicable.
‫Talk about the normalization.
‫We we certainly recently moved to this area where we started merging different tables into one.
‫And as a result, you're going to get fields that are not applicable to the joint results.
‫Right.
‫Whether there is no value or already, sometimes you started just adding fields and the application
‫have the context of the certain subset of fields, but you just want to merge things together to essentially
‫a fat dateable.
‫Right.
‫And yeah, some fields will be no, some people will be will have no value.
‫And there's no absolutely no value that I can put to represent no value.
‫I have I need Noll's.
‫So we absolutely need nulls.
‫Now let's think about that a little bit.
‫And we talked about this concept of fat tables that the tables that have a la la la la la la la of Gollum's,
‫if you have a table with a lot of columns, think about it.
‫How do how does the rule is represented?
‫Of course we're talking about a row store database's here without without saying.
‫Right.
‫This is within the whole country's overall database.
‫AROW store database.
‫Right.
‫Not Coloma store.
‫It's very important to to draw that distinction so that we can essentially discuss that in details like
‫con oriented stuff is a totally different beast here and can change the semantics a little bit.
‫So if I have a huge table, a lot of columns, the row then becomes larger, becomes fat.
‫Right.
‫And as a result, I'm going to put an asterisk there on on rules that are larger based.
‫That really depends.
‫And as a result, this rule is going to be large and say one kilobyte and on at least one posterous.
‫I'm not sure what other databases are specifically in disclosable representation.
‫Bosco's page, a single page on disk is a K a kilobyte.
‫So if you rows one K, which is very large, obviously that means you can store, what, eight rows
‫list a little bit less with headers and stuff like that.
‫Right.
‫So you can see that only eight rows versus a few if you row is let's say 100, that gives you 80 rows,
‫which is beautiful.
‫Right.
‫Why does that matter?
‫If you can fit more rows into a page that definitely is better than fitting lists rolls into a page
‫because a page when you go read Postgres or any that don't read Rowby at all, it's not byte address
‫like Hyram, right.
‫We read in Block and specifically in a page.
‫Hey, go and read that page when you say, hey, give me that page.
‫Right, it will go and fetch that entire page and it will pay the price of eyehole to the operating
‫system to do that.
‫And if you get back, that's thinking page.
‫Obviously it is eight K, but the logical rows inside that page is only eight.
‫Right.
‫Then if you're doing like a range query, then you have to turn around and fritschi the second page
‫because the rest of the rows of you need is actually in the second page.
‫So you get a D do more I o OK, essentially.
‫So now we talked about the smaller the rows, the, the more rows you can cram into a page and with
‫a single read io you get a lot of rows and that's pretty, that's beautiful.
‫So now I said what are you talking about here.
‫We're talking about.
‫Yeah.
‫Yeah I understand that now.
‫OK, m more rows are better, but what is now no good has to do with them.
‫Well we talked about that a lot of columns caused larger rows, but if most of those columns are nulls,
‫you essentially can save space.
‫And I don't really care about storage in the I don't care about their own disks.
‫We're not talking about that.
‫We're talking about how much role can I fit in a page.
‫And if nulls if you know, these fields are supposed to be null, don't store zero or some other value
‫in them to represent null or D some default value that you don't really care about and you're not querying
‫against.
‫Right.
‫If you don't care, don't don't just store nulls.
‫And as a result you get thinner table with that, you get thinner.
‫Rose, smaller, rose.
‫Despite you having a lot of calm, then that's absolutely fine and that yields a very great performance
‫because now a single page all of a sudden gives you all these rows tucked in.
‫And why?
‫Because most of them are nulls.
‫And yeah, Postgres and other databases are very efficient in storing nulls.
‫They don't they don't require if it's an integer, they don't require that 30 to bet integer at all
‫these columns.
‫Let's say if you have five hundred columns and all of them for simplicity, say all of them on integer,
‫how much do you save?
‫That's a lot, right.
‫And yeah, there is a there is a cost associated with that, but it's a fixed cost is the fixed cost
‫of maintaining nulls compared to the saving 32 bit times.
‫Five hundred bits.
‫What if most of all of them or most of them are null.
‫You are essentially just save that amount of bits essentially.
‫Right.
‫And I always talk about finally talk about indexing and the beauty of this here if you are not.
‫Going to query that table based on that column, based on these null values you don't care about, hey,
‫give me all this fair value, Rose, where this is equal.
‫No, that's the best case scenario.
‫Then what you can do is create the index.
‫A partial index says, hey, I want this index and don't index or not just to say, hey, where, where?
‫Create index where, where?
‫This is not.
‫No.
‫So this will be created in a very tucked in and then tight space without all this garbage nulls, without
‫the cost of maintaining.
‫So, yeah, even let's take that even more.
‫Even if you do have some queries that use nulls as a predicate filter and you see that the query like,
‫I don't know, admin is doing something, maybe who cares?
‫It's a it's a stinking admin.
‫We don't care about admins.
‫We got about users.
‫If the admin can wait an extra few seconds, big deal.
‫They love them when we're not we're not improving the admin experience.
‫We're improving the user experience.
‫Let the admin suffer.
‫It's OK.
‫You have to be pragmatic.
‫Read the pragmatic programmer book rate that drug book programatic programmer book.
‫It's a very good book.
‫Be pragmatic when you make decisions.
‫Don't just say I want to make the best of the best of the best.
‫Everything should be best.
‫Now you've got to cut corners.
‫You've got a what's the other word you've got to there is there is a this American I'm trying to remember
‫someone at work told me I can't remember.
‫Yes, you got to trade horses.
‫And I took me a while to understand.
‫So they had to explain it to me and all.
‫I guess that's it for me today.
‫I'm going to see you in the next one.
‫Do you use nulls on your tables and have you learned anything from this video?
‫And again, if you see anything that I missed probably or a mistake, I would love for you to correct
‫me because we are all here learning and we're going to evolve essentially our guys.
‫That's it for me today.
‫I'm going to see you in the next one.
‫This has been the back end and junior show with your host to say goodbye.