1 00:00:00,360 --> 00:00:03,180 Michael: Hello and welcome to Postgres FM, a weekly show about 2 00:00:03,180 --> 00:00:04,080 all things PostgreSQL. 3 00:00:04,400 --> 00:00:06,080 I am Michael, founder of pgMustard. 4 00:00:06,300 --> 00:00:09,180 And this is my co-host Nikolay, founder of Postgres.AI. 5 00:00:09,960 --> 00:00:12,220 Hey Nikolay, what are we talking about today? 6 00:00:12,700 --> 00:00:15,300 Nikolay: Let's talk about something we shouldn't do. 7 00:00:16,980 --> 00:00:19,640 Michael: All the things that we shouldn't do with respect to 8 00:00:19,640 --> 00:00:20,780 Postgres, right? 9 00:00:21,460 --> 00:00:24,340 Nikolay: Of course, we are not talking about alcohol or sugar, 10 00:00:24,340 --> 00:00:24,840 right? 11 00:00:25,240 --> 00:00:28,160 Michael: Neither of which you've ever done on the podcast, yeah. 12 00:00:28,680 --> 00:00:32,680 Nikolay: Well, I was drinking a lot of Red Bull on the podcast and 13 00:00:32,680 --> 00:00:34,300 it has a lot of sugar, so. 14 00:00:34,540 --> 00:00:36,260 But at least it's not alcohol. 15 00:00:36,900 --> 00:00:37,400 Michael: True. 16 00:00:37,460 --> 00:00:42,160 So there's a fairly famous wiki page, which I was actually surprised 17 00:00:42,160 --> 00:00:44,240 is only about 6 years old. 18 00:00:45,060 --> 00:00:48,460 It's probably the wiki page I link to the most in the PostgreSQL 19 00:00:48,880 --> 00:00:49,380 wiki. 20 00:00:49,540 --> 00:00:51,680 And it's called, it's titled, don't do this. 21 00:00:51,680 --> 00:00:56,300 And it's a list of a bunch of things that are largely inadvisable, 22 00:00:56,760 --> 00:01:00,480 but there's also a couple of other, you've written a how-to that 23 00:01:00,480 --> 00:01:04,540 includes a lot of don't do this that aren't on the wiki. 24 00:01:04,860 --> 00:01:08,000 And there was a really good page that you sent me that I've looked 25 00:01:08,000 --> 00:01:13,220 at as well by Yandex who also have some additional don't do this 26 00:01:13,260 --> 00:01:13,760 advice. 27 00:01:14,060 --> 00:01:18,440 So we're going to pick some of our favorites from those and help 28 00:01:18,440 --> 00:01:21,300 explain why not and what you can do instead. 29 00:01:21,900 --> 00:01:22,960 Nikolay: Right, right, right. 30 00:01:22,960 --> 00:01:26,140 So the wiki list is quite large. 31 00:01:26,140 --> 00:01:28,280 It's not huge, but quite large. 32 00:01:29,060 --> 00:01:31,620 And I like almost everything. 33 00:01:32,380 --> 00:01:33,700 Not everything, not everything. 34 00:01:33,700 --> 00:01:36,560 For example, between, I use sometimes between, you just need 35 00:01:36,560 --> 00:01:37,940 to understand how it works. 36 00:01:38,080 --> 00:01:44,660 So it's like including both sides of the range, both boundaries, 37 00:01:44,800 --> 00:01:45,300 right? 38 00:01:45,860 --> 00:01:49,200 I don't see anything wrong if you use it with an integer, for 39 00:01:49,200 --> 00:01:49,700 example. 40 00:01:49,840 --> 00:01:51,720 Michael: But that's what they say, isn't it? 41 00:01:51,900 --> 00:01:54,300 I think that's what the wiki is really good at doing. 42 00:01:54,960 --> 00:02:00,220 Nikolay: Well, if this list is supposed to be simple to memorize, 43 00:02:00,540 --> 00:02:07,360 and if something is included, like, don't use between, and you 44 00:02:07,360 --> 00:02:09,140 think, okay, I should avoid it. 45 00:02:09,140 --> 00:02:10,900 Maybe actually you should avoid it. 46 00:02:11,420 --> 00:02:18,700 But if we take between, SQL is such a rich language, we have 47 00:02:18,700 --> 00:02:21,000 many things to exclude then as well. 48 00:02:21,500 --> 00:02:25,020 For example, I don't know, like various things with join. 49 00:02:25,680 --> 00:02:32,840 Like, this can bring us to the area, for example, always use 50 00:02:32,840 --> 00:02:37,060 as, don't use aliases without as, right? 51 00:02:37,060 --> 00:02:39,360 And so on, like these things, these kinds of things. 52 00:02:39,760 --> 00:02:41,400 Because it can lead to confusion. 53 00:02:41,600 --> 00:02:45,940 Don't use order by 123, or group by 123. 54 00:02:46,020 --> 00:02:51,240 Actually which is good advice in general, but most of experienced 55 00:02:51,260 --> 00:02:55,220 guys use it anyway because it's just short, right? 56 00:02:56,040 --> 00:02:58,740 And like this list, I actually don't like this. 57 00:02:58,740 --> 00:03:03,680 These kinds of items I don't really like, in my opinion, they 58 00:03:03,680 --> 00:03:04,700 are quite shallow. 59 00:03:06,100 --> 00:03:08,100 Michael: So I'm going to push back. 60 00:03:08,100 --> 00:03:11,200 I think this isn't a list to be memorized and not looked at. 61 00:03:11,200 --> 00:03:15,280 I think one of the benefits of having it written down with some 62 00:03:15,280 --> 00:03:18,440 extra points, like for example, in the title, it says don't use 63 00:03:18,440 --> 00:03:21,780 between in a parenthesis, especially with timestamps. 64 00:03:22,260 --> 00:03:26,820 And then it has why not explains that it includes the boundaries, 65 00:03:27,180 --> 00:03:31,360 whereas you probably don't want that if you're dealing with continuous 66 00:03:31,400 --> 00:03:32,580 ranges like timestamps. 67 00:03:33,280 --> 00:03:35,060 And then it says, when should you? 68 00:03:35,060 --> 00:03:38,260 So it says, between is safe for discrete quantities like integers 69 00:03:38,260 --> 00:03:42,100 or dates, as long as you remember that both ends of the range 70 00:03:42,100 --> 00:03:43,520 are included in the result. 71 00:03:43,520 --> 00:03:45,720 And then they say, but it's a bad habit to get into. 72 00:03:45,720 --> 00:03:48,760 And I actually think that contains all of the subject you were 73 00:03:48,760 --> 00:03:49,820 just talking about. 74 00:03:50,600 --> 00:03:54,720 Nikolay: Well, let's maybe edit wiki and change the title to 75 00:03:54,720 --> 00:03:57,980 don't use between for timestamps to make it clear. 76 00:03:58,000 --> 00:04:00,880 Because for integers I don't see the problem at all. 77 00:04:01,020 --> 00:04:04,660 Michael: So you could say like 0 to 9 and 10 to 19 or you know, 78 00:04:04,660 --> 00:04:05,160 yeah. 79 00:04:08,000 --> 00:04:11,960 Nikolay: With timestamps it's tricky because sometimes if people 80 00:04:11,960 --> 00:04:16,620 don't spend time understanding, like thinking about what exactly 81 00:04:16,720 --> 00:04:22,400 they include, what they don't, and they can make quite bad problems. 82 00:04:23,040 --> 00:04:27,480 They can have troubles even without between, using just simple 83 00:04:27,540 --> 00:04:28,040 comparison. 84 00:04:29,880 --> 00:04:33,700 Also grouping of timestamp, truncated timestamps, like with that 85 00:04:33,700 --> 00:04:36,620 date trunk, can be tricky as well. 86 00:04:36,620 --> 00:04:40,440 You just need to understand that, like, depending on your data, 87 00:04:40,860 --> 00:04:42,360 the edge can be tricky. 88 00:04:42,360 --> 00:04:44,280 Sometimes it includes a lot of rows. 89 00:04:45,040 --> 00:04:49,700 Midnight can be a lot of rows and you need to decide where should 90 00:04:49,700 --> 00:04:53,960 it go to the previous day or next day, right? 91 00:04:54,320 --> 00:04:58,440 Or sometimes it can be 0 rows on the edge because you always 92 00:04:58,440 --> 00:04:59,880 have milliseconds. 93 00:05:01,120 --> 00:05:03,500 Michael: Or it's even more precise than that, right? 94 00:05:04,020 --> 00:05:07,080 Nikolay: Yeah, you need to think about edges and where to include 95 00:05:07,080 --> 00:05:07,580 them. 96 00:05:07,900 --> 00:05:11,320 And of course, if you use between, it's hidden a little bit. 97 00:05:11,320 --> 00:05:12,480 I agree with this. 98 00:05:13,580 --> 00:05:18,140 But in general, don't use between, especially with timestamps. 99 00:05:18,140 --> 00:05:19,500 I cannot agree with this. 100 00:05:19,980 --> 00:05:20,860 Michael: Okay, well, yeah. 101 00:05:20,860 --> 00:05:24,140 I guess it's a wiki for a reason and edits are allowed. 102 00:05:24,720 --> 00:05:25,080 Right. 103 00:05:25,080 --> 00:05:27,440 But should we, should we focus on some of the ones we do agree 104 00:05:27,440 --> 00:05:28,580 more with or like the, 105 00:05:28,580 --> 00:05:29,320 Nikolay: Yeah, sure. 106 00:05:29,640 --> 00:05:30,520 Choose anything. 107 00:05:31,860 --> 00:05:35,900 Michael: Well, I like the not in, like, I think it starts with 108 00:05:35,900 --> 00:05:35,980 the wiki. 109 00:05:35,980 --> 00:05:41,040 The wiki starts with a bunch that I don't see as being that useful, 110 00:05:41,040 --> 00:05:43,240 don't seem to trip that many people up. 111 00:05:43,260 --> 00:05:48,300 But it gets to a few sections in, it gets to SQL constructs and 112 00:05:48,380 --> 00:05:51,360 the first of those is don't use not in. 113 00:05:51,680 --> 00:05:55,280 Nikolay: Yeah I would recommend reading Juk blog post Lucas said 114 00:05:55,680 --> 00:06:02,060 about this like it's very well explained why it's bad and if 115 00:06:02,060 --> 00:06:06,440 you remember I sometimes 1 day I mentioned the very nasty problem 116 00:06:06,440 --> 00:06:11,360 I had with 1 of my startups in the past when we couldn't launch 117 00:06:11,360 --> 00:06:15,360 properly for several months, losing traction and then I found 118 00:06:16,100 --> 00:06:17,640 an issue related to nulls. 119 00:06:17,860 --> 00:06:20,580 This exactly was related to this. 120 00:06:21,220 --> 00:06:25,460 It was in the episode about nulls, 1 of our very first episodes. 121 00:06:26,040 --> 00:06:29,620 And yeah, nulls are in general very tricky, and this is 1 of 122 00:06:29,620 --> 00:06:32,520 the traps where it can catch you. 123 00:06:32,620 --> 00:06:37,340 And you need to study 3-value logic, but for some persons, for 124 00:06:37,340 --> 00:06:40,680 example myself, it will be always a trap. 125 00:06:40,680 --> 00:06:44,800 I mean, I know I can, even with 20 years of experience, I can 126 00:06:44,800 --> 00:06:46,080 be caught there. 127 00:06:46,620 --> 00:06:50,680 That's why, like, you just need to practise avoiding these traps. 128 00:06:50,680 --> 00:06:54,800 So basically if you have not in and you have 1 of the values 129 00:06:54,800 --> 00:07:00,300 inside it now, it will always be unknown, so it will not return 130 00:07:00,300 --> 00:07:01,140 what you expect. 131 00:07:01,640 --> 00:07:04,540 Michael: Yeah, and I think the reason I think this trips 132 00:07:04,540 --> 00:07:10,440 people up the most is that in and exists can be used say interchangeably, 133 00:07:11,680 --> 00:07:14,940 but that's not true for not exists and not in. 134 00:07:16,020 --> 00:07:18,480 And that's confusing like That's 135 00:07:19,300 --> 00:07:19,800 Nikolay: confusing. 136 00:07:20,340 --> 00:07:20,640 Yeah. 137 00:07:20,640 --> 00:07:22,540 Now it's confusing in general. 138 00:07:22,800 --> 00:07:25,220 Three-valued logic is confusing in general. 139 00:07:27,380 --> 00:07:29,680 So now is a big trap itself. 140 00:07:30,140 --> 00:07:34,260 It has just many faces and not in is 1 of these faces. 141 00:07:35,020 --> 00:07:37,760 Michael: Yeah, well, I'll link to the I'll link to the episode 142 00:07:37,760 --> 00:07:42,040 we did on nulls in the show notes for anybody that wasn't wasn't 143 00:07:42,040 --> 00:07:44,100 a listener back then or missed that 1. 144 00:07:44,240 --> 00:07:47,340 That also reminds me on the in the Duk list. 145 00:07:48,000 --> 00:07:50,180 There was there was some schema advice. 146 00:07:50,500 --> 00:07:53,040 So some general don't do this with your design. 147 00:07:53,300 --> 00:07:58,380 And 1 of the things was around, like, constraints on columns 148 00:07:58,380 --> 00:07:59,240 in the first place. 149 00:07:59,240 --> 00:08:04,840 Like, it's the default to let, in Postgres at least, I think 150 00:08:04,840 --> 00:08:07,700 in all databases I've used, or at least the ones I remember, 151 00:08:08,320 --> 00:08:10,840 the default is columns can contain nulls. 152 00:08:10,840 --> 00:08:11,440 Nikolay: Can accept 153 00:08:12,340 --> 00:08:12,840 Michael: nulls. 154 00:08:13,440 --> 00:08:16,000 And it's an interesting point that you make, that maybe that 155 00:08:16,000 --> 00:08:18,980 should be flipped and not null should be the default. 156 00:08:19,060 --> 00:08:21,940 Or at least when we're designing schemas, it could make sense 157 00:08:21,940 --> 00:08:22,960 to do it that way. 158 00:08:22,960 --> 00:08:24,680 Nikolay: Yeah, yeah, maybe. 159 00:08:25,080 --> 00:08:28,880 At least when you design something, it's a good habit to consider 160 00:08:28,940 --> 00:08:30,520 everything as not null. 161 00:08:30,920 --> 00:08:33,840 If needed, the next step is to think about the default value 162 00:08:33,840 --> 00:08:36,600 and only then accept nulls. 163 00:08:36,880 --> 00:08:41,600 Yeah, just reduce the number of cases where nulls can appear. 164 00:08:42,540 --> 00:08:46,240 And that's a great, I would say, style. 165 00:08:46,680 --> 00:08:48,200 This is about style. 166 00:08:48,880 --> 00:08:51,260 Michael: It does have performance implications, that 1 as well, 167 00:08:51,260 --> 00:08:55,940 because if the database can know that there are no nulls in that 168 00:08:56,000 --> 00:08:59,140 column, that can allow certain optimizations as well. 169 00:09:00,060 --> 00:09:01,440 Nikolay: Well, yes and no. 170 00:09:01,820 --> 00:09:03,440 Well, it depends. 171 00:09:03,580 --> 00:09:04,940 Nulls are stored differently. 172 00:09:05,180 --> 00:09:09,660 If you put some default value, it will be stored as is, right? 173 00:09:09,660 --> 00:09:12,280 Unless you're adding a column for an existing table. 174 00:09:12,980 --> 00:09:17,540 In this case, since Postgres 11, we have like a virtual default 175 00:09:17,560 --> 00:09:19,460 and it's stored virtually, not physically. 176 00:09:19,900 --> 00:09:24,260 But for all new rows, a default real value will be really stored. 177 00:09:25,160 --> 00:09:29,440 And nulls are just stored as like a bitmap, right? 178 00:09:31,420 --> 00:09:35,580 And, But I agree in general with indexes and so on, nulls sometimes 179 00:09:36,040 --> 00:09:38,740 present some challenges in terms of performance. 180 00:09:41,180 --> 00:09:44,360 Michael: I think also it can rewrite certain queries if it knows 181 00:09:44,380 --> 00:09:49,300 for sure that a column cannot contain nulls. 182 00:09:49,300 --> 00:09:51,420 If there's like a constraint on it. 183 00:09:51,820 --> 00:09:54,780 If you've written, I guess this is a contrived example, but if 184 00:09:54,780 --> 00:10:01,860 you've written where X is not null, It can just ignore that completely. 185 00:10:02,020 --> 00:10:06,160 It can just ignore certain filters or certain operations. 186 00:10:07,360 --> 00:10:08,100 Nikolay: Good point. 187 00:10:08,460 --> 00:10:11,720 Michael: But yeah, this is not important in the grand scheme 188 00:10:11,720 --> 00:10:12,420 of things. 189 00:10:13,380 --> 00:10:14,860 Nikolay: Just not in is dangerous. 190 00:10:15,040 --> 00:10:15,440 That's it. 191 00:10:15,440 --> 00:10:16,720 Not in is dangerous. 192 00:10:16,720 --> 00:10:21,820 Here we can put it to, I think the wiki page has it as well, 193 00:10:21,820 --> 00:10:22,320 right? 194 00:10:22,800 --> 00:10:24,300 And don't use not in. 195 00:10:25,080 --> 00:10:25,580 Agreed. 196 00:10:26,400 --> 00:10:28,940 With this statement, I agree. 197 00:10:30,240 --> 00:10:32,660 Michael: What do you think of the next one on the wiki which 198 00:10:32,780 --> 00:10:35,340 says don't use uppercase table or column names? 199 00:10:37,060 --> 00:10:39,160 Nikolay: Again, it's a matter of style. 200 00:10:39,520 --> 00:10:40,120 Right here. 201 00:10:40,120 --> 00:10:41,260 It's a matter of style. 202 00:10:41,280 --> 00:10:46,160 So I don't like uppercase in general, not only for identifiers. 203 00:10:46,560 --> 00:10:49,540 I don't like it for keywords like select. 204 00:10:49,640 --> 00:10:50,820 I prefer lowercase. 205 00:10:52,200 --> 00:10:57,720 But of course, if you don't want to be required to use double 206 00:10:57,720 --> 00:11:01,100 quotes, then don't. 207 00:11:02,220 --> 00:11:06,940 Or just if you do it, you can avoid using double quotes, but 208 00:11:06,940 --> 00:11:10,580 in this case you need to always use... 209 00:11:11,980 --> 00:11:16,980 So, basically, if you don't have double quotes, names are like 210 00:11:16,980 --> 00:11:18,760 case-sensitive search. 211 00:11:19,560 --> 00:11:20,820 Michael: They're lowercased, right? 212 00:11:21,380 --> 00:11:24,860 Nikolay: They're internally lowercase, but you can use mixed 213 00:11:24,860 --> 00:11:28,160 case and it will be the same as lowercase, right? 214 00:11:28,500 --> 00:11:33,500 So, no big problem if you just type all table names uppercase 215 00:11:33,940 --> 00:11:37,620 until you start using quotes, double quotes or some of your software 216 00:11:37,660 --> 00:11:39,880 starts using it and then you're in trouble. 217 00:11:39,880 --> 00:11:41,540 So better to lowercase everything. 218 00:11:42,040 --> 00:11:46,480 But sometimes we have migrated systems from other database systems 219 00:11:46,500 --> 00:11:51,760 and in this case we do see table names like in camel style for 220 00:11:51,760 --> 00:11:52,580 example, right? 221 00:11:52,580 --> 00:11:56,880 In this case it's just legacy we need to deal with. 222 00:11:56,920 --> 00:12:01,960 I personally like to lowercase everything because it's like, 223 00:12:02,280 --> 00:12:07,720 like, economical reasons, like less, less actions when you type, 224 00:12:07,720 --> 00:12:07,960 right? 225 00:12:07,960 --> 00:12:08,680 That's it. 226 00:12:08,680 --> 00:12:09,180 Michael: Yeah. 227 00:12:09,720 --> 00:12:13,140 And underscores between words in object names? 228 00:12:13,140 --> 00:12:14,180 Nikolay: Underscore, yeah. 229 00:12:14,340 --> 00:12:16,980 Well, maybe sometimes, yeah. 230 00:12:17,280 --> 00:12:21,480 So like in Python and Shell, snake style, right? 231 00:12:21,980 --> 00:12:23,080 Michael: Snake case, yeah. 232 00:12:23,400 --> 00:12:24,180 Nikolay: Snake case. 233 00:12:24,620 --> 00:12:25,120 Michael: Cool. 234 00:12:25,240 --> 00:12:29,020 So we're into the meat of the, and we've got, we've discussed 235 00:12:29,020 --> 00:12:31,880 the timestamp stuff, and we've got a whole episode on times and 236 00:12:31,880 --> 00:12:34,340 timestamp things so we can link that as well. 237 00:12:34,900 --> 00:12:37,520 Nikolay: In general, I wanted to mention it in the beginning 238 00:12:37,520 --> 00:12:38,300 but I forgot. 239 00:12:38,760 --> 00:12:41,260 This episode is like kind of basic. 240 00:12:41,720 --> 00:12:43,260 We like basic episodes, right? 241 00:12:43,260 --> 00:12:47,240 Because we know many people are not database experts who listen 242 00:12:47,240 --> 00:12:53,200 to us and it's good to not to dive into some very narrow problems 243 00:12:53,200 --> 00:12:59,020 and deep but sometimes just to remind about some basics and it's 244 00:12:59,020 --> 00:13:02,360 good if no new in this episode for you. 245 00:13:02,360 --> 00:13:06,260 In this case, it's also good, but I hope for some folks it will 246 00:13:06,260 --> 00:13:06,860 be useful. 247 00:13:07,240 --> 00:13:11,400 Michael: I do think it's also useful sometimes as people that 248 00:13:11,400 --> 00:13:14,600 have got a lot more experience to remember what it was like being 249 00:13:14,600 --> 00:13:15,220 a beginner. 250 00:13:15,320 --> 00:13:17,800 Chances are you've got people on your team or people you have 251 00:13:17,800 --> 00:13:22,320 to help those new to this stuff and having a few more resources 252 00:13:22,360 --> 00:13:25,440 that you can point people at as to like, why this is a bad idea, 253 00:13:25,440 --> 00:13:28,220 instead of having to explain everything again from first principles, 254 00:13:28,260 --> 00:13:30,360 I find that somewhat helpful. 255 00:13:30,860 --> 00:13:33,140 Hence why I link people to the wiki so often. 256 00:13:33,340 --> 00:13:36,320 But yeah, also in considering should some defaults change? 257 00:13:36,580 --> 00:13:38,000 Can we make the wiki clearer? 258 00:13:38,000 --> 00:13:41,440 Like, if you do know all this stuff already, it'd be cool if 259 00:13:41,440 --> 00:13:45,940 you could help us make these resources clearer or give better 260 00:13:45,940 --> 00:13:46,280 advice. 261 00:13:46,280 --> 00:13:49,400 Is there anything that you advise people never to do in Postgres 262 00:13:49,400 --> 00:13:50,680 that isn't on the wiki yet? 263 00:13:50,680 --> 00:13:51,860 Could you update it? 264 00:13:51,860 --> 00:13:55,840 So I'd love even more advanced people to think about how they 265 00:13:55,840 --> 00:13:58,480 can make things easier for new folks coming along. 266 00:13:58,480 --> 00:13:59,980 Should we make defaults different? 267 00:14:00,800 --> 00:14:03,540 No beginner is going to be able to dive into the Postgres codebase 268 00:14:03,840 --> 00:14:06,960 and argue on the mailing list for making a default different, 269 00:14:06,960 --> 00:14:07,900 I don't think. 270 00:14:08,680 --> 00:14:09,180 Nikolay: Right. 271 00:14:10,120 --> 00:14:14,360 Yeah, many defaults are outdated, you know my opinion about this. 272 00:14:15,080 --> 00:14:16,720 Michael: But this is a bit different, right? 273 00:14:16,780 --> 00:14:20,460 We're often talking about GC settings, when we're talking about 274 00:14:20,460 --> 00:14:21,220 those defaults. 275 00:14:21,220 --> 00:14:23,760 This is more a design goal. 276 00:14:24,620 --> 00:14:25,760 Nikolay: Yeah, here as well. 277 00:14:25,760 --> 00:14:29,940 Well, again, I don't see how a nullable column can, like... 278 00:14:30,300 --> 00:14:35,720 Not null can become a global standard, the global default. 279 00:14:36,420 --> 00:14:40,760 But I can see how in a particular project it can become a code 280 00:14:40,760 --> 00:14:44,480 style or schema design style standard easily. 281 00:14:45,300 --> 00:14:46,860 Michael: Yeah, it's an interesting point. 282 00:14:47,080 --> 00:14:50,520 Obviously, because we have to support backward compatibility, 283 00:14:51,180 --> 00:14:52,860 it's impossible now, I think. 284 00:14:53,600 --> 00:14:55,260 But at the beginning, you could. 285 00:14:55,580 --> 00:14:59,240 Nikolay: Also, I guess, there may be some standard things here. 286 00:14:59,440 --> 00:15:00,420 Anyway, I don't know. 287 00:15:00,420 --> 00:15:03,620 Maybe there is a standard regarding defaults, maybe not. 288 00:15:03,840 --> 00:15:08,300 Let's proceed to some more like bigger problems maybe. 289 00:15:09,320 --> 00:15:10,120 What about... 290 00:15:11,400 --> 00:15:13,120 Let's finish about small problems. 291 00:15:13,140 --> 00:15:14,240 Don't use money. 292 00:15:15,780 --> 00:15:16,720 Don't use money. 293 00:15:17,360 --> 00:15:18,080 Michael: The data type. 294 00:15:18,080 --> 00:15:19,980 I know you find this funny every time. 295 00:15:20,140 --> 00:15:22,440 Nikolay: Well, the wiki says don't use money. 296 00:15:22,440 --> 00:15:24,280 They don't say don't use data type money. 297 00:15:24,280 --> 00:15:25,580 They say don't use money. 298 00:15:26,820 --> 00:15:29,320 Some communists here, I think. 299 00:15:30,100 --> 00:15:32,540 So, yeah, money is a bad data type. 300 00:15:32,900 --> 00:15:34,240 Why is it still there? 301 00:15:35,280 --> 00:15:36,340 Maybe it's standard as well? 302 00:15:36,340 --> 00:15:37,200 I don't think so. 303 00:15:37,200 --> 00:15:38,040 I don't know. 304 00:15:38,240 --> 00:15:39,840 So implementation is terrible. 305 00:15:39,900 --> 00:15:44,360 You will start losing sense if you use it. 306 00:15:44,580 --> 00:15:47,700 Michael: Surrounding's an issue, Partial. 307 00:15:47,880 --> 00:15:50,040 There's quite a lot of pricing these days. 308 00:15:50,380 --> 00:15:55,460 If we're paying for compute and paying for API, like tokens or 309 00:15:55,580 --> 00:15:59,360 access to things, often we're paying fractions of a cent per 310 00:15:59,860 --> 00:16:03,960 image, per minute, per whatever we're paying for. 311 00:16:04,280 --> 00:16:05,540 So fractions of 312 00:16:05,540 --> 00:16:05,930 Nikolay: a cent... 313 00:16:05,930 --> 00:16:09,960 If you format transactions, you will start losing some money, 314 00:16:09,960 --> 00:16:11,180 so don't do it. 315 00:16:11,840 --> 00:16:12,180 Yeah. 316 00:16:12,180 --> 00:16:16,680 Use numeric, but check the performance, obviously. 317 00:16:17,640 --> 00:16:22,400 Michael: Yeah, I've also seen people use integers with like well 318 00:16:26,640 --> 00:16:29,780 Nikolay: okay but what about fractions of cents for example again 319 00:16:29,800 --> 00:16:33,340 Michael: well That's the idea like you can just move the decimal 320 00:16:33,340 --> 00:16:37,060 place like if you store it as an integer and just with like 321 00:16:37,820 --> 00:16:41,720 Nikolay: Well, okay, if you define precision in advance and then 322 00:16:41,720 --> 00:16:44,020 follow this rule, okay, but why not? 323 00:16:45,280 --> 00:16:46,840 Michael: I guess performance reasons. 324 00:16:46,840 --> 00:16:48,240 I don't know, I didn't ask. 325 00:16:48,920 --> 00:16:54,360 Nikolay: Right, right. Okay, good simple right. Don't use money. 326 00:16:55,440 --> 00:16:56,260 What's next? 327 00:16:56,640 --> 00:16:58,680 Let's choose some bigger topic. 328 00:16:59,340 --> 00:17:00,260 What do you think? 329 00:17:00,660 --> 00:17:02,220 Michael: Well, I think the primary key... 330 00:17:02,220 --> 00:17:03,080 Yeah, let's go to... 331 00:17:03,080 --> 00:17:07,320 Let's jump to your list, your how-to guide that we'll link up 332 00:17:07,320 --> 00:17:08,000 as well. 333 00:17:08,160 --> 00:17:08,800 Nikolay: Let's do it. 334 00:17:08,800 --> 00:17:13,360 So yeah, primary key is quite a simple thing, just don't use integer 335 00:17:13,360 --> 00:17:15,040 4 primary keys. 336 00:17:15,200 --> 00:17:19,940 Consider them in the same manner as integer 2 primary keys. 337 00:17:20,600 --> 00:17:25,780 Do it only when you fully understand the consequences and reasons. 338 00:17:25,830 --> 00:17:33,480 I mean, like, integer 4 has a capacity of 2.1 billion rows and 2.1 339 00:17:33,600 --> 00:17:34,460 billion values. 340 00:17:35,020 --> 00:17:37,440 The maximum is roughly 2.1 billion. 341 00:17:38,320 --> 00:17:40,620 And sometimes we reach this. 342 00:17:41,280 --> 00:17:42,540 Yeah, positive, negative. 343 00:17:42,540 --> 00:17:47,520 Well, it's interesting that we can use negative values, but usually 344 00:17:47,520 --> 00:17:50,380 people for surrogate keys, they don't use negative values. 345 00:17:50,380 --> 00:17:53,740 There is an idea, well, like to... 346 00:17:55,080 --> 00:17:58,640 I saw it many times, people say, okay, we are approaching 2.1 347 00:17:58,840 --> 00:18:01,800 billion soon, let's just start using negative values. 348 00:18:01,800 --> 00:18:05,420 Okay, good luck with that in URLs and so on. 349 00:18:05,900 --> 00:18:09,920 It's like, I think it's definitely possible, but the amount of 350 00:18:09,920 --> 00:18:11,400 work is not trivial. 351 00:18:11,460 --> 00:18:15,060 Again, it's better to convert to int8, which is not trivial 352 00:18:15,060 --> 00:18:19,000 if you have a billion rows already or 4000000000 rows, I would 353 00:18:19,000 --> 00:18:22,320 say probably it's a case of emergency already. 354 00:18:23,460 --> 00:18:29,680 But our checkup tool suggests, I think it starts raising big 355 00:18:29,680 --> 00:18:34,320 flags, yellow or even red, after 50% of capacity used. 356 00:18:35,740 --> 00:18:40,480 By the way, it's an interesting problem how in one query to find 357 00:18:40,480 --> 00:18:46,080 all tables checking pg_class and all other system catalogs which 358 00:18:46,080 --> 00:18:50,280 have primary key, surrogate primary key, int4, or it can 359 00:18:50,280 --> 00:18:54,440 be maybe a multi-column primary key, but one of the parts is int4 360 00:18:54,440 --> 00:18:59,980 and then to check the current value in the sequence. 361 00:19:00,820 --> 00:19:01,920 It's not trivial. 362 00:19:02,300 --> 00:19:05,640 Unfortunately, I remember I was trying to solve it with pure 363 00:19:05,640 --> 00:19:11,400 SQL, not possible, I downgraded to PL/pgSQL and have a snippet. 364 00:19:11,540 --> 00:19:15,180 It's an anonymous do block or maybe a function, anyway. 365 00:19:15,720 --> 00:19:19,540 And then in this case, it can scan your schema and then check 366 00:19:19,540 --> 00:19:25,960 values in sequences and report the capacity for each int4 367 00:19:25,960 --> 00:19:27,280 primary key case. 368 00:19:28,380 --> 00:19:31,360 And again, my approach is very simple. 369 00:19:32,220 --> 00:19:36,720 If you use int4 primary key, it's similar to int2 primary 370 00:19:36,720 --> 00:19:37,220 key. 371 00:19:37,900 --> 00:19:40,960 Because the data volumes we deal with usually are already so 372 00:19:40,960 --> 00:19:41,460 big. 373 00:19:42,560 --> 00:19:49,080 So, it makes sense if you do know what column tetris and padding 374 00:19:49,080 --> 00:19:49,580 alignment are. 375 00:19:50,820 --> 00:19:53,100 If you know how the storage is organized. 376 00:19:53,420 --> 00:19:54,020 You always, 377 00:19:54,120 --> 00:19:57,340 Michael: Yeah, you jumped to this before, like, I think you're 378 00:19:57,340 --> 00:19:58,820 right to mention it. 379 00:19:59,040 --> 00:20:03,960 But I think the larger argument is there's almost no cost to, 380 00:20:03,960 --> 00:20:07,660 like, even when there is a cost to using bigint over int, 381 00:20:07,660 --> 00:20:12,900 so even when you have considered column tetris, still the cost 382 00:20:12,900 --> 00:20:15,520 is so small compared to the long-term advantages. 383 00:20:15,520 --> 00:20:16,940 Nikolay: 4 bytes for each row. 384 00:20:17,300 --> 00:20:18,480 Michael: Yeah, it's trivial. 385 00:20:18,540 --> 00:20:20,980 Nikolay: But this is why people usually choose int4. 386 00:20:20,980 --> 00:20:23,320 They say, okay, we will save 4 bytes. 387 00:20:23,320 --> 00:20:27,400 We won't, in this table, we won't reach 2000000000 and we will 388 00:20:27,400 --> 00:20:29,220 be saving 4 bytes for each row. 389 00:20:29,220 --> 00:20:31,560 It's good for us and they choose it. 390 00:20:31,560 --> 00:20:32,200 Michael: What you think is 391 00:20:32,200 --> 00:20:32,424 Nikolay: like, I think most... 392 00:20:32,424 --> 00:20:34,760 I'm talking about a conscious choice. 393 00:20:34,760 --> 00:20:37,580 Michael: I don't, yeah, I think most choice is unconscious. 394 00:20:37,900 --> 00:20:41,260 I think most people are just thinking it's default. 395 00:20:41,680 --> 00:20:46,920 It's in there, like, yeah, maybe an ORM, maybe like a tutorial they're 396 00:20:46,920 --> 00:20:47,420 following. 397 00:20:47,520 --> 00:20:51,400 Maybe like, I want an integer primary key. 398 00:20:51,800 --> 00:20:52,260 Why not 399 00:20:52,260 --> 00:20:53,100 Nikolay: choose the type 400 00:20:53,100 --> 00:20:54,100 Michael: that's called integer? 401 00:20:54,220 --> 00:20:57,380 So I think most of the choices of this is not conscious. 402 00:20:58,300 --> 00:20:59,180 Nikolay: I agree with you. 403 00:20:59,180 --> 00:21:02,420 Yeah, behavior of humans is that they just choose defaults and 404 00:21:02,420 --> 00:21:04,420 go because they don't have time to understand. 405 00:21:04,740 --> 00:21:07,580 But why I'm talking about this so precisely? 406 00:21:08,180 --> 00:21:11,280 Because I saw it many times, people are already approaching 2 407 00:21:11,280 --> 00:21:14,560 billion, they have problems, it's hard to migrate without downtime, 408 00:21:15,060 --> 00:21:15,860 it's possible. 409 00:21:16,260 --> 00:21:20,660 And then we tell them, you know guys, not only do you have a 410 00:21:20,660 --> 00:21:25,160 big problem and you'll be down soon, I mean partially down, not 411 00:21:25,160 --> 00:21:29,680 accepting inserts anymore for this table, but also look at this, 412 00:21:29,920 --> 00:21:31,740 ID and created at. 413 00:21:33,000 --> 00:21:38,300 4 bytes, 8 bytes, so you even don't have any benefits from int4. 414 00:21:38,300 --> 00:21:38,800 4. 415 00:21:39,020 --> 00:21:49,400 It's so emotionally big, understanding that you have four zero bytes 416 00:21:49,400 --> 00:21:50,660 for all rows. 417 00:21:51,340 --> 00:21:53,800 So it's like, why did we do this? 418 00:21:54,020 --> 00:21:55,280 Why did we do this? 419 00:21:55,680 --> 00:21:57,380 I just, this is an emotion. 420 00:21:57,380 --> 00:21:59,440 That's why I'm talking about it so much. 421 00:21:59,440 --> 00:22:01,020 I just, I saw it in many teams. 422 00:22:01,020 --> 00:22:03,220 I'm just sharing experience here. 423 00:22:03,820 --> 00:22:06,900 Michael: Yeah, and you're totally right to mention it, that a 424 00:22:06,900 --> 00:22:10,320 lot of the time due to alignment padding we don't even get the 425 00:22:10,320 --> 00:22:12,880 benefit of the saved 4 bytes. 426 00:22:14,800 --> 00:22:17,920 But what I meant was more, like, so totally right to mention 427 00:22:17,920 --> 00:22:18,280 it. 428 00:22:18,280 --> 00:22:20,180 And I think that will shock people. 429 00:22:20,660 --> 00:22:24,580 But even without the fact that there's sometimes zero benefit, 430 00:22:24,660 --> 00:22:26,340 I think it's still worth it. 431 00:22:26,880 --> 00:22:27,380 Nikolay: Right. 432 00:22:27,560 --> 00:22:30,740 Well, in the end of the day, four bytes for each row, it's not, 433 00:22:30,740 --> 00:22:35,060 it's not a huge saving for a billion rows. 434 00:22:35,140 --> 00:22:36,400 It's just 4 billion. 435 00:22:37,340 --> 00:22:39,880 But it's not, not, not a lot, right. 436 00:22:40,200 --> 00:22:42,080 Noticeable probably, but not a lot. 437 00:22:43,140 --> 00:22:46,560 Michael: Yeah, and even if you add up the fact that that's, it's 438 00:22:46,560 --> 00:22:49,900 probably duplicated in some indexes, like you probably have several 439 00:22:49,900 --> 00:22:54,180 indexes that involve the primary key, like maybe some 440 00:22:54,180 --> 00:22:55,220 multi-column indexes. 441 00:22:55,940 --> 00:23:00,780 Maybe you've worried about memory, like, but yeah, it's 442 00:23:00,780 --> 00:23:01,580 not big. 443 00:23:01,780 --> 00:23:06,340 And the time you will realize that it wasn't worth it is the 444 00:23:06,340 --> 00:23:07,160 one project. 445 00:23:07,200 --> 00:23:11,460 Let's say you have a hundred tables and you've put them all as BigInt 446 00:23:11,580 --> 00:23:14,640 and instead of, or you've used int4 for all of them. 447 00:23:14,640 --> 00:23:18,760 And only one of those tables has to be converted to int8 later. 448 00:23:18,940 --> 00:23:22,020 That project you go through to do that conversion, you're 449 00:23:22,020 --> 00:23:25,160 going to realize we'd have been better off going with int8 450 00:23:25,160 --> 00:23:25,900 for all of them. 451 00:23:25,900 --> 00:23:29,280 You only have to go through one of these projects once to realize 452 00:23:29,640 --> 00:23:31,120 it's just not worth it. 453 00:23:31,120 --> 00:23:32,660 The time investment alone. 454 00:23:32,840 --> 00:23:35,580 Never mind the stress, like, under pressure. 455 00:23:36,220 --> 00:23:39,400 Nikolay: Interesting that the wiki list doesn't have this at 456 00:23:39,400 --> 00:23:43,220 all and it doesn't talk about primary keys at all and it says 457 00:23:43,260 --> 00:23:49,160 don't use serial. But it's not about the number of bytes, it's 458 00:23:49,160 --> 00:23:51,380 about in general serial is not a good thing. 459 00:23:51,380 --> 00:23:55,460 So I mean, I guess bigserial is also a bad thing in the opinion 460 00:23:56,380 --> 00:23:59,240 of people who wrote it. 461 00:23:59,280 --> 00:24:00,960 So interesting, right? 462 00:24:01,080 --> 00:24:02,920 I mean, it's quite common. 463 00:24:03,940 --> 00:24:05,040 We could edit, yeah. 464 00:24:06,300 --> 00:24:08,500 Next, choose anything. 465 00:24:08,800 --> 00:24:09,520 What do you think? 466 00:24:09,520 --> 00:24:12,100 Michael: From your list, we've talked about NULLs a bit already. 467 00:24:12,740 --> 00:24:15,760 I think Transactional DDL and DELETE. 468 00:24:16,020 --> 00:24:17,260 DELETE is a great one. 469 00:24:17,260 --> 00:24:19,900 DELETE a lot of rows with one command. 470 00:24:20,980 --> 00:24:22,500 Nikolay: Massive DELETE is massive. 471 00:24:23,300 --> 00:24:24,380 Yeah, I had incidents. 472 00:24:24,880 --> 00:24:30,060 It was on weaker storage, but it was very painful incidents when 473 00:24:30,060 --> 00:24:35,640 just deleting 10 million rows led to more than 10 minutes outage 474 00:24:35,740 --> 00:24:38,000 and a lot of money loss. 475 00:24:39,160 --> 00:24:44,700 So yeah, it's not easy to delete rows in non-partitioned tables, 476 00:24:45,160 --> 00:24:46,160 in large tables. 477 00:24:46,820 --> 00:24:50,700 So it's a big task actually if you need to clean up. 478 00:24:51,280 --> 00:24:56,980 And maybe I told you like one day I went to the VLDB conference which 479 00:24:56,980 --> 00:25:01,480 is probably like the oldest, maybe in the area of databases, the 480 00:25:01,480 --> 00:25:04,900 big conference, mostly with academic people. 481 00:25:05,320 --> 00:25:09,820 It was in Los Angeles and it was one of the keynotes saying data volumes 482 00:25:09,820 --> 00:25:15,280 grow so massively that we need to study how to delete data and 483 00:25:15,280 --> 00:25:20,100 not how to store it, or how to find things which we need to delete. 484 00:25:20,380 --> 00:25:24,060 But with Postgres, knowing how MVCC is organized in Postgres 485 00:25:24,060 --> 00:25:29,560 and how DELETE works, you need to understand, without downtime, 486 00:25:30,180 --> 00:25:32,820 without big stress, DELETEs should be batched. 487 00:25:33,680 --> 00:25:35,400 And the batch size should be... 488 00:25:37,300 --> 00:25:41,000 When I was young, it was difficult for me to understand how to 489 00:25:41,000 --> 00:25:43,040 find the proper batch size. 490 00:25:43,660 --> 00:25:47,540 If you take batch size one, probably too much transaction overhead. 491 00:25:48,340 --> 00:25:51,000 And it will affect this throughput as well. 492 00:25:51,000 --> 00:25:55,520 Like you will be deleting fewer rows per minute or hour, your 493 00:25:55,520 --> 00:25:56,760 background jobs, right? 494 00:25:57,040 --> 00:25:57,540 Michael: Yeah. 495 00:25:58,040 --> 00:26:00,600 Well, I tend to see like low thousands. 496 00:26:00,860 --> 00:26:04,240 Is that how, I guess it depends, but no, what do you tend 497 00:26:04,240 --> 00:26:04,640 Nikolay: to do? 498 00:26:04,640 --> 00:26:05,880 I have a simple approach. 499 00:26:06,400 --> 00:26:08,240 Remember our very first episode. 500 00:26:08,460 --> 00:26:13,220 I actually already described this and you see, like, reminding 501 00:26:13,260 --> 00:26:15,040 things it's also useful sometimes. 502 00:26:15,040 --> 00:26:16,440 So I have a very simple approach. 503 00:26:16,440 --> 00:26:20,020 It's based on what our final goal is. 504 00:26:20,020 --> 00:26:25,040 Our final goal with OLTP systems is that systems should be working 505 00:26:25,040 --> 00:26:25,920 fast in general. 506 00:26:25,920 --> 00:26:30,900 For humans, fast means below 100 or 200 milliseconds, right? 507 00:26:30,900 --> 00:26:31,300 Michael: Nice, 508 00:26:31,300 --> 00:26:31,800 Nikolay: yeah. 509 00:26:32,220 --> 00:26:36,140 1 second is quite slow, 10 seconds is very slow. 510 00:26:36,420 --> 00:26:39,780 Deletes can block some people, some other transactions. 511 00:26:40,560 --> 00:26:43,480 Plus, like, they can lead to I/O. 512 00:26:43,680 --> 00:26:45,280 Effects, like, and so on. 513 00:26:45,280 --> 00:26:50,420 So I prefer batches to last not more than 1, 2, 3 seconds. 514 00:26:51,040 --> 00:26:56,380 In this case, we know negative effects won't last 1 or a few 515 00:26:56,380 --> 00:26:56,880 seconds. 516 00:26:57,740 --> 00:27:00,660 And even in the worst case, we will be blocking, for example, 517 00:27:00,660 --> 00:27:05,520 someone for just 1 second, it's quite slow, but not terribly 518 00:27:05,560 --> 00:27:06,060 slow. 519 00:27:06,560 --> 00:27:07,060 Yeah. 520 00:27:07,060 --> 00:27:11,140 If it's already 10 seconds, it's quite too slow for people and 521 00:27:11,140 --> 00:27:14,860 some of them, like, we might start losing traffic if we talk 522 00:27:14,860 --> 00:27:18,540 about web projects or people start complaining and leaving us, 523 00:27:18,540 --> 00:27:19,040 right? 524 00:27:21,500 --> 00:27:25,400 If it's 100 milliseconds, it's good, but probably too small. 525 00:27:25,640 --> 00:27:29,640 So batches like half a second, 1 second, in my opinion, are perfect. 526 00:27:30,060 --> 00:27:34,840 But sometimes it's hard to understand the duration in advance 527 00:27:35,820 --> 00:27:36,880 for all batches. 528 00:27:36,880 --> 00:27:39,640 You try a few batches, they are fast, but over time they can 529 00:27:39,640 --> 00:27:40,140 degrade. 530 00:27:40,580 --> 00:27:48,580 So you need to keep in mind that if you're batching and deleting 531 00:27:48,640 --> 00:27:52,360 according to some order by, for example, timestamp deleting very 532 00:27:52,360 --> 00:27:55,020 old data first, then new, new, new. 533 00:27:55,080 --> 00:28:00,300 You need to control vacuum because probably your delete trying 534 00:28:00,300 --> 00:28:04,240 to find the next batch will be scanning too many dead tuples. 535 00:28:04,600 --> 00:28:11,540 I mean, index will have a lot of links to, pointers to dead tuples, 536 00:28:11,920 --> 00:28:17,380 and performance of a single batch delete will degrade over time. 537 00:28:17,720 --> 00:28:21,280 Not to allow it, you need to control vacuum behavior and maybe 538 00:28:21,280 --> 00:28:25,080 to do vacuum yourself from time to time, cleaning up dead tuples 539 00:28:25,800 --> 00:28:28,220 and let index be fresh. 540 00:28:28,460 --> 00:28:33,420 Or an additional thing, like sometimes I found myself, maybe we 541 00:28:33,420 --> 00:28:36,220 need an episode about delete actually, or massive operations 542 00:28:36,220 --> 00:28:37,940 if we haven't had it. 543 00:28:37,960 --> 00:28:44,640 But sometimes I just decide, okay, I like stateless queries which 544 00:28:45,040 --> 00:28:47,760 delete batch after batch and we don't remember the state. 545 00:28:47,760 --> 00:28:52,060 But sometimes, like instead of dealing with vacuum myself, I 546 00:28:52,060 --> 00:28:55,580 just delegate it, like I say, okay, I will memorize, I will make 547 00:28:55,580 --> 00:29:00,120 my script or program memorize the state and just know the latest 548 00:29:00,520 --> 00:29:04,160 ID or timestamp deleted and start from there. 549 00:29:04,160 --> 00:29:06,860 It's similar to key set pagination basically. 550 00:29:07,200 --> 00:29:07,700 Michael: Yeah. 551 00:29:08,500 --> 00:29:08,840 It's 552 00:29:08,840 --> 00:29:10,680 Nikolay: not similar, it's it. 553 00:29:11,320 --> 00:29:14,500 In this case, you don't depend on the vacuum behavior and can 554 00:29:14,500 --> 00:29:16,060 go faster and so on. 555 00:29:16,260 --> 00:29:19,000 Michael: The other time I've seen this cause real issues is when 556 00:29:19,000 --> 00:29:24,140 people don't see how many deletes, for example if they, let's say 557 00:29:24,140 --> 00:29:26,680 it's like a SaaS account and they're deleting an account and 558 00:29:26,680 --> 00:29:29,720 then that account has users and those users have events 559 00:29:29,760 --> 00:29:33,280 and these like the amount of deletes a single delete can cascade 560 00:29:33,400 --> 00:29:37,280 to can be very different depending on like if you're deleting 561 00:29:37,280 --> 00:29:39,520 a large account that's a very different amount of deletes than 562 00:29:39,520 --> 00:29:42,600 if you're deleting a small account so I'm guessing this is where 563 00:29:42,600 --> 00:29:44,880 you avoid delete cascade on cascade. 564 00:29:45,520 --> 00:29:49,940 Nikolay: Yeah that's interesting. And in the systems I built fully, 565 00:29:49,940 --> 00:29:53,940 which were built fully under my control, I always tried to avoid 566 00:29:54,140 --> 00:29:55,120 cascade deletes. 567 00:29:55,940 --> 00:30:00,560 But surprisingly, I saw quite big systems which use it. 568 00:30:00,720 --> 00:30:01,220 Yeah. 569 00:30:01,320 --> 00:30:04,620 With many dependent objects deleted and so on. 570 00:30:05,020 --> 00:30:09,480 And I don't know, I think it depends. 571 00:30:09,520 --> 00:30:13,300 You need to think about it in advance, of course, and maybe just 572 00:30:13,440 --> 00:30:14,560 rely on it. 573 00:30:15,180 --> 00:30:17,420 There might be a hybrid approach developed. 574 00:30:17,500 --> 00:30:21,660 So if we know this object is not huge, we delete it relying on 575 00:30:21,660 --> 00:30:22,460 delete cascade. 576 00:30:23,480 --> 00:30:29,720 But if we know the object is huge, maybe we perform like the last 577 00:30:29,720 --> 00:30:33,780 delete, deleting the main object, we clean up asynchronously before 578 00:30:33,780 --> 00:30:35,340 it and only then we delete. 579 00:30:35,340 --> 00:30:35,740 Michael: In patches. 580 00:30:35,740 --> 00:30:35,980 It 581 00:30:35,980 --> 00:30:36,480 Nikolay: depends. 582 00:30:37,000 --> 00:30:37,700 Right, right, right. 583 00:30:38,300 --> 00:30:42,260 It depends on a particular system, but the surprise to me was 584 00:30:42,260 --> 00:30:46,220 to see that there are good systems relying on cascaded delete 585 00:30:46,280 --> 00:30:49,960 with many, many dependent objects, like thousands, tens of thousands, 586 00:30:49,960 --> 00:30:51,920 and kind of okay. 587 00:30:52,600 --> 00:30:57,720 Performance, I would say, as always, it's worth thinking in advance 588 00:30:58,660 --> 00:30:59,440 and testing. 589 00:30:59,640 --> 00:31:04,240 Just testing your system, your environment hardware, performance, 590 00:31:04,940 --> 00:31:09,620 imagine the worst case, test it, see how it works, and then follow 591 00:31:09,620 --> 00:31:13,940 this rule, like 1 second is good, half a second is good, right? 592 00:31:14,180 --> 00:31:15,180 Michael: Yeah, I like that. 593 00:31:15,180 --> 00:31:16,280 I like that a lot. 594 00:31:17,220 --> 00:31:21,420 Nikolay: Yeah, but we actually, since we spoke about, like, this 595 00:31:21,420 --> 00:31:27,540 is kind of an episode about basics, of course, we forgot an elephant 596 00:31:27,540 --> 00:31:28,520 in the room, right? 597 00:31:28,520 --> 00:31:32,460 If you delete without any conditions, without a WHERE clause. 598 00:31:32,900 --> 00:31:38,040 From a table, it will be slow if the table is big, but also it 599 00:31:38,040 --> 00:31:41,880 will be interesting to see that sometimes the table is not like, 600 00:31:41,880 --> 00:31:46,980 the space is not immediately available or like it won't be your 601 00:31:46,980 --> 00:31:48,180 disk space immediately. 602 00:31:49,020 --> 00:31:50,940 Because delete consists of 2 processes. 603 00:31:51,060 --> 00:31:55,200 First is your delete, synchronous, and then vacuum, which really 604 00:31:55,200 --> 00:31:57,680 deletes physically the data tuples. 605 00:31:58,480 --> 00:32:00,660 Michael: That isn't where I thought you were going with that. 606 00:32:00,660 --> 00:32:04,840 I used to work on a tool for SQL Server, Microsoft SQL Server, 607 00:32:04,840 --> 00:32:09,720 it was like a plug into the IDE that they have, that Microsoft 608 00:32:09,780 --> 00:32:10,280 has. 609 00:32:10,520 --> 00:32:13,340 And one of our favorite features, one of the features people loved 610 00:32:13,340 --> 00:32:18,580 the most was a warning to say, did you mean to... 611 00:32:18,580 --> 00:32:20,140 It was for delete and update. 612 00:32:21,580 --> 00:32:22,240 Did you... 613 00:32:22,420 --> 00:32:26,060 If you try and run delete without a where clause, it would warn 614 00:32:26,060 --> 00:32:27,420 you before running it. 615 00:32:27,980 --> 00:32:32,160 If anybody's out there who's writing Postgres IDEs, please add 616 00:32:32,160 --> 00:32:32,920 that feature. 617 00:32:33,420 --> 00:32:34,080 It's so helpful. 618 00:32:34,080 --> 00:32:38,200 Nikolay: Actually, yeah, there is an extension to prohibit this. 619 00:32:38,200 --> 00:32:41,760 And actually, it was funny, I think the very first thing we developed 620 00:32:41,760 --> 00:32:47,240 with Andrei was this exact patch for Postgres, but it was rejected 621 00:32:47,940 --> 00:32:48,200 by 622 00:32:48,200 --> 00:32:49,020 Michael: Oh, for Postgres? 623 00:32:49,360 --> 00:32:50,360 Nikolay: By hackers, yeah, yeah. 624 00:32:50,360 --> 00:32:53,860 But, like, we just wanted a warning to be produced or maybe the 625 00:32:53,860 --> 00:32:58,420 ability to forbid wireless deletes. 626 00:32:59,340 --> 00:33:03,460 So Yeah, and yeah, let's maybe continue. 627 00:33:05,500 --> 00:33:08,600 Michael: But IDEs don't have to worry about the core Postgres. 628 00:33:08,680 --> 00:33:12,040 It can be implemented on a case-by-case basis there, at least. 629 00:33:12,040 --> 00:33:17,140 Nikolay: This is a big mistake if you forget where and execute 630 00:33:17,140 --> 00:33:20,020 it, and sometimes we don't need a semicolon in the end. 631 00:33:20,020 --> 00:33:28,300 For example, in PSQL, if it's hyphen C and you write something, 632 00:33:28,940 --> 00:33:30,060 it will execute it. 633 00:33:30,060 --> 00:33:32,060 But of course, you need quotes. 634 00:33:33,700 --> 00:33:37,860 Sometimes we don't need semicolon if it's a single query execution. 635 00:33:38,220 --> 00:33:40,340 In this case, it can be terribly bad. 636 00:33:40,520 --> 00:33:41,980 So you deleted everything. 637 00:33:42,900 --> 00:33:43,820 Right, right, right. 638 00:33:43,980 --> 00:33:50,520 Okay, but in general, yeah, in general, delete is tricky. 639 00:33:51,380 --> 00:33:51,880 So. 640 00:33:52,480 --> 00:33:54,740 Michael: Oh, I have one more beginner- friendly one. 641 00:33:56,040 --> 00:33:57,320 From the quick list. 642 00:33:57,740 --> 00:34:02,220 Don't use select star, or at least in application code. 643 00:34:02,580 --> 00:34:06,000 I think it's useful for ad hoc queries and exploration. 644 00:34:07,200 --> 00:34:08,660 How do you feel about this one? 645 00:34:09,520 --> 00:34:14,720 Nikolay: Yeah, but do you remember what should be used instead? 646 00:34:15,800 --> 00:34:21,260 Because I don't like like 50 columns to be listed also. 647 00:34:21,820 --> 00:34:25,220 And then you understand that that's all of them. 648 00:34:25,680 --> 00:34:26,180 Right? 649 00:34:26,840 --> 00:34:28,000 Michael: Well, but... 650 00:34:28,000 --> 00:34:30,060 So I think there's a few reasons for this. 651 00:34:30,060 --> 00:34:32,980 Firstly, do you really need 50 columns? 652 00:34:32,980 --> 00:34:35,380 Like, what are you doing that requires 50 columns? 653 00:34:35,380 --> 00:34:36,660 Nikolay: There are so many cases. 654 00:34:37,240 --> 00:34:39,060 This advice cannot be generic. 655 00:34:39,320 --> 00:34:43,020 Because, for example, if I define a view which should consist 656 00:34:43,040 --> 00:34:47,560 of all columns of the underlying table, I will definitely use star, 657 00:34:47,560 --> 00:34:52,020 then later if I need to add a column I redefine view, and I don't 658 00:34:52,020 --> 00:34:54,340 need to rewrite this query, right? 659 00:34:54,340 --> 00:34:56,100 And I know everything is included. 660 00:34:57,040 --> 00:34:58,500 There are many different situations. 661 00:35:00,060 --> 00:35:03,280 Of course, it's a minimalistic approach. 662 00:35:03,420 --> 00:35:05,460 Don't take too much, right? 663 00:35:06,500 --> 00:35:07,180 In general. 664 00:35:07,440 --> 00:35:12,840 But listing all columns, if I need all columns, I would say I 665 00:35:12,840 --> 00:35:15,480 would use star, an asterisk instead. 666 00:35:16,780 --> 00:35:19,700 Michael: So in application code, I think it can break in unexpected 667 00:35:19,940 --> 00:35:20,280 ways. 668 00:35:20,280 --> 00:35:24,400 So like when you if you then add some columns to that, can your 669 00:35:24,400 --> 00:35:28,380 code handle more columns coming back in the future than the current 670 00:35:28,660 --> 00:35:29,160 depends. 671 00:35:30,520 --> 00:35:36,000 Nikolay: If I put the star, of course, I think about future changes 672 00:35:36,000 --> 00:35:36,700 of schema. 673 00:35:36,760 --> 00:35:38,260 I do think about it. 674 00:35:39,280 --> 00:35:39,780 Michael: Cool. 675 00:35:39,920 --> 00:35:40,740 Well, okay. 676 00:35:40,760 --> 00:35:41,260 Interesting. 677 00:35:43,380 --> 00:35:47,640 Nikolay: You know, in Postgres, you can say select table1 from 678 00:35:47,640 --> 00:35:48,500 table1. 679 00:35:49,360 --> 00:35:50,680 Michael: Or just table1. 680 00:35:52,280 --> 00:35:53,660 Nikolay: No, no, no, no, it's different. 681 00:35:54,320 --> 00:35:59,120 I mean, table1 is just selecting everything, right? 682 00:35:59,900 --> 00:36:01,320 Michael: You can do like. 683 00:36:02,020 --> 00:36:03,940 Nikolay: Select table name from table name. 684 00:36:04,700 --> 00:36:08,940 It will give you just 1 column, but of a record type. 685 00:36:09,840 --> 00:36:11,740 Like, kind of a virtual data type. 686 00:36:13,140 --> 00:36:18,040 And everything will be collapsed, and you can unwrap it later 687 00:36:18,580 --> 00:36:20,820 in your queries if it's a subquery. 688 00:36:21,680 --> 00:36:25,140 But this is powerful; you don't need to list all columns. 689 00:36:25,900 --> 00:36:29,440 And it's better than star because sometimes you just need... 690 00:36:29,720 --> 00:36:33,700 This is the magic of Postgres, where maybe First Normal Form 691 00:36:33,700 --> 00:36:37,280 is broken, actually, because you basically wrap everything into 692 00:36:37,280 --> 00:36:38,160 a single column. 693 00:36:38,760 --> 00:36:38,950 Okay. 694 00:36:38,950 --> 00:36:39,140 Michael: The 695 00:36:39,140 --> 00:36:40,020 Nikolay: whole table. 696 00:36:41,320 --> 00:36:41,960 Which is great. 697 00:36:41,960 --> 00:36:45,580 And then you can, if you're inside, for example, PL/pgSQL 698 00:36:45,580 --> 00:36:47,620 context, it's perfect to do this. 699 00:36:47,640 --> 00:36:52,000 You just define a record variable, and you can insert a whole 700 00:36:52,360 --> 00:36:55,500 row with all these columns into this row. 701 00:36:55,760 --> 00:37:00,380 Later, if there is evolution of schema and more columns, this 702 00:37:00,380 --> 00:37:02,720 code will still work. 703 00:37:02,900 --> 00:37:07,200 But depending on your language and driver, I don't know what 704 00:37:07,200 --> 00:37:10,320 will happen if you select table name from table name limit 1, 705 00:37:10,320 --> 00:37:12,080 for example, with some WHERE clause. 706 00:37:12,380 --> 00:37:13,240 I don't know. 707 00:37:14,440 --> 00:37:16,600 It's worth checking, but it's interesting. 708 00:37:16,740 --> 00:37:20,420 This is when you said if we have more columns when they're needed 709 00:37:21,140 --> 00:37:21,760 all right. 710 00:37:21,780 --> 00:37:24,920 Michael: yeah well the time I see this used the most where 711 00:37:24,920 --> 00:37:29,220 I like the reason I think this advice is good is I so often see 712 00:37:29,220 --> 00:37:33,120 I think mostly as a result of ORMs people selecting every 713 00:37:33,120 --> 00:37:35,320 column when they only need 2. 714 00:37:37,360 --> 00:37:38,300 Nikolay: Yeah, yeah, yeah. 715 00:37:39,600 --> 00:37:41,360 This minimalism approach, I get this. 716 00:37:41,360 --> 00:37:45,780 But I have a student right now who writes Python code. 717 00:37:46,680 --> 00:37:51,600 And sharing experience, I just see the code which is written 718 00:37:51,600 --> 00:37:55,320 and I always say like, oh, you define this function but you use 719 00:37:55,320 --> 00:37:56,400 it just once. 720 00:37:56,520 --> 00:37:58,400 Why do you need the function here? 721 00:37:58,660 --> 00:38:01,840 You define the variable which you assign to a different variable 722 00:38:01,840 --> 00:38:03,980 and then you just use it once. 723 00:38:04,020 --> 00:38:05,240 Why do you do this? 724 00:38:05,540 --> 00:38:08,800 You had some constant you defined and used it once. 725 00:38:09,380 --> 00:38:12,080 Just like, why do you need these things? 726 00:38:12,980 --> 00:38:14,920 And here I see a similar pattern. 727 00:38:15,320 --> 00:38:19,200 If we select column names, what if we rename these column names, 728 00:38:19,200 --> 00:38:19,700 right? 729 00:38:21,000 --> 00:38:25,000 We need to go there and rewrite it as well, something like this. 730 00:38:25,000 --> 00:38:28,440 Well, it depends on the situation, of course, but I don't like 731 00:38:28,440 --> 00:38:32,700 the idea that we will list all column names many times if we 732 00:38:32,700 --> 00:38:35,340 know we need the whole row, for example. 733 00:38:36,220 --> 00:38:39,260 It's just like observations trying to... 734 00:38:39,960 --> 00:38:41,980 There is minimalism in star as well. 735 00:38:41,980 --> 00:38:43,540 This is what I'm trying to say. 736 00:38:44,440 --> 00:38:45,760 A different kind of minimalism. 737 00:38:45,940 --> 00:38:48,260 So there is a trade-off here. 738 00:38:50,240 --> 00:38:54,520 Michael: So definitely not always don't do this, but I'd say 739 00:38:54,520 --> 00:38:56,100 don't do this without thinking. 740 00:38:56,820 --> 00:38:57,840 It sounds like. 741 00:38:57,980 --> 00:38:58,880 Nikolay: Right, right. 742 00:38:59,240 --> 00:39:03,960 You need to consider the code and the future evolution, and so 743 00:39:03,960 --> 00:39:04,460 on. 744 00:39:05,280 --> 00:39:05,780 Michael: Nice. 745 00:39:05,900 --> 00:39:09,740 Do you want to end on any that you actually do think are universal? 746 00:39:11,120 --> 00:39:12,040 Nikolay: I don't know. 747 00:39:12,180 --> 00:39:13,480 It's hard, actually. 748 00:39:13,860 --> 00:39:15,260 Michael: It always depends, right? 749 00:39:15,740 --> 00:39:17,760 Nikolay: Yeah, maybe about transactional DDL. 750 00:39:18,900 --> 00:39:20,100 Postgres is cool. 751 00:39:20,580 --> 00:39:24,220 It has transactional DDL until you cannot use it. 752 00:39:28,480 --> 00:39:33,540 And most really heavily loaded systems understand that transactional 753 00:39:33,580 --> 00:39:35,460 DDL is some kind of myth. 754 00:39:36,700 --> 00:39:41,600 I mean, you need it, but to really apply schema changes without 755 00:39:41,600 --> 00:39:43,580 downtime, you need to break it. 756 00:39:44,200 --> 00:39:47,480 Create index concurrently is non-transactional, right? 757 00:39:49,020 --> 00:39:52,240 We discussed it, you mentioned that it has 2 transactions. 758 00:39:55,000 --> 00:39:59,240 If it's interrupted, it's not fully rolled back, so it's not 759 00:39:59,240 --> 00:40:00,360 a transaction already. 760 00:40:00,560 --> 00:40:06,520 You will have an invalid index left, leftovers of your actions. 761 00:40:07,947 --> 00:40:16,260 If you want to change schema in any aspect, If you want to do 762 00:40:16,260 --> 00:40:20,040 it without downtime, you usually need multiple transactions. 763 00:40:20,060 --> 00:40:24,520 For example, if you want some foreign key to define or some check 764 00:40:24,520 --> 00:40:28,240 constraint to define, not now, you will always need, and you 765 00:40:28,240 --> 00:40:30,140 have already a big table loaded. 766 00:40:30,600 --> 00:40:37,180 You will need to think about multiple steps, definitely, and 767 00:40:37,180 --> 00:40:40,300 be ready to roll back 1 of it and go retry. 768 00:40:41,880 --> 00:40:44,240 So, transactional DDL is not easy. 769 00:40:45,400 --> 00:40:46,460 This topic is not easy. 770 00:40:46,460 --> 00:40:49,960 It's not like, oh, we have transactional DDL, all problems solved. 771 00:40:49,960 --> 00:40:50,460 No. 772 00:40:51,500 --> 00:40:57,040 In a highly concurrent environment, it will actually put you to 773 00:40:57,040 --> 00:41:00,260 downtime if you just blindly use transactional DDL. 774 00:41:00,400 --> 00:41:02,620 And create index concurrently is a perfect example. 775 00:41:03,300 --> 00:41:04,160 It's not transactional. 776 00:41:04,640 --> 00:41:08,500 If you use create index, which is transactional, you have downtime. 777 00:41:09,960 --> 00:41:10,580 Partial downtime. 778 00:41:10,580 --> 00:41:15,140 Michael: I'm trying to think if drop index concurrently is transactional. 779 00:41:16,380 --> 00:41:18,900 Nikolay: It needs to acquire an exclusive lock. 780 00:41:19,660 --> 00:41:22,540 It's similar to dropping a column, but Postgres doesn't offer 781 00:41:22,540 --> 00:41:27,340 any tools for 0 downtime drop column. 782 00:41:28,260 --> 00:41:32,200 Drop column cannot be 0 downtime unless you cook it properly 783 00:41:32,200 --> 00:41:34,700 with a low lock timeout and retries. 784 00:41:35,900 --> 00:41:39,720 Drop index concurrently, it's shipped with Postgres; this tool 785 00:41:39,720 --> 00:41:43,660 exists, great, but it's not transactional as well because 786 00:41:43,660 --> 00:41:44,980 it might fail as well. 787 00:41:45,420 --> 00:41:49,660 Right, for example, like, and well, in terms of leftovers, no 788 00:41:49,900 --> 00:41:50,400 leftovers. 789 00:41:53,040 --> 00:41:53,740 Michael: No, no, no. 790 00:41:53,740 --> 00:41:54,960 But it might fail, that's the good thing. 791 00:41:54,960 --> 00:41:57,800 Nikolay: It's just to acquire a lock gracefully, right? 792 00:41:57,800 --> 00:42:03,340 Not to block selects or other queries which came after you started. 793 00:42:04,340 --> 00:42:07,740 Michael: You've worded this quite well in your how-to, but the 794 00:42:07,740 --> 00:42:11,760 idea here is don't assume that Postgres having transactional 795 00:42:11,820 --> 00:42:14,200 DDL will make all your problems go away. 796 00:42:14,200 --> 00:42:16,400 You're still going to have some complex... 797 00:42:17,060 --> 00:42:21,200 Nikolay: I mean, it has it, but in many cases you cannot use 798 00:42:21,200 --> 00:42:26,160 it and in heavily loaded systems you absolutely cannot use it 799 00:42:26,160 --> 00:42:26,720 as is. 800 00:42:26,720 --> 00:42:30,780 You need to cook it properly and have a lot of things around. 801 00:42:31,400 --> 00:42:34,140 A lot of dances need to be learned right 802 00:42:34,700 --> 00:42:37,460 Michael: because of the heavy locks because you normally 803 00:42:38,940 --> 00:42:41,980 Nikolay: Yeah, yeah because of locking issues basically exactly 804 00:42:43,100 --> 00:42:44,540 Cool, right 805 00:42:44,720 --> 00:42:45,440 Michael: Nice one. 806 00:42:45,680 --> 00:42:48,460 Well, I think I'll link all of these up in the show notes. 807 00:42:49,060 --> 00:42:52,200 Maybe the wiki will have changed by the time this episode goes 808 00:42:52,200 --> 00:42:52,640 out. 809 00:42:52,640 --> 00:42:55,580 It'd be cool to hear if anybody thinks there should be additional 810 00:42:55,580 --> 00:42:57,020 ones that should be in there. 811 00:42:58,920 --> 00:43:02,620 Nikolay: If we say don't do it, don't use transactional DDL. 812 00:43:03,540 --> 00:43:07,400 I mean, use it, but only partially, like as pieces. 813 00:43:07,860 --> 00:43:12,660 You cannot use it in 100% of everything, like all schema changes. 814 00:43:12,920 --> 00:43:15,260 You cannot use create index, which is transactional. 815 00:43:16,100 --> 00:43:17,200 Don't use transactional DDL. 816 00:43:19,760 --> 00:43:21,620 I hope people understand when I say this. 817 00:43:21,620 --> 00:43:23,220 Don't use transactional DDL. 818 00:43:24,020 --> 00:43:28,580 Michael: Yeah, I guess create index is the one that will catch people 819 00:43:28,580 --> 00:43:30,260 out if they don't know about 820 00:43:31,100 --> 00:43:31,600 Nikolay: locks. 821 00:43:31,680 --> 00:43:36,700 You cannot drop a column without proper lock timeout and retries. 822 00:43:37,660 --> 00:43:39,880 And this is already beyond a single transaction. 823 00:43:41,040 --> 00:43:43,840 Michael: Well, and in the past, even adding columns... 824 00:43:45,900 --> 00:43:47,740 Nikolay: You cannot add a column without default. 825 00:43:49,940 --> 00:43:50,860 You cannot add it. 826 00:43:50,860 --> 00:43:52,840 It's the same as dropping a column. 827 00:43:53,180 --> 00:43:57,240 You need an exclusive lock and if there is an ongoing long transaction, 828 00:43:57,340 --> 00:43:59,180 you're blocked and you have troubles. 829 00:43:59,760 --> 00:44:03,140 It means you need some additional orchestration. 830 00:44:04,640 --> 00:44:07,300 Michael: Nice, I think you'll find it hard to word that for the 831 00:44:07,300 --> 00:44:09,440 wiki, but I think it would be a good addition. 832 00:44:09,440 --> 00:44:11,080 Nikolay: Don't use transactional DDL. 833 00:44:12,260 --> 00:44:12,760 Okay. 834 00:44:13,660 --> 00:44:15,040 Michael: You'd have a lot of caveats. 835 00:44:16,220 --> 00:44:19,440 Nikolay: Right, well it's very similar to many items we discussed 836 00:44:19,440 --> 00:44:19,940 today. 837 00:44:20,440 --> 00:44:21,500 Michael: True, true. 838 00:44:21,500 --> 00:44:22,840 Nikolay: Right, this advice. 839 00:44:23,560 --> 00:44:24,060 Okay? 840 00:44:25,080 --> 00:44:26,540 Michael: Yeah, thanks so much, Nikolay. 841 00:44:26,940 --> 00:44:28,100 Thanks everyone for listening. 842 00:44:28,320 --> 00:44:29,440 Catch you next week.