1 00:00:00,060 --> 00:00:01,820 Nikolay: Hello, hello, Postgres.FM. 2 00:00:01,880 --> 00:00:05,200 My name is Nik, Postgres.AI, and as usual my co-host is 3 00:00:05,200 --> 00:00:06,140 Michael, pgMustard. 4 00:00:06,380 --> 00:00:07,080 Hi, Michael. 5 00:00:07,080 --> 00:00:07,760 Michael: Hi, Nik. 6 00:00:07,760 --> 00:00:08,580 How's it going? 7 00:00:08,640 --> 00:00:09,640 Nikolay: I'm very good. 8 00:00:09,640 --> 00:00:10,460 How are you? 9 00:00:10,840 --> 00:00:12,160 Michael: Yeah, very good also. 10 00:00:12,720 --> 00:00:15,200 Nikolay: It's again this time of the year, right? 11 00:00:15,440 --> 00:00:19,240 When it's obviously the right moment to discuss something. 12 00:00:19,540 --> 00:00:23,180 We make a mystery of it, but people already saw the title. 13 00:00:23,760 --> 00:00:27,240 And I'm pretty sure you won't hide it in the title, right? 14 00:00:27,240 --> 00:00:28,640 This elephant in the room. 15 00:00:28,860 --> 00:00:31,720 Michael: I was going to call the episode Christmas for Postgres 16 00:00:31,720 --> 00:00:32,220 users. 17 00:00:32,420 --> 00:00:33,900 Nikolay: Big box of gifts. 18 00:00:34,640 --> 00:00:35,140 Michael: Exactly. 19 00:00:35,820 --> 00:00:36,320 Nikolay: Cool. 20 00:00:36,460 --> 00:00:38,100 Okay, let's talk about it. 21 00:00:38,100 --> 00:00:41,040 Michael: Well, I think for anybody brand new to Postgres or like 22 00:00:41,040 --> 00:00:43,900 relatively new to the show, we've done these episodes each year 23 00:00:43,900 --> 00:00:46,800 when the major releases come out and Postgres has got a really 24 00:00:46,800 --> 00:00:52,580 good track record for many many years now of doing a single major 25 00:00:52,580 --> 00:00:57,340 release each year around this time of year and that's the only 26 00:00:57,340 --> 00:01:00,580 release in the year that will include like new functionality, 27 00:01:00,700 --> 00:01:05,180 breaking changes, you know very semantic version style major 28 00:01:05,180 --> 00:01:05,680 version. 29 00:01:05,900 --> 00:01:10,240 Nikolay: No it's not same there at all And we have many years 30 00:01:10,240 --> 00:01:12,780 we have a dispute inside my team. 31 00:01:13,180 --> 00:01:14,980 Same there or not same there. 32 00:01:15,180 --> 00:01:19,520 I noticed by the way, Tiger Data, Timescale, they appreciate 33 00:01:19,820 --> 00:01:23,820 Postgres approach, only 2 numbers, not 3, because original SemVar 34 00:01:23,860 --> 00:01:24,940 is 3 numbers. 35 00:01:25,760 --> 00:01:30,200 And 3 numbers, I always have, like I understand the logic behind 36 00:01:30,200 --> 00:01:33,920 3 number versioning, But I like Postgres approach much more. 37 00:01:33,920 --> 00:01:37,620 It's much more simplified and so on. 38 00:01:38,360 --> 00:01:38,760 Michael: Cool. 39 00:01:38,760 --> 00:01:41,960 Well, breaking changes will only ever go into a major version, 40 00:01:41,960 --> 00:01:45,220 but you're right, even the major version, we would still cut 41 00:01:45,220 --> 00:01:47,720 a new major version even if it was only new features, even if 42 00:01:47,720 --> 00:01:49,300 it didn't have breaking changes. 43 00:01:49,440 --> 00:01:50,540 So that's a good point. 44 00:01:50,540 --> 00:01:51,520 So yeah, you're right. 45 00:01:51,580 --> 00:01:52,560 Nikolay: Yeah, yeah, yeah. 46 00:01:52,720 --> 00:01:56,980 It's only 2 numbers, but yeah, major version, minor version. 47 00:01:57,040 --> 00:01:57,540 Simplicity. 48 00:01:58,320 --> 00:01:58,820 Michael: Yes. 49 00:01:59,100 --> 00:02:01,120 Nikolay: And major version changes every year. 50 00:02:01,320 --> 00:02:04,480 Michael: Major version every year, minor versions at least every 51 00:02:04,480 --> 00:02:08,500 quarter, sometimes more often if they need to do quick security 52 00:02:08,640 --> 00:02:09,140 fixes. 53 00:02:09,620 --> 00:02:12,620 So yeah, this is the time of year where we get new features and 54 00:02:12,620 --> 00:02:15,880 the only time of the year that we get new features in core Postgres. 55 00:02:16,640 --> 00:02:21,680 So as such there tend to be a lot of them, so we've got a lot 56 00:02:21,680 --> 00:02:23,860 of things we could be choosing, no chance we're going to talk 57 00:02:23,860 --> 00:02:26,680 about them all today, but the idea is to talk about some of our 58 00:02:26,680 --> 00:02:29,340 favorites, some of the kind of categories of things that are 59 00:02:29,340 --> 00:02:33,600 coming, things that we've been kind of involved with or emotionally 60 00:02:33,600 --> 00:02:37,160 attached to or wanting for many years, all sorts. 61 00:02:37,300 --> 00:02:38,540 Where would you like to start? 62 00:02:38,860 --> 00:02:41,840 Nikolay: Well, first of all, maybe we should just mention the 63 00:02:41,840 --> 00:02:45,020 features which we already discussed in depth. 64 00:02:45,020 --> 00:02:48,160 And we had the whole episode sometimes for some features which 65 00:02:48,160 --> 00:02:49,440 are going to be out. 66 00:02:49,640 --> 00:02:52,780 Right, so for example, skip scans in B-tree, right? 67 00:02:53,440 --> 00:02:54,620 Michael: Yeah, that's a huge one. 68 00:02:54,620 --> 00:02:57,240 And we had a great chat with Peter Geoghegan, didn't we? 69 00:02:57,240 --> 00:02:58,180 All about them. 70 00:02:58,480 --> 00:02:59,200 Nikolay: Right, right. 71 00:02:59,200 --> 00:03:06,300 And the TLDR version of it, it's sometimes index on two columns, 72 00:03:06,300 --> 00:03:06,980 for example. 73 00:03:07,440 --> 00:03:11,580 It will be able to support searches, for example, only on the 74 00:03:11,580 --> 00:03:12,340 second column. 75 00:03:12,740 --> 00:03:18,000 So this rule, we got used to it over time that we must put the 76 00:03:18,000 --> 00:03:22,660 second column on the first place if we need searches or create 77 00:03:22,660 --> 00:03:26,500 additional index only on that column, second column. 78 00:03:26,740 --> 00:03:30,840 Now it's sometimes not true and things become more complicated 79 00:03:31,020 --> 00:03:34,460 when we make decisions on indexing, right? 80 00:03:34,860 --> 00:03:38,240 This is my perception, oh, like it's even more complicated now. 81 00:03:38,480 --> 00:03:41,500 Michael: Yeah, I would add a little bit that it's about the efficiency 82 00:03:41,640 --> 00:03:43,320 of scanning that index as well. 83 00:03:43,320 --> 00:03:48,640 So we could previously scan a full index for things that didn't 84 00:03:48,640 --> 00:03:53,120 have filters on the on the leading columns it just wasn't it 85 00:03:53,120 --> 00:03:56,040 had no chance of being efficient or it's very unlikely to be 86 00:03:56,040 --> 00:04:00,880 efficient depending on the exact case now we can efficiently 87 00:04:01,120 --> 00:04:06,360 do it or Postgres can efficiently scan skipping any equality 88 00:04:06,380 --> 00:04:10,200 like across the first column if the first column doesn't have 89 00:04:10,200 --> 00:04:10,900 high cardinality. 90 00:04:11,540 --> 00:04:14,880 Let's say I think you gave the example a while ago of a Boolean. 91 00:04:15,040 --> 00:04:18,720 So if we had only true and false, we could jump to the true and 92 00:04:18,720 --> 00:04:22,240 check that for the thing we want to filter on, and then skip 93 00:04:22,260 --> 00:04:25,840 the rest of the true values and then jump to the false where 94 00:04:25,840 --> 00:04:27,580 we, and pick up again there. 95 00:04:27,720 --> 00:04:31,400 So it's a much, much more efficient way of being able to scan 96 00:04:31,400 --> 00:04:33,480 indexes that aren't perfect for our query. 97 00:04:33,480 --> 00:04:35,840 Nikolay: For performance it's excellent news. 98 00:04:35,980 --> 00:04:36,480 Excellent. 99 00:04:37,360 --> 00:04:42,280 I'm just thinking about the decision-making process when we have 100 00:04:42,280 --> 00:04:46,240 a lot of tables and complex workload and we need to choose proper 101 00:04:46,240 --> 00:04:47,060 index set. 102 00:04:47,560 --> 00:04:52,040 And I'm already, as you know, I shifted my mind totally to full 103 00:04:52,040 --> 00:04:57,100 automation because of full self-driving idea, full self-driving 104 00:04:57,100 --> 00:04:57,600 Postgres. 105 00:04:58,260 --> 00:05:03,880 And I see several good attempts to create index advisors mechanisms. 106 00:05:04,340 --> 00:05:08,140 1 more is from Konstantin Knizhnik, there is blog post and Neon 107 00:05:08,140 --> 00:05:09,220 published this week. 108 00:05:09,680 --> 00:05:14,020 So and there is also a pganalyze Index Advisor. 109 00:05:14,640 --> 00:05:20,140 I was just thinking how this new feature will affect those tools. 110 00:05:21,140 --> 00:05:21,820 Michael: Interesting to see. 111 00:05:21,820 --> 00:05:25,560 The good news for users though, this is 1 of those features that 112 00:05:25,560 --> 00:05:29,560 if you upgrade you might just see certain queries get faster 113 00:05:29,560 --> 00:05:31,580 without having to add new indexes. 114 00:05:31,640 --> 00:05:36,680 If you haven't thoroughly gone through all of your queries, optimizing 115 00:05:36,680 --> 00:05:39,860 them with perfect indexes already, there's a good chance that 116 00:05:39,860 --> 00:05:43,320 some of your existing queries will better serve queries that 117 00:05:43,320 --> 00:05:43,980 you haven't optimized. 118 00:05:43,980 --> 00:05:46,680 Nikolay: So if in the past we could consider something like this 119 00:05:46,680 --> 00:05:47,420 as a mistake. 120 00:05:47,720 --> 00:05:50,820 Now, like, Postgres is more foggy in this area. 121 00:05:51,100 --> 00:05:51,880 Michael: Much more. 122 00:05:52,060 --> 00:05:52,560 Yeah. 123 00:05:53,100 --> 00:05:53,560 Nikolay: Good, good. 124 00:05:53,560 --> 00:05:55,680 Michael: So yeah, this is 1 of those cool features that I think 125 00:05:55,680 --> 00:05:58,200 loads of people are going to benefit without even necessarily 126 00:05:58,320 --> 00:06:00,460 knowing that they're going to, or... 127 00:06:00,660 --> 00:06:03,940 Nikolay: As usual, my advice remains to verify everything with 128 00:06:03,940 --> 00:06:08,820 real testing, with proper datasets and settings of Postgres Planner 129 00:06:09,140 --> 00:06:12,240 and work_mem, which is not Postgres Planner setting. 130 00:06:13,280 --> 00:06:16,120 And just verify everything and so on. 131 00:06:17,060 --> 00:06:17,100 Cool. 132 00:06:17,100 --> 00:06:17,280 Michael: Testing and 133 00:06:17,280 --> 00:06:18,020 Nikolay: so on. 134 00:06:18,620 --> 00:06:22,560 And maybe, maybe we will be able to drop some indexes additionally 135 00:06:22,640 --> 00:06:24,180 because of this feature, who knows? 136 00:06:25,440 --> 00:06:29,940 It's yet to be discovered, because we don't like a lot of indexes 137 00:06:30,020 --> 00:06:33,480 due to many reasons, and now that 1, like 1 of them will be discussed 138 00:06:33,480 --> 00:06:34,900 later, I'm pretty sure. 139 00:06:36,060 --> 00:06:37,620 What else we already discussed? 140 00:06:38,400 --> 00:06:40,920 Michael: So the 1 we've discussed, I think we've had at least 141 00:06:40,920 --> 00:06:46,800 2, maybe 3 episodes on, was where the BUFFERS should be on. 142 00:06:47,240 --> 00:06:49,120 Nikolay: Ah, okay, I didn't get, now I get. 143 00:06:49,120 --> 00:06:52,320 Yes, this is our favorite, and this is big news for this podcast 144 00:06:52,360 --> 00:06:52,820 actually. 145 00:06:52,820 --> 00:06:53,320 Right. 146 00:06:53,320 --> 00:06:57,100 This is why in January I said this is I think it was committed 147 00:06:57,100 --> 00:07:00,780 very long ago in January or it was already obvious that it's 148 00:07:00,780 --> 00:07:01,820 going to be committed. 149 00:07:02,080 --> 00:07:06,420 So we spent 2 years, actually, this is my position, my point 150 00:07:06,420 --> 00:07:12,240 of view, we spent 2 years advertising for using BUFFERS inside 151 00:07:12,940 --> 00:07:19,580 EXPLAIN when you do EXPLAIN ANALYZE, and finally, I feel like, 152 00:07:20,240 --> 00:07:25,360 I feel slightly being sad because we don't need this anymore. 153 00:07:26,040 --> 00:07:30,320 I actually had t-shirt with this, EXPLAIN (ANALYZE, BUFFERS) with BUFFERS 154 00:07:30,600 --> 00:07:31,740 written in bold. 155 00:07:32,040 --> 00:07:40,740 So yeah, BUFFERS after Postgres 18 hitting your Postgres servers. 156 00:07:41,640 --> 00:07:43,740 Yeah, it's not needed anymore. 157 00:07:43,740 --> 00:07:46,060 You just say EXPLAIN ANALYZE and you get BUFFERS. 158 00:07:47,280 --> 00:07:49,580 Yeah, I had some slight shift. 159 00:07:49,600 --> 00:07:54,280 I know pgMustard website has a recommendation how to properly 160 00:07:54,280 --> 00:07:56,140 collect plans, execution plans. 161 00:07:56,680 --> 00:08:01,620 And I shifted my mind already, focus shifted to SETTINGS, WAL, 162 00:08:01,620 --> 00:08:02,280 and VERBOSE. 163 00:08:03,120 --> 00:08:03,620 Great. 164 00:08:04,200 --> 00:08:04,700 Yeah. 165 00:08:04,820 --> 00:08:09,440 So now, for older Postgres versions, we write EXPLAIN ANALYZE, 166 00:08:09,480 --> 00:08:11,760 BUFFERS, WAL, SETTINGS, VERBOSE. 167 00:08:13,200 --> 00:08:15,040 Then it will be minus 1 word. 168 00:08:15,060 --> 00:08:18,980 But it feels still like there is a room for improvement here. 169 00:08:19,020 --> 00:08:20,420 And we had this discussion. 170 00:08:20,600 --> 00:08:23,800 So let's just refer to it and move on. 171 00:08:23,800 --> 00:08:27,280 I think what I've got confused, because we also had a couple 172 00:08:27,280 --> 00:08:28,700 of episodes about UUID. 173 00:08:30,380 --> 00:08:32,740 Maybe 1 of them was without you actually. 174 00:08:32,920 --> 00:08:33,580 Michael: It was. 175 00:08:33,940 --> 00:08:37,400 Nikolay: Yeah, this work uuidv7 started a couple of years 176 00:08:37,400 --> 00:08:41,880 ago when Kirk Wolak joined Postgres hacking sessions where Andrey 177 00:08:41,880 --> 00:08:46,020 Borodin and I tried to cook something online on YouTube channel, 178 00:08:46,100 --> 00:08:47,780 Postgres TV YouTube channel. 179 00:08:48,180 --> 00:08:52,460 And he joined with idea, let's implement ULID originally. 180 00:08:53,360 --> 00:08:57,620 Then it shifted to uuidv7, but then we needed to wait. 181 00:08:57,620 --> 00:09:01,060 I mean, Postgres project needed to wait for quite some time because 182 00:09:01,360 --> 00:09:05,380 RFC was not polished and Peter Eisentraut's position was like, 183 00:09:05,380 --> 00:09:08,300 we should release only when it's polished because who knows, 184 00:09:08,300 --> 00:09:10,780 maybe something is changed, is going to change. 185 00:09:11,320 --> 00:09:14,180 So I remember like, yeah, very conservative. 186 00:09:15,060 --> 00:09:16,500 Everyone already has it. 187 00:09:16,500 --> 00:09:20,800 Various NPM modules, Python, Google projects, they all already 188 00:09:20,800 --> 00:09:25,540 have uuidv7 support, even though RFC is not finalized. 189 00:09:26,200 --> 00:09:29,340 But Postgres, I think, took very conservative position. 190 00:09:29,340 --> 00:09:32,460 Maybe it's good for Database because it should be conservative, 191 00:09:32,600 --> 00:09:33,100 right? 192 00:09:33,900 --> 00:09:36,260 Michael: Well, and we need to support it for 5 years, right? 193 00:09:36,260 --> 00:09:40,060 Like if whatever we implement, we're saying we're supporting 194 00:09:40,080 --> 00:09:43,680 this version for 5 years, even if you automatically, even if 195 00:09:43,680 --> 00:09:47,320 you manage to deprecate it the next version, which is also nearly 196 00:09:47,320 --> 00:09:47,800 impossible. 197 00:09:47,800 --> 00:09:50,380 Normally you need to deprecate, give warnings that it's being 198 00:09:50,380 --> 00:09:53,680 deprecated, then actually remove it in a later version. 199 00:09:53,680 --> 00:09:59,180 So I really understand that and I think also Postgres did support, 200 00:09:59,180 --> 00:10:02,260 like you've said this multiple times, Postgres does support uuidv7 201 00:10:02,980 --> 00:10:04,660 in older versions of Postgres. 202 00:10:04,780 --> 00:10:08,580 You just have to generate them either outside of the database 203 00:10:09,280 --> 00:10:10,440 or with your own function. 204 00:10:10,440 --> 00:10:12,100 You've done it even within Postgres. 205 00:10:12,520 --> 00:10:15,780 Nikolay: Yeah, this is what we do already for a couple of years. 206 00:10:15,880 --> 00:10:21,600 And we have how to do it in PL/pgSQL or just with pure SQL. 207 00:10:22,340 --> 00:10:23,860 It was part of my how-tos. 208 00:10:24,400 --> 00:10:26,140 Yeah, it's already aged. 209 00:10:26,540 --> 00:10:30,540 And you can combine it with partitioning, TimescaleDB or whatever. 210 00:10:31,340 --> 00:10:35,460 But this time it's just, actually it's like basically synthetic 211 00:10:35,680 --> 00:10:36,600 sugar, honestly. 212 00:10:36,740 --> 00:10:38,400 Michael: It's a nice function, right? 213 00:10:39,160 --> 00:10:43,200 Nikolay: Since it's official we can expect it will be more widely 214 00:10:43,200 --> 00:10:43,700 used. 215 00:10:44,280 --> 00:10:47,160 Yes, and it was discussed a couple of times on Hacker News and 216 00:10:47,160 --> 00:10:51,440 people say there is like concerns about security ability to guess. 217 00:10:51,440 --> 00:10:54,600 I don't get those concerns because it's really hard to guess 218 00:10:54,600 --> 00:10:56,540 anything from those values. 219 00:10:56,820 --> 00:11:00,700 You can extract timestamp and unlike recommendations from original 220 00:11:01,200 --> 00:11:06,780 RFC authors, Postgres decided to present a function uuid_extract_timestamp 221 00:11:06,820 --> 00:11:11,680 from value, from uuidv7 value, which I find useful, for 222 00:11:11,680 --> 00:11:13,400 example, for partitioning implementation. 223 00:11:14,180 --> 00:11:15,700 So anyway, this is a great feature. 224 00:11:15,700 --> 00:11:16,640 We discussed it in depth. 225 00:11:16,640 --> 00:11:18,020 Let's not lose time. 226 00:11:18,080 --> 00:11:21,900 But it's not like we have a data type, we just have a couple 227 00:11:21,900 --> 00:11:25,440 of functions to generate and to extract timestamp, that's it. 228 00:11:25,440 --> 00:11:29,340 But data type remains just UUID, it's version agnostic. 229 00:11:29,340 --> 00:11:31,820 Michael: Yeah, we already, we do have a data type, we already 230 00:11:31,820 --> 00:11:34,640 have the perfect data type, which is we've had it for many years, 231 00:11:34,640 --> 00:11:36,940 now we have nice functions around it too. 232 00:11:36,980 --> 00:11:40,460 Nikolay: Now it's just in Query and everything in Query is great. 233 00:11:40,680 --> 00:11:43,080 Just use it, use it. 234 00:11:43,080 --> 00:11:47,380 It's better than version 4 for performance reasons and also convenient. 235 00:11:47,640 --> 00:11:52,100 I still find it helpful to keep timestamp like created at separately, 236 00:11:53,040 --> 00:11:57,660 although you lose additional bytes, but it's a matter of like, 237 00:11:57,740 --> 00:11:59,800 you need to compare what's better for you. 238 00:11:59,860 --> 00:12:05,960 Just rely on ID value, if it's uuidv7, or have it separate. 239 00:12:06,480 --> 00:12:11,180 I think it's worth a separate blog post to compare pros and cons 240 00:12:11,180 --> 00:12:12,140 for this, actually. 241 00:12:13,480 --> 00:12:16,040 It's an interesting topic for developers, I think. 242 00:12:16,480 --> 00:12:17,220 What else? 243 00:12:17,400 --> 00:12:24,620 We also discussed 1 feature a few times, which I'm listed as 244 00:12:24,620 --> 00:12:25,520 author, right? 245 00:12:25,680 --> 00:12:28,760 But I'm like, I wipe coded it originally. 246 00:12:30,720 --> 00:12:35,140 And it was --no-policies support for pg_dump and pg_restore. 247 00:12:35,860 --> 00:12:40,740 I know there is an issue which some other folks were fixing because 248 00:12:41,640 --> 00:12:43,680 it didn't eliminate comments. 249 00:12:44,240 --> 00:12:46,260 Policies can have comments, you know. 250 00:12:47,360 --> 00:12:51,960 So if you specify --no-policies, I saw there was an additional 251 00:12:52,080 --> 00:12:54,740 work following up this patch. 252 00:12:55,360 --> 00:12:57,180 And yeah, it's a pity that I missed it. 253 00:12:57,180 --> 00:13:01,700 But in my defense, I must admit that other know something in 254 00:13:01,700 --> 00:13:04,540 pg_dump and pg_restore also forgot about comments. 255 00:13:04,860 --> 00:13:10,100 And this work was done in Claude 3.7 in January, I think. 256 00:13:10,600 --> 00:13:14,560 Was committed later by Tom Lane with some improvements. 257 00:13:15,240 --> 00:13:20,120 But when I say VibeCoded, it was originally VibeCoded, but eventually 258 00:13:20,340 --> 00:13:25,360 before sending patch, I always review line by line everything 259 00:13:25,520 --> 00:13:29,280 and polish myself and we're using multiple LLMs. 260 00:13:29,600 --> 00:13:32,280 So I think it's not strictly speaking vibe-coding. 261 00:13:32,720 --> 00:13:37,960 We call it vibe-hacking, but it's just good for prototyping to 262 00:13:37,960 --> 00:13:41,200 use some help of LLM if you don't write C code every day. 263 00:13:41,200 --> 00:13:46,420 And I think it unlocks the power for people who are not C coders. 264 00:13:46,560 --> 00:13:49,040 They can implement things and present patches. 265 00:13:49,200 --> 00:13:52,860 But obviously, if you do it without looking into code and send 266 00:13:52,860 --> 00:13:56,740 some vibe-coded, purely vibe-coded patch, it's a terrible idea. 267 00:13:57,180 --> 00:14:02,720 And inside my company, everyone is using LLM AI to code, but 268 00:14:02,720 --> 00:14:07,120 it's like, it's prohibited to right now to start coding on your 269 00:14:07,120 --> 00:14:11,360 own, not thinking about LLM, but it's also prohibited, not, not 270 00:14:11,360 --> 00:14:13,640 taking responsibility for final result. 271 00:14:13,940 --> 00:14:19,020 If it's some BS code, it's your, on your shoulders, not like 272 00:14:19,400 --> 00:14:20,640 you cannot blame AI. 273 00:14:20,640 --> 00:14:20,900 Right. 274 00:14:20,900 --> 00:14:24,680 And if you, if you send something, some proposal, open pull request, 275 00:14:24,680 --> 00:14:28,340 merge request, or sending patches, obviously you are responsible 276 00:14:28,420 --> 00:14:30,780 for what you are sending right 277 00:14:31,240 --> 00:14:33,160 Michael: yeah well I think it's quite rude as well. 278 00:14:33,160 --> 00:14:37,480 Isn't it because you're asking when you send a proposal PR merge 279 00:14:37,480 --> 00:14:41,740 request Anything you're asking other people to review it and 280 00:14:41,980 --> 00:14:45,980 I think the minimum you owe them is that you've reviewed it yourself 281 00:14:46,040 --> 00:14:49,220 yeah, when you when you ask other people to review something. 282 00:14:49,640 --> 00:14:53,860 And that didn't used to be an issue because by writing something, 283 00:14:54,060 --> 00:14:56,020 you are reviewing it in a way. 284 00:14:56,280 --> 00:15:00,060 But now that people have options that involve them not even writing 285 00:15:00,060 --> 00:15:04,620 it, I think it is a step worth being insistent on. 286 00:15:04,900 --> 00:15:07,720 Nikolay: If you like not writing C code all the time, but you 287 00:15:07,720 --> 00:15:12,100 want to implement something, go take AI to help you, but then 288 00:15:12,100 --> 00:15:16,700 take another AI to review it and explain every line, explain 289 00:15:16,720 --> 00:15:20,500 every line to you so you fully understand what you, like the 290 00:15:20,500 --> 00:15:23,800 final result and you are happy with it and you have tests and 291 00:15:23,800 --> 00:15:24,300 documentation. 292 00:15:24,960 --> 00:15:28,880 By the way, small tip, start with writing documentation first. 293 00:15:29,380 --> 00:15:34,020 And like usually people like, it's boring to think about documentation 294 00:15:34,200 --> 00:15:35,200 and tests with AI. 295 00:15:35,200 --> 00:15:39,320 It's slightly more like productive and Postgres test system is 296 00:15:39,320 --> 00:15:39,960 it's Perl. 297 00:15:39,960 --> 00:15:45,260 So it's really like not easy to deal with if you've like never 298 00:15:45,780 --> 00:15:50,140 wrote Perl or already forgot it after 20 years, for example, 299 00:15:50,140 --> 00:15:50,820 or something. 300 00:15:50,940 --> 00:15:52,480 Anyway, I think AI is great. 301 00:15:52,480 --> 00:15:56,140 It's like it's unlocking creativity here, but the final result 302 00:15:56,140 --> 00:15:57,680 is on your shoulders fully. 303 00:15:57,940 --> 00:16:02,120 And I also, before this episode, I checked a couple of articles 304 00:16:02,320 --> 00:16:06,240 about Postgres 18 and I found terrible articles. 305 00:16:07,820 --> 00:16:08,740 Terrible articles. 306 00:16:08,860 --> 00:16:13,660 I can, like, I already, I'm building the set list of companies 307 00:16:13,700 --> 00:16:18,060 who produce BS blog posts, obviously LLM generated. 308 00:16:19,080 --> 00:16:22,200 And this is like not okay, in my opinion. 309 00:16:23,600 --> 00:16:25,460 Do you mind if I name the companies? 310 00:16:26,320 --> 00:16:27,240 Michael: Not at all. 311 00:16:27,380 --> 00:16:28,580 I find it quite frustrating. 312 00:16:28,580 --> 00:16:30,060 And I think again, it's rude, right? 313 00:16:30,060 --> 00:16:34,280 You're taking a shortcut and publishing something that you hope 314 00:16:34,280 --> 00:16:38,000 will be read by many, many people, taking many, many more hours 315 00:16:38,000 --> 00:16:39,960 to read it than you took to write it. 316 00:16:39,960 --> 00:16:41,020 I just think it's... 317 00:16:41,600 --> 00:16:42,100 Nikolay: Yeah. 318 00:16:45,020 --> 00:16:46,320 In the past, I saw a lot of BS articles about Postgres on the 319 00:16:46,320 --> 00:16:51,440 website called MinervaDB.xyz, something like 320 00:16:51,440 --> 00:16:51,940 this. 321 00:16:51,980 --> 00:16:56,260 This time stormatics.tech blog post has a lot of wrong information. 322 00:16:56,680 --> 00:16:59,300 Michael: Well yeah I guess this goes back to your your age-old 323 00:16:59,300 --> 00:17:02,500 thing of always verify right but sadly now we need to make sure 324 00:17:02,500 --> 00:17:06,220 we're also verifying things from what we considered maybe in 325 00:17:06,220 --> 00:17:08,460 the past to be reputable companies. 326 00:17:09,000 --> 00:17:11,760 Nikolay: Actually let me correct myself this stormatics.tech 327 00:17:12,560 --> 00:17:16,000 article was not I'm in the context of you know like building 328 00:17:16,000 --> 00:17:18,760 some police bot for blog posts about Postgres. 329 00:17:20,320 --> 00:17:25,900 This blog post was about optimization of moving pg_wal to different 330 00:17:26,280 --> 00:17:29,020 disk and it had a lot of mistakes. 331 00:17:29,640 --> 00:17:35,020 For example, mentioning undo in WAL and a few others. 332 00:17:35,020 --> 00:17:39,060 But this time about Postgres 18, we have blog post published 333 00:17:39,060 --> 00:17:44,020 on dev.to, devtips, and it has a lot of, like it's LLM generated 334 00:17:44,100 --> 00:17:47,860 because it says logical application gets full DDL support. 335 00:17:47,860 --> 00:17:48,900 Well, no, it's not. 336 00:17:48,900 --> 00:17:51,000 This is 1 of the pains we still have. 337 00:17:51,140 --> 00:17:52,540 Obviously, LLM generated. 338 00:17:52,540 --> 00:17:58,700 So I think before we consider problems with patches, wipe coded, 339 00:17:59,240 --> 00:18:03,120 purely wipe coded, not wipe hacked, as I said, but wipe coded. 340 00:18:03,120 --> 00:18:07,120 We have a bigger problem of misinformation being spread on in 341 00:18:07,120 --> 00:18:11,520 blog posts So yeah, and what do you think it would it would help 342 00:18:11,520 --> 00:18:15,560 to have some tool to quickly verify if I to do some fact-checking 343 00:18:16,300 --> 00:18:19,220 So we I checking AI and so on. 344 00:18:19,640 --> 00:18:22,060 Michael: The challenge is false positives and false negatives 345 00:18:22,120 --> 00:18:22,620 right? 346 00:18:22,700 --> 00:18:27,840 Like if an AI is capable of coming up with this stuff, well who's 347 00:18:27,840 --> 00:18:30,540 to say an AI wouldn't also rubber stamp it? 348 00:18:30,600 --> 00:18:33,480 So that's the kind of, there's the, there's the not catching 349 00:18:33,480 --> 00:18:36,240 the ones that are AI written. 350 00:18:36,600 --> 00:18:40,100 And well, and then there's the issue of catching or flagging 351 00:18:40,400 --> 00:18:44,220 blog posts that were human written, but it thinks AI and accusing 352 00:18:44,220 --> 00:18:45,420 those of being AI written. 353 00:18:45,420 --> 00:18:47,640 I think this is an unsolved problem, isn't it? 354 00:18:47,640 --> 00:18:51,180 Don't education have this exact same issue with coursework and 355 00:18:51,180 --> 00:18:51,260 things? 356 00:18:51,260 --> 00:18:51,500 I think 357 00:18:51,500 --> 00:18:52,500 Nikolay: you are right. 358 00:18:52,660 --> 00:18:53,820 Humans also hallucinate. 359 00:18:54,140 --> 00:18:56,700 I hallucinated on this very podcast sometimes. 360 00:18:57,260 --> 00:19:02,040 I think we should say, not AI checking AI, we should say, I have 361 00:19:02,040 --> 00:19:02,720 this information. 362 00:19:02,760 --> 00:19:05,200 It can be a blog post or some report. 363 00:19:05,500 --> 00:19:08,540 We actually, internally, we have such tools already because we 364 00:19:08,560 --> 00:19:12,680 do a lot of consulting We write some reports and we need to verify 365 00:19:12,700 --> 00:19:15,460 we do we need to do fact-checking because sometimes you think 366 00:19:15,460 --> 00:19:18,400 this is this is it this is how Postgres works, but it worked 367 00:19:18,400 --> 00:19:20,940 like that 10 years ago and it already changed. 368 00:19:22,120 --> 00:19:26,200 So we need to dig deeper and do some fact-checking all the time. 369 00:19:26,200 --> 00:19:30,600 Because for example, I was wrong recently when I said changing 370 00:19:31,020 --> 00:19:37,120 your primary key from int4 to int8 is going to produce 371 00:19:37,120 --> 00:19:37,940 a lot of bloat. 372 00:19:37,940 --> 00:19:39,300 I was completely wrong. 373 00:19:39,400 --> 00:19:42,420 And in that case, I was wrong very deeply. 374 00:19:42,660 --> 00:19:46,600 So it turned out there is a table rewrite mechanism similar to 375 00:19:46,600 --> 00:19:47,100 VACUUM FULL. 376 00:19:47,940 --> 00:19:48,300 Nice. 377 00:19:48,300 --> 00:19:52,360 And table comes out from it, like alter table, alter column. 378 00:19:52,540 --> 00:19:56,100 Table in the end is actually fresh and all indexes are freshly 379 00:19:56,160 --> 00:19:56,660 rebuilt. 380 00:19:57,240 --> 00:20:01,440 And sometimes it's acceptable to have this downtime maintenance 381 00:20:01,440 --> 00:20:01,940 window. 382 00:20:02,660 --> 00:20:06,300 Michael: Yeah of course completely offline operation but no bloat. 383 00:20:06,780 --> 00:20:10,760 Nikolay: So yeah I sometimes don't trust myself and this tool 384 00:20:10,760 --> 00:20:14,720 like checking, fact checking we could use it yeah we use it internally 385 00:20:14,760 --> 00:20:18,800 again Like I am thinking to present it as an external tool so 386 00:20:18,800 --> 00:20:23,140 we could check blog posts we suspect are not accurate. 387 00:20:23,800 --> 00:20:26,160 Michael: I think that well accuracy is slightly different to 388 00:20:26,160 --> 00:20:30,040 LLM generated and I think it depends what you want to like. 389 00:20:30,040 --> 00:20:33,080 Do you want accurate posts or do you want human written posts? 390 00:20:33,080 --> 00:20:34,740 And that those are subtly different. 391 00:20:34,840 --> 00:20:37,700 Nikolay: I don't care about who wrote it, I want accurate posts. 392 00:20:38,000 --> 00:20:41,680 Michael: Yes, so I wasn't even talking about inaccuracies. 393 00:20:42,040 --> 00:20:45,600 I was talking about inaccuracies in the checking. 394 00:20:45,720 --> 00:20:48,980 So like, even if you assume the article is completely accurate 395 00:20:49,040 --> 00:20:53,940 and human-written an LLM checker could still say we think this 396 00:20:53,940 --> 00:20:57,680 was this has some hallucinations in it you know hallucinating 397 00:20:57,740 --> 00:21:02,440 the hallucination so I don't have much hope in this, other than 398 00:21:02,440 --> 00:21:02,940 trust-based. 399 00:21:03,120 --> 00:21:07,540 You know, if you as a company start publishing posts that you 400 00:21:07,540 --> 00:21:11,880 haven't checked, whether with humans or if it has inaccuracies 401 00:21:12,160 --> 00:21:14,240 in it, I'll forgive a few. 402 00:21:14,240 --> 00:21:17,000 But if you're doing it consistently and there's loads of made 403 00:21:17,000 --> 00:21:19,640 up stuff, I'm gonna stop reading your stuff and I'm gonna stop 404 00:21:19,640 --> 00:21:21,680 sharing it and you know there's... 405 00:21:22,360 --> 00:21:27,540 Nikolay: So LLM can be considered like amplifier of your like 406 00:21:27,540 --> 00:21:28,020 state. 407 00:21:28,020 --> 00:21:32,640 If you are not accurate, you produce a lot of bad pieces of advice, 408 00:21:32,640 --> 00:21:33,560 wrong things. 409 00:21:34,180 --> 00:21:36,040 It just amplifies it, right? 410 00:21:36,220 --> 00:21:38,980 So if you don't check information. 411 00:21:39,860 --> 00:21:43,760 To connect bits a little bit and finalize this AI discussion, 412 00:21:44,380 --> 00:21:47,280 I find it Also interesting that sometimes hallucinations are 413 00:21:47,280 --> 00:21:48,040 really helpful. 414 00:21:48,040 --> 00:21:51,920 I had several hallucinations which led to the idea, actually 415 00:21:51,980 --> 00:21:54,440 this missing feature would be great to have. 416 00:21:54,440 --> 00:21:56,020 Well, this is great hallucination. 417 00:21:56,280 --> 00:21:58,580 Let's have DDL in Postgres 18. 418 00:21:58,900 --> 00:22:00,400 Already too late, right? 419 00:22:00,400 --> 00:22:02,180 There is a work in progress, unfortunately. 420 00:22:03,240 --> 00:22:07,820 And by the way, I recently checked it, DDL and logical, support 421 00:22:07,820 --> 00:22:09,360 of DDL and logical replication. 422 00:22:09,940 --> 00:22:12,080 It's not like we discussed it, right? 423 00:22:12,660 --> 00:22:16,720 I have low expectations that it will be implemented very soon, 424 00:22:16,720 --> 00:22:17,220 unfortunately. 425 00:22:17,660 --> 00:22:18,140 And it's 426 00:22:18,140 --> 00:22:19,540 Michael: a lot of work, isn't it? 427 00:22:19,540 --> 00:22:23,540 And there's probably lower hanging fruit like sequences and things. 428 00:22:23,560 --> 00:22:25,380 Nikolay: Yes, this is also a work in progress. 429 00:22:25,380 --> 00:22:30,340 And I think, is it already going to post this 19 next year? 430 00:22:30,340 --> 00:22:32,080 I'm not sure. 431 00:22:32,600 --> 00:22:38,040 But sometimes I like just working, writing some ideas, I'm getting 432 00:22:38,040 --> 00:22:43,000 some great ideas like we had the case with corruption and we 433 00:22:43,000 --> 00:22:46,320 needed to fix it with all backups and so on. 434 00:22:46,320 --> 00:22:48,420 So we needed to apply a pg_resetwal. 435 00:22:49,340 --> 00:22:52,580 The idea was we need to recover from some backups and they were 436 00:22:52,580 --> 00:22:58,800 broken, so LLM suggested to use pg_resetwal with a system identifier 437 00:22:58,980 --> 00:23:00,480 option which doesn't exist. 438 00:23:00,920 --> 00:23:05,380 So I ended up implementing that option and proposing a patch. 439 00:23:05,380 --> 00:23:09,800 Unfortunately, it was not finished before Postgres 18, but maybe 440 00:23:09,800 --> 00:23:13,380 it will be finished before Postgres 19 and we will have this 441 00:23:13,380 --> 00:23:13,880 option. 442 00:23:14,140 --> 00:23:15,960 Yeah, so there is discussion about this. 443 00:23:15,960 --> 00:23:16,780 Michael: That's funny. 444 00:23:16,880 --> 00:23:19,280 Nikolay: Yeah, and a few things like this. 445 00:23:19,280 --> 00:23:24,600 So sometimes, so this is like, interesting thing to have LLMs, 446 00:23:24,840 --> 00:23:27,980 but you must fact check and sometimes you think, oh, this is 447 00:23:27,980 --> 00:23:30,260 a good idea and go implement it. 448 00:23:30,280 --> 00:23:31,200 So why not? 449 00:23:31,960 --> 00:23:32,460 Michael: Yeah. 450 00:23:32,640 --> 00:23:33,200 All right. 451 00:23:33,200 --> 00:23:35,760 Well, while we're on topic of corruption, another thing we've 452 00:23:35,760 --> 00:23:40,120 discussed a few times is checksums and encouraging people to 453 00:23:40,120 --> 00:23:40,960 enable them. 454 00:23:41,120 --> 00:23:44,040 And we're getting them, they're going to be on by default now 455 00:23:44,040 --> 00:23:45,200 from 18 onwards. 456 00:23:46,120 --> 00:23:48,280 Nikolay: I think it's long overdue. 457 00:23:48,840 --> 00:23:50,720 Michael: Yeah, it was but great. 458 00:23:51,060 --> 00:23:55,320 Nikolay: Yeah, it's a great great thing Minus 1 recommendation 459 00:23:55,520 --> 00:24:00,020 we will have in the future for our clients and RDS had it for 460 00:24:00,020 --> 00:24:00,920 ages, right? 461 00:24:00,920 --> 00:24:02,920 Checksums are enabled there by default. 462 00:24:02,920 --> 00:24:05,820 Google Cloud also yeah yeah just great thing 463 00:24:05,820 --> 00:24:08,800 Michael: yeah I think quite a few of the hosting providers do 464 00:24:08,800 --> 00:24:12,780 but each of them have to it's another 1 that any new hosting 465 00:24:12,780 --> 00:24:17,460 provider that comes along they have to then know to do that or 466 00:24:17,460 --> 00:24:19,120 their customers don't get it by default. 467 00:24:19,120 --> 00:24:21,660 Now they don't even have to know and they'll get it on by default 468 00:24:21,660 --> 00:24:22,740 which I think is great. 469 00:24:23,320 --> 00:24:23,820 Yeah. 470 00:24:24,060 --> 00:24:30,040 Another 1 we've discussed a few times is pg_upgrade getting or 471 00:24:30,040 --> 00:24:34,900 preserving the statistics, the optimized statistics across a 472 00:24:34,900 --> 00:24:35,820 version upgrade? 473 00:24:37,200 --> 00:24:40,580 Nikolay: This is a huge pain, and especially because, unlike 474 00:24:40,680 --> 00:24:44,280 previous topics, this is not solved in managed Postgres providers. 475 00:24:45,160 --> 00:24:49,700 I didn't see any of them, I mean, major providers like RDS, Cloud 476 00:24:49,700 --> 00:24:51,000 SQL, others. 477 00:24:51,540 --> 00:24:57,100 They always put it on shoulders on users to run, Analyze after 478 00:24:57,180 --> 00:24:57,820 major upgrade. 479 00:24:57,820 --> 00:25:00,220 And we had outages because of that. 480 00:25:00,520 --> 00:25:04,120 So now I think in the future, not now, in the future. 481 00:25:05,320 --> 00:25:09,940 You cannot use it before your old cluster is already on 18. 482 00:25:10,080 --> 00:25:14,180 So it's only like first time we'll be able to benefit from it 483 00:25:14,180 --> 00:25:19,240 in 1 or 2 years only, when you upgrade from 18 to newer version. 484 00:25:19,540 --> 00:25:24,560 But anyway, this is great news, so I feel really happy that this 485 00:25:24,560 --> 00:25:25,580 is solved finally. 486 00:25:25,800 --> 00:25:30,480 And this is solved in a very elegant way because now you can 487 00:25:30,480 --> 00:25:34,280 dump statistics, So it's not only for upgrades, you can use it 488 00:25:34,280 --> 00:25:40,320 for other, in other areas because you know, like, pg_restore also 489 00:25:40,460 --> 00:25:41,820 doesn't care about it. 490 00:25:42,340 --> 00:25:45,860 You restore and you need to run, Analyze manually, also vacuum 491 00:25:45,860 --> 00:25:47,480 actually, you need to run vacuum. 492 00:25:47,680 --> 00:25:51,120 Now, recommendation will be after pg_restore, after restoring 493 00:25:51,120 --> 00:25:57,620 from a logical backups, aka dumps, like logical backups is fine 494 00:25:57,620 --> 00:26:00,820 to name dumps, like, but not just backups. 495 00:26:01,680 --> 00:26:06,960 So when you restore from it, we always said you need to run a 496 00:26:06,960 --> 00:26:07,740 vacuum Analyze. 497 00:26:07,740 --> 00:26:13,330 Now it's just a vacuum to have visibility maps created sooner. 498 00:26:13,330 --> 00:26:15,320 Michael: Your index only scans, yeah. 499 00:26:15,520 --> 00:26:18,360 Nikolay: Well if you forget vacuum, it's not a big deal. 500 00:26:19,340 --> 00:26:23,140 Maybe you will like, and of course, autovacuum will take care 501 00:26:23,140 --> 00:26:28,520 of it, but lacking statistics, it's a big danger. 502 00:26:30,320 --> 00:26:33,500 Now you can dump statistics, restore statistics. 503 00:26:33,520 --> 00:26:37,280 This is great I think from this we can benefit sooner than from 504 00:26:37,360 --> 00:26:41,380 having it in upgrades because once you're already running Postgres 505 00:26:41,380 --> 00:26:43,120 18, you can start using it 506 00:26:44,180 --> 00:26:47,240 Michael: By the way, I think I think we might owe at least 1 507 00:26:47,240 --> 00:26:49,340 cloud provider an apology. 508 00:26:50,080 --> 00:26:53,160 Do you know who implemented this feature? 509 00:26:53,960 --> 00:26:55,640 3 guys, all from AWS. 510 00:26:56,240 --> 00:27:00,360 So I think at least 1 cloud provider does deserve credit for 511 00:27:00,360 --> 00:27:02,600 implementing this feature, and they've actually implemented it 512 00:27:02,600 --> 00:27:04,860 in a way that everybody benefits which is cool. 513 00:27:05,280 --> 00:27:07,540 Nikolay: Right, but why do we need to apologize? 514 00:27:08,160 --> 00:27:10,580 Michael: Well because you said all of the cloud providers have 515 00:27:10,580 --> 00:27:13,680 just shoved this onto users and didn't do anything about it. 516 00:27:13,780 --> 00:27:16,540 Nikolay: I think yes, we can apologize easily, not a problem. 517 00:27:16,760 --> 00:27:17,740 And this is great. 518 00:27:17,800 --> 00:27:22,080 I knew this feature is from RDS team. 519 00:27:22,080 --> 00:27:22,900 This is great. 520 00:27:23,360 --> 00:27:30,320 But still, if you run upgrade on RDS, it has 16 points, 16 steps. 521 00:27:31,020 --> 00:27:31,920 Michael: Yeah, yeah, yeah. 522 00:27:31,940 --> 00:27:35,420 Nikolay: How to upgrade using pg_upgrade official documentation 523 00:27:35,640 --> 00:27:37,000 has 19 steps. 524 00:27:37,540 --> 00:27:39,120 RDS has 16 steps. 525 00:27:39,440 --> 00:27:43,820 And 1 of them, last 1, 1 of couple of last ones, is you don't 526 00:27:43,820 --> 00:27:44,920 need to run analyze. 527 00:27:45,140 --> 00:27:50,440 And people tend to forget or just say, okay, maybe it's not super 528 00:27:50,440 --> 00:27:50,940 important. 529 00:27:52,080 --> 00:27:53,440 And it happens. 530 00:27:53,660 --> 00:27:56,080 People, it's manual. 531 00:27:56,820 --> 00:27:58,880 And so I stand my ground. 532 00:28:00,560 --> 00:28:05,040 They don't automate it, they put it on shoulders on users. 533 00:28:05,340 --> 00:28:10,480 But of course, now they need to wait until 18 and then 19, and 534 00:28:10,480 --> 00:28:13,360 then it will be fully automated, which is great. 535 00:28:15,020 --> 00:28:18,640 Yeah, I heard reasons why they don't automate it, because it 536 00:28:18,640 --> 00:28:23,160 can take time, prolonging downtime window. 537 00:28:23,800 --> 00:28:27,580 And there is an official approach, analyzing stages, which I 538 00:28:27,580 --> 00:28:29,120 think is a mistake, honestly. 539 00:28:30,060 --> 00:28:34,460 Because in OLTP cases, we better to get final statistics sooner 540 00:28:35,420 --> 00:28:37,400 and do it inside maintenance window. 541 00:28:38,100 --> 00:28:38,560 Right? 542 00:28:38,560 --> 00:28:39,060 Michael: Yeah. 543 00:28:39,620 --> 00:28:44,560 I just think if you care about the downtime, you should probably 544 00:28:45,160 --> 00:28:50,380 be working out how to do a 0 downtime upgrade in some way, shape, 545 00:28:50,380 --> 00:28:52,440 or form, and then you don't have to worry about this problem 546 00:28:52,440 --> 00:28:52,940 anyway. 547 00:28:53,220 --> 00:28:57,100 So it's more, I think if you can take the downtime, why not just 548 00:28:57,100 --> 00:28:58,220 do the full analyze? 549 00:28:59,160 --> 00:28:59,660 Nikolay: Exactly. 550 00:29:00,060 --> 00:29:05,320 And also in this area, pg_upgrade, I think, did you see that it 551 00:29:05,320 --> 00:29:09,640 got parallelization --drops option for... 552 00:29:09,720 --> 00:29:10,820 Michael: Yes, I did, yeah. 553 00:29:10,840 --> 00:29:14,720 Nikolay: This is also great news for those who care about upgrades. 554 00:29:15,780 --> 00:29:17,900 Everyone should care about upgrades, right? 555 00:29:19,660 --> 00:29:21,520 Michael: Yeah, especially when we're talking about a new major 556 00:29:21,520 --> 00:29:22,020 version. 557 00:29:23,260 --> 00:29:26,160 Nikolay: But anyway, like, am I right that statistics can be 558 00:29:26,160 --> 00:29:29,160 dumped and restored, even not in the context of upgrades? 559 00:29:29,860 --> 00:29:33,420 Michael: If it's if If they can, then actually people can already 560 00:29:33,420 --> 00:29:35,260 benefit from this because you can use... 561 00:29:35,740 --> 00:29:38,680 Nikolay: Yeah, this is a dump option statistics. 562 00:29:39,060 --> 00:29:40,120 So once we are... 563 00:29:40,760 --> 00:29:43,440 Because this is discussed in the context of upgrades only, but 564 00:29:43,440 --> 00:29:45,420 again, it's not about only upgrades. 565 00:29:46,800 --> 00:29:50,860 Once your server is running Postgres 18, you can already start 566 00:29:50,860 --> 00:29:55,460 benefiting and adjust your dump scripts and all these dump statistics. 567 00:29:57,900 --> 00:30:01,300 And then you can just restore it and that's it. 568 00:30:01,300 --> 00:30:06,200 I also noticed that vacuumdb has now, in Postgres 18, received 569 00:30:06,200 --> 00:30:10,220 the option missing stats only, to compute only missing optimizer 570 00:30:10,320 --> 00:30:10,820 statistics. 571 00:30:10,840 --> 00:30:14,720 This is interesting, because vacuumdb has also --jobs, 572 00:30:14,720 --> 00:30:18,220 so to move faster if you have many many cores? 573 00:30:18,580 --> 00:30:19,280 Right, you can say... 574 00:30:19,280 --> 00:30:22,320 Michael: I think, yeah, I think we need the missing statistics 575 00:30:22,440 --> 00:30:27,460 because currently extended statistics aren't dumped and aren't 576 00:30:28,140 --> 00:30:28,640 preserved. 577 00:30:29,140 --> 00:30:33,940 So If you create, you know, we talked about correlations between 578 00:30:33,940 --> 00:30:34,620 2 columns. 579 00:30:34,860 --> 00:30:36,980 If you create those, those are not preserved. 580 00:30:36,980 --> 00:30:42,380 So I suspect that got added to analyze so that we don't have 581 00:30:42,380 --> 00:30:44,120 to run full analyze still. 582 00:30:44,200 --> 00:30:47,120 Otherwise there's, Well, if we've used create statistics, if 583 00:30:47,120 --> 00:30:48,840 we've used the extended statistics. 584 00:30:49,240 --> 00:30:50,340 Nikolay: Yeah, that's interesting. 585 00:30:50,740 --> 00:30:57,260 But also, as like, to be careful with vacuumdb --drops, 586 00:30:57,260 --> 00:30:58,680 I love it. 587 00:30:58,680 --> 00:31:02,980 And because if, if you have increased default statistics target, 588 00:31:02,980 --> 00:31:07,480 or like a lot of statistics to a lot of tables and so on, it's 589 00:31:07,480 --> 00:31:11,260 great to have parallelized processing here, right? 590 00:31:11,460 --> 00:31:16,300 But unfortunately, until version 19, so next year only, partition 591 00:31:16,320 --> 00:31:21,600 tables will still be in trouble because vacuumdb doesn't take 592 00:31:21,780 --> 00:31:25,580 proper care of them, it only cares about partitions themselves 593 00:31:25,580 --> 00:31:31,360 but root table is not processed and if you process it with analyze 594 00:31:31,640 --> 00:31:35,240 it will process partitions once again anyway so there is a problem 595 00:31:35,240 --> 00:31:35,740 here. 596 00:31:36,020 --> 00:31:38,900 Michael: We have a new feature for that too in 18. 597 00:31:39,240 --> 00:31:41,140 Nikolay: No it should be in 19. 598 00:31:42,040 --> 00:31:45,040 Michael: We have an analyze only for the parent partition. 599 00:31:45,720 --> 00:31:50,100 Nikolay: No no no Analyze only for parent partition is in work. 600 00:31:50,220 --> 00:31:54,320 I think it's not like it's, it's, maybe it's committed already, 601 00:31:54,320 --> 00:31:55,840 but it's not in Postgres 18. 602 00:31:57,440 --> 00:31:59,360 Laurenz Albe did it. 603 00:31:59,840 --> 00:32:03,740 I noticed because I also wanted to do it but it was only recently 604 00:32:03,740 --> 00:32:04,780 committed I think. 605 00:32:04,780 --> 00:32:09,240 There is allow vacuum analyze to process partition tables without 606 00:32:09,240 --> 00:32:10,640 processing their children. 607 00:32:11,120 --> 00:32:15,780 This is great but this is not vacuumdb stuff. 608 00:32:16,620 --> 00:32:20,840 Michael: Yeah okay but we can do analyze only and then the table, 609 00:32:20,900 --> 00:32:23,480 the parent name, you're right it's a separate part. 610 00:32:23,480 --> 00:32:26,040 Nikolay: Analyze only we had always, I mean for ages. 611 00:32:26,040 --> 00:32:29,380 vacuumdb --analyze-only, this is what we do after upgrade, I know 612 00:32:29,380 --> 00:32:33,080 it because This was our recipe until we hit this very problem 613 00:32:33,080 --> 00:32:37,980 when partition tables lacked, root table lacked statistics and 614 00:32:38,520 --> 00:32:41,400 some queries suffered from it. 615 00:32:41,480 --> 00:32:44,340 Michael: No, but if you did it on the parent partition, it would 616 00:32:44,340 --> 00:32:48,840 also gather statistics on all of the child notations as well. 617 00:32:49,060 --> 00:32:50,500 Nikolay: There are many things here. 618 00:32:50,500 --> 00:32:55,580 There is a single threaded SQL command, right? 619 00:32:55,600 --> 00:32:57,840 Analyze, or vacuum analyze, doesn't matter. 620 00:32:57,840 --> 00:32:58,440 They are single 621 00:32:58,440 --> 00:32:58,940 Michael: thread, 622 00:32:59,140 --> 00:32:59,640 Nikolay: right? 623 00:33:00,020 --> 00:33:04,200 And there is also vacuumdb, which has a -j, aka 624 00:33:04,200 --> 00:33:07,160 --drops option, which helps you parallelize. 625 00:33:07,760 --> 00:33:14,560 The problem is when you run vacuumdb -j like 60, I'll analyze 626 00:33:14,560 --> 00:33:15,060 only. 627 00:33:15,560 --> 00:33:19,980 Root tables in partition tables will lack statistics after it. 628 00:33:20,080 --> 00:33:24,880 And this is solved after Postgres 18 beta 1 was out. 629 00:33:24,960 --> 00:33:27,280 So we need to wait until 19. 630 00:33:27,340 --> 00:33:28,660 Michael: So that would be in 19, 631 00:33:28,660 --> 00:33:29,160 Nikolay: sure. 632 00:33:29,280 --> 00:33:34,080 We cannot move fast and take care of partitions, unfortunately, 633 00:33:34,200 --> 00:33:35,880 partition tables properly. 634 00:33:37,240 --> 00:33:37,540 Yeah. 635 00:33:37,540 --> 00:33:40,840 Michael: We need to set, then you need to do a separate task. 636 00:33:40,840 --> 00:33:41,340 Nikolay: Right. 637 00:33:41,580 --> 00:33:45,700 So in Postgres 18, first of all, statistics can be dumped, which 638 00:33:45,700 --> 00:33:46,320 is great. 639 00:33:46,520 --> 00:33:48,460 pg_upgrade is not in trouble anymore. 640 00:33:48,460 --> 00:33:48,680 Okay. 641 00:33:48,680 --> 00:33:50,720 But sometimes we still need to rebuild statistics. 642 00:33:51,060 --> 00:33:54,840 In Postgres 18, also, vacuumdb received missing stats only. 643 00:33:55,400 --> 00:33:59,240 And vacuum, single-threaded SQL command, received ability to 644 00:33:59,380 --> 00:34:01,300 take care of partition tables properly. 645 00:34:02,140 --> 00:34:03,220 This is what happened. 646 00:34:03,760 --> 00:34:05,580 Michael: Not just vacuum, but also analyze. 647 00:34:06,760 --> 00:34:07,260 Nikolay: Yeah. 648 00:34:07,940 --> 00:34:10,820 Vacuum, well, yeah, vacuum analyze and analyze. 649 00:34:11,820 --> 00:34:13,940 Michael: So vacuum's a bit pointless. 650 00:34:13,980 --> 00:34:16,980 I don't think there's any point vacuuming a parent partition 651 00:34:17,080 --> 00:34:19,260 because it's not going to have any data in it. 652 00:34:19,840 --> 00:34:21,100 Nikolay: But it needs statistics. 653 00:34:21,360 --> 00:34:22,340 Vacuum, analyze, vacuum. 654 00:34:22,340 --> 00:34:26,980 Michael: Exactly, it does need statistics separately from its 655 00:34:27,100 --> 00:34:27,600 children. 656 00:34:28,320 --> 00:34:32,960 But in the past, in 17 for example, in version 17, 16, 15, if 657 00:34:32,960 --> 00:34:36,380 I, to get statistics on the parent partition, I'd have to run 658 00:34:36,380 --> 00:34:41,420 analyze on the parent table, which would also gather, regather 659 00:34:41,580 --> 00:34:44,840 statistics for all of the child tables, which is a lot, like 660 00:34:44,840 --> 00:34:46,980 that, There's so much more work. 661 00:34:46,980 --> 00:34:50,660 Nikolay: So what happened, we say, vacuumdb -j is great because 662 00:34:51,360 --> 00:34:54,320 we have 96 cores, let's go. 663 00:34:54,640 --> 00:34:56,320 Hyphen-j 96, let's go. 664 00:34:56,320 --> 00:35:00,320 But then, oh, actually, root tables and partition case, they 665 00:35:00,320 --> 00:35:01,140 lack statistics. 666 00:35:01,560 --> 00:35:03,420 We need to run analyze on them. 667 00:35:03,420 --> 00:35:08,320 But when you run analyze on root table, it recalculates statistics 668 00:35:08,360 --> 00:35:11,100 for all partitions once again. 669 00:35:11,440 --> 00:35:15,980 Now in 18, we have ability to skip that and say, okay, vacuumdb 670 00:35:16,420 --> 00:35:19,020 -j, move really fast. 671 00:35:19,060 --> 00:35:22,640 And then additionally run analyze only on partition tables, only 672 00:35:22,640 --> 00:35:24,920 for root partitions, skipping partitions themselves, because 673 00:35:24,920 --> 00:35:26,460 vacuumdb already did it. 674 00:35:26,460 --> 00:35:31,100 So now we have full recipe for Postgres 18, how to recalculate 675 00:35:31,160 --> 00:35:33,620 statistics in all database and move really fast. 676 00:35:34,060 --> 00:35:37,580 When you're like inside maintenance window, for example, you 677 00:35:37,580 --> 00:35:41,500 need vacuumdb -j, and then separately you need to take care of 678 00:35:41,580 --> 00:35:45,900 root tables for partition tables, skipping partitions themselves. 679 00:35:46,560 --> 00:35:47,060 Right? 680 00:35:47,480 --> 00:35:48,900 This will be the fastest. 681 00:35:49,660 --> 00:35:54,160 In Postgres 19, vacuumdb will take care of this and we will need 682 00:35:54,160 --> 00:35:56,400 to get rid of this second step. 683 00:35:56,860 --> 00:35:59,700 Michael: Yeah, I didn't check the commit, but it's possible. 684 00:35:59,760 --> 00:36:02,540 I said missing stats only might be for create statistics, it 685 00:36:02,540 --> 00:36:05,140 might be for extended statistics, but it might also be for parent 686 00:36:05,140 --> 00:36:05,640 partitions. 687 00:36:05,980 --> 00:36:08,140 That could be another place you're missing stats. 688 00:36:08,140 --> 00:36:08,420 Yeah. 689 00:36:08,420 --> 00:36:08,980 I haven't checked. 690 00:36:08,980 --> 00:36:12,500 Nikolay: Anyway, 19 will be excellent and we will have all the 691 00:36:12,500 --> 00:36:15,140 pieces of the puzzle ready and we can move fast. 692 00:36:15,140 --> 00:36:21,180 I like, I honestly think like Vacuum and Analyze could have some 693 00:36:21,180 --> 00:36:25,320 option, I mean SQL commands, they could have some option to tell 694 00:36:25,520 --> 00:36:28,300 like I want additional workers. 695 00:36:29,760 --> 00:36:34,840 Give me like, you know, like vacuumdb is good, But if you in 696 00:36:34,840 --> 00:36:38,940 managed Postgres case like RDS, you cannot easily run vacuumdb 697 00:36:38,940 --> 00:36:41,840 because you need this to instance running in the same region 698 00:36:41,840 --> 00:36:42,520 or something. 699 00:36:42,520 --> 00:36:45,920 So you like it's terrible idea to run it from laptop, right? 700 00:36:45,920 --> 00:36:49,060 Because connection issues and so on, you will lose it. 701 00:36:49,060 --> 00:36:52,960 So you need an EC2 instance next to it, like some maintenance 702 00:36:53,040 --> 00:36:55,020 host, and from there you can... 703 00:36:55,760 --> 00:36:57,080 It's not convenient, right? 704 00:36:57,080 --> 00:37:00,520 It would be great to have autovacuum and say, I want like 16 drops 705 00:37:00,520 --> 00:37:04,360 or 96 drops and let's go full speed, we are inside maintenance 706 00:37:04,360 --> 00:37:04,860 window. 707 00:37:05,660 --> 00:37:06,600 Makes sense, right? 708 00:37:07,120 --> 00:37:08,460 Michael: I think so, yeah. 709 00:37:09,720 --> 00:37:13,080 Yeah I'm thinking also you might always want to have more, if 710 00:37:13,080 --> 00:37:17,500 you've got, you know, regularly scheduled jobs, you might always 711 00:37:17,500 --> 00:37:19,440 be able to give it more. 712 00:37:19,440 --> 00:37:22,260 You might even want a configuration parameters like always give 713 00:37:22,260 --> 00:37:25,940 my vacuums at least 3, you know up to 3 workers or something. 714 00:37:26,900 --> 00:37:29,640 Anyway, getting into autovacuum territory here. 715 00:37:30,120 --> 00:37:30,560 Nikolay: Yeah, yeah, yeah. 716 00:37:30,560 --> 00:37:34,040 Let's maybe jump in between some other pieces. 717 00:37:34,160 --> 00:37:36,760 There are actually so many great things. 718 00:37:37,660 --> 00:37:41,100 I like the release overall, a lot of stuff. 719 00:37:41,580 --> 00:37:45,900 And for example, I like the idea that now regular check, not 720 00:37:45,900 --> 00:37:49,500 check, not null constraints can be defined as not valid. 721 00:37:49,960 --> 00:37:53,800 As we discussed a few times, it's terrible naming not valid because 722 00:37:54,280 --> 00:37:59,060 they will be checked for new writes, but they are not checked 723 00:37:59,060 --> 00:38:00,060 for old writes. 724 00:38:00,060 --> 00:38:03,680 I think it's like all the pieces we had before already, because 725 00:38:03,680 --> 00:38:07,780 not null, I think in Postgres 12, since Postgres 12, it can rely 726 00:38:07,820 --> 00:38:12,180 on check constraints, check not null constraints. 727 00:38:12,820 --> 00:38:14,880 There is not null and there is check constraint. 728 00:38:16,640 --> 00:38:20,680 For primary keys, we needed only regular not nulls, not null 729 00:38:20,680 --> 00:38:21,680 constraints, right? 730 00:38:21,680 --> 00:38:25,800 But implicitly we could have already used like regular check 731 00:38:25,800 --> 00:38:29,720 constraints with not null and then do some dance around it. 732 00:38:29,720 --> 00:38:33,480 Now it's like becoming easier to redefine primary keys without 733 00:38:33,480 --> 00:38:35,400 thinking about all these nuances. 734 00:38:36,060 --> 00:38:36,980 I like this. 735 00:38:38,040 --> 00:38:41,100 Things are polished now in many areas. 736 00:38:42,920 --> 00:38:46,400 Like we just discussed it with statistics and this, not now. 737 00:38:46,480 --> 00:38:47,420 I like this. 738 00:38:47,800 --> 00:38:51,160 Maturity of many pieces, many functionalities. 739 00:38:52,440 --> 00:38:57,260 Michael: It feels like a release chock full of things that have 740 00:38:57,260 --> 00:38:58,140 come up for real. 741 00:38:58,140 --> 00:39:01,720 For real users have hit these issues and people have come up 742 00:39:01,720 --> 00:39:02,980 with fixes for them. 743 00:39:02,980 --> 00:39:07,860 It's not like, I know that's the almost a definition of a maturing 744 00:39:07,960 --> 00:39:12,840 product but it doesn't feel like there's that bigger divide between 745 00:39:13,120 --> 00:39:16,220 hackers working on things that hackers want to work on versus 746 00:39:16,860 --> 00:39:19,920 users hitting problems and actually wanting to solve problems 747 00:39:19,920 --> 00:39:23,760 you know that it feels very very tightly done which is nice. 748 00:39:23,960 --> 00:39:27,540 Nikolay: Yeah yeah another thing I wanted to mention is ability 749 00:39:27,980 --> 00:39:37,500 is infamous number of fast path slots for Lock Manager, right? 750 00:39:37,500 --> 00:39:38,900 We had 16 only. 751 00:39:39,180 --> 00:39:42,540 Yeah, we had issues in multiple customer databases. 752 00:39:43,320 --> 00:39:44,620 Several companies suffered. 753 00:39:45,360 --> 00:39:48,870 And I remember Jeremy Schneider, who did it? 754 00:39:48,870 --> 00:39:51,320 Like this was year of Lock Manager. 755 00:39:51,460 --> 00:39:52,820 Michael: Yes, that was his post. 756 00:39:53,260 --> 00:39:55,760 Nikolay: Yeah, it was maybe a couple of years ago, right? 757 00:39:55,760 --> 00:39:58,160 Or maybe, yeah, 2023, I think. 758 00:39:58,520 --> 00:40:03,680 And now, yeah, I raised this last year in hackers that we need 759 00:40:03,740 --> 00:40:07,920 to make this probably adjustable, like expose it as a setting. 760 00:40:08,760 --> 00:40:13,500 And Postgres 18 finally has this solved, but in a different way. 761 00:40:13,520 --> 00:40:18,080 I think Tomas Vondra, who worked on it mainly, right? 762 00:40:18,340 --> 00:40:24,640 And instead of adding 1 more GUC setting, we already have almost 763 00:40:24,640 --> 00:40:25,660 300, right? 764 00:40:26,840 --> 00:40:27,540 Too many. 765 00:40:27,980 --> 00:40:34,300 Instead of that, now it takes into account max locks per transaction 766 00:40:34,600 --> 00:40:35,520 setting, right? 767 00:40:35,900 --> 00:40:36,540 I think. 768 00:40:37,120 --> 00:40:39,560 And automatically adjusts based on that. 769 00:40:39,920 --> 00:40:43,820 So if you have a lot of cores, you likely, if you tune your Postgres, 770 00:40:43,840 --> 00:40:45,700 you raise that setting already. 771 00:40:46,420 --> 00:40:52,680 So number of these slots for fast path checks in the Lock Manager 772 00:40:52,680 --> 00:40:55,360 mechanism, it will automatically raise. 773 00:40:55,840 --> 00:41:02,220 And we checked in 1 case, we had 16 hitting replicas in that 774 00:41:02,220 --> 00:41:02,540 case. 775 00:41:02,540 --> 00:41:06,520 Sometimes it's hitting your primary workloads, sometimes replicas 776 00:41:06,560 --> 00:41:07,060 workloads. 777 00:41:07,480 --> 00:41:12,160 In that case, we had 16 by default, right, before 18, but now 778 00:41:12,160 --> 00:41:15,940 we will have, due to that setting, it's already adjusted, we 779 00:41:15,940 --> 00:41:16,940 will have 64. 780 00:41:17,620 --> 00:41:24,500 And honestly, in half an hour, there's a scheduled benchmark 781 00:41:24,600 --> 00:41:26,340 I need to do, I need to conduct. 782 00:41:27,720 --> 00:41:31,880 But unfortunately, I remember early implementations of the idea 783 00:41:31,880 --> 00:41:36,820 of raising this threshold were not super successful in terms 784 00:41:36,820 --> 00:41:39,600 of helping with this performance cliff. 785 00:41:39,720 --> 00:41:40,860 So I'm not sure. 786 00:41:41,060 --> 00:41:45,060 This should be very carefully tested in each particular case, 787 00:41:45,060 --> 00:41:48,420 because there are several variants of this cliff. 788 00:41:49,400 --> 00:41:52,860 Michael: Yeah, well if I trust anybody around performance cliff, 789 00:41:53,480 --> 00:41:55,880 I do trust Tomas to have looked into it. 790 00:41:56,500 --> 00:41:57,480 Nikolay: So I'm optimistic. 791 00:41:57,880 --> 00:42:01,680 As we learned from our podcast episode, we have different definitions 792 00:42:01,920 --> 00:42:02,720 of performance cliff. 793 00:42:02,720 --> 00:42:03,220 Michael: True. 794 00:42:04,660 --> 00:42:09,620 There is 1 huge feature in Postgres 18 we haven't talked about 795 00:42:09,620 --> 00:42:11,780 yet, which is asynchronous I/O. 796 00:42:12,100 --> 00:42:14,540 Nikolay: Yeah, well, this is elephant in the room, honestly. 797 00:42:14,760 --> 00:42:17,860 And I must admit, I lack experience. 798 00:42:19,300 --> 00:42:21,280 So I cannot tell a lot about this. 799 00:42:21,280 --> 00:42:26,540 I know this is a big work led by Andres Freund, right? 800 00:42:26,680 --> 00:42:27,180 Yeah. 801 00:42:27,700 --> 00:42:30,840 So yeah, I remember following this work. 802 00:42:31,020 --> 00:42:31,860 It's a lot. 803 00:42:31,880 --> 00:42:37,440 I hope we will find good results in some benchmarks in like actual 804 00:42:37,440 --> 00:42:43,540 systems we help manage But I don't have such experience yet. 805 00:42:43,820 --> 00:42:45,840 Looking forward to it, honestly, so 806 00:42:46,620 --> 00:42:48,900 Michael: Yeah, it'll be good to benchmark it because I think 807 00:42:48,900 --> 00:42:53,240 a lot of the normal benchmark type workloads, you know, pgbench, 808 00:42:53,420 --> 00:42:57,400 sysbench, that kind of thing, won't see benefits from this work. 809 00:42:57,400 --> 00:43:02,420 There are like lots and lots of small queries and this is much 810 00:43:02,420 --> 00:43:04,200 better at large scans, right? 811 00:43:04,200 --> 00:43:08,440 Like if you're benefiting from gathering a lot at once, you're 812 00:43:08,440 --> 00:43:12,160 gonna see bigger differences when you're getting more blocks 813 00:43:12,160 --> 00:43:14,080 than 1 block, for example. 814 00:43:14,540 --> 00:43:17,320 So yeah, I think it'll be interesting in real world workloads 815 00:43:17,320 --> 00:43:18,240 to see how big a difference. 816 00:43:18,240 --> 00:43:21,540 I think it's another 1 of those things that you could see significant 817 00:43:21,880 --> 00:43:26,100 improvements to how fast vacuums run on some of your largest 818 00:43:26,100 --> 00:43:30,020 tables or how fast, I don't actually don't know exactly which 819 00:43:30,020 --> 00:43:33,560 things have made it in, but I think bitmap heap scans definitely 820 00:43:33,560 --> 00:43:38,200 have and vacuum definitely has and there's a few others but as 821 00:43:38,200 --> 00:43:40,600 we see more and more of those get used in future versions of 822 00:43:40,600 --> 00:43:43,980 Postgres I think we're going to see the benefits of this without 823 00:43:43,980 --> 00:43:46,720 having to change anything again it's another 1 of those features 824 00:43:46,720 --> 00:43:50,640 that has managed to be committed on by default and with quite 825 00:43:50,640 --> 00:43:54,120 high, like not, maybe not high is not necessarily the right word, 826 00:43:54,120 --> 00:43:57,180 but it hasn't been shipped with really conservative defaults, 827 00:43:57,180 --> 00:43:57,900 I don't think. 828 00:43:57,900 --> 00:43:59,640 That's how I'd phrase it. 829 00:43:59,860 --> 00:44:00,300 Nikolay: Yeah. 830 00:44:00,300 --> 00:44:06,960 I would also pay attention to additional CLI tools this version 831 00:44:06,960 --> 00:44:07,840 is bringing. 832 00:44:08,800 --> 00:44:14,440 Because in Postgres 17 somehow I've overlooked and we in our 833 00:44:14,440 --> 00:44:18,760 discussion didn't touch it, but later I noticed, not I noticed, 834 00:44:18,760 --> 00:44:25,520 somebody pointed out to this big new tool appeared, a pg_create_subscription, 835 00:44:25,520 --> 00:44:30,220 converting physical standby to logical replica, 836 00:44:30,420 --> 00:44:34,140 which is great, like absolutely, like this is automation of, 837 00:44:34,260 --> 00:44:37,920 like we can throw out a lot of automation, which is great. 838 00:44:38,000 --> 00:44:40,120 I always love to do it. 839 00:44:40,280 --> 00:44:42,980 It's official and it's working. 840 00:44:43,380 --> 00:44:48,160 I'm not sure about managed Postgres setups yet because I think 841 00:44:48,480 --> 00:44:53,760 they must expose it via their APIs and CLIs because it needs 842 00:44:53,760 --> 00:44:57,320 to be run on the server and we don't have access to it. 843 00:44:57,560 --> 00:45:01,980 But in self-managed setups, it's already available since last 844 00:45:01,980 --> 00:45:02,480 year. 845 00:45:02,900 --> 00:45:06,680 I mean, since your production is running Postgres 17. 846 00:45:07,220 --> 00:45:07,580 Yeah. 847 00:45:07,580 --> 00:45:13,820 But also, this release Postgres 18 gets extension pg_logicalinspect. 848 00:45:15,060 --> 00:45:16,980 Well, it's not CLI, but some extension. 849 00:45:17,440 --> 00:45:21,560 I'm not sure what's inside in detail, but I would like to explore 850 00:45:21,560 --> 00:45:26,640 it, because logical replication is quite complex, and inspecting 851 00:45:26,880 --> 00:45:29,780 logical snapshots and sounds is a really good idea. 852 00:45:30,060 --> 00:45:31,660 And also there is extension pg_overexplain. 853 00:45:33,340 --> 00:45:36,420 I'm jumping between CLI tools and some extensions, I know. 854 00:45:36,780 --> 00:45:42,160 But this is a good thing, I think, to understand. 855 00:45:42,500 --> 00:45:44,180 I remember how it was born. 856 00:45:45,140 --> 00:45:46,800 Robert Haas did it, right? 857 00:45:47,260 --> 00:45:47,760 Yeah. 858 00:45:48,500 --> 00:45:51,980 And I think maybe you can tell more about this because it's closer 859 00:45:51,980 --> 00:45:55,420 to your fields, dealing with EXPLAIN plans. 860 00:45:55,680 --> 00:46:00,060 Michael: Yeah, I think, firstly, kudos for an awesome name. 861 00:46:00,060 --> 00:46:02,260 How good a name is pg_overexplain 862 00:46:02,780 --> 00:46:04,300 Nikolay: but over tune 863 00:46:05,440 --> 00:46:09,920 Michael: Yeah But yeah, so I think it does 2 things that are 864 00:46:09,920 --> 00:46:14,200 quite important 1 is it's the first customer of the ability to 865 00:46:14,200 --> 00:46:18,260 extend EXPLAIN in an extension, So I think it's a good example 866 00:46:18,260 --> 00:46:21,820 to people if they want to add more features to EXPLAIN in the 867 00:46:21,820 --> 00:46:25,520 future which has been, you know, over the last 4 or 5 versions 868 00:46:25,520 --> 00:46:28,700 we've had quite a few new parameters added to EXPLAIN and I suspect 869 00:46:29,540 --> 00:46:32,700 there's some amount of belief that maybe some of those could 870 00:46:32,700 --> 00:46:35,720 have been extensions for a while first and seen how popular they 871 00:46:35,720 --> 00:46:37,320 were before adding them for everybody. 872 00:46:37,840 --> 00:46:41,760 So that first and foremost Robert made it EXPLAIN extensible 873 00:46:41,980 --> 00:46:48,060 and then overexplain is an example of that but also is more 874 00:46:48,060 --> 00:46:51,280 information than a user would normally want from EXPLAIN that 875 00:46:51,280 --> 00:46:55,120 would generally be useful for hackers like people working on 876 00:46:55,120 --> 00:46:59,760 the Postgres source code or optimizer trying to understand why 877 00:46:59,760 --> 00:47:03,080 it's making certain decisions while they're working on a new 878 00:47:03,080 --> 00:47:03,580 feature. 879 00:47:04,120 --> 00:47:10,080 So for example the behavior around disabled nodes. 880 00:47:10,080 --> 00:47:13,080 So you know when you can you know when you're testing why isn't 881 00:47:13,080 --> 00:47:19,780 Postgres using my index and you want to do enable_seqscan = off 882 00:47:20,160 --> 00:47:24,500 so that you completely disable, well you try and discourage sequential 883 00:47:24,520 --> 00:47:27,680 scans so that if it could possibly use your index it will. 884 00:47:28,180 --> 00:47:32,540 In the past that used to work by adding a massive cost constant 885 00:47:33,060 --> 00:47:36,840 to sequential scans so that they'd be discouraged but not impossible 886 00:47:37,760 --> 00:47:41,180 That's changed in I know Robert actually was happy to be the 887 00:47:41,180 --> 00:47:45,180 person working on that 1 as well and it's now done on a on a 888 00:47:45,180 --> 00:47:48,300 base of counting the number of disabled nodes as the first tiebreak 889 00:47:48,400 --> 00:47:52,520 and then cost as the second tiebreak which is a really neat implementation 890 00:47:53,000 --> 00:47:56,820 I've actually blogged about that but the overexplain instead 891 00:47:56,820 --> 00:48:00,880 of telling you which nodes are disabled within the EXPLAIN plan 892 00:48:00,880 --> 00:48:04,540 it will give you a counter of the number of disabled nodes so 893 00:48:04,540 --> 00:48:08,440 far so that's useful for hackers but it's really confusing as 894 00:48:08,440 --> 00:48:12,440 a user because if you're using it you can see disabled nodes 895 00:48:12,440 --> 00:48:15,360 counter being 1 all the way up and you have to kind of look back 896 00:48:15,360 --> 00:48:19,300 to see what the first 1 was and luckily I think it was David 897 00:48:19,300 --> 00:48:23,500 Rowley who kind of backed that out and made it more user-friendly 898 00:48:23,600 --> 00:48:26,620 but the the point is EXPLAIN is designed for users. 899 00:48:27,260 --> 00:48:30,920 The primary user of EXPLAIN and EXPLAIN ANALYZE are people trying 900 00:48:30,920 --> 00:48:32,300 to speed up slow queries. 901 00:48:32,780 --> 00:48:36,040 Nikolay: But- Do you mean human users or do you 902 00:48:36,040 --> 00:48:37,100 Michael: mean- I 903 00:48:37,100 --> 00:48:37,640 Nikolay: mean human users. 904 00:48:37,640 --> 00:48:42,440 Michael: I mean human users and what I mean is not Postgres hackers, 905 00:48:42,440 --> 00:48:44,520 not people working on the optimizer. 906 00:48:44,700 --> 00:48:47,880 So overexplain gives them a bit more information about exactly 907 00:48:47,880 --> 00:48:50,580 what the optimizer is doing at each stage. 908 00:48:50,580 --> 00:48:53,220 And I just don't think it's going to be useful for users. 909 00:48:53,680 --> 00:48:53,960 Nikolay: Yeah. 910 00:48:53,960 --> 00:48:57,020 Let's also mention moving to macro level. 911 00:48:57,180 --> 00:49:00,540 We mentioned that pg_stat_statements have a couple of improvements. 912 00:49:01,240 --> 00:49:05,380 We always suggest that especially for Java applications or some 913 00:49:05,380 --> 00:49:08,680 applications which tend to set application_name to some like 914 00:49:08,680 --> 00:49:10,180 session ID, blah, blah, blah. 915 00:49:10,580 --> 00:49:14,240 And set comment could pollute pg_stat_statements. 916 00:49:14,800 --> 00:49:18,800 We always said set pg_stat_statements.track_utility to off, so you 917 00:49:18,800 --> 00:49:20,040 don't track set commands. 918 00:49:20,320 --> 00:49:23,040 Now in Postgres 18 it's not a problem anymore. 919 00:49:23,040 --> 00:49:25,600 Parameter is just like, I think $1, right? 920 00:49:25,600 --> 00:49:26,180 Should be. 921 00:49:26,180 --> 00:49:26,680 Yeah. 922 00:49:27,940 --> 00:49:30,860 So yeah, you basically it's all, everything is just 1 entry set 923 00:49:30,860 --> 00:49:32,340 application_name to something. 924 00:49:33,420 --> 00:49:36,940 So normalization is implemented for set commands. 925 00:49:37,040 --> 00:49:41,600 And also something is improved in the area of tracking parallel 926 00:49:41,600 --> 00:49:43,020 activity, right? 927 00:49:43,140 --> 00:49:44,980 Michael: Yes, there's like some extra columns. 928 00:49:45,040 --> 00:49:46,840 Nikolay: Not enough columns in pg_stat_statements. 929 00:49:48,900 --> 00:49:52,000 Michael: I have actually seen a conversation since then discouraging 930 00:49:52,300 --> 00:49:53,080 more columns. 931 00:49:53,760 --> 00:49:54,960 Interesting that that 1 got in. 932 00:49:54,960 --> 00:49:58,100 I wouldn't have thought that would be the 1 that snuck in last. 933 00:49:58,460 --> 00:50:02,220 Talking of new columns, there's also some new ones on 934 00:50:02,420 --> 00:50:07,980 pg_stat_all_tables and similar for vacuum tracking, vacuum and analyze 935 00:50:08,160 --> 00:50:10,160 time spent which looks really useful. 936 00:50:10,160 --> 00:50:14,300 Nikolay: Yeah yeah I like this and I think it's worth working 937 00:50:14,300 --> 00:50:20,760 on moving information from logs to SQL yeah It's easier to monitor 938 00:50:20,760 --> 00:50:23,120 it than dealing with logs. 939 00:50:24,840 --> 00:50:29,320 I can't wait until errors will be tracked properly in Postgres. 940 00:50:29,540 --> 00:50:32,160 There is work in progress in this area. 941 00:50:32,160 --> 00:50:35,880 I hope it will end up being added to 19. 942 00:50:36,940 --> 00:50:41,200 Not yet, I just like, maybe it won't work, but I think errors 943 00:50:41,200 --> 00:50:43,240 should be tracked as well, properly. 944 00:50:43,660 --> 00:50:45,300 Counters at least, like something. 945 00:50:46,160 --> 00:50:49,140 Yeah, because now we need to go to logs all the time. 946 00:50:49,640 --> 00:50:53,240 So yeah, although I actually, I learn all the time. 947 00:50:53,240 --> 00:50:57,340 Last 20 years I learned Postgres and sometimes I feel like I'm 948 00:50:57,340 --> 00:50:57,840 lagging. 949 00:50:57,940 --> 00:51:00,640 Development is faster than my learning curve. 950 00:51:00,940 --> 00:51:01,440 Yeah. 951 00:51:01,660 --> 00:51:06,760 So I just learned that there is a pg_stat_database_conflicts or something, 952 00:51:07,900 --> 00:51:12,500 view, which exposes some types of errors. 953 00:51:12,500 --> 00:51:16,220 And for example, it can tell you that query was canceled because 954 00:51:16,840 --> 00:51:18,300 tablespace was moved. 955 00:51:18,900 --> 00:51:23,400 It also can tell you that it was canceled because of certain 956 00:51:23,400 --> 00:51:27,680 timeouts, but not statement timeout and not transaction timeout, 957 00:51:27,720 --> 00:51:29,400 not idle transaction timeout. 958 00:51:30,020 --> 00:51:32,300 Yeah, due to lock timeouts, for example. 959 00:51:32,560 --> 00:51:35,720 Lock timeouts, yes, but not statement timeout, not transaction, 960 00:51:35,740 --> 00:51:37,080 not, I don't know. 961 00:51:37,440 --> 00:51:39,580 And a few more like replication conflicts. 962 00:51:39,900 --> 00:51:45,040 So it tracks some counters, but choice is really strange. 963 00:51:47,500 --> 00:51:51,200 And query cancellation due to very common reasons like statement 964 00:51:51,200 --> 00:51:51,700 timeout. 965 00:51:52,280 --> 00:51:56,100 Unfortunately, you need to parse logs right now or use an extension 966 00:51:56,320 --> 00:51:57,480 called logerrors. 967 00:51:57,880 --> 00:51:59,840 Not super popular, but very convenient. 968 00:52:00,460 --> 00:52:03,680 Unfortunately, available nowhere, almost. 969 00:52:04,300 --> 00:52:06,540 So only in self-managed case, I guess. 970 00:52:07,120 --> 00:52:10,940 But I hope this idea of logerrors will be finally implemented 971 00:52:10,940 --> 00:52:13,740 in the future in Postgres itself and yeah. 972 00:52:14,440 --> 00:52:15,200 Okay, good. 973 00:52:15,300 --> 00:52:17,720 We touched some future a little bit as well. 974 00:52:18,860 --> 00:52:22,020 Michael: I had 1 more favorite I wanted to give a shout out to. 975 00:52:23,920 --> 00:52:26,020 Obviously it's EXPLAIN related being me. 976 00:52:26,480 --> 00:52:26,980 Nikolay: Right. 977 00:52:27,260 --> 00:52:29,680 Michael: I think there's a really big improvement that has gone 978 00:52:29,680 --> 00:52:34,440 a bit under the radar for many, which is actual row counts are 979 00:52:34,440 --> 00:52:41,140 going to be reported as decimals now so this is most important 980 00:52:41,240 --> 00:52:47,800 when you have loops and lots of loops and it will report you've 981 00:52:47,800 --> 00:52:50,180 got 100,000 loops. 982 00:52:50,680 --> 00:52:51,420 Pardon me? 983 00:52:51,580 --> 00:52:53,360 Nikolay: 1.5 rows, right? 984 00:52:53,940 --> 00:52:58,720 Michael: 1.5 per loop is way more useful than 1 or 2 rounded 985 00:52:58,740 --> 00:53:03,820 to, but it's even more important when you are around the 0.5 986 00:53:04,340 --> 00:53:09,440 rows per loop or 0 like anything 0 to 1 is particularly interesting 987 00:53:10,240 --> 00:53:15,520 below 0.5 is very very important because it will round to 0 and 988 00:53:15,520 --> 00:53:18,800 no matter what you multiply 0 by you're gonna think that that's 989 00:53:18,800 --> 00:53:23,740 returning 0 rows and that's really Really not true when you've 990 00:53:23,740 --> 00:53:25,220 got a hundred thousand loops. 991 00:53:25,400 --> 00:53:29,680 Nikolay: Yeah Yeah, the mistake accumulates and multiplies. 992 00:53:30,020 --> 00:53:35,080 Michael: Yeah, exactly Okay, the next like once you get past 993 00:53:35,080 --> 00:53:38,240 the looping operation, you will see the actual number of rows, 994 00:53:38,240 --> 00:53:40,140 but you don't know what's happened in between. 995 00:53:40,680 --> 00:53:42,560 Now you don't have to worry about that. 996 00:53:42,560 --> 00:53:46,080 It will report it as a decimal and you can multiply that. 997 00:53:46,080 --> 00:53:49,540 Nikolay: Let me add 1 more because this is great actually because 998 00:53:49,540 --> 00:53:52,740 I feel connection to so many features and 1 more yeah this is 999 00:53:52,740 --> 00:53:56,140 where Andrey Borodin worked like and others many others actually. 1000 00:53:56,460 --> 00:54:01,380 He worked for quite some years and finally amcheck is getting 1001 00:54:01,460 --> 00:54:02,920 support for GIN indexes. 1002 00:54:03,280 --> 00:54:03,780 Yeah. 1003 00:54:03,820 --> 00:54:07,540 This is big because we do need it. 1004 00:54:07,540 --> 00:54:10,340 We do need it because we need to use amcheck often. 1005 00:54:11,480 --> 00:54:14,000 Michael: I actually missed that in the release notes as well. 1006 00:54:14,200 --> 00:54:17,400 Nikolay: It always worked for B-tree to check indexes for corruption 1007 00:54:17,400 --> 00:54:21,560 when you, for example, perform OS upgrade or other stuff. 1008 00:54:21,580 --> 00:54:23,980 You need to check for corruption from time to time. 1009 00:54:24,520 --> 00:54:27,840 But GIN indexes, like, we couldn't do it. 1010 00:54:27,840 --> 00:54:32,640 Well, we usually, in self-managed cases, yeah, only in self-managed 1011 00:54:32,720 --> 00:54:34,800 cases, I think we used patched version. 1012 00:54:34,800 --> 00:54:36,640 So we, we tested that patch. 1013 00:54:38,000 --> 00:54:42,240 We, it, it took several years for, for the patch to mature and 1014 00:54:42,240 --> 00:54:45,920 get rid of false positives, false negatives, I think. 1015 00:54:47,780 --> 00:54:49,620 Especially false negatives when it's missing. 1016 00:54:49,620 --> 00:54:50,620 I always get confused. 1017 00:54:50,620 --> 00:54:52,940 But it was false positives as well. 1018 00:54:53,000 --> 00:54:54,720 Yeah, but now it's great. 1019 00:54:54,960 --> 00:54:55,700 It's there. 1020 00:54:55,920 --> 00:54:57,560 So it's time to use it. 1021 00:54:57,620 --> 00:55:00,600 Extend your amcheck actions to a GIN. 1022 00:55:01,020 --> 00:55:01,520 Yeah. 1023 00:55:01,720 --> 00:55:04,340 And if you don't have amcheck actions, you must. 1024 00:55:04,460 --> 00:55:06,800 I mean, you should, not must, you should. 1025 00:55:07,360 --> 00:55:08,900 Because amcheck is a great tool. 1026 00:55:09,160 --> 00:55:15,640 I think maybe it should be renamed because we use it mostly to 1027 00:55:15,640 --> 00:55:17,000 check indexes for corruption. 1028 00:55:18,700 --> 00:55:22,360 But yeah, anyway, maybe it's a good name actually because it's 1029 00:55:22,360 --> 00:55:22,860 unique. 1030 00:55:23,160 --> 00:55:27,780 But I think for large Postgres databases, everyone should check 1031 00:55:27,800 --> 00:55:30,640 for corruption from time to time for various types of corruption 1032 00:55:31,020 --> 00:55:32,900 and amcheck is a great tool for it. 1033 00:55:33,540 --> 00:55:34,940 Michael: Is it access method? 1034 00:55:35,280 --> 00:55:36,500 Nikolay: Access method check, yeah. 1035 00:55:36,500 --> 00:55:37,500 Michael: Yeah, makes sense. 1036 00:55:37,700 --> 00:55:39,440 All right, yeah, good one. 1037 00:55:39,560 --> 00:55:40,640 Thanks so much, Nikolai. 1038 00:55:40,640 --> 00:55:40,822 Catch you next week. 1039 00:55:40,822 --> 00:55:40,904 Nikolay: Thank you, enjoy it.