1 0:0:0,06 --> 0:0:2,06 Nikolay: Hello, hello, this is PostgresFM. 2 0:0:2,12 --> 0:0:7,24 My name is Nik, PostgresAI, and as usual with me, Michael, pgMustard. 3 0:0:7,5 --> 0:0:8,299999 Hi, Michael. 4 0:0:9,28 --> 0:0:10,12 Michael: Hi, Nik. How are you doing? 5 0:0:11,24 --> 0:0:12,139999 Nikolay: So the topic... 6 0:0:12,7 --> 0:0:14,28 I'm doing great, how are you? 7 0:0:14,599999 --> 0:0:15,86 Michael: I'm good, Thank you. 8 0:0:15,94 --> 0:0:16,86 What's the topic? 9 0:0:17,26 --> 0:0:18,119999 Nikolay: What's the topic? 10 0:0:18,119999 --> 0:0:21,66 The topic I chose, and everyone already saw it on cover you created, 11 0:0:21,66 --> 0:0:22,06 right? 12 0:0:22,06 --> 0:0:29,86 So the topic is 5 or so, maybe 6 things you should check when 13 0:0:29,86 --> 0:0:32,98 you start new schema or you already have some schema and your 14 0:0:32,98 --> 0:0:38,32 project is growing and you want to be better prepared for bigger 15 0:0:38,32 --> 0:0:40,02 scale and performance wise. 16 0:0:40,6 --> 0:0:45,32 So what, especially if you use AI to design and improve your 17 0:0:45,32 --> 0:0:48,48 schema and your project is growing. 18 0:0:48,76 --> 0:0:55,02 So what should you look at to check your schema health? 19 0:0:55,38 --> 0:0:56,14 Michael: Makes sense. 20 0:0:56,32 --> 0:1:0,2 Just on the topic in general, I do think there are some things 21 0:1:0,2 --> 0:1:3,58 that are better suited to asking for help from AI and not? 22 0:1:3,68 --> 0:1:7,58 Do you think like schema design is 1 of them or actually is it 23 0:1:7,58 --> 0:1:11,32 1 of those ones that maybe we should still do it a little bit 24 0:1:11,32 --> 0:1:13,12 more ourselves at the moment? 25 0:1:13,66 --> 0:1:18,04 Nikolay: Well as you know I'm very very very intensive user of 26 0:1:18,04 --> 0:1:18,54 AI. 27 0:1:18,86 --> 0:1:19,36 Yeah. 28 0:1:20,28 --> 0:1:23,4 Honestly, like I do everything through AI right now. 29 0:1:24,44 --> 0:1:29,24 And I'm 100% schema design is a topic where AI can help a lot. 30 0:1:29,24 --> 0:1:32,36 But it also matters which questions you ask. 31 0:1:32,98 --> 0:1:37,06 If you just say, I want this and don't pay attention, you will 32 0:1:37,06 --> 0:1:39,64 have low quality schema in many cases still. 33 0:1:40,44 --> 0:1:44,34 That's why I said, let's have this episode because it might be 34 0:1:44,34 --> 0:1:48,74 quite basic for many advanced users of Postgres, But this checklist 35 0:1:48,84 --> 0:1:53,08 we are going to present today, it can be good thing to keep in 36 0:1:53,08 --> 0:1:56,82 mind always when you design schema, just to ask right questions 37 0:1:56,94 --> 0:1:57,66 and revisit. 38 0:1:58,26 --> 0:2:1,4 Also, I'm not sure how exactly you use AI. 39 0:2:1,4 --> 0:2:6,16 I use it always like I work with people, developers. 40 0:2:7,4 --> 0:2:12,58 When you just ask something and think those engineers, humans 41 0:2:12,58 --> 0:2:16,46 or AI, will solve all your problems and make all the decisions, 42 0:2:17,1 --> 0:2:20,28 you're offloading full responsibility to their shoulders, so 43 0:2:20,28 --> 0:2:24,36 then you have big negative surprises always, right? 44 0:2:24,6 --> 0:2:29,64 So it's better to keep requests as detailed as possible when 45 0:2:29,64 --> 0:2:34,48 you work with AI, and also make some iterations to review and 46 0:2:34,48 --> 0:2:34,98 improve. 47 0:2:35,54 --> 0:2:38,92 And when making iterations, like first, like I want some schema, 48 0:2:39,02 --> 0:2:44,12 like, I don't know, like LinkedIn social media schema or something, 49 0:2:44,12 --> 0:2:44,62 right? 50 0:2:45,04 --> 0:2:48,98 It will give you immediately something, but then you want some 51 0:2:49,22 --> 0:2:50,52 details to be checked. 52 0:2:50,54 --> 0:2:53,76 And this is exactly when this checklist can be very helpful. 53 0:2:54,02 --> 0:2:58,82 So you can revisit and think and imagine when you will have gigabytes 54 0:2:58,86 --> 0:3:1,58 and terabytes of data, what will happen. 55 0:3:1,86 --> 0:3:7,64 So to avoid painful refactoring in the future and outages maybe 56 0:3:7,64 --> 0:3:12,16 or performance degradation, the price you can pay in the beginning 57 0:3:12,16 --> 0:3:14,98 is much lower than the price you pay later. 58 0:3:16,96 --> 0:3:21,86 So I never work with AI in 1 shot, I always make iterations to 59 0:3:21,86 --> 0:3:25,08 improve in any question and to revisit and sometimes combine 60 0:3:25,08 --> 0:3:26,18 multiple LLMs. 61 0:3:28,26 --> 0:3:31,36 This is exactly when you can use this list of questions. 62 0:3:31,36 --> 0:3:35,58 So first topic is the data type of primary key. 63 0:3:36,68 --> 0:3:39,56 Michael: Yeah, and I feel like the answer to this has changed, 64 0:3:39,64 --> 0:3:43,9 or my opinion on what the best default for this has changed, 65 0:3:43,9 --> 0:3:49,2 actually, in the last few years, just off the back of learning 66 0:3:49,2 --> 0:3:53,4 more about timestamp ordered UUIDs, right? 67 0:3:53,56 --> 0:3:57,88 This used to be an age-old debate, right, UUID versus integer 68 0:3:57,88 --> 0:3:59,34 or big integer really. 69 0:4:0,06 --> 0:4:3,84 And most of the kind of blog posts I would see are like trying 70 0:4:3,84 --> 0:4:6,3 to just encourage people to use BIGINT over int. 71 0:4:7,06 --> 0:4:12,48 But now that we have first-class support for time-ordered UUIDs, 72 0:4:13,48 --> 0:4:15,92 I don't see many reasons not to go with that. 73 0:4:15,92 --> 0:4:16,8 What do you think? 74 0:4:16,8 --> 0:4:20,78 Nikolay: Well, the size, 8, is double. 75 0:4:21,04 --> 0:4:21,54 Yeah. 76 0:4:21,58 --> 0:4:24,94 Yeah, your ID is 16 bytes, so it's quite a big price to pay. 77 0:4:25,2 --> 0:4:29,44 Each row, you need to have 8 more bytes. 78 0:4:30,14 --> 0:4:31,015 It's not that much, is it? 79 0:4:31,015 --> 0:4:32,02 In many cases, it's fine. 80 0:4:32,2 --> 0:4:34,34 Well, indexes will be also bigger. 81 0:4:34,82 --> 0:4:36,3 Indexes double, yeah. 82 0:4:37,12 --> 0:4:39,98 It might make sense if volumes are huge. 83 0:4:40,12 --> 0:4:44,44 But anyway, to keep things simple, a rule to remember, red flags, 84 0:4:45,06 --> 0:4:49,5 If you are prepared for really large volumes of data, it must 85 0:4:49,5 --> 0:4:55,16 not be int4, it should be int8. 86 0:4:55,44 --> 0:5:2,24 And in terms of UUID path, data type is always UUID for both 87 0:5:2,24 --> 0:5:4,18 version 4 and version 7. 88 0:5:4,6 --> 0:5:8,7 But you can check default how it's generated. 89 0:5:8,94 --> 0:5:13,78 And in modern Postgres, there is a function, I think in 18, only 90 0:5:13,78 --> 0:5:14,6 in the latest version. 91 0:5:14,6 --> 0:5:15,02 I think 92 0:5:15,02 --> 0:5:15,26 Michael: so. 93 0:5:15,26 --> 0:5:17,44 It didn't quite make 17, did it? 94 0:5:17,44 --> 0:5:21,76 Nikolay: So if you're on Postgres 18, so it's better to use UUIDv7 95 0:5:21,76 --> 0:5:25,86 but also remembering trade-offs, because there is 96 0:5:25,86 --> 0:5:29,52 timestamp there, and the values will be ordered, like with integer 97 0:5:30,06 --> 0:5:31,08 or big integer. 98 0:5:31,4 --> 0:5:33,18 So there is a nuance here. 99 0:5:33,58 --> 0:5:37,12 But performance-wise it's much better than UUIDv4. 100 0:5:37,9 --> 0:5:41,54 There are many articles about it and you just need to check, 101 0:5:41,54 --> 0:5:44,36 okay, are we going to go with integer? 102 0:5:45,04 --> 0:5:48,7 Then it's int8 or BIGINT, right? 103 0:5:48,96 --> 0:5:50,64 Are we going to go with UUID? 104 0:5:51,06 --> 0:5:56,2 Then default generation, default should be version 7 for Postgres 105 0:5:56,2 --> 0:5:56,58 18. 106 0:5:56,58 --> 0:5:59,92 For older version, it's also not an excuse to stick with version 107 0:5:59,92 --> 0:6:0,28 4. 108 0:6:0,28 --> 0:6:2,0 You can write your own function. 109 0:6:2,9 --> 0:6:5,42 And you ask AI to write your own function. 110 0:6:6,04 --> 0:6:8,1 Michael: Yes, or generate them outside of the database. 111 0:6:8,3 --> 0:6:12,62 Nikolay: I slightly prefer this option less because if you have 112 0:6:12,62 --> 0:6:17,08 multiple application nodes, Who knows what will happen with their 113 0:6:17,08 --> 0:6:17,58 clocks? 114 0:6:18,58 --> 0:6:25,4 And since UUIDv7 includes timestamp inside it, order might 115 0:6:25,4 --> 0:6:29,9 be not, I don't know, like I prefer to leave it on database shoulders 116 0:6:29,9 --> 0:6:32,8 to have like on the primary single source of true. 117 0:6:32,8 --> 0:6:37,4 On the other side, UUID by design is generated, it serves purpose 118 0:6:37,44 --> 0:6:40,46 to work on multiple, like on distributed systems, right? 119 0:6:40,58 --> 0:6:42,18 So maybe it's not a big deal. 120 0:6:42,18 --> 0:6:43,82 Generate on client side. 121 0:6:44,38 --> 0:6:47,68 Michael: All I mean is if you're on an old Postgres and it saves 122 0:6:47,68 --> 0:6:49,78 you creating your own function or whatever. 123 0:6:50,28 --> 0:6:55,36 Nikolay: Postgres waited a couple of years to bring UUIDv7 officially. 124 0:6:56,0 --> 0:6:59,1 Although again, you can create even SQL function, even not PL/pgSQL 125 0:6:59,1 --> 0:7:0,8 function, simple SQL function. 126 0:7:0,8 --> 0:7:4,64 And if you ask your AI to search for example, probably it will 127 0:7:4,64 --> 0:7:8,8 find my page how to do it, but or other people's pages. 128 0:7:9,44 --> 0:7:13,68 But in other libraries from Google, from Node.js library, everything, 129 0:7:14,24 --> 0:7:17,74 they have it for a couple of years already, so for longer. 130 0:7:18,12 --> 0:7:20,74 They implemented before standard was finalized. 131 0:7:21,62 --> 0:7:24,92 Michael: Yeah, so huge red flags to look out for if it's using 132 0:7:24,92 --> 0:7:28,64 the integer data type, which is the same as int4, or serial. 133 0:7:29,02 --> 0:7:31,92 I think AI sometimes uses, it will define it as serial. 134 0:7:31,92 --> 0:7:35,46 So if you just see that word without bigserial, you might hit 135 0:7:35,46 --> 0:7:38,48 that limit at 2000000000 or whatever the, I can't remember the 136 0:7:38,48 --> 0:7:39,52 exact upper limit. 137 0:7:39,56 --> 0:7:44,08 Nikolay: Yeah, and avoid UUID version 4 if you want good performance 138 0:7:44,1 --> 0:7:45,4 on large volumes of data. 139 0:7:45,4 --> 0:7:46,56 Yeah, that's it actually. 140 0:7:46,56 --> 0:7:50,04 Let's keep it simple because we could discuss more advanced situations. 141 0:7:50,74 --> 0:7:54,44 Michael: I think we should do a whole episode someday on migrations, 142 0:7:54,72 --> 0:7:57,34 like for people that are getting close to that 2 billion 143 0:7:57,34 --> 0:7:58,26 Nikolay: number, that 144 0:7:58,26 --> 0:7:59,44 Michael: would be a good topic. 145 0:7:59,44 --> 0:8:2,12 Nikolay: I can share, I had a lot of experience with different 146 0:8:2,12 --> 0:8:2,62 approaches. 147 0:8:3,66 --> 0:8:4,28 Let's do that. 148 0:8:4,28 --> 0:8:4,78 Good. 149 0:8:5,66 --> 0:8:6,98 So how to define primary key? 150 0:8:6,98 --> 0:8:10,46 I can actually have an article about it, how to... 151 0:8:11,28 --> 0:8:11,78 Cool. 152 0:8:12,1 --> 0:8:12,74 Michael: Let's do that. 153 0:8:12,74 --> 0:8:15,58 Nikolay: Covering multiple paths so we could talk about this. 154 0:8:16,38 --> 0:8:18,28 Number 2 is constraints. 155 0:8:18,62 --> 0:8:19,7 Topic is constraints. 156 0:8:21,38 --> 0:8:22,32 Michael: Other constraints. 157 0:8:22,54 --> 0:8:22,94 Nikolay: Yeah, yeah. 158 0:8:22,94 --> 0:8:23,6 Yeah, other. 159 0:8:23,94 --> 0:8:25,32 Beyond primary key. 160 0:8:25,58 --> 0:8:25,9 Yeah. 161 0:8:25,9 --> 0:8:31,68 So I would just ask, first thing, are we using constraints well? 162 0:8:31,78 --> 0:8:33,84 Like to myself or AI and so on. 163 0:8:33,84 --> 0:8:38,26 And because Postgres has 6 types of constraints and it's quite 164 0:8:38,26 --> 0:8:45,56 rich tooling, and it's much more convenient to have them earlier, 165 0:8:46,08 --> 0:8:48,48 not because adding them later is a big pain. 166 0:8:48,48 --> 0:8:54,44 Right now we have quite good support of two-step constraint definitions. 167 0:8:55,4 --> 0:9:0,48 I think in Postgres 19 it will be possible also if not null constraints, 168 0:9:0,48 --> 0:9:1,36 which is interesting. 169 0:9:1,36 --> 0:9:5,14 But anyway, check constraints, foreign keys, unique constraints, 170 0:9:5,14 --> 0:9:10,02 it's possible to create first in almost finished state and then 171 0:9:10,02 --> 0:9:13,38 finalize it to avoid the long-lasting locks. 172 0:9:13,38 --> 0:9:17,96 But this is not the reason why I prefer to think about it in 173 0:9:17,96 --> 0:9:18,82 the very beginning. 174 0:9:18,96 --> 0:9:21,8 The main reason is the quality of data. 175 0:9:21,98 --> 0:9:24,82 This is why constraints exist in the first place, right? 176 0:9:25,44 --> 0:9:29,62 So if you introduce constraints early in your project, data quality 177 0:9:29,62 --> 0:9:30,3 is good. 178 0:9:31,84 --> 0:9:32,52 Introducing them 179 0:9:32,52 --> 0:9:33,32 Michael: later probably will... 180 0:9:33,32 --> 0:9:36,42 Yeah, and database side as well. 181 0:9:36,42 --> 0:9:37,4 I mean, it just... 182 0:9:37,64 --> 0:9:38,6 Nikolay: Oh yeah, yeah. 183 0:9:38,94 --> 0:9:40,78 I don't trust constraints outside. 184 0:9:43,26 --> 0:9:45,54 With age, I became less radical. 185 0:9:45,66 --> 0:9:49,18 My points of view became less radical with it. 186 0:9:49,18 --> 0:9:53,54 So I just observe without fighting, like with backend engineers 187 0:9:53,94 --> 0:9:57,74 who try to implement constraints on application side. 188 0:9:57,74 --> 0:10:1,16 I just have a note, like they are not reliable because who knows 189 0:10:1,44 --> 0:10:5,08 which other layers of types of applications, especially these 190 0:10:5,08 --> 0:10:9,72 days with AI, it's so easy to add something else, implement it 191 0:10:9,72 --> 0:10:12,56 in a different framework or even a language, it's super easy 192 0:10:12,56 --> 0:10:13,24 right now. 193 0:10:13,38 --> 0:10:16,5 And you will need to reimplement those constraints, or you will 194 0:10:16,5 --> 0:10:19,46 just forget and miss them and data quality will suffer. 195 0:10:21,04 --> 0:10:25,64 Michael: So specifically, something I think I'd like to see more 196 0:10:25,64 --> 0:10:30,26 of or at least I see a lot of people not doing is columns that 197 0:10:30,26 --> 0:10:32,8 you're not expecting to put any null values in, or you don't 198 0:10:32,8 --> 0:10:36,22 want to have any null values in, actually explicitly making them 199 0:10:36,22 --> 0:10:37,0 not null. 200 0:10:37,06 --> 0:10:40,96 Like, if you don't specify, then columns are nullable. 201 0:10:41,76 --> 0:10:45,84 And I think most people, or at least most AIs I see, are creating, 202 0:10:46,1 --> 0:10:49,58 like by default, they don't put NOT NULL on every column. 203 0:10:49,84 --> 0:10:52,7 Nikolay: Yeah, there's an opinion that NOT NULL should be default. 204 0:10:53,2 --> 0:10:56,2 Anyway, it's hard to have a default, but inside your project 205 0:10:56,2 --> 0:11:0,1 you can say, okay, unless it's explicitly specified, let's make 206 0:11:0,1 --> 0:11:1,28 columns NOT NULL. 207 0:11:1,62 --> 0:11:2,12 Exactly. 208 0:11:3,16 --> 0:11:6,82 And avoid troubles with NULL we talked about in a special episode 209 0:11:6,82 --> 0:11:8,08 about NULLs, right? 210 0:11:8,26 --> 0:11:12,72 Michael: Yeah, well and I guess in this specific case there is 211 0:11:12,72 --> 0:11:17,58 also, well with some constraints, mostly we care about, we're 212 0:11:17,58 --> 0:11:20,52 doing it for quality reasons, but there is, there is like a tiny 213 0:11:20,52 --> 0:11:23,56 amount of performance optimization that comes out of them. 214 0:11:23,56 --> 0:11:28,48 I guess a bit more for unique constraints, like if you, if the 215 0:11:28,48 --> 0:11:31,32 database knows for sure this is going to be, this is unique, 216 0:11:31,32 --> 0:11:32,58 it can do some optimizations. 217 0:11:33,32 --> 0:11:36,02 So yeah, anyway, but yeah, good point about, I like that about 218 0:11:36,02 --> 0:11:36,52 NOT NULL. 219 0:11:37,84 --> 0:11:39,82 Nikolay: Yeah, yeah, unique is interesting stuff. 220 0:11:39,96 --> 0:11:44,08 And also, I don't think foreign keys are underused in Postgres 221 0:11:44,12 --> 0:11:44,62 ecosystem. 222 0:11:45,3 --> 0:11:49,34 Days when everyone suggested that foreign keys are super bad, 223 0:11:49,54 --> 0:11:52,8 for me it's 20 years ago, I remember these topics, let's avoid 224 0:11:52,8 --> 0:11:53,76 foreign keys completely. 225 0:11:54,52 --> 0:11:57,5 Although we know that foreign keys can bite badly, we had an 226 0:11:57,5 --> 0:12:4,14 episode with Metronome company about issues basically with MultiXact 227 0:12:4,76 --> 0:12:8,56 related issues which can be caused when you use foreign keys 228 0:12:8,56 --> 0:12:9,06 a lot. 229 0:12:9,06 --> 0:12:12,68 But anyway, I still think foreign keys is a good thing to have, 230 0:12:12,8 --> 0:12:13,54 data quality. 231 0:12:13,86 --> 0:12:18,3 But I think what is usually underused is check constraints. 232 0:12:18,84 --> 0:12:19,7 They are great. 233 0:12:20,38 --> 0:12:22,72 They could be used much more often. 234 0:12:23,1 --> 0:12:25,9 And for example, a classic example, when I say, like, when I 235 0:12:25,9 --> 0:12:31,08 see in schema ENUM data type, I think, okay, later, If we want 236 0:12:31,08 --> 0:12:36,36 to change this list of possible values, how will our migration? 237 0:12:37,36 --> 0:12:38,94 Michael: Especially removing any. 238 0:12:39,74 --> 0:12:40,24 Yeah, 239 0:12:40,52 --> 0:12:43,76 Nikolay: so check constraints are slightly more flexible. 240 0:12:46,58 --> 0:12:49,44 Well, Also removing is a topic, right? 241 0:12:49,44 --> 0:12:52,18 But check constraint you can introduce in 2 stages. 242 0:12:52,92 --> 0:12:57,04 Again, you can drop the old 1 and you can first create a new 243 0:12:57,04 --> 0:13:0,6 1 in NOT VALID state and then validate it in a separate transaction. 244 0:13:1,56 --> 0:13:7,12 But I just see it like quite useful, quite convenient tool for 245 0:13:7,12 --> 0:13:11,04 me and you see it in schema right away like this this column 246 0:13:11,04 --> 0:13:15,36 should be like for example in this range or it should be this 247 0:13:15,36 --> 0:13:16,36 list or something. 248 0:13:17,02 --> 0:13:20,7 So I wish it was used more often. 249 0:13:21,48 --> 0:13:21,98 Michael: Yeah. 250 0:13:22,82 --> 0:13:24,3 What do you see in... 251 0:13:24,32 --> 0:13:28,9 Going back to foreign keys quickly, do you see a lot of the LLMs 252 0:13:29,32 --> 0:13:33,8 doing a lot of things on update cascade, on delete set NULL, 253 0:13:33,8 --> 0:13:34,76 things like that. 254 0:13:34,76 --> 0:13:38,68 Do you see any cascade stuff popping in there by default or not 255 0:13:38,68 --> 0:13:39,97 when it should be or when you think it 256 0:13:39,97 --> 0:13:40,08 Nikolay: should be? 257 0:13:40,08 --> 0:13:41,06 It's a good question. 258 0:13:41,18 --> 0:13:44,94 I actually don't remember having issues with this question. 259 0:13:45,12 --> 0:13:51,26 I know deferred constraints can might be an issue. 260 0:13:51,46 --> 0:13:55,12 I remember a case when pg_repack, it was a company called Miro, 261 0:13:55,12 --> 0:13:56,42 maybe you know it, right? 262 0:13:56,72 --> 0:13:59,7 They had issues with pg_repack and deferred constraints. 263 0:13:59,7 --> 0:14:3,42 There is an article, old article from them about this. 264 0:14:4,12 --> 0:14:8,72 But I don't remember particular issues here, and I think this 265 0:14:8,72 --> 0:14:12,48 leads us actually to the question for our particular projects, 266 0:14:12,8 --> 0:14:13,64 what's better? 267 0:14:14,68 --> 0:14:19,14 And If I had any doubts with AI, I would just make some experiments. 268 0:14:19,9 --> 0:14:24,96 Based on the product we are developing, I would say, okay, for 269 0:14:24,96 --> 0:14:30,58 example, in 1 year, how many of entities in which entity table 270 0:14:30,58 --> 0:14:31,4 we expect? 271 0:14:32,32 --> 0:14:37,46 All other tables, let's fill it with fake data, run vacuum Analyze 272 0:14:37,58 --> 0:14:41,76 to have good state, and then just explore plans with pgMustard, 273 0:14:41,98 --> 0:14:42,68 for example. 274 0:14:44,16 --> 0:14:47,96 Let's think what kind of workload we might have according to 275 0:14:47,96 --> 0:14:51,4 user stories in our specification of the product and so on. 276 0:14:51,58 --> 0:14:54,3 And then collect plans and see, right? 277 0:14:54,52 --> 0:14:59,44 And then include also modifying queries and collect those plans 278 0:14:59,44 --> 0:15:0,92 and think how it will work. 279 0:15:1,02 --> 0:15:5,74 It's so easy these days to make these experiments before we finalize 280 0:15:5,74 --> 0:15:9,38 all the decisions and then you will see it in action and it's 281 0:15:9,38 --> 0:15:9,88 great. 282 0:15:10,26 --> 0:15:15,22 So collect all the plans, find like weak spots where we have 283 0:15:15,38 --> 0:15:18,84 suboptimal plans and how we can deal with this. 284 0:15:18,84 --> 0:15:23,94 If, for example, we have entity and another, and 1 too many, 285 0:15:24,14 --> 0:15:26,14 and it's many, so many, right? 286 0:15:26,18 --> 0:15:30,14 And then we want Delete to be supported in UI, where people, 287 0:15:30,14 --> 0:15:33,58 as we know, expect 200 milliseconds, less than 1 second, let's 288 0:15:33,58 --> 0:15:34,26 say, right? 289 0:15:34,3 --> 0:15:38,48 And when this Delete of 1 row triggers propagating according 290 0:15:38,48 --> 0:15:43,62 to foreign key triggers, deletion of 1000000 rows, it cannot 291 0:15:43,62 --> 0:15:45,08 meet our requirements, right? 292 0:15:45,08 --> 0:15:49,64 Now we need to think about asynchronous deletion, maybe using 293 0:15:49,64 --> 0:15:52,66 some event queue system or something, right? 294 0:15:52,8 --> 0:15:53,3 Yeah. 295 0:15:53,6 --> 0:15:57,78 And this will pop up if you prepare a good experiment here, right? 296 0:15:57,78 --> 0:15:59,8 Without experiment, it's hard to say. 297 0:15:59,9 --> 0:16:1,3 You need to answer questions. 298 0:16:2,08 --> 0:16:5,64 How big are the tables and how this relationship between tables 299 0:16:5,64 --> 0:16:8,98 on foreign keys will be in the worst case, for example. 300 0:16:8,98 --> 0:16:13,52 Can user create like million of entities and then we need to 301 0:16:13,52 --> 0:16:14,96 delete this user, for example. 302 0:16:14,96 --> 0:16:18,16 This is a great question and with AI you can iterate so fast 303 0:16:18,16 --> 0:16:18,9 these days. 304 0:16:18,9 --> 0:16:19,62 So great. 305 0:16:20,28 --> 0:16:22,16 Collect plans and then think and decide. 306 0:16:23,5 --> 0:16:28,28 Okay, it's a good question, but I don't see it as a simple question. 307 0:16:28,78 --> 0:16:29,38 Michael: No, no, no. 308 0:16:29,38 --> 0:16:32,68 It was more, I just asked in case you'd seen there was a chance 309 0:16:32,68 --> 0:16:37,54 that LLMs were throwing it in on every foreign key and That was 310 0:16:37,54 --> 0:16:40,64 maybe a bad like bad deal Oh, but it sounds like they're not 311 0:16:40,64 --> 0:16:43,26 Nikolay: maybe a wrong person to ask about this because I don't 312 0:16:43,26 --> 0:16:46,24 just I design sometimes these days I have a few applications 313 0:16:46,36 --> 0:16:49,9 designed Yeah created from scratch with AI but I mostly deal 314 0:16:49,9 --> 0:16:53,26 with things which other people already created, and I just see 315 0:16:53,26 --> 0:16:54,9 how AI can help. 316 0:16:55,32 --> 0:17:0,12 So I'm not sure will this propagation of deletion, for example, 317 0:17:0,12 --> 0:17:6,5 will be supported in how exactly it will be implemented. 318 0:17:6,5 --> 0:17:10,02 But it's definitely worth thinking in the context of latencies 319 0:17:10,44 --> 0:17:13,44 and what should be offloaded and what tools we'll be using to 320 0:17:13,44 --> 0:17:15,78 offload in this, to background jobs, for example. 321 0:17:18,48 --> 0:17:24,72 It's just a thing that you need to, instead of thinking, looking 322 0:17:24,72 --> 0:17:30,08 at code, I would think looking at dynamic results, experiments. 323 0:17:31,64 --> 0:17:35,04 Michael: Yeah, and I'm thinking, like if we're talking specifically 324 0:17:35,4 --> 0:17:40,44 about schema design when you expect to hit scale, I might default 325 0:17:40,64 --> 0:17:47,56 to not using ON X CASCADE just by default because of those, because 326 0:17:47,56 --> 0:17:51,14 at scale it can become problematic so that would probably be, 327 0:17:51,34 --> 0:17:54,62 if you had to come up with a best practice for if you're designing 328 0:17:54,62 --> 0:17:56,58 at scale, maybe lean in that direction. 329 0:17:56,58 --> 0:18:0,22 Nikolay: That's interesting, that's interesting because This 330 0:18:0,22 --> 0:18:5,22 was my position for a long time, but it's not about AI building 331 0:18:5,22 --> 0:18:5,9 or something. 332 0:18:5,94 --> 0:18:6,92 This was my position. 333 0:18:7,04 --> 0:18:10,52 Let's, like, it's dangerous because who knows how many rows we 334 0:18:10,52 --> 0:18:11,46 will need to delete. 335 0:18:11,46 --> 0:18:13,98 Like, it's like it's unbounded delete, right? 336 0:18:14,06 --> 0:18:16,26 Unlimited delete, This is how it feels. 337 0:18:17,4 --> 0:18:18,8 Let's design without it. 338 0:18:18,8 --> 0:18:24,86 But then I see in my consulting practice, I see cases where really 339 0:18:24,86 --> 0:18:28,02 large projects have it and it's just not 1 project. 340 0:18:28,08 --> 0:18:31,96 Many projects I see and they keep it and somehow live with it. 341 0:18:31,96 --> 0:18:35,14 They at some point implement non-cascaded, like asynchronous 342 0:18:35,22 --> 0:18:39,22 deletion, but by default they still rely on cascaded deletes 343 0:18:39,22 --> 0:18:40,74 somehow and they are fine. 344 0:18:40,9 --> 0:18:41,68 This is interesting. 345 0:18:41,76 --> 0:18:44,28 So reality shows it's not that bad. 346 0:18:44,44 --> 0:18:48,34 But I agree with your way of thinking and this is my default 347 0:18:48,34 --> 0:18:49,66 way of thinking as well. 348 0:18:51,04 --> 0:18:55,6 Postgres can survive very terrible things these days much better 349 0:18:55,6 --> 0:18:57,1 than 20 years ago. 350 0:18:57,78 --> 0:18:58,78 Including the revolution 351 0:18:59,7 --> 0:19:0,06 of 100 years... 352 0:19:0,06 --> 0:19:0,48 Michael: The only 353 0:19:0,48 --> 0:19:4,44 time I see that being a horrendous issue, even in small 354 0:19:4,44 --> 0:19:8,04 to medium-sized projects, is if people haven't indexed the 355 0:19:8,48 --> 0:19:9,76 Nikolay: second part of foreign key. 356 0:19:9,76 --> 0:19:11,3 Michael: Exactly, the referencing column. 357 0:19:11,38 --> 0:19:12,88 Nikolay: There are 2 ends of foreign key. 358 0:19:12,88 --> 0:19:18,42 1 is always indexed because it's primary key, the other 1, it's 359 0:19:18,42 --> 0:19:20,04 your job to index it, right? 360 0:19:20,38 --> 0:19:21,48 To not forget. 361 0:19:22,06 --> 0:19:23,2 Yeah, yeah, yeah. 362 0:19:23,2 --> 0:19:25,52 We have now a checkup, old checkup, actually. 363 0:19:25,52 --> 0:19:29,2 We have a special report just for this case, yeah. 364 0:19:30,04 --> 0:19:35,06 Good, but it's all good questions, but I feel we can spend a 365 0:19:35,06 --> 0:19:37,12 lot in the Constraints area. 366 0:19:37,12 --> 0:19:38,68 I think we had Constraints episodes. 367 0:19:38,68 --> 0:19:39,0 We did 368 0:19:39,0 --> 0:19:39,96 Michael: have an episode, yeah. 369 0:19:39,96 --> 0:19:41,74 Let's link that up and move on. 370 0:19:41,98 --> 0:19:42,86 Nikolay: Yeah, let's move on. 371 0:19:42,86 --> 0:19:47,18 Some entertaining topic, Column Tetris, which is sometimes it 372 0:19:47,18 --> 0:19:51,3 might bring some quite interesting and good savings. 373 0:19:52,12 --> 0:19:55,66 In many cases it won't, but I just see people enjoy this topic, 374 0:19:55,94 --> 0:19:58,02 and I decided to include it. 375 0:19:58,02 --> 0:20:3,24 So the idea is, when you design a new Schema, the price to pay 376 0:20:3,26 --> 0:20:5,16 to reorder Columns is very low. 377 0:20:5,16 --> 0:20:7,38 You can reorder it easily. 378 0:20:9,22 --> 0:20:13,48 The problem is that for Postgres, physically, the Column order 379 0:20:13,48 --> 0:20:13,98 matters. 380 0:20:15,16 --> 0:20:19,92 Due to padding alignment, if int2, for example, is followed 381 0:20:19,92 --> 0:20:26,64 by int8, you will see a gap of 6 bytes of zeros in every 382 0:20:26,64 --> 0:20:27,14 row. 383 0:20:27,34 --> 0:20:28,34 And this is bad. 384 0:20:28,58 --> 0:20:33,34 It's like a waste of storage and also memory, which is not only 385 0:20:33,34 --> 0:20:36,54 disk, it will pollute with those zeros, it will pollute memory. 386 0:20:36,58 --> 0:20:41,34 And sometimes you can be so unlucky, I saw cases like 30% of 387 0:20:41,74 --> 0:20:43,48 each row is like zeros. 388 0:20:44,54 --> 0:20:48,96 It's, And Postgres doesn't have automatic reordering of Columns. 389 0:20:48,96 --> 0:20:51,24 It could, actually, but it doesn't have it. 390 0:20:51,9 --> 0:20:54,76 Michael: So the order you define it in, let's say you do create 391 0:20:54,76 --> 0:20:58,78 table, the order you put those Columns in, that's the order they 392 0:20:58,78 --> 0:20:59,76 end up on disk. 393 0:20:59,76 --> 0:21:3,14 And every new Column you add goes at the end. 394 0:21:4,08 --> 0:21:5,28 That makes sense, right? 395 0:21:5,28 --> 0:21:8,68 But even if it could be slotted in, let's say you add a new Boolean 396 0:21:8,68 --> 0:21:11,54 type, and it could, I don't actually know if that would work, 397 0:21:11,54 --> 0:21:13,26 but it always goes on the end. 398 0:21:13,26 --> 0:21:14,044 Nikolay: And there's no way to sum up. 399 0:21:14,044 --> 0:21:14,64 Boolean is 1 byte. 400 0:21:15,28 --> 0:21:17,64 Michael: Yeah, but I wonder if that would work. 401 0:21:17,64 --> 0:21:19,58 Nikolay: In Boolean, 7 bits are wasted already. 402 0:21:21,04 --> 0:21:22,96 If you zoom in like under my 403 0:21:22,96 --> 0:21:24,96 Michael: let's put brilliant brilliant brilliant next to each 404 0:21:24,96 --> 0:21:25,66 other right 405 0:21:25,84 --> 0:21:28,18 Nikolay: no no no no no Boolean 406 0:21:28,18 --> 0:21:28,52 Michael: really 407 0:21:28,52 --> 0:21:29,74 Nikolay: is 1 bit. 408 0:21:30,16 --> 0:21:30,7 Michael: Oh bit. 409 0:21:30,7 --> 0:21:32,44 Yeah, good point good point, so yeah, 410 0:21:32,64 --> 0:21:34,66 Nikolay: But it's stored as a 1 byte. 411 0:21:35,2 --> 0:21:37,36 7 bits already wasted. 412 0:21:37,8 --> 0:21:43,48 But if you have Boolean and then timestamp, for example. 413 0:21:44,34 --> 0:21:48,58 For example, first column, as we all agree, should be id, the 414 0:21:48,58 --> 0:21:49,56 primary key. 415 0:21:49,64 --> 0:21:52,56 I'm joking, it's not always, but it's very popular. 416 0:21:52,9 --> 0:21:57,16 Id is the first column, int8, occupies the whole 8-byte 417 0:21:57,18 --> 0:21:57,68 word. 418 0:21:58,46 --> 0:22:0,38 And then we have Boolean and then timestamp. 419 0:22:1,1 --> 0:22:5,98 Not only you wasted 7 bits inside Boolean, it's already by design, 420 0:22:5,98 --> 0:22:9,12 like Postgres doesn't have less than 1 byte. 421 0:22:10,02 --> 0:22:15,6 But then you waste 7 bytes, so 7 bits first and 7 bytes because 422 0:22:15,6 --> 0:22:17,06 of this alignment padding. 423 0:22:17,54 --> 0:22:23,0 And your Boolean, which is like, by sense, it's only 1 bit, suddenly 424 0:22:23,0 --> 0:22:24,06 occupies 8 bytes. 425 0:22:24,06 --> 0:22:27,38 It's a huge waste of resources, right? 426 0:22:27,72 --> 0:22:33,18 But the bigger picture here is that people usually realize that 427 0:22:33,18 --> 0:22:37,48 waste is significant only when it's a huge table, like 100 million 428 0:22:37,48 --> 0:22:38,2 rows, right? 429 0:22:38,44 --> 0:22:42,7 And then, oh, so many spaces wasted because we didn't play this 430 0:22:42,7 --> 0:22:43,94 column Tetris initially. 431 0:22:45,06 --> 0:22:49,62 But this is like chicken or egg problem, maybe inverted. 432 0:22:49,84 --> 0:22:54,02 Anyway, so you realize it later, but you could do it earlier, 433 0:22:54,1 --> 0:22:56,96 but you didn't think about it because when you have only 1,000 434 0:22:56,96 --> 0:22:59,32 rows, for example, it costs you nothing. 435 0:23:0,18 --> 0:23:2,46 But again, with AI, it's so easy. 436 0:23:2,9 --> 0:23:5,3 You just say, let's play Column Tetris in Postgres. 437 0:23:5,84 --> 0:23:7,04 AI should know it. 438 0:23:7,04 --> 0:23:9,88 I haven't tried, but I'm pretty sure it should know it, because 439 0:23:9,88 --> 0:23:11,84 there are many articles about it. 440 0:23:12,12 --> 0:23:13,54 Michael: Oh, and we did an episode. 441 0:23:14,32 --> 0:23:17,08 Nikolay: Yeah, starting from Stack Overflow, where this topic 442 0:23:17,08 --> 0:23:18,9 was very well described. 443 0:23:19,4 --> 0:23:22,16 So AI should know it, this topic. 444 0:23:22,66 --> 0:23:25,84 When you create a new table, you think many rows will be stored, 445 0:23:25,84 --> 0:23:28,22 just let's apply column Tetris to this. 446 0:23:28,48 --> 0:23:28,98 Boom. 447 0:23:29,38 --> 0:23:30,74 It will reorder columns. 448 0:23:32,2 --> 0:23:32,88 Never try it again. 449 0:23:32,88 --> 0:23:36,1 Michael: Yeah, what do you think, like I feel like there's a... 450 0:23:37,58 --> 0:23:40,52 I don't know if I'd still want to have, I'd still want to do 451 0:23:40,52 --> 0:23:46,32 it myself just because I feel like there's also a natural order 452 0:23:46,32 --> 0:23:46,92 for columns. 453 0:23:46,92 --> 0:23:51,68 So I would want to put all the 8-byte ones together and 16-byte 454 0:23:51,76 --> 0:23:55,44 ones in to make sure they're all with similar and then all of 455 0:23:55,44 --> 0:23:55,94 the... 456 0:23:56,54 --> 0:23:57,16 Yeah, exactly. 457 0:23:57,16 --> 0:23:58,04 Nikolay: Ah, similar. 458 0:23:59,06 --> 0:24:2,82 Michael: But equally, like, imagine if it's like a users table 459 0:24:3,2 --> 0:24:8,72 and I could put the I could put certain information about that 460 0:24:8,72 --> 0:24:12,08 user that makes sense to go together together and then information 461 0:24:12,22 --> 0:24:15,92 about something like maybe like there all the IDs that maybe 462 0:24:15,92 --> 0:24:19,24 the foreign keys like their team ID or their organization, all 463 0:24:19,24 --> 0:24:22,58 those kind of like referencing columns, put those together. 464 0:24:22,58 --> 0:24:25,78 So I know the order doesn't matter. 465 0:24:25,84 --> 0:24:28,86 I know you could just specify column order, but you know when 466 0:24:28,86 --> 0:24:30,72 you do SELECT * or you just... 467 0:24:30,72 --> 0:24:34,34 Nikolay: This is what I was trying to say, like, are you trying 468 0:24:34,34 --> 0:24:37,06 to tell me that you're using SELECT *? 469 0:24:38,04 --> 0:24:39,1 And here we go. 470 0:24:39,34 --> 0:24:41,28 So I'm joking, actually. 471 0:24:41,28 --> 0:24:43,16 I'm using SELECT * all the time. 472 0:24:44,34 --> 0:24:48,54 Also all the time I see articles how bad it is to use SELECT *. 473 0:24:49,24 --> 0:24:49,74 Right? 474 0:24:50,14 --> 0:24:56,5 It's convenient, I know, yeah, but also if you manually work, 475 0:24:56,98 --> 0:25:1,78 it's inconvenient to have order and SELECT * doesn't work well. 476 0:25:1,84 --> 0:25:4,2 But applications shouldn't use SELECT *. 477 0:25:4,2 --> 0:25:4,98 Michael: You're right. 478 0:25:5,08 --> 0:25:8,48 And I'm even thinking about examples like the system views in 479 0:25:8,48 --> 0:25:8,86 Postgres. 480 0:25:8,86 --> 0:25:11,92 Like you do SELECT * FROM pg_stat_statements or something. 481 0:25:12,62 --> 0:25:14,12 But that's not even the table, right? 482 0:25:14,12 --> 0:25:14,92 That's the view. 483 0:25:14,92 --> 0:25:16,4 And then they can define the order. 484 0:25:16,4 --> 0:25:18,3 So it doesn't actually matter. 485 0:25:19,12 --> 0:25:20,52 Nikolay: Yeah, it's an interesting topic. 486 0:25:21,04 --> 0:25:24,66 I played Column Tetris a few times heavily, and then it was really 487 0:25:24,66 --> 0:25:25,16 inconvenient. 488 0:25:25,68 --> 0:25:27,22 Oh, this is an ugly table. 489 0:25:27,6 --> 0:25:28,54 It looks ugly. 490 0:25:28,78 --> 0:25:30,4 Like, this order is ugly. 491 0:25:30,4 --> 0:25:33,34 But at the same time, it's your problem that because you use 492 0:25:33,34 --> 0:25:36,1 SELECT *, which is considered like not good practice. 493 0:25:36,28 --> 0:25:38,58 It's practice for exploring and so on. 494 0:25:39,14 --> 0:25:42,8 Okay, explore with AI, ask to provide better order or something. 495 0:25:42,8 --> 0:25:43,64 I don't know. 496 0:25:44,24 --> 0:25:47,84 But anyway, if you talk about, if you focus on performance, again 497 0:25:47,84 --> 0:25:51,24 I saw cases where it was significant waste. 498 0:25:51,42 --> 0:25:53,86 Michael: 1 of the first articles I saw and it was by Braintree 499 0:25:53,86 --> 0:25:57,34 and I think they reported an average of about 10% on some of 500 0:25:57,34 --> 0:25:58,44 their largest tables. 501 0:25:58,44 --> 0:25:59,0 Nikolay: It depends. 502 0:25:59,76 --> 0:26:2,48 Michael: Obviously it completely depends but that was quite surprising 503 0:26:2,48 --> 0:26:2,68 to me. 504 0:26:2,68 --> 0:26:5,5 I know you mentioned an extreme case where you saw 30%, but I 505 0:26:5,5 --> 0:26:9,96 was quite surprised that in an actual production case, it was 506 0:26:9,96 --> 0:26:11,0 as high as 10%. 507 0:26:11,0 --> 0:26:13,68 And when you're talking about hundreds of gigabytes or terabytes, 508 0:26:15,06 --> 0:26:16,36 That's actually a decent amount. 509 0:26:17,32 --> 0:26:18,3 Nikolay: Yeah, yeah, exactly. 510 0:26:18,52 --> 0:26:21,88 The problem also, of course, in existing projects which were 511 0:26:21,88 --> 0:26:25,96 developed during many years, usually it's not so simple because 512 0:26:25,96 --> 0:26:30,32 the schema is evolving during many years and you cannot insert 513 0:26:30,32 --> 0:26:31,94 your new column in the middle. 514 0:26:32,04 --> 0:26:34,22 It's always, as you said, at the end. 515 0:26:34,44 --> 0:26:38,9 So sometimes we do need to, for example, to change the order 516 0:26:39,24 --> 0:26:42,08 using some redesign thing. 517 0:26:42,14 --> 0:26:48,78 By the way, have you heard that Postgres 19 will have repack 518 0:26:48,84 --> 0:26:52,58 and also repack concurrently, which is basically vacuum concurrently. 519 0:26:53,14 --> 0:26:55,4 We had an episode with author of pg_squeeze. 520 0:26:56,2 --> 0:26:58,02 It's a vacuum full concurrently. 521 0:26:58,78 --> 0:26:59,76 Exactly, exactly. 522 0:26:59,76 --> 0:27:1,02 Not vacuum concurrently. 523 0:27:1,02 --> 0:27:4,78 But the decision was made to name it Repack concurrently. 524 0:27:5,02 --> 0:27:7,78 Michael: I think that's sensible, getting away from people thinking 525 0:27:7,78 --> 0:27:9,4 it's vacuum related, yeah. 526 0:27:9,86 --> 0:27:13,94 Nikolay: I haven't looked into details, but naturally this is 527 0:27:13,94 --> 0:27:17,64 the point where you could reorder columns, because it's based 528 0:27:17,64 --> 0:27:18,76 on logical replication. 529 0:27:19,74 --> 0:27:23,16 Michael: Yeah, I suspect if they do that, it will be a different 530 0:27:23,16 --> 0:27:24,1 keyword again. 531 0:27:24,72 --> 0:27:25,46 Nikolay: Who knows? 532 0:27:25,6 --> 0:27:27,38 Again, I haven't looked into details. 533 0:27:27,44 --> 0:27:30,84 There are long discussions on pgsql-hackers mailing list. 534 0:27:31,04 --> 0:27:34,54 It just feels naturally, because it's based on logical replication. 535 0:27:34,54 --> 0:27:37,86 This is where you can reorder columns and play Tetris for existing 536 0:27:37,86 --> 0:27:39,14 large tables. 537 0:27:39,62 --> 0:27:41,78 I hope it will be implemented at some point. 538 0:27:41,78 --> 0:27:43,94 Maybe it's already partially implemented or something. 539 0:27:43,94 --> 0:27:44,74 I don't know. 540 0:27:45,16 --> 0:27:49,54 You basically, before you start filling the new table, you can 541 0:27:49,54 --> 0:27:51,52 reorder it, because it's logical, right? 542 0:27:52,08 --> 0:27:53,22 It should be possible. 543 0:27:53,94 --> 0:27:56,4 Anyway, this is content, it's an entertaining topic. 544 0:27:56,4 --> 0:28:0,68 Let's move on to a more serious topic called indexes, index chaos. 545 0:28:1,34 --> 0:28:6,04 If you just use AI and if you don't spend enough time for things 546 0:28:6,04 --> 0:28:11,3 as I said like experimenting with real data benchmarks you might 547 0:28:11,38 --> 0:28:15,08 end up with under-indexed situation or over-indexed situation 548 0:28:16,06 --> 0:28:18,98 And usually people are scared to have under-indexed. 549 0:28:19,46 --> 0:28:22,16 In my practice, I see over-indexed very often. 550 0:28:22,54 --> 0:28:23,74 Sometimes to extreme. 551 0:28:25,3 --> 0:28:28,3 Michael: I guess for new projects, I would be surprised to see 552 0:28:28,3 --> 0:28:29,16 them over-indexed. 553 0:28:30,08 --> 0:28:30,76 But for- it's 554 0:28:30,76 --> 0:28:32,58 Nikolay: possible, it's possible still. 555 0:28:32,78 --> 0:28:36,1 Michael: Is that like as a result of AI or something else? 556 0:28:36,1 --> 0:28:39,62 Nikolay: Well, the problem here is that, again, everything depends 557 0:28:39,62 --> 0:28:43,38 on your prompts, depends on angles. 558 0:28:43,68 --> 0:28:50,24 If you say we will need to order by arbitrary column, right? 559 0:28:50,34 --> 0:28:53,92 Remember our episode with CTO Gadget, Harry, right? 560 0:28:54,38 --> 0:28:56,42 So any column we can reorder. 561 0:28:57,44 --> 0:29:2,06 Naturally, AI might decide to put index on every column, right? 562 0:29:2,86 --> 0:29:3,84 But is it really 563 0:29:3,84 --> 0:29:4,54 Michael: what you want? 564 0:29:4,54 --> 0:29:7,72 But that's what Gadget decided to do as well, right? 565 0:29:7,8 --> 0:29:11,66 Nikolay: Right, but with understanding consequences, right? 566 0:29:11,98 --> 0:29:17,68 But in reality, will it be used If 1 year later you see that 567 0:29:17,68 --> 0:29:21,58 90% of those indexes are unused and the gadget has special case 568 0:29:21,58 --> 0:29:25,84 They have many applications to support if you design your own 569 0:29:25,84 --> 0:29:29,96 only 1 application This is where I would just stop and think 570 0:29:29,96 --> 0:29:34,3 with AI again with some experiments I would ask questions like 571 0:29:34,3 --> 0:29:36,46 this all depends on workload, right? 572 0:29:36,46 --> 0:29:39,66 Which queries we will have, right? 573 0:29:40,08 --> 0:29:44,34 And I would just think, which queries we really need and which 574 0:29:44,34 --> 0:29:49,5 we won't need, and then choose a proper minimal set of indexes. 575 0:29:51,6 --> 0:29:52,52 Michael: I like that. 576 0:29:52,64 --> 0:29:56,8 I think there's also like some rules of thumb we can give people 577 0:29:56,8 --> 0:30:0,32 in terms of like how many is where it starts to get dangerous 578 0:30:0,32 --> 0:30:1,2 for other reasons. 579 0:30:1,88 --> 0:30:6,72 But the kind of 1 place where it's not even and it depends is 580 0:30:6,96 --> 0:30:11,4 overlapping and I see more and more where people are adding indexes 581 0:30:11,58 --> 0:30:13,24 based on AI suggestions. 582 0:30:13,78 --> 0:30:16,72 They're adding indexes they've effectively already got or they're 583 0:30:16,72 --> 0:30:20,8 adding ones that overlap with indexes they've already got, and 584 0:30:20,8 --> 0:30:23,34 therefore only 1 of them is needed. 585 0:30:24,14 --> 0:30:26,52 And by the way, it won't always show up in your stats, it won't 586 0:30:26,52 --> 0:30:29,8 always show up as an unused index, because it is still using 587 0:30:29,8 --> 0:30:30,92 the smaller index. 588 0:30:31,56 --> 0:30:35,98 Nikolay: It will show up in PostgreSQL stats because it's called 589 0:30:35,98 --> 0:30:39,52 in our case redundant index in Postgres monitoring systems and 590 0:30:39,52 --> 0:30:40,02 CheckUp. 591 0:30:40,46 --> 0:30:42,42 I never heard the term overlapping. 592 0:30:43,38 --> 0:30:46,64 I see people talk about duplicate indexes, which is like trivial 593 0:30:46,64 --> 0:30:48,9 case, exact same definition. 594 0:30:50,28 --> 0:30:52,2 But we call it redundant indexes. 595 0:30:52,2 --> 0:30:57,18 It's not an easy topic as 1 might think, but we solved it in 596 0:30:57,18 --> 0:30:57,8 many parts. 597 0:30:57,8 --> 0:31:3,22 Over years, we have quite a good detection approach, how to reliably 598 0:31:3,58 --> 0:31:5,14 detect redundant indexes. 599 0:31:5,86 --> 0:31:10,64 And as you say, this is true, in many cases all of those indexes 600 0:31:10,64 --> 0:31:11,36 are used. 601 0:31:11,82 --> 0:31:16,56 And it's quite like, there is a big fear, oh, I'm going to drop 602 0:31:16,56 --> 0:31:18,98 this, it's used, should I drop it? 603 0:31:19,28 --> 0:31:23,82 Yeah, but the reports we have quite reliable and of course I 604 0:31:23,82 --> 0:31:27,84 wish we had very native standard algorithm to disable indexes 605 0:31:27,84 --> 0:31:32,32 for quite for some time like without involving HypoPG and hypothetical 606 0:31:32,5 --> 0:31:35,46 indexes, which currently supports it. 607 0:31:35,82 --> 0:31:38,14 Michael: Our last episode was on what's missing in Postgres, 608 0:31:38,14 --> 0:31:41,5 and I think we got a nice comment, I've forgotten who from, but 609 0:31:41,52 --> 0:31:45,26 saying 1 of their features would be make this 610 0:31:45,26 --> 0:31:45,32 Nikolay: invisible. 611 0:31:45,32 --> 0:31:48,26 Temporally hide index from the planner. 612 0:31:48,48 --> 0:31:52,54 There are extensions for it, HypoPG supports it, but we want 613 0:31:52,54 --> 0:31:58,04 some mechanism beyond index setting, and index is valid to false. 614 0:31:58,38 --> 0:31:59,14 We want this. 615 0:31:59,14 --> 0:32:0,66 Yeah, yeah, yeah, I agree. 616 0:32:0,96 --> 0:32:5,14 And this is a good thing to probably work on for those who want 617 0:32:5,24 --> 0:32:9,8 to start hacking Postgres so it's a good thing to officially implement 618 0:32:9,8 --> 0:32:10,3 something. 619 0:32:10,68 --> 0:32:15,94 Anyway, questions to ask is, they all start here, should start 620 0:32:15,94 --> 0:32:17,36 from understanding workload. 621 0:32:17,86 --> 0:32:22,18 So we cannot think about indexes without understanding workload. 622 0:32:22,28 --> 0:32:26,14 So first we need to generate some fake data and then start thinking 623 0:32:26,38 --> 0:32:28,04 about usage patterns. 624 0:32:28,7 --> 0:32:30,92 And without AI It's really hard work. 625 0:32:30,92 --> 0:32:32,06 It's a lot of time. 626 0:32:32,72 --> 0:32:34,8 With AI it becomes easier. 627 0:32:37,0 --> 0:32:38,7 If you have user stories defined already, you understand what 628 0:32:38,7 --> 0:32:42,38 kind of patterns we will have, and you can start imagining. 629 0:32:43,38 --> 0:32:47,78 It will not be 100% accurate, but it will be good enough, much 630 0:32:47,78 --> 0:32:51,82 better than without it or with manual work. 631 0:32:51,82 --> 0:32:56,4 So you can iterate here and see, and then with queries in hand 632 0:32:56,84 --> 0:33:2,04 and some like lab already developed, I see it should take 1 or 633 0:33:2,04 --> 0:33:7,08 2 hours for like medium-sized project I mean in terms of complexity 634 0:33:7,42 --> 0:33:10,58 and then you can start iterating collecting plans and think which 635 0:33:10,58 --> 0:33:12,32 indexes you can have playing 636 0:33:13,02 --> 0:33:15,58 Michael: But also this whilst this might be the most important 637 0:33:15,58 --> 0:33:19,2 in terms of performance that we've talked about so far it's also 638 0:33:19,2 --> 0:33:24,52 the the easiest to change later right we can yeah yeah we can 639 0:33:24,52 --> 0:33:27,88 set up a certain set of indexes and later decide we want a slightly 640 0:33:27,88 --> 0:33:30,92 different set of indexes and that's not super painful in a lot 641 0:33:30,92 --> 0:33:34,54 in most cases especially if there's no partitioning involved 642 0:33:35,28 --> 0:33:40,2 so that for me feels like yeah it's important it's really important 643 0:33:40,58 --> 0:33:44,34 But if we don't get it right on day 1, it's much, much easier 644 0:33:44,34 --> 0:33:47,82 to fix than the primary key being the wrong data type or not 645 0:33:47,82 --> 0:33:50,34 having the right constraints in place, that kind of thing. 646 0:33:50,72 --> 0:33:52,28 Or even the column order, yeah. 647 0:33:52,72 --> 0:33:56,82 Nikolay: So the point is you should build like wind tunnel for 648 0:33:56,82 --> 0:33:58,04 your project, right? 649 0:33:58,86 --> 0:34:1,58 Like, you know, like, Test lab, yeah. 650 0:34:1,86 --> 0:34:2,98 Database lab. 651 0:34:3,42 --> 0:34:7,42 And then you should put this workload like wind and see like 652 0:34:7,42 --> 0:34:11,94 how your profile of your database behaves under this wind. 653 0:34:12,28 --> 0:34:14,84 Michael: Yeah, a lot of people do that with production. 654 0:34:15,14 --> 0:34:17,7 They just do it with their slow queries. 655 0:34:18,94 --> 0:34:22,84 Nikolay: But with AI it's easier to have massive experiments 656 0:34:23,0 --> 0:34:25,06 before you finalize all the decisions. 657 0:34:25,38 --> 0:34:27,28 So my point is do it. 658 0:34:27,28 --> 0:34:30,72 And then questions to ask, are there any indexes we don't need 659 0:34:30,72 --> 0:34:33,9 in terms of they are redundant or unused or what indexes are 660 0:34:33,9 --> 0:34:36,72 missing because we have bad plans and this again like pgMustard 661 0:34:37,2 --> 0:34:40,62 will visualize it and so on and you have API already, right? 662 0:34:40,88 --> 0:34:42,94 Michael: Yeah Yeah, it's quite nice 663 0:34:43,14 --> 0:34:46,3 Nikolay: You can connect your AI to pgMustard for example and 664 0:34:46,3 --> 0:34:49,44 ask to use pgMustard to explain what's happening and find bad 665 0:34:49,44 --> 0:34:53,5 problems and missing indexes with this lab environment. 666 0:34:54,64 --> 0:34:55,12 Great? 667 0:34:55,12 --> 0:34:55,78 Michael: Yeah, OK. 668 0:34:57,1 --> 0:35:1,72 Going just like to the extreme, How many indexes on a table would 669 0:35:1,72 --> 0:35:5,7 you automatically just think, oh wow, that's like a lot? 670 0:35:6,22 --> 0:35:10,54 Maybe it's justified, but I'm already thinking that's too many. 671 0:35:10,92 --> 0:35:13,34 Nikolay: These rule of thumbs are quite weak in my opinion, but 672 0:35:13,34 --> 0:35:14,44 we have them anyway. 673 0:35:14,54 --> 0:35:17,44 Like for example, if the volume of indexes exceeds the volume 674 0:35:17,44 --> 0:35:20,88 of data, it's already some bell is ringing. 675 0:35:23,22 --> 0:35:27,72 Or before Postgres 18 with this nasty lightweight lock manager 676 0:35:27,72 --> 0:35:31,22 problem means I don't want more than 15 indexes per table. 677 0:35:31,96 --> 0:35:34,62 Michael: Because of the 16 relation limit. 678 0:35:34,94 --> 0:35:38,76 Nikolay: So simple, primary key lookup which quite likely will 679 0:35:38,76 --> 0:35:45,22 be very, will have quite high QPS will suffer, and because of 680 0:35:45,22 --> 0:35:45,72 locking. 681 0:35:46,26 --> 0:35:49,7 So there are a couple of kind of these rules, right, but they 682 0:35:49,7 --> 0:35:54,64 are not strict and again I say that I consider them weak, but 683 0:35:54,72 --> 0:35:55,22 maybe... 684 0:35:55,44 --> 0:35:57,78 Michael: But I think it's still helpful, right, if you've got 685 0:35:57,78 --> 0:36:2,56 a schema designed by AI and it's got 20 indexes on 1 table, maybe 686 0:36:2,56 --> 0:36:4,18 consider is that smart? 687 0:36:6,8 --> 0:36:11,3 Nikolay: But it happens, index data exceeding heap data it happens. 688 0:36:12,88 --> 0:36:15,6 Michael: Especially with some, especially like if you're doing 689 0:36:15,6 --> 0:36:16,34 gin indexes. 690 0:36:16,64 --> 0:36:17,34 Nikolay: Oh yes. 691 0:36:17,4 --> 0:36:19,54 Michael: If it's just B-trees, yeah. 692 0:36:20,38 --> 0:36:22,46 Nikolay: Yeah, okay, great. 693 0:36:22,54 --> 0:36:25,88 So the final topic, we couldn't decide on which 1 to choose. 694 0:36:25,88 --> 0:36:27,6 We had 2 ideas. 695 0:36:27,8 --> 0:36:30,7 1 is RLS and another is partitioning. 696 0:36:32,4 --> 0:36:33,6 Let's touch both maybe. 697 0:36:33,6 --> 0:36:34,4 Michael: Yeah I think so. 698 0:36:34,4 --> 0:36:39,3 So from an AI perspective, like when you're asking it for a new 699 0:36:39,3 --> 0:36:43,3 schema, do you see partitioning come up? 700 0:36:43,38 --> 0:36:44,4 Does it over partition? 701 0:36:44,4 --> 0:36:48,64 Nikolay: It won't come up Unless you start saying I'm going to 702 0:36:48,64 --> 0:36:54,72 have a lot of data here and I want good performance, I want billions 703 0:36:54,72 --> 0:36:58,04 of rows stored in this table, it should be maintainable. 704 0:36:58,26 --> 0:37:1,3 It will naturally come to the idea of having partitioning. 705 0:37:3,26 --> 0:37:7,32 Partitioning will be interesting to join with UUID version 7 or 706 0:37:7,36 --> 0:37:8,9 8, it doesn't matter. 707 0:37:8,92 --> 0:37:13,12 It's possible, again, I have a recipe for this in my set of how-tos. 708 0:37:14,06 --> 0:37:18,46 And it can be also the question, like partitioning you cannot 709 0:37:18,46 --> 0:37:20,96 develop without understanding workload again. 710 0:37:21,26 --> 0:37:24,62 It's similar to indexes, like it will be something, but how it 711 0:37:24,62 --> 0:37:25,84 will behave, who knows? 712 0:37:25,84 --> 0:37:29,36 Maybe you will end up, your queries will need to scan all partitions, 713 0:37:29,38 --> 0:37:31,36 which is terrible in most cases. 714 0:37:32,1 --> 0:37:34,24 And then plan behavior. 715 0:37:34,46 --> 0:37:37,7 Anyway, the question is to ask, like you said requirements, I 716 0:37:37,7 --> 0:37:41,94 want a lot of rows and I want well maintainability of this. 717 0:37:43,08 --> 0:37:43,82 Help me. 718 0:37:43,94 --> 0:37:46,2 Michael: Like deleting old data, are you thinking? 719 0:37:46,72 --> 0:37:49,04 Nikolay: No, creating indexes, for example, or reindexing. 720 0:37:49,12 --> 0:37:50,02 Michael: OK, yeah, sure. 721 0:37:50,02 --> 0:37:50,52 Vacuum. 722 0:37:50,9 --> 0:37:51,9 Nikolay: Vacuum, yeah, yeah. 723 0:37:51,9 --> 0:37:56,72 These are problems biting us much more often and badly than just, 724 0:37:56,72 --> 0:37:59,76 I don't know, like, we talk about this a lot, as well, right? 725 0:37:59,76 --> 0:38:0,7 Michael: I know, I know. 726 0:38:0,7 --> 0:38:1,2 Nikolay: Yeah. 727 0:38:2,38 --> 0:38:5,98 Direct performance benefits from partitioning are good, but also 728 0:38:5,98 --> 0:38:13,32 the problem when creation of index or full table vacuum, it will 729 0:38:13,32 --> 0:38:17,22 take hours or half a day, it's already so painful. 730 0:38:17,24 --> 0:38:22,4 Especially if it's to prevent transaction wraparound, or if it's, 731 0:38:22,7 --> 0:38:25,16 again, indexing means that xmin horizon... 732 0:38:25,2 --> 0:38:27,42 Like anyway, let's not go too deep. 733 0:38:27,44 --> 0:38:31,12 The question to ask, will we survive n number of rows because 734 0:38:31,12 --> 0:38:32,46 we expect all the data. 735 0:38:33,84 --> 0:38:34,52 I want- 736 0:38:34,54 --> 0:38:38,96 Michael: And just out of interest, is it pretty much always doing 737 0:38:38,96 --> 0:38:41,7 range partitioning based on timestamp type? 738 0:38:41,77 --> 0:38:42,52 Is that 739 0:38:42,52 --> 0:38:44,56 Nikolay: like- Again, I'm the wrong person to ask. 740 0:38:44,56 --> 0:38:45,1 Michael: Okay, yeah, fair 741 0:38:45,1 --> 0:38:45,6 Nikolay: enough. 742 0:38:45,86 --> 0:38:49,74 In many cases, I just use to still use TimescaleDB if it's time-based. 743 0:38:50,74 --> 0:38:51,54 Michael: Makes sense. 744 0:38:52,74 --> 0:38:55,16 Nikolay: And I'm happy because there is compression there, it's 745 0:38:55,16 --> 0:38:55,66 great. 746 0:38:55,9 --> 0:39:0,08 But I also saw cases when it was decided, not AI, but it was 747 0:39:0,08 --> 0:39:3,06 decided, like this partitioning and full control over partitions, 748 0:39:3,42 --> 0:39:4,7 special table created. 749 0:39:4,9 --> 0:39:8,86 It was also working well and served needs, but on surface, you 750 0:39:8,86 --> 0:39:13,02 should think about workload and then push your AI or something 751 0:39:13,04 --> 0:39:17,06 to think about how it will behave if you have that much data. 752 0:39:17,96 --> 0:39:19,86 Partitioning is inevitable there. 753 0:39:19,86 --> 0:39:21,14 And then experiments again. 754 0:39:21,28 --> 0:39:24,24 Even experiment with 1000000000 rows, it won't take a lot, just 755 0:39:24,24 --> 0:39:25,84 allocate machine and do it. 756 0:39:25,84 --> 0:39:28,78 It's like just so easy, right? 757 0:39:28,86 --> 0:39:33,04 Half an hour of wait to generate it, or more like even if it's 758 0:39:33,04 --> 0:39:36,0 1 hour why I don't understand why people don't do it all the 759 0:39:36,0 --> 0:39:40,58 time They come to they come to us with questions, which I'm grateful. 760 0:39:40,58 --> 0:39:44,84 Thank you with money and so on But it's so easy just to experiment 761 0:39:44,86 --> 0:39:46,2 more often these days 762 0:39:47,32 --> 0:39:50,0 Michael: Yeah, even when it even leave it running overnight 763 0:39:51,0 --> 0:39:51,5 Nikolay: Yeah 764 0:39:53,98 --> 0:39:58,02 Michael: Yeah, I'm still I'm still in the fence I love partitioning 765 0:39:58,04 --> 0:40:2,76 for the reasons you mentioned, But I think so many projects won't 766 0:40:2,76 --> 0:40:6,96 ever need it that I understand not doing it by default with a 767 0:40:6,96 --> 0:40:9,92 new project unless you know, unless it's like a new feature for 768 0:40:9,92 --> 0:40:12,5 an existing system that you know is going to get heavy amounts 769 0:40:12,5 --> 0:40:13,76 of data really quickly. 770 0:40:14,1 --> 0:40:17,22 Just there are so many trade-offs like that it's still a little 771 0:40:17,22 --> 0:40:17,64 bit like... 772 0:40:17,64 --> 0:40:18,4 Nikolay: Which trade-offs? 773 0:40:20,18 --> 0:40:24,18 Michael: So for example indexes, being able to create indexes 774 0:40:24,18 --> 0:40:26,9 concurrently, delete, drop indexes concurrently. 775 0:40:27,98 --> 0:40:30,72 Nikolay: You can concurrently create index on each partition 776 0:40:30,76 --> 0:40:34,04 and then yeah it exists on each partition you can create this 777 0:40:34,04 --> 0:40:39,44 these things can be automated and of course I like to be living 778 0:40:39,44 --> 0:40:42,94 much more better automated there is a big potential here but 779 0:40:42,94 --> 0:40:47,78 also every Every Postgres version gets a lot of improvements 780 0:40:47,86 --> 0:40:50,1 in this area, last maybe 10 years already. 781 0:40:50,14 --> 0:40:53,94 So it's not like frozen. 782 0:40:54,66 --> 0:40:56,4 Yeah, but also make like, 783 0:40:57,34 --> 0:41:1,12 Michael: just being extra careful that every high frequency query 784 0:41:1,12 --> 0:41:6,3 you have contains the partition key and is getting pruned at 785 0:41:6,6 --> 0:41:9,5 planning time like there are a few gotchas 786 0:41:9,64 --> 0:41:12,18 Nikolay: that hit you worse. 787 0:41:12,34 --> 0:41:12,5332 Michael: Yeah there are, 788 0:41:12,5332 --> 0:41:13,98 Nikolay: I have a series of articles about this and I went quite 789 0:41:13,98 --> 0:41:15,92 deep there and it was crazy yeah. 790 0:41:16,42 --> 0:41:18,16 Michael: But that's my, they're my hesitations. 791 0:41:19,2 --> 0:41:19,7 Nikolay: Creation 792 0:41:19,76 --> 0:41:20,72 Michael: of foreign keys. 793 0:41:22,42 --> 0:41:23,04 Nikolay: That's why I'm hesitant for example working with timescale 794 0:41:23,04 --> 0:41:27,44 timescale DB something like often we just Abandoned idea of having 795 0:41:27,44 --> 0:41:31,72 foreign key because yeah, it's important If you do partitioning 796 0:41:31,72 --> 0:41:35,8 yourself, you will deal with, if you have large tables, adding 797 0:41:35,8 --> 0:41:37,16 foreign key between them. 798 0:41:37,96 --> 0:41:41,06 If partitioning is involved, it's an art, I would say. 799 0:41:41,14 --> 0:41:42,52 And GitLab mastered it. 800 0:41:42,52 --> 0:41:45,08 They have Migration Helpers RB. 801 0:41:45,48 --> 0:41:49,94 This is a great source of experience of many people involved 802 0:41:49,94 --> 0:41:53,44 and I can't stop recommending how great it is because it's open 803 0:41:53,44 --> 0:41:53,94 source. 804 0:41:54,38 --> 0:41:57,62 It's a great place to look at and also they have great documentation 805 0:41:57,88 --> 0:41:58,58 about it. 806 0:41:58,58 --> 0:42:1,56 But anyway, it's a thing to decide. 807 0:42:1,56 --> 0:42:5,1 If you don't expect billions of rows, don't do it, of course. 808 0:42:6,66 --> 0:42:11,26 At the same time, we have also very sad cases when people come 809 0:42:11,26 --> 0:42:12,6 to us for consulting. 810 0:42:12,7 --> 0:42:15,6 We identify problems, we say partitioning is needed. 811 0:42:16,22 --> 0:42:19,74 Sometimes we go and spend a lot of effort because the project 812 0:42:19,74 --> 0:42:23,32 is huge or there are many databases to take care of. 813 0:42:23,5 --> 0:42:26,5 And then finally it's implemented great. 814 0:42:27,94 --> 0:42:31,26 There are many hidden dangers to explore. 815 0:42:31,62 --> 0:42:36,04 Mid-journey, for example, they came to us, helped, forgot about 816 0:42:36,04 --> 0:42:39,14 lightweight lock, well, not forgot, we didn't know about that 817 0:42:39,14 --> 0:42:42,78 at that time, and they hit it, lightweight LockManager problem 818 0:42:43,28 --> 0:42:47,72 was hit badly, and there are articles about it. 819 0:42:48,1 --> 0:42:51,6 But then some projects just say, okay, they evaluate how much 820 0:42:51,6 --> 0:42:54,78 effort it is to deal with partitioning when tables are huge, 821 0:42:55,12 --> 0:42:58,06 and start talking, okay, maybe we should migrate to my MongoDB. 822 0:42:59,72 --> 0:43:1,82 Michael: Yeah, Or even sharding, right? 823 0:43:1,82 --> 0:43:6,82 Like instead, I think when we talked to Notion, Arka, he mentioned 824 0:43:6,82 --> 0:43:10,44 that because they keep their shards relatively small, they've 825 0:43:10,44 --> 0:43:12,68 opted to never partition at all. 826 0:43:12,74 --> 0:43:14,48 Nikolay: Yeah, the need for partitioning diminishes. 827 0:43:15,04 --> 0:43:17,86 I agree with this, but not vanishes completely. 828 0:43:18,96 --> 0:43:19,78 Michael: What, maybe? 829 0:43:20,34 --> 0:43:20,64 Nikolay: Not 830 0:43:20,64 --> 0:43:21,14 Michael: maybe. 831 0:43:21,28 --> 0:43:25,22 You mentioned, it depends how small you keep your shards, right? 832 0:43:26,12 --> 0:43:27,68 Nikolay: Yeah, yeah. 833 0:43:28,08 --> 0:43:30,52 Michael: Like Sugu, for example, Multigres, he talked about 834 0:43:30,52 --> 0:43:33,3 having lots of smaller databases being much easier. 835 0:43:33,4 --> 0:43:34,42 Nikolay: Yeah, yeah, yeah, yeah. 836 0:43:34,64 --> 0:43:35,56 Maybe you're right. 837 0:43:36,34 --> 0:43:36,5 Michael: Okay. 838 0:43:36,5 --> 0:43:37,4 What about RLS? 839 0:43:37,54 --> 0:43:38,72 You mentioned you wanted to touch on 840 0:43:38,72 --> 0:43:39,22 Nikolay: that. 841 0:43:39,38 --> 0:43:43,68 Yeah, finally, RLS, if you, for example, I just noticed it's 842 0:43:43,68 --> 0:43:47,44 not like Supabase is very heavily promoting RLS. 843 0:43:47,44 --> 0:43:51,02 They have a lot of articles including how to avoid problems. 844 0:43:51,22 --> 0:43:56,6 But if you just use AI, I noticed it often adds RLS. 845 0:43:57,18 --> 0:43:57,9 Michael: Oh, really? 846 0:43:58,26 --> 0:43:59,16 Nikolay: Yeah, I just... 847 0:43:59,16 --> 0:43:59,66 Interesting. 848 0:44:0,06 --> 0:44:0,92 This is how it works. 849 0:44:0,92 --> 0:44:4,86 I just noticed I had a couple of cases where suddenly it brought 850 0:44:4,9 --> 0:44:6,9 like, we are going to use RLS. 851 0:44:6,9 --> 0:44:8,74 It's multi-tenancy here, let's do it. 852 0:44:8,74 --> 0:44:12,58 Okay, let's do it, but let's also pause and think about performance 853 0:44:13,38 --> 0:44:19,24 and avoid problems like current_setting inside a loop and you 854 0:44:19,54 --> 0:44:23,2 SELECT count of million rows suddenly becomes super slow. 855 0:44:23,42 --> 0:44:28,4 So the question to ask AI, again, benchmark is ideal here. 856 0:44:30,06 --> 0:44:34,06 With RLS, without RLS, what is the overhead of having RLS in 857 0:44:34,06 --> 0:44:34,92 our particular case? 858 0:44:34,92 --> 0:44:36,18 Let's benchmark it. 859 0:44:36,22 --> 0:44:39,72 And if you see it's not okay, then let's optimize it, because 860 0:44:39,72 --> 0:44:41,82 there are tricks how to optimize it. 861 0:44:43,66 --> 0:44:48,16 So if I notice an AI-generated schema with RLS, I would ask question 862 0:44:48,16 --> 0:44:50,14 like, what will be performance impact? 863 0:44:50,98 --> 0:44:53,74 Let's benchmark it, and if it's bad, let's optimize. 864 0:44:53,8 --> 0:44:54,38 That's it. 865 0:44:54,38 --> 0:44:54,88 Yeah. 866 0:44:55,84 --> 0:44:56,34 Great. 867 0:44:57,22 --> 0:44:57,72 Great. 868 0:44:58,44 --> 0:44:59,32 That's it, actually. 869 0:44:59,38 --> 0:44:59,64 I think 870 0:44:59,64 --> 0:45:1,2 Michael: that's a useful checklist, actually. 871 0:45:1,2 --> 0:45:5,72 Nikolay: 5 or 6 things to ask your AI when you design schema. 872 0:45:7,66 --> 0:45:9,3 Michael: Yeah or your colleague even. 873 0:45:10,24 --> 0:45:13,08 Nikolay: Yeah also or your own schema which you have already 874 0:45:13,08 --> 0:45:14,06 10 years, why not. 875 0:45:14,06 --> 0:45:16,96 Michael: Yeah exactly or even a new table if you're doing a new 876 0:45:16,96 --> 0:45:19,96 feature it still makes sense that all of these make sense. 877 0:45:20,58 --> 0:45:21,24 Nikolay: I agree. 878 0:45:21,42 --> 0:45:23,5 Cool, thank you for listening. 879 0:45:24,16 --> 0:45:25,9 Yeah, see you soon.