1 00:00:00,060 --> 00:00:02,480 Michael: Hello and welcome to Postgres.FM, a weekly show about 2 00:00:02,480 --> 00:00:03,380 all things PostgreSQL. 3 00:00:03,560 --> 00:00:06,580 I am Michael, founder of pgMustard, and this is my co-host Nikolay, 4 00:00:06,580 --> 00:00:07,900 founder of Postgres.AI. 5 00:00:07,900 --> 00:00:10,020 Hey Nikolay, what are we talking about today? 6 00:00:10,860 --> 00:00:14,180 Nikolay: Hi Michael, we are talking about some article which 7 00:00:14,720 --> 00:00:15,920 attracted my attention. 8 00:00:17,380 --> 00:00:20,540 Michael: And I hope not just the article, because it was the article 9 00:00:20,540 --> 00:00:24,740 was about best practices, and I'm I'm hoping to discuss that. 10 00:00:24,740 --> 00:00:30,520 Nikolay: Yeah I always like like 15 mistakes, 25 recipes, this 11 00:00:30,520 --> 00:00:31,960 kind of titles. 12 00:00:32,640 --> 00:00:33,140 Michael: Clickbait. 13 00:00:34,020 --> 00:00:36,780 Nikolay: Yeah and honestly I chose it like let's just choose 14 00:00:36,780 --> 00:00:41,760 this article, and it attracted my attention just by title. 15 00:00:42,660 --> 00:00:44,880 7 crucial Postgres best practices. 16 00:00:45,300 --> 00:00:46,560 Good title, right? 17 00:00:47,300 --> 00:00:50,380 Michael: Yeah, and we haven't had an episode called best practices. 18 00:00:50,380 --> 00:00:53,760 So it's an interesting, it's a interesting omission on our part. 19 00:00:53,760 --> 00:00:57,940 Nikolay: Really? Yeah. Yeah, okay, best practices. 20 00:00:58,740 --> 00:01:01,720 We have something like mistakes to avoid maybe, right? 21 00:01:02,520 --> 00:01:04,040 Michael: Oh, maybe, yeah. 22 00:01:04,040 --> 00:01:08,040 Nikolay: From my experience being program committee of various 23 00:01:08,140 --> 00:01:13,440 conferences and just general conference experience, people love 24 00:01:13,440 --> 00:01:14,600 to hear about mistakes. 25 00:01:14,600 --> 00:01:17,680 They also love to hear pieces of advice, but mistakes we somehow 26 00:01:17,680 --> 00:01:20,300 love more because it's like things to avoid. 27 00:01:21,180 --> 00:01:23,980 And yeah, and best practices. 28 00:01:24,180 --> 00:01:25,640 What does it mean best practices? 29 00:01:26,640 --> 00:01:29,100 Do this, not that, right? 30 00:01:30,100 --> 00:01:32,060 Michael: Yeah, I've been thinking about this. 31 00:01:32,300 --> 00:01:35,140 What's the difference between best practices and good practices? 32 00:01:36,160 --> 00:01:41,720 Like, I think it's like shorthand for if I'm new to this and 33 00:01:41,720 --> 00:01:44,540 I don't really know what I'm doing, what are some things I can 34 00:01:44,540 --> 00:01:45,960 do to not look stupid? 35 00:01:46,300 --> 00:01:49,760 Or to, you know, to at least cover what people generally say 36 00:01:49,760 --> 00:01:54,340 is a good idea because in anything complex, especially databases, 37 00:01:55,840 --> 00:01:59,540 we know often it depends, like it's going to depend on exactly 38 00:01:59,540 --> 00:02:02,220 what you're doing as to whether you should do X or Y. 39 00:02:02,220 --> 00:02:07,120 So I think these lists and these suggestions are almost fraught 40 00:02:07,120 --> 00:02:10,520 with danger from the beginning, but I think they can be useful 41 00:02:10,520 --> 00:02:11,020 educationally. 42 00:02:11,380 --> 00:02:14,760 Like, they can be useful for people to say, in general, this 43 00:02:14,760 --> 00:02:15,740 is a good idea. 44 00:02:15,920 --> 00:02:17,820 In general, this is a bad idea. 45 00:02:17,960 --> 00:02:20,400 Nikolay: You could be a great consultant, you know. 46 00:02:22,860 --> 00:02:24,620 Because you sound like one. 47 00:02:24,940 --> 00:02:25,440 Michael: Yeah. 48 00:02:25,760 --> 00:02:28,220 Well, what do you think best practices mean? 49 00:02:28,220 --> 00:02:31,660 Nikolay: For me, it's not about avoiding to be, to look stupid. 50 00:02:32,400 --> 00:02:37,580 It's about, I like the word shortcut, like to quickly find what 51 00:02:37,580 --> 00:02:42,640 other people already experienced and choose maybe not best, but 52 00:02:42,640 --> 00:02:47,480 something better than you would choose on your own, right? 53 00:02:48,140 --> 00:02:48,640 So. 54 00:02:48,660 --> 00:02:49,760 Michael: That's a good point, actually. 55 00:02:49,760 --> 00:02:52,260 Not so much about looking cheaper, but maybe to avoid things 56 00:02:52,260 --> 00:02:52,960 going wrong. 57 00:02:52,960 --> 00:02:54,810 Like to avoid the most common traps. 58 00:02:54,810 --> 00:02:54,840 Right. 59 00:02:54,840 --> 00:02:54,900 The goal 60 00:02:54,900 --> 00:02:56,340 Nikolay: is not about how we look. 61 00:02:57,540 --> 00:03:00,700 The goal is not to look bad when making decisions, engineering 62 00:03:00,700 --> 00:03:04,400 decisions around polygons, but the goal is to make database work 63 00:03:04,400 --> 00:03:09,140 better, more reliably, more like better performance, avoid data 64 00:03:09,140 --> 00:03:11,720 loss, avoid downtime, these kind of things. 65 00:03:11,720 --> 00:03:16,900 Actually 3, well, correctness is 1 of the goals, but naturally 66 00:03:16,960 --> 00:03:20,640 Postgres has very good, in most cases, if we don't touch the 67 00:03:20,640 --> 00:03:21,840 null topic, right? 68 00:03:21,900 --> 00:03:22,860 Null topic. 69 00:03:23,100 --> 00:03:26,100 In this case, if we don't touch the null topic, most of the time 70 00:03:26,100 --> 00:03:30,140 Postgres works in a very correct way and predictable. 71 00:03:31,120 --> 00:03:33,660 I expect this, I got what expected. 72 00:03:34,080 --> 00:03:39,880 And so in general, 3 areas, big areas are good performance, reliability, 73 00:03:41,360 --> 00:03:47,980 and well, avoidance of data losses, big fear of database guys, 74 00:03:47,980 --> 00:03:48,420 right? 75 00:03:48,420 --> 00:03:52,000 And also no downtime or very short downtime. 76 00:03:52,860 --> 00:03:53,660 Michael: Yeah, perfect. 77 00:03:54,020 --> 00:03:54,980 Nikolay: Big 3 areas. 78 00:03:55,440 --> 00:03:56,920 Let's dive into this article. 79 00:03:56,920 --> 00:04:03,280 I know we both are not thinking it's super great material. 80 00:04:04,460 --> 00:04:05,340 Michael: Yeah, sadly. 81 00:04:05,460 --> 00:04:06,300 Nikolay: Let's be honest. 82 00:04:07,120 --> 00:04:09,100 Well, it's an attempt. 83 00:04:09,960 --> 00:04:14,640 Michael: It looks to me like somebody has asked their favorite 84 00:04:14,640 --> 00:04:19,120 LLM for a list of Postgres best practices and almost copy and 85 00:04:19,120 --> 00:04:21,840 pasted it verbatim into an article. 86 00:04:22,360 --> 00:04:25,120 That's what it looks like to me without much review. 87 00:04:25,120 --> 00:04:28,740 Nikolay: Well maybe it's written by LLM, yeah I'm not sure. 88 00:04:28,740 --> 00:04:33,800 The author name is Zak and I think it's good that the good attempt 89 00:04:33,800 --> 00:04:36,600 is made to bring this again. 90 00:04:36,700 --> 00:04:39,120 And actually it was on Hacker News Top, I guess. 91 00:04:39,240 --> 00:04:41,900 That's how it caught my attention. 92 00:04:43,140 --> 00:04:45,860 It's a good attempt, but I agree with you some parts. 93 00:04:45,860 --> 00:04:47,740 Maybe it's written purely manually. 94 00:04:48,480 --> 00:04:49,400 It's possible. 95 00:04:49,440 --> 00:04:57,080 But I would like to just reflect on the fields covered, 7 fields. 96 00:04:58,260 --> 00:05:01,780 From the beginning, I don't agree that we have 7 practices here. 97 00:05:01,780 --> 00:05:05,020 We have 7 fields covered and inside each field we have some practices 98 00:05:05,020 --> 00:05:05,520 mentioned. 99 00:05:06,040 --> 00:05:11,120 And you also mentioned that you think there should be more fields 100 00:05:11,120 --> 00:05:14,840 covered if we try to come to have this like kind of digest of 101 00:05:14,960 --> 00:05:16,520 tips, right? 102 00:05:18,580 --> 00:05:24,640 But let's touch, my idea, let's touch every one of these seven, bring 103 00:05:24,640 --> 00:05:33,620 up to three our own tips we should, like, should be noticed, like, 104 00:05:33,620 --> 00:05:35,240 involved into consideration. 105 00:05:35,780 --> 00:05:40,360 And then in the end, I'm very curious what you think is missing 106 00:05:40,360 --> 00:05:42,180 in terms of the fields, the areas. 107 00:05:42,520 --> 00:05:43,020 Right? 108 00:05:43,500 --> 00:05:45,200 So, deal. 109 00:05:45,200 --> 00:05:45,620 Okay. 110 00:05:45,620 --> 00:05:50,640 First, the first field is database design and discussion is about 111 00:05:51,040 --> 00:05:54,140 naming conventions and so on. What what you think? 112 00:05:54,140 --> 00:05:58,720 What's your number one take when we think about how to make database 113 00:05:58,740 --> 00:05:59,680 design good? 114 00:05:59,680 --> 00:06:01,900 What what makes you good? 115 00:06:03,540 --> 00:06:06,300 Michael: Yes, I mean I'm surprised it starts with this. 116 00:06:06,300 --> 00:06:09,960 I actually saw some of the Hacker News comments and it's an odd 117 00:06:09,960 --> 00:06:14,820 choice of where to start but I do see a lot of people making 118 00:06:14,820 --> 00:06:19,780 the mistake of using spaces in their names for objects or using 119 00:06:19,820 --> 00:06:23,620 mixed case and things and technically if they followed this advice 120 00:06:23,620 --> 00:06:27,340 they wouldn't do that so I do see people making the opposite 121 00:06:27,340 --> 00:06:33,400 mistake so yeah I don't think it's bad advice and generally people 122 00:06:33,400 --> 00:06:34,340 do have conventions. 123 00:06:34,540 --> 00:06:37,100 I actually follow a lot of the ones that are listed here. 124 00:06:37,400 --> 00:06:38,560 So yeah, not bad. 125 00:06:38,560 --> 00:06:39,400 How about you? 126 00:06:40,080 --> 00:06:43,320 Nikolay: Well, first of all, I think it's not a mistake if you... 127 00:06:44,060 --> 00:06:48,140 My previous startup had camel case table names. 128 00:06:48,140 --> 00:06:51,940 It was inherited and I understand it very well that it's not 129 00:06:51,940 --> 00:06:52,680 super convenient. 130 00:06:52,760 --> 00:06:55,180 I left it as is because it was intentional. 131 00:06:56,660 --> 00:07:01,460 I also think one step back, database design being the first topic, 132 00:07:01,480 --> 00:07:05,520 and then performance will be the next topic and leaving reliability 133 00:07:05,800 --> 00:07:08,440 like backups and HA later. 134 00:07:08,560 --> 00:07:12,420 It just shows a tendency that probably this article and this 135 00:07:12,500 --> 00:07:17,340 set of tips are targeting more backend engineers, developers, 136 00:07:17,620 --> 00:07:22,760 programmers, or those who create some systems rather than purely 137 00:07:22,760 --> 00:07:23,680 database folks. 138 00:07:23,680 --> 00:07:24,920 And it's totally fine. 139 00:07:24,920 --> 00:07:28,380 And also, like, if we take managed service, managed Postgres, 140 00:07:28,380 --> 00:07:32,720 like RDS or something, The important areas like backups HA are 141 00:07:32,720 --> 00:07:35,280 presumably solved already. 142 00:07:37,250 --> 00:07:43,900 So we have opportunity to focus on things we deal with every 143 00:07:43,900 --> 00:07:46,160 day, like database design, performance. 144 00:07:46,160 --> 00:07:47,840 These things are closer to us. 145 00:07:47,840 --> 00:07:50,300 I can understand this choice of order. 146 00:07:51,580 --> 00:07:55,020 If we talk about database design, I think the main idea is to 147 00:07:55,020 --> 00:07:55,960 have some convention. 148 00:07:56,940 --> 00:08:01,240 It can be even camel case with spaces, but you understand you 149 00:08:01,240 --> 00:08:05,580 will be forced to use double quotes all the time, everywhere. 150 00:08:06,280 --> 00:08:09,560 But if it's choice, well, it's inconvenient. 151 00:08:09,880 --> 00:08:11,260 I know I was there. 152 00:08:11,680 --> 00:08:16,160 And I was there after a couple of other startups where I was, 153 00:08:17,220 --> 00:08:23,560 how to say, like, very, very, like, polishing every symbol, you 154 00:08:23,560 --> 00:08:24,060 know. 155 00:08:24,920 --> 00:08:26,540 I think this is what matters here. 156 00:08:26,600 --> 00:08:31,040 Like, convention is important for yourself, for people who work 157 00:08:31,040 --> 00:08:32,260 with you also, right? 158 00:08:32,400 --> 00:08:35,100 And for LLMs who work with you as well. 159 00:08:36,760 --> 00:08:37,840 Michael: But it's a good point. 160 00:08:37,840 --> 00:08:40,780 It doesn't really matter why is it number 1 in this list. 161 00:08:42,040 --> 00:08:42,760 Like, yeah. 162 00:08:43,440 --> 00:08:47,640 And there's also no mention in database design and schema design, 163 00:08:48,340 --> 00:08:51,580 there's no mention of normalization, which is a bit odd. 164 00:08:52,780 --> 00:08:56,880 Nikolay: Well, let's shift to what we think matters. 165 00:08:58,080 --> 00:09:06,160 So you say convention and also we need to normalize the tables. 166 00:09:06,580 --> 00:09:07,620 I tend to agree. 167 00:09:08,460 --> 00:09:14,900 And also, if you think about convention, I love also to think 168 00:09:14,900 --> 00:09:19,740 not only about how we name database objects, but also how we 169 00:09:19,740 --> 00:09:20,500 write SQL. 170 00:09:20,500 --> 00:09:25,640 I'm a big fan of Mozilla SQL Style Guide, which is closer to 171 00:09:25,640 --> 00:09:31,560 normal programming languages and moving away from this central 172 00:09:32,380 --> 00:09:35,860 space colon, right? 173 00:09:35,860 --> 00:09:40,820 How to say like central space thing like in... 174 00:09:41,000 --> 00:09:41,280 We all... 175 00:09:41,280 --> 00:09:42,420 Michael: You mean like an underscore? 176 00:09:43,100 --> 00:09:45,840 Nikolay: No, not only, so SELECT space blah, blah, then you're 177 00:09:45,840 --> 00:09:47,720 from and you need to move from. 178 00:09:47,720 --> 00:09:52,320 So you maintain the central space vertical line. 179 00:09:52,960 --> 00:09:53,650 Michael: Oh, alignment. 180 00:09:53,650 --> 00:09:54,640 Nikolay: Alignment of spaces. 181 00:09:54,960 --> 00:09:56,020 Alignment, yes. 182 00:09:56,840 --> 00:10:00,700 I honestly, I hate it so much. 183 00:10:01,840 --> 00:10:06,900 I know it's old-fashioned approach and many books follow this 184 00:10:06,900 --> 00:10:11,180 rule, but it's so inconvenient, guys. 185 00:10:11,480 --> 00:10:16,940 Just take Mozilla SQL Style Guide, publish it inside your company 186 00:10:17,640 --> 00:10:22,280 wiki or documents, like any confluence or something, what you 187 00:10:22,280 --> 00:10:22,780 have. 188 00:10:23,000 --> 00:10:25,460 Adjust it for your flavor a little bit and that's it. 189 00:10:25,460 --> 00:10:27,780 And agree, okay, we are following this convention. 190 00:10:28,260 --> 00:10:34,600 As for normalization, I guess let's mention the episode we had 191 00:10:34,600 --> 00:10:39,480 a couple of weeks ago, 3 weeks ago maybe, with Franck Pachot, about 192 00:10:39,480 --> 00:10:40,900 NoSQL versus SQL. 193 00:10:40,920 --> 00:10:43,780 And we talked about normalization, denormalization quite a lot 194 00:10:43,780 --> 00:10:44,060 there. 195 00:10:44,060 --> 00:10:48,160 I agree this is a very important topic to agree on and follow 196 00:10:48,160 --> 00:10:48,660 this. 197 00:10:49,160 --> 00:10:55,220 Well, also, for me, I think constraints are super important. 198 00:10:55,320 --> 00:11:00,780 Like, I would say, I would put this as a big, like, it's a best 199 00:11:00,780 --> 00:11:05,040 practice to use database constraints, all kinds of constraints, 200 00:11:05,080 --> 00:11:08,640 understanding the power of them, and also understanding overhead 201 00:11:08,720 --> 00:11:09,400 as well. 202 00:11:09,560 --> 00:11:13,420 Michael: Yeah, I think it's good default, like if in doubt, add 203 00:11:13,420 --> 00:11:17,160 the constraint, and you might want to remove it in future if 204 00:11:17,160 --> 00:11:20,220 you get to a certain scale, but I like the idea of having it 205 00:11:20,220 --> 00:11:20,940 by default. 206 00:11:21,580 --> 00:11:24,140 Nikolay: For example, we discussed in the same episode with Franck, 207 00:11:24,140 --> 00:11:28,020 we discussed maybe we should have not NULL constraint by default. 208 00:11:28,380 --> 00:11:32,060 Not that, unfortunately, it's not so usually in all systems. 209 00:11:33,060 --> 00:11:39,020 But we can agree, let's put it by default and lift it only if 210 00:11:39,020 --> 00:11:42,000 we know NULLs are going to be useful. 211 00:11:42,440 --> 00:11:46,080 So yeah, that's it, I guess 3 tips, enough, right? 212 00:11:46,900 --> 00:11:49,740 Michael: And what about schema namespacing do you ever do you 213 00:11:49,740 --> 00:11:50,860 use it oh 214 00:11:50,860 --> 00:11:56,180 Nikolay: it's interesting I I'm I'm shifting back and forth and 215 00:11:56,180 --> 00:12:01,960 in my career use namespaces schemas or not use them depends on 216 00:12:01,960 --> 00:12:06,500 the project here I am like with you it depends if it's 217 00:12:06,820 --> 00:12:08,900 Michael: Also it doesn't matter that much, right? 218 00:12:10,380 --> 00:12:13,620 Nikolay: Well, it does, because it's about convenience as well. 219 00:12:13,620 --> 00:12:17,660 If you think about others, they can be spending a lot of time 220 00:12:17,660 --> 00:12:18,920 trying to find your table. 221 00:12:20,200 --> 00:12:26,100 Again, consistency here is the key, because if you chose to start 222 00:12:26,100 --> 00:12:32,280 using namespaces, schemas, suddenly after a few years of development, 223 00:12:32,360 --> 00:12:35,100 and the majority of your tables are in the main... 224 00:12:35,380 --> 00:12:36,680 And it happens all the time. 225 00:12:36,680 --> 00:12:37,960 Like, I see it all the time. 226 00:12:37,960 --> 00:12:38,340 Yeah, sure. 227 00:12:38,340 --> 00:12:41,760 The majority of the tables are in public schema, but suddenly 228 00:12:41,760 --> 00:12:48,560 you start using some additional schemas and hide tables there 229 00:12:48,560 --> 00:12:49,060 basically. 230 00:12:49,960 --> 00:12:55,020 It's maintainability as well, for example you can control permissions 231 00:12:55,240 --> 00:13:00,360 better and it also sometimes namespaces are serving some trick 232 00:13:00,360 --> 00:13:04,000 like you can have multiple tables with the same name sitting 233 00:13:04,000 --> 00:13:10,260 in different schemas and but this can go this can lead to some 234 00:13:10,260 --> 00:13:15,400 messy state and problems yeah so but I would say this is part 235 00:13:15,400 --> 00:13:20,980 of conventional agreements you how we use schemas so yeah sometimes 236 00:13:20,980 --> 00:13:23,860 people choose you know like choose this is good topic by the 237 00:13:23,860 --> 00:13:26,500 way for maybe 1 day we should discuss it. 238 00:13:27,390 --> 00:13:34,380 Multi-tenant applications and architectures and how to create 239 00:13:34,500 --> 00:13:37,480 multi-tenancy with good properties in database. 240 00:13:38,240 --> 00:13:41,820 I just recently learned that if you go to Timescale cloud, you 241 00:13:42,180 --> 00:13:46,360 cannot run create database and you cannot have multiple logical 242 00:13:46,360 --> 00:13:49,520 databases in a single server. 243 00:13:50,540 --> 00:13:54,020 So in single Postgres, usually we are allowed to have multiple 244 00:13:54,020 --> 00:13:54,940 logical databases. 245 00:13:54,960 --> 00:13:58,180 And their recipe for multi-tenancy, they have an article about 246 00:13:58,180 --> 00:14:01,580 this, like, let's use schemas and just that's it. 247 00:14:02,040 --> 00:14:06,640 But for me, schema is a very weak wall between data. 248 00:14:06,820 --> 00:14:11,980 Logical database is a big wall between data because you cannot 249 00:14:13,260 --> 00:14:16,100 read from another database being connected to 1 database. 250 00:14:16,100 --> 00:14:18,900 You cannot read from another database not using something like 251 00:14:18,900 --> 00:14:20,700 postgres_fdw or dblink. 252 00:14:21,600 --> 00:14:23,300 Because this is how Postgres works. 253 00:14:23,340 --> 00:14:27,680 Database name is a part of connection properties, the core connection 254 00:14:27,720 --> 00:14:28,220 properties. 255 00:14:29,140 --> 00:14:31,040 Unlike schema, schema is kind of... 256 00:14:32,320 --> 00:14:33,280 It's something... 257 00:14:34,660 --> 00:14:35,160 Michael: Namespace. 258 00:14:36,060 --> 00:14:36,920 Nikolay: Well, yeah. 259 00:14:37,660 --> 00:14:41,640 It's namespace, and you can revoke rights to use some schema, 260 00:14:41,640 --> 00:14:44,880 it's good, but still it's inside the same database. 261 00:14:45,040 --> 00:14:51,480 So if you messed up with permissions, you can end up having security 262 00:14:51,480 --> 00:14:51,980 issues. 263 00:14:53,000 --> 00:14:57,440 If you want strict segregation of data, separation of data for 264 00:14:57,440 --> 00:14:58,300 your tenants. 265 00:14:59,600 --> 00:15:01,820 Okay, This is a different topic. 266 00:15:02,220 --> 00:15:06,220 I think quite good, like we have at least let's move on to the 267 00:15:06,220 --> 00:15:08,340 performance topic, the second topic, right? 268 00:15:11,540 --> 00:15:16,740 Michael: So, they talk about indexing and they talk about query 269 00:15:16,740 --> 00:15:17,240 optimization. 270 00:15:18,000 --> 00:15:21,820 I think, right, we've in the past talked about a couple of things, 271 00:15:21,820 --> 00:15:25,440 like micro optimizations and macro optimizations. 272 00:15:26,380 --> 00:15:31,380 And I think we could maybe, like, think in those terms instead. 273 00:15:31,500 --> 00:15:34,820 I do think it's obviously performance is a lot about indexing. 274 00:15:36,200 --> 00:15:38,040 And about individual query optimization. 275 00:15:38,180 --> 00:15:42,080 But I do think there's some best practices around macro optimization. 276 00:15:42,520 --> 00:15:46,540 Like in Postgres, by default, we don't have pg_stat_statements 277 00:15:46,560 --> 00:15:47,300 turned on. 278 00:15:47,360 --> 00:15:50,580 That feels to me like a pretty much a best practice that we should 279 00:15:50,580 --> 00:15:54,120 probably have pg_stat_statements on or some or an extension for 280 00:15:54,120 --> 00:15:58,380 doing that analysis that's on the on the wider system level. 281 00:15:58,820 --> 00:16:03,080 Maybe some logging like I think there's some performance logging 282 00:16:03,080 --> 00:16:04,040 of slow queries. 283 00:16:04,540 --> 00:16:06,680 Nikolay: Just choose just 1 tip right now. 284 00:16:06,760 --> 00:16:07,840 It's already too many. 285 00:16:07,840 --> 00:16:08,920 Michael: Oh, A, best practice. 286 00:16:08,920 --> 00:16:09,560 All right. 287 00:16:10,840 --> 00:16:11,180 Yeah, best practice. 288 00:16:11,180 --> 00:16:15,680 I would say, well, I like their first 1, I guess. 289 00:16:16,200 --> 00:16:16,940 Use indexes. 290 00:16:19,300 --> 00:16:23,720 Nikolay: Yeah, use indexes is obviously, but yeah, I think my 291 00:16:23,720 --> 00:16:29,240 take would be don't leave autovacuum untuned and don't postpone 292 00:16:29,440 --> 00:16:30,280 bloat control. 293 00:16:30,480 --> 00:16:32,720 This is how Postgres works right now, right? 294 00:16:32,980 --> 00:16:38,600 So we must be aware of what bloat is, what dead tuples are, and 295 00:16:38,600 --> 00:16:39,820 distinguish them actually. 296 00:16:41,360 --> 00:16:43,140 And... 297 00:16:43,140 --> 00:16:44,360 Michael: They cover that in maintenance. 298 00:16:45,540 --> 00:16:46,040 Yeah. 299 00:16:46,080 --> 00:16:46,580 Nikolay: Yeah. 300 00:16:46,980 --> 00:16:54,060 And this is what nobody do it well among managed Postgres platforms 301 00:16:54,060 --> 00:16:54,740 right now. 302 00:16:54,760 --> 00:16:55,260 Nobody. 303 00:16:55,940 --> 00:16:58,080 I mean, they don't give you good tools. 304 00:16:58,080 --> 00:17:04,360 Some of them have basic autovacuum tuning, right? 305 00:17:04,760 --> 00:17:04,903 And that's it. 306 00:17:04,903 --> 00:17:05,045 Basic autovacuum tuning. 307 00:17:05,045 --> 00:17:05,640 And that's it. 308 00:17:06,540 --> 00:17:12,180 Some of them provide pg_repack, and also have it indexed concurrently, 309 00:17:12,340 --> 00:17:13,300 but that's it. 310 00:17:14,340 --> 00:17:18,620 Obviously, I see future where more platforms will provide more, 311 00:17:19,240 --> 00:17:21,780 better tools to help you in this area. 312 00:17:21,780 --> 00:17:26,940 But so, knowing that right now it's on your shoulders when you 313 00:17:26,940 --> 00:17:30,780 build some system, this should be not postponed, right? 314 00:17:30,940 --> 00:17:33,280 Index is obvious, like everyone knows index. 315 00:17:33,920 --> 00:17:34,940 Let's create indexes. 316 00:17:36,600 --> 00:17:41,920 Michael: But 2 important things that aren't mentioned here are 317 00:17:41,920 --> 00:17:46,200 do it with concurrently and don't index everything. 318 00:17:46,880 --> 00:17:50,060 We've done a whole episode on over-indexing, but I feel like 319 00:17:50,060 --> 00:17:54,900 a whole beginner category of issues are caused by folks creating 320 00:17:54,940 --> 00:17:56,340 indexes everywhere 321 00:17:57,380 --> 00:18:02,080 Nikolay: yeah I agree and well there's There are dozens of tips 322 00:18:02,080 --> 00:18:04,700 we can put there around indexing. 323 00:18:05,460 --> 00:18:07,360 Michael: Yeah, you said only a couple, right? 324 00:18:08,000 --> 00:18:10,520 Nikolay: Yeah, only a couple, but you already named more than 325 00:18:10,520 --> 00:18:11,020 5. 326 00:18:11,880 --> 00:18:13,020 Pre-registered statements. 327 00:18:13,260 --> 00:18:13,940 I agree. 328 00:18:13,980 --> 00:18:17,560 While we are all sitting here and waiting until hackers decide 329 00:18:17,560 --> 00:18:23,900 to move this extension to core, we need to keep this tip active. 330 00:18:24,480 --> 00:18:27,540 And register statements must be installed in any database. 331 00:18:28,000 --> 00:18:32,320 Understanding some very edge case or maybe corner case overhead 332 00:18:32,420 --> 00:18:35,005 we also talked about in the past. 333 00:18:35,005 --> 00:18:35,460 Yeah. 334 00:18:35,460 --> 00:18:35,960 Good. 335 00:18:36,740 --> 00:18:37,820 Yeah, okay, good. 336 00:18:37,820 --> 00:18:40,740 And that's it maybe, right? 337 00:18:40,860 --> 00:18:42,520 Well, performance is a huge topic. 338 00:18:42,520 --> 00:18:43,420 We love it. 339 00:18:44,060 --> 00:18:46,220 Yeah, we had many episodes about performance. 340 00:18:48,480 --> 00:18:56,680 But again, autovacuum tuning, learn indexes, many best practices. 341 00:18:57,700 --> 00:18:59,660 And the predecessor sentence must be installed. 342 00:19:00,720 --> 00:19:02,620 Agree on these 3 maybe, right? 343 00:19:03,480 --> 00:19:03,980 Good. 344 00:19:04,280 --> 00:19:04,780 Good. 345 00:19:04,960 --> 00:19:05,460 Good. 346 00:19:05,860 --> 00:19:10,780 So next field, again, the list is far from being complete. 347 00:19:11,040 --> 00:19:13,100 It's just like the tip of the iceberg. 348 00:19:16,580 --> 00:19:18,720 So next security, suddenly. 349 00:19:20,240 --> 00:19:26,340 I would say make sure you don't have Postgres open 350 00:19:29,900 --> 00:19:30,680 Michael: to the world. 351 00:19:33,180 --> 00:19:35,940 That's the main mistake I see people making. 352 00:19:36,200 --> 00:19:36,700 Yeah 353 00:19:37,400 --> 00:19:38,000 Nikolay: What else 354 00:19:38,000 --> 00:19:41,320 Michael: but but obviously if you if you're using certain like 355 00:19:41,320 --> 00:19:46,500 managed services, that's on My default in a lot of cases so then 356 00:19:46,500 --> 00:19:48,640 it becomes well of having good 357 00:19:49,400 --> 00:19:53,900 Nikolay: Right, they open it to public, but protected, maybe, 358 00:19:53,900 --> 00:19:54,340 right? 359 00:19:54,340 --> 00:19:55,740 Michael: Yes, but... 360 00:19:57,440 --> 00:19:58,160 Nikolay: But protected how? 361 00:19:58,820 --> 00:20:01,660 Michael: Then it becomes about good password policies and things. 362 00:20:02,240 --> 00:20:03,280 Nikolay: Right, right, right. 363 00:20:03,280 --> 00:20:06,820 But this started with Heroku, I think. 364 00:20:06,820 --> 00:20:11,660 They also have Postgres open to public and security by obscurity 365 00:20:11,880 --> 00:20:15,940 when hostname is a very large hash. 366 00:20:16,100 --> 00:20:16,600 Yeah. 367 00:20:16,720 --> 00:20:18,000 And username also. 368 00:20:18,540 --> 00:20:23,540 Everything is like large hash, but it's weak security. 369 00:20:24,340 --> 00:20:31,880 But the password is strong, and if it's not MD5, but Scram, it's 370 00:20:31,880 --> 00:20:33,100 something better already. 371 00:20:33,520 --> 00:20:36,400 But I agree, if you're a serious project, this should not be 372 00:20:36,940 --> 00:20:37,640 the case. 373 00:20:38,140 --> 00:20:40,260 A port should not be available to public. 374 00:20:40,260 --> 00:20:43,520 I think it should be only like local IP address and available 375 00:20:43,520 --> 00:20:48,360 only internally in networks VP Yeah, so Good. 376 00:20:48,360 --> 00:20:50,560 Michael: I'm saying Are you saying it depends? 377 00:20:51,820 --> 00:20:55,360 Nikolay: I'm saying that I'm not against like I can understand 378 00:20:55,400 --> 00:21:02,360 people who have well platforms who have it open to public for 379 00:21:02,500 --> 00:21:04,940 because sometimes we want to start very quickly. 380 00:21:06,160 --> 00:21:09,640 And at the same time, I would not allow this in a serious project 381 00:21:11,680 --> 00:21:15,180 with data of customers, PII, and so on. 382 00:21:15,180 --> 00:21:20,020 I would not allow this at all, to let this port to be open, even 383 00:21:20,020 --> 00:21:24,900 if the password is absolutely difficult to find. 384 00:21:26,120 --> 00:21:31,600 For example, we have 2 factors of authentication everywhere, 385 00:21:32,460 --> 00:21:37,200 but here we have just 1 password well what about if it's leaked 386 00:21:37,360 --> 00:21:37,860 right 387 00:21:38,840 --> 00:21:42,080 Michael: yeah this is this is honestly the difficult thing about 388 00:21:42,080 --> 00:21:47,880 security best practices is because Best practices with security 389 00:21:48,160 --> 00:21:51,520 means do everything you possibly can to secure everything. 390 00:21:52,360 --> 00:21:55,540 And that's just often not practical. 391 00:21:56,460 --> 00:21:57,440 Like at what cost? 392 00:21:57,440 --> 00:21:59,680 At what, you know, what are you trading off? 393 00:21:59,680 --> 00:22:04,360 Often you do trade off things for better security, like as you 394 00:22:04,360 --> 00:22:08,660 were saying, like quickness of getting started or developer experience, 395 00:22:08,680 --> 00:22:09,780 like ease of use. 396 00:22:10,240 --> 00:22:14,140 So there's like a, I do think it's a tricky topic to give best 397 00:22:14,140 --> 00:22:17,080 practices on because you don't ever want to say do something 398 00:22:17,080 --> 00:22:20,460 that's slightly less secure because it has these other benefits 399 00:22:20,460 --> 00:22:23,800 because it's it's not a it's not a best practice anymore. 400 00:22:24,960 --> 00:22:28,520 Nikolay: Yeah it also makes me think we could we could have bad 401 00:22:28,520 --> 00:22:32,440 pieces of advice episode you know we could have it. 402 00:22:32,440 --> 00:22:33,900 Yeah we did like we 403 00:22:33,900 --> 00:22:35,620 Michael: did go through there's a wiki. 404 00:22:36,260 --> 00:22:37,280 Nikolay: Don't do this. 405 00:22:38,300 --> 00:22:45,600 My next advice, next tip for security is divide and conquer. 406 00:22:46,100 --> 00:22:50,040 So and unfortunately, many teams don't follow this. 407 00:22:50,200 --> 00:22:54,220 So use different users for different parts of workload. 408 00:22:54,400 --> 00:22:59,360 And for example, humans should have different usernames in database. 409 00:22:59,680 --> 00:23:05,320 So we can distinguish them quickly and probably limit differently 410 00:23:05,380 --> 00:23:10,940 and so on and parts of application also it's it's wise to to 411 00:23:11,000 --> 00:23:13,980 avoid using the single user name for everything yeah 412 00:23:15,260 --> 00:23:16,220 Michael: I really like 413 00:23:16,220 --> 00:23:16,960 Nikolay: yeah I 414 00:23:16,960 --> 00:23:19,520 Michael: really like that about humans as well. 415 00:23:19,900 --> 00:23:23,560 I mean, we don't like to think about it, but if you were to need 416 00:23:23,560 --> 00:23:27,280 to remove somebody from a project, it's much easier to do that 417 00:23:27,280 --> 00:23:30,300 at the user level if they've got their own user than it is to 418 00:23:30,300 --> 00:23:34,440 roll out and, you know, to think about what did they have access 419 00:23:34,440 --> 00:23:37,580 to, which passwords did they know that were team-wide or something 420 00:23:37,580 --> 00:23:38,280 like that. 421 00:23:38,500 --> 00:23:39,100 So yeah. 422 00:23:39,100 --> 00:23:43,320 I think I've heard of a similar view, in fact I think this article 423 00:23:43,320 --> 00:23:47,040 mentions it, but there's a general concept of principle of least 424 00:23:47,040 --> 00:23:47,540 privilege. 425 00:23:48,080 --> 00:23:48,600 Nikolay: Yeah, yeah, yeah. 426 00:23:48,600 --> 00:23:51,220 Well, this is super important principle, of course. 427 00:23:51,220 --> 00:23:52,980 And it just, it should be a habit. 428 00:23:53,600 --> 00:23:54,960 It's hard to enforce it. 429 00:23:54,960 --> 00:23:59,280 It should be a habit of all the team members to follow this principle. 430 00:24:00,040 --> 00:24:01,560 Michael: But it is also painful, right? 431 00:24:01,560 --> 00:24:05,720 Like, it is painful because it means anytime you add anything, 432 00:24:05,740 --> 00:24:09,240 you then need to add permissions to it, like from all of the 433 00:24:09,240 --> 00:24:09,740 things. 434 00:24:09,960 --> 00:24:11,660 I get that it's good for security. 435 00:24:11,780 --> 00:24:14,340 Nikolay: Default privileges and Postgres, the concept of default 436 00:24:14,340 --> 00:24:17,060 privileges, sometimes you can define them as well. 437 00:24:17,720 --> 00:24:18,940 Michael: Is that the least? 438 00:24:20,140 --> 00:24:20,640 Nikolay: Yeah. 439 00:24:21,820 --> 00:24:22,760 Yeah, well, yeah. 440 00:24:23,040 --> 00:24:25,340 Another, maybe, let's do the last tip. 441 00:24:25,460 --> 00:24:28,620 I'm grabbing the ideas from article, but we're trying to unwrap 442 00:24:28,620 --> 00:24:29,380 them here. 443 00:24:31,260 --> 00:24:32,200 Password rotation. 444 00:24:33,260 --> 00:24:34,280 Michael: Oh, I hate it. 445 00:24:34,280 --> 00:24:36,000 I've written yuck about that. 446 00:24:36,620 --> 00:24:38,300 Nikolay: Well nobody does it actually. 447 00:24:38,320 --> 00:24:43,460 Well, some people do it but honestly, honestly, it's first of 448 00:24:43,460 --> 00:24:44,440 all it's a pain. 449 00:24:45,860 --> 00:24:51,380 Michael: For me it's a relic of when people used to use the same 450 00:24:51,380 --> 00:24:53,220 passwords across multiple services. 451 00:24:53,740 --> 00:24:57,360 So I think the risk, when I say used to, obviously a lot of people 452 00:24:57,360 --> 00:24:57,700 still do. 453 00:24:57,700 --> 00:24:58,080 You think 454 00:24:58,080 --> 00:24:59,080 Nikolay: it's a bad advice? 455 00:25:00,240 --> 00:25:03,100 Michael: My advice would be make sure all your users are using 456 00:25:04,020 --> 00:25:07,580 Some password generator or password manager so that they have 457 00:25:07,580 --> 00:25:11,360 a unique password per service because then the risk of their 458 00:25:11,360 --> 00:25:14,540 password getting leaked by a different service that then they 459 00:25:14,540 --> 00:25:19,280 can use as an attack vector on your service is 0. 460 00:25:20,140 --> 00:25:24,720 So it's only if you leak the password that it becomes an attack 461 00:25:24,720 --> 00:25:25,220 vector. 462 00:25:25,240 --> 00:25:27,940 Which, you know, do you see where I'm coming from? 463 00:25:28,080 --> 00:25:29,720 Nikolay: Yeah, yeah, yeah, I understand that. 464 00:25:30,480 --> 00:25:33,480 Also, like, yeah, I agree. 465 00:25:33,480 --> 00:25:37,000 I think actually Postgres could have improvements in this area. 466 00:25:37,120 --> 00:25:43,100 For example, you could say, if it would be possible to define 467 00:25:43,540 --> 00:25:46,960 password policies and so on, like, for example, to forbid short 468 00:25:46,960 --> 00:25:50,560 passwords, simple passwords, and Postgres would ensure it's not 469 00:25:50,560 --> 00:25:51,060 happening. 470 00:25:51,180 --> 00:25:54,480 Or also PgBouncer, I don't know, like Postgres first of all. 471 00:25:54,480 --> 00:25:57,720 And maybe password retention could be 1 of the policies if people 472 00:25:57,720 --> 00:25:58,940 choose to use it. 473 00:25:59,140 --> 00:26:00,140 Right, I don't know. 474 00:26:00,210 --> 00:26:04,840 Like, I don't believe in the rules here established and followed 475 00:26:04,840 --> 00:26:05,460 at all. 476 00:26:06,180 --> 00:26:11,040 It's like I just see companies have this principle and nobody 477 00:26:11,040 --> 00:26:13,660 follows this principle and attempts are like hard. 478 00:26:13,660 --> 00:26:15,940 It should be Postgres who enforces this rule. 479 00:26:17,140 --> 00:26:19,360 To like avoid weak passwords, for example. 480 00:26:19,940 --> 00:26:20,640 Michael: Yeah, right. 481 00:26:20,900 --> 00:26:21,400 Nikolay: Yeah. 482 00:26:21,620 --> 00:26:21,900 Okay. 483 00:26:21,900 --> 00:26:22,400 Let's... 484 00:26:22,780 --> 00:26:26,200 Well, the article also mentions RLS, but I think it should be 485 00:26:26,200 --> 00:26:32,780 very connected to performance and to be used with big caution. 486 00:26:33,040 --> 00:26:38,420 So it's a controversial topic to just put it, oh, just let's 487 00:26:38,420 --> 00:26:39,160 use RLS. 488 00:26:39,520 --> 00:26:42,340 Well, yeah, let's check performance additionally. 489 00:26:42,720 --> 00:26:44,540 OK, next, backup and recovery. 490 00:26:44,540 --> 00:26:48,860 If you're on a managed service, well, supposedly it should work 491 00:26:48,860 --> 00:26:49,360 good. 492 00:26:49,920 --> 00:26:52,320 But it depends on the service, on the provider. 493 00:26:52,780 --> 00:26:54,360 Not everyone has good capabilities. 494 00:26:55,260 --> 00:26:58,320 Michael: I would say also a best practice would be to have a 495 00:26:58,320 --> 00:27:02,500 version like to take I know we've talked about the difference 496 00:27:02,500 --> 00:27:06,180 between a backup and a logical backup or a dump, but if you're 497 00:27:06,180 --> 00:27:10,680 on a managed service I would still recommend having a copy on 498 00:27:10,680 --> 00:27:14,560 a period that you're comfortable with off the service. 499 00:27:14,860 --> 00:27:15,360 Yes, 500 00:27:15,720 --> 00:27:16,960 Nikolay: Automatically created. 501 00:27:17,740 --> 00:27:23,120 So diversification of backup locations and some platform can 502 00:27:23,120 --> 00:27:25,960 go down or your account can be stolen. 503 00:27:26,820 --> 00:27:31,540 And you, you must have the backups, maybe not super frequently, 504 00:27:31,560 --> 00:27:35,780 but some backups additionally in secondary provider or location, 505 00:27:36,980 --> 00:27:38,680 like isolated. 506 00:27:39,720 --> 00:27:44,160 It's similar to having backups on the same Server where you work, 507 00:27:44,480 --> 00:27:47,280 but just a different layer. 508 00:27:47,900 --> 00:27:49,460 Same principle, different layer. 509 00:27:49,920 --> 00:27:54,900 If all backups are in the hands of RDS, well, and single account, 510 00:27:54,960 --> 00:27:56,260 well, it's not good. 511 00:27:57,260 --> 00:27:57,760 Yeah. 512 00:27:57,860 --> 00:28:02,520 And unfortunately, if you want, if you are on RDS and you want 513 00:28:02,520 --> 00:28:05,140 to follow this principle, you need to deal with dumps. 514 00:28:05,380 --> 00:28:06,340 Logical backups. 515 00:28:07,800 --> 00:28:10,200 Michael: And it's not unique to them, there are quite a lot of 516 00:28:10,200 --> 00:28:12,260 service providers that that's the only option. 517 00:28:12,260 --> 00:28:12,940 Yeah, yeah. 518 00:28:13,200 --> 00:28:13,780 Nikolay: I agree. 519 00:28:13,780 --> 00:28:15,480 What else about backup and recovery? 520 00:28:16,260 --> 00:28:17,720 Testing sometimes, right? 521 00:28:18,420 --> 00:28:20,900 Michael: Yeah, I guess that is the other big 1, isn't it? 522 00:28:20,900 --> 00:28:23,460 It's not a backup unless you know it could be restored. 523 00:28:24,160 --> 00:28:25,760 Nikolay: Yeah, shorting the backup. 524 00:28:25,760 --> 00:28:28,240 Michael: So some amount of testing that restores. 525 00:28:28,820 --> 00:28:33,460 Nikolay: And I don't think platforms test all your backups. 526 00:28:34,360 --> 00:28:39,160 They test only some of them and who knows, right? 527 00:28:39,160 --> 00:28:43,740 So I think it's a good idea to test even if you trust them and 528 00:28:43,740 --> 00:28:47,120 don't hear bad stories, still have some testing. 529 00:28:47,980 --> 00:28:51,040 Michael: Yeah, what's the age-old saying, like, Trust but verify, 530 00:28:51,040 --> 00:28:51,540 right? 531 00:28:51,740 --> 00:28:53,820 Trust them to do it, but verify it. 532 00:28:53,920 --> 00:28:55,780 Nikolay: Final responsibility is yours. 533 00:28:57,160 --> 00:28:59,880 If you lose everything, well... 534 00:29:01,420 --> 00:29:03,800 Yeah, so what else? 535 00:29:04,540 --> 00:29:05,680 Anything else in this area? 536 00:29:05,680 --> 00:29:06,660 Backup and recovery? 537 00:29:06,820 --> 00:29:08,080 Understand RPO, RTO? 538 00:29:09,400 --> 00:29:11,080 Michael: Yeah, I think discuss that. 539 00:29:11,200 --> 00:29:13,940 RPO, RTO, I think it's about discussions internally. 540 00:29:14,280 --> 00:29:16,080 Like have those discussions. 541 00:29:16,080 --> 00:29:20,680 I think RPO and RTO are really good tools and good things to 542 00:29:20,680 --> 00:29:23,740 define, but more importantly, they're good to enforce. 543 00:29:23,740 --> 00:29:27,780 You've even had the conversation with, you know, senior management 544 00:29:27,780 --> 00:29:30,100 or whoever it is, maybe co-founders. 545 00:29:31,020 --> 00:29:32,320 What are our trade-offs here? 546 00:29:32,320 --> 00:29:36,900 Like, are we willing to pay this much more for, like, a reduced 547 00:29:36,900 --> 00:29:37,320 amount? 548 00:29:37,320 --> 00:29:41,360 Or are we willing to lose a tiny bit of data to bring our costs 549 00:29:41,360 --> 00:29:42,080 right down? 550 00:29:42,180 --> 00:29:45,540 I think those are difficult discussions to have, and not everybody 551 00:29:45,580 --> 00:29:46,560 has the same... 552 00:29:47,220 --> 00:29:49,400 Not everybody would make the same trade-offs there in different 553 00:29:49,400 --> 00:29:49,900 situations. 554 00:29:50,660 --> 00:29:54,660 Nikolay: Let's move on and next, it was number 4, field number 555 00:29:54,660 --> 00:29:54,960 4. 556 00:29:54,960 --> 00:29:58,360 I would jump to field number 7 here, high availability, because 557 00:29:58,660 --> 00:30:04,620 in my opinion backups and like DR, basically, that disaster recovery 558 00:30:04,620 --> 00:30:08,960 and HA, they should consider together as a foundation of good 559 00:30:09,080 --> 00:30:11,760 database system platform or something. 560 00:30:12,080 --> 00:30:13,140 So HA, right? 561 00:30:13,140 --> 00:30:14,400 HA, it's interesting. 562 00:30:14,620 --> 00:30:22,300 If My number 1 advice is avoid systems which are not HA systems 563 00:30:22,300 --> 00:30:25,740 but split-brain systems, like replication manager, avoid it. 564 00:30:26,520 --> 00:30:27,600 Just avoid it. 565 00:30:27,700 --> 00:30:28,620 And I don't know... 566 00:30:28,620 --> 00:30:30,660 Michael: Well, if you're going to say a best practice, maybe 567 00:30:30,660 --> 00:30:31,820 we'll just say Patroni? 568 00:30:32,080 --> 00:30:33,200 Or what would you say? 569 00:30:33,200 --> 00:30:34,140 Nikolay: Yeah, just Patroni. 570 00:30:34,160 --> 00:30:37,380 But you know, right now, CloudNativePG is gaining popularity, 571 00:30:37,380 --> 00:30:38,960 and it's not using Patroni. 572 00:30:40,080 --> 00:30:45,520 So I wrote on Twitter, anyone experienced already split brains 573 00:30:45,580 --> 00:30:46,840 with CloudNativePG? 574 00:30:46,840 --> 00:30:49,060 I'm just wondering, I don't know. 575 00:30:49,660 --> 00:30:54,720 So Patroni is definitely passed a huge path to achieve very good 576 00:30:54,720 --> 00:30:58,580 quality in terms of avoidance of split brains and maintaining 577 00:30:58,860 --> 00:31:04,700 very low downtime in case of primary disappears and so on. 578 00:31:04,780 --> 00:31:05,840 Yeah, this is number 1. 579 00:31:05,840 --> 00:31:08,080 Like I would just if possible choose Patroni. 580 00:31:08,080 --> 00:31:09,020 This is my advice. 581 00:31:09,100 --> 00:31:09,820 What else? 582 00:31:10,260 --> 00:31:11,600 Michael: Or managed service provider. 583 00:31:11,600 --> 00:31:15,320 I think that this is where the big, well all the cloud providers 584 00:31:15,320 --> 00:31:16,300 that offer this. 585 00:31:16,640 --> 00:31:18,980 I'm so glad they handle this for me. 586 00:31:19,010 --> 00:31:24,020 Nikolay: In this case, like I can speak about this openly. 587 00:31:24,480 --> 00:31:29,120 Crunchy Bridge, please don't do backups from primary, physical 588 00:31:29,120 --> 00:31:29,620 backups. 589 00:31:30,040 --> 00:31:30,940 They still do. 590 00:31:31,160 --> 00:31:32,500 You need to fix this. 591 00:31:33,200 --> 00:31:37,760 If you follow us, like right now, full backup, if it's not snapshot, 592 00:31:37,760 --> 00:31:38,980 even if it's not snapshot. 593 00:31:39,480 --> 00:31:46,080 So it should be on from some replica, some physical Standby, 594 00:31:46,560 --> 00:31:48,300 because it's very I/O intensive. 595 00:31:48,480 --> 00:31:51,100 Even cloud snapshots can be I/O intensive. 596 00:31:51,220 --> 00:31:54,660 And of course, wall archiving should be on the primary to minimize 597 00:31:54,920 --> 00:31:57,840 the lag and minimize the RPO. 598 00:31:58,280 --> 00:31:59,940 Improve RPO, right? 599 00:32:00,360 --> 00:32:03,800 But full backup, if you do it from primary, it's huge stress 600 00:32:03,800 --> 00:32:06,840 for the primary, and if it's heavily loaded project, it suffers. 601 00:32:07,500 --> 00:32:10,460 What we recently observed, it was not good. 602 00:32:10,940 --> 00:32:15,360 So advice to backup, if it's under your control, organize backups. 603 00:32:15,360 --> 00:32:20,140 So full backup or Delta backup or snapshot is not done, not on 604 00:32:20,140 --> 00:32:20,780 the primary. 605 00:32:20,800 --> 00:32:21,520 It's possible. 606 00:32:22,280 --> 00:32:25,700 pgBackRest backups, pg_basebackup, they support non-exclusive 607 00:32:26,520 --> 00:32:27,440 backups, right? 608 00:32:27,440 --> 00:32:29,560 So we can do it on replicas. 609 00:32:30,420 --> 00:32:31,020 On the... 610 00:32:31,020 --> 00:32:31,660 Michael: Makes sense. 611 00:32:32,000 --> 00:32:35,280 And if we've got HA in place, the replica's not doing anything, 612 00:32:35,280 --> 00:32:35,640 right? 613 00:32:35,640 --> 00:32:36,980 It's not a read replica. 614 00:32:38,000 --> 00:32:40,120 Nikolay: Well, yeah, it depends here, but I agree. 615 00:32:40,120 --> 00:32:44,760 It's good to do backup from replica, which won't be primary. 616 00:32:44,760 --> 00:32:51,360 So if we talk in terminology of Patroni, it should have no failover 617 00:32:51,560 --> 00:32:52,060 tag. 618 00:32:52,640 --> 00:32:56,540 Probably also no load balance tag, but definitely no failover 619 00:32:56,580 --> 00:32:57,080 tag. 620 00:32:58,260 --> 00:32:59,560 It's Patroni terminology. 621 00:32:59,660 --> 00:33:03,840 You can put the tag on the replica to say no failover, so if 622 00:33:03,840 --> 00:33:07,360 failover needs to happen, Patroni won't choose this replica. 623 00:33:07,740 --> 00:33:08,200 Michael: Got it. 624 00:33:08,200 --> 00:33:08,700 Okay. 625 00:33:09,280 --> 00:33:12,040 I was actually thinking you did want it to be that 1, but yeah, 626 00:33:12,040 --> 00:33:12,540 sure. 627 00:33:12,720 --> 00:33:14,760 Nikolay: And then you do backup from it. 628 00:33:14,760 --> 00:33:16,840 Well, there's another topic. 629 00:33:18,180 --> 00:33:21,880 If you do backups, only full backups, only from single node, 630 00:33:21,880 --> 00:33:24,840 what happens if it has some local corruption and other nodes 631 00:33:24,840 --> 00:33:25,540 are fine? 632 00:33:25,960 --> 00:33:29,880 So all your backups are corrupted, but it's an interesting, already 633 00:33:29,960 --> 00:33:30,840 deeper topic. 634 00:33:33,300 --> 00:33:36,240 Let's talk about HA, we talked about Patroni, what else? 635 00:33:36,420 --> 00:33:38,200 Lag control, right? 636 00:33:38,540 --> 00:33:39,240 Michael: Lag control. 637 00:33:39,240 --> 00:33:40,700 Monitor lag, yeah. 638 00:33:41,760 --> 00:33:46,220 Nikolay: Well, also not monitor, monitor, but also it can be 639 00:33:46,220 --> 00:33:46,720 tuned. 640 00:33:47,140 --> 00:33:48,300 Oh, sure, yeah. 641 00:33:48,620 --> 00:33:53,860 To have a very low lag, and also when you do load balancing it 642 00:33:53,860 --> 00:33:55,440 can be very smart logic. 643 00:33:55,440 --> 00:33:58,220 Many people already implemented it or else implemented it in 644 00:33:58,220 --> 00:33:59,020 core already. 645 00:33:59,340 --> 00:34:03,260 So it can be like if some write happened, this session deals 646 00:34:03,260 --> 00:34:04,940 with primary for some time. 647 00:34:05,380 --> 00:34:08,680 It can be even smarter, right, comparing other sense and so on. 648 00:34:08,680 --> 00:34:13,340 So you have some, it's not strictly about HA, but it's about 649 00:34:13,340 --> 00:34:16,020 load balancing mostly and how we use our replicas. 650 00:34:16,320 --> 00:34:18,540 It's like a dressing topic. 651 00:34:18,760 --> 00:34:20,380 What else about HA? 652 00:34:20,380 --> 00:34:21,900 We don't have a lot of time. 653 00:34:21,900 --> 00:34:22,360 Unfortunately, 654 00:34:22,360 --> 00:34:22,800 Michael: we haven't. 655 00:34:22,800 --> 00:34:24,340 We have a whole episode on it. 656 00:34:24,340 --> 00:34:25,240 Let's link to that. 657 00:34:25,240 --> 00:34:26,320 Nikolay: Yeah, yeah, yeah, yeah. 658 00:34:26,320 --> 00:34:27,440 What else in this area? 659 00:34:27,440 --> 00:34:28,480 Any other tips? 660 00:34:29,540 --> 00:34:30,780 Test failovers, right? 661 00:34:31,160 --> 00:34:31,920 Or switchovers? 662 00:34:32,020 --> 00:34:32,820 Michael: Yeah, true. 663 00:34:32,980 --> 00:34:33,460 Nikolay: Yeah. 664 00:34:33,460 --> 00:34:40,340 I remember I chased RDS guys asking, you have this checkbox HA, 665 00:34:40,440 --> 00:34:45,400 which makes me pay double price for primary, but how can I trust 666 00:34:45,400 --> 00:34:45,710 you? 667 00:34:45,710 --> 00:34:49,540 I mean, I trust you in words, but in action, how can I test it? 668 00:34:49,540 --> 00:34:52,680 And they implemented it, you can test failover, which is great. 669 00:34:52,820 --> 00:34:58,100 So simulate primary failure and see how failover happens, how 670 00:34:58,100 --> 00:35:00,460 your application experiences it, for example. 671 00:35:01,060 --> 00:35:05,040 Important, another tip of advice here is to write applications 672 00:35:05,160 --> 00:35:09,440 so it has retry logic, not to lose writes. 673 00:35:09,960 --> 00:35:13,700 Okay, let's move on, because this is like huge areas. 674 00:35:14,720 --> 00:35:17,060 Each of them are huge areas. 675 00:35:17,660 --> 00:35:18,400 What's left? 676 00:35:18,480 --> 00:35:19,700 Maintenance and monitoring. 677 00:35:19,820 --> 00:35:20,860 Wow, huge area. 678 00:35:20,860 --> 00:35:21,880 Development practices. 679 00:35:22,300 --> 00:35:23,220 And that's it. 680 00:35:23,480 --> 00:35:24,360 Only 2 areas. 681 00:35:24,800 --> 00:35:28,840 And I think it's not possible to pack it into a few minutes. 682 00:35:30,060 --> 00:35:34,340 Let's only maybe use 1 or 2 tips everywhere. 683 00:35:34,600 --> 00:35:38,460 For maintenance and monitoring, we already had a tip to always 684 00:35:38,460 --> 00:35:39,940 install pg_stat_statements. 685 00:35:40,240 --> 00:35:40,960 What else? 686 00:35:41,160 --> 00:35:41,880 Your choice. 687 00:35:43,200 --> 00:35:45,860 Michael: Well, a big mistake I see people doing sometimes is 688 00:35:45,860 --> 00:35:47,380 turning off autovacuum. 689 00:35:47,460 --> 00:35:50,780 And this, the author of this doesn't seem aware of autovacuum 690 00:35:50,820 --> 00:35:55,520 so that that would be my main tip is keep autovacuum on and 691 00:35:55,520 --> 00:35:56,540 tune it I know you 692 00:35:56,540 --> 00:36:00,980 Nikolay: mentioned my tips are 2 of them like bloat control and 693 00:36:00,980 --> 00:36:04,460 index maintenance and we had we had several episodes on these 694 00:36:04,460 --> 00:36:08,180 topics And just these must be implemented in any project which 695 00:36:08,320 --> 00:36:12,660 aims to grow even not to huge numbers, but some moderate numbers. 696 00:36:13,140 --> 00:36:17,180 Okay, and finally, development practices, and then your secret 697 00:36:17,380 --> 00:36:18,460 area as well. 698 00:36:18,480 --> 00:36:20,340 Development practices, what do you think? 699 00:36:20,920 --> 00:36:23,500 Version control, code organization, well? 700 00:36:23,520 --> 00:36:26,520 Michael: Yeah, I mean, version control has been a best practice 701 00:36:26,520 --> 00:36:29,840 for years, but I still think it's harder than it should be. 702 00:36:30,040 --> 00:36:31,860 I know we did a whole episode on this. 703 00:36:32,200 --> 00:36:36,240 But some basic, however you choose to do it, some way of tracking 704 00:36:36,560 --> 00:36:40,600 what changes have you made to your database, some form of version 705 00:36:40,600 --> 00:36:43,460 control is so much better than not having anything. 706 00:36:44,200 --> 00:36:46,580 Nikolay: Yeah, well, my advice is straightforward. 707 00:36:47,220 --> 00:36:52,440 Based on what we do last 5 years, use proper testing during development 708 00:36:53,000 --> 00:36:53,860 and before deployment. 709 00:36:54,060 --> 00:36:57,180 And this testing should involve a lot of data. 710 00:36:58,060 --> 00:37:01,860 So thin clones or database branching are great. 711 00:37:02,540 --> 00:37:06,060 Michael: Or a staging server that has like just some not has 712 00:37:06,060 --> 00:37:07,860 some reasonable amount of data. 713 00:37:07,960 --> 00:37:09,520 Nikolay: Well, if you're alone, yes. 714 00:37:09,520 --> 00:37:13,160 And also, even if you're alone, if you work on 2 tasks separately, 715 00:37:13,180 --> 00:37:15,240 it's still like single staging is not enough. 716 00:37:15,240 --> 00:37:18,900 You need to have, And what if you started doing something and 717 00:37:18,900 --> 00:37:20,900 made everything wrong and data is changed? 718 00:37:20,900 --> 00:37:21,780 How to restart? 719 00:37:22,840 --> 00:37:24,880 So database branching is the key. 720 00:37:25,340 --> 00:37:26,400 Michael: Or best practice. 721 00:37:28,340 --> 00:37:33,480 Nikolay: Yeah, so I recommend in development practices, grow 722 00:37:33,700 --> 00:37:39,920 your level of development methodologies, so database branching 723 00:37:39,920 --> 00:37:44,920 starts to be involved into all development and testing activities. 724 00:37:45,400 --> 00:37:47,620 It will be level up, huge level up. 725 00:37:47,900 --> 00:37:50,040 So this is what we do at Postgres AI. 726 00:37:50,280 --> 00:37:53,440 And okay, what's your secret topic, secret area? 727 00:37:54,520 --> 00:37:57,540 Michael: Not secret, we've discussed it a bunch while we're talking, 728 00:37:57,540 --> 00:38:01,360 but I couldn't believe that config tuning wasn't in, Like, no 729 00:38:01,360 --> 00:38:03,680 mention of changing shared buffers even. 730 00:38:03,680 --> 00:38:08,480 Like, I don't understand how you can talk about best practices 731 00:38:08,480 --> 00:38:11,700 for running Postgres and not talk about changing settings. 732 00:38:11,820 --> 00:38:15,240 Nikolay: Again, if you are a RDS user, I can imagine, like, we 733 00:38:15,240 --> 00:38:19,780 talked about HA, but we talked about backups, and it's very... 734 00:38:20,380 --> 00:38:23,940 In this article, it's very development-focused point of view, 735 00:38:23,940 --> 00:38:26,420 like, developers point of view. 736 00:38:26,820 --> 00:38:31,220 Like, things already solved, and configuration partially also 737 00:38:31,220 --> 00:38:32,440 solved, but we... 738 00:38:32,940 --> 00:38:36,740 We all remember, like, Crunchy Bridge tuned a random page cost 739 00:38:36,740 --> 00:38:39,360 after our complaint. 740 00:38:40,240 --> 00:38:43,280 Michael: They did, but I don't think RDS have yet or most of 741 00:38:43,280 --> 00:38:43,940 the others. 742 00:38:44,140 --> 00:38:47,540 So random page cost is still the default on most major providers. 743 00:38:47,960 --> 00:38:49,700 Nikolay: So many things are still default. 744 00:38:49,740 --> 00:38:52,620 I'm happy I have work doing consulting. 745 00:38:53,860 --> 00:38:55,940 Michael: We help many people still. 746 00:38:57,440 --> 00:39:00,640 I know that could be covered, but it really just wasn't mentioned. 747 00:39:00,700 --> 00:39:03,960 And then the other big 1, I'm sure there are others that have 748 00:39:03,960 --> 00:39:07,020 it's hard to spot like the absence of things right when you go 749 00:39:07,020 --> 00:39:10,200 through something but I couldn't believe I didn't see a single 750 00:39:10,200 --> 00:39:14,860 mention until the conclusion of upgrades of staying even in security 751 00:39:14,860 --> 00:39:18,060 we didn't mention staying up-to-date with the latest minor versions 752 00:39:18,600 --> 00:39:22,120 and in performance we didn't talk about like upgrading to major 753 00:39:22,120 --> 00:39:22,620 versions. 754 00:39:23,440 --> 00:39:27,340 That feels to me like a best practice should be to stay up to 755 00:39:27,340 --> 00:39:27,840 date. 756 00:39:29,220 --> 00:39:30,200 Nikolay: Yeah, yeah I agree. 757 00:39:30,220 --> 00:39:32,940 It's part of our healthy Postgres vision. 758 00:39:33,380 --> 00:39:36,300 Definitely minor upgrades, major upgrades for sure. 759 00:39:36,500 --> 00:39:38,720 It's part of our health check we do. 760 00:39:39,220 --> 00:39:41,320 Michael: Everything that's in your health check in my opinion 761 00:39:41,320 --> 00:39:42,600 should be your best bet. 762 00:39:42,900 --> 00:39:43,400 Yeah. 763 00:39:44,100 --> 00:39:45,040 If that makes sense. 764 00:39:45,420 --> 00:39:48,120 Nikolay: And so also still I always have a feeling that it's 765 00:39:48,120 --> 00:39:51,300 not enough, we need to extend all the time. 766 00:39:52,200 --> 00:39:56,860 Some things go away, like upgrades, we will have, in 2018 they 767 00:39:56,860 --> 00:40:02,120 will have statistics, so this will, this tip will go away, but 768 00:40:02,120 --> 00:40:04,980 so many tips are still coming. 769 00:40:05,120 --> 00:40:09,180 Okay I think it's great like maybe you're not very well structured 770 00:40:09,680 --> 00:40:15,280 but I hope some some people heard some good pieces of advice 771 00:40:15,380 --> 00:40:15,880 today. 772 00:40:16,160 --> 00:40:20,160 Let us know in the comments please or on Twitter or LinkedIn 773 00:40:20,160 --> 00:40:20,660 somewhere. 774 00:40:22,120 --> 00:40:22,620 Good. 775 00:40:22,660 --> 00:40:26,380 And we have document actually, maybe it's a good time to remind 776 00:40:26,380 --> 00:40:31,400 that we have a document with ideas and also if something is missing 777 00:40:31,420 --> 00:40:33,900 and we could dive into some topic. 778 00:40:34,460 --> 00:40:37,680 Michael: Yeah, you mean, so we've got a document that lets people 779 00:40:37,680 --> 00:40:39,860 request or suggest episode ideas. 780 00:40:40,240 --> 00:40:40,380 Nikolay: Right. 781 00:40:40,380 --> 00:40:41,340 Michael: Yeah, topic suggestions. 782 00:40:42,440 --> 00:40:42,940 Nikolay: Yeah. 783 00:40:43,300 --> 00:40:43,652 Good. 784 00:40:43,652 --> 00:40:44,004 Michael: Nice. 785 00:40:44,004 --> 00:40:44,356 Nikolay: Good. 786 00:40:44,356 --> 00:40:45,060 Michael: Thanks, Nikolay. 787 00:40:45,060 --> 00:40:46,260 Catch you next week. 788 00:40:46,380 --> 00:40:46,860 Nikolay: Thank you. 789 00:40:46,860 --> 00:40:47,580 Bye bye.