1 00:00:00,060 --> 00:00:02,600 Michael: Hello and welcome to PostgresFM, a weekly show about 2 00:00:02,600 --> 00:00:03,540 all things PostgreSQL. 3 00:00:03,740 --> 00:00:05,580 I am Michael, founder of pgMustard. 4 00:00:05,680 --> 00:00:07,640 This is Nikolay, founder of Postgres.AI. 5 00:00:07,640 --> 00:00:08,460 Hello Nikolay. 6 00:00:08,940 --> 00:00:10,300 Nikolay: Hi Michael, how are you? 7 00:00:10,860 --> 00:00:12,280 Michael: I'm good, how are you? 8 00:00:12,560 --> 00:00:13,280 Nikolay: Very good. 9 00:00:13,580 --> 00:00:15,660 Let's talk about some crazy stuff. 10 00:00:16,160 --> 00:00:20,040 Michael: I chose the topic this week and it was a listener suggestion, 11 00:00:20,280 --> 00:00:24,020 it's Postgres gotchas and we've had a short discussion before 12 00:00:24,020 --> 00:00:26,700 this call about what we should even count as gotchas. 13 00:00:27,400 --> 00:00:30,620 So how do you define it, what do you think of when you hear Postgres 14 00:00:30,620 --> 00:00:31,120 gotcha? 15 00:00:31,580 --> 00:00:35,160 Nikolay: Well if you for example run, I don't know, like SELECT 16 00:00:35,360 --> 00:00:39,980 5/2, what will you get? 17 00:00:41,580 --> 00:00:43,220 Michael: Is this like data type casting? 18 00:00:43,740 --> 00:00:47,000 Nikolay: Right right well it's not Postgres related I think it's 19 00:00:47,040 --> 00:00:51,140 SQL related but still what do you expect to get? 20 00:00:51,180 --> 00:00:54,560 Normal person would expect 2.5, right? 21 00:00:55,260 --> 00:00:58,300 But you will get, and I just double checked because I always... 22 00:00:59,220 --> 00:01:02,260 I'm always like thinking, am I right? 23 00:01:02,580 --> 00:01:05,580 So you're getting 2, so 5. 24 00:01:05,940 --> 00:01:08,740 Michael: 0 wow, I actually thought it was going to be 3. 25 00:01:13,700 --> 00:01:15,700 Nikolay: We probably have 2 things to discuss here. 26 00:01:15,700 --> 00:01:19,560 First is of course integer and integer, right? 27 00:01:19,560 --> 00:01:23,000 Results should be integer, so there's no decimal, there's no 28 00:01:23,000 --> 00:01:24,820 point here, there's no dot. 29 00:01:25,280 --> 00:01:27,680 And, but 2 or 3? 30 00:01:29,340 --> 00:01:31,600 Rounding or flooring, right? 31 00:01:31,640 --> 00:01:35,280 Michael: Yeah, because like rounding conventions at least where 32 00:01:35,280 --> 00:01:40,600 I grew up we generally round up from 0.5 but not clearly not 33 00:01:40,600 --> 00:01:45,560 here anyway I like this almost this definition which is something 34 00:01:45,860 --> 00:01:50,820 where you may expect 1 outcome but a different outcome happens. 35 00:01:50,860 --> 00:01:51,360 That's 36 00:01:51,560 --> 00:01:54,100 Nikolay: unexpected behavior basically in other words. 37 00:01:54,860 --> 00:01:57,900 Michael: Yeah, especially unexpected if you haven't read the 38 00:01:57,900 --> 00:01:58,400 documentation. 39 00:02:00,640 --> 00:02:04,740 Nikolay: Sometimes documentation also wrong or misleading or 40 00:02:05,020 --> 00:02:07,360 sometimes it's just confusing. 41 00:02:08,540 --> 00:02:09,220 Michael: Yeah fair enough. 42 00:02:09,220 --> 00:02:12,160 I just wanted an excuse to list a few things that are made very 43 00:02:12,160 --> 00:02:16,020 clear in the documentation, but I know people get confused by 44 00:02:16,020 --> 00:02:17,940 it because often they don't read that. 45 00:02:18,820 --> 00:02:22,280 Nikolay: You know, I'm curious, I actually found a good example, 46 00:02:22,280 --> 00:02:22,540 right? 47 00:02:22,540 --> 00:02:25,680 It's like probably a gotcha SQL, not Postgres, because 48 00:02:25,680 --> 00:02:30,040 I think it's maybe defined by standard, but, and this is super 49 00:02:30,040 --> 00:02:34,940 basic, but I'm wondering why indeed it's 2 not 3 because if you 50 00:02:34,940 --> 00:02:39,440 for example I what I did I double check of course I checked floor 51 00:02:39,480 --> 00:02:44,060 and round functions and of course as expected floor I converted 52 00:02:44,060 --> 00:02:47,780 5 to numeric or just just to 53 00:02:47,780 --> 00:02:49,320 Michael: last with the colon colon 54 00:02:49,360 --> 00:02:52,360 Nikolay: you're right right so the result is also numeric and 55 00:02:52,360 --> 00:02:59,480 then we have of course 2.500 like so but then I checked floor 56 00:02:59,480 --> 00:03:03,800 gives me 2 round gives me 3 is expected because 2.5 means like 57 00:03:03,800 --> 00:03:08,800 we need if you're rounding according to their names for these 58 00:03:08,800 --> 00:03:09,860 rules, I don't remember, right? 59 00:03:09,860 --> 00:03:14,700 But usually 5 you go up, 4 you go down, right? 60 00:03:15,540 --> 00:03:18,660 And it gives 3, Round gives 3. 61 00:03:18,660 --> 00:03:26,100 But I was thinking, okay, why 5 slash 2 integer and integer gives 62 00:03:26,100 --> 00:03:27,320 2 and not 3? 63 00:03:27,400 --> 00:03:29,200 Maybe because it uses 4. 64 00:03:30,180 --> 00:03:34,120 We could check the source code, of course, but the next thing 65 00:03:34,120 --> 00:03:36,300 I checked is the final thing I checked. 66 00:03:36,740 --> 00:03:40,160 5 converted to numeric slash 2 gives you 2.5. 67 00:03:40,260 --> 00:03:42,040 And then you convert back to int, right? 68 00:03:42,040 --> 00:03:43,860 Again, double colon int. 69 00:03:44,380 --> 00:03:45,600 It gives 3. 70 00:03:46,300 --> 00:03:46,800 Amazing. 71 00:03:50,440 --> 00:03:55,940 So how come we have 2 if we don't use type conversion, explicit 72 00:03:55,960 --> 00:03:57,320 type conversion at all? 73 00:03:58,080 --> 00:03:58,760 Any ideas? 74 00:03:59,760 --> 00:04:01,580 It was good gotcha to find. 75 00:04:01,960 --> 00:04:04,200 I always knew that it's 2 because I demonstrated. 76 00:04:04,200 --> 00:04:06,920 I just didn't think like why. 77 00:04:07,500 --> 00:04:08,600 Now I think why. 78 00:04:08,940 --> 00:04:09,940 I have questions. 79 00:04:10,240 --> 00:04:13,780 Maybe there is some good explanation, simple, but I can see easily 80 00:04:13,780 --> 00:04:15,040 that it can be gotcha. 81 00:04:15,480 --> 00:04:17,180 It can be considered gotcha. 82 00:04:17,860 --> 00:04:20,420 And let's agree on 1 more thing. 83 00:04:20,740 --> 00:04:22,400 We don't talk about nulls today. 84 00:04:23,920 --> 00:04:26,520 Michael: I actually did have them on my list, but not to go into 85 00:04:26,520 --> 00:04:27,020 detail. 86 00:04:27,260 --> 00:04:30,580 Nikolay: We had a whole episode, and this whole episode is telling 87 00:04:30,580 --> 00:04:36,000 like, nulls are surrounded by gotchas both like standard defined 88 00:04:36,600 --> 00:04:41,040 standard produce and like global SQL gotchas and local Postgres 89 00:04:41,040 --> 00:04:45,060 related gotchas as well for example when you go to a race right 90 00:04:45,060 --> 00:04:49,460 Let's just exclude it because it's a whole massive area. 91 00:04:49,900 --> 00:04:51,260 Michael: I think that's very sensible. 92 00:04:51,600 --> 00:04:53,800 And if you haven't listened to that episode, I'll link it up 93 00:04:53,800 --> 00:04:54,740 in the show notes. 94 00:04:55,240 --> 00:04:58,940 Nikolay: Consider it to be fully included here. 95 00:04:59,140 --> 00:04:59,640 Yeah. 96 00:05:00,080 --> 00:05:00,580 Right. 97 00:05:00,920 --> 00:05:03,680 Okay, let's maybe switch to your ideas. 98 00:05:03,680 --> 00:05:06,400 I know you have a list, pull something from it. 99 00:05:06,460 --> 00:05:09,940 Michael: Yeah, I've got a few that I wanted to make sure we mentioned. 100 00:05:10,080 --> 00:05:14,180 I'm gonna start with some ones I consider more beginner level 101 00:05:14,540 --> 00:05:17,580 gotchas and then maybe we can work our way up a little bit. 102 00:05:17,620 --> 00:05:23,880 The first 1 I had is 1 I see all the time in kind of forums every 103 00:05:23,880 --> 00:05:28,260 now and again on Reddit, quite a few times in like the Postgres 104 00:05:28,260 --> 00:05:30,520 Slack, a few up on a few mailing lists. 105 00:05:31,220 --> 00:05:35,760 People getting confused or annoyed when they name objects in 106 00:05:35,760 --> 00:05:42,380 Postgres and they use mixed case, so maybe camel case or some 107 00:05:42,380 --> 00:05:47,220 way of naming a table like for example like a two-worded name 108 00:05:47,380 --> 00:05:50,640 and using capital letters for the 2 names and no space in between 109 00:05:50,640 --> 00:05:53,680 them that kind of thing and then realizing the table is not called 110 00:05:53,680 --> 00:05:58,040 that behind the scenes and it's all lowercase and if they've 111 00:05:58,120 --> 00:06:02,820 used an ORM or something to name these at the ORM often puts 112 00:06:03,420 --> 00:06:07,500 quotes around those case names so then they become cased in the 113 00:06:07,500 --> 00:06:08,000 database. 114 00:06:08,420 --> 00:06:13,300 So I think this whole area is like a 1 big gotcha for beginners 115 00:06:14,440 --> 00:06:18,060 and the people who set up the schema in the first place, if they 116 00:06:18,340 --> 00:06:22,340 do something like this, if they don't just use lowercase object 117 00:06:22,340 --> 00:06:26,140 names, can really make life a bit painful for everybody that 118 00:06:26,140 --> 00:06:29,680 comes after them having to double quote everything. 119 00:06:30,180 --> 00:06:33,840 Nikolay: Yeah, especially Java people like it. 120 00:06:34,300 --> 00:06:35,040 Michael: Oh, really? 121 00:06:35,140 --> 00:06:37,180 Nikolay: Yeah, I noticed. 122 00:06:37,940 --> 00:06:43,180 Usually, if we have a new customer and we start discussing their 123 00:06:43,180 --> 00:06:47,780 database, we see some camel-stack-cased table names. 124 00:06:47,780 --> 00:06:52,440 And I think, I'm already thinking, are you Java guys? 125 00:06:52,440 --> 00:06:53,540 Yeah, there are. 126 00:06:53,940 --> 00:06:56,260 Usually, not always, but usually so. 127 00:06:56,440 --> 00:07:00,520 And my previous startup was using it camel-style. 128 00:07:03,420 --> 00:07:06,240 And we just decided to keep it as is because it was inherited 129 00:07:06,260 --> 00:07:06,960 from some... 130 00:07:07,200 --> 00:07:09,260 I think it was in MySQL originally. 131 00:07:09,900 --> 00:07:14,020 Like, we started very lean, so we borrowed some schema from some 132 00:07:14,020 --> 00:07:15,080 open source project. 133 00:07:15,480 --> 00:07:16,580 And it had it. 134 00:07:16,640 --> 00:07:20,680 And I just, knowing this very well, I still decided to keep it. 135 00:07:20,680 --> 00:07:22,960 But the problem with double quotes is not like... 136 00:07:22,960 --> 00:07:24,640 It's not a big deal, but when... 137 00:07:24,720 --> 00:07:27,760 You know what comes with quotes usually, right? 138 00:07:28,580 --> 00:07:29,080 Backslashes. 139 00:07:30,480 --> 00:07:31,180 Michael: Oh, having... 140 00:07:31,180 --> 00:07:32,800 Yeah, having to escape them. 141 00:07:32,900 --> 00:07:34,920 Nikolay: Yeah, and you cannot escape from this. 142 00:07:34,920 --> 00:07:37,400 You need to use it to escape. 143 00:07:37,660 --> 00:07:37,940 Right? 144 00:07:37,940 --> 00:07:39,980 I mean, yeah. 145 00:07:40,080 --> 00:07:44,780 And backslashes, if you have automation in many languages, like 146 00:07:44,780 --> 00:07:46,100 sometimes it's nightmare. 147 00:07:46,960 --> 00:07:50,580 Right now we have an ongoing problem with backslashes and escaping 148 00:07:51,040 --> 00:07:54,360 of quotes and double quotes in our AI system. 149 00:07:54,440 --> 00:07:57,900 It's a combination of Python and Bash actually. 150 00:07:58,520 --> 00:08:00,520 And yeah, it drives us crazy sometimes. 151 00:08:00,700 --> 00:08:03,220 We solved some problems, but not all of them still. 152 00:08:03,580 --> 00:08:07,660 And when Yi needs to talk about your table names and you give 153 00:08:07,660 --> 00:08:11,420 the schema with double quotes, and then you want to experiment 154 00:08:11,480 --> 00:08:16,620 in DBLab to find some plans or something, it sometimes is broken 155 00:08:16,620 --> 00:08:21,160 just because of problems with backslashes and passing these pieces 156 00:08:21,220 --> 00:08:22,700 between different components. 157 00:08:24,060 --> 00:08:27,040 I would prefer keeping everything lowercase, that's it. 158 00:08:27,040 --> 00:08:27,680 In post-process. 159 00:08:27,800 --> 00:08:28,300 Right? 160 00:08:29,440 --> 00:08:30,600 Snake style, right? 161 00:08:31,100 --> 00:08:31,600 Michael: Yeah. 162 00:08:31,620 --> 00:08:32,220 Nikolay: And that's it. 163 00:08:32,220 --> 00:08:33,640 And just forget about it. 164 00:08:35,220 --> 00:08:36,000 Michael: Yeah, plays. 165 00:08:36,140 --> 00:08:37,040 Pay for plays. 166 00:08:37,500 --> 00:08:40,440 Nikolay: I wanted to mention the list of gotchas I saw actually 167 00:08:40,440 --> 00:08:44,960 this is like as we discussed right before this recording I didn't 168 00:08:44,960 --> 00:08:49,780 know this the word gotcha until 2006 or 2007 and this is exactly 169 00:08:49,780 --> 00:08:52,860 when this list was published. 170 00:08:53,220 --> 00:08:56,500 And I remember we had 2 lists, 1 for Postgres and 1 for MySQL. 171 00:08:57,180 --> 00:09:00,840 For MySQL, it's outside of scope, right? 172 00:09:01,100 --> 00:09:03,640 Again, even more than nulls. 173 00:09:03,820 --> 00:09:08,080 But this list, I'm looking at it right now and I'm very first 174 00:09:08,080 --> 00:09:15,940 of all it's survived how many years like it's insane 18 17 years. 175 00:09:16,860 --> 00:09:17,300 Wow. 176 00:09:17,300 --> 00:09:21,600 And I'm looking at this, it's quite short list at SQLinfo.de, 177 00:09:23,100 --> 00:09:23,600 right? 178 00:09:24,480 --> 00:09:27,040 And almost everything is crossed out. 179 00:09:27,980 --> 00:09:28,480 Good. 180 00:09:29,240 --> 00:09:31,640 Michael: Yeah, this I've never seen this page before. 181 00:09:31,640 --> 00:09:32,740 This is really cool. 182 00:09:32,980 --> 00:09:36,980 And yeah, maybe more than half of them have been cost 183 00:09:36,980 --> 00:09:37,480 down. 184 00:09:40,080 --> 00:09:41,760 Nikolay: Yeah, account start is very slow. 185 00:09:42,660 --> 00:09:44,120 Let's just laugh at it. 186 00:09:45,060 --> 00:09:47,140 Michael: Oh, and we had an episode on that, right? 187 00:09:47,540 --> 00:09:48,840 Nikolay: I think we should. 188 00:09:48,840 --> 00:09:51,580 Unicode means UTF-8 left as well. 189 00:09:51,580 --> 00:09:52,400 Well, yeah. 190 00:09:53,740 --> 00:09:56,840 Yeah, constraint checking is interesting, but maybe let's not 191 00:09:56,840 --> 00:09:57,620 dive into it. 192 00:09:57,620 --> 00:10:00,720 And finally, I wanted to highlight what else is left. 193 00:10:00,720 --> 00:10:01,660 Only 4 items. 194 00:10:01,880 --> 00:10:02,880 5, Okay, 5. 195 00:10:02,880 --> 00:10:06,960 But 1 of them is upgrades, upgrades, related stuff. 196 00:10:07,200 --> 00:10:12,100 And I was discussing on Twitter, on X, I was discussing this 197 00:10:12,100 --> 00:10:13,940 topic over the last few days. 198 00:10:14,180 --> 00:10:20,260 I just checked documentation of 3 major managed Postgres providers. 199 00:10:20,840 --> 00:10:24,300 So RDS, Cloud SQL, and Azure Postgres. 200 00:10:25,240 --> 00:10:30,460 I saw that all of them say, okay, upgrade is done, major upgrade. 201 00:10:30,600 --> 00:10:33,900 Now it's time, don't forget, now it's time to run ANALYZE. 202 00:10:33,900 --> 00:10:38,300 And they say ANALYZE like they give you a snippet, ANALYZE semicolon, 203 00:10:38,400 --> 00:10:41,220 which means it's single-threaded as well. 204 00:10:41,520 --> 00:10:43,860 First of all, they don't include it. 205 00:10:43,860 --> 00:10:46,800 None of them include it into automation. 206 00:10:47,620 --> 00:10:50,460 But we don't talk about managed Postgres providers today, we 207 00:10:50,460 --> 00:10:51,320 talk about Postgres. 208 00:10:52,500 --> 00:10:58,180 My question is why pg_upgrade does not include... 209 00:10:58,180 --> 00:11:01,220 It prints only some text in there, but who reads the text? 210 00:11:01,440 --> 00:11:05,140 Many people will say, for those who don't read, it's on their 211 00:11:05,140 --> 00:11:06,520 shoulders, it's their problem. 212 00:11:06,820 --> 00:11:08,460 But I cannot agree with it. 213 00:11:09,600 --> 00:11:14,340 Michael: I could even make a case for the gotcha here is that 214 00:11:14,340 --> 00:11:17,460 statistics are not preserved on major upgrade. 215 00:11:17,800 --> 00:11:23,860 I think if you asked somebody that didn't know whether they would 216 00:11:23,860 --> 00:11:27,900 expect the statistics to be wiped or to be preserved, somebody 217 00:11:27,900 --> 00:11:31,080 maybe doesn't understand how difficult it might be or like the 218 00:11:31,080 --> 00:11:34,640 catalog, like the reasons why they're not preserved, you'd be 219 00:11:34,640 --> 00:11:38,600 forgiven to think like in a in the most advanced open source 220 00:11:38,600 --> 00:11:42,600 database that that might be that might happen even though it's 221 00:11:42,600 --> 00:11:45,110 complicated and I think there has been some work. 222 00:11:45,110 --> 00:11:45,290 It should 223 00:11:45,290 --> 00:11:47,660 Nikolay: happen and Everyone agrees on it, just not solved. 224 00:11:48,280 --> 00:11:50,520 Michael: Yeah, so I think that's the main gotcha. 225 00:11:50,540 --> 00:11:54,280 And then, obviously, the solution at the moment is we need to 226 00:11:54,280 --> 00:11:55,060 want to analyze. 227 00:11:55,520 --> 00:11:56,980 Nikolay: Yeah, let's unwrap a little bit. 228 00:11:56,980 --> 00:11:59,860 So we talk about statistics, which is required for a planner 229 00:11:59,860 --> 00:12:01,720 to make right decisions. 230 00:12:02,300 --> 00:12:05,640 If the database is not trivial, like containing like 100 rows 231 00:12:05,640 --> 00:12:06,960 in each table, that's it. 232 00:12:06,960 --> 00:12:08,860 If it's not trivial, we need statistics. 233 00:12:09,520 --> 00:12:11,440 Without statistics, database is slow. 234 00:12:12,700 --> 00:12:17,680 And what happens, I think in a zillion cases, it happens. 235 00:12:17,920 --> 00:12:21,420 Like It's hard to estimate, but when I posted this tweet, I received 236 00:12:21,420 --> 00:12:29,120 several replies immediately that, oh, we got beaten by this badly. 237 00:12:31,400 --> 00:12:35,740 Some person wrote, we learned this hard way, obviously. 238 00:12:36,260 --> 00:12:38,460 And this is like what happens all the time. 239 00:12:38,460 --> 00:12:45,800 And recently, during summer, we also like failed to help a customer 240 00:12:45,800 --> 00:12:46,160 with it. 241 00:12:46,160 --> 00:12:48,580 Like we discussed major upgrades, but we... 242 00:12:48,680 --> 00:12:52,040 From now on, I'm pointing out, like, don't forget, don't forget, 243 00:12:52,040 --> 00:12:52,780 don't forget. 244 00:12:53,100 --> 00:12:55,960 But we discussed upgrades, we discussed some, like, complicated 245 00:12:56,040 --> 00:13:00,520 stuff, and then they went to upgrade, and next Monday they say, 246 00:13:00,520 --> 00:13:02,160 oh, today they are in Europe. 247 00:13:02,160 --> 00:13:06,220 So in the morning, it was already by the evening in Europe. 248 00:13:06,220 --> 00:13:10,380 And they say, we had nasty database incidents today because guess 249 00:13:10,380 --> 00:13:10,760 what? 250 00:13:10,760 --> 00:13:13,120 We forgot to run ANALYZE. 251 00:13:14,240 --> 00:13:18,480 And this is huge gotcha not only by managed services, they could 252 00:13:18,480 --> 00:13:21,520 do it but I don't care too much about them. 253 00:13:21,660 --> 00:13:23,040 This is a gotcha of pg_upgrade. 254 00:13:23,240 --> 00:13:26,140 I think it's very well understood, there is work in progress 255 00:13:26,200 --> 00:13:31,980 for export and import of statistics, like quite a long thread 256 00:13:31,980 --> 00:13:34,500 in hackers already and commitfest entry. 257 00:13:34,900 --> 00:13:38,340 It's still not done, but there is hope. 258 00:13:39,960 --> 00:13:41,400 There is 1 more thing here. 259 00:13:41,400 --> 00:13:44,940 So I think pg_upgrade definitely should have it. 260 00:13:45,060 --> 00:13:48,220 And also pg_restore, but let's return to it in a few minutes. 261 00:13:50,280 --> 00:13:54,780 So when someone says you need to recalculate statistics yourself 262 00:13:55,240 --> 00:13:57,140 after upgrade, pg_upgrade. 263 00:13:58,920 --> 00:14:01,820 I definitely would expect pg_upgrade to do this for sure. 264 00:14:02,020 --> 00:14:04,220 But okay, it just prints do it. 265 00:14:04,340 --> 00:14:07,420 pg_upgrade prints vacuumdb, I think, no? 266 00:14:07,420 --> 00:14:10,280 Or in stages, or like vacuumdb in stages. 267 00:14:10,280 --> 00:14:14,060 It has this vacuumdb analyze only in stages. 268 00:14:14,060 --> 00:14:14,640 2 options. 269 00:14:14,640 --> 00:14:16,940 Analyze only means no vacuum and just Analyze. 270 00:14:17,320 --> 00:14:23,420 In stages means first it gets only 1 bucket for each table, for 271 00:14:23,420 --> 00:14:25,320 each column actually, right? 272 00:14:25,520 --> 00:14:29,480 And then some midway until your... 273 00:14:29,480 --> 00:14:34,600 And then default_statistics_target or individual column based 274 00:14:34,600 --> 00:14:36,180 setting for each column. 275 00:14:36,340 --> 00:14:39,780 default_statistics_target by default is 100, 100 buckets. 276 00:14:40,560 --> 00:14:46,580 This is what defines how much Postgres planner will know about 277 00:14:46,680 --> 00:14:48,780 distribution in each column, right? 278 00:14:48,780 --> 00:14:52,040 And it can be adjusted globally or for individual columns. 279 00:14:52,840 --> 00:14:53,260 But... 280 00:14:53,260 --> 00:14:56,100 Michael: Almost like a sample size, like the sample size increases 281 00:14:56,260 --> 00:14:57,320 of what it samples. 282 00:14:57,320 --> 00:14:57,560 Nikolay: Yeah. 283 00:14:57,560 --> 00:15:01,200 And I quickly realized this is not what you want to do in OLTP, 284 00:15:01,220 --> 00:15:06,560 because in OLTP, you either include Analyze inside maintenance 285 00:15:06,580 --> 00:15:06,980 window. 286 00:15:06,980 --> 00:15:11,180 In this case, there is no sense to run it 3 times, or you just... 287 00:15:12,880 --> 00:15:14,940 Or that's it, there is only option actually. 288 00:15:15,180 --> 00:15:19,440 Like, I don't trust in opening gates with weak statistics. 289 00:15:19,680 --> 00:15:22,220 It will be like unpredictable again incident. 290 00:15:23,080 --> 00:15:23,580 Michael: Yeah. 291 00:15:24,140 --> 00:15:24,820 Yeah, risky. 292 00:15:24,860 --> 00:15:28,080 But this feels like a topic that might be worth a deeper dive. 293 00:15:28,440 --> 00:15:28,940 Nikolay: Right. 294 00:15:29,280 --> 00:15:29,640 Right. 295 00:15:29,640 --> 00:15:30,040 Right. 296 00:15:30,040 --> 00:15:34,300 I just wanted to point out that when people say Analyze, it's 297 00:15:34,300 --> 00:15:36,640 just 1 threaded, right? 298 00:15:37,120 --> 00:15:41,420 And you expect Postgres has a lot of parallelism implemented. 299 00:15:41,940 --> 00:15:44,440 But when you run Analyze, it's always single threaded. 300 00:15:44,680 --> 00:15:46,800 This is unexpected as well, right? 301 00:15:47,100 --> 00:15:47,540 Michael: Good 1. 302 00:15:47,540 --> 00:15:48,520 Yeah, I like that. 303 00:15:48,520 --> 00:15:51,640 Nikolay: It's kind of gotcha inside gotcha we have matryoshka 304 00:15:51,700 --> 00:15:54,620 style gotcha here right Russian doll style 305 00:15:54,620 --> 00:15:57,620 Michael: what do you do you recommend doing like just kicking 306 00:15:57,620 --> 00:16:00,540 off a bunch of queries analyzed tape like per table 307 00:16:00,540 --> 00:16:04,380 Nikolay: I recommend partition I recommend vacuumdb 308 00:16:04,800 --> 00:16:05,780 Michael: nice okay 309 00:16:05,920 --> 00:16:08,680 Nikolay: And speed up and go full speed inside maintenance window. 310 00:16:08,680 --> 00:16:09,940 Again, it's a different story. 311 00:16:10,200 --> 00:16:15,780 But this vacuumdb, it has --jobs 312 00:16:15,780 --> 00:16:17,120 or -j. 313 00:16:17,440 --> 00:16:18,300 You can define. 314 00:16:18,300 --> 00:16:22,240 And usually, we take as many courses we have on the server and 315 00:16:22,240 --> 00:16:24,780 go full speed inside maintenance window to achieve. 316 00:16:25,120 --> 00:16:28,200 Analyze should be quite fast if you don't do vacuum. 317 00:16:29,160 --> 00:16:33,540 There is also gotcha a little bit in vacuumdb options because 318 00:16:33,600 --> 00:16:37,320 vacuumdb's, --analyze will also do vacuum. 319 00:16:37,580 --> 00:16:38,200 So there's 320 00:16:38,200 --> 00:16:39,860 Michael: also analyze only or 321 00:16:39,860 --> 00:16:41,140 Nikolay: there is analyze only. 322 00:16:41,240 --> 00:16:43,120 Yeah, but it's easy to overlook. 323 00:16:43,340 --> 00:16:45,800 There's 1 more gotcha here inside all this. 324 00:16:46,300 --> 00:16:51,560 When people go with manual single-threaded analyze or maybe multi-threaded 325 00:16:51,820 --> 00:16:54,120 but just explicit analyze. 326 00:16:55,080 --> 00:16:59,960 I've like quickly, it was interesting, I was thinking someone 327 00:16:59,960 --> 00:17:01,560 said analyze can be super expensive. 328 00:17:01,560 --> 00:17:05,140 I agree, but even if it's like, if the default_statistics_target 329 00:17:05,140 --> 00:17:06,780 is 1000, it's not that expensive. 330 00:17:06,780 --> 00:17:11,020 But back to this, can we throttle it? 331 00:17:12,520 --> 00:17:16,100 For example, you want it to go very slow, because autovacuum 332 00:17:16,100 --> 00:17:19,660 has autovacuum, doesn't have actually 333 00:17:19,660 --> 00:17:20,940 Michael: a vacuum_cost_delay also? 334 00:17:21,180 --> 00:17:24,060 Nikolay: Yes, there is vacuum_cost_delay and vacuum_cost_limit. 335 00:17:25,360 --> 00:17:28,440 By default, it's off because I think the cost limit is 0, meaning 336 00:17:28,440 --> 00:17:30,040 that it's not checked at all. 337 00:17:30,400 --> 00:17:33,720 Not cost limit, cost delay is 0, like no check. 338 00:17:34,120 --> 00:17:38,820 But autovacuum has like mirrored pair of these settings. 339 00:17:39,560 --> 00:17:40,460 It's throttled. 340 00:17:41,060 --> 00:17:43,520 Long story short, autovacuum is throttled usually. 341 00:17:44,340 --> 00:17:47,440 And we actually usually fight with our customers a little bit 342 00:17:47,440 --> 00:17:51,000 to increase this, make it even more aggressive. 343 00:17:52,800 --> 00:17:56,040 And managed providers already did part of that job, which is 344 00:17:56,040 --> 00:17:56,540 good. 345 00:17:56,880 --> 00:18:00,840 But if we run it manually, it's not throttled. 346 00:18:00,840 --> 00:18:02,140 It will go full speed. 347 00:18:02,200 --> 00:18:06,960 I doubt you will need to throttle it, but some people want, okay, 348 00:18:06,960 --> 00:18:08,040 let's throttle it. 349 00:18:08,100 --> 00:18:09,340 Question, how? 350 00:18:09,620 --> 00:18:14,440 We have a vacuum limit, vacuum_cost_limit, vacuum_cost_delay. 351 00:18:15,040 --> 00:18:19,060 We can set vacuum_cost_delay from default 0 to some point. 352 00:18:19,600 --> 00:18:23,320 But is it about Analyze, or it's only about vacuum? 353 00:18:23,320 --> 00:18:25,680 Based on just the naming, it's not about Analyze. 354 00:18:25,680 --> 00:18:27,040 It's about vacuum only. 355 00:18:28,140 --> 00:18:28,640 Michael: Right? 356 00:18:28,680 --> 00:18:29,900 Yeah, that would be my guess. 357 00:18:29,900 --> 00:18:33,780 But by the line of your questioning, I'm guessing it does affect 358 00:18:33,780 --> 00:18:34,620 your analysis. 359 00:18:34,820 --> 00:18:35,220 Nikolay: Right. 360 00:18:35,220 --> 00:18:39,620 I go to documentation and I see vacuum_cost_limit, vacuum_cost_delay, 361 00:18:39,620 --> 00:18:40,680 I think. 362 00:18:40,680 --> 00:18:41,740 And I see description. 363 00:18:41,980 --> 00:18:44,560 They talk only about vacuum, they don't talk about analysis. 364 00:18:45,040 --> 00:18:47,740 So I make conclusion, it's only about vacuum. 365 00:18:48,400 --> 00:18:53,560 But some part of my very long-term memory tells me, is it really 366 00:18:53,560 --> 00:18:54,060 so? 367 00:18:55,840 --> 00:18:59,540 And then Sadek Dusty, who helped me a lot with, and still helping 368 00:18:59,540 --> 00:19:03,240 sometimes when I write at how-to's and reviews How-To's, I'm 369 00:19:03,240 --> 00:19:06,060 very thankful for that. 370 00:19:06,880 --> 00:19:08,660 I have already 94 how-tos. 371 00:19:09,720 --> 00:19:11,660 So I wrote how-to, how-to Analyze. 372 00:19:11,930 --> 00:19:14,980 And right now I need to add this. 373 00:19:16,220 --> 00:19:19,140 vacuum_cost_limit, vacuum_cost_delay, they affect Analyze. 374 00:19:19,440 --> 00:19:23,680 If you scroll up to the beginning of section, you will see that 375 00:19:23,680 --> 00:19:26,340 it talks about both vacuum and Analyze. 376 00:19:26,800 --> 00:19:30,680 And we also, using AI, I checked source code and quickly found 377 00:19:30,680 --> 00:19:35,200 that there is a function called vacuumExec, which works with 378 00:19:35,200 --> 00:19:36,540 both vacuum and Analyze. 379 00:19:36,820 --> 00:19:40,040 And this confusion comes from source code, propagates to the 380 00:19:40,040 --> 00:19:42,480 documentation, and then to our heads, right? 381 00:19:42,560 --> 00:19:43,940 This is gotcha as well. 382 00:19:43,940 --> 00:19:47,460 So you can use vacuum_cost_limit, vacuum_cost_delay, to throttle 383 00:19:47,600 --> 00:19:51,300 Analyze, although I usually don't do it because I think we need 384 00:19:51,300 --> 00:19:52,320 to go full speed. 385 00:19:53,400 --> 00:19:55,900 Michael: And in the 1 example we're talking about here, we want 386 00:19:55,900 --> 00:19:57,280 to go faster, not slower. 387 00:19:57,660 --> 00:19:58,880 Nikolay: Right, right. 388 00:19:59,340 --> 00:20:00,560 But it's interesting, right? 389 00:20:00,660 --> 00:20:02,080 So there is some small confusion. 390 00:20:02,080 --> 00:20:04,060 I think there are many such confusions. 391 00:20:04,820 --> 00:20:06,540 But they are slowly fixed. 392 00:20:06,680 --> 00:20:08,400 They are slowly being fixed. 393 00:20:08,400 --> 00:20:10,860 Michael: Great example of 1 where the documentation 394 00:20:10,920 --> 00:20:13,760 wasn't helpful in avoiding that 1. 395 00:20:13,940 --> 00:20:14,780 So yeah, nice. 396 00:20:15,120 --> 00:20:17,960 I've got a few more beginner-friendly ones that maybe we could 397 00:20:17,960 --> 00:20:18,740 rattle through. 398 00:20:18,740 --> 00:20:23,560 I'm not even sure you'd consider them all gotchas, but 1 relates 399 00:20:27,620 --> 00:20:28,280 of places. 400 00:20:29,040 --> 00:20:30,900 I think that not only catches... 401 00:20:30,920 --> 00:20:32,660 Well, you don't think it's a gotcha? 402 00:20:33,120 --> 00:20:36,220 Nikolay: I think we should exclude this on the same, like, we're 403 00:20:36,220 --> 00:20:41,540 using the same approach as now because it's a huge bag of, yeah. 404 00:20:41,920 --> 00:20:42,320 Michael: Cool. 405 00:20:42,320 --> 00:20:45,080 And then another 1 that I see catch out, well, a couple that 406 00:20:45,080 --> 00:20:48,280 I see catch out beginners, again, maybe this is another huge 407 00:20:48,280 --> 00:20:53,400 topic, but access exclusive locks on like just basically DDL 408 00:20:54,300 --> 00:20:58,080 blocking selects, like blocking every, blocking inserts and like 409 00:20:58,080 --> 00:21:02,620 you know all sorts of people getting, I think people don't realize 410 00:21:02,620 --> 00:21:05,680 because if they're working on small databases at first, they 411 00:21:05,680 --> 00:21:09,860 start to grow in confidence of how to do things. 412 00:21:10,080 --> 00:21:14,620 And then it only catches them out once the startup's got really 413 00:21:14,620 --> 00:21:17,260 successful and the table's much bigger, that kind of thing. 414 00:21:17,680 --> 00:21:23,080 Nikolay: Yeah, I think there is some memes with Gauss distribution, 415 00:21:23,080 --> 00:21:24,500 or how it's called, right? 416 00:21:24,680 --> 00:21:25,660 Gaussian distribution. 417 00:21:25,760 --> 00:21:29,840 And first, I think schema changes are painful. 418 00:21:31,300 --> 00:21:34,660 In the, like, schema changes are super painful. 419 00:21:34,660 --> 00:21:36,920 They require expertise, like, beginner, right? 420 00:21:37,040 --> 00:21:41,460 Then in the middle, you can imagine, oh, Postgres has transactional 421 00:21:41,580 --> 00:21:44,720 DDL, everything is fine, we just need to wrap everything into 422 00:21:44,720 --> 00:21:48,400 1 transaction and, and rely on Postgres great capabilities of 423 00:21:48,400 --> 00:21:49,040 transactional DDL. 424 00:21:49,040 --> 00:21:50,860 Michael: Yeah, or just concurrently and think 425 00:21:50,860 --> 00:21:53,400 Nikolay: yeah Yeah, MVCC works. 426 00:21:53,400 --> 00:21:56,680 Well, it doesn't block sell Excel X are always working you can 427 00:21:56,680 --> 00:22:01,580 you can have a lot of Like exciting stuff in the middle and then 428 00:22:01,580 --> 00:22:05,360 on the on the right you say post this doesn't look was this has 429 00:22:05,360 --> 00:22:08,520 come Transactional detail, but you cannot use it 430 00:22:09,040 --> 00:22:11,280 Michael: Well the in on the right has to be the same as the left, 431 00:22:11,280 --> 00:22:11,720 doesn't it? 432 00:22:11,720 --> 00:22:13,040 Schema changes are painful. 433 00:22:13,380 --> 00:22:17,320 Nikolay: Yes, they are painful, transactional DDL cannot be used 434 00:22:17,320 --> 00:22:24,380 in many, many cases, and you can shoot your feet very well many 435 00:22:24,380 --> 00:22:24,880 times. 436 00:22:29,600 --> 00:22:31,120 So this is it, I think. 437 00:22:31,120 --> 00:22:32,940 And a lot of gotchas there. 438 00:22:33,740 --> 00:22:36,180 Michael: Another 1 that's not necessarily, I'm not sure if this 439 00:22:36,180 --> 00:22:38,400 counts as a gotcha, I'd be interested in your opinion but it 440 00:22:38,400 --> 00:22:41,780 does have seen it catch people out and I guess it's a little 441 00:22:41,780 --> 00:22:45,340 bit of a foot gun and that's you know you see the memes about 442 00:22:45,340 --> 00:22:48,620 this as well actually DELETE or UPDATE without a WHERE clause 443 00:22:48,620 --> 00:22:52,000 and then just suddenly seeing you know instead of 5 updated. 444 00:22:55,080 --> 00:22:57,720 But if you think like it's not Postgres, why do you say it's 445 00:22:57,720 --> 00:22:59,120 not Postgres out of interest? 446 00:22:59,340 --> 00:23:02,360 Nikolay: Because the SQL standard does it like defines this and 447 00:23:02,360 --> 00:23:06,540 any relational database which follows SQL standard to some extent 448 00:23:07,120 --> 00:23:07,940 has it. 449 00:23:08,760 --> 00:23:09,260 Michael: Yeah. 450 00:23:10,140 --> 00:23:17,360 But I've seen clients catch these, But the ones that ship, well, 451 00:23:17,360 --> 00:23:18,980 the only, the psql doesn't. 452 00:23:19,120 --> 00:23:22,480 So I don't think it's unreasonable that Postgres does it this 453 00:23:22,480 --> 00:23:24,980 way, but I've definitely seen it catch people out. 454 00:23:25,440 --> 00:23:26,100 Nikolay: I agree. 455 00:23:26,580 --> 00:23:27,880 Blame SQL standard. 456 00:23:28,140 --> 00:23:30,780 And there is extension to forbid it. 457 00:23:30,780 --> 00:23:35,080 And also it was maybe 1 of the very first hacking sessions with 458 00:23:35,080 --> 00:23:35,500 Andrei. 459 00:23:35,500 --> 00:23:43,000 We implemented some setting, GUC setting in Postgres, which would 460 00:23:43,000 --> 00:23:48,820 allow administrators to, I don't think error, warning, like completely 461 00:23:48,820 --> 00:23:50,380 forbid or just warn, I don't remember. 462 00:23:50,380 --> 00:23:52,220 I think error should be here. 463 00:23:52,540 --> 00:23:55,680 But it was not accepted, of course, because I think it was discussed 464 00:23:55,680 --> 00:23:56,420 many times. 465 00:23:57,280 --> 00:23:58,480 I mean, it's possible. 466 00:23:58,940 --> 00:24:02,960 And right now, as I see, I think PostgREST does it, right? 467 00:24:03,340 --> 00:24:03,840 PostgREST... 468 00:24:04,460 --> 00:24:05,440 Michael: Oh, does it? 469 00:24:05,640 --> 00:24:08,800 Nikolay: So usually right now it's sold, if there is some middleware, 470 00:24:08,940 --> 00:24:09,840 it's sold there. 471 00:24:10,320 --> 00:24:15,220 So if you develop API using PostgREST, for example, I think, Maybe 472 00:24:15,220 --> 00:24:17,220 I'm mistaken, but I think there is a way. 473 00:24:17,320 --> 00:24:19,900 If there is no way, it's a good idea to implement, actually. 474 00:24:19,900 --> 00:24:20,980 Michael: Yeah, it would be, yeah. 475 00:24:20,980 --> 00:24:26,820 Nikolay: Yeah, so if in URL of your HTTP request of RESTful API, 476 00:24:27,080 --> 00:24:31,720 there is a command to run delete without any filters, I think 477 00:24:31,720 --> 00:24:36,860 it will not do it it will not go to progress with it so but inside 478 00:24:36,860 --> 00:24:42,380 database I don't I don't think it's going to change right and 479 00:24:42,380 --> 00:24:44,080 we tried actually so 480 00:24:44,760 --> 00:24:48,060 Michael: yes with the well and we tried what do you mean each 481 00:24:48,060 --> 00:24:49,120 right again 482 00:24:49,140 --> 00:24:51,900 Nikolay: we had a session yeah we had a patch and they submitted 483 00:24:51,900 --> 00:24:56,240 it, but it was a small discussion and chances are close to 0 484 00:24:56,240 --> 00:24:58,860 that it will be successful. 485 00:24:59,840 --> 00:25:02,060 Michael: Patch in psql or somewhere else? 486 00:25:02,220 --> 00:25:03,340 Nikolay: No, not in psql. 487 00:25:03,580 --> 00:25:08,340 We wanted to have a Postgres setting, GUC setting, to just 488 00:25:08,340 --> 00:25:12,840 allow administrators to forbid unspecified, I don't remember 489 00:25:12,840 --> 00:25:16,500 how we called it, maybe unspecified delete and update, when you 490 00:25:16,500 --> 00:25:19,020 don't have the WHERE clause at all. 491 00:25:19,800 --> 00:25:21,120 Just like, 492 00:25:21,220 --> 00:25:21,720 Michael: cool, 493 00:25:22,060 --> 00:25:22,820 Nikolay: don't do it. 494 00:25:22,820 --> 00:25:26,260 There's a truncate for if you want to hit right an update of 495 00:25:26,260 --> 00:25:30,900 whole table I don't know yeah but again like right now it's in 496 00:25:30,900 --> 00:25:33,700 middleware usually sold in middle where You can have triggers 497 00:25:33,700 --> 00:25:35,900 or something to protect yourself if you want. 498 00:25:36,300 --> 00:25:38,960 Or this extension if you're on self-managed Postgres. 499 00:25:38,960 --> 00:25:40,540 There is some extension for it. 500 00:25:40,760 --> 00:25:44,840 I wanted to mention 1 thing, step back to DDL. 501 00:25:45,420 --> 00:25:51,000 My favorite example is, do you think just adding a Column, you 502 00:25:51,000 --> 00:25:55,240 cannot put your traffic down or your Database down? 503 00:25:55,240 --> 00:25:57,580 You can, if it cannot acquire Lock. 504 00:25:57,800 --> 00:26:01,660 It starts blocking all Queries to this Table, even SELECTs. 505 00:26:02,580 --> 00:26:05,780 Like, I cannot acquire Lock, everyone else can wait. 506 00:26:05,900 --> 00:26:11,540 Even SELECTs who come with access Share Lock, don't touch this. 507 00:26:12,040 --> 00:26:12,760 That's it. 508 00:26:13,320 --> 00:26:14,340 And that's not good. 509 00:26:14,680 --> 00:26:17,660 And this is super unexpected and a super big surprise. 510 00:26:18,720 --> 00:26:22,000 Michael: Yeah, especially because you could even test that. 511 00:26:22,580 --> 00:26:27,620 You could even make a fork of production or clone it run that 512 00:26:27,620 --> 00:26:31,520 on its own in isolation where there is no Lock and it runs super 513 00:26:31,520 --> 00:26:35,540 fast so you think this is safe but even though it was super fast 514 00:26:35,540 --> 00:26:39,720 it does require that heavy Lock and that blocks everything else 515 00:26:39,720 --> 00:26:41,780 definitely a gotcha that's a great 1 516 00:26:41,800 --> 00:26:45,480 Nikolay: yeah yeah and there are many others in the area of DDL 517 00:26:45,480 --> 00:26:51,600 but this I find like affecting every single project which had 518 00:26:51,660 --> 00:26:55,900 like at least some growth like I don't like to 1000 TPS and it's 519 00:26:55,900 --> 00:26:58,820 already noticeable it's not noticeable in tiny projects 520 00:27:00,140 --> 00:27:02,900 Michael: yeah yeah which I think is part is kind of part of the 521 00:27:02,900 --> 00:27:06,100 problem right or like at least makes it worse because anybody 522 00:27:06,100 --> 00:27:10,920 that gains experience on smaller projects as they grow Get some 523 00:27:10,920 --> 00:27:13,940 confidence that these things work or they're fine and then it 524 00:27:13,940 --> 00:27:15,740 change changes at some point 525 00:27:16,320 --> 00:27:16,820 Nikolay: exactly 526 00:27:18,060 --> 00:27:20,140 Michael: so you've got a great article in this actually I think 527 00:27:20,140 --> 00:27:24,900 that I can share about DDL it's all about DDL changes I think 528 00:27:25,160 --> 00:27:28,860 Nikolay: yeah it's yeah yeah it's this this 1 is definitely included 529 00:27:28,860 --> 00:27:33,180 I think this 1 like has a different blog post about like I have 530 00:27:33,180 --> 00:27:37,980 few about Details okay what else 531 00:27:38,760 --> 00:27:41,720 Michael: well in that in that area of making big changes partly 532 00:27:41,720 --> 00:27:45,200 by mistake you know that the DELETE or UPDATE without WHERE Do 533 00:27:45,200 --> 00:27:48,660 you think people would expect to be able to undo something? 534 00:27:48,940 --> 00:27:54,440 I'm not thinking just from people with experience of relational 535 00:27:54,440 --> 00:27:58,520 databases, but the fact that if you do something like that by 536 00:27:58,520 --> 00:28:02,080 mistake, that you have to go to your backups to get that data 537 00:28:02,080 --> 00:28:02,580 back. 538 00:28:03,080 --> 00:28:06,260 If I was in my early 20s or just out, you know, if I was in my 539 00:28:06,260 --> 00:28:10,120 teens, all other software I use has the ability to undo something 540 00:28:10,120 --> 00:28:11,420 if I do it by mistake. 541 00:28:11,940 --> 00:28:16,220 So it does feel slightly unexpected, potentially if I've grown 542 00:28:16,220 --> 00:28:19,640 up in that world, that I can make 1 mistake and not be able to 543 00:28:19,640 --> 00:28:19,960 undo it. 544 00:28:19,960 --> 00:28:21,220 Nikolay: And what Database you can? 545 00:28:21,220 --> 00:28:22,440 I mean, of course, there are like... 546 00:28:22,440 --> 00:28:23,440 Michael: Yeah, I don't know. 547 00:28:23,440 --> 00:28:24,660 Nikolay: Time travel or... 548 00:28:25,840 --> 00:28:27,500 If branching is supported, you can... 549 00:28:27,500 --> 00:28:32,140 But it's still like not super simple, right? 550 00:28:32,520 --> 00:28:33,720 Well, yeah, I don't know. 551 00:28:33,720 --> 00:28:36,800 I mean, there are several projects which try to solve this problem 552 00:28:36,820 --> 00:28:41,760 and I mean the data specific databases supporting branching for 553 00:28:41,760 --> 00:28:47,620 example or even other I think Have you heard about this new company 554 00:28:47,660 --> 00:28:49,520 where Michael Stonebraker is participating? 555 00:28:50,920 --> 00:28:51,060 Michael: I've 556 00:28:51,060 --> 00:28:53,000 heard of it, I don't know much about it. 557 00:28:53,000 --> 00:28:53,500 Nikolay: DBOps? 558 00:28:53,840 --> 00:28:54,840 Or how is it called? 559 00:28:54,840 --> 00:28:55,700 I don't remember. 560 00:28:55,920 --> 00:28:57,100 Not DBOps, DBOS. 561 00:28:58,700 --> 00:28:59,200 Michael: Yes. 562 00:29:00,180 --> 00:29:03,160 Nikolay: So I wonder if this feature will be inside it. 563 00:29:03,860 --> 00:29:04,700 I don't know. 564 00:29:04,740 --> 00:29:06,260 For me, it's not expected. 565 00:29:06,380 --> 00:29:12,100 I cannot name this gotcha because it's a hard problem, and we 566 00:29:13,140 --> 00:29:18,820 didn't get used to it, to having it, because it's rare. 567 00:29:19,710 --> 00:29:28,100 But if we go to young people's minds, there are many gotchas 568 00:29:28,100 --> 00:29:28,780 for them, right? 569 00:29:28,780 --> 00:29:30,700 Oh, this is working not as I expected. 570 00:29:32,080 --> 00:29:36,360 So naming is hard just because it should avoid gotchas. 571 00:29:36,380 --> 00:29:40,480 If you chose some name, you should think about other people and 572 00:29:40,480 --> 00:29:42,180 think to be very predictable. 573 00:29:42,980 --> 00:29:44,840 This is the art of naming. 574 00:29:45,440 --> 00:29:49,200 And we just discussed this vacuum_cost_limit affecting analyze. 575 00:29:50,140 --> 00:29:56,280 So yeah, and in young minds, gotchas, because gotchas world is 576 00:29:56,280 --> 00:30:00,740 much bigger because they don't understand the logic in many places, 577 00:30:00,740 --> 00:30:05,740 how it's like, like common practice in many places, right? 578 00:30:05,740 --> 00:30:08,540 And that's why many more unexpected situations. 579 00:30:09,640 --> 00:30:10,460 Michael: Yeah, fair. 580 00:30:11,000 --> 00:30:14,540 Nikolay: Yeah, by the way, if you run pg_dump on a very large 581 00:30:14,880 --> 00:30:21,400 database, like 1 table after another, And then we deal with snapshot, 582 00:30:21,400 --> 00:30:21,900 right? 583 00:30:22,580 --> 00:30:26,700 With snapshot, it's a repeatable read and snapshot. 584 00:30:26,820 --> 00:30:32,000 And even if you use multiple workers for pg_dump, they are synchronized 585 00:30:32,360 --> 00:30:33,780 reading from the same snapshot. 586 00:30:34,200 --> 00:30:38,100 But what if some table is dropped? 587 00:30:38,360 --> 00:30:40,340 Will it be present in this table? 588 00:30:41,040 --> 00:30:42,180 I'm not sure, actually. 589 00:30:42,880 --> 00:30:44,340 I just think there is a God check 590 00:30:44,340 --> 00:30:44,840 Michael: here. 591 00:30:45,660 --> 00:30:51,300 Nikolay: If during dump there are some schema changes, can we 592 00:30:51,300 --> 00:30:54,240 have some inconsistency in backups? 593 00:30:55,260 --> 00:30:56,580 Not in backups, in dumps. 594 00:30:56,580 --> 00:31:01,160 By the way, this confusion was resolved recently, right? 595 00:31:01,220 --> 00:31:06,040 We didn't mention it in the previous discussions of confusions, 596 00:31:06,060 --> 00:31:15,520 but the pg_dump page is not saying it's for dumps anymore. 597 00:31:15,940 --> 00:31:17,380 So dumps are not backups. 598 00:31:19,660 --> 00:31:20,880 Michael: That's coming. 599 00:31:21,960 --> 00:31:22,960 Nikolay: Yeah, in 2018. 600 00:31:23,100 --> 00:31:23,940 Okay, okay, okay. 601 00:31:23,940 --> 00:31:25,300 We will discuss this additionally. 602 00:31:25,640 --> 00:31:29,940 But this is a big confusion when the computation says pg_dump 603 00:31:30,060 --> 00:31:31,080 is a backup tool. 604 00:31:31,080 --> 00:31:33,220 It's still so, even in 16. 605 00:31:33,760 --> 00:31:38,660 And now we have all rights to say it's like all people who said 606 00:31:38,800 --> 00:31:42,500 pg_dump is not a backup tool, we are right, because finally in 607 00:31:42,500 --> 00:31:44,040 18 it's already changed. 608 00:31:44,040 --> 00:31:47,340 I think it won't be reverted, I hope so, this patch. 609 00:31:49,540 --> 00:31:54,800 But back to our topic, pg_dump can be expected to be a tool for 610 00:31:54,800 --> 00:31:56,460 backups just because of documentation. 611 00:31:56,720 --> 00:32:01,320 Then you have quite a painful process of realizing it's very 612 00:32:01,320 --> 00:32:02,320 bad for backups. 613 00:32:02,900 --> 00:32:06,600 So it's a gotcha, which is already fixed in the future in Postgres 614 00:32:07,000 --> 00:32:07,500 18. 615 00:32:09,860 --> 00:32:18,900 And if we, in this area, pg_restore doesn't run analyze, which 616 00:32:19,160 --> 00:32:24,620 hurts every second attempt to restore a big database, even with 617 00:32:24,620 --> 00:32:26,620 experienced people like I am. 618 00:32:26,880 --> 00:32:29,920 Because, again, it's like with pg_upgrade, we discussed it. 619 00:32:29,920 --> 00:32:33,400 pg_restore, You restore a huge dump, you start working with 620 00:32:33,400 --> 00:32:38,940 it, but it's slow, and in plans you see where you expect a simple 621 00:32:38,940 --> 00:32:42,520 index scan, you see bitmap scans, bitmap index, bitmap heap scan, 622 00:32:42,520 --> 00:32:44,720 because there is no vacuum, there is no analysis. 623 00:32:45,060 --> 00:32:50,580 It also should run on a vacuum to collect visibility maps. 624 00:32:51,340 --> 00:32:51,840 Or 625 00:32:53,300 --> 00:32:56,980 Michael: it might not be unreasonable to think that maybe backups 626 00:32:56,980 --> 00:32:59,980 would include the statistics and then restore would restore the 627 00:32:59,980 --> 00:33:00,480 statistics. 628 00:33:01,400 --> 00:33:02,920 Nikolay: Physical backups, I agree. 629 00:33:03,280 --> 00:33:04,860 Logical backups, I disagree. 630 00:33:06,680 --> 00:33:10,260 You don't include indexes, which is derivative from data itself. 631 00:33:10,260 --> 00:33:12,840 Statistics is also derivative of data. 632 00:33:13,660 --> 00:33:16,120 So you include description, like create index. 633 00:33:16,320 --> 00:33:20,520 There should be, maybe should be some word like analyze in there. 634 00:33:22,060 --> 00:33:25,620 I wanted to say create statistics, but it was analyzed already. 635 00:33:26,180 --> 00:33:27,100 There's such word. 636 00:33:27,500 --> 00:33:28,200 Why restore? 637 00:33:28,200 --> 00:33:31,420 And restore could run it. 638 00:33:31,420 --> 00:33:37,280 If we use pg_restore -j 8, for example, 8 parallel drops. 639 00:33:37,920 --> 00:33:43,160 In this case, why are we not recalculating statistics? 640 00:33:44,100 --> 00:33:48,720 Right now, everyone who deals with some automation, for example, 641 00:33:48,720 --> 00:33:52,200 for DBLab Engine, we need it when provisioning is logical. 642 00:33:52,200 --> 00:33:55,440 So it's dump restore and we need to move as fast as possible 643 00:33:55,440 --> 00:34:00,180 multiple jobs, multiple workers for dump, multiple workers for 644 00:34:00,180 --> 00:34:00,680 restore. 645 00:34:01,100 --> 00:34:05,920 And then of course, vacuumdb, in this case, --analyze 646 00:34:05,920 --> 00:34:10,840 to have vacuum as well and also -j, but People forget about 647 00:34:10,840 --> 00:34:14,340 this and forget forgetting means like you expect it will be fine, 648 00:34:14,340 --> 00:34:20,220 but if you don't run analyze yourself ideally in multiple threads 649 00:34:20,220 --> 00:34:23,400 and multiple workers using multiple workers in this case performance 650 00:34:23,400 --> 00:34:28,200 will be very bad and people start thinking oh Postgres is slow 651 00:34:30,040 --> 00:34:33,540 have you heard about this Like I restore from dump and it's slow. 652 00:34:35,140 --> 00:34:39,400 Michael: I have to admit I haven't heard that complaint, no. 653 00:34:40,860 --> 00:34:46,240 Nikolay: Okay, I'm hearing it and I'm doing it myself almost 654 00:34:46,240 --> 00:34:47,060 20 years. 655 00:34:47,088 --> 00:34:49,172 I know about this. 656 00:34:49,200 --> 00:34:51,500 Michael: No, I'm not saying it doesn't exist, I just haven't, 657 00:34:51,500 --> 00:34:54,880 you know, maybe it's just not the category of problem maybe what 658 00:34:54,880 --> 00:34:58,940 happens when people experience that is they don't noticed online 659 00:34:59,380 --> 00:35:02,860 or I didn't know it's or they don't post they just go try something 660 00:35:02,860 --> 00:35:03,360 else 661 00:35:03,520 --> 00:35:07,120 Nikolay: yeah it's it's not a big deal if you restore, and it's 662 00:35:07,120 --> 00:35:10,760 working, and then you want to deal with it 1 day later. 663 00:35:10,760 --> 00:35:14,120 Because probably, likely, autovacuum will do it. 664 00:35:14,600 --> 00:35:18,620 But If you expect to run fast queries immediately after restoration 665 00:35:18,920 --> 00:35:24,000 from a dump, I'm talking about something which is 100% very well 666 00:35:24,000 --> 00:35:27,940 known to everyone, who is at least a little bit experienced. 667 00:35:28,700 --> 00:35:32,000 But it doesn't make it non-gotcha, it's still gotcha. 668 00:35:34,360 --> 00:35:39,440 That pg_restore doesn't calculate statistics and doesn't run Vacuum. 669 00:35:42,040 --> 00:35:44,620 Michael: I mean it's quite remarkable, I know we could go on 670 00:35:44,620 --> 00:35:47,960 with this list for quite a while and there's like varying levels 671 00:35:47,960 --> 00:35:53,000 of gotchas but I like that I like the list you sent me how how 672 00:35:53,000 --> 00:35:57,840 short and brief it is and I I really think for many other databases 673 00:35:58,320 --> 00:36:02,760 it would be a much much longer list I listened to well you're 674 00:36:02,760 --> 00:36:06,260 listening to your experience of you know a couple of days of 675 00:36:06,260 --> 00:36:10,840 trying to use MySQL at the beginning, and recently I was listening 676 00:36:10,840 --> 00:36:14,820 to an interview with Tom Lane, and he mentioned, like, checking 677 00:36:14,820 --> 00:36:19,540 out MySQL at the beginning and not liking a few things about 678 00:36:19,540 --> 00:36:19,940 it. 679 00:36:19,940 --> 00:36:22,100 And that feels like a really common experience. 680 00:36:22,540 --> 00:36:25,920 And having worked with commercial databases like SQL Server and 681 00:36:25,920 --> 00:36:32,080 Oracle, that often move much faster in terms of adding features 682 00:36:32,080 --> 00:36:33,940 that enterprise clients really want. 683 00:36:34,120 --> 00:36:39,240 There's just so many more gotchas that result from processes 684 00:36:39,360 --> 00:36:39,800 like that. 685 00:36:39,800 --> 00:36:41,120 So I imagine I could be wrong. 686 00:36:41,120 --> 00:36:44,620 Nikolay: I think Postgres has lots of gotchas, lots of them. 687 00:36:45,300 --> 00:36:47,580 Michael: But compared to other relational databases? 688 00:36:48,340 --> 00:36:51,360 Nikolay: Yeah, it's a complex system, all complex systems have 689 00:36:51,360 --> 00:36:52,280 a lot of gotchas. 690 00:36:52,600 --> 00:36:57,920 Some of them are just very niche, very narrow and deep and so 691 00:36:57,920 --> 00:36:58,420 on. 692 00:36:58,940 --> 00:37:01,760 Some of them are like nobody knows about them. 693 00:37:01,760 --> 00:37:04,900 For example, SELECT INTO. 694 00:37:06,820 --> 00:37:09,740 This is what you can write inside PL/pgSQL, right? 695 00:37:10,080 --> 00:37:15,140 So you write a FUNCTION or TRIGGER, and you write SELECT INTO 696 00:37:15,300 --> 00:37:17,560 some variable, blah, blah, blah. 697 00:37:17,560 --> 00:37:20,860 And then you think, OK, this SELECT is like maybe it's complex 698 00:37:20,860 --> 00:37:21,760 SELECT, actually. 699 00:37:22,380 --> 00:37:26,040 Subselect or, I don't know, joins and so on. 700 00:37:26,040 --> 00:37:29,360 But in the end, you have a scalar or something, and you select 701 00:37:29,600 --> 00:37:31,680 into a variable inside PL/pgSQL. 702 00:37:31,800 --> 00:37:34,240 And then you think, I want to understand the plan, or I want 703 00:37:34,240 --> 00:37:38,940 to just run it and try to troubleshoot, and you put it to psql, 704 00:37:39,840 --> 00:37:40,840 what will happen? 705 00:37:40,840 --> 00:37:45,860 If you put SELECT INTO blah blah blah, and then some regular 706 00:37:45,920 --> 00:37:49,380 select from where order by you know 707 00:37:49,380 --> 00:37:52,620 Michael: just an error no what happens 708 00:37:52,900 --> 00:37:54,240 Nikolay: it will create a table 709 00:37:56,120 --> 00:37:57,120 Michael: oh okay yeah 710 00:37:57,120 --> 00:38:01,360 Nikolay: go select into is 1 of your lights it's great table 711 00:38:02,420 --> 00:38:06,140 This I think just should be removed, like completely removed, 712 00:38:06,380 --> 00:38:06,880 deprecated. 713 00:38:07,900 --> 00:38:10,300 Select into creating a table, it's a DDL. 714 00:38:15,140 --> 00:38:17,580 This is definitely a gotcha for everyone. 715 00:38:18,840 --> 00:38:21,540 Michael: Yes, imagine all those parsers that just... 716 00:38:21,820 --> 00:38:25,240 I know this is flawed for multiple reasons, but look at any query 717 00:38:25,240 --> 00:38:28,140 that starts with SELECT, and like, oh, it's a read query. 718 00:38:28,200 --> 00:38:32,780 Nikolay: Well, yeah, usually we say SELECT for update is acquiring 719 00:38:32,860 --> 00:38:35,280 a heavy lock, blocking all writes. 720 00:38:36,220 --> 00:38:42,300 Yes, blocking all writes on these rows, particular rows, not 721 00:38:42,380 --> 00:38:43,280 the whole table. 722 00:38:43,620 --> 00:38:46,780 But Select can create a table. 723 00:38:47,180 --> 00:38:49,700 And we have create table as select already. 724 00:38:49,840 --> 00:38:53,180 And this is what all people use, I think, who need it. 725 00:38:53,600 --> 00:38:59,440 So select into probably just some very old stuff that could be 726 00:38:59,440 --> 00:39:01,080 potentially removed, I think. 727 00:39:01,400 --> 00:39:03,280 Michael: If it's not in the SQL standard 728 00:39:05,200 --> 00:39:09,560 Nikolay: oh it's a good point if it is no chances right 729 00:39:09,780 --> 00:39:13,700 Michael: yeah well probably not no cool anything else you want 730 00:39:13,700 --> 00:39:14,840 to make sure we covered 731 00:39:15,420 --> 00:39:18,480 Nikolay: maybe it's enough I think we covered less than 1% of 732 00:39:18,480 --> 00:39:19,620 what we could. 733 00:39:19,700 --> 00:39:20,740 This is my feeling. 734 00:39:20,740 --> 00:39:21,640 What do you think? 735 00:39:22,240 --> 00:39:25,560 Michael: Well, it depends what you count as gotchas and how niche 736 00:39:25,560 --> 00:39:26,640 you're willing to go. 737 00:39:26,640 --> 00:39:29,640 It turns out the more you know, the more gotchas you know about, 738 00:39:29,640 --> 00:39:30,280 I guess. 739 00:39:31,220 --> 00:39:34,100 Nikolay: So I could not resist and I checked the documentation. 740 00:39:34,640 --> 00:39:37,940 SQL standard uses SELECT INTO to represent selecting values into 741 00:39:37,940 --> 00:39:43,380 a scalar variable of a host program rather than creating a new 742 00:39:43,380 --> 00:39:43,700 table. 743 00:39:43,700 --> 00:39:47,220 It's definitely Postgresism and I think it should be removed. 744 00:39:47,720 --> 00:39:49,340 Michael: Okay, cool, good 1. 745 00:39:50,500 --> 00:39:53,220 Nikolay: Good, okay, good chat, maybe some kind of entertaining 746 00:39:53,240 --> 00:39:56,100 more than useful, but I enjoyed it. 747 00:39:56,100 --> 00:39:56,820 I enjoyed it. 748 00:39:56,820 --> 00:39:57,540 Michael: Yeah, absolutely. 749 00:39:58,320 --> 00:40:02,660 And feel free to send us any more that you've got or that you 750 00:40:02,660 --> 00:40:05,900 Nikolay: Maybe we should think about specific areas like we did 751 00:40:05,900 --> 00:40:11,740 for for now and maybe we should explore some areas from this 752 00:40:11,740 --> 00:40:13,320 perspective what can be unexpected 753 00:40:14,340 --> 00:40:16,680 Michael: Yeah, for sure. Thanks so much. 754 00:40:16,680 --> 00:40:17,220 Nikolay. 755 00:40:17,220 --> 00:40:18,180 Catch you next week. 756 00:40:18,180 --> 00:40:18,240 Nikolay: Thank you. 757 00:40:18,240 --> 00:40:18,560 You too.