1 00:00:00,060 --> 00:00:01,720 Nikolay: Hello, hello, this is Postgres.FM. 2 00:00:01,720 --> 00:00:06,100 I'm Nikolay from Postgres.AI and as usual, my co-host is Michael 3 00:00:06,100 --> 00:00:07,080 from pgMustard. 4 00:00:07,080 --> 00:00:08,980 Hi Michael, how are you doing? 5 00:00:09,400 --> 00:00:10,320 Michael: Hello, Nikolay. 6 00:00:10,320 --> 00:00:10,900 I am good. 7 00:00:10,900 --> 00:00:11,260 Thank you. 8 00:00:11,260 --> 00:00:12,060 How are you? 9 00:00:12,240 --> 00:00:13,280 Nikolay: I'm very good. 10 00:00:13,320 --> 00:00:17,640 So our topic today, we are going to discuss denormalization. But 11 00:00:17,640 --> 00:00:21,300 it's very like maybe high level and basics and the pros and cons 12 00:00:21,300 --> 00:00:24,640 of having it used, applied as a methodology. 13 00:00:25,240 --> 00:00:27,440 So we already had a couple of episodes. 14 00:00:27,600 --> 00:00:29,440 We discussed materialized views. 15 00:00:29,540 --> 00:00:31,580 It's a very close topic to this one. 16 00:00:32,040 --> 00:00:32,620 What else? 17 00:00:32,620 --> 00:00:34,500 We discussed schema design. 18 00:00:35,020 --> 00:00:36,820 Michael: Yeah, we called it data model trade-offs. 19 00:00:36,820 --> 00:00:38,480 I'll link up those two. 20 00:00:38,480 --> 00:00:40,880 Yeah, I think data model trade-offs was probably the closest 21 00:00:40,940 --> 00:00:45,520 to this because naturally it came up as a big topic. 22 00:00:45,860 --> 00:00:49,860 And then the third one that it came up in was I had a good chat 23 00:00:49,860 --> 00:00:53,540 with Markus Winand and he made some great points around normalization 24 00:00:53,800 --> 00:00:57,720 and denormalization that well, at least normalization I'll 25 00:00:57,720 --> 00:00:58,440 link as well. 26 00:00:58,440 --> 00:01:00,280 Maybe I need to re-listen to that one. 27 00:01:00,280 --> 00:01:04,220 Nikolay: Yeah, it was my choice for this week, and I'm just 28 00:01:04,220 --> 00:01:09,780 thinking that it's important to get back to this topic at some 29 00:01:09,840 --> 00:01:14,620 maybe slightly different angles because I keep seeing issues 30 00:01:14,640 --> 00:01:20,780 with this and cases of this, and I have two different customers 31 00:01:20,860 --> 00:01:24,520 which have consulting contracts with Postgres.AI, and I just 32 00:01:24,520 --> 00:01:28,620 observed the need for denormalization, or at least to consider 33 00:01:28,860 --> 00:01:32,540 applying denormalization to solve some performance issues. 34 00:01:34,260 --> 00:01:39,380 Before we discuss performance and reasons, let me say this. 35 00:01:39,800 --> 00:01:43,840 If you don't understand normalization, don't use denormalization. 36 00:01:45,920 --> 00:01:50,820 You need first to learn normalization, and feel it, and use it. 37 00:01:51,380 --> 00:01:56,320 Because otherwise, with relational databases, it can be tricky 38 00:01:56,320 --> 00:02:00,920 to understand particular cases and limitations and consequences 39 00:02:01,120 --> 00:02:02,280 of applying denormalization. 40 00:02:03,160 --> 00:02:08,180 I'm not talking about non-relational databases, but we can maybe 41 00:02:08,840 --> 00:02:14,980 mention the work by Stonebraker in the EDB blog, discussing 42 00:02:15,140 --> 00:02:19,740 that MongoDB particularly, or maybe DynamoDB there as well, I 43 00:02:19,740 --> 00:02:25,680 don't remember exactly, the problems which happen to MongoDB, 44 00:02:25,680 --> 00:02:30,060 I think, document databases and big JSONs, and the tendency to 45 00:02:30,060 --> 00:02:36,920 have denormalized state design by default normally, and issues 46 00:02:37,360 --> 00:02:40,940 later, both flexibility and performance-wise. 47 00:02:42,180 --> 00:02:43,760 Michael: Was that the blog post with Álvaro? 48 00:02:44,640 --> 00:02:46,320 Nikolay: Yeah, but, yeah, yeah, yeah. 49 00:02:46,320 --> 00:02:51,100 So it also had the word harmful, which I inherited and then got 50 00:02:51,100 --> 00:02:56,760 a big backlash on my sub-transactions related blog post on Hacker 51 00:02:56,760 --> 00:02:57,260 News. 52 00:02:57,700 --> 00:03:01,620 Yeah, so let's keep that aside and consider we talk about only 53 00:03:01,620 --> 00:03:05,040 Postgres and maybe slightly wider relational databases. 54 00:03:05,240 --> 00:03:08,580 First thing you need to understand is normalization, normal forms, 55 00:03:09,140 --> 00:03:12,540 at least 3, 4, Boyce-Codd, right? 56 00:03:12,800 --> 00:03:14,200 Normal form of M. 57 00:03:14,560 --> 00:03:18,200 Functional dependencies, it's very interesting thing to understand. 58 00:03:18,820 --> 00:03:23,460 It's not only theory because it's like it's it feels theory and 59 00:03:23,860 --> 00:03:28,020 always felt like theory to me but when you design your schema 60 00:03:28,040 --> 00:03:32,280 if you understand these things you learned it studied them you 61 00:03:32,640 --> 00:03:36,980 feel much better thinking like how to design schema tables and 62 00:03:36,980 --> 00:03:39,680 columns and foreign keys between them right? 63 00:03:40,440 --> 00:03:44,200 Michael: Yeah but and in the in the world at least in the last 64 00:03:44,440 --> 00:03:50,780 3 to 5 years I think the vast majority of systems I've come across 65 00:03:51,220 --> 00:03:56,780 have had pretty good like understandings of normalization and 66 00:03:56,820 --> 00:03:59,800 I've got pretty good hygiene in that area from the things I've 67 00:03:59,800 --> 00:04:00,200 come across. 68 00:04:00,200 --> 00:04:03,980 I think it's 1 of those database things that back-end engineers, 69 00:04:04,060 --> 00:04:06,600 full-stack engineers, generally do look up on. 70 00:04:06,600 --> 00:04:10,460 They do know it's important and they do some thinking and up-front 71 00:04:10,560 --> 00:04:12,720 design work, get second opinions and things. 72 00:04:12,720 --> 00:04:15,040 So I actually haven't come up... 73 00:04:15,040 --> 00:04:19,940 I have come across some cases of, you know, like, is it EAV, 74 00:04:20,540 --> 00:04:24,840 entity attribute value, like nonsense or like just things that 75 00:04:24,840 --> 00:04:28,200 haven't been fully thought through, but they've been really rare 76 00:04:28,200 --> 00:04:30,860 compared to people that have done things pretty well from the 77 00:04:30,860 --> 00:04:31,360 start. 78 00:04:31,560 --> 00:04:34,020 Nikolay: EAV is radical normalization, right? 79 00:04:34,020 --> 00:04:36,840 Which leads to bad performance, usually. 80 00:04:37,580 --> 00:04:38,980 Yeah, that's a good point. 81 00:04:39,060 --> 00:04:43,880 But I think many back-end engineers look at this topic, normalization 82 00:04:43,940 --> 00:04:50,200 versus standardization in general, using lens of ORM, right? 83 00:04:50,980 --> 00:04:53,480 They have different terminology, right? 84 00:04:53,580 --> 00:04:56,340 Sometimes hard to understand each other talking. 85 00:04:57,700 --> 00:05:04,120 Also, I think today Most powerful LLMs, they do a great job designing, 86 00:05:04,120 --> 00:05:05,060 suggesting design. 87 00:05:05,060 --> 00:05:09,360 For example, we integrated and it produces mermaid diagrams. 88 00:05:09,600 --> 00:05:13,120 So if you ask to design, I don't know, like Twitter-like or Facebook-like, 89 00:05:13,260 --> 00:05:18,340 YouTube-like schema or e-commerce, and then you iterate adding 90 00:05:18,340 --> 00:05:23,800 tables, it generally tries to be like, to apply common sense 91 00:05:23,940 --> 00:05:25,880 in terms of normalization as well 92 00:05:26,500 --> 00:05:27,460 Michael: That makes sense. 93 00:05:27,640 --> 00:05:30,900 Nikolay: But again before starting to use denormalization. 94 00:05:31,040 --> 00:05:34,580 It's good to understand normalization first in several forms. 95 00:05:35,800 --> 00:05:41,900 Then first thing when you Let's say sometimes it's convenient 96 00:05:42,440 --> 00:05:43,520 to have a denormalization. 97 00:05:44,340 --> 00:05:47,680 I maybe cannot think about a particular case right now, but it 98 00:05:47,680 --> 00:05:47,960 happens. 99 00:05:47,960 --> 00:05:48,900 I know it happens. 100 00:05:48,900 --> 00:05:53,080 Sometimes you just think there is functional dependency here, 101 00:05:53,620 --> 00:05:57,340 but I don't go and normalize and move this data to a separate 102 00:05:57,340 --> 00:06:00,360 table, for example, because I know, for example, this functional 103 00:06:00,360 --> 00:06:03,060 dependency might vanish in the future. 104 00:06:03,580 --> 00:06:04,820 You know, it might change. 105 00:06:04,820 --> 00:06:10,600 I just know my field, and this functional dependency is only 106 00:06:10,600 --> 00:06:12,660 for now, it feels so, but it might change. 107 00:06:12,660 --> 00:06:18,400 In this case, it might be beneficial and providing more flexibility 108 00:06:18,760 --> 00:06:23,700 if you don't apply next step of normalization in this situation. 109 00:06:24,220 --> 00:06:28,120 Yeah, it sounds abstract and yeah, I don't, I cannot imagine 110 00:06:28,140 --> 00:06:28,640 some. 111 00:06:28,840 --> 00:06:32,220 Michael: I think of 1 example, like imagine if you're an early 112 00:06:32,220 --> 00:06:36,380 stage startup without product market fit and you're adding features 113 00:06:36,380 --> 00:06:40,140 quite quickly and make like a simple example would be You're 114 00:06:40,140 --> 00:06:43,900 a blogging platform and you think the idea of adding tags would 115 00:06:43,900 --> 00:06:44,860 be a good idea. 116 00:06:44,860 --> 00:06:49,280 So you instead of adding a whole extra schema around that you 117 00:06:49,280 --> 00:06:53,040 could have a single column where you let people add a few tags 118 00:06:53,080 --> 00:06:56,860 and you know that might not be how you want it to be eventually 119 00:06:57,400 --> 00:07:00,080 but you also might remove that feature in a couple of weeks if 120 00:07:00,080 --> 00:07:04,520 people hate it or something So like you might for convenience 121 00:07:04,780 --> 00:07:05,380 go with 1. 122 00:07:05,380 --> 00:07:06,660 Well you don't like that. 123 00:07:06,900 --> 00:07:10,740 Nikolay: Well this particular case I remember my talk in Maryland 124 00:07:10,760 --> 00:07:15,400 2008 the first time I came to the US to speak how good Postgres 125 00:07:15,400 --> 00:07:16,660 is for web 2.0. 126 00:07:18,400 --> 00:07:20,040 It's like 16 years, right? 127 00:07:21,600 --> 00:07:24,660 And this was my example, tags. 128 00:07:25,760 --> 00:07:27,780 And I talked about hstore. 129 00:07:28,680 --> 00:07:30,480 Or today we can talk about Array. 130 00:07:32,220 --> 00:07:36,140 Back to that time, there was no GIN at all. 131 00:07:36,960 --> 00:07:38,720 GIN was not created yet. 132 00:07:39,780 --> 00:07:45,060 We used only GiST and R-tree or L3 in some cases, it depends. 133 00:07:45,060 --> 00:07:49,020 But in the case of tags, it's natural to think, okay, I'm going 134 00:07:49,020 --> 00:07:54,960 to put these 2 different tables, and then you need additional 135 00:07:54,960 --> 00:07:58,040 table to have many-to-many relationship. 136 00:07:58,780 --> 00:08:00,960 And this is AE-AV, exactly. 137 00:08:01,860 --> 00:08:05,060 This is what is going to have bad performance if you have a lot 138 00:08:05,060 --> 00:08:09,140 of entries in the main table of like your objects and also a 139 00:08:09,140 --> 00:08:12,880 lot of tags and a lot of relationships between them. 140 00:08:12,880 --> 00:08:13,760 It's really... 141 00:08:15,060 --> 00:08:17,860 This join can be problematic sometimes. 142 00:08:18,680 --> 00:08:23,140 But we will probably return to this schema, like 3-table schema. 143 00:08:23,760 --> 00:08:28,820 Later what I wanted to just emphasize, I cannot just not to comment 144 00:08:28,820 --> 00:08:30,040 here, it's about performance. 145 00:08:30,100 --> 00:08:35,980 I would these days consider arrays with tags or maybe arrays 146 00:08:35,980 --> 00:08:36,780 with IDs. 147 00:08:37,340 --> 00:08:43,100 We know foreign keys don't support this, like 1 row and you have 148 00:08:43,100 --> 00:08:48,000 tags, tag IDs, right, and then like multiple foreign keys to 149 00:08:48,000 --> 00:08:52,260 different entries in the text table, but we can live without 150 00:08:52,280 --> 00:08:53,500 the foreign key here. 151 00:08:53,760 --> 00:08:57,440 For the sake of having good performance, we can just have these 152 00:08:57,440 --> 00:09:03,480 IDs and or just text as is like text, text array, right? 153 00:09:03,820 --> 00:09:07,520 So integer 8 array, big int array or text array. 154 00:09:07,660 --> 00:09:10,360 In this case, using GIN should be good. 155 00:09:10,360 --> 00:09:11,640 Michael: Or just JSONB. 156 00:09:13,520 --> 00:09:19,500 Nikolay: JSONB, I feel would add like additional layer of something 157 00:09:19,500 --> 00:09:23,600 here which we don't really need because we just need ideas or 158 00:09:23,960 --> 00:09:26,880 bunch of phrases, texts, like text, right? 159 00:09:26,880 --> 00:09:28,020 So I don't know. 160 00:09:28,520 --> 00:09:29,940 Do you think this is denormalization? 161 00:09:31,020 --> 00:09:32,760 Michael: I think strictly speaking, yeah. 162 00:09:33,400 --> 00:09:37,940 Nikolay: Well, let's not say we are breaking first normal form 163 00:09:38,000 --> 00:09:43,580 here Because of course first normal form is like say it's saying 164 00:09:43,580 --> 00:09:48,360 it's just saying that in each cell basically of a table In each 165 00:09:48,700 --> 00:09:50,220 Each value should be atomic, right? 166 00:09:50,220 --> 00:09:52,440 It should not be a table, for example. 167 00:09:52,440 --> 00:09:54,480 Or in this case, array or JSON. 168 00:09:54,640 --> 00:09:58,800 We are breaking it, strictly speaking. 169 00:09:58,860 --> 00:10:01,500 But in this sense, if we consider... 170 00:10:02,680 --> 00:10:06,720 In this case, every time we use array, we are already breaking 171 00:10:06,720 --> 00:10:07,580 normal form. 172 00:10:07,660 --> 00:10:09,780 But in what sense it's denormalization. 173 00:10:12,260 --> 00:10:17,660 If we are repeating values, For example, later we found a typo 174 00:10:17,660 --> 00:10:20,420 in some tag and going to adjust it. 175 00:10:20,420 --> 00:10:23,680 This is functional dependency, of course, and we will need to 176 00:10:23,680 --> 00:10:25,300 update a lot of rows here. 177 00:10:25,340 --> 00:10:31,000 Of course, it's better to store IDs and have tag values in a 178 00:10:31,000 --> 00:10:31,820 separate table. 179 00:10:32,980 --> 00:10:39,960 So in this case, we can even join in an interesting way, maybe 180 00:10:39,960 --> 00:10:42,180 with lateral join or unnest here. 181 00:10:42,180 --> 00:10:44,440 It's interesting, but it's a separate topic. 182 00:10:45,060 --> 00:10:50,480 So if we don't store values of text, I don't see denormalization 183 00:10:50,680 --> 00:10:51,600 aspect here. 184 00:10:52,580 --> 00:10:59,160 It's just, yeah, we treat the first normal form in interesting 185 00:10:59,180 --> 00:10:59,680 way. 186 00:11:00,040 --> 00:11:02,060 This discussion happened 20 years ago, I think. 187 00:11:02,720 --> 00:11:06,040 I remember some from Chris Date, right? 188 00:11:06,280 --> 00:11:12,840 Some works like, are we breaking the first normal form in such 189 00:11:12,840 --> 00:11:13,880 cases or no? 190 00:11:13,940 --> 00:11:18,240 But if we store just IDs, well, it's quite well normalized and 191 00:11:18,240 --> 00:11:24,220 we don't have a AV so we can use GIN search much faster. 192 00:11:24,380 --> 00:11:29,340 If we need some tags, we like this is this query is going to 193 00:11:29,340 --> 00:11:31,900 be much more efficient, in my opinion. 194 00:11:32,440 --> 00:11:33,980 But we lack foreign key, unfortunately. 195 00:11:34,040 --> 00:11:36,400 This is what we lose in such approach. 196 00:11:38,800 --> 00:11:41,140 So, flexibility is 1 thing. 197 00:11:41,320 --> 00:11:44,160 Different thing, and this is most interesting of course, performance. 198 00:11:44,160 --> 00:11:45,820 We already started discussing performance. 199 00:11:46,560 --> 00:11:49,020 I would highlight 2 particular cases. 200 00:11:49,120 --> 00:11:53,160 This is exactly 2 particular customers I observed over the last 201 00:11:53,160 --> 00:11:54,980 couple of weeks, 2 particular situations. 202 00:11:55,460 --> 00:11:57,680 Let's start from maybe a simpler 1. 203 00:11:57,740 --> 00:11:59,720 It's slow count, right? 204 00:11:59,720 --> 00:12:01,300 Did we have an episode about it? 205 00:12:01,300 --> 00:12:02,000 I'm sure we 206 00:12:02,000 --> 00:12:02,720 Michael: did, yeah? 207 00:12:04,540 --> 00:12:09,740 Nikolay: So slow count or in broader view, slow aggregates. 208 00:12:11,400 --> 00:12:15,510 So there are approaches to solve it using like index-only scan. 209 00:12:15,510 --> 00:12:16,700 We also had an episode. 210 00:12:16,700 --> 00:12:19,340 I think we are covering more and more things, which is great. 211 00:12:20,200 --> 00:12:25,620 But if we see that it's not enough, what else? 212 00:12:26,000 --> 00:12:29,400 We need to start denormalizing in some form. 213 00:12:29,720 --> 00:12:34,700 And obviously, we could use materialized view, which has all the 214 00:12:35,020 --> 00:12:36,960 counts, but it's very asynchronous. 215 00:12:37,780 --> 00:12:38,100 Michael: And the... 216 00:12:38,100 --> 00:12:38,860 Like pre-calculated. 217 00:12:39,640 --> 00:12:40,420 Nikolay: Yeah, pre-calculated. 218 00:12:40,680 --> 00:12:44,020 You can do it, you cannot do it often. 219 00:12:44,160 --> 00:12:48,000 You cannot, For example, we have a huge table, like 10 billion 220 00:12:48,000 --> 00:12:50,780 rows, for example, and we insert 1 more row. 221 00:12:50,840 --> 00:12:55,820 And we say, okay, our materialized view is just create materialized 222 00:12:55,860 --> 00:13:00,540 view, blah, blah, blah, name as select count star from the table. 223 00:13:01,080 --> 00:13:02,140 Interesting, right? 224 00:13:02,380 --> 00:13:04,900 Is it a good idea to do it often? 225 00:13:04,960 --> 00:13:06,260 Well, maybe no. 226 00:13:06,440 --> 00:13:09,220 We should do it not often. 227 00:13:09,520 --> 00:13:14,740 In this case, it will be lagging and showing not relevant information, 228 00:13:14,820 --> 00:13:16,420 like kind of eventual consistency. 229 00:13:16,560 --> 00:13:21,040 Yes, we will reflect next insert unless it's got it's got deleted 230 00:13:21,040 --> 00:13:26,000 right but it will be lagging and some people can notice it not 231 00:13:26,000 --> 00:13:26,500 good. 232 00:13:26,980 --> 00:13:34,620 So what else like we can consider synchronous and also like when 233 00:13:34,620 --> 00:13:37,780 you refresh materialized view, it's kind of everything like it could 234 00:13:37,780 --> 00:13:42,660 skip some old data understanding it hasn't changed, and just 235 00:13:42,660 --> 00:13:46,080 count the only fresh part of the table. 236 00:13:46,080 --> 00:13:48,740 Maybe we have partitioning there, I don't know. 237 00:13:50,020 --> 00:13:53,740 But Postgres doesn't support only additional extensions like pg_ivm, 238 00:13:54,960 --> 00:13:58,940 or what was the name of different extension, I forgot, I just 239 00:13:59,140 --> 00:14:00,040 found it recently. 240 00:14:00,040 --> 00:14:01,620 Michael: The 1 you just mentioned, was it denorm? 241 00:14:02,120 --> 00:14:02,860 Nikolay: Yeah, denorm. 242 00:14:02,880 --> 00:14:03,660 It was interesting. 243 00:14:03,900 --> 00:14:09,400 I think it's Python and external to Postgres, because pg_ivm 244 00:14:09,400 --> 00:14:14,540 is an extension, so obviously you don't have it on many managed 245 00:14:14,860 --> 00:14:15,740 Postgres offerings. 246 00:14:16,340 --> 00:14:19,600 But if it's external, or for example, you implement it yourself, 247 00:14:19,600 --> 00:14:24,140 it's quite possible, using maybe triggers or something, maybe 248 00:14:24,140 --> 00:14:27,680 some queue mechanism involved externally to make it asynchronous 249 00:14:28,260 --> 00:14:30,560 and incrementally updated. 250 00:14:30,920 --> 00:14:35,260 In this case, it can be good and maybe more resilient, like updating 251 00:14:35,320 --> 00:14:39,980 more faster and having more actual data. 252 00:14:42,540 --> 00:14:47,000 The problem is, like with modularity, the problem is it's heavy. 253 00:14:47,000 --> 00:14:48,900 It's a very rough and heavy tool. 254 00:14:49,240 --> 00:14:50,860 In most cases I deal with them. 255 00:14:50,860 --> 00:14:53,580 I say let's try to avoid them. 256 00:14:54,060 --> 00:14:55,360 Get rid of them, right? 257 00:14:55,580 --> 00:14:57,280 In this case particularly as well. 258 00:14:57,720 --> 00:15:01,900 By the way, we could build also our own like kind of materialization 259 00:15:02,640 --> 00:15:05,320 mechanism using logical decoding, right? 260 00:15:05,340 --> 00:15:06,800 Logical replication even. 261 00:15:07,500 --> 00:15:12,720 For example, accumulating some events through, it can be also 262 00:15:12,720 --> 00:15:16,140 external queue mechanism, but also using logical replication, 263 00:15:16,320 --> 00:15:22,740 if you batch and then update, not on each insert, but after 100 264 00:15:22,740 --> 00:15:25,220 inserts or updates and so on, deletes also. 265 00:15:25,400 --> 00:15:29,080 And then you reflect this change, and nothing is lost because 266 00:15:29,380 --> 00:15:33,280 it's quite Postgres-centric approach, right? 267 00:15:33,280 --> 00:15:36,000 Because Postgres guarantees nothing is lost. 268 00:15:36,300 --> 00:15:37,180 What do you think? 269 00:15:37,620 --> 00:15:39,020 Michael: Well, I like it. 270 00:15:39,020 --> 00:15:43,480 I remember reading a good blog post by Timescale on how they 271 00:15:43,740 --> 00:15:47,780 how continuous aggregates work and I remember thinking I would 272 00:15:47,780 --> 00:15:51,040 I wouldn't want to implement that myself like I remember thinking 273 00:15:51,040 --> 00:15:55,960 it like keeping it synchronous or like synchronous enough like 274 00:15:55,960 --> 00:15:59,540 it's just quite painful so I do admire people that have done 275 00:15:59,540 --> 00:16:03,460 that and yeah I can see the argument for it, but I also I think 276 00:16:03,460 --> 00:16:07,540 at this point if once you're considering that you probably also 277 00:16:07,540 --> 00:16:10,460 should be considering the synchronous approaches like triggers 278 00:16:10,760 --> 00:16:14,440 or some other way of keeping things just actually in sync. 279 00:16:15,020 --> 00:16:18,520 Nikolay: Yeah, well, I agree and I like this. 280 00:16:18,520 --> 00:16:23,940 I have a sense of like lack of some additional materiality mechanism 281 00:16:24,240 --> 00:16:29,700 in Postgres which is not yet developed which could support some 282 00:16:29,700 --> 00:16:33,460 kind of asynchronous way to update things. 283 00:16:34,460 --> 00:16:37,280 Michael: Oh I'd love it if it was in core that would be amazing. 284 00:16:37,440 --> 00:16:40,960 Nikolay: Yeah in core exactly this would be great but it's maybe 285 00:16:40,960 --> 00:16:43,180 a hard task to have. 286 00:16:43,180 --> 00:16:45,480 Michael: And also not that high up the priority list for most 287 00:16:45,480 --> 00:16:46,360 people I think. 288 00:16:46,360 --> 00:16:50,820 Nikolay: Yeah but it would give a lot of interesting things interesting 289 00:16:51,780 --> 00:16:56,540 capabilities to develop interesting well-performant and responsive 290 00:16:56,600 --> 00:16:59,960 I mean not responsive but data is coming and you reflect it like 291 00:16:59,960 --> 00:17:01,860 within a few seconds it's great. 292 00:17:03,720 --> 00:17:04,420 By the way 293 00:17:05,020 --> 00:17:08,000 Michael: when you mentioned performance so I looked up the definition 294 00:17:08,000 --> 00:17:11,620 of denormalization on Wikipedia, and it said denormalization 295 00:17:11,840 --> 00:17:16,100 is a strategy on a previously normalized database to increase 296 00:17:16,160 --> 00:17:16,660 performance. 297 00:17:17,380 --> 00:17:19,900 So they're explicitly saying that... 298 00:17:20,900 --> 00:17:25,860 Nikolay: Right, but we can assume that in our head sometimes 299 00:17:25,900 --> 00:17:31,240 we normalize and then we move back and denormalize and then go 300 00:17:31,240 --> 00:17:33,860 like deploy it right away in production. 301 00:17:33,920 --> 00:17:34,400 This would... 302 00:17:34,400 --> 00:17:36,580 Michael: Oh yeah, I thought, obviously there is the... 303 00:17:36,580 --> 00:17:39,020 There's an interesting part there that says it has to be previously 304 00:17:39,020 --> 00:17:42,520 normalized, but I thought it was also interesting that the only 305 00:17:42,520 --> 00:17:45,340 reason they gave was for performance reasons. 306 00:17:46,020 --> 00:17:49,840 Nikolay: Well, maybe I'm wrong thinking it can be not only for 307 00:17:49,840 --> 00:17:52,800 performance reasons Of course in my in my experience I did for 308 00:17:52,800 --> 00:17:53,420 performance reasons. 309 00:17:53,420 --> 00:17:54,880 Let's be honest. 310 00:17:55,360 --> 00:17:59,120 Let's be honest here here I just wanted to cover maybe cases 311 00:17:59,180 --> 00:18:00,260 which can happen 312 00:18:00,720 --> 00:18:04,160 Michael: You mentioned a couple of cases that come up recently. 313 00:18:04,960 --> 00:18:09,320 Nikolay: Yeah, well, before we move to the next 1, I wanted to 314 00:18:09,320 --> 00:18:13,860 emphasize, if we think about, okay, we are not going to use materialized 315 00:18:13,860 --> 00:18:14,840 view, what else? 316 00:18:15,340 --> 00:18:19,160 We have a table, we insert, but we need the count very fast imagine 317 00:18:19,160 --> 00:18:23,560 We have a trigger and we update that count on each insert Or 318 00:18:23,560 --> 00:18:25,080 update and or delete the 319 00:18:25,080 --> 00:18:26,320 Michael: update and delete. 320 00:18:26,320 --> 00:18:29,980 Nikolay: Yeah delete Update maybe no big depending of course 321 00:18:29,980 --> 00:18:35,960 if you have soft delete approach then you need To reflect updates 322 00:18:35,980 --> 00:18:38,820 because they might be doing soft deletion, right? 323 00:18:39,060 --> 00:18:43,440 Michael: Well, and if it's only a count, yeah, sure, but if it's 324 00:18:43,440 --> 00:18:46,280 a different kind of Aggregate function, then you might have to worry about 325 00:18:46,280 --> 00:18:47,880 Nikolay: averages, sum and so on. 326 00:18:47,880 --> 00:18:53,860 Yeah, min-max probably is fast enough, thanks to index anyway. 327 00:18:54,280 --> 00:18:58,400 Yeah, by the way, of course, this also, there is ongoing discussion, 328 00:18:58,500 --> 00:19:01,560 there are ongoing discussions about having Column store and Postgres 329 00:19:01,560 --> 00:19:05,240 all the time, which might be good here, but maybe not. 330 00:19:05,240 --> 00:19:08,460 It depends on the particular case, because if your main part 331 00:19:08,460 --> 00:19:13,880 of data needs to be Row store, you still need to deal with some 332 00:19:14,380 --> 00:19:17,840 replication inside your engine or outside of your engine. 333 00:19:17,840 --> 00:19:21,280 So replication means like maybe it's triggers, maybe it's logical 334 00:19:21,280 --> 00:19:22,660 Replication involved, who knows. 335 00:19:22,660 --> 00:19:25,680 But yeah, it can be interesting. 336 00:19:25,840 --> 00:19:29,580 But again, we have a main table and trigger which on insert, 337 00:19:29,580 --> 00:19:34,600 on each insert, it increments this counter on this additional 338 00:19:34,600 --> 00:19:37,060 table, what do you think will happen? 339 00:19:38,300 --> 00:19:38,960 Is it good? 340 00:19:38,960 --> 00:19:40,680 Michael: Like hot spots, we've talked, yeah. 341 00:19:40,680 --> 00:19:41,400 Nikolay: Hot spots. 342 00:19:41,540 --> 00:19:42,040 Michael: Yeah. 343 00:19:42,660 --> 00:19:43,780 At sufficient scale. 344 00:19:43,940 --> 00:19:46,960 And I guess all of this is only relevant at sufficient scale, 345 00:19:46,960 --> 00:19:47,700 isn't it? 346 00:19:47,720 --> 00:19:50,720 Nikolay: Yeah, so this synchronous approach can be super dangerous 347 00:19:50,740 --> 00:19:56,520 because locking, heavy locking, and even without heavy locks, 348 00:19:56,520 --> 00:20:01,400 if we have Foreign keys, for example, there, and Yeah, it's going 349 00:20:01,400 --> 00:20:05,040 to have multixact ID involved and even SELECTs can downgrade 350 00:20:05,820 --> 00:20:06,060 So I 351 00:20:06,060 --> 00:20:09,040 Michael: think you can spread out you can spread out the hotspot 352 00:20:09,060 --> 00:20:13,520 like You don't have to have a single record that gets updated. 353 00:20:13,540 --> 00:20:17,140 You could have a series and that that series could grow as your 354 00:20:17,660 --> 00:20:18,840 concurrency grows. 355 00:20:19,440 --> 00:20:20,140 Nikolay: Right, right. 356 00:20:20,140 --> 00:20:23,260 That's like, yeah, like, butches in place, right? 357 00:20:23,260 --> 00:20:27,620 And you, then you just sum all of it and this is your total count, 358 00:20:27,620 --> 00:20:27,900 right? 359 00:20:27,900 --> 00:20:28,820 That's, that's good. 360 00:20:28,820 --> 00:20:28,980 Yeah. 361 00:20:28,980 --> 00:20:29,340 Yeah. 362 00:20:29,340 --> 00:20:31,080 This is actually what we did a few times. 363 00:20:31,080 --> 00:20:32,380 We just had batches. 364 00:20:34,060 --> 00:20:35,900 I don't remember particularly how. 365 00:20:35,900 --> 00:20:40,200 Basically, some kind of partitioning of your account inside 1 366 00:20:40,200 --> 00:20:41,020 table, right? 367 00:20:41,540 --> 00:20:46,200 You partition those counts into buckets or batches, how to say. 368 00:20:46,840 --> 00:20:50,340 Then you increment them sometimes collapsing maybe and so on 369 00:20:50,340 --> 00:20:52,380 Yeah, also vacuum is an issue here. 370 00:20:52,380 --> 00:20:56,660 If you update that table very often and it can 1 row can can 371 00:20:56,660 --> 00:21:00,800 be super heavy in terms of real Disk space because of that tuples, 372 00:21:00,800 --> 00:21:01,300 right? 373 00:21:01,740 --> 00:21:06,140 Yeah, So Postgres MVCC is not good in this particular case. 374 00:21:06,540 --> 00:21:09,140 Michael: Well, I think you'd ideally be aiming for those to be 375 00:21:09,140 --> 00:21:09,640 hot. 376 00:21:09,760 --> 00:21:12,740 There's no reason, I think, to index that column. 377 00:21:12,740 --> 00:21:16,160 So I think you'd be aiming for those to be hot updates. 378 00:21:17,160 --> 00:21:17,860 Nikolay: Good point. 379 00:21:18,380 --> 00:21:19,640 Yeah, good point. 380 00:21:19,640 --> 00:21:22,400 Michael: And then hopefully avoid vacuum issues at all. 381 00:21:23,420 --> 00:21:23,480 Nikolay: It's great. 382 00:21:23,480 --> 00:21:24,440 Like index less stable. 383 00:21:24,480 --> 00:21:29,280 Yeah, actually, this is maybe the case when primary key is definitely 384 00:21:29,540 --> 00:21:31,240 against our goals. 385 00:21:31,460 --> 00:21:32,720 Good performance against 386 00:21:33,340 --> 00:21:34,580 Michael: the primary key anyway. 387 00:21:34,740 --> 00:21:35,680 But yeah, I understand. 388 00:21:35,680 --> 00:21:38,860 Nikolay: Yeah, I mean, this is a concept from theory as well. 389 00:21:39,100 --> 00:21:42,840 At the same time, when we learn relational theory, normalization, 390 00:21:43,020 --> 00:21:46,860 we also learn that tables without primary keys, basically, it's 391 00:21:46,860 --> 00:21:49,640 also breaking rules, right? 392 00:21:50,280 --> 00:21:53,260 So in this particular case, we don't want primary key. 393 00:21:53,260 --> 00:21:53,920 It's interesting. 394 00:21:55,060 --> 00:21:57,260 Michael: Isn't there like a saying, something like, first you 395 00:21:57,260 --> 00:22:00,320 have to learn all the rules so then you know how like how and 396 00:22:00,320 --> 00:22:02,060 when it's safe to break them. 397 00:22:02,440 --> 00:22:03,600 Nikolay: Yeah, yeah. 398 00:22:03,600 --> 00:22:07,540 Well, the whole demonization idea, you need to learn how to do 399 00:22:07,540 --> 00:22:09,560 it right and then how to break it right. 400 00:22:10,080 --> 00:22:11,260 To have good performance. 401 00:22:11,680 --> 00:22:16,680 So let's, we don't have a lot of time, so let's discuss these 402 00:22:16,880 --> 00:22:18,800 most interesting maybe case. 403 00:22:19,880 --> 00:22:22,940 Back to AV maybe, but not particularly. 404 00:22:23,860 --> 00:22:26,860 In my projects, I did it several times. 405 00:22:27,440 --> 00:22:29,940 I imagine you have social media, for example. 406 00:22:30,220 --> 00:22:33,480 You have a lot of users, say a few million users, and you have 407 00:22:33,480 --> 00:22:36,780 a lot of objects, say dozens of millions of objects. 408 00:22:36,780 --> 00:22:39,440 It can be posts or comments, anything. 409 00:22:40,040 --> 00:22:42,100 Maybe it's not only like posts. 410 00:22:42,100 --> 00:22:46,780 There is also something, some concept, like kind of blog organization 411 00:22:47,120 --> 00:22:51,000 or some data source where posts exist. 412 00:22:52,120 --> 00:22:55,240 And people can relate to those channels, right? 413 00:22:55,240 --> 00:22:57,040 Channels, let's say channels. 414 00:22:57,900 --> 00:23:01,980 And for example, they can subscribe Or they can have permissions 415 00:23:02,100 --> 00:23:07,400 to view them or not to view them as different things So and then 416 00:23:07,400 --> 00:23:14,020 you just need to display last the most the freshest 100 entries 417 00:23:14,380 --> 00:23:15,980 posts or something right 418 00:23:16,220 --> 00:23:17,360 Michael: relevant to them 419 00:23:17,360 --> 00:23:19,840 Nikolay: relevant to this person yeah only from subscriptions 420 00:23:21,100 --> 00:23:27,020 yeah or only only that data which is allowed to view to access 421 00:23:27,100 --> 00:23:34,520 to edit or even even worse if you want to show hundred last updated 422 00:23:34,800 --> 00:23:40,380 or last accessed last changed different kinds of timestamps can 423 00:23:40,380 --> 00:23:41,180 happen here. 424 00:23:41,200 --> 00:23:44,500 And it's interesting because some timestamps belong to the objects 425 00:23:44,500 --> 00:23:47,420 itself, for posts, for example, creation time or modification 426 00:23:47,560 --> 00:23:48,060 time. 427 00:23:48,280 --> 00:23:51,920 But some of timestamps can belong to this particular relationship 428 00:23:52,020 --> 00:23:55,160 between users and those channels or posts themselves. 429 00:23:55,280 --> 00:23:56,780 For example, last accessed. 430 00:23:58,040 --> 00:23:58,520 Right. 431 00:23:58,520 --> 00:24:00,260 It's definitely for each person. 432 00:24:00,300 --> 00:24:04,200 It's different in respect to particular post. 433 00:24:05,660 --> 00:24:10,120 1 user accessed it 1 day, another user, it's the same day but 434 00:24:10,120 --> 00:24:12,220 different time, so timestamp is different. 435 00:24:12,740 --> 00:24:15,340 So this particular task is very common. 436 00:24:15,340 --> 00:24:16,500 This is common pattern. 437 00:24:17,380 --> 00:24:21,180 Not pattern, Pattern maybe should be applied to solutions, right? 438 00:24:21,260 --> 00:24:24,220 But usually people just do SELECTs, joins, and that's it. 439 00:24:24,920 --> 00:24:27,460 And at some point, performance become terrible. 440 00:24:28,340 --> 00:24:34,020 It happened first in 1 of my social medias, and I was super upset. 441 00:24:34,940 --> 00:24:39,160 I thought it's really similar to graph issues Like graph working 442 00:24:39,160 --> 00:24:43,980 with graphs like for example return First circle of connections 443 00:24:43,980 --> 00:24:46,800 second circle of connections and try to find some connections 444 00:24:46,800 --> 00:24:48,060 like in LinkedIn, right? 445 00:24:48,180 --> 00:24:52,080 I remember many, many years ago, we were trying to solve it in 446 00:24:52,080 --> 00:24:52,960 relational database. 447 00:24:52,960 --> 00:24:55,020 It's quite difficult. 448 00:24:55,120 --> 00:24:58,940 We didn't have a recursive CTE at the time and lateral joins 449 00:24:58,940 --> 00:24:59,740 and so on. 450 00:24:59,760 --> 00:25:01,820 So It's a hard problem to solve. 451 00:25:03,480 --> 00:25:08,860 Query has several joins and filtering, order by, but some filters 452 00:25:09,440 --> 00:25:12,240 columns exist in 1 column, in 1 table. 453 00:25:13,260 --> 00:25:15,320 Different columns exist in different tables. 454 00:25:15,660 --> 00:25:19,860 When filters and order by are in different columns, in different 455 00:25:19,860 --> 00:25:23,720 tables, it means you cannot have ideal situation, a single index 456 00:25:23,720 --> 00:25:26,680 scan or even index only scan, which is very good. 457 00:25:27,440 --> 00:25:33,360 Then you need to rely on 1 of 3 join algorithms, Postgres implements. 458 00:25:34,700 --> 00:25:38,900 I can assure you there will be edge cases where statistics and 459 00:25:38,900 --> 00:25:42,580 planner, they don't have idea how to execute this. 460 00:25:42,980 --> 00:25:43,260 So 461 00:25:43,260 --> 00:25:44,340 Michael: this is 462 00:25:44,340 --> 00:25:45,040 Nikolay: bad performance. 463 00:25:46,060 --> 00:25:48,360 Michael: The age-old issue of which is more selective. 464 00:25:49,640 --> 00:25:53,040 Is the planner going to be better off going through an index 465 00:25:53,080 --> 00:25:58,080 on the order by until it finds enough posts, or whatever the 466 00:25:58,260 --> 00:26:00,840 example is, to satisfy the limit? 467 00:26:01,080 --> 00:26:05,100 Or is it going to be cheaper to look at all posts that satisfy 468 00:26:05,160 --> 00:26:09,400 the other conditions and then order those assuming they're a 469 00:26:09,400 --> 00:26:13,940 smaller set and unless the statistics are good then you could 470 00:26:13,940 --> 00:26:17,440 end up doing the wrong 1, you know, the more expensive 1, and 471 00:26:17,440 --> 00:26:20,660 have a very slow query if it's a huge index. 472 00:26:20,660 --> 00:26:21,100 Nikolay: Exactly. 473 00:26:21,100 --> 00:26:26,340 Imagine we have, we order by creation time, and we just subscribe 474 00:26:27,340 --> 00:26:30,700 to channels, channels have posts, so we have users table, we 475 00:26:30,700 --> 00:26:33,900 join with channels, we have subscriptions and posts, and then 476 00:26:34,200 --> 00:26:38,940 we order by creation time of posts and limit 100, right? 477 00:26:38,940 --> 00:26:42,100 Order by desc, order by creation at desc, limit 100. 478 00:26:42,160 --> 00:26:46,360 So in this case, indeed, as you say, Postgres need to choose 479 00:26:46,360 --> 00:26:47,220 what to do. 480 00:26:47,220 --> 00:26:50,680 Is it a good idea to extract all subscriptions and then in memory 481 00:26:50,680 --> 00:26:53,300 order by and find 100? 482 00:26:53,620 --> 00:26:58,980 Or it's better to use an index on created ad, go backwards on 483 00:26:58,980 --> 00:27:03,220 this index, and try to find posts which can be displayed for 484 00:27:03,220 --> 00:27:06,100 this particular user, meaning that they are among subscriptions. 485 00:27:07,660 --> 00:27:09,240 Actually, both paths are bad. 486 00:27:11,980 --> 00:27:17,060 They might be good in some cases, but at a larger scale, there 487 00:27:17,060 --> 00:27:22,460 will definitely be cases where both paths perform really bad, 488 00:27:22,900 --> 00:27:27,540 dealing with a lot of buffer operations and bad bad timing, right 489 00:27:27,540 --> 00:27:28,580 execution time. 490 00:27:28,780 --> 00:27:32,240 So how would you solve this particular? 491 00:27:32,380 --> 00:27:32,800 How? 492 00:27:32,800 --> 00:27:33,300 What was 493 00:27:33,300 --> 00:27:33,960 Michael: the solution? 494 00:27:34,200 --> 00:27:37,760 Nikolay: Yeah, yeah, well, denormalization is 1 of the ways, 495 00:27:37,840 --> 00:27:38,340 right? 496 00:27:38,600 --> 00:27:43,400 Michael: So like storing a duplicate of this data in a materialized 497 00:27:43,680 --> 00:27:44,180 view? 498 00:27:44,380 --> 00:27:47,060 Nikolay: Yeah, For example, we take creation time and we just 499 00:27:48,080 --> 00:27:49,140 propagate it. 500 00:27:51,060 --> 00:27:55,140 For example, if it's a relationship to particular items, posts, 501 00:27:55,140 --> 00:27:58,860 we can just propagate to this table which represents this relationship. 502 00:27:59,020 --> 00:28:03,400 And then We can even have an ideal situation, a single index 503 00:28:03,400 --> 00:28:03,900 scan. 504 00:28:04,340 --> 00:28:07,500 Not index only scan because we usually need to join data and 505 00:28:07,500 --> 00:28:13,340 bring actual data from… Maybe it's not in the index, but it might 506 00:28:13,340 --> 00:28:17,060 be index only scan for this particular table, which has a relationship. 507 00:28:17,440 --> 00:28:18,780 This is 1 of the ways. 508 00:28:18,940 --> 00:28:24,180 Of course, if you have channels, well, it's more difficult, because 509 00:28:24,520 --> 00:28:27,380 if we order by creation of posts, not channels. 510 00:28:28,380 --> 00:28:29,940 So it's, yeah. 511 00:28:30,360 --> 00:28:32,640 Michael: Yeah, I think some of these cases, you mentioned the 512 00:28:32,640 --> 00:28:35,220 1 where different users have different timestamps, for example, 513 00:28:35,220 --> 00:28:38,040 if it's like last viewed, I think that gets really complicated 514 00:28:38,200 --> 00:28:39,020 in terms of... 515 00:28:39,020 --> 00:28:43,100 Nikolay: No, vice versa, I think it's good, because if we order 516 00:28:43,100 --> 00:28:47,740 by last access, For example, last access timestamp on channel, 517 00:28:49,020 --> 00:28:51,020 we just deal with channels. 518 00:28:51,020 --> 00:28:53,540 We can propagate it to the posts themselves. 519 00:28:54,100 --> 00:28:54,960 Well, we cannot. 520 00:28:54,960 --> 00:28:56,340 Yeah, I agree with you. 521 00:28:56,380 --> 00:28:57,600 It really becomes complicated. 522 00:28:58,080 --> 00:29:01,220 So there are cases where it's really hard to apply denormalization, 523 00:29:01,440 --> 00:29:03,300 but there are cases where it's easy. 524 00:29:03,340 --> 00:29:07,180 If we forget about channels and, for example, think about relationship 525 00:29:07,580 --> 00:29:11,620 between users and these objects, it can be, for example, permissions 526 00:29:11,760 --> 00:29:15,480 or it can be, I don't know, like last access timestamps should 527 00:29:15,480 --> 00:29:19,220 be stored neither in users nor in objects table. 528 00:29:19,220 --> 00:29:22,560 It should be stored in a table in between, right? 529 00:29:22,900 --> 00:29:24,360 Like many-to-many relationship. 530 00:29:24,560 --> 00:29:28,180 So in this case, usually, by default it's good. 531 00:29:28,180 --> 00:29:32,240 We just use this table, We have index, we quickly find what we 532 00:29:32,240 --> 00:29:37,020 need, 100 for this particular user, 100 objects that should be 533 00:29:37,020 --> 00:29:37,520 displayed. 534 00:29:37,720 --> 00:29:40,760 But if additional filtering, this is usually in real life, we 535 00:29:40,760 --> 00:29:44,720 need additional filtering involving data inside objects table, 536 00:29:44,720 --> 00:29:45,220 right? 537 00:29:45,420 --> 00:29:48,460 It can be, well, soft delete, for example, we mentioned soft 538 00:29:48,460 --> 00:29:48,820 delete. 539 00:29:48,820 --> 00:29:51,400 So like, deleted at timestamp. 540 00:29:51,480 --> 00:29:56,880 If it's not, if it's filled if it's not now Then the subject 541 00:29:56,880 --> 00:30:01,160 should not be showed in this result set right But we can propagate 542 00:30:01,200 --> 00:30:04,360 when we delete, we can propagate in all entries this object has 543 00:30:04,360 --> 00:30:05,400 to all users. 544 00:30:06,340 --> 00:30:08,100 We can propagate to this table. 545 00:30:08,100 --> 00:30:09,760 Of course, it can be a lot of rows. 546 00:30:09,760 --> 00:30:11,460 This depends on the situation. 547 00:30:11,780 --> 00:30:14,660 I would not propagate it synchronously if it's more than 1, 000 548 00:30:14,660 --> 00:30:17,340 rows, because delete will be super heavy. 549 00:30:17,980 --> 00:30:20,100 Soft delete, it's update actually, right? 550 00:30:21,280 --> 00:30:29,540 But we know if it's like limited number of users can access each 551 00:30:29,540 --> 00:30:32,620 object, like not more than 1,000 say, or 10,000. 552 00:30:33,280 --> 00:30:34,020 We can do that. 553 00:30:34,020 --> 00:30:35,340 Or we can do it asynchronously. 554 00:30:35,500 --> 00:30:39,380 Again, there is some need in, like I would say there is a need 555 00:30:39,380 --> 00:30:42,800 not in asynchronous, not in incremental materials, but maybe 556 00:30:42,800 --> 00:30:43,940 in asynchronous triggers. 557 00:30:44,540 --> 00:30:48,960 So like there is an Oracle, there is pragma autonomous. 558 00:30:49,640 --> 00:30:52,400 It's not exactly the same, but sometimes. 559 00:30:53,680 --> 00:30:57,700 There are folks which are using PgQ, for example, in Cloud SQL. 560 00:30:57,700 --> 00:30:58,760 It's available, right? 561 00:30:58,940 --> 00:31:02,080 1 of the good things about Cloud SQL is the availability of PgQ, 562 00:31:02,080 --> 00:31:05,180 so you can implement asynchronous triggers there yourself. 563 00:31:05,860 --> 00:31:10,240 If some object has update to be soft deleted, we propagate to 564 00:31:10,240 --> 00:31:17,000 all rows of this last accessed or last viewed or something table. 565 00:31:17,520 --> 00:31:22,160 In this case, we can have, again, we can have single index only 566 00:31:22,160 --> 00:31:22,660 scan. 567 00:31:23,560 --> 00:31:27,540 But it might be more difficult than that. 568 00:31:28,380 --> 00:31:29,340 There is another way. 569 00:31:29,340 --> 00:31:30,580 It's not about denormalization. 570 00:31:30,820 --> 00:31:32,400 This is how we solved it. 571 00:31:32,400 --> 00:31:35,860 Not only we, I know GitLab also has to-do list and they apply 572 00:31:35,860 --> 00:31:39,520 the same recipe which I think 1 day we will blog about. 573 00:31:40,920 --> 00:31:44,900 Forget about denormalization and just use huge recursive CTE with 574 00:31:44,900 --> 00:31:50,440 some tricks, some algorithm actually implementing this newsfeed 575 00:31:51,040 --> 00:31:54,340 pattern to solve it in a very interesting way. 576 00:31:54,780 --> 00:31:58,020 Michael: Oh, is it like the skip scan recursive CTE? 577 00:31:58,780 --> 00:32:02,020 Nikolay: It's not skip scan, it's like advanced skip scan. 578 00:32:03,400 --> 00:32:06,260 So you know you need to return 100 rows. 579 00:32:06,340 --> 00:32:12,280 You have 100 slots to fill, and then you start working with channels, 580 00:32:13,260 --> 00:32:16,420 filling these slots, and each time you work with next channel, 581 00:32:16,420 --> 00:32:21,000 you think, okay, this channel has a fresh post, for example, 582 00:32:21,000 --> 00:32:25,700 has a fresh object, and this is the place we like we replace 583 00:32:25,760 --> 00:32:30,480 some object we keep in memory we replace and put it to the slot 584 00:32:30,480 --> 00:32:35,660 and at some point you finish and return but it requires some 585 00:32:35,660 --> 00:32:36,680 effort to implement 586 00:32:37,080 --> 00:32:40,440 Michael: yeah I can imagine that that sounds really cool it reminds 587 00:32:40,440 --> 00:32:43,320 me a little bit of the latest feature 1 of the latest features 588 00:32:43,320 --> 00:32:47,560 in pgvector where have you have you seen this in I think 0.8 589 00:32:47,920 --> 00:32:51,420 it added being able to continue a scan. 590 00:32:51,420 --> 00:32:52,100 Like if you... 591 00:32:52,100 --> 00:32:52,920 Nikolay: Didn't help us. 592 00:32:52,920 --> 00:32:53,980 We checked yesterday. 593 00:32:54,520 --> 00:32:55,420 Michael: Oh, no. 594 00:32:55,580 --> 00:32:57,540 But the idea is the same, right? 595 00:32:57,540 --> 00:33:01,740 If you don't get enough results in your limit, go back and continue 596 00:33:01,760 --> 00:33:02,420 the scan. 597 00:33:02,840 --> 00:33:07,360 Nikolay: It's good if you have quite like uniform database. 598 00:33:07,440 --> 00:33:11,180 In our case, we are speaking about Postgres.AI, for example, 599 00:33:11,180 --> 00:33:15,620 we have more than 1 million documents and more than 90% of them 600 00:33:15,620 --> 00:33:20,340 is mailing lists, mailing lists and in-entries emails, right? 601 00:33:20,740 --> 00:33:26,180 And when, for example, you ask for source category in this huge… 602 00:33:27,440 --> 00:33:28,520 This approach doesn't help. 603 00:33:28,520 --> 00:33:32,640 But still, it encounters so many mailing list entries, it cannot 604 00:33:32,640 --> 00:33:33,540 find sources. 605 00:33:33,580 --> 00:33:36,960 So basically we are considering either, I think we currently 606 00:33:36,960 --> 00:33:41,040 already using partial indexes there, and I'm seriously thinking 607 00:33:41,040 --> 00:33:44,160 we should move to separate tables because we have only limited 608 00:33:44,720 --> 00:33:49,820 categories and our use cases, very often we deal with only 1 609 00:33:49,820 --> 00:33:50,320 source. 610 00:33:50,740 --> 00:33:52,620 Michael: You could maybe partition on source. 611 00:33:53,800 --> 00:33:58,020 Nikolay: Yeah, actually it's also a good idea maybe, yeah. 612 00:33:58,140 --> 00:34:04,340 So we deviated here from original denormalization topic, and 613 00:34:04,340 --> 00:34:08,000 I think it's another story how to implement newsfeed better, 614 00:34:08,000 --> 00:34:11,440 right, without denormalization, but with huge recursive CTE. 615 00:34:12,260 --> 00:34:16,780 But, like, my summary is that first of all, denormalization should 616 00:34:16,780 --> 00:34:22,740 be applied only you know normalization, and it can bring new 617 00:34:22,740 --> 00:34:26,820 performance dangers if you don't think about concurrency and 618 00:34:26,820 --> 00:34:32,740 various issues with Postgres MVCC, locking, LockManager, for 619 00:34:32,740 --> 00:34:34,460 example, and so on. 620 00:34:34,740 --> 00:34:41,140 It's an interesting thing, still quite useful to consider in 621 00:34:41,140 --> 00:34:44,740 many cases, but it should be tested well as usual. 622 00:34:45,060 --> 00:34:47,240 Michael: I think it's 1 of those sharp tools, right? 623 00:34:47,240 --> 00:34:54,680 Like, sometimes advanced craftsmen need sharp tools, but be careful 624 00:34:54,680 --> 00:34:55,540 with their sharp. 625 00:34:56,320 --> 00:34:57,680 Nikolay: Yeah, yeah. 626 00:34:57,920 --> 00:34:58,880 Something like this. 627 00:34:58,940 --> 00:34:59,440 Agree. 628 00:35:00,280 --> 00:35:00,780 Michael: Wonderful. 629 00:35:00,780 --> 00:35:01,820 Thanks so much, Nikolay. 630 00:35:01,960 --> 00:35:03,340 Catch you next week. 631 00:35:03,940 --> 00:35:06,360 Nikolay: Sounds good, have a good week, you too. 632 00:35:06,360 --> 00:35:07,060 Bye bye.