1 00:00:00,099 --> 00:00:02,879 Nikolay: Hello, hello, this is PostgresFM episode 76. 2 00:00:03,959 --> 00:00:08,940 My name is Nikolay and as usual, together with me is Michael. 3 00:00:08,940 --> 00:00:09,639 Hi, Michael. 4 00:00:10,120 --> 00:00:10,960 Michael: Hello, Nikolay. 5 00:00:12,040 --> 00:00:15,940 Nikolay: So, today's topic is super interesting and very popular, 6 00:00:15,940 --> 00:00:16,440 right? 7 00:00:16,980 --> 00:00:17,720 Michael: Not exactly. 8 00:00:18,420 --> 00:00:19,600 I chose this one. 9 00:00:19,960 --> 00:00:20,460 Nikolay: Okay. 10 00:00:20,660 --> 00:00:21,880 Michael: Therefore, it's boring. 11 00:00:22,279 --> 00:00:24,740 Nikolay: But we will learn something new, which is good. 12 00:00:25,380 --> 00:00:26,360 Michael: I hope so. 13 00:00:26,439 --> 00:00:29,080 Nikolay: Even if it's useless, it's still new. 14 00:00:30,180 --> 00:00:31,820 We're here to learn, right? 15 00:00:31,920 --> 00:00:34,440 Michael: So Nikolay's teasing me because I've picked the topic 16 00:00:34,440 --> 00:00:40,739 hash indexes, which are, well, until relatively recently, were 17 00:00:41,100 --> 00:00:46,559 highly discouraged by the Postgres docs and not very sensible 18 00:00:46,559 --> 00:00:47,540 to use, but- 19 00:00:47,540 --> 00:00:48,840 Nikolay: until PostgreSQL 10. 20 00:00:49,540 --> 00:00:53,600 Michael: Yes, but have been in Postgres for a long, long time. 21 00:00:53,600 --> 00:00:56,760 I looked into the history of it and therefore some people must 22 00:00:56,760 --> 00:00:57,780 think they're useful. 23 00:00:57,840 --> 00:01:00,800 And I definitely think there's one or two use cases for them, but 24 00:01:00,800 --> 00:01:03,340 more to the point, I think they're very interesting for people. 25 00:01:03,340 --> 00:01:06,560 I think this is the kind of thing that will make you appreciate 26 00:01:06,580 --> 00:01:11,120 the B-tree index as well, and also understand a little bit more 27 00:01:11,120 --> 00:01:15,740 about trade-offs we make when we're choosing things within Postgres. 28 00:01:16,280 --> 00:01:17,380 Nikolay: Indexes, right? 29 00:01:17,440 --> 00:01:23,320 I mean, why doesn't Postgres choose indexes, index type for us? 30 00:01:23,320 --> 00:01:25,679 We just want to make it fast and that's it, right? 31 00:01:25,679 --> 00:01:26,660 It should choose. 32 00:01:27,280 --> 00:01:28,600 Okay, a different question. 33 00:01:28,680 --> 00:01:34,820 Are hash indexes more useful than the money data type? 34 00:01:34,900 --> 00:01:36,520 Michael: Oh, I think so, yes. 35 00:01:36,980 --> 00:01:38,600 Nikolay: Okay, this is already better. 36 00:01:38,620 --> 00:01:42,840 I hope we will never have an episode about the money data type. 37 00:01:42,979 --> 00:01:43,940 Michael: Yeah, me too. 38 00:01:44,680 --> 00:01:45,180 Nikolay: Okay. 39 00:01:45,660 --> 00:01:49,140 Michael: So yeah, should we start with what a hash index is? 40 00:01:49,440 --> 00:01:51,840 Nikolay: Yeah, hash function, hash index, right? 41 00:01:52,360 --> 00:01:54,520 Michael: Yeah, that's pretty much it. 42 00:01:54,520 --> 00:01:55,280 Hash table. 43 00:01:56,600 --> 00:01:57,880 Yeah, so in fact that's a good point. 44 00:01:57,880 --> 00:02:01,120 A lot of people have come across hashing from, well, looking 45 00:02:01,120 --> 00:02:06,740 at query plans for hash joins or backend engineers, very familiar 46 00:02:06,740 --> 00:02:12,040 with hash tables, can be a very efficient way of taking a value, 47 00:02:12,520 --> 00:02:16,720 hashing it, in this case we get a, well, Postgres behind the 48 00:02:16,720 --> 00:02:22,120 scenes is calculating a 4-byte integer, storing that, and then 49 00:02:22,120 --> 00:02:26,920 in the future if we want to look up the value by equality and equality 50 00:02:27,040 --> 00:02:33,120 only, we can hash the value we seek and look it up in the index 51 00:02:33,120 --> 00:02:33,980 in this case. 52 00:02:34,780 --> 00:02:41,040 So people that like theory talk a lot about big O notation, don't 53 00:02:41,040 --> 00:02:41,360 they? 54 00:02:41,360 --> 00:02:46,920 So this is one of those things that I struggle to love, but it has 55 00:02:46,920 --> 00:02:50,020 a low like O(1) is what people often say. 56 00:02:50,020 --> 00:02:53,400 I'm not sure it's strictly true, depending on how deep down that 57 00:02:53,400 --> 00:02:54,600 rabbit hole you dive. 58 00:02:54,620 --> 00:02:59,380 But yeah, it can be very, very efficient for looking up things 59 00:02:59,380 --> 00:03:01,860 by equality, especially in certain conditions. 60 00:03:03,420 --> 00:03:08,300 So that's like, actually a couple of things on details. 61 00:03:08,860 --> 00:03:10,080 I didn't realize you could... 62 00:03:10,080 --> 00:03:13,580 And it makes sense, but you can use a hash index on any value, 63 00:03:13,580 --> 00:03:16,020 any data type, which is quite cool. 64 00:03:16,080 --> 00:03:17,380 I guess the same is true. 65 00:03:17,480 --> 00:03:21,420 Nikolay: Postgres has a lot of internal hashing functions for 66 00:03:21,420 --> 00:03:23,960 all data types and also for the record. 67 00:03:23,960 --> 00:03:29,880 So you can say record for some table and it will give you a hash 68 00:03:30,520 --> 00:03:31,740 integer 4, right? 69 00:03:31,740 --> 00:03:35,280 Regular integer 32 bytes or bits. 70 00:03:35,540 --> 00:03:37,040 Michael: 32 bit, 4 byte, yeah. 71 00:03:37,200 --> 00:03:41,300 Nikolay: Yeah, and I actually recently rediscovered it and used 72 00:03:41,480 --> 00:03:42,340 it. It was interesting. 73 00:03:42,340 --> 00:03:46,120 I didn't use the hash index, but I used one of, probably, hash 74 00:03:46,120 --> 00:03:47,020 text function. 75 00:03:48,100 --> 00:03:50,320 Yeah, it was hash text function. 76 00:03:50,320 --> 00:03:55,100 I needed to, you know, when you need to reindex a lot of indexes, 77 00:03:56,140 --> 00:03:57,600 and it's a huge database. 78 00:03:58,380 --> 00:04:03,160 For example, after upgrade to Postgres 13 or 14, 14 especially, 79 00:04:03,160 --> 00:04:06,900 you need to re-index a lot of bit re-indexes to start benefiting 80 00:04:07,040 --> 00:04:11,180 from the optimizations Peter Geoghegan and others implemented. 81 00:04:12,520 --> 00:04:14,600 And obviously you need to re-index. 82 00:04:15,480 --> 00:04:19,160 You will do re-index concurrently and so on, it's good, but the 83 00:04:19,160 --> 00:04:22,060 question will be how to move faster. 84 00:04:23,320 --> 00:04:26,100 There are parallel workers, as we discussed, and so on. 85 00:04:26,120 --> 00:04:31,560 But probably you will decide to use multiple processes to issuing 86 00:04:31,720 --> 00:04:33,460 the reindex concurrently command. 87 00:04:33,620 --> 00:04:38,140 And in this case, if two of them try to reindex two different indexes 88 00:04:38,140 --> 00:04:41,020 which belong to the same table, you will have a deadlock. 89 00:04:43,480 --> 00:04:48,280 So my idea was we just need to attach each table associated with 90 00:04:48,280 --> 00:04:49,400 a particular worker. 91 00:04:50,500 --> 00:04:56,920 And to do it, I just took a table name, calculated hash text 92 00:04:56,920 --> 00:05:00,240 from it, and then modulo number of workers. 93 00:05:00,240 --> 00:05:04,280 If I have, for example, 5 workers, I just... 94 00:05:04,280 --> 00:05:08,800 So we have table name, hash text function, the hash text function 95 00:05:08,800 --> 00:05:13,340 produces number, like integer 4 number, and then we just modulo 96 00:05:13,340 --> 00:05:19,840 of 5, like remainder of the division, it will be like 0, 1, 2, 97 00:05:19,840 --> 00:05:22,540 3, 4, and that's it again, 0, 1, 2, 3, 4. 98 00:05:22,800 --> 00:05:26,760 And each index associated with a particular table will always go 99 00:05:26,760 --> 00:05:27,880 to a particular worker. 100 00:05:27,880 --> 00:05:31,780 So there will never be a conflict, no more deadlocks. 101 00:05:32,900 --> 00:05:35,780 So this is how you can redistribute work. 102 00:05:35,840 --> 00:05:39,860 Maybe also check if you need to redistribute, you need to check 103 00:05:39,860 --> 00:05:41,920 a lot of indexes for corruption. 104 00:05:42,340 --> 00:05:46,160 You can also use many like 16 workers, for example, and you redistribute 105 00:05:46,320 --> 00:05:48,500 work to avoid deadlocks. 106 00:05:48,580 --> 00:05:50,400 So, hash text is useful. 107 00:05:51,600 --> 00:05:55,680 I was already like CRC32 or something, I was thinking, oh, I 108 00:05:55,680 --> 00:06:00,200 need to, oh, by the way, I asked my bot to advise me, and it 109 00:06:00,200 --> 00:06:01,860 says, oh, there is hash text. 110 00:06:02,920 --> 00:06:06,140 I never used it before, or maybe I just forgot. 111 00:06:07,540 --> 00:06:12,260 And yeah, then I, if you're in psql, if you say backslash df, 112 00:06:13,060 --> 00:06:16,840 then hash star, you will see more than 50 functions. 113 00:06:16,840 --> 00:06:18,380 We checked it before the call. 114 00:06:18,380 --> 00:06:22,340 More than 50 functions, many of them return int4, some of 115 00:06:22,340 --> 00:06:23,740 them return int8. 116 00:06:23,960 --> 00:06:28,580 So it's good to have a lot of already implemented functions. 117 00:06:28,580 --> 00:06:30,460 You don't need to care, just use it. 118 00:06:31,560 --> 00:06:32,060 Good? 119 00:06:32,200 --> 00:06:33,040 Yeah, absolutely. 120 00:06:33,280 --> 00:06:36,440 Yes, Small comment, slightly off topic. 121 00:06:37,120 --> 00:06:40,220 Michael: Yeah, I mean related to hashing, but I guess completely 122 00:06:40,240 --> 00:06:40,740 unrelated 123 00:06:40,760 --> 00:06:41,620 Nikolay: to hash indexing. 124 00:06:42,100 --> 00:06:43,020 Michael: Yeah, exactly. 125 00:06:43,520 --> 00:06:47,540 So you mentioned already, there's a big change in the history 126 00:06:47,540 --> 00:06:50,760 of hash indexes I think is worth mentioning, and that's the version 127 00:06:50,760 --> 00:06:56,260 10 made hash indexes crash safe or write-ahead log. 128 00:06:58,660 --> 00:07:02,400 Which is really important, like before that it meant if you had 129 00:07:02,400 --> 00:07:07,640 like, it made them basically unusable or unsafe to use, especially 130 00:07:07,640 --> 00:07:10,900 in a high availability setup if you had replication and then 131 00:07:10,900 --> 00:07:14,840 failed over, your replica wouldn't have that index. 132 00:07:15,700 --> 00:07:16,860 Nikolay: Ephemeral indexes. 133 00:07:17,640 --> 00:07:18,500 Michael: Yeah, right. 134 00:07:18,900 --> 00:07:21,540 Nikolay: But not anymore, so it's solved. 135 00:07:22,200 --> 00:07:26,300 The same was solved with GIST a very long ago. 136 00:07:26,660 --> 00:07:31,880 Originally GIST was also not write-ahead log-supported at all, but it was 137 00:07:31,880 --> 00:07:34,020 implemented like 20 something years ago. 138 00:07:35,740 --> 00:07:38,400 Michael: Speaking of 20 something years ago, I was looking up, 139 00:07:38,400 --> 00:07:42,380 I was trying to find out when hash indexes were added, and the 140 00:07:42,380 --> 00:07:45,720 git history doesn't go far enough back, I don't think, and the 141 00:07:45,720 --> 00:07:51,000 docs, the online docs 7.2 is the oldest version on the online 142 00:07:51,000 --> 00:07:52,620 docs and they have hash indexes. 143 00:07:53,000 --> 00:07:55,380 So that's as far back as 2002 already. 144 00:07:55,600 --> 00:07:58,040 So they're more than 20 years old as well. 145 00:07:58,080 --> 00:08:01,120 Nikolay: Well I'm quite sure they're older because it's maybe 146 00:08:01,120 --> 00:08:03,660 one of the simplest types of index you can implement. 147 00:08:04,040 --> 00:08:09,060 But interesting that WAL was not implemented for so long, right? 148 00:08:09,520 --> 00:08:12,420 Right. Until 2017, right? 149 00:08:12,740 --> 00:08:14,060 Michael: I think you'd struggle to get... 150 00:08:15,060 --> 00:08:18,840 I could be wrong, but I think if we didn't have hash indexes 151 00:08:18,840 --> 00:08:22,660 in Postgres today, they wouldn't get added and they would be 152 00:08:22,660 --> 00:08:24,560 encouraged to be added via an extension. 153 00:08:24,640 --> 00:08:28,520 That's my guess as to how they would be implemented if they were 154 00:08:28,520 --> 00:08:29,280 done today. 155 00:08:29,640 --> 00:08:33,660 But I mean, I might be, or maybe via the contrib modules. 156 00:08:34,540 --> 00:08:35,360 Nikolay: Well, yeah. 157 00:08:35,640 --> 00:08:36,050 Michael: But we have them. 158 00:08:36,050 --> 00:08:37,700 They're first class supported. 159 00:08:38,420 --> 00:08:40,620 They're well logged now. 160 00:08:40,840 --> 00:08:42,480 They're replicated and everything. 161 00:08:42,660 --> 00:08:46,880 Crash safe, even while they're doing complex operations, it might 162 00:08:46,880 --> 00:08:49,100 get to a bit later, it will recover. 163 00:08:49,140 --> 00:08:51,060 So we can use them if we want to. 164 00:08:51,060 --> 00:08:54,220 I guess the question then is why would we? 165 00:08:54,400 --> 00:08:58,040 And you mentioned something before the call I found very interesting 166 00:08:58,040 --> 00:08:59,440 and it's probably very telling. 167 00:09:00,060 --> 00:09:03,540 But should I ask you, have you ever used the hash index? 168 00:09:04,080 --> 00:09:08,140 Nikolay: Only in experimental environments, never on production. 169 00:09:09,020 --> 00:09:12,860 I think I saw them in the wild, you know, saw them. 170 00:09:17,220 --> 00:09:20,780 I don't think I ever seriously considered them. 171 00:09:21,160 --> 00:09:25,660 But you need to take into account that version 10 and later, 172 00:09:25,840 --> 00:09:28,100 it's already mostly my consulting practice. 173 00:09:28,100 --> 00:09:32,040 Before that, I created a lot of systems, social networks and 174 00:09:32,040 --> 00:09:32,740 so on. 175 00:09:33,580 --> 00:09:37,060 It was before Postgres 10, so I never considered them. 176 00:09:37,500 --> 00:09:40,360 My memory says stay away from them. 177 00:09:40,680 --> 00:09:42,260 Michael: Yeah, that's a really good point. 178 00:09:42,260 --> 00:09:46,360 So it's only since 2017 that you should even be considering using 179 00:09:46,360 --> 00:09:46,860 these. 180 00:09:46,980 --> 00:09:49,900 So I'd like to first cover, I know there's going to be quite 181 00:09:49,900 --> 00:09:52,480 a few limitations that we need to talk about, but I'd like to 182 00:09:52,480 --> 00:09:55,280 cover some of the potential benefits of hash indexes. 183 00:09:55,280 --> 00:09:57,380 Like I think there's a couple that are worth mentioning. 184 00:09:57,880 --> 00:09:58,380 Nikolay: Size? 185 00:09:59,060 --> 00:10:05,780 Michael: Yes, So they can be, so for, imagine if you are hashing 186 00:10:05,820 --> 00:10:10,920 a fairly large string or even a number, or just a piece of data 187 00:10:10,920 --> 00:10:13,400 that is more than 4 bytes. 188 00:10:14,320 --> 00:10:17,800 When you are indexing it with a hash index, it's only having 189 00:10:17,800 --> 00:10:22,300 to store 4 bytes, or a bit more, but it's not having to store 190 00:10:22,300 --> 00:10:23,700 that value in the index. 191 00:10:23,860 --> 00:10:28,740 And that means for larger data types or larger values, it can 192 00:10:28,740 --> 00:10:33,580 be significantly smaller than a B-tree index with some caveats. 193 00:10:34,760 --> 00:10:38,560 Naturally, there are some optimizations for B-tree that we've 194 00:10:38,560 --> 00:10:39,980 got in recent versions. 195 00:10:40,440 --> 00:10:44,700 And yeah, this really stands out for larger values basically 196 00:10:44,720 --> 00:10:50,020 and for indexes that are unique or mostly unique, like, oh sorry, 197 00:10:50,020 --> 00:10:52,540 for columns that are unique or mostly unique. 198 00:10:53,160 --> 00:10:55,020 So that's a big difference as well. 199 00:10:55,120 --> 00:10:57,140 But there are cases like that, right? 200 00:10:57,440 --> 00:11:02,050 Like I did a collaboration with Haki Benita for his blog and 201 00:11:02,050 --> 00:11:03,620 he did a lot of the work. 202 00:11:03,620 --> 00:11:06,560 He deserves most of the credit but he put me down as a co-author 203 00:11:06,560 --> 00:11:11,060 which was kind of him and we looked at quite a few things and 204 00:11:11,520 --> 00:11:16,300 for the use case that he had for hash indexes which was a URL 205 00:11:16,340 --> 00:11:21,260 shortening service you get some quite large URLs can be quite 206 00:11:21,260 --> 00:11:24,520 large strings in the grand scheme of things, especially for URLs. 207 00:11:24,960 --> 00:11:28,860 So they actually came out quite a lot smaller when you put a 208 00:11:28,860 --> 00:11:32,120 hash index on it versus a B-tree, which is pretty cool, Even 209 00:11:32,120 --> 00:11:36,380 post deduplication, because these are largely going to be unique. 210 00:11:36,380 --> 00:11:40,020 We didn't make them completely unique, but largely your URL shortening 211 00:11:40,020 --> 00:11:42,040 thing is going to be unique strings. 212 00:11:42,380 --> 00:11:45,880 So yeah, they can be significantly smaller, they can be significantly 213 00:11:45,920 --> 00:11:47,320 bigger, depending on your data. 214 00:11:47,320 --> 00:11:51,100 But I think size is underrated in terms of index types. 215 00:11:51,100 --> 00:11:54,580 I think not only we're going to look at performance in a bit, 216 00:11:54,580 --> 00:11:57,520 but base and the cache, you know, we've talked about this kind 217 00:11:57,520 --> 00:12:00,120 of thing before, but I think it is worth mentioning. 218 00:12:00,860 --> 00:12:05,040 Nikolay: Yeah, by the way, if we try to index in a regular way 219 00:12:05,040 --> 00:12:09,520 with B-tree some large text values, there's some limitation, 220 00:12:09,620 --> 00:12:10,320 I always forget. 221 00:12:10,320 --> 00:12:10,680 Michael: Oh, good 222 00:12:10,680 --> 00:12:11,180 Nikolay: point. 223 00:12:11,520 --> 00:12:16,060 Yeah, so sometimes we just need to apply hash and use expression 224 00:12:16,880 --> 00:12:20,640 B-tree on top of it, but it's not, of course, it's not a hash index, 225 00:12:20,640 --> 00:12:22,740 but we use a combination, right? 226 00:12:22,740 --> 00:12:27,580 In this case, we cannot order these values, but for exact matching, 227 00:12:27,840 --> 00:12:30,040 this is what can work well. 228 00:12:30,460 --> 00:12:33,840 Michael: Why did you, why do you hash it and then put a B-tree 229 00:12:33,840 --> 00:12:36,960 index on instead of just putting a hash index on? 230 00:12:38,100 --> 00:12:40,360 Nikolay: Because again, I'm an old guy. 231 00:12:41,680 --> 00:12:45,660 You know, like again, my mom says stay away from hash. 232 00:12:45,660 --> 00:12:49,900 By the way, with my bot and GitHub, we just checked when hash 233 00:12:49,900 --> 00:12:54,520 build, the hash build function was added and it's 27 years ago, 234 00:12:54,520 --> 00:12:55,940 original Postgres 95. 235 00:12:56,400 --> 00:12:56,900 Michael: 27? 236 00:12:57,260 --> 00:12:59,880 Nikolay: Yeah, original source code of Postgres 95. 237 00:13:00,720 --> 00:13:01,220 Michael: Wow. 238 00:13:01,620 --> 00:13:03,660 Nikolay: Committed by Mark Fournier. 239 00:13:04,900 --> 00:13:10,840 It had already this hash build, which builds a new hash index. 240 00:13:11,000 --> 00:13:12,780 So this is super old stuff. 241 00:13:12,780 --> 00:13:16,620 I think maybe it even came from original Postgres. 242 00:13:16,620 --> 00:13:19,840 Obviously, it came from original Postgres, maybe even from Ingres, 243 00:13:19,840 --> 00:13:20,460 who knows. 244 00:13:20,740 --> 00:13:24,220 So maybe it's even more than 30 years ago. 245 00:13:24,720 --> 00:13:26,240 Michael: Yeah, very cool. 246 00:13:26,480 --> 00:13:28,440 But yeah, that's another advantage, right? 247 00:13:28,440 --> 00:13:30,140 There's no limit to the size. 248 00:13:30,180 --> 00:13:32,980 Nikolay: So you think, yeah, that's great. 249 00:13:32,980 --> 00:13:37,120 So you think I just can create a hash index if I have very large 250 00:13:37,120 --> 00:13:38,000 text values? 251 00:13:38,320 --> 00:13:39,900 Michael: You're losing the same things, right? 252 00:13:39,900 --> 00:13:41,540 You can't order by them. 253 00:13:42,040 --> 00:13:47,040 And you're kind of doing a self-rolled one, which maybe even proves 254 00:13:47,040 --> 00:13:48,300 that we don't need them. 255 00:13:49,120 --> 00:13:53,380 But I think less footprint, right, because you're having to... 256 00:13:53,700 --> 00:13:54,720 Yeah, I'm not sure. 257 00:13:54,800 --> 00:13:57,780 I think there might be some weird trade-offs around maintenance, 258 00:13:57,980 --> 00:14:01,040 but I think you'd largely recreate them, yeah. 259 00:14:01,720 --> 00:14:05,920 Nikolay: So if I say create table T something as select and then 260 00:14:05,920 --> 00:14:10,200 I say repeat as column C1 and I repeat some letter a million 261 00:14:10,200 --> 00:14:14,380 times, I have obviously a text value, a million letters, when I 262 00:14:14,380 --> 00:14:18,220 say create index on this value, on this table, on this column, 263 00:14:18,480 --> 00:14:26,460 in this case, B-tree will say index row requires 11,464 bytes, so 264 00:14:26,480 --> 00:14:27,500 11k, right? 265 00:14:27,520 --> 00:14:28,020 Mm-hmm. 266 00:14:29,320 --> 00:14:30,420 Ah, in my case. 267 00:14:30,480 --> 00:14:35,100 But maximum size is 8K, okay, like block size. 268 00:14:35,900 --> 00:14:40,960 But if I say using hash, right, using hash, same column, create 269 00:14:40,960 --> 00:14:42,220 index, yeah, it works. 270 00:14:42,700 --> 00:14:46,640 So why do I, okay, I'm an old guy, I have old habits, I need to get 271 00:14:46,640 --> 00:14:47,500 rid of it. 272 00:14:47,520 --> 00:14:48,680 Actually, this is the perfect case. 273 00:14:48,680 --> 00:14:49,940 Michael: Or consider it. 274 00:14:50,060 --> 00:14:53,860 Nikolay: Large text values, I don't need to hash first and then 275 00:14:53,860 --> 00:14:56,100 B-tree, I just use a hash index in this case. 276 00:14:57,180 --> 00:14:58,580 I will need to think about it. 277 00:14:58,580 --> 00:14:59,240 Thank you. 278 00:14:59,340 --> 00:15:00,040 This is unexpected. 279 00:15:00,040 --> 00:15:01,280 Michael: There are some reasons you might not 280 00:15:01,280 --> 00:15:01,720 Nikolay: want to 281 00:15:01,720 --> 00:15:02,460 Michael: do this. 282 00:15:02,560 --> 00:15:03,060 Nikolay: Why? 283 00:15:03,960 --> 00:15:05,740 Michael: Okay, well, let's go through the positives first. 284 00:15:05,740 --> 00:15:06,680 I promised positives. 285 00:15:07,280 --> 00:15:11,140 Size can be, it can be worse, but it can be a lot better. 286 00:15:11,640 --> 00:15:17,060 Speed can be better, not just lookups, but also the average latency 287 00:15:17,160 --> 00:15:17,860 of inserts. 288 00:15:19,280 --> 00:15:23,460 I definitely had some discussions with Haki when we were reporting 289 00:15:23,480 --> 00:15:26,100 these numbers that we could have done a better job, I think. 290 00:15:26,120 --> 00:15:28,860 I hold my hands up, this was a couple of years ago, and I've 291 00:15:28,860 --> 00:15:30,260 learned a lot since then. 292 00:15:31,520 --> 00:15:35,200 But insert performance on a batch was, or when we were inserting 293 00:15:35,200 --> 00:15:40,340 1 row at a time, was about 10% slower, I think, for the B-trees 294 00:15:40,520 --> 00:15:43,180 than versus hash, which I was a bit surprised by, because I knew 295 00:15:43,180 --> 00:15:47,860 there was some overhead around splits for hash indexes, or higher 296 00:15:47,860 --> 00:15:49,340 overheads some of the time. 297 00:15:49,640 --> 00:15:50,780 But lookup performance... 298 00:15:50,980 --> 00:15:53,260 Nikolay: B-tree also has splits sometimes, right? 299 00:15:53,260 --> 00:15:54,060 Michael: Yeah, of course. 300 00:15:54,240 --> 00:15:55,420 Nikolay: So we need to compare. 301 00:15:56,120 --> 00:16:00,720 In this case, we can't say hash index has this disadvantage and 302 00:16:00,720 --> 00:16:01,340 that's it, right? 303 00:16:01,340 --> 00:16:03,020 B-tree also has this disadvantage. 304 00:16:03,920 --> 00:16:07,760 Michael: Yeah, so they're kind of bigger splits less often. 305 00:16:08,160 --> 00:16:09,440 Yeah, that's a good point. 306 00:16:09,720 --> 00:16:13,280 But lookup speed, I was expecting a difference and admittedly, 307 00:16:13,360 --> 00:16:14,560 they're both really fast, right? 308 00:16:14,560 --> 00:16:17,220 We're talking about basically key lookups. 309 00:16:18,320 --> 00:16:22,900 So B-trees, we've always raved about how good they are at like 310 00:16:22,900 --> 00:16:24,120 key lookups, right? 311 00:16:24,520 --> 00:16:27,680 Nikolay: That's what- Yeah, just a few pages to find a proper 312 00:16:28,000 --> 00:16:33,760 reference to, like it's just a few blocks to fetch from disk 313 00:16:33,760 --> 00:16:36,100 or to read from page cache. 314 00:16:37,360 --> 00:16:41,100 Michael: I think we only did about 100,000 lookups and it was 315 00:16:41,100 --> 00:16:44,020 both of them were under a second to do 100,000 lookups. 316 00:16:44,540 --> 00:16:46,920 Nikolay: So you haven't checked shared buffers at that time? 317 00:16:47,360 --> 00:16:49,620 Only timing, which is quite volatile. 318 00:16:49,900 --> 00:16:50,380 Michael: Yeah. 319 00:16:50,380 --> 00:16:52,740 Again, I would do some things differently now. 320 00:16:53,040 --> 00:16:54,520 This is a couple of years ago. 321 00:16:54,520 --> 00:17:00,320 But the lookups, whilst they were both under a second, I think 322 00:17:00,320 --> 00:17:04,220 it was something like 30 or 40% faster for the hash lookups, 323 00:17:04,280 --> 00:17:06,860 which was, I thought, actually quite significant. 324 00:17:08,160 --> 00:17:13,780 So I think there are some cases where in some extreme, if you've 325 00:17:13,780 --> 00:17:18,460 got long strings and you only care about uniqueness lookups, 326 00:17:19,300 --> 00:17:22,080 then I think there's a case for using them. 327 00:17:22,080 --> 00:17:25,200 But that is obviously a very narrow, very specific use case. 328 00:17:25,200 --> 00:17:28,980 Nikolay: I wonder in these tests if you considered the planning 329 00:17:28,980 --> 00:17:33,480 time, because I guess if table is not absolutely huge, like billions 330 00:17:33,480 --> 00:17:34,080 of rows. 331 00:17:34,080 --> 00:17:37,920 In this case, planning time can be maybe even a bigger contributor 332 00:17:38,040 --> 00:17:39,640 than index scan itself. 333 00:17:41,200 --> 00:17:45,240 So we need to exclude it using maybe prepared statements and consider 334 00:17:45,240 --> 00:17:45,760 only execution. 335 00:17:46,960 --> 00:17:49,540 Michael: That would be a good test, but also why would the planning 336 00:17:49,540 --> 00:17:51,540 time differ between index types? 337 00:17:52,720 --> 00:17:55,880 Nikolay: I'm not saying it differs, I'm saying if you're comparing 338 00:17:56,800 --> 00:18:01,880 these latencies, what percentage of planning time is there here? 339 00:18:01,880 --> 00:18:06,600 So we need to exclude it to compare clean numbers, right? 340 00:18:06,740 --> 00:18:07,860 Michael: Yeah, that'd be great. 341 00:18:08,000 --> 00:18:09,820 I don't think we used prepared statements. 342 00:18:09,860 --> 00:18:11,140 I'm pretty sure we didn't. 343 00:18:11,280 --> 00:18:14,800 Nikolay: And honestly, I wouldn't look at timing here at the 344 00:18:14,800 --> 00:18:16,720 first, like this first metric. 345 00:18:16,720 --> 00:18:19,180 I would still prefer looking at shared buffers. 346 00:18:19,340 --> 00:18:20,040 But okay. 347 00:18:21,460 --> 00:18:24,360 Michael: But the point is they can be more efficient and that's 348 00:18:24,360 --> 00:18:27,800 partly because they're, if you imagine a B-tree structure, you 349 00:18:27,800 --> 00:18:29,440 might have to go through 350 00:18:31,560 --> 00:18:31,960 Nikolay: a few 351 00:18:31,960 --> 00:18:35,660 Michael: hops, especially once your table gets really large. 352 00:18:35,920 --> 00:18:38,220 Now we've talked about partitioning so many times, right? 353 00:18:38,220 --> 00:18:41,140 So you could argue that you could keep your B-trees relatively 354 00:18:41,640 --> 00:18:43,380 small in the grand scheme of things. 355 00:18:43,680 --> 00:18:47,860 But if we do get large enough, the hash structure is just so 356 00:18:47,860 --> 00:18:50,640 much more efficient and that will show up in shared buffers as well. 357 00:18:50,640 --> 00:18:52,540 So that is the argument, I think. 358 00:18:52,540 --> 00:18:56,740 So you've got smaller, potentially smaller, indexes for certain, 359 00:18:57,040 --> 00:19:03,300 you know, for highly unique, slightly larger data types and potential 360 00:19:03,480 --> 00:19:06,560 benefits on the insert and lookup speeds. 361 00:19:09,020 --> 00:19:10,420 Nikolay: Have you considered collisions? 362 00:19:10,440 --> 00:19:13,480 Because if you have only integer four, obviously you might have 363 00:19:13,480 --> 00:19:17,180 collisions and you need to resolve this additional hops, right? 364 00:19:17,180 --> 00:19:19,260 Additional comparison and so on. 365 00:19:19,540 --> 00:19:25,360 Have you considered trying to measure some edge cases? 366 00:19:26,600 --> 00:19:30,020 Michael: I looked into this, I looked into the internals a bit. 367 00:19:30,020 --> 00:19:32,800 Well, actually there's a couple of really good internals pages 368 00:19:32,800 --> 00:19:35,040 on the PostgreSQL docs on hash indexes. 369 00:19:35,940 --> 00:19:39,080 And it's only a throwaway word quite early on in one of them that 370 00:19:39,080 --> 00:19:41,300 mentions that they are a lossy index type. 371 00:19:41,380 --> 00:19:45,060 So it's not the only lossy index type, but it means that just 372 00:19:45,060 --> 00:19:51,700 because we get a match for the hash, the 32-bit integer, doesn't 373 00:19:51,700 --> 00:19:53,560 mean we do actually have a hit. 374 00:19:53,560 --> 00:19:55,460 We could have a collision. 375 00:19:55,760 --> 00:20:01,020 So there is a recheck, and you can get rows removed by index 376 00:20:01,020 --> 00:20:04,740 recheck type things in your explain, but I haven't seen it. 377 00:20:04,740 --> 00:20:07,280 Like we're talking, but you'd have to have, well, I'm not sure you'd have to have billions, 378 00:20:07,280 --> 00:20:09,800 but I'm guessing you'd start, you know, in the tests I've done, 379 00:20:09,800 --> 00:20:13,120 I haven't seen any. 380 00:20:13,320 --> 00:20:15,920 But I guess you'd pay that overhead for every lookup, right? 381 00:20:15,920 --> 00:20:18,160 Like you're having to do that recheck all the time because they're 382 00:20:18,160 --> 00:20:18,660 lossy. 383 00:20:18,720 --> 00:20:21,680 It's not just when there are collisions that you pay that recheck. 384 00:20:22,060 --> 00:20:25,280 So those numbers I was talking about include the overhead of 385 00:20:25,280 --> 00:20:25,780 rechecks. 386 00:20:27,340 --> 00:20:27,840 Nikolay: Okay. 387 00:20:28,580 --> 00:20:30,040 So what are the downsides? 388 00:20:30,960 --> 00:20:31,800 Why shouldn't I 389 00:20:31,800 --> 00:20:32,720 Michael: use- So many. 390 00:20:33,140 --> 00:20:33,980 Nikolay: So many, okay. 391 00:20:33,980 --> 00:20:36,260 Index only scan, as I remember, right? 392 00:20:36,560 --> 00:20:37,060 Yep. 393 00:20:37,740 --> 00:20:38,500 Lack of 394 00:20:38,940 --> 00:20:39,340 Michael: index only scan. 395 00:20:39,340 --> 00:20:42,940 Because we don't have the value in the index, it's literally 396 00:20:42,980 --> 00:20:45,200 impossible to have an index only scan. 397 00:20:45,340 --> 00:20:45,840 Right. 398 00:20:46,100 --> 00:20:46,808 Nikolay: And that's bad. 399 00:20:46,808 --> 00:20:47,277 Yeah. Right. 400 00:20:47,277 --> 00:20:47,980 And that's bad. 401 00:20:47,980 --> 00:20:48,480 Yeah. 402 00:20:49,780 --> 00:20:50,580 Yeah, so. 403 00:20:50,900 --> 00:20:56,020 But if I, in my case, if I have large text value, and I consider 404 00:20:56,160 --> 00:20:59,400 B-tree versus hash expression. 405 00:21:00,940 --> 00:21:03,300 Michael: You can't have an index only scan there either. 406 00:21:03,740 --> 00:21:06,440 Nikolay: Yeah, because I cannot fetch the original value. 407 00:21:07,080 --> 00:21:10,620 So it's not a reason that would stop me from using hash index. 408 00:21:11,660 --> 00:21:12,280 Okay. Right. 409 00:21:12,700 --> 00:21:15,120 Michael: In fact, if you consider collisions for that. 410 00:21:15,540 --> 00:21:16,720 Nikolay: Yeah, well, yeah, yeah. 411 00:21:16,720 --> 00:21:18,220 Michael: You have to implement it yourself. 412 00:21:18,900 --> 00:21:19,980 Nikolay: That's true, yeah. 413 00:21:20,900 --> 00:21:22,700 Michael: So I think the big one is ordering. 414 00:21:24,340 --> 00:21:26,820 Obviously not in the case you're talking about where you're hashing, 415 00:21:26,840 --> 00:21:32,140 but the big advantage B-trees have is they order your data and 416 00:21:32,580 --> 00:21:35,060 that supports so many different things. 417 00:21:35,060 --> 00:21:37,700 Nikolay: And greater than or less than comparison, obviously. 418 00:21:38,300 --> 00:21:38,800 Yeah, 419 00:21:39,240 --> 00:21:43,620 Michael: range scans, yeah, it feels like a limitless number 420 00:21:43,620 --> 00:21:45,740 of things that that then enables. 421 00:21:46,060 --> 00:21:49,280 Nikolay: Right, you can deal with collation issues, it's so cool. 422 00:21:49,900 --> 00:21:52,700 They can be corrupted because of collation changes. 423 00:21:52,700 --> 00:21:54,120 Michael: I mean that's a good point. 424 00:21:55,240 --> 00:21:57,780 Maybe hash indexes are less likely to get corrupted. 425 00:21:58,140 --> 00:21:58,640 Nikolay: Right. 426 00:21:59,700 --> 00:22:00,200 Okay. 427 00:22:01,780 --> 00:22:06,380 Michael: Actually, what if the hash function created a different 428 00:22:06,380 --> 00:22:07,260 value instead? 429 00:22:08,500 --> 00:22:10,920 Nikolay: I don't think it's a good question. 430 00:22:10,920 --> 00:22:13,220 I don't know how it's implemented internally. 431 00:22:13,320 --> 00:22:15,560 It should not be super difficult. 432 00:22:15,760 --> 00:22:20,740 But obviously, how will it hash non-latin characters and so 433 00:22:20,740 --> 00:22:21,240 on? 434 00:22:21,340 --> 00:22:21,900 Michael: Yeah, exactly. 435 00:22:21,900 --> 00:22:22,660 Does it depend 436 00:22:22,660 --> 00:22:24,960 Nikolay: on glibc version change, for example? 437 00:22:24,960 --> 00:22:26,300 It's an interesting question. 438 00:22:26,580 --> 00:22:29,600 I never had this question because, again, I'm not using them 439 00:22:29,600 --> 00:22:31,220 in the wild often at all. 440 00:22:31,880 --> 00:22:35,140 Michael: Another huge difference, and I don't think this is a, 441 00:22:35,140 --> 00:22:38,040 I don't think this is necessarily based on it's, I don't think 442 00:22:38,040 --> 00:22:40,380 this is like the index only scans where it's literally impossible, 443 00:22:40,440 --> 00:22:44,440 but you, in Postgres, we can't use hash indexes to enforce uniqueness. 444 00:22:44,700 --> 00:22:48,220 So for unique constraints, which is a big deal. 445 00:22:48,220 --> 00:22:51,260 Like, that's a really, like, for example, we can't use them for 446 00:22:51,260 --> 00:22:52,100 primary keys. 447 00:22:52,480 --> 00:22:55,020 Or like, there's no point because we've already got a B-tree 448 00:22:55,020 --> 00:22:56,020 index on them. 449 00:22:56,280 --> 00:22:56,780 Nikolay: Okay. 450 00:22:57,620 --> 00:22:58,480 Good to know. 451 00:23:00,140 --> 00:23:00,820 What else? 452 00:23:00,900 --> 00:23:01,820 Multicolumn, right? 453 00:23:01,820 --> 00:23:02,880 I remember now. 454 00:23:03,160 --> 00:23:03,660 Yeah, 455 00:23:03,940 --> 00:23:04,440 Michael: Exactly. 456 00:23:06,400 --> 00:23:08,580 So, hash indexes don't support multicolumn. 457 00:23:09,640 --> 00:23:12,540 Nikolay: You can combine values, so you can, I don't know? 458 00:23:15,040 --> 00:23:18,520 Michael: But more to the point, one of the nice things about multi-column 459 00:23:18,520 --> 00:23:22,240 B-tree indexes is you've also got the sorting sorted by the first 460 00:23:22,240 --> 00:23:23,720 column and sorted by the second. 461 00:23:23,720 --> 00:23:25,840 So we just don't have advantages like that. 462 00:23:25,840 --> 00:23:28,580 And it ties into the index-only scans as well. 463 00:23:28,820 --> 00:23:31,760 One of the best things about multi-column indexes is we then get 464 00:23:31,760 --> 00:23:34,020 potential for index-only scans as well. 465 00:23:34,540 --> 00:23:37,080 So these things, a lot of them are interplay. 466 00:23:37,720 --> 00:23:41,620 One difference that isn't necessarily an issue, but I found interesting, 467 00:23:41,640 --> 00:23:45,920 I thought you might as well, was that the fill factor for hash 468 00:23:45,920 --> 00:23:49,620 indexes is 75 by default, whereas B-tree is 90. 469 00:23:49,900 --> 00:23:50,720 Really interesting. 470 00:23:51,740 --> 00:23:53,260 Nikolay: Some old decision, I guess. 471 00:23:53,380 --> 00:23:56,140 Maybe not, but maybe, I don't know. 472 00:23:56,740 --> 00:23:57,440 Yeah, Michael: Interesting. 473 00:23:57,440 --> 00:24:01,220 Well, I guess, like, in the blog post, we looked at what does 474 00:24:01,220 --> 00:24:05,420 it look like if you change the fill factor to 25, 50 or 100. 475 00:24:06,000 --> 00:24:12,800 And at 100 versus 75, you just see, you see it not getting larger 476 00:24:12,800 --> 00:24:17,680 as quickly, but when it does, it bounces at a faster rate. 477 00:24:17,680 --> 00:24:23,000 So as the index grows, it grows less at first and then more all 478 00:24:23,000 --> 00:24:23,660 at once. 479 00:24:23,680 --> 00:24:30,040 So my guess is it's some trade-off between general size and performance 480 00:24:30,300 --> 00:24:34,540 and the impact of splits or the cost we pay at splits. 481 00:24:34,540 --> 00:24:37,480 And in fact, this is probably the biggest one you'd want to consider 482 00:24:37,640 --> 00:24:41,600 on the difference between doing a B-tree index on a hash function 483 00:24:41,600 --> 00:24:43,580 versus using a hash index. 484 00:24:43,780 --> 00:24:47,860 And that's how insert performance would look in 485 00:24:47,860 --> 00:24:51,480 terms of probably not average latency, but maybe the standard 486 00:24:51,480 --> 00:24:52,680 deviation of latency. 487 00:24:53,340 --> 00:24:57,900 So with hash indexes, the way they're structured, we have buckets 488 00:24:57,900 --> 00:25:02,080 that values can go into, but beyond a certain size, Postgres 489 00:25:02,080 --> 00:25:04,060 works it all out, and it does all this for you. 490 00:25:04,460 --> 00:25:06,740 It decides, oh, we need more space. 491 00:25:07,120 --> 00:25:08,860 It'd be best to have another bucket. 492 00:25:09,240 --> 00:25:11,180 Let's split one of the existing buckets. 493 00:25:11,480 --> 00:25:14,800 And then we pay that overhead during the insert, right? 494 00:25:14,800 --> 00:25:16,940 Like that, it can slow down your insert. 495 00:25:17,300 --> 00:25:21,500 So my guess is the standard deviation for inserts would be perhaps 496 00:25:21,500 --> 00:25:24,260 significantly higher than B-trees but I haven't checked. 497 00:25:24,720 --> 00:25:26,600 It's another thing I would like to do. 498 00:25:26,980 --> 00:25:27,480 Yeah. 499 00:25:28,580 --> 00:25:33,760 So on high throughput, there is a note in the internals document 500 00:25:33,800 --> 00:25:35,100 about high throughput. 501 00:25:35,280 --> 00:25:39,400 Let's say you've got a really high throughput table, you might 502 00:25:39,400 --> 00:25:42,340 actually be better off with your B-tree index that you mentioned 503 00:25:42,340 --> 00:25:43,540 than a hash one. 504 00:25:44,380 --> 00:25:44,880 Nikolay: Okay. 505 00:25:45,660 --> 00:25:49,940 And multi-column index, I think we can, I've just checked by 506 00:25:49,940 --> 00:25:53,740 the way, hash index applied to whole table row. 507 00:25:54,520 --> 00:25:55,220 It works. 508 00:25:55,440 --> 00:25:57,980 Produces interferer number, it works. 509 00:25:57,980 --> 00:26:02,840 I think we might probably decide to use it to, I remember I was 510 00:26:02,840 --> 00:26:07,880 always using MD5 hash, converting row first to text and then 511 00:26:07,880 --> 00:26:12,540 MD5, but maybe hash index is a better choice when we need to, 512 00:26:12,540 --> 00:26:17,540 for example, compare the content of two snapshots, row by row, 513 00:26:17,540 --> 00:26:24,640 you know, like to find which rows were changed, comparing them, 514 00:26:24,720 --> 00:26:28,260 like joining by ID, by primary key, for example, and then we 515 00:26:28,260 --> 00:26:31,580 compare hashes of whole row, right? 516 00:26:31,580 --> 00:26:33,660 In this case, hash index is working. 517 00:26:34,060 --> 00:26:38,620 I wonder if, like, if we, okay, multi-column index is not supported. 518 00:26:38,640 --> 00:26:44,540 What if we just combine multiple columns and produce hash out 519 00:26:44,540 --> 00:26:47,140 of them and almost the same, right? 520 00:26:48,300 --> 00:26:49,840 It's strange that it's not supported. 521 00:26:50,280 --> 00:26:53,200 Michael: I mean, we can still only use uniqueness lookups, right? 522 00:26:53,200 --> 00:26:58,020 So I guess it's the case where you want to see if two columns when 523 00:26:58,020 --> 00:27:01,800 combined are equal to the record you've stored, yeah? 524 00:27:01,980 --> 00:27:04,120 But I'm not seeing the same benefits. 525 00:27:05,280 --> 00:27:07,740 Nikolay: Okay, I will check a couple of things more. 526 00:27:09,280 --> 00:27:11,240 Any more limitations or that's it? 527 00:27:12,340 --> 00:27:13,440 Michael: I don't think so. 528 00:27:13,880 --> 00:27:14,860 I might have missed one. 529 00:27:14,860 --> 00:27:17,940 But those for me are substantial limitations. 530 00:27:18,080 --> 00:27:20,840 And I think if you're, like when you're, imagine when you're 531 00:27:20,840 --> 00:27:24,060 designing a system where you're choosing an index type for a 532 00:27:24,060 --> 00:27:30,240 new feature building, and it turns out that right now, the hash 533 00:27:30,240 --> 00:27:32,140 might just about come out on top. 534 00:27:32,480 --> 00:27:35,500 Maybe you get slightly smaller index, maybe your lookups are 535 00:27:35,500 --> 00:27:36,420 slightly faster. 536 00:27:37,200 --> 00:27:40,120 I'm not sure I'd still advise using a hash index. 537 00:27:40,120 --> 00:27:43,220 I think I might say, look, if your requirements change in the 538 00:27:43,220 --> 00:27:46,360 future, if you ever do need to look up like a range of these 539 00:27:46,360 --> 00:27:49,760 values, if there's some analytics you might need to do on it 540 00:27:49,760 --> 00:27:52,600 or something, you might have been better off with a B-tree index. 541 00:27:52,600 --> 00:27:53,940 You've got that extra flexibility. 542 00:27:54,320 --> 00:27:57,540 You've also got, I think, probably more effort being put into 543 00:27:57,540 --> 00:27:59,680 optimizations on them going forwards. 544 00:28:00,140 --> 00:28:03,800 If it's a tight call between them, I might still encourage people 545 00:28:03,800 --> 00:28:05,300 not to use them in general. 546 00:28:05,740 --> 00:28:07,940 Nikolay: I never did it, I just did it. 547 00:28:07,940 --> 00:28:13,260 I used hash record function, applied to the whole row in a table, 548 00:28:13,260 --> 00:28:14,840 which has 3 columns. 549 00:28:15,700 --> 00:28:19,660 And then I created an index on top of this, that hash record. 550 00:28:19,900 --> 00:28:24,520 So, for the first time, I created an index not specifying columns, but 551 00:28:25,440 --> 00:28:27,340 involving all columns in a table. 552 00:28:27,340 --> 00:28:28,420 This is super strange. 553 00:28:29,040 --> 00:28:32,560 It could be bit-free actually as well because the hash record produces 554 00:28:32,560 --> 00:28:33,740 an int4, right? 555 00:28:34,160 --> 00:28:36,640 So it applies to all columns. 556 00:28:37,740 --> 00:28:42,480 So I said, create an index on T1 using a hash of hash record of 557 00:28:42,480 --> 00:28:42,980 T1. 558 00:28:46,100 --> 00:28:47,100 The whole record. 559 00:28:47,100 --> 00:28:48,220 That's super strange. 560 00:28:48,220 --> 00:28:48,580 I need to think Michael: about it. 561 00:28:48,580 --> 00:28:50,880 What use case are you imagining for this? 562 00:28:52,360 --> 00:28:53,200 Nikolay: I don't know. 563 00:28:53,560 --> 00:28:56,360 I'm just exploring, you know, like, curious. 564 00:28:56,520 --> 00:29:01,360 I don't know, maybe like, again, like to track which records 565 00:29:01,620 --> 00:29:04,240 changed content or something. 566 00:29:04,240 --> 00:29:04,820 I don't know. 567 00:29:04,820 --> 00:29:05,280 I don't know. 568 00:29:05,280 --> 00:29:07,000 It's just interesting that it works. 569 00:29:07,200 --> 00:29:10,400 I thought we were required to specify columns, right? 570 00:29:10,400 --> 00:29:15,220 When we like this column, that column, but here you just specify 571 00:29:15,460 --> 00:29:17,280 the whole record and it works. 572 00:29:17,380 --> 00:29:20,040 This shows the flexibility of Postgres as usual, right? 573 00:29:21,340 --> 00:29:25,940 But I created a multi-column index hash, but it's a hash of hash. 574 00:29:26,800 --> 00:29:28,640 So double hashing here. 575 00:29:28,820 --> 00:29:29,320 Okay. 576 00:29:30,560 --> 00:29:32,540 So I don't know. 577 00:29:32,860 --> 00:29:34,860 I'm still thinking about use cases. 578 00:29:34,860 --> 00:29:39,320 I still only, like, a realistic one is only this, like, really large 579 00:29:39,320 --> 00:29:40,120 text values. 580 00:29:40,760 --> 00:29:42,260 That's it so far for 581 00:29:42,260 --> 00:29:42,740 Michael: me. 582 00:29:42,740 --> 00:29:45,980 Yeah, and when you only need unique lookups of them. 583 00:29:48,040 --> 00:29:51,080 Nikolay: Yeah, so yeah, interesting. 584 00:29:51,500 --> 00:29:53,000 So I will keep it in mind. 585 00:29:53,680 --> 00:29:56,760 Michael: Yeah, I love that we've gone, during this episode we've 586 00:29:56,760 --> 00:29:59,680 gone from I never have use cases for hash indexes. 587 00:29:59,800 --> 00:30:00,480 Yeah, exactly. 588 00:30:00,480 --> 00:30:01,300 There we go. 589 00:30:02,140 --> 00:30:02,540 Cool. 590 00:30:02,540 --> 00:30:04,740 But I also think, you know, you said you've seen them in the 591 00:30:04,740 --> 00:30:05,080 wild. 592 00:30:05,080 --> 00:30:08,680 I think that might be a result of people overthinking it. 593 00:30:08,680 --> 00:30:10,240 And I was there a couple of years ago. 594 00:30:10,240 --> 00:30:12,740 I was thinking, you know, there might be some cases where that 595 00:30:12,740 --> 00:30:17,240 the difference is meaningful and worthwhile, but for the flexibility, 596 00:30:17,460 --> 00:30:18,060 I think. 597 00:30:18,060 --> 00:30:20,460 Anyway, I've said that a few times now. 598 00:30:21,220 --> 00:30:21,720 Cool. 599 00:30:21,900 --> 00:30:25,240 Well, thank you, Nikolay. Thanks for indulging me. 600 00:30:25,240 --> 00:30:28,180 Hopefully a few people found that interesting and useful, and 601 00:30:28,180 --> 00:30:29,840 yeah, catch you next week.