1
00:00:00,060 --> 00:00:05,130
‫In this episode, we're going to discuss all about nulls in database system, if you're interested.

2
00:00:05,160 --> 00:00:06,640
‫Stay tuned.

3
00:00:06,660 --> 00:00:08,340
‫How I always start with the obvious question.

4
00:00:08,340 --> 00:00:09,780
‫What is a null?

5
00:00:10,260 --> 00:00:18,560
‫NULL is it's a very interesting thing because it is a representation of something that is missing.

6
00:00:19,020 --> 00:00:26,510
‫Its the value and skill standard hates when you call null a value because it's not really a value.

7
00:00:26,520 --> 00:00:30,390
‫There is no value there, but it is.

8
00:00:30,390 --> 00:00:34,290
‫And now we do refer, whether you like it or not, we do say no value.

9
00:00:34,320 --> 00:00:40,110
‫Hey, I'm going to set my variable as no hey I'm going to query for all my columns that are not all

10
00:00:40,110 --> 00:00:40,760
‫my rules that I.

11
00:00:40,830 --> 00:00:43,020
‫No, no, you we do say that.

12
00:00:43,020 --> 00:00:43,340
‫Right.

13
00:00:43,650 --> 00:00:46,080
‫But it's a philosophical question.

14
00:00:46,620 --> 00:00:51,390
‫It says essentially that a presentation, whether things exist or not and it's absolutely useful.

15
00:00:51,390 --> 00:01:02,650
‫We we need this third bit that tells us that, hey, this is not one or zero or seven or nineteen ninety

16
00:01:02,670 --> 00:01:07,470
‫one January 3rd or the string.

17
00:01:07,470 --> 00:01:08,100
‫Hello, Ward.

18
00:01:08,100 --> 00:01:10,830
‫This is actually there is nothing stored here.

19
00:01:11,190 --> 00:01:13,500
‫We want something, nothing stored here.

20
00:01:13,500 --> 00:01:21,030
‫But how do you not store something while you're actually you have to store something to tell the database

21
00:01:21,030 --> 00:01:24,180
‫that is there that there isn't anything stored there.

22
00:01:25,080 --> 00:01:33,060
‫That's why we're going to talk about the next topic is no persistent then the persisting of nulls in

23
00:01:33,060 --> 00:01:33,900
‫the database.

24
00:01:33,900 --> 00:01:37,470
‫And that's what I'm going to talk about here, by the way, because it's only with the with regard to

25
00:01:37,470 --> 00:01:43,830
‫the database itself, I'm not going to talk about the programming language aspect of it or we're talking

26
00:01:43,830 --> 00:01:47,310
‫about the databases here, the beautiful database.

27
00:01:47,850 --> 00:01:59,100
‫So when you have a field that is integer 32 bit and you store the value zero in that and.

28
00:02:00,260 --> 00:02:12,050
‫That is going to take 30 to bit slot on Ururoa on desk right next door, a lot of other columns if you

29
00:02:12,050 --> 00:02:16,550
‫have it right in your page, Evangelion own desk.

30
00:02:17,330 --> 00:02:28,850
‫If you store, however, a value of two billion in that column, that also takes 32 bit, no matter

31
00:02:28,850 --> 00:02:32,990
‫how large the value is, is going to always going to take 32 bit.

32
00:02:32,990 --> 00:02:36,440
‫And that's a very, you might say Hosain frickin dollar.

33
00:02:36,470 --> 00:02:37,450
‫Of course we know this.

34
00:02:37,460 --> 00:02:38,360
‫What are you telling us?

35
00:02:38,960 --> 00:02:46,760
‫But I want you to I want I want to emphasize this point, because no matter what value story, you regardless,

36
00:02:46,760 --> 00:02:54,460
‫you going to take 32 precious bits on desk, right?

37
00:02:55,440 --> 00:03:01,710
‫I'm not really saying this because I'm worried about space, because in twenty one, this space is not

38
00:03:01,710 --> 00:03:02,990
‫an issue, right?

39
00:03:03,240 --> 00:03:07,310
‫I'm worried about other things we're going to talk about later.

40
00:03:07,630 --> 00:03:16,530
‫OK, so 32 bit, but now we have 32, but you can store up to two billion, write negative and positive.

41
00:03:16,530 --> 00:03:18,910
‫Depends on the order the integers signed or not.

42
00:03:19,530 --> 00:03:25,590
‫But what if I want to store null that I want the database to say, hey, there isn't anything stored

43
00:03:25,590 --> 00:03:25,840
‫there.

44
00:03:25,860 --> 00:03:28,640
‫So what do we do to store this?

45
00:03:28,950 --> 00:03:35,640
‫The database, actually, if you think about it, cannot use the value minus one to indicate that,

46
00:03:35,640 --> 00:03:41,640
‫hey, this is a null no minus one could be a legit value that you as a developer put it, can use any

47
00:03:41,640 --> 00:03:50,730
‫single value in these 32 bit to indicate that this is not so what it does, it actually adds more structures

48
00:03:51,150 --> 00:03:56,040
‫to the row to indicate that that column can be no.

49
00:03:56,280 --> 00:03:59,830
‫And if it's actually null, it sits that bet to one or zero.

50
00:03:59,850 --> 00:04:04,500
‫So Postgres does a differently SQL Servers, does a differently Oracle does it differently.

51
00:04:05,280 --> 00:04:10,020
‫But I'm going to speak from my Bosco's experience because that's what I that database I'm trying to

52
00:04:10,020 --> 00:04:15,390
‫focus my energy on, although these are oh that abysses I work with daily basis.

53
00:04:15,390 --> 00:04:23,830
‫Postgres have this idea of a bit a no bitmap that adds in front of every arrow in the page.

54
00:04:24,150 --> 00:04:27,900
‫It's essentially I think it starts with one by so eight bits.

55
00:04:28,140 --> 00:04:28,460
‫All right.

56
00:04:28,530 --> 00:04:33,240
‫That tells OK for each, for each bit, each bit represent a column.

57
00:04:33,270 --> 00:04:37,470
‫So if you have eight columns, this, this byte is therefore free for you.

58
00:04:37,950 --> 00:04:42,600
‫So if the first bit is set, that means that the the first column is null.

59
00:04:42,600 --> 00:04:46,740
‫The second one is said this is the second column is set and so on.

60
00:04:46,810 --> 00:04:46,950
‫Right.

61
00:04:47,170 --> 00:04:48,450
‫There's a bitmap.

62
00:04:48,450 --> 00:04:49,830
‫So we added one.

63
00:04:49,830 --> 00:04:57,510
‫But if you have more than eight fields, Postgres start adding an increment of eight bytes.

64
00:04:58,020 --> 00:04:58,650
‫Yikes.

65
00:04:58,770 --> 00:05:02,000
‫I know that's a you might say yikes a lot.

66
00:05:02,250 --> 00:05:08,280
‫So if you have like nine or sixty four or sixty three fields then.

67
00:05:10,000 --> 00:05:16,360
‫Postgres adds another eight bites, which is 60 for better, represent those sixty four columns, essentially,

68
00:05:16,720 --> 00:05:22,840
‫and you get the idea every time you go up 128, it adds another I bite another bite, but it's an incremental

69
00:05:22,840 --> 00:05:23,310
‫of a bite.

70
00:05:23,320 --> 00:05:28,570
‫So we need metadata to represent these calls.

71
00:05:28,570 --> 00:05:29,910
‫And you might say, I will.

72
00:05:29,910 --> 00:05:31,530
‫I'm not going to use those anymore.

73
00:05:31,780 --> 00:05:37,000
‫We're adding storage to so I'm going to set all my field as not now.

74
00:05:38,320 --> 00:05:42,040
‫Hold your horses that think about it a little bit.

75
00:05:42,050 --> 00:05:42,520
‫Think about it.

76
00:05:42,820 --> 00:05:43,420
‫Yeah.

77
00:05:43,870 --> 00:05:46,750
‫How expensive this thing is.

78
00:05:46,750 --> 00:05:48,190
‫Compare what you're trying to do.

79
00:05:48,650 --> 00:05:49,120
‫Yeah.

80
00:05:49,420 --> 00:05:54,160
‫Like take take up to one thousand twenty four columns if you have that.

81
00:05:54,280 --> 00:05:54,880
‫How many.

82
00:05:55,120 --> 00:05:56,170
‫What's the overhead.

83
00:05:56,530 --> 00:05:58,180
‫Seventy two bytes.

84
00:05:58,250 --> 00:06:00,940
‫I didn't pause the video to calculate that at all.

85
00:06:01,340 --> 00:06:01,690
‫Yeah.

86
00:06:01,870 --> 00:06:07,180
‫So you can see that there is a cost associated with having some fields as nullable.

87
00:06:07,180 --> 00:06:07,570
‫Right.

88
00:06:08,410 --> 00:06:11,770
‫But look at the flip side of the equation.

89
00:06:12,310 --> 00:06:16,870
‫If you actually persist Noles in those columns.

90
00:06:18,090 --> 00:06:26,760
‫What does that mean postscripts or any other database doesn't really need that 32 bit in case of an

91
00:06:26,760 --> 00:06:34,350
‫integer or that much string bytes corrected in case of a string or 64, but in case of doubles, certain

92
00:06:34,350 --> 00:06:36,750
‫flow of numbers, it doesn't need that anymore.

93
00:06:36,760 --> 00:06:39,450
‫So you just saved actually values.

94
00:06:39,690 --> 00:06:49,290
‫So it knows in this case can be better than storing defaults of zero that you think is the equivalent

95
00:06:49,290 --> 00:06:49,740
‫of no.

96
00:06:49,770 --> 00:06:50,820
‫So some people do that.

97
00:06:50,820 --> 00:06:53,190
‫Some say there's nothing stored there.

98
00:06:53,190 --> 00:06:54,420
‫Let's put minus one in that.

99
00:06:55,320 --> 00:06:58,610
‫That is actually Kostia.

100
00:06:58,650 --> 00:07:06,590
‫I don't know if that is award costly, more costly than storing nulls because Nulls will just sit that

101
00:07:06,600 --> 00:07:11,790
‫single bet for that particular column and we don't have to use that space anymore.

102
00:07:11,800 --> 00:07:16,620
‫We just shivved some spaces now that we understood.

103
00:07:16,900 --> 00:07:18,090
‫Now persistance.

104
00:07:18,270 --> 00:07:19,050
‫How the heck?

105
00:07:19,080 --> 00:07:24,210
‫Yeah, we add a little bit here, but it's almost like a fixed cause given the values that is giving

106
00:07:24,210 --> 00:07:29,760
‫us on the back end on the on the actual Rozz are now we understand no persistent.

107
00:07:30,120 --> 00:07:36,300
‫We understand what null is when nulls are actually naughty or bad.

108
00:07:36,330 --> 00:07:40,220
‫And I have a list here that I came up from my research Sunshine.

109
00:07:40,290 --> 00:07:45,750
‫So the Noles semantics are almost always inconsistent.

110
00:07:46,110 --> 00:07:48,710
‫But you think I wrote this somewhere?

111
00:07:49,050 --> 00:07:55,140
‫If you think you understand nulls, that means you really don't understand it because it will always

112
00:07:55,140 --> 00:07:56,580
‫disappoint you, does it?

113
00:07:56,580 --> 00:07:59,040
‫Is it is designed to trick you these about?

114
00:07:59,350 --> 00:08:00,720
‫Because I'm going to give you some examples.

115
00:08:00,720 --> 00:08:02,040
‫So, for example, let's give an example.

116
00:08:02,220 --> 00:08:10,650
‫If you do a select star from Test the table, that the rose that will be counted are pretty much all

117
00:08:10,650 --> 00:08:11,400
‫the rolls widget.

118
00:08:11,430 --> 00:08:14,760
‫With all of that, some of the fields are null or some other fields are not null.

119
00:08:15,240 --> 00:08:20,970
‫However, if you do select count and you provide the column.

120
00:08:22,060 --> 00:08:22,630
‫Then.

121
00:08:23,910 --> 00:08:32,730
‫Based on the equal standard, it is designed to actually ignore nulls for you, which is absolutely

122
00:08:33,120 --> 00:08:33,910
‫freaky.

123
00:08:33,910 --> 00:08:41,370
‫If you if you alternate between select column or select one and select star and select a column, you

124
00:08:41,370 --> 00:08:42,420
‫get different results.

125
00:08:42,570 --> 00:08:45,660
‫You're quoting the same exact workloads, changing the account.

126
00:08:45,660 --> 00:08:50,530
‫You get different results based on their roles, whether the roles have analyzed them or not.

127
00:08:51,000 --> 00:08:54,500
‫So that's a weird behavior that you just need to watch out.

128
00:08:54,510 --> 00:08:58,530
‫But if you if you are aware of it, then I understand how those work.

129
00:08:58,950 --> 00:09:00,060
‫Stop insulting me.

130
00:09:00,220 --> 00:09:05,910
‫I was like, OK, well, I know Rosie, of course, 100 percent.

131
00:09:05,940 --> 00:09:07,320
‫Yes, keep using it, but just.

132
00:09:07,620 --> 00:09:08,850
‫Yeah, just keep that in mind.

133
00:09:08,880 --> 00:09:10,990
‫Another thing about Knowles's something.

134
00:09:10,990 --> 00:09:13,830
‫All of this, if you do, you cannot compare Nel's together.

135
00:09:13,830 --> 00:09:22,080
‫You can do hey, where field T is equal to null because the best on this equals non null is not really

136
00:09:22,080 --> 00:09:22,710
‫a value.

137
00:09:22,710 --> 00:09:26,270
‫So you can say you cannot put it as a function, you cannot compare.

138
00:09:26,640 --> 00:09:29,560
‫So we invented this new thing that's called is null.

139
00:09:29,670 --> 00:09:36,210
‫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.

140
00:09:36,750 --> 00:09:44,850
‫And alternatively you can say, hey, where T is not null, where you're going to get all the rows that

141
00:09:44,850 --> 00:09:49,870
‫are that have values in them that have not as initially null in them.

142
00:09:49,890 --> 00:09:56,850
‫However, if you use a completely different semantic, let's say, where t n and you put another secure

143
00:09:56,850 --> 00:10:02,880
‫statement that happened to return null, that actually returns nothing for you, even if there are nulls

144
00:10:02,880 --> 00:10:03,470
‫in T.

145
00:10:04,110 --> 00:10:04,560
‫Yeah.

146
00:10:04,680 --> 00:10:05,310
‫Oh yeah.

147
00:10:05,610 --> 00:10:06,170
‫Oh yeah.

148
00:10:06,420 --> 00:10:06,840
‫Yes.

149
00:10:06,840 --> 00:10:11,040
‫So don't rely on nulls being consistent all the time.

150
00:10:11,040 --> 00:10:12,430
‫They are nasty when it comes to.

151
00:10:12,450 --> 00:10:13,330
‫They are not.

152
00:10:13,740 --> 00:10:15,310
‫No, no, no.

153
00:10:16,200 --> 00:10:17,100
‫Very naughty.

154
00:10:18,600 --> 00:10:25,880
‫And obviously even if you do t where t not in null that will give you also nothing doesn't matter.

155
00:10:25,890 --> 00:10:27,420
‫This statement doesn't mean anything.

156
00:10:28,110 --> 00:10:31,890
‫So that's, that's some Godchaux when it comes to n few of the gotchas.

157
00:10:31,890 --> 00:10:32,190
‫Right.

158
00:10:32,430 --> 00:10:41,520
‫Another thing you have to be careful with is when you have queries that have predicate filters based

159
00:10:41,520 --> 00:10:45,420
‫on nulls like this where T is equal null or T as null.

160
00:10:45,630 --> 00:10:54,540
‫To be specific, make sure that the database that you use in actually support indexes with nulls in

161
00:10:54,540 --> 00:10:54,810
‫them.

162
00:10:55,170 --> 00:10:56,820
‫Not all databases support that.

163
00:10:57,120 --> 00:10:58,050
‫Bosco's does.

164
00:10:58,080 --> 00:11:00,090
‫Oracle doesn't.

165
00:11:00,090 --> 00:11:02,100
‫By default, KLA-Tencor doesn't.

166
00:11:02,100 --> 00:11:12,870
‫By default, you can do some sort of manipulation to make no Oracle understand and and trick Oracle

167
00:11:12,870 --> 00:11:14,670
‫to use the index in case of null.

168
00:11:14,820 --> 00:11:20,910
‫I'm assuming, obviously, that the that the the field that you're acquiring has an index on it.

169
00:11:21,210 --> 00:11:25,950
‫But by default, Oracle, if you do Oracle where T is no right.

170
00:11:25,950 --> 00:11:34,050
‫And T has an index, Oracle will not use the index because it doesn't store nulls in the index.

171
00:11:34,500 --> 00:11:35,520
‫You can trick it.

172
00:11:35,520 --> 00:11:40,890
‫However, postscripts since 83, I believe started supporting nulls in the index.

173
00:11:40,890 --> 00:11:42,080
‫Just pay attention to that.

174
00:11:42,720 --> 00:11:47,100
‫Don't take things for granted when you do a query, understand what it does.

175
00:11:47,340 --> 00:11:47,600
‫All right.

176
00:11:47,790 --> 00:11:48,390
‫So let's jump.

177
00:11:48,570 --> 00:11:51,090
‫So we we took the naughty list of nulls.

178
00:11:51,330 --> 00:11:54,450
‫How about we jump into that good and useful things?

179
00:11:54,460 --> 00:11:55,210
‫No, no, no.

180
00:11:55,220 --> 00:11:59,430
‫One of the things that are useful is a hey, hey, there's a birthday call.

181
00:11:59,730 --> 00:12:02,330
‫I don't wish to disclose my birthday.

182
00:12:02,370 --> 00:12:06,990
‫What are you purchases that you bought this one one nineteen hundreds.

183
00:12:07,770 --> 00:12:10,290
‫The epic date, the person's minus one.

184
00:12:10,710 --> 00:12:16,500
‫You can put those random values because I don't know some some someone might be born in nineteen hundred

185
00:12:16,500 --> 00:12:22,440
‫and still alive and then maybe he when he was or she ought to use your system and they can't because

186
00:12:22,680 --> 00:12:28,620
‫you treated nineteen hundreds one one nineteen hundreds as a as an old value.

187
00:12:29,160 --> 00:12:30,360
‫So no you cannot do that.

188
00:12:30,480 --> 00:12:33,990
‫So you want to store and an null and a birth date.

189
00:12:34,260 --> 00:12:34,620
‫Right.

190
00:12:35,220 --> 00:12:37,130
‫Field or a date field in general.

191
00:12:37,140 --> 00:12:37,490
‫Right.

192
00:12:38,250 --> 00:12:40,170
‫You want to stress persistent actual.

193
00:12:40,170 --> 00:12:40,440
‫Not so.

194
00:12:40,440 --> 00:12:40,650
‫Yeah.

195
00:12:41,010 --> 00:12:42,210
‫Another that is like hey.

196
00:12:44,480 --> 00:12:45,800
‫Not that I don't have value.

197
00:12:45,890 --> 00:12:47,360
‫This thing is not applicable.

198
00:12:48,110 --> 00:12:49,760
‫Talk about the normalization.

199
00:12:50,030 --> 00:12:58,420
‫We we certainly recently moved to this area where we started merging different tables into one.

200
00:12:58,970 --> 00:13:05,980
‫And as a result, you're going to get fields that are not applicable to the joint results.

201
00:13:06,000 --> 00:13:06,310
‫Right.

202
00:13:06,560 --> 00:13:13,100
‫Whether there is no value or already, sometimes you started just adding fields and the application

203
00:13:13,550 --> 00:13:21,020
‫have the context of the certain subset of fields, but you just want to merge things together to essentially

204
00:13:21,020 --> 00:13:22,010
‫a fat dateable.

205
00:13:22,010 --> 00:13:22,330
‫Right.

206
00:13:23,300 --> 00:13:28,730
‫And yeah, some fields will be no, some people will be will have no value.

207
00:13:28,730 --> 00:13:32,960
‫And there's no absolutely no value that I can put to represent no value.

208
00:13:32,960 --> 00:13:34,130
‫I have I need Noll's.

209
00:13:34,160 --> 00:13:36,620
‫So we absolutely need nulls.

210
00:13:36,630 --> 00:13:38,580
‫Now let's think about that a little bit.

211
00:13:38,600 --> 00:13:46,130
‫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,

212
00:13:46,490 --> 00:13:49,160
‫if you have a table with a lot of columns, think about it.

213
00:13:49,310 --> 00:13:53,570
‫How do how does the rule is represented?

214
00:13:54,380 --> 00:13:59,690
‫Of course we're talking about a row store database's here without without saying.

215
00:13:59,690 --> 00:13:59,990
‫Right.

216
00:14:00,320 --> 00:14:02,600
‫This is within the whole country's overall database.

217
00:14:03,200 --> 00:14:05,030
‫AROW store database.

218
00:14:05,030 --> 00:14:05,240
‫Right.

219
00:14:05,240 --> 00:14:06,290
‫Not Coloma store.

220
00:14:07,490 --> 00:14:17,030
‫It's very important to to draw that distinction so that we can essentially discuss that in details like

221
00:14:17,030 --> 00:14:23,990
‫con oriented stuff is a totally different beast here and can change the semantics a little bit.

222
00:14:25,160 --> 00:14:35,770
‫So if I have a huge table, a lot of columns, the row then becomes larger, becomes fat.

223
00:14:35,780 --> 00:14:36,230
‫Right.

224
00:14:36,530 --> 00:14:44,500
‫And as a result, I'm going to put an asterisk there on on rules that are larger based.

225
00:14:44,510 --> 00:14:45,460
‫That really depends.

226
00:14:45,740 --> 00:14:53,210
‫And as a result, this rule is going to be large and say one kilobyte and on at least one posterous.

227
00:14:53,450 --> 00:14:57,380
‫I'm not sure what other databases are specifically in disclosable representation.

228
00:14:57,710 --> 00:15:03,140
‫Bosco's page, a single page on disk is a K a kilobyte.

229
00:15:03,140 --> 00:15:08,840
‫So if you rows one K, which is very large, obviously that means you can store, what, eight rows

230
00:15:09,020 --> 00:15:11,240
‫list a little bit less with headers and stuff like that.

231
00:15:11,240 --> 00:15:11,470
‫Right.

232
00:15:11,930 --> 00:15:21,530
‫So you can see that only eight rows versus a few if you row is let's say 100, that gives you 80 rows,

233
00:15:21,800 --> 00:15:22,910
‫which is beautiful.

234
00:15:22,910 --> 00:15:23,230
‫Right.

235
00:15:24,290 --> 00:15:25,280
‫Why does that matter?

236
00:15:25,520 --> 00:15:33,980
‫If you can fit more rows into a page that definitely is better than fitting lists rolls into a page

237
00:15:33,980 --> 00:15:42,290
‫because a page when you go read Postgres or any that don't read Rowby at all, it's not byte address

238
00:15:42,290 --> 00:15:43,520
‫like Hyram, right.

239
00:15:43,730 --> 00:15:46,940
‫We read in Block and specifically in a page.

240
00:15:47,240 --> 00:15:51,290
‫Hey, go and read that page when you say, hey, give me that page.

241
00:15:51,320 --> 00:15:59,390
‫Right, it will go and fetch that entire page and it will pay the price of eyehole to the operating

242
00:15:59,390 --> 00:16:00,740
‫system to do that.

243
00:16:01,070 --> 00:16:03,920
‫And if you get back, that's thinking page.

244
00:16:04,790 --> 00:16:13,310
‫Obviously it is eight K, but the logical rows inside that page is only eight.

245
00:16:13,940 --> 00:16:14,300
‫Right.

246
00:16:14,690 --> 00:16:20,930
‫Then if you're doing like a range query, then you have to turn around and fritschi the second page

247
00:16:20,930 --> 00:16:23,500
‫because the rest of the rows of you need is actually in the second page.

248
00:16:23,930 --> 00:16:28,290
‫So you get a D do more I o OK, essentially.

249
00:16:28,520 --> 00:16:37,580
‫So now we talked about the smaller the rows, the, the more rows you can cram into a page and with

250
00:16:37,580 --> 00:16:44,040
‫a single read io you get a lot of rows and that's pretty, that's beautiful.

251
00:16:44,060 --> 00:16:45,770
‫So now I said what are you talking about here.

252
00:16:45,770 --> 00:16:46,380
‫We're talking about.

253
00:16:46,610 --> 00:16:46,850
‫Yeah.

254
00:16:47,000 --> 00:16:48,340
‫Yeah I understand that now.

255
00:16:48,350 --> 00:16:52,690
‫OK, m more rows are better, but what is now no good has to do with them.

256
00:16:52,840 --> 00:17:02,510
‫Well we talked about that a lot of columns caused larger rows, but if most of those columns are nulls,

257
00:17:04,550 --> 00:17:08,270
‫you essentially can save space.

258
00:17:08,660 --> 00:17:12,700
‫And I don't really care about storage in the I don't care about their own disks.

259
00:17:12,830 --> 00:17:13,790
‫We're not talking about that.

260
00:17:13,970 --> 00:17:17,150
‫We're talking about how much role can I fit in a page.

261
00:17:17,960 --> 00:17:26,060
‫And if nulls if you know, these fields are supposed to be null, don't store zero or some other value

262
00:17:26,060 --> 00:17:32,660
‫in them to represent null or D some default value that you don't really care about and you're not querying

263
00:17:32,660 --> 00:17:33,140
‫against.

264
00:17:33,140 --> 00:17:33,500
‫Right.

265
00:17:33,990 --> 00:17:37,250
‫If you don't care, don't don't just store nulls.

266
00:17:37,550 --> 00:17:42,860
‫And as a result you get thinner table with that, you get thinner.

267
00:17:43,030 --> 00:17:44,740
‫Rose, smaller, rose.

268
00:17:46,350 --> 00:17:53,610
‫Despite you having a lot of calm, then that's absolutely fine and that yields a very great performance

269
00:17:53,610 --> 00:17:59,100
‫because now a single page all of a sudden gives you all these rows tucked in.

270
00:17:59,100 --> 00:17:59,760
‫And why?

271
00:17:59,760 --> 00:18:01,410
‫Because most of them are nulls.

272
00:18:01,410 --> 00:18:06,600
‫And yeah, Postgres and other databases are very efficient in storing nulls.

273
00:18:06,600 --> 00:18:13,040
‫They don't they don't require if it's an integer, they don't require that 30 to bet integer at all

274
00:18:13,320 --> 00:18:13,980
‫these columns.

275
00:18:14,190 --> 00:18:18,420
‫Let's say if you have five hundred columns and all of them for simplicity, say all of them on integer,

276
00:18:19,170 --> 00:18:20,280
‫how much do you save?

277
00:18:20,520 --> 00:18:22,130
‫That's a lot, right.

278
00:18:22,890 --> 00:18:28,800
‫And yeah, there is a there is a cost associated with that, but it's a fixed cost is the fixed cost

279
00:18:28,800 --> 00:18:33,030
‫of maintaining nulls compared to the saving 32 bit times.

280
00:18:33,030 --> 00:18:34,280
‫Five hundred bits.

281
00:18:34,290 --> 00:18:38,020
‫What if most of all of them or most of them are null.

282
00:18:38,620 --> 00:18:42,480
‫You are essentially just save that amount of bits essentially.

283
00:18:42,480 --> 00:18:42,700
‫Right.

284
00:18:43,260 --> 00:18:51,210
‫And I always talk about finally talk about indexing and the beauty of this here if you are not.

285
00:18:52,570 --> 00:19:02,020
‫Going to query that table based on that column, based on these null values you don't care about, hey,

286
00:19:02,230 --> 00:19:05,370
‫give me all this fair value, Rose, where this is equal.

287
00:19:05,380 --> 00:19:08,860
‫No, that's the best case scenario.

288
00:19:09,520 --> 00:19:14,260
‫Then what you can do is create the index.

289
00:19:14,830 --> 00:19:20,780
‫A partial index says, hey, I want this index and don't index or not just to say, hey, where, where?

290
00:19:21,070 --> 00:19:23,290
‫Create index where, where?

291
00:19:23,290 --> 00:19:23,770
‫This is not.

292
00:19:23,770 --> 00:19:24,040
‫No.

293
00:19:24,400 --> 00:19:34,540
‫So this will be created in a very tucked in and then tight space without all this garbage nulls, without

294
00:19:34,540 --> 00:19:35,600
‫the cost of maintaining.

295
00:19:36,370 --> 00:19:39,790
‫So, yeah, even let's take that even more.

296
00:19:39,790 --> 00:19:47,680
‫Even if you do have some queries that use nulls as a predicate filter and you see that the query like,

297
00:19:47,680 --> 00:19:51,070
‫I don't know, admin is doing something, maybe who cares?

298
00:19:51,250 --> 00:19:52,990
‫It's a it's a stinking admin.

299
00:19:52,990 --> 00:19:54,220
‫We don't care about admins.

300
00:19:54,220 --> 00:19:55,150
‫We got about users.

301
00:19:55,360 --> 00:19:59,200
‫If the admin can wait an extra few seconds, big deal.

302
00:19:59,200 --> 00:20:02,760
‫They love them when we're not we're not improving the admin experience.

303
00:20:03,040 --> 00:20:05,380
‫We're improving the user experience.

304
00:20:05,950 --> 00:20:07,090
‫Let the admin suffer.

305
00:20:07,270 --> 00:20:08,050
‫It's OK.

306
00:20:08,350 --> 00:20:10,000
‫You have to be pragmatic.

307
00:20:10,180 --> 00:20:16,120
‫Read the pragmatic programmer book rate that drug book programatic programmer book.

308
00:20:16,270 --> 00:20:17,140
‫It's a very good book.

309
00:20:17,470 --> 00:20:19,390
‫Be pragmatic when you make decisions.

310
00:20:19,540 --> 00:20:22,420
‫Don't just say I want to make the best of the best of the best.

311
00:20:22,420 --> 00:20:23,380
‫Everything should be best.

312
00:20:23,560 --> 00:20:25,420
‫Now you've got to cut corners.

313
00:20:25,600 --> 00:20:32,470
‫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

314
00:20:33,080 --> 00:20:35,570
‫someone at work told me I can't remember.

315
00:20:35,590 --> 00:20:37,930
‫Yes, you got to trade horses.

316
00:20:39,040 --> 00:20:41,220
‫And I took me a while to understand.

317
00:20:41,230 --> 00:20:43,150
‫So they had to explain it to me and all.

318
00:20:43,240 --> 00:20:45,160
‫I guess that's it for me today.

319
00:20:45,910 --> 00:20:46,870
‫I'm going to see you in the next one.

320
00:20:46,870 --> 00:20:52,450
‫Do you use nulls on your tables and have you learned anything from this video?

321
00:20:52,450 --> 00:21:00,100
‫And again, if you see anything that I missed probably or a mistake, I would love for you to correct

322
00:21:00,100 --> 00:21:05,380
‫me because we are all here learning and we're going to evolve essentially our guys.

323
00:21:05,500 --> 00:21:06,220
‫That's it for me today.

324
00:21:06,220 --> 00:21:08,260
‫I'm going to see you in the next one.

325
00:21:08,380 --> 00:21:11,950
‫This has been the back end and junior show with your host to say goodbye.