1 00:00:00,060 --> 00:00:02,720 Michael: Hello and welcome to Postgres.FM, a weekly show about 2 00:00:02,720 --> 00:00:03,660 all things PostgreSQL. 3 00:00:03,900 --> 00:00:05,660 I am Michael, founder of pgMustard. 4 00:00:05,660 --> 00:00:08,080 This is my co-host, Nikolay, founder of Postgres.AI. 5 00:00:08,200 --> 00:00:08,940 Hey Nikolay. 6 00:00:09,360 --> 00:00:10,100 Nikolay: Hi Michael. 7 00:00:10,180 --> 00:00:10,980 How are you? 8 00:00:11,260 --> 00:00:11,520 Michael: Good. 9 00:00:11,520 --> 00:00:11,780 How are 10 00:00:11,780 --> 00:00:12,280 you? 11 00:00:12,740 --> 00:00:13,520 Nikolay: Very good. 12 00:00:13,940 --> 00:00:15,200 Michael: Well, yeah, me too. 13 00:00:15,780 --> 00:00:16,840 Haki: Thank you for asking. 14 00:00:18,720 --> 00:00:19,740 Nikolay: Thank you for asking. 15 00:00:20,620 --> 00:00:21,740 Haki: Like I'm here too. 16 00:00:21,740 --> 00:00:25,120 If you just want to keep chat among yourselves, I'll just wait 17 00:00:25,120 --> 00:00:25,620 here. 18 00:00:25,760 --> 00:00:27,880 Nikolay: Look, look, we have a guest today. 19 00:00:28,260 --> 00:00:28,520 Haki: Yeah. 20 00:00:28,520 --> 00:00:29,020 Hi. 21 00:00:29,300 --> 00:00:29,640 Michael: Yeah. 22 00:00:29,640 --> 00:00:34,300 We are delighted to have a guest today and it is the one and only 23 00:00:34,300 --> 00:00:38,600 Haki Benita, technical lead and database enthusiast who also 24 00:00:38,600 --> 00:00:42,520 writes an excellent blog over at hakibenita.com that we've mentioned 25 00:00:42,520 --> 00:00:44,020 many times on the podcast before. 26 00:00:44,020 --> 00:00:46,560 He also gives popular talks and training sessions. 27 00:00:46,680 --> 00:00:48,220 Thank you so much for joining us. 28 00:00:48,220 --> 00:00:48,560 Haki: Thank you 29 00:00:48,560 --> 00:00:49,620 for having me. 30 00:00:50,020 --> 00:00:51,520 Big fan, very excited. 31 00:00:52,760 --> 00:00:53,940 Nikolay: The blog is great. 32 00:00:53,980 --> 00:00:58,140 If someone didn't read it yet, like it's, it's a must-read 33 00:00:58,140 --> 00:01:00,780 for, I think, and long reads, right? 34 00:01:00,780 --> 00:01:02,800 So, It's long reads, but not very frequent. 35 00:01:02,800 --> 00:01:05,860 I guess it's not possible to make them frequent, right? 36 00:01:06,380 --> 00:01:07,860 Haki: Yeah, it's a lot of work. 37 00:01:09,140 --> 00:01:13,440 Nikolay: Funny story is we agreed that our bot will have your 38 00:01:13,440 --> 00:01:16,180 blog post in our knowledge base. 39 00:01:16,180 --> 00:01:16,800 Haki: Yeah, that's right. 40 00:01:16,800 --> 00:01:18,040 Nikolay: And thank you for that. 41 00:01:18,280 --> 00:01:21,740 And like a few days ago, I told the team like, you know, like 42 00:01:21,740 --> 00:01:25,100 this part of our knowledge base was not updated since January, 43 00:01:25,580 --> 00:01:26,520 since alpha release. 44 00:01:26,520 --> 00:01:28,640 And now we're approaching beta release. 45 00:01:28,940 --> 00:01:31,840 And they said, no problem, we'll update and pay attention to 46 00:01:31,840 --> 00:01:37,280 this blog, I said, and then they came and said, we have updated 47 00:01:37,540 --> 00:01:43,180 only one article added since January, but it makes sense because 48 00:01:43,180 --> 00:01:44,800 long reads require a lot of time. 49 00:01:44,800 --> 00:01:45,920 I know it very well. 50 00:01:45,920 --> 00:01:46,280 Right. 51 00:01:46,280 --> 00:01:46,440 Haki: Yeah. 52 00:01:46,440 --> 00:01:50,360 It takes a very long time to produce these types of articles. 53 00:01:50,440 --> 00:01:51,660 Yeah, I know. 54 00:01:51,760 --> 00:01:54,720 I've been writing for a very, very long time and I've been very 55 00:01:54,720 --> 00:01:56,820 surprised about this article. 56 00:01:56,820 --> 00:01:59,940 I'll be honest with you, you know, after you write for some time, 57 00:02:00,060 --> 00:02:03,260 you start to develop like a sense of which ones are going to 58 00:02:03,260 --> 00:02:05,460 do well and be very accepted. 59 00:02:05,540 --> 00:02:08,760 And which ones are going to be like, you know, just one for the, 60 00:02:08,760 --> 00:02:09,580 for the archives. 61 00:02:10,160 --> 00:02:15,360 So, you know, when I wrote stuff like comparing pandas to, to 62 00:02:15,360 --> 00:02:18,840 SQL, I knew that it's going to be a hit because people like pandas. 63 00:02:18,840 --> 00:02:21,600 And if you say pandas is slow, then people are going to hate 64 00:02:21,600 --> 00:02:22,100 you. 65 00:02:22,120 --> 00:02:25,760 And when I wrote this, me and Michael wrote something about hash 66 00:02:25,760 --> 00:02:30,980 indexes a few years back, and I think it's a top result for Postgres 67 00:02:31,020 --> 00:02:33,940 hash index on just about every search engine. 68 00:02:33,940 --> 00:02:36,760 So that one, you know, you, you released that one, you know, that 69 00:02:36,760 --> 00:02:39,600 it's going to do well, but then I released this one. 70 00:02:39,600 --> 00:02:42,340 I said, man, it's so long, such a niche problem. 71 00:02:42,400 --> 00:02:46,400 No, no chance anybody's actually going to read all of that. 72 00:02:46,400 --> 00:02:50,780 And I was very surprised by the amount of responses that I received 73 00:02:51,040 --> 00:02:51,840 to this article. 74 00:02:51,840 --> 00:02:57,300 And I think the most surprising comment that I received on this 75 00:02:57,300 --> 00:03:01,260 article is that a lot of Postgres people didn't know about merge. 76 00:03:02,080 --> 00:03:06,440 Now I came from, I came from Oracle, so I knew all about merge. 77 00:03:06,680 --> 00:03:11,680 And actually when I came to Postgres, I wrote a lot of ETL processes 78 00:03:11,840 --> 00:03:15,860 and I really wanted merge and I didn't have merge, so I had to 79 00:03:15,860 --> 00:03:17,740 learn about INSERT ON CONFLICT. 80 00:03:18,600 --> 00:03:23,000 So a lot of people told me this is the first time I heard about 81 00:03:23,000 --> 00:03:23,500 merge. 82 00:03:23,560 --> 00:03:24,640 Such a cool command. 83 00:03:24,840 --> 00:03:26,640 Nikolay: Because it's new, it's still new. 84 00:03:26,640 --> 00:03:27,040 Haki: I know. 85 00:03:27,040 --> 00:03:28,820 Nikolay: It was 15, right? 86 00:03:28,940 --> 00:03:29,440 15. 87 00:03:29,540 --> 00:03:31,320 So it's super new. 88 00:03:31,940 --> 00:03:36,020 Haki: And also to be honest, INSERT ON CONFLICT is like what 89 00:03:36,020 --> 00:03:36,700 you want. 90 00:03:37,040 --> 00:03:38,260 99% of the cases. 91 00:03:39,160 --> 00:03:44,760 And while merge is nice, I think that ON CONFLICT is, you seem 92 00:03:44,760 --> 00:03:45,060 pissed. 93 00:03:45,060 --> 00:03:46,500 Why, why did I say? 94 00:03:47,780 --> 00:03:50,860 Nikolay: Well, it was you describing your article, why it's bad, 95 00:03:50,860 --> 00:03:51,180 right? 96 00:03:51,180 --> 00:03:52,760 Let's talk about why it's bad. 97 00:03:52,960 --> 00:03:56,100 These gaps in sequences, like a BLOAT created. 98 00:03:56,720 --> 00:03:57,220 Yeah. 99 00:03:57,900 --> 00:03:59,540 This is not what people want. 100 00:03:59,600 --> 00:04:00,100 Right. 101 00:04:00,720 --> 00:04:01,160 I know. 102 00:04:01,160 --> 00:04:02,540 Let's step, let's step back. 103 00:04:02,540 --> 00:04:07,660 Like our, Michael likes us to pronounce the topic. 104 00:04:10,840 --> 00:04:11,920 Haki: I have to tell you something. 105 00:04:11,920 --> 00:04:12,640 I have to tell you something. 106 00:04:12,640 --> 00:04:13,140 It's funny. 107 00:04:13,140 --> 00:04:13,320 Okay. 108 00:04:13,320 --> 00:04:14,200 I have to tell you something. 109 00:04:14,200 --> 00:04:18,440 So when I talked with Michael about doing this show and I asked 110 00:04:18,440 --> 00:04:20,140 him, okay, let's do it. 111 00:04:20,140 --> 00:04:23,860 Can you give me like a rough outline of how it's going to go? 112 00:04:23,920 --> 00:04:27,600 And he told me, I can tell you how it's going to go, but to be 113 00:04:27,600 --> 00:04:30,960 honest, if Nikolay is going to be on the show, then I can't promise 114 00:04:30,960 --> 00:04:31,460 anything. 115 00:04:33,780 --> 00:04:35,360 Michael: I can't promise any structure. 116 00:04:35,660 --> 00:04:38,760 Nikolay: Well, I see you are the same type of person, actually. 117 00:04:38,800 --> 00:04:39,240 Haki: I don't know. 118 00:04:39,240 --> 00:04:41,400 You delivered like on the first 5 minutes. 119 00:04:41,400 --> 00:04:43,440 Nikolay: Don't follow the agenda, right? 120 00:04:43,580 --> 00:04:47,360 So you said that you were surprised this topic would be popular, 121 00:04:47,360 --> 00:04:49,080 but how come? It's super popular. 122 00:04:49,080 --> 00:04:53,100 It's like in computer science, particularly in databases, find 123 00:04:53,100 --> 00:04:56,760 or create, this is like a super common pattern, right? 124 00:04:56,820 --> 00:05:00,360 And the fact that in Postgres, the solutions have so many pros 125 00:05:00,360 --> 00:05:03,740 and cons, like it's super interesting because everyone needs 126 00:05:03,740 --> 00:05:04,440 it actually. 127 00:05:04,540 --> 00:05:04,920 Yeah. 128 00:05:04,920 --> 00:05:06,900 You build something, you want this, right? 129 00:05:07,280 --> 00:05:07,760 Haki: Yeah. 130 00:05:07,760 --> 00:05:09,300 I'm going to tell you a secret. 131 00:05:09,600 --> 00:05:14,620 After I published this article, I actually discovered that there 132 00:05:14,620 --> 00:05:19,400 are still situations where you get very unpredictable results. 133 00:05:20,280 --> 00:05:20,740 Okay. 134 00:05:20,740 --> 00:05:24,720 And, and, and, and I've done some experimenting in the past 2 135 00:05:24,720 --> 00:05:28,260 weeks based on comments that I received and I haven't cracked 136 00:05:28,260 --> 00:05:28,940 it yet. 137 00:05:30,520 --> 00:05:31,020 Yeah. 138 00:05:31,280 --> 00:05:32,720 It gets a lot more complicated. 139 00:05:33,700 --> 00:05:35,460 And I'll even tell you another secret. 140 00:05:35,540 --> 00:05:40,380 There's a very, in my opinion, an unexpected difference between 141 00:05:40,380 --> 00:05:45,160 the way merge and insert on conflict behave under different circumstances. 142 00:05:46,640 --> 00:05:49,440 But, you know, we promised Michael that we'll describe the topic 143 00:05:49,440 --> 00:05:51,980 before we actually dive into the details. 144 00:05:52,840 --> 00:05:53,340 Nikolay: Right. 145 00:05:54,000 --> 00:05:55,580 Let's name the topic. 146 00:05:55,920 --> 00:05:56,120 Yeah. 147 00:05:56,120 --> 00:05:56,620 Yeah. 148 00:05:56,820 --> 00:05:58,780 Michael: Well, I was interested in your thoughts on this actually, 149 00:05:58,780 --> 00:06:02,160 because I feel like you deliberately called your blog post get 150 00:06:02,160 --> 00:06:02,880 or create. 151 00:06:03,180 --> 00:06:06,360 Whereas from the database side of it, I've always thought of 152 00:06:06,360 --> 00:06:08,300 it as like INSERT or SELECT. 153 00:06:08,300 --> 00:06:11,120 And I think Nikolay's called it that, but in the past in like 154 00:06:11,120 --> 00:06:12,440 a how-to guide and things. 155 00:06:12,440 --> 00:06:16,020 So we'll link up the blog post and we'll link up Nikolay's how 156 00:06:16,020 --> 00:06:18,480 to, and there's like a few other things. 157 00:06:18,620 --> 00:06:21,340 I think one of the things you're referencing about how it's become 158 00:06:21,340 --> 00:06:23,940 even more complicated in the last few weeks was like you linked 159 00:06:23,940 --> 00:06:28,540 me to a great answer by Erwin Brandstetter on Stack Overflow 160 00:06:28,740 --> 00:06:31,260 that discusses this as well, but it's a surprise. 161 00:06:31,260 --> 00:06:32,620 It sounds really simple, right? 162 00:06:32,720 --> 00:06:35,360 SELECT or INSERT, get or create. 163 00:06:35,360 --> 00:06:37,780 It seems such a simple piece of functionality. 164 00:06:37,900 --> 00:06:43,260 And in small, low traffic systems, it can be like you can, like 165 00:06:43,260 --> 00:06:47,180 any newbie developer could come up with a solution to this problem. 166 00:06:47,400 --> 00:06:51,960 It just gets complicated when you have to factor in concurrency 167 00:06:52,420 --> 00:06:55,240 and MVCC implementation and things. 168 00:06:55,240 --> 00:06:57,180 So, yeah, I loved your post. 169 00:06:57,180 --> 00:07:00,040 I found it really interesting how complicated this could get, 170 00:07:00,040 --> 00:07:02,560 But yeah, maybe we should start simple and like, it'd be great 171 00:07:02,560 --> 00:07:04,800 to hear a little bit about like you, you both said it's quite 172 00:07:04,800 --> 00:07:07,540 a common thing that you come, like you have to implement it quite 173 00:07:07,540 --> 00:07:08,040 often. 174 00:07:08,560 --> 00:07:10,020 I haven't had to that often. 175 00:07:10,020 --> 00:07:13,040 Like I've got a couple of occasions, but not maybe not as often 176 00:07:13,040 --> 00:07:13,380 as you. 177 00:07:13,380 --> 00:07:15,640 So I'd be interested to hear like where you're coming across 178 00:07:15,640 --> 00:07:16,140 this. 179 00:07:16,400 --> 00:07:16,900 Haki: Okay. 180 00:07:16,960 --> 00:07:21,060 So my use case was very similar to what I actually implemented 181 00:07:21,100 --> 00:07:25,680 in the article because I had this organization table and we had 182 00:07:25,680 --> 00:07:30,260 members and we wanted to have users be able to tag members with 183 00:07:30,260 --> 00:07:33,560 their own tags and we wanted to encourage reuse of tags. 184 00:07:33,560 --> 00:07:35,360 Otherwise, they don't make much sense. 185 00:07:35,500 --> 00:07:38,720 So we've set up this very simple HTML form. 186 00:07:38,720 --> 00:07:40,680 Now HTML forms are not SPAs. 187 00:07:40,680 --> 00:07:42,900 They're not, they're not very complicated. 188 00:07:43,100 --> 00:07:44,340 You can send JSON. 189 00:07:44,440 --> 00:07:49,700 You just send a list of names, like from a radio selection or like 190 00:07:49,700 --> 00:07:50,960 an autocomplete, whatever. 191 00:07:51,740 --> 00:07:55,460 So at the end of the day, the server receives a list of strings 192 00:07:56,240 --> 00:07:59,620 and it wants to set these tags on the member. 193 00:08:00,040 --> 00:08:04,540 Now, if the tag exists, you want to reuse the tag. 194 00:08:04,700 --> 00:08:07,920 And if the tag does not exist, you want to create a new tag and 195 00:08:07,920 --> 00:08:08,800 get the ID. 196 00:08:09,060 --> 00:08:13,380 Because the next step would be to get all the IDs and put them in 197 00:08:13,380 --> 00:08:16,560 the table associating tags with members, right? 198 00:08:16,560 --> 00:08:23,560 So that's the first part where you have a list of tag names and 199 00:08:23,560 --> 00:08:26,340 you want to get or create tags. 200 00:08:26,720 --> 00:08:28,920 This is where I came to this idea. 201 00:08:29,140 --> 00:08:32,920 Now, the thing that surprised me is that now this is not a very 202 00:08:32,920 --> 00:08:34,840 high traffic part of the application. 203 00:08:35,140 --> 00:08:39,140 I could have done the brute force approach would have been just 204 00:08:39,140 --> 00:08:39,640 fine. 205 00:08:40,080 --> 00:08:44,500 But you know, I wrote some unit testing and 1 of the tests was, 206 00:08:44,500 --> 00:08:48,380 let's see what happens when you just have an existing tag, you 207 00:08:48,380 --> 00:08:49,900 want to make sure that it's reused. 208 00:08:50,280 --> 00:08:56,180 So I use INSERT ON CONFLICT DO NOTHING with RETURNING *. 209 00:08:56,840 --> 00:09:02,480 So I expected that if I insert, for example, 2 tags and 1 of 210 00:09:02,480 --> 00:09:06,840 them already exists, I expected to get 2 tags in return. 211 00:09:07,800 --> 00:09:10,660 But in fact, what I got was just 1 tag. 212 00:09:11,060 --> 00:09:14,320 So this was very strange to me. 213 00:09:14,600 --> 00:09:18,220 So at this point I started investigating, you know, and starting 214 00:09:18,220 --> 00:09:20,080 to explore why this is happening. 215 00:09:20,080 --> 00:09:25,460 And in fact, the first thing that I thought about was let's do 216 00:09:25,560 --> 00:09:26,960 a meaningless UPDATE. 217 00:09:26,960 --> 00:09:32,240 Like instead of doing ON CONFLICT DO NOTHING, I did ON CONFLICT 218 00:09:32,980 --> 00:09:35,820 SET ID = EXCLUDED.ID. 219 00:09:36,100 --> 00:09:40,300 Like let's fool the database into thinking that this tag was 220 00:09:40,520 --> 00:09:41,020 modified. 221 00:09:41,160 --> 00:09:44,640 So I get, get that in return, but it really bugged me because 222 00:09:44,640 --> 00:09:48,080 it's very like, it's a very ugly solution. 223 00:09:48,080 --> 00:09:51,180 Why would I want to update something for no reason just to get 224 00:09:51,180 --> 00:09:51,840 it back? 225 00:09:52,080 --> 00:09:55,220 So this is where, you know, all the, the different scenarios 226 00:09:55,440 --> 00:09:56,360 started to uncover. 227 00:09:56,360 --> 00:09:59,740 And as I tested farther and farther, I came to the conclusion 228 00:09:59,760 --> 00:10:01,740 like, Hell man, why is this so complicated? 229 00:10:02,140 --> 00:10:05,140 I mean, this, this is what database and applications do. 230 00:10:05,280 --> 00:10:06,580 This should be so simple. 231 00:10:06,580 --> 00:10:07,940 Why is this so complicated? 232 00:10:08,520 --> 00:10:13,760 And then I started digging and 1 of the places that I eventually 233 00:10:13,840 --> 00:10:16,480 arrived was Django. 234 00:10:16,900 --> 00:10:21,780 Django is a web framework and there's a, an implementation of 235 00:10:21,780 --> 00:10:23,120 get_or_create(). 236 00:10:23,140 --> 00:10:29,760 And what Django is doing, they try to fetch the record. 237 00:10:30,260 --> 00:10:32,060 If they find it, they return it. 238 00:10:32,060 --> 00:10:34,900 If they don't find it, they try to insert. 239 00:10:35,740 --> 00:10:36,240 Right. 240 00:10:36,740 --> 00:10:41,060 But then, and that's like the funny part, the non-intuitive part, 241 00:10:41,280 --> 00:10:45,700 then they actually handle an integrity, unique constraint violation, 242 00:10:46,320 --> 00:10:48,100 and then they try to get it again. 243 00:10:48,660 --> 00:10:52,580 This is the select, insert, select, but then it gets even more 244 00:10:52,580 --> 00:10:57,080 complicated because if you have a unique constraint violation 245 00:10:57,160 --> 00:11:00,540 inside a transaction, it aborts the transaction, right? 246 00:11:01,060 --> 00:11:05,440 You need to wrap the second insert in another transaction. 247 00:11:06,820 --> 00:11:07,320 Right. 248 00:11:07,360 --> 00:11:08,260 Nikolay: Or use subtransactions. 249 00:11:08,680 --> 00:11:12,360 I think Django by default uses subtransactions, which is a very 250 00:11:12,360 --> 00:11:14,880 bad idea, actually, but we will talk about it later. 251 00:11:15,040 --> 00:11:18,160 Haki: The reason that he uses subtransactions, and I know you're 252 00:11:18,160 --> 00:11:21,260 a very big fan of subtransaction, you use them all the time. 253 00:11:21,260 --> 00:11:23,100 You encourage everybody to use subtransactions. 254 00:11:23,920 --> 00:11:27,320 You wrote a very long blog post about why they are absolutely 255 00:11:27,480 --> 00:11:29,480 great and you should use them all the time. 256 00:11:30,360 --> 00:11:34,720 But the reason that you do that in Django, the reason that they're 257 00:11:34,720 --> 00:11:39,620 doing that is because if you happen to call this function inside 258 00:11:39,620 --> 00:11:45,720 a transaction of your own, and if you reach a unique constraint 259 00:11:45,720 --> 00:11:48,580 violation, it gets your transaction aborted. 260 00:11:48,680 --> 00:11:49,820 So that's a problem. 261 00:11:50,000 --> 00:11:53,360 So the only way that Django can make sure that they don't get 262 00:11:53,360 --> 00:11:57,660 your transaction aborted is to execute this part in a subtransaction 263 00:11:57,780 --> 00:11:58,700 of its own. 264 00:11:59,440 --> 00:12:02,800 Now There's also another very interesting thing that happens 265 00:12:02,800 --> 00:12:03,300 here. 266 00:12:03,340 --> 00:12:05,860 And this is also something that I mentioned in the article. 267 00:12:06,580 --> 00:12:10,020 Python encourages asking for forgiveness. 268 00:12:11,500 --> 00:12:15,800 So in Python, the idiom says that you should try something and 269 00:12:15,800 --> 00:12:17,580 then handle all the possible exceptions. 270 00:12:18,460 --> 00:12:21,660 So trying to insert a record and then handling the unique constraint 271 00:12:21,660 --> 00:12:25,520 violation is actually consistent with how you're expected to 272 00:12:25,520 --> 00:12:26,700 do things in Python. 273 00:12:27,180 --> 00:12:31,400 But it kind of conflicts with the way Postgres handles this situation. 274 00:12:31,500 --> 00:12:36,300 Because in Postgres, when you have an exception inside a transaction, 275 00:12:36,300 --> 00:12:40,160 it aborts the transaction, which is not a very good thing. 276 00:12:40,800 --> 00:12:44,380 So the approaches between how you would do things in Postgres 277 00:12:44,380 --> 00:12:47,660 and how you would do things in, in Python kind of clash in 278 00:12:47,660 --> 00:12:48,360 this case. 279 00:12:48,420 --> 00:12:49,980 I thought it was very interesting. 280 00:12:50,500 --> 00:12:54,360 So, you know, I explored different approaches, like what would 281 00:12:54,360 --> 00:12:58,240 be the equivalent of asking for forgiveness in Postgres, what 282 00:12:58,240 --> 00:13:01,560 would be the equivalent of looking before you leap, check all 283 00:13:01,560 --> 00:13:02,940 the preconditions in advance. 284 00:13:04,460 --> 00:13:08,800 So, yeah, it turned out to be way more complicated than I anticipated. 285 00:13:10,320 --> 00:13:11,880 Nikolay: Yeah, that's interesting. 286 00:13:11,980 --> 00:13:15,660 And you explored several properties, right? 287 00:13:16,020 --> 00:13:20,500 Bloat, concurrency, constraint and idempotency, right? 288 00:13:20,500 --> 00:13:21,880 Haki: Idempotency, yeah. 289 00:13:23,080 --> 00:13:24,640 Nikolay: So I'm very curious. 290 00:13:25,360 --> 00:13:29,140 I saw some parts of benchmarks, but you decided not to include 291 00:13:29,140 --> 00:13:33,920 the performance to the resulting table when you compared methods. 292 00:13:34,280 --> 00:13:35,040 Why so? 293 00:13:35,380 --> 00:13:37,160 Performance is also interesting, right? 294 00:13:37,540 --> 00:13:39,060 Haki: Performance is interesting. 295 00:13:39,100 --> 00:13:43,580 And I've seen some articles that do this from a performance 296 00:13:43,580 --> 00:13:44,440 point of view. 297 00:13:45,040 --> 00:13:48,220 In my situation, performance was not a concern. 298 00:13:48,820 --> 00:13:52,960 I was more focused on getting the functional attributes that 299 00:13:52,960 --> 00:13:57,400 I wanted, like idempotency, for example, was a lot more important 300 00:13:57,400 --> 00:13:59,280 to me than making it fast. 301 00:13:59,280 --> 00:14:02,820 Because at the end of the day, you don't UPDATE lots of tags. 302 00:14:03,240 --> 00:14:05,580 You probably set no more than 5. 303 00:14:06,100 --> 00:14:08,220 So performance was not a concern. 304 00:14:08,480 --> 00:14:11,420 I did want to focus on the functional attributes. 305 00:14:11,720 --> 00:14:13,100 Also, it got pretty long. 306 00:14:13,100 --> 00:14:16,080 So at some point you need to let something go, you know. 307 00:14:16,860 --> 00:14:17,320 Nikolay: Right. 308 00:14:17,320 --> 00:14:17,820 Yeah. 309 00:14:18,220 --> 00:14:22,460 Let's maybe talk about your favorite method right now and for 310 00:14:22,460 --> 00:14:22,860 future. 311 00:14:22,860 --> 00:14:24,300 For future, I guess it's MERGE, right? 312 00:14:24,300 --> 00:14:27,720 Because in Postgres 17 it will have RETURNING clause. 313 00:14:27,720 --> 00:14:28,880 Actually, I didn't know. 314 00:14:29,060 --> 00:14:33,340 And when I read about it in your blog post, I immediately thought, 315 00:14:33,340 --> 00:14:37,740 oh, this is a candidate to be one of my favorite additions to Postgres 316 00:14:37,740 --> 00:14:38,240 17. 317 00:14:38,380 --> 00:14:40,280 I somehow overlooked it. 318 00:14:40,360 --> 00:14:41,180 So it's great. 319 00:14:41,780 --> 00:14:44,840 MERGE was added to Postgres 15. 320 00:14:45,480 --> 00:14:48,200 It was being developed for more than 10 years, I think. 321 00:14:49,080 --> 00:14:50,460 It was a huge story. 322 00:14:51,220 --> 00:14:53,860 But it lacks RETURNING in 2015 and 2016. 323 00:14:53,880 --> 00:14:58,240 Now in 2017, to be honest, it's receiving RETURNING. 324 00:14:58,740 --> 00:15:03,720 It looks like all your checkboxes are green, right? 325 00:15:07,460 --> 00:15:11,980 You will choose this if you already was on, we are on Postgres 326 00:15:12,240 --> 00:15:16,020 17, but before 17, what's your favorite method? 327 00:15:16,220 --> 00:15:20,640 Haki: Well, INSERT ON CONFLICT, DO NOTHING with a UNION is currently 328 00:15:20,680 --> 00:15:22,320 my way to go. 329 00:15:22,680 --> 00:15:28,080 And I expect it to remain my go-to approach, mostly because of 330 00:15:28,080 --> 00:15:28,580 habit. 331 00:15:29,340 --> 00:15:35,320 And also because I experimented with the new MERGE under different 332 00:15:35,320 --> 00:15:37,360 circumstances in Postgres 17. 333 00:15:38,140 --> 00:15:43,080 And it turns out that MERGE can actually fail with unique constraint 334 00:15:43,080 --> 00:15:43,580 violation. 335 00:15:45,520 --> 00:15:47,580 So I found it unexpected. 336 00:15:48,480 --> 00:15:51,800 I don't know, because I don't know much about how these two are 337 00:15:51,820 --> 00:15:54,640 different in the underlying implementation, but I'm guessing 338 00:15:55,080 --> 00:15:57,420 that they are not implemented the same way. 339 00:15:57,700 --> 00:15:59,960 Nikolay: And we talk about it commitment level. 340 00:15:59,960 --> 00:16:00,560 Haki: Yes, of course. 341 00:16:00,560 --> 00:16:02,320 Nikolay: I think it also matters, right? 342 00:16:02,320 --> 00:16:03,220 It's the lowest. 343 00:16:03,900 --> 00:16:04,400 Haki: Yeah. 344 00:16:04,440 --> 00:16:09,900 So, so after I published this article, I, some reader reached 345 00:16:09,900 --> 00:16:13,080 out to me and he said, I really liked your posts and everything 346 00:16:13,080 --> 00:16:16,780 is very comprehensive, blah, blah, blah, But there is one race 347 00:16:16,780 --> 00:16:20,420 condition that can still cause inconsistent results. 348 00:16:20,900 --> 00:16:24,480 So basically, if you have two sessions inserting the exact same 349 00:16:24,480 --> 00:16:28,880 tag at the exact same time, then you can end up with a situation 350 00:16:29,100 --> 00:16:35,040 where using INSERT ON CONFLICT, you would not get idempotency. 351 00:16:35,500 --> 00:16:42,920 meaning the first statement would return the tag, but the second 352 00:16:42,920 --> 00:16:44,040 would return nothing. 353 00:16:45,940 --> 00:16:52,000 And the reason for that is when you start the first transaction, 354 00:16:52,660 --> 00:16:54,360 you INSERT the tag A. 355 00:16:55,200 --> 00:17:03,060 So tag A does not exist and INSERT, INSERT the tag, right? 356 00:17:03,780 --> 00:17:06,700 And then you get that in return, but then you don't COMMIT. 357 00:17:07,640 --> 00:17:12,560 Now another session starts, the transaction begins, and now you 358 00:17:12,560 --> 00:17:13,820 do the exact same thing. 359 00:17:13,820 --> 00:17:19,900 You do INSERT tag A ON CONFLICT DO NOTHING, RETURNING *, and 360 00:17:19,900 --> 00:17:22,660 then you SELECT from the table and SELECT from the RETURNING. 361 00:17:23,160 --> 00:17:27,560 What's happening now is interesting, because right now, the row 362 00:17:27,560 --> 00:17:28,300 is LOCKED. 363 00:17:28,660 --> 00:17:31,900 So, the second session hangs. 364 00:17:32,620 --> 00:17:35,440 So now you COMMIT the first session. 365 00:17:36,180 --> 00:17:39,860 So the second session at this point is going to return nothing. 366 00:17:41,180 --> 00:17:45,820 And the reason is INSERT ON CONFLICT LOCKED. 367 00:17:46,020 --> 00:17:50,600 It encountered a unique constraint violation, so the row was 368 00:17:50,600 --> 00:17:51,420 not inserted. 369 00:17:51,800 --> 00:17:55,900 But then when you tried to SELECT from the table, it found nothing 370 00:17:55,960 --> 00:18:01,520 because the snapshot is older than the other transaction. 371 00:18:03,160 --> 00:18:07,900 So this is a scenario where, where you get, you don't get idempotency. 372 00:18:08,540 --> 00:18:13,500 You expect to get the same output in both cases, but you don't. 373 00:18:13,740 --> 00:18:17,140 And the more interesting thing is if you do the exact same exercise 374 00:18:17,160 --> 00:18:19,640 with MERGE, you get different results. 375 00:18:19,640 --> 00:18:20,600 What do you get? 376 00:18:20,600 --> 00:18:21,520 Can you guess? 377 00:18:22,180 --> 00:18:23,600 I found it to be surprising. 378 00:18:24,620 --> 00:18:27,380 If you do the exact same experiment with MERGE, you are going 379 00:18:27,380 --> 00:18:28,860 to get unique constraint violation. 380 00:18:31,720 --> 00:18:36,220 Even though you can say in the MERGE clause, you can say ON CONFLICT, 381 00:18:36,220 --> 00:18:38,500 DO NOTHING WHEN MATCHED, DO NOTHING. 382 00:18:38,940 --> 00:18:41,600 You are still going to get unique constraint violation. 383 00:18:42,080 --> 00:18:47,780 So this raises a question of whether MERGE is really, or I would, 384 00:18:47,780 --> 00:18:52,240 I wouldn't want to say something like, is it safe in concurrent, 385 00:18:52,240 --> 00:18:56,280 highly concurrent reloads, but it doesn't do what it promises. 386 00:18:56,280 --> 00:18:59,480 Better understanding of how MERGE handles these types of situations, 387 00:18:59,620 --> 00:19:00,260 you know. 388 00:19:01,260 --> 00:19:01,500 Nikolay: Yeah. 389 00:19:01,500 --> 00:19:06,300 It shouldn't produce unique constraint violation in read committed, 390 00:19:06,300 --> 00:19:07,120 but it does. 391 00:19:08,680 --> 00:19:11,760 But it should be easy to check based on what you say, like if 392 00:19:11,760 --> 00:19:17,320 you just do pgbench multiple sessions in parallel doing the same 393 00:19:17,320 --> 00:19:18,100 thing, right? 394 00:19:18,100 --> 00:19:19,580 Haki: It's very easily reproducible. 395 00:19:19,740 --> 00:19:22,940 You just need 2 terminal windows and you can immediately replicate 396 00:19:23,000 --> 00:19:23,260 it. 397 00:19:23,260 --> 00:19:27,240 I, the thing I found to be, I would say disturbing. 398 00:19:27,540 --> 00:19:27,880 Yeah. 399 00:19:27,880 --> 00:19:31,960 And I'm using air quotes for those of you just listening is that 400 00:19:31,960 --> 00:19:36,100 when you do MERGE and you set ON MATCH, DO NOTHING, you don't 401 00:19:36,100 --> 00:19:38,040 expect to get Unique constraint violation. 402 00:19:38,620 --> 00:19:38,940 Right. 403 00:19:38,940 --> 00:19:43,260 If I wrote this statement in my code, I would not handle any 404 00:19:43,260 --> 00:19:44,120 Constraint violation. 405 00:19:45,060 --> 00:19:45,320 Okay. 406 00:19:45,320 --> 00:19:49,280 Because if I'm inserting into a row and I know that it can raise, 407 00:19:49,280 --> 00:19:52,840 I would handle the situation and do whatever I need to do, but 408 00:19:52,840 --> 00:19:58,120 I would not expect to get integrity error when I explicitly handle 409 00:19:58,120 --> 00:20:01,100 the case where there is a match. 410 00:20:01,560 --> 00:20:03,220 So I found this to be surprising. 411 00:20:03,240 --> 00:20:06,760 So to answer your previous question, what would I use now? 412 00:20:07,240 --> 00:20:11,180 And that's a, that's a tough choice between you don't get the 413 00:20:11,180 --> 00:20:13,340 expected result to you get an exception. 414 00:20:15,060 --> 00:20:17,360 You know, when I'm thinking about right now, I think that it 415 00:20:17,360 --> 00:20:19,020 kind of makes sense to get the exception. 416 00:20:20,240 --> 00:20:20,740 Right? 417 00:20:20,820 --> 00:20:21,060 Nikolay: Yeah. 418 00:20:21,060 --> 00:20:25,960 Well, I guess we're moving from the end of article backwards, 419 00:20:26,000 --> 00:20:26,420 right? 420 00:20:26,420 --> 00:20:31,180 So much, this, the problem you describe, is it discussed somewhere? 421 00:20:31,360 --> 00:20:35,860 Like, do you know, like, like this behavior was discussed in 422 00:20:35,860 --> 00:20:37,310 mailing lists or somewhere, no? 423 00:20:37,310 --> 00:20:38,000 Haki: I don't know. 424 00:20:38,000 --> 00:20:40,840 Maybe I'm guessing that it might have, but I haven't seen any. 425 00:20:40,840 --> 00:20:43,220 And it also, as you said, it's kind of new in Postgres. 426 00:20:43,280 --> 00:20:46,640 So it's possible that some of the people that worked on it didn't 427 00:20:46,640 --> 00:20:49,200 have enough time to actually document it. 428 00:20:49,200 --> 00:20:52,700 I wouldn't say document, but maybe write these types of articles 429 00:20:52,720 --> 00:20:56,140 on it, analyzing how it behaves under different circumstances. 430 00:20:57,040 --> 00:20:57,440 Nikolay: Yeah. 431 00:20:57,440 --> 00:20:58,240 I'm curious. 432 00:20:58,260 --> 00:20:59,720 I think this should be documented. 433 00:20:59,820 --> 00:21:01,880 Maybe, maybe it's actually documented. 434 00:21:01,940 --> 00:21:03,980 We should check, But it's super interesting. 435 00:21:04,080 --> 00:21:06,780 I never used MERGE in production, never, ever. 436 00:21:07,360 --> 00:21:08,760 But yeah, so what... 437 00:21:09,520 --> 00:21:14,840 Well, honestly, most production systems are still behind a lot 438 00:21:14,840 --> 00:21:16,360 in terms of major version. 439 00:21:16,860 --> 00:21:21,560 But moving backwards, in SERP, on conflict, do nothing or do 440 00:21:21,560 --> 00:21:22,040 UPDATE. 441 00:21:22,040 --> 00:21:27,340 This is interesting because we said our goal is find or create 442 00:21:27,340 --> 00:21:30,920 or get or create, right? 443 00:21:31,020 --> 00:21:34,960 But when you say, let's also UPDATE, it's like a third layer 444 00:21:34,960 --> 00:21:36,240 of logic, right? 445 00:21:36,680 --> 00:21:41,440 Find or create, but if you found also UPDATE, like it's, it's 446 00:21:41,440 --> 00:21:42,180 more complicated. 447 00:21:42,180 --> 00:21:45,020 I like it, but it's already a different task, right? 448 00:21:45,020 --> 00:21:45,520 Haki: Yeah. 449 00:21:45,860 --> 00:21:49,640 Well, you know, when I wrote lots of ETL, I don't know, 15 years 450 00:21:49,640 --> 00:21:53,100 ago in, in Oracle, I used MERGE all the time. 451 00:21:53,460 --> 00:21:56,420 It put like a third hand every time I needed to do anything, 452 00:21:56,420 --> 00:22:00,040 I would use MERGE because when you do ETL processes, you're basically 453 00:22:00,060 --> 00:22:03,560 taking data from one place and you want to sync it to some kind 454 00:22:03,560 --> 00:22:04,260 of target. 455 00:22:04,440 --> 00:22:06,360 So you use MERGE all the time. 456 00:22:06,620 --> 00:22:10,340 In Postgres, I kind of gotten used to not using it. 457 00:22:10,760 --> 00:22:16,460 And also some may also claim that the whole ETL, the way that 458 00:22:16,460 --> 00:22:20,220 we do ETL now is kind of different than the way we used to do 459 00:22:20,220 --> 00:22:21,860 ETL 15 years ago. 460 00:22:21,940 --> 00:22:25,060 A lot of it is the same, but some of it is still kind of different. 461 00:22:25,320 --> 00:22:27,340 Yeah, I think it's interesting. 462 00:22:27,380 --> 00:22:31,020 And I, and hopefully when more people adopt MERGE, I know that 463 00:22:31,020 --> 00:22:33,960 it was a very desirable feature, right? 464 00:22:34,200 --> 00:22:35,780 A lot of people wanted that. 465 00:22:37,060 --> 00:22:37,560 Right. 466 00:22:37,660 --> 00:22:37,900 Nikolay: People 467 00:22:37,900 --> 00:22:39,840 Haki: were very excited when it came out. 468 00:22:39,840 --> 00:22:41,060 It was all in there. 469 00:22:41,060 --> 00:22:44,940 What I anticipate, what I expect, most in version, you know, 470 00:22:45,060 --> 00:22:45,980 the MERGE command. 471 00:22:46,800 --> 00:22:49,760 So hopefully we're going to see some people doing interesting 472 00:22:49,760 --> 00:22:50,780 stuff with MERGE. 473 00:22:51,760 --> 00:22:51,960 Nikolay: Yeah. 474 00:22:51,960 --> 00:22:56,580 And I was surprised to see, so there is a difference between, in 475 00:22:56,580 --> 00:22:59,760 certain ON CONFLICT DO NOTHING and DO UPDATE in the table you provided 476 00:22:59,760 --> 00:23:04,860 in the end of the article and DO UPDATE, there is a like red 477 00:23:04,860 --> 00:23:09,400 cross in the column of bloat, meaning that it has bloat issues, 478 00:23:09,400 --> 00:23:11,020 so obviously update, okay. 479 00:23:11,160 --> 00:23:16,500 But also you have a green checkbox for INSERT ON CONFLICT DO NOTHING. 480 00:23:16,720 --> 00:23:21,140 The thing is that when we created our bot, we also used INSERT 481 00:23:21,140 --> 00:23:23,640 ON CONFLICT DO NOTHING for the knowledge base. 482 00:23:23,640 --> 00:23:28,440 For example, if your article gets inserted, it searches if this 483 00:23:28,440 --> 00:23:29,940 URL is known already. 484 00:23:31,220 --> 00:23:33,180 And if it's known already, DO NOTHING. 485 00:23:33,180 --> 00:23:35,420 We decided not to apply update yet. 486 00:23:36,280 --> 00:23:42,080 So I was surprised to see you concluded that there is no bloat 487 00:23:42,080 --> 00:23:46,980 issues because we had a lot of activity, a lot of data to insert 488 00:23:47,420 --> 00:23:49,140 and it suffers from bloat. 489 00:23:49,700 --> 00:23:50,660 We have gaps. 490 00:23:51,580 --> 00:23:55,580 So if you have a collision, right? 491 00:23:55,680 --> 00:23:58,460 So you insert something, you try to insert and DO NOTHING. 492 00:23:59,340 --> 00:24:01,740 It actually generates that tuple, I think. 493 00:24:01,740 --> 00:24:02,240 No, 494 00:24:02,480 --> 00:24:05,040 Haki: No, I think that, first of all, gaps, 495 00:24:05,200 --> 00:24:05,980 Nikolay: gaps for sure. 496 00:24:05,980 --> 00:24:06,380 I just 497 00:24:06,380 --> 00:24:06,760 Haki: checked it. 498 00:24:06,760 --> 00:24:07,740 Nikolay: Gaps for sure. 499 00:24:07,800 --> 00:24:08,040 Haki: Yeah. 500 00:24:08,040 --> 00:24:09,060 Gaps for sure. 501 00:24:09,160 --> 00:24:09,720 And okay. 502 00:24:09,720 --> 00:24:10,920 I know what you're talking about. 503 00:24:10,920 --> 00:24:15,140 You, you, you're talking about the, the issue of bloat, right? 504 00:24:15,140 --> 00:24:19,540 So this is also a very interesting topic, which I briefly mentioned, 505 00:24:19,540 --> 00:24:22,940 but I think that this is also something that some people commented 506 00:24:22,940 --> 00:24:26,720 about that they weren't aware of the fact that it causes bloat. 507 00:24:26,920 --> 00:24:30,140 So apparently in Postgres, when you have unique constraint violation, 508 00:24:30,300 --> 00:24:34,340 it can cause bloat because the way it works is that Postgres 509 00:24:34,440 --> 00:24:39,860 tries to, it basically inserts the row into the table and then 510 00:24:39,860 --> 00:24:43,640 it checks that there are no unique constraint violations, right? 511 00:24:43,640 --> 00:24:46,560 And if there is a unique constraint violation, this row is marked 512 00:24:46,560 --> 00:24:48,460 as dead, which causes bloat. 513 00:24:48,960 --> 00:24:53,680 So this is what's happening if you rely, heavily rely on catching 514 00:24:53,680 --> 00:24:54,860 unique constraint violation. 515 00:24:55,240 --> 00:24:59,740 However, however, however, and this is also in the original RDS 516 00:24:59,760 --> 00:25:02,720 article, which is how I found out about it. 517 00:25:02,720 --> 00:25:06,080 It was also very unexpected that unique constraint violation 518 00:25:06,080 --> 00:25:07,020 would cause bloat. 519 00:25:07,200 --> 00:25:09,900 But according to the article and according to the benchmarks 520 00:25:09,960 --> 00:25:14,040 that I've done, when you use INSERT ON CONFLICT, it uses a different 521 00:25:14,040 --> 00:25:20,640 mechanism that can check in advance for collisions, which prevents 522 00:25:20,640 --> 00:25:21,140 bloat. 523 00:25:21,600 --> 00:25:26,520 So I'm pretty confident that INSERT ON CONFLICT DO NOTHING cause 524 00:25:26,520 --> 00:25:30,960 bloat, which is a big plus for DO NOTHING. 525 00:25:32,380 --> 00:25:36,340 And also, you know, we, we talked before about the difference 526 00:25:36,340 --> 00:25:39,300 between how you do things in Python, like asking for forgiveness 527 00:25:39,840 --> 00:25:41,640 versus look before you leap. 528 00:25:41,640 --> 00:25:45,940 So in Python, the pattern that is encouraged, basically try to 529 00:25:45,940 --> 00:25:49,200 do something and then handle the exception can end up causing 530 00:25:49,200 --> 00:25:51,660 lots of bloat, which is a problem. 531 00:25:51,960 --> 00:25:55,080 So if you have a process with lots of collisions where you actually 532 00:25:55,080 --> 00:25:58,380 rely on unique constraint violation, this is something that you 533 00:25:58,380 --> 00:25:59,740 need to think about. 534 00:26:00,180 --> 00:26:00,600 Nikolay: Right. 535 00:26:00,600 --> 00:26:01,840 So yeah, I double-checked. 536 00:26:01,840 --> 00:26:02,440 You're right. 537 00:26:02,440 --> 00:26:05,460 No bloat, but the sequence gaps are happening. 538 00:26:05,580 --> 00:26:06,960 We have a sequence gap. 539 00:26:07,600 --> 00:26:11,520 Haki: You know, there was a period where I liked to ask in a job 540 00:26:11,520 --> 00:26:14,000 interviews, how would you implement gapless sequences? 541 00:26:15,600 --> 00:26:17,780 Well, have you ever talked about this? 542 00:26:17,780 --> 00:26:18,680 Michael: Evil question. 543 00:26:18,740 --> 00:26:19,720 Haki: Evil question. 544 00:26:19,760 --> 00:26:20,260 Yeah. 545 00:26:20,380 --> 00:26:20,820 Nikolay: Yeah. 546 00:26:20,820 --> 00:26:25,320 It has many depths to go into, right? 547 00:26:25,320 --> 00:26:25,600 Yeah. 548 00:26:25,600 --> 00:26:26,740 Deeper, deeper, deeper. 549 00:26:26,820 --> 00:26:27,320 Yeah. 550 00:26:27,840 --> 00:26:30,720 Michael: There's a company that a few of my friends and former 551 00:26:30,720 --> 00:26:32,580 colleagues work at called incident.io. 552 00:26:33,340 --> 00:26:36,800 They do like incident management within things like Slack and 553 00:26:36,800 --> 00:26:37,300 teams. 554 00:26:37,700 --> 00:26:41,640 And they had this issue where they, well, they blogged about 555 00:26:41,640 --> 00:26:45,560 it, but they wanted gapless sequences for their incident numbers. 556 00:26:45,620 --> 00:26:49,540 They didn't, The customers got confused by the idea of the incident 557 00:26:49,540 --> 00:26:52,800 number jumping by like 5 when they'd only had, you know, they've 558 00:26:52,800 --> 00:26:55,080 had 1 incident, then a second incident, a third incident, suddenly 559 00:26:55,080 --> 00:26:56,260 they're on incident 6. 560 00:26:56,260 --> 00:26:56,980 What happened? 561 00:26:57,040 --> 00:26:59,540 So there's a really nice blog post there. 562 00:26:59,760 --> 00:27:01,740 We can explain how that was. 563 00:27:03,740 --> 00:27:08,080 But I'm curious, why are you bothered by sequence gaps, Nikolay? 564 00:27:08,160 --> 00:27:09,280 Why is that an issue? 565 00:27:09,280 --> 00:27:10,820 Nikolay: Well, it's not a big issue. 566 00:27:12,040 --> 00:27:15,560 It might be an issue for some cases, but actually I don't care. 567 00:27:15,660 --> 00:27:18,960 It gave me a false impression that there is bloat. 568 00:27:19,920 --> 00:27:23,040 But now I double-checked, I'm absolutely right. 569 00:27:23,160 --> 00:27:23,900 No bloat. 570 00:27:24,760 --> 00:27:29,440 So INSERT ON CONFLICT DO NOTHING probably is the winner in Postgres 571 00:27:29,440 --> 00:27:30,520 16 or older. 572 00:27:32,520 --> 00:27:34,960 Michael: I've got a question here, because you mentioned the 573 00:27:34,960 --> 00:27:39,360 kind of trade-off between the issues in the highly concurrent 574 00:27:39,440 --> 00:27:44,440 cases or like the potential issue of INSERT ON CONFLICT DO NOTHING 575 00:27:44,760 --> 00:27:51,100 returning null if you happen to have this case with the 2 concurrent 576 00:27:51,140 --> 00:27:56,140 sessions inserting the exact same tag at the same time, wouldn't 577 00:27:56,820 --> 00:28:01,820 INSERT ON CONFLICT DO UPDATE avoid that issue at the cost of 578 00:28:01,820 --> 00:28:03,480 some bloat? 579 00:28:04,820 --> 00:28:07,200 Haki: Well, that's a different issue. 580 00:28:07,200 --> 00:28:11,920 The issue I describe is that when you have 2 transactions trying 581 00:28:11,920 --> 00:28:15,520 to insert a new tag at the same time, the same new tag at the 582 00:28:15,520 --> 00:28:21,140 same time, then the second ON CONFLICT would not do anything 583 00:28:21,280 --> 00:28:23,540 and it would also not return anything. 584 00:28:24,840 --> 00:28:28,780 This is the unexpected part because one of my requirements is that 585 00:28:28,780 --> 00:28:30,260 the operation would be idempotent. 586 00:28:30,400 --> 00:28:34,300 So if I give it tag a, I expect to get an item returned. 587 00:28:34,740 --> 00:28:37,800 So in this case, there is a scenario where I provide it with 588 00:28:37,800 --> 00:28:42,020 a list of N tags and I get less than N tags in return. 589 00:28:43,260 --> 00:28:46,440 Michael: But if they both updated, imagine if both concurrent 590 00:28:46,440 --> 00:28:50,280 sessions were doing INSERT ON CONFLICT UPDATE, they both get 591 00:28:50,280 --> 00:28:51,180 to the update stage. 592 00:28:51,180 --> 00:28:54,820 You get 2 updates, but you still get back all the tags that you 593 00:28:55,240 --> 00:28:56,260 wanted to insert. 594 00:28:56,840 --> 00:29:00,080 Haki: First of all, in my scenario, I don't update. 595 00:29:00,240 --> 00:29:04,120 This is a very, a lot of people got this very confused. 596 00:29:04,120 --> 00:29:08,240 This is why I added the comment and some people made like a very, 597 00:29:09,140 --> 00:29:11,120 let's call them funny comments about it. 598 00:29:11,120 --> 00:29:11,620 Okay. 599 00:29:12,840 --> 00:29:16,620 But there's a difference between upsert where you want to update 600 00:29:16,780 --> 00:29:21,420 or insert, which is also a very, a very popular pattern. 601 00:29:21,780 --> 00:29:26,200 In this case, by the way, it's simpler because if you actually 602 00:29:26,200 --> 00:29:30,780 update the row, then it's going to get returned by RETURNING 603 00:29:30,820 --> 00:29:31,320 STAR. 604 00:29:31,340 --> 00:29:31,840 Michael: Returning. 605 00:29:31,980 --> 00:29:32,180 Haki: Yeah. 606 00:29:32,180 --> 00:29:33,840 So that's like the easy case. 607 00:29:33,840 --> 00:29:37,120 In my case, I don't want to touch the row, but I still want 608 00:29:37,120 --> 00:29:38,140 to get it back. 609 00:29:38,440 --> 00:29:42,760 This is why get_or_create() is like a more difficult variation 610 00:29:42,980 --> 00:29:43,720 of UPSERT. 611 00:29:44,480 --> 00:29:45,420 If you will. 612 00:29:45,700 --> 00:29:46,200 Okay. 613 00:29:46,640 --> 00:29:49,520 So, there's a tricky part. 614 00:29:50,020 --> 00:29:52,040 So apparently there's no good solution. 615 00:29:52,040 --> 00:29:57,840 By the way, the post by Erwin on Stack Overflow lands 616 00:29:58,100 --> 00:30:02,440 on a brute force solution where you basically loop until you 617 00:30:02,440 --> 00:30:03,580 get what you expect. 618 00:30:05,340 --> 00:30:09,520 I don't know if I would have, if I, maybe this is the way to 619 00:30:09,520 --> 00:30:10,020 go. 620 00:30:10,520 --> 00:30:14,360 Nikolay: Loop where in, in a different language or in PL/pgSQL? 621 00:30:14,380 --> 00:30:17,660 Haki: He ended up writing a function where you essentially loop 622 00:30:18,340 --> 00:30:20,900 and you need constraint violations. 623 00:30:20,960 --> 00:30:21,340 Yeah. 624 00:30:21,340 --> 00:30:23,440 Nikolay: But you, you need a subtransaction. 625 00:30:23,760 --> 00:30:25,380 So this is no go for me. 626 00:30:25,380 --> 00:30:25,840 Definitely. 627 00:30:25,840 --> 00:30:26,340 Right. 628 00:30:27,180 --> 00:30:28,320 This doesn't scale. 629 00:30:28,320 --> 00:30:28,940 I mean, this, 630 00:30:29,280 --> 00:30:31,360 Haki: Nikolay, you are a man with a mission. 631 00:30:32,380 --> 00:30:34,260 Nikolay: Well, it's, it's no joke. 632 00:30:34,260 --> 00:30:38,080 I, I would have clients literally last week I spent like half 633 00:30:38,100 --> 00:30:42,760 1 hour, and people went and switched off it in Django. 634 00:30:43,260 --> 00:30:47,280 Because it hurts, it hurts constantly people, like subtransaction 635 00:30:47,440 --> 00:30:49,500 SLRU, wait a minute. 636 00:30:49,640 --> 00:30:51,000 It happens all the time. 637 00:30:51,180 --> 00:30:54,940 The fact that Django by default uses them and people don't realize 638 00:30:54,960 --> 00:30:59,080 and then come to us with subtransaction SLRU, it's good for my income, 639 00:30:59,800 --> 00:31:03,200 but it's bad in general. 640 00:31:03,480 --> 00:31:05,460 By the way, I wanted to highlight 1 thing. 641 00:31:05,460 --> 00:31:10,560 If you do INSERT ON CONFLICT DO NOTHING, RETURNING STAR, it won't 642 00:31:10,560 --> 00:31:12,700 return anything if a row already exists. 643 00:31:12,700 --> 00:31:15,700 I just wanted to highlight that you have a trick. 644 00:31:16,100 --> 00:31:20,140 You use CTE, right? And then SELECT for such case, right. 645 00:31:20,140 --> 00:31:20,580 Yeah. 646 00:31:20,580 --> 00:31:22,740 Additionally, with UNION, right? 647 00:31:22,840 --> 00:31:24,060 Haki: Yeah, that's right. 648 00:31:24,060 --> 00:31:24,860 Nikolay: UNION or what? 649 00:31:24,860 --> 00:31:25,080 Haki: Yeah. 650 00:31:25,080 --> 00:31:27,540 This is the only way that he can actually communicate between 651 00:31:27,540 --> 00:31:28,680 parts of a query. 652 00:31:28,680 --> 00:31:31,600 Otherwise, they all see the same snapshot and you don't get. 653 00:31:32,220 --> 00:31:34,700 Nikolay: This is a trick also not straightforward at all. 654 00:31:34,760 --> 00:31:35,260 Honestly. 655 00:31:35,500 --> 00:31:35,860 Yeah. 656 00:31:35,860 --> 00:31:40,200 Like some people can move away from INSERT ON CONFLICT DO NOTHING 657 00:31:40,200 --> 00:31:41,360 just because of that. 658 00:31:42,040 --> 00:31:42,340 Michael: Yeah. 659 00:31:42,340 --> 00:31:42,840 Right. 660 00:31:42,900 --> 00:31:43,400 Yeah. 661 00:31:43,900 --> 00:31:46,060 One of many cool tricks in that book. 662 00:31:46,720 --> 00:31:49,620 Haki: I went through this process where I try to figure out, 663 00:31:49,620 --> 00:31:53,260 wait, I just inserted this row while, when I SELECT outside the 664 00:31:53,260 --> 00:31:53,800 CTE, I 665 00:31:53,800 --> 00:31:54,940 Nikolay: don't get it. 666 00:31:56,040 --> 00:31:56,540 Michael: Yeah. 667 00:31:57,260 --> 00:31:57,540 Yeah. 668 00:31:57,540 --> 00:32:02,880 I understand there's some costs to UPSERT, but given the complexity 669 00:32:03,000 --> 00:32:07,780 we're running into here, is it, would you both see it as acceptable 670 00:32:08,000 --> 00:32:13,180 to pay that extra cost, the kind of heavier, I know you mentioned 671 00:32:13,180 --> 00:32:16,320 the double hit of the table being annoying, but like 672 00:32:16,620 --> 00:32:19,840 those updates, even though they'd be setting the tag name to 673 00:32:19,840 --> 00:32:23,360 the exact same tag name, potentially over and over again. 674 00:32:23,360 --> 00:32:23,860 Again. 675 00:32:24,120 --> 00:32:27,780 I just see the guarantees that provides is so attractive. 676 00:32:28,940 --> 00:32:30,200 Haki: I'll be honest with you. 677 00:32:30,200 --> 00:32:32,700 I understand what you're saying and you're probably correct. 678 00:32:33,160 --> 00:32:36,760 This is like the practical approach, but I would not do it. 679 00:32:37,120 --> 00:32:38,100 It just bugs me. 680 00:32:38,100 --> 00:32:39,440 I would not do it. 681 00:32:40,640 --> 00:32:43,900 I would not do a meaningless update just to save, I would just 682 00:32:43,900 --> 00:32:44,860 do two queries. 683 00:32:44,860 --> 00:32:46,820 I would INSERT, and then I would SELECT. 684 00:32:47,640 --> 00:32:48,080 Separately. 685 00:32:48,080 --> 00:32:50,680 Nikolay: Like an INSERT fallback, INSERT fallback, you end up 686 00:32:50,680 --> 00:32:53,980 having a lot of bloat depending on the concrete situation. 687 00:32:54,940 --> 00:33:01,340 So this, so subtransactions and bloat, huge limitations of performance 688 00:33:01,380 --> 00:33:01,720 here. 689 00:33:01,720 --> 00:33:02,120 Right? 690 00:33:02,120 --> 00:33:02,660 Haki: I know. 691 00:33:02,660 --> 00:33:03,960 I think this is a scenario. 692 00:33:03,960 --> 00:33:08,680 This is a case where you kind of understand the limitation and 693 00:33:08,680 --> 00:33:12,780 restrictions of the database and you kind of end up solving it 694 00:33:12,780 --> 00:33:14,180 at the application level. 695 00:33:15,720 --> 00:33:20,160 Now, you mentioned at the beginning, I know that I, well, 696 00:33:20,160 --> 00:33:26,760 get_or_create() is, is useful, but I haven't had a chance to implement 697 00:33:26,760 --> 00:33:28,060 it as much. 698 00:33:30,060 --> 00:33:33,400 So yeah, for the rare occasion, what I actually need to do get 699 00:33:33,400 --> 00:33:37,660 or create, I would just do it at the application level because 700 00:33:37,660 --> 00:33:41,020 as it turns out, it's very complicated at the database level. 701 00:33:41,520 --> 00:33:41,880 Nikolay: Right. 702 00:33:41,880 --> 00:33:43,720 In applications, it becomes simpler. 703 00:33:44,340 --> 00:33:47,220 You like, you don't deal with, probably don't deal with subtransactions 704 00:33:47,480 --> 00:33:52,760 and bloat, but latency like there are trip times between attempts 705 00:33:52,800 --> 00:33:57,940 to do one step, another step, it increases chances of failures, 706 00:33:57,980 --> 00:33:58,260 right? 707 00:33:58,260 --> 00:34:00,200 I mean, collisions and so on. 708 00:34:00,420 --> 00:34:00,580 Yeah. 709 00:34:00,580 --> 00:34:04,200 Haki: But you know, if, if my main business is to get or create, 710 00:34:04,200 --> 00:34:07,120 I would come up with a very innovative solution, put a lot of 711 00:34:07,120 --> 00:34:08,680 engineering work into it. 712 00:34:08,680 --> 00:34:11,760 But if it's just updating tags for members in an organization, 713 00:34:12,040 --> 00:34:12,940 I would go. 714 00:34:13,200 --> 00:34:13,520 Yeah. 715 00:34:13,520 --> 00:34:14,920 But I understand what you're saying. 716 00:34:14,920 --> 00:34:19,640 I spent a lot of time doing unnecessary optimizations on, you 717 00:34:19,640 --> 00:34:22,860 know, weird places in the code just for fun. 718 00:34:23,260 --> 00:34:24,700 I do it all the time. 719 00:34:25,440 --> 00:34:25,840 Yeah. 720 00:34:25,840 --> 00:34:30,860 Like 90% of my blog posts are inspired by these strange endeavors 721 00:34:30,860 --> 00:34:32,660 where I try to optimize things. 722 00:34:33,760 --> 00:34:35,540 So yeah, I, this is interesting. 723 00:34:35,760 --> 00:34:40,320 And I think that this very simple problem surfaced a lot of issues. 724 00:34:41,000 --> 00:34:43,620 I also learned a lot from writing this. 725 00:34:43,860 --> 00:34:48,100 It got me interested in, in speculative insertion, the mechanism 726 00:34:48,340 --> 00:34:50,040 used by INSERT ON CONFLICT. 727 00:34:50,740 --> 00:34:54,220 It, I think, broadened my understanding of how the interaction 728 00:34:54,280 --> 00:34:57,400 between different queries and a common table expressions work 729 00:34:57,400 --> 00:34:59,120 within the same query. 730 00:34:59,640 --> 00:35:03,200 No, lots of things that I'm now aware of them. 731 00:35:03,200 --> 00:35:09,140 So at least I'm better equipped to debug issues I might have 732 00:35:09,140 --> 00:35:10,660 and don't even know about. 733 00:35:12,160 --> 00:35:12,660 Michael: Yeah. 734 00:35:12,720 --> 00:35:12,980 Yeah. 735 00:35:12,980 --> 00:35:14,600 I really liked it as a journey. 736 00:35:14,600 --> 00:35:17,600 I would encourage people to read this, even if they don't have 737 00:35:17,600 --> 00:35:21,560 this problem, just as a, almost as just to watch somebody go 738 00:35:21,560 --> 00:35:23,000 through this problem. 739 00:35:24,160 --> 00:35:26,880 And you know, I feel like you've included things as well. 740 00:35:26,880 --> 00:35:30,660 I like there's 1 section that's I took a long time as well, like 741 00:35:30,660 --> 00:35:31,080 that. 742 00:35:31,080 --> 00:35:33,600 And that's so helpful to share those things with people. 743 00:35:33,600 --> 00:35:36,380 Cause otherwise you can read some of these blog posts and they 744 00:35:36,380 --> 00:35:39,660 just sound like the person knew, like just got it right straight 745 00:35:39,660 --> 00:35:40,080 away. 746 00:35:40,080 --> 00:35:42,660 And it's quite frustrating as somebody that quite often goes 747 00:35:42,660 --> 00:35:44,580 down long-term for me reading those. 748 00:35:44,580 --> 00:35:46,280 So I appreciated that. 749 00:35:46,380 --> 00:35:49,840 Nikolay: I just wish you had also some warning about subtransactions 750 00:35:50,220 --> 00:35:50,720 anyway. 751 00:35:53,300 --> 00:35:55,600 Haki: You know what, let me just change the banner. 752 00:35:55,600 --> 00:35:58,860 The top of the website, instead of the about page, I would just 753 00:35:58,860 --> 00:36:01,380 place like this huge warning, They don't need subtransactions. 754 00:36:02,020 --> 00:36:03,380 Nikolay: Let me put right here. 755 00:36:03,380 --> 00:36:09,360 If you see a PL/pgSQL with BEGIN, EXCEPTION 756 00:36:09,480 --> 00:36:10,400 WHEN or something. 757 00:36:10,440 --> 00:36:15,400 So anyway, nested BEGIN... END blocks, this is subtransaction. 758 00:36:16,720 --> 00:36:17,220 Haki: Yeah. 759 00:36:17,580 --> 00:36:20,720 But in this case, it's warranted because otherwise, it cancels 760 00:36:20,720 --> 00:36:21,720 any calling transaction. 761 00:36:22,060 --> 00:36:24,800 So that would be the responsible thing to do in this case. 762 00:36:24,800 --> 00:36:25,760 But you know what? 763 00:36:25,760 --> 00:36:29,180 Maybe it needs a comment saying this is a good subtransaction. 764 00:36:30,360 --> 00:36:33,040 Nikolay: It's not a good subtransaction because if you have 765 00:36:33,040 --> 00:36:33,440 high... 766 00:36:33,440 --> 00:36:36,360 Haki: This is the least worst type of subtransaction. 767 00:36:36,980 --> 00:36:40,100 Nikolay: I cannot agree with you here because you talk about, 768 00:36:40,840 --> 00:36:44,420 you explore clashes and concurrency issues. 769 00:36:44,680 --> 00:36:45,180 Yeah. 770 00:36:45,720 --> 00:36:50,140 It means you think that there will be a lot of sessions issuing 771 00:36:50,900 --> 00:36:52,000 this query. 772 00:36:52,440 --> 00:36:56,740 It means eventually the project will bump into this wall. 773 00:36:57,940 --> 00:37:03,120 Good subtransactions is, for example, DDL, which happens not 774 00:37:03,120 --> 00:37:04,660 often in 1 session. 775 00:37:04,780 --> 00:37:05,540 This is good. 776 00:37:05,540 --> 00:37:09,600 And even there, it can become bad. 777 00:37:09,900 --> 00:37:14,280 But here I cannot agree because we should consider multiple sessions 778 00:37:14,540 --> 00:37:17,120 fighting and this is where things can go wrong. 779 00:37:17,120 --> 00:37:19,860 Haki: What you're saying reinforces what I'm saying that in this 780 00:37:19,860 --> 00:37:22,700 situation, because it's so complicated in the database, I would 781 00:37:22,700 --> 00:37:27,940 just elevate that to the application level and try to find a 782 00:37:27,940 --> 00:37:30,420 solution there to avoid all the complexity. 783 00:37:30,480 --> 00:37:33,480 But it was a nice thought exercise, understanding different types 784 00:37:33,480 --> 00:37:34,080 of transactions. 785 00:37:34,080 --> 00:37:37,280 Nikolay: Just to try regular, regular transaction, just to try 786 00:37:37,280 --> 00:37:38,260 regular transaction maybe. 787 00:37:38,260 --> 00:37:38,680 Right. 788 00:37:38,680 --> 00:37:38,860 Yeah. 789 00:37:38,860 --> 00:37:40,920 It should not be so complex, right? 790 00:37:40,920 --> 00:37:41,460 Haki: I know. 791 00:37:41,580 --> 00:37:43,520 I was surprised that it was so complicated. 792 00:37:44,240 --> 00:37:45,180 Nikolay: Why is it so? 793 00:37:45,560 --> 00:37:49,120 Michael: What do you, like, what would you both like as a, is 794 00:37:49,120 --> 00:37:51,700 it that you would like MERGE to work as expected? 795 00:37:51,780 --> 00:37:54,940 Like what is the nice solution on the database side for this? 796 00:37:55,080 --> 00:37:59,120 Haki: Well, the dream syntax would be for SELECT DO NOTHING RETURNING 797 00:37:59,120 --> 00:38:01,480 STAR to just return everything. 798 00:38:02,020 --> 00:38:03,780 That would be like the dream syntax. 799 00:38:04,220 --> 00:38:07,080 Nikolay: I think part of the problem is that RETURNING STAR is 800 00:38:07,080 --> 00:38:12,240 quite like not super popular thing, which was added later to 801 00:38:12,240 --> 00:38:14,000 INSERT and UPDATE and DELETE. 802 00:38:14,180 --> 00:38:15,740 It was not originally there. 803 00:38:15,960 --> 00:38:17,880 So it's not a super natural thing. 804 00:38:18,820 --> 00:38:21,540 Michael: Also, once you've defined that behavior, it's so difficult 805 00:38:21,560 --> 00:38:24,840 to like, you can't change the behavior for existing applications. 806 00:38:24,840 --> 00:38:25,340 Haki: You can't. 807 00:38:25,340 --> 00:38:30,740 And I can even give you an example because you usually use INSERT 808 00:38:31,180 --> 00:38:32,060 ON CONFLICT. 809 00:38:32,980 --> 00:38:36,160 With, you know, there is no MERGE command in Postgres. 810 00:38:36,300 --> 00:38:42,340 So if you want to sync data, you use INSERT ON CONFLICT and then 811 00:38:42,340 --> 00:38:43,680 when matched do nothing. 812 00:38:44,100 --> 00:38:44,600 OK. 813 00:38:44,820 --> 00:38:48,400 And more often what you want to do is you want to avoid the unnecessary 814 00:38:48,540 --> 00:38:48,960 updates. 815 00:38:48,960 --> 00:38:54,900 So you do when matched, do UPDATE where something actually changed. 816 00:38:55,200 --> 00:38:55,580 OK. 817 00:38:55,580 --> 00:38:59,120 And then some rows end up not being affected. 818 00:38:59,500 --> 00:39:02,300 So at this point you do RETURNING star. 819 00:39:02,660 --> 00:39:07,120 And then usually what I like to do in my ETL processes is I count 820 00:39:07,120 --> 00:39:09,860 the rows so I can log how many rows were affected. 821 00:39:11,180 --> 00:39:11,680 Right? 822 00:39:11,820 --> 00:39:16,440 So now the question is, if I expect to get the number of affected 823 00:39:16,560 --> 00:39:19,440 rows, we go for a circle here. 824 00:39:19,440 --> 00:39:20,020 I know. 825 00:39:20,020 --> 00:39:23,860 So if I'm just expecting that the affected rows, that if I'm 826 00:39:23,860 --> 00:39:29,600 going to get rows that were not affected, also very strange, 827 00:39:29,620 --> 00:39:30,120 right? 828 00:39:31,060 --> 00:39:31,560 Nikolay: Right. 829 00:39:32,540 --> 00:39:33,040 Haki: Yeah. 830 00:39:33,420 --> 00:39:34,520 So what's the conclusion? 831 00:39:34,640 --> 00:39:35,320 Come on, guys. 832 00:39:35,320 --> 00:39:36,560 I mean, what's the conclusion? 833 00:39:36,600 --> 00:39:37,940 Just do it in the application? 834 00:39:37,960 --> 00:39:38,900 Nikolay: Don't go there. 835 00:39:38,940 --> 00:39:40,780 The main conclusion is don't go there. 836 00:39:40,840 --> 00:39:41,920 Don't think about it. 837 00:39:41,920 --> 00:39:42,420 Haki: Yeah. 838 00:39:44,440 --> 00:39:49,340 Nikolay: If an error happens, it only confirms that application 839 00:39:49,540 --> 00:39:52,580 developers should implement proper retry logic. 840 00:39:52,820 --> 00:39:53,320 Always. 841 00:39:54,020 --> 00:39:57,940 If a transaction fails, code should be ready to retry it. 842 00:39:58,040 --> 00:39:58,780 That's it. 843 00:39:59,180 --> 00:40:00,880 Universal solution to everything. 844 00:40:02,420 --> 00:40:05,400 Haki: We should rename this to just do it in the application 845 00:40:05,560 --> 00:40:06,060 FM. 846 00:40:08,160 --> 00:40:10,960 Well, it's complicated FM. 847 00:40:13,140 --> 00:40:13,960 Michael: It's complicated. 848 00:40:14,140 --> 00:40:15,260 Haki: No, it is complicated. 849 00:40:15,480 --> 00:40:16,620 No, no jokes aside. 850 00:40:16,620 --> 00:40:16,860 It is. 851 00:40:16,860 --> 00:40:19,520 I don't know why it's so complicated, but it is. 852 00:40:19,700 --> 00:40:21,500 Michael: Turns out concurrency is hard. 853 00:40:21,500 --> 00:40:22,000 Haki: Yeah. 854 00:40:22,240 --> 00:40:25,240 You know, one of the things that I remember is that 855 00:40:25,240 --> 00:40:29,060 when I read this RDS article about a unique constraint violation 856 00:40:29,060 --> 00:40:31,160 causing bloat, I was shocked. 857 00:40:32,020 --> 00:40:37,120 I was shocked because coming from Python, I'm like encouraged 858 00:40:37,360 --> 00:40:38,440 to trigger exceptions. 859 00:40:39,220 --> 00:40:40,860 This is how I do things. 860 00:40:41,040 --> 00:40:43,680 I fail and then I, I adjust. 861 00:40:44,840 --> 00:40:47,540 So, that was very strange for me. 862 00:40:47,540 --> 00:40:52,160 I have this entire lecture about how to handle concurrency with 863 00:40:52,160 --> 00:40:53,980 a short URL system and everything. 864 00:40:53,980 --> 00:40:58,680 Like there's a problem of how you generate short random IDs. 865 00:40:59,180 --> 00:41:00,200 How do you do it? 866 00:41:00,200 --> 00:41:03,320 You need to, if you know, like a short URL system, you need to 867 00:41:03,320 --> 00:41:05,240 generate keys, very small keys. 868 00:41:06,040 --> 00:41:07,420 So the space is limited. 869 00:41:07,900 --> 00:41:10,520 So you want to generate random unique keys. 870 00:41:10,520 --> 00:41:11,920 So how do you do it? 871 00:41:12,700 --> 00:41:16,960 So you end up in a solution where you try to INSERT and then 872 00:41:16,960 --> 00:41:21,540 you loop until you don't get unique constraint violations anymore. 873 00:41:22,200 --> 00:41:22,700 Right. 874 00:41:22,800 --> 00:41:28,440 And now all I'm thinking is I may have caused lots of bloat without 875 00:41:28,440 --> 00:41:28,940 intention. 876 00:41:30,040 --> 00:41:31,500 Maybe you have a better solution. 877 00:41:31,720 --> 00:41:34,320 By the way, it's very similar to the question about the gap, 878 00:41:34,320 --> 00:41:35,040 the sequences. 879 00:41:36,340 --> 00:41:36,840 Michael: Yeah. 880 00:41:37,540 --> 00:41:39,240 I get that bloat is bad, right? 881 00:41:39,240 --> 00:41:42,540 Like I understand, I see lots of slow queries that are caused by 882 00:41:42,540 --> 00:41:46,220 bloat, but once you're aware of it, it's like something that can be managed. 883 00:41:46,220 --> 00:41:47,500 And you've written a great blog post about how to manage it at 884 00:41:47,500 --> 00:41:50,080 Haki as well, that I'll include. 885 00:41:50,080 --> 00:41:55,640 But it feels to me like it might be one of those pains worth paying 886 00:41:55,640 --> 00:41:59,100 sometimes when you're aware, like every single PostgreSQL table 887 00:41:59,100 --> 00:42:02,240 is going to have some bloat in it, in a natural work life. 888 00:42:02,240 --> 00:42:02,640 You're not going to get it down to zero, even when you do some of 889 00:42:02,640 --> 00:42:05,360 these maintenance tasks. 890 00:42:05,360 --> 00:42:06,820 So we're always managing some amount. 891 00:42:06,820 --> 00:42:08,720 It's just like, what's a, what's a healthy amount and can, can 892 00:42:08,720 --> 00:42:11,720 we keep it within those boundaries? 893 00:42:11,720 --> 00:42:13,420 It's going to be more painful with certain workflows. 894 00:42:13,440 --> 00:42:15,760 Like maybe once we're getting near the end of that, like once 895 00:42:15,760 --> 00:42:18,560 we get past maybe halfway full of that space, we're starting 896 00:42:18,560 --> 00:42:22,320 like an average of 2 tries per unique URL might start to get 897 00:42:22,320 --> 00:42:26,480 annoying. 898 00:42:26,480 --> 00:42:26,940 So maybe that's the point where you start expanding the space. 899 00:42:29,540 --> 00:42:30,900 by an extra digit or something. 900 00:42:30,900 --> 00:42:33,740 I don't, I imagine that once you're aware of the problems, you 901 00:42:33,740 --> 00:42:37,040 can design a solution that isn't that bad and then manage the 902 00:42:37,040 --> 00:42:37,540 bloat. 903 00:42:37,540 --> 00:42:40,120 Haki: Well, it all boils down to how many collisions you actually 904 00:42:40,120 --> 00:42:41,140 expect to get. 905 00:42:42,040 --> 00:42:50,580 Nikolay: I think the fact that rollback inserts cause bloat makes 906 00:42:50,580 --> 00:42:53,420 me think that only undo people can save us someday. 907 00:42:54,280 --> 00:42:55,900 Michael: We had a quote last week. 908 00:42:57,260 --> 00:42:58,840 Nikolay: The new term, undo people. 909 00:42:58,940 --> 00:43:00,080 Because indeed, if you think 910 00:43:00,080 --> 00:43:00,980 Michael: about it, 911 00:43:03,940 --> 00:43:07,480 Nikolay: this different approach would place a new row in the 912 00:43:07,480 --> 00:43:10,740 same position on the page, right? 913 00:43:11,540 --> 00:43:14,380 And if it's committed, it's there. 914 00:43:14,380 --> 00:43:18,220 If not, like, I mean, the different approach would not cause 915 00:43:19,120 --> 00:43:21,700 bloat if an insert is rolled back. 916 00:43:22,540 --> 00:43:23,040 Right. 917 00:43:23,200 --> 00:43:28,680 So this is just the design of PostgreSQL's MVCC we should blame. 918 00:43:29,540 --> 00:43:30,040 Right. 919 00:43:30,940 --> 00:43:31,440 Yeah. 920 00:43:32,020 --> 00:43:34,300 It's very unexpected for sure. 921 00:43:34,300 --> 00:43:34,700 Haki: Yeah. 922 00:43:34,700 --> 00:43:38,140 Well, at least we can run very long queries without worrying 923 00:43:38,140 --> 00:43:41,300 about undo segments running out. 924 00:43:42,560 --> 00:43:43,060 Nikolay: Yeah. 925 00:43:43,360 --> 00:43:44,140 Michael: I've been there. 926 00:43:44,140 --> 00:43:47,120 And it's great to have an old group, like a former ORACLE person 927 00:43:47,120 --> 00:43:49,740 understanding the downsides of undo too. 928 00:43:49,740 --> 00:43:50,240 Right. 929 00:43:51,820 --> 00:43:54,560 Haki, any, any last things that we should have mentioned that 930 00:43:54,560 --> 00:43:54,960 we didn't? 931 00:43:54,960 --> 00:43:55,440 Nikolay: And 932 00:43:55,440 --> 00:43:58,980 Haki: no, I think that we zigzagged across this article very, 933 00:43:58,980 --> 00:43:59,820 very nicely. 934 00:44:00,240 --> 00:44:02,520 Nikolay definitely delivered on his promise. 935 00:44:03,500 --> 00:44:03,900 Nikolay: Yeah. 936 00:44:03,900 --> 00:44:06,420 Well, are you planning part 2 with benchmarks? 937 00:44:07,800 --> 00:44:10,520 Haki: I don't know if I still don't care much about performance 938 00:44:10,520 --> 00:44:14,800 in this case, but I think that I might add an update on some 939 00:44:14,800 --> 00:44:19,180 of the findings about how merge and insert can end up eventually 940 00:44:19,500 --> 00:44:26,180 causing a unique constraint violation and non-idempotent results, 941 00:44:27,180 --> 00:44:33,180 I hope that you implemented upsert so you can identify where 942 00:44:33,180 --> 00:44:35,200 blog post is updated. 943 00:44:36,260 --> 00:44:39,120 So your AI can answer this correctly. 944 00:44:40,300 --> 00:44:41,640 Nikolay: Oh, this is good. 945 00:44:41,760 --> 00:44:42,500 Good goal. 946 00:44:42,500 --> 00:44:44,080 Actually, we don't have. 947 00:44:45,240 --> 00:44:45,740 Haki: Yeah. 948 00:44:46,620 --> 00:44:48,520 Misconceptions about blog posts. 949 00:44:48,520 --> 00:44:49,500 They never update. 950 00:44:52,720 --> 00:44:52,940 Yeah. 951 00:44:52,940 --> 00:44:55,760 I just solved your get or create problem. 952 00:44:55,760 --> 00:44:57,940 You can just update all the time. 953 00:44:58,260 --> 00:45:01,200 Nikolay: It's not that easy because we have many pre-processing 954 00:45:01,420 --> 00:45:05,460 stuff for really long articles as yours, because you know, the 955 00:45:05,580 --> 00:45:10,460 LLM has usually has some limitation OpenAI has like 8K tokens 956 00:45:10,840 --> 00:45:14,640 as input, and I'm sure your article exceeds it a lot. 957 00:45:15,120 --> 00:45:15,620 So. 958 00:45:16,120 --> 00:45:17,780 Haki: I try to write shorter ones. 959 00:45:18,700 --> 00:45:19,200 Nikolay: Yeah. 960 00:45:20,060 --> 00:45:21,680 Probably you should have 2 versions. 961 00:45:21,980 --> 00:45:25,760 No, like for people who have issues with long reads. 962 00:45:25,760 --> 00:45:25,940 Yeah. 963 00:45:25,940 --> 00:45:26,720 I do actually. 964 00:45:26,720 --> 00:45:28,420 Haki: You like blog posts in parts. 965 00:45:30,060 --> 00:45:33,840 Nikolay: I like short versions till they are, but with some details. 966 00:45:35,460 --> 00:45:37,280 Michael: I'm a big fan of long blog posts. 967 00:45:37,280 --> 00:45:39,440 I can, I know there's a lot of others out there too? 968 00:45:39,440 --> 00:45:42,080 So thank you for continuing to work for us. 969 00:45:42,260 --> 00:45:42,760 Nikolay: Yeah. 970 00:45:43,660 --> 00:45:47,920 It's something that I I'm going to cite for sure. 971 00:45:48,280 --> 00:45:48,780 Yeah. 972 00:45:48,780 --> 00:45:51,060 Links will be used to this article. 973 00:45:51,060 --> 00:45:51,560 Yeah. 974 00:45:51,780 --> 00:45:52,740 Thank you for this. 975 00:45:53,040 --> 00:45:54,720 Haki: I need an idea for the next 1. 976 00:45:54,720 --> 00:46:00,300 So if you have 1 ping me, I have a working title in my mind. 977 00:46:01,500 --> 00:46:04,620 It's called how to take a dump like a pro. 978 00:46:04,940 --> 00:46:06,540 I couldn't resist the title. 979 00:46:07,360 --> 00:46:10,180 Nikolay: That's a huge topic actually. 980 00:46:10,800 --> 00:46:11,040 Haki: Yeah. 981 00:46:11,040 --> 00:46:14,200 I've actually had some experience with it lately and I think 982 00:46:14,200 --> 00:46:17,900 that it's Interesting to see how you can optimize pg_dump with 983 00:46:17,900 --> 00:46:22,060 the different compression algorithms, how they affect the CPU. 984 00:46:23,420 --> 00:46:29,700 And when dumping in parallel is actually not useful at all. 985 00:46:30,860 --> 00:46:32,120 Nikolay: Unpartitioned tables. 986 00:46:33,460 --> 00:46:35,960 Haki: No, if you have 1 big table. 987 00:46:36,660 --> 00:46:40,100 Nikolay: Right, it's possible, but you need to engineer yourself 988 00:46:40,200 --> 00:46:42,540 with snapshots and repeatable transactions. 989 00:46:44,240 --> 00:46:45,200 Haki: So that's common. 990 00:46:45,420 --> 00:46:47,020 Nikolay: And use ranges of IDs. 991 00:46:47,320 --> 00:46:51,760 This is what Patroni, for example, does for logical replication 992 00:46:51,940 --> 00:46:52,440 initialization. 993 00:46:53,080 --> 00:46:54,740 So it's possible, for sure. 994 00:46:55,080 --> 00:46:57,540 Haki: Yeah, but I do have another consideration that you didn't 995 00:46:57,540 --> 00:46:58,340 think about. 996 00:46:59,440 --> 00:47:04,600 Because 1 of my restrictions was that I wanted to stream the 997 00:47:04,600 --> 00:47:10,740 output directly to S3 and not to the local file system. 998 00:47:10,760 --> 00:47:12,900 Imagine that you run this in a Lambda. 999 00:47:13,380 --> 00:47:18,820 In this case, using parallel is problematic, but using a single 1000 00:47:18,820 --> 00:47:22,460 file, you can just stream it directly and skip the file system. 1001 00:47:22,920 --> 00:47:29,120 And also if you want to pass the dump through some kind of encryption, 1002 00:47:30,160 --> 00:47:33,760 then also doing things in parallel makes it a bit difficult. 1003 00:47:34,500 --> 00:47:37,460 So yeah, different restrictions, different solution. 1004 00:47:38,040 --> 00:47:41,080 Nikolay: When you do this, like you, you find some like lacking 1005 00:47:41,200 --> 00:47:41,700 feature. 1006 00:47:42,560 --> 00:47:46,480 Do you have sometimes idea to implement something? 1007 00:47:47,940 --> 00:47:48,300 Haki: Implement? 1008 00:47:48,300 --> 00:47:48,440 Nikolay: To 1009 00:47:48,440 --> 00:47:49,420 become a hacker. 1010 00:47:49,560 --> 00:47:50,640 To become a hacker. 1011 00:47:51,340 --> 00:47:54,200 Haki: Oh, what, like contribute to Postgres? 1012 00:47:55,960 --> 00:47:58,780 Nikolay: Because if you, well, dump lacks a lot of stuff all 1013 00:47:58,780 --> 00:48:01,840 the time, like, but it's super complicated to add some things 1014 00:48:01,840 --> 00:48:04,460 there, but definitely it likes a lot of stuff. 1015 00:48:05,140 --> 00:48:09,960 So I'm curious if you've had such feeling, like idea, like, Oh, 1016 00:48:09,960 --> 00:48:12,080 I'm going to try to implement this. 1017 00:48:12,440 --> 00:48:12,900 No? 1018 00:48:12,900 --> 00:48:17,700 Haki: I've looked at the Postgres code many, many times. 1019 00:48:18,160 --> 00:48:22,400 I think that the documentation in the source itself is absolutely 1020 00:48:22,680 --> 00:48:23,180 excellent. 1021 00:48:23,940 --> 00:48:29,020 I remember when we worked on this, hash indexes article, we looked 1022 00:48:29,020 --> 00:48:30,180 at the actual source file. 1023 00:48:30,180 --> 00:48:33,060 There's lots of very, very useful information. 1024 00:48:33,420 --> 00:48:36,940 By the way, this information was a lot more useful than anything 1025 00:48:36,940 --> 00:48:39,600 I could find online, including the documentation itself. 1026 00:48:40,240 --> 00:48:42,040 So it was absolutely excellent. 1027 00:48:42,040 --> 00:48:44,180 And I think that the code is very well written. 1028 00:48:44,600 --> 00:48:45,720 I'll be honest with you. 1029 00:48:45,720 --> 00:48:48,820 I don't think I'm capable enough to contribute to Postgres itself. 1030 00:48:49,400 --> 00:48:54,440 But, you know, I do from time to time think about my contribution, 1031 00:48:54,680 --> 00:48:57,100 my type of contribution to the Postgres community. 1032 00:48:57,380 --> 00:49:00,600 And I know that the most straightforward way to contribute to 1033 00:49:00,600 --> 00:49:07,940 Postgres is to contribute code, but I feel like I contribute 1034 00:49:08,000 --> 00:49:13,840 in my way by advocating for Postgres and educating people how 1035 00:49:13,840 --> 00:49:17,820 to use Postgres correctly because I believe that if people would 1036 00:49:17,960 --> 00:49:21,800 have good systems, they'll be happy with Postgres, they'll continue 1037 00:49:21,820 --> 00:49:25,760 using it, they'll recommend it to their friends, and this is 1038 00:49:25,760 --> 00:49:26,940 how you build a community. 1039 00:49:27,360 --> 00:49:31,880 So I think that I'm more capable in telling stories, educate 1040 00:49:31,880 --> 00:49:35,520 people about Postgres, working on ETL processes and applications 1041 00:49:35,660 --> 00:49:39,380 that I do working on, you know, the tooling, the internal stuff. 1042 00:49:39,800 --> 00:49:43,220 There are people much smarter than me that can do this stuff. 1043 00:49:44,600 --> 00:49:45,980 I'll write about them. 1044 00:49:47,380 --> 00:49:48,140 Nikolay: Well, yeah. 1045 00:49:48,140 --> 00:49:52,580 Postgres documentation lacks how-to recipes, and this is what 1046 00:49:52,580 --> 00:49:53,000 you do. 1047 00:49:53,000 --> 00:49:54,960 You describe some recipes and pros and cons. 1048 00:49:54,960 --> 00:49:56,180 This is an excellent thing. 1049 00:49:56,180 --> 00:49:56,680 Haki: Yeah. 1050 00:49:57,040 --> 00:49:57,900 War stories. 1051 00:49:58,440 --> 00:50:00,900 Michael: You absolutely do all those things, and we really appreciate 1052 00:50:00,900 --> 00:50:01,340 it, Haki. 1053 00:50:01,340 --> 00:50:02,840 Thank you so much for coming on as well. 1054 00:50:02,840 --> 00:50:04,460 It's been a pleasure having you here. 1055 00:50:04,640 --> 00:50:05,140 Haki: Okay. 1056 00:50:05,140 --> 00:50:06,720 Thank you, Nikolay, Michael. 1057 00:50:07,120 --> 00:50:08,000 It was a pleasure. 1058 00:50:08,000 --> 00:50:10,460 This is the first time for me in a podcast. 1059 00:50:10,900 --> 00:50:11,980 Michael: But you did great. 1060 00:50:12,180 --> 00:50:13,400 Haki: Thank you so much. 1061 00:50:14,440 --> 00:50:14,820 Bye. 1062 00:50:14,820 --> 00:50:15,580 Nikolay: Thank you. Have a good week. 1063 00:50:16,700 --> 00:50:16,744 Michael: Take care. 1064 00:50:16,744 --> 00:50:16,766 Haki: Thanks.