1 00:00:00,060 --> 00:00:03,840 Nikolay: Hello, hello, this is PostgresFM and today as usual 2 00:00:04,160 --> 00:00:05,220 only 2 of us. 3 00:00:05,220 --> 00:00:07,620 Not as usual, sometimes we have guests, right? 4 00:00:07,800 --> 00:00:12,820 So my name is Nikolay, Postgres.AI and my co-host is Michael, 5 00:00:13,040 --> 00:00:13,540 pgMustard. 6 00:00:13,860 --> 00:00:14,560 Hi Michael. 7 00:00:15,100 --> 00:00:15,920 Michael: Hello Nikolay. 8 00:00:16,560 --> 00:00:18,120 Nikolay: Yeah, how are you doing? 9 00:00:18,900 --> 00:00:20,260 Michael: I am good, how are you? 10 00:00:20,380 --> 00:00:21,360 Nikolay: I'm very good. 11 00:00:21,600 --> 00:00:26,020 So we decided, you decided actually, I probably wouldn't decide 12 00:00:26,120 --> 00:00:32,140 myself, but you decided suddenly, and this is a coincidence I 13 00:00:32,140 --> 00:00:35,180 guess, hopefully yesterday Postgres 17 was released. 14 00:00:35,740 --> 00:00:41,120 So let's talk about some features, random features we just picked 15 00:00:41,120 --> 00:00:42,280 up from the list. 16 00:00:42,740 --> 00:00:46,240 It has a lot of, a long list of features, right? 17 00:00:46,240 --> 00:00:46,980 As usual. 18 00:00:48,280 --> 00:00:49,080 Michael: Yeah, absolutely. 19 00:00:49,620 --> 00:00:50,120 Hundreds. 20 00:00:50,380 --> 00:00:53,040 So we're definitely not going to be able to talk about the vast 21 00:00:53,040 --> 00:00:53,940 majority of them. 22 00:00:53,940 --> 00:00:56,600 So it'd be good to hear, like, looking through the list, which 23 00:00:56,600 --> 00:00:57,440 are your favorites. 24 00:00:57,800 --> 00:01:01,620 But before that, I think it's worth saying it's actually really 25 00:01:01,620 --> 00:01:02,120 impressive. 26 00:01:02,540 --> 00:01:06,560 A group of distributed people shipping to a release date that 27 00:01:06,560 --> 00:01:09,440 they've set quite a little while in advance you know they've 28 00:01:09,440 --> 00:01:13,780 had several betas a release candidate a few weeks ago and barring 29 00:01:14,280 --> 00:01:17,360 I haven't seen any big issues so I'd be surprised if it isn't, 30 00:01:17,360 --> 00:01:20,660 but completely respect the people making the final decision if 31 00:01:20,660 --> 00:01:21,960 they have decided to postpone. 32 00:01:22,240 --> 00:01:24,860 But yeah, it's really impressive that they can ship to a date 33 00:01:24,860 --> 00:01:27,540 and each year we get a new major release. 34 00:01:27,540 --> 00:01:32,640 I think that's not super common in large distributed projects 35 00:01:32,860 --> 00:01:37,620 to get major releases annually, same time every year. 36 00:01:37,740 --> 00:01:40,620 Almost you could set your calendar by it. 37 00:01:41,200 --> 00:01:45,460 Nikolay: Yeah, it sounds like obligation, which usually is happening 38 00:01:45,460 --> 00:01:47,580 inside big corporations, right? 39 00:01:47,780 --> 00:01:50,820 Like, let's plan to do this and be consistent. 40 00:01:51,500 --> 00:01:55,080 Michael: Yeah, and I know there's a lot of infrastructure, or 41 00:01:55,080 --> 00:01:59,540 like, kind of things like the commit fests that mean, you know, 42 00:01:59,540 --> 00:02:02,040 there's a feature freeze many, many months in advance. 43 00:02:02,040 --> 00:02:04,900 And that's the kind of thing that allows them to be predictable. 44 00:02:05,060 --> 00:02:09,440 Like if they allowed features to go in near the end, that would 45 00:02:09,440 --> 00:02:12,620 almost certainly postpone things if something went wrong. 46 00:02:12,620 --> 00:02:15,040 It means there's tons of time to revert things if needed. 47 00:02:15,040 --> 00:02:17,460 There were a couple of things reverted again this time. 48 00:02:17,920 --> 00:02:22,720 Nikolay: Well, 1 of the most popular Linux distributions is doing 49 00:02:22,720 --> 00:02:24,500 a similar thing, right? 50 00:02:25,680 --> 00:02:26,340 Which 1? 51 00:02:26,460 --> 00:02:26,960 Ubuntu. 52 00:02:27,940 --> 00:02:28,440 Oh. 53 00:02:28,660 --> 00:02:34,440 Releases are like this structure defined long ago and every year 54 00:02:34,540 --> 00:02:35,820 2 releases, right? 55 00:02:35,980 --> 00:02:38,740 And there are long-term support releases as well. 56 00:02:38,740 --> 00:02:40,940 And this is similar to Postgres has. 57 00:02:41,400 --> 00:02:42,180 Michael: Yeah, good point. 58 00:02:42,180 --> 00:02:43,780 Nikolay: Postgres doesn't have LTS, but... 59 00:02:43,780 --> 00:02:45,160 Michael: Yeah, we don't have... 60 00:02:45,180 --> 00:02:47,960 Almost every version's LTS, I feel like, in Postgres. 61 00:02:48,140 --> 00:02:50,880 Like, it's supported for 5 years. 62 00:02:51,180 --> 00:02:54,840 Nikolay: Postgres don't have this concept, but my point is that 63 00:02:54,840 --> 00:02:58,600 it's also like planned dates of releases, right? 64 00:02:58,940 --> 00:02:59,660 Same thing. 65 00:03:00,060 --> 00:03:00,520 Michael: Yeah, true. 66 00:03:00,520 --> 00:03:01,420 Another good example. 67 00:03:01,640 --> 00:03:03,660 Nikolay: And also quite popular project. 68 00:03:04,240 --> 00:03:06,180 Michael: Yeah, I've heard of it. 69 00:03:06,180 --> 00:03:07,260 Nikolay: Also open source. 70 00:03:08,260 --> 00:03:08,740 Yeah. 71 00:03:08,740 --> 00:03:11,260 But there is a single company behind it. 72 00:03:11,780 --> 00:03:12,600 Michael: Yeah, true. 73 00:03:12,620 --> 00:03:13,580 Nikolay: Yeah, well, okay. 74 00:03:13,580 --> 00:03:17,420 I also wanted to mention that those who listen to us regularly 75 00:03:18,500 --> 00:03:22,820 should already remember some features coming with this release. 76 00:03:23,360 --> 00:03:27,900 We discussed a few of them recently in previous episodes. 77 00:03:28,660 --> 00:03:31,500 transaction_timeout was a whole episode. 78 00:03:31,500 --> 00:03:32,140 What else? 79 00:03:33,400 --> 00:03:34,260 Vacuum improvements? 80 00:03:34,900 --> 00:03:37,420 Michael: Yeah, vacuum improvements came up when we had a good 81 00:03:37,420 --> 00:03:38,600 conversation with Melanie. 82 00:03:39,720 --> 00:03:43,220 And we talked to Peter Geoghegan recently. 83 00:03:43,820 --> 00:03:47,660 Yeah, and that work was split over 17 and 18. 84 00:03:47,920 --> 00:03:52,360 But even since we spoke to Peter, there have been some cool blog 85 00:03:52,360 --> 00:03:55,640 posts about those improvements and people benchmarking them against 86 00:03:55,640 --> 00:03:56,540 real workloads. 87 00:03:56,980 --> 00:03:58,640 And the results are pretty cool. 88 00:03:58,640 --> 00:04:03,300 So both Benoit, who we mentioned in the episode, has published 89 00:04:03,420 --> 00:04:06,820 a blog post that I'll link up, and the Crunchy Data team, I think 90 00:04:06,820 --> 00:04:10,520 it was Brandur, published a great blog post benchmarking those 91 00:04:10,520 --> 00:04:11,020 improvements. 92 00:04:11,260 --> 00:04:14,000 I believe it's almost entirely due to those improvements. 93 00:04:14,800 --> 00:04:16,120 So, yeah, that was really cool. 94 00:04:16,120 --> 00:04:17,880 And I think there's 1 more as well. 95 00:04:17,880 --> 00:04:22,320 Yes, we discussed with Haki Benita, get or create, in which the 96 00:04:22,320 --> 00:04:25,900 topic of MERGE RETURNING came up, which we're getting in 17. 97 00:04:26,320 --> 00:04:30,060 So yeah, for at least 4 of the of the several hundred features 98 00:04:30,060 --> 00:04:30,720 we've discussed. 99 00:04:30,720 --> 00:04:33,980 Nikolay: I also like maybe this will be a start already I wanted 100 00:04:34,080 --> 00:04:39,380 to congratulate basically Andrey Borodin who has multiple contributions 101 00:04:39,520 --> 00:04:45,520 in this release and 2 of them were started during Postgres TV 102 00:04:45,980 --> 00:04:51,260 online hacking sessions and it's transaction_timeout and also 103 00:04:51,900 --> 00:04:54,400 UUID version 7 functions. 104 00:04:54,800 --> 00:04:58,760 Unfortunately Postgres 17 doesn't have UUID version 7 because 105 00:04:59,180 --> 00:05:03,360 there was a decision to wait until finalization of RFC. 106 00:05:04,160 --> 00:05:06,520 So standard needs to be out. 107 00:05:06,780 --> 00:05:11,880 Actually, it also was a controversial decision because many libraries 108 00:05:11,960 --> 00:05:17,960 already implemented UUID version 7, a lot of them, Node.js library, 109 00:05:17,960 --> 00:05:21,820 Go library, many, many, many, relying on the fact that it's very 110 00:05:21,820 --> 00:05:24,060 unlikely that something will change. 111 00:05:24,280 --> 00:05:28,200 But Postgres decided to be more conservative and, well, it's 112 00:05:28,200 --> 00:05:28,700 okay. 113 00:05:28,820 --> 00:05:31,600 But Postgres 17 has a function uuid_extract_timestamp. 114 00:05:33,820 --> 00:05:36,500 So we can, like, It's a preparation, basically. 115 00:05:37,060 --> 00:05:41,700 And also uuid_extract_version to return UUID information. 116 00:05:42,180 --> 00:05:45,980 I remember also the decision to have this function was controversial. 117 00:05:46,240 --> 00:05:50,500 The authors of Standard didn't encourage the fact that we can 118 00:05:50,500 --> 00:05:54,580 extract timestamp from UUID version 7. 119 00:05:54,640 --> 00:05:55,780 It's interesting, right? 120 00:05:55,840 --> 00:05:58,920 UUID version 7 is not supported, but already there are functions 121 00:05:59,280 --> 00:06:03,940 which are prepared to work with it, And they are not part of 122 00:06:03,940 --> 00:06:04,440 standard. 123 00:06:04,700 --> 00:06:07,400 Maybe that's why they are being released, right? 124 00:06:07,540 --> 00:06:12,180 But to me, this is an interesting feature because I think it's 125 00:06:12,180 --> 00:06:13,040 already so. 126 00:06:13,140 --> 00:06:16,720 For our clients during consulting sessions, when we see UUID 127 00:06:16,720 --> 00:06:20,940 version 4 being used as primary key, we say, don't do it. 128 00:06:21,420 --> 00:06:25,620 Because there are many articles already showing, with benchmarks 129 00:06:25,680 --> 00:06:30,600 showing how bad it is to have — for B-tree health, it's bad 130 00:06:30,600 --> 00:06:33,460 to insert in arbitrary 131 00:06:33,520 --> 00:06:33,760 Michael: places. 132 00:06:33,760 --> 00:06:34,260 Randomly. 133 00:06:34,540 --> 00:06:36,400 Nikolay: Yeah, basically randomly, right? 134 00:06:36,820 --> 00:06:41,440 And UUID version 7, it has benefits from both worlds. 135 00:06:41,460 --> 00:06:43,380 Actually, I think we had an episode about. 136 00:06:43,380 --> 00:06:43,780 Michael: I think we 137 00:06:43,780 --> 00:06:44,240 Nikolay: did. 138 00:06:44,240 --> 00:06:46,120 Yeah, maybe I was alone, actually. 139 00:06:46,580 --> 00:06:49,900 Yeah, so we encourage to use UUID version 7. 140 00:06:50,140 --> 00:06:54,360 And Interesting that it's easy to move from UUID version 4 to 141 00:06:54,360 --> 00:06:56,260 version 7 in existing projects. 142 00:06:56,280 --> 00:06:58,380 If you have a big table already, you just... 143 00:06:58,380 --> 00:07:04,400 They have the similar format, So you don't need to change anything 144 00:07:04,600 --> 00:07:08,740 besides how default is organized or how you insert value itself. 145 00:07:08,860 --> 00:07:11,980 So no table structure changes, which is good. 146 00:07:12,360 --> 00:07:16,400 And yeah, I think this fact that Now we have a function which 147 00:07:16,400 --> 00:07:21,000 allows us to extract timestamp if it's version 7, which has timestamp. 148 00:07:21,400 --> 00:07:25,140 It means also probably in many cases we can avoid additional 149 00:07:25,240 --> 00:07:31,340 16 bytes for column created at, because we can get this information 150 00:07:31,440 --> 00:07:33,140 right from the ID column. 151 00:07:33,760 --> 00:07:39,940 And also we can work with this information to have time-based 152 00:07:40,240 --> 00:07:40,740 partitioning. 153 00:07:41,320 --> 00:07:43,940 Even TimescaleDB can work with this. 154 00:07:43,940 --> 00:07:45,460 I have a recipe somewhere. 155 00:07:46,220 --> 00:07:47,680 I think we also discussed this. 156 00:07:47,680 --> 00:07:49,540 Michael: That's the 1 I joined you for, partitioning. 157 00:07:49,540 --> 00:07:53,320 Nikolay: Oh, okay, so we had 2 episodes about UUIDv7 and how to 158 00:07:53,320 --> 00:07:54,380 partition with it. 159 00:07:54,380 --> 00:08:00,820 It's very, I think it's very basic, important tricks to know 160 00:08:00,820 --> 00:08:01,300 about. 161 00:08:01,300 --> 00:08:01,560 Yeah. 162 00:08:01,560 --> 00:08:05,520 Because it can be useful in many, many systems. 163 00:08:07,540 --> 00:08:12,260 So this function now is part of core of Postgres, so just use 164 00:08:12,260 --> 00:08:12,760 it. 165 00:08:12,900 --> 00:08:13,680 It's great. 166 00:08:14,020 --> 00:08:14,700 What else? 167 00:08:14,700 --> 00:08:16,220 What else do you want to discuss? 168 00:08:16,480 --> 00:08:20,200 Michael: Well, maybe as a starting point, do you have a favorite 169 00:08:20,200 --> 00:08:24,100 or favorite kind of theme or area of improvement in 17? 170 00:08:24,340 --> 00:08:28,820 Nikolay: Well, my overall impression, like I had this in many 171 00:08:28,940 --> 00:08:33,340 previous Postgres versions when they were released, Impression 172 00:08:33,340 --> 00:08:36,640 is a lot of small stuff, but sometimes it's very important stuff 173 00:08:36,640 --> 00:08:37,560 for some projects. 174 00:08:38,040 --> 00:08:42,970 There's no like huge, 1 big huge thing in this release, right? 175 00:08:43,260 --> 00:08:47,120 And of course I like transaction_timeout because it was my idea. 176 00:08:47,180 --> 00:08:48,340 I like it a lot. 177 00:08:50,660 --> 00:08:54,140 I'm very grateful to Andrei who implemented this online. 178 00:08:55,580 --> 00:08:56,940 I like that we have it. 179 00:08:56,940 --> 00:08:59,280 Now I have a further idea we started discussing. 180 00:08:59,760 --> 00:09:00,840 Let me share it. 181 00:09:01,640 --> 00:09:07,640 I was thinking, a log_min_duration_statement setting defines when... 182 00:09:07,820 --> 00:09:11,080 It's similar to timeout basically, but instead of cancelling 183 00:09:11,480 --> 00:09:15,360 your transaction issue in rollback right it just logs. 184 00:09:15,480 --> 00:09:17,820 Michael: So you're thinking log min duration transaction? 185 00:09:18,260 --> 00:09:21,520 Nikolay: I think about transaction I think about idle in transaction 186 00:09:21,580 --> 00:09:22,560 state as well. 187 00:09:23,080 --> 00:09:28,580 Instead of cancelling, we could just log and maybe draw some... 188 00:09:28,780 --> 00:09:31,020 Let's not cancel, but warn, right? 189 00:09:31,080 --> 00:09:33,420 That something is not good here happening. 190 00:09:33,420 --> 00:09:33,920 Yeah. 191 00:09:34,020 --> 00:09:37,320 Basically, each transaction consists of only 3 normal states. 192 00:09:37,740 --> 00:09:42,980 Either we execute some statement or we don't execute something, 193 00:09:42,980 --> 00:09:43,320 right? 194 00:09:43,320 --> 00:09:47,520 We are idle between 2 statements or between statement and commit, 195 00:09:47,520 --> 00:09:48,280 for example. 196 00:09:48,520 --> 00:09:49,740 Michael: So what's the third? 197 00:09:50,380 --> 00:09:51,780 Nikolay: Third is the whole 198 00:09:51,900 --> 00:09:52,780 Michael: What do you mean? 199 00:09:52,900 --> 00:09:55,960 Nikolay: Oh, sorry, 2 states of but also there is the 200 00:09:55,960 --> 00:10:00,400 whole. Sorry sorry. Yeah, Yeah, you're right. 201 00:10:00,400 --> 00:10:00,660 Either we 202 00:10:00,660 --> 00:10:03,460 do something or we don't do anything inside transaction. 203 00:10:03,620 --> 00:10:06,320 There is also state outside, which is idle, right? 204 00:10:06,660 --> 00:10:07,540 And there's a hole. 205 00:10:07,540 --> 00:10:12,140 So basically, 3 timeout settings make sense. 206 00:10:12,180 --> 00:10:15,080 This already finally fully implemented in 2024. 207 00:10:16,400 --> 00:10:21,240 But also, It would make sense to warn just logging right? 208 00:10:21,680 --> 00:10:23,200 Maybe it's a good idea as well, right? 209 00:10:23,200 --> 00:10:28,780 I just I think I just came This idea came to me like a few days 210 00:10:28,780 --> 00:10:33,300 ago, honestly Maybe I should write to hackers about this. 211 00:10:34,300 --> 00:10:37,660 Michael: So you mean like as an administrator or somebody looking 212 00:10:37,660 --> 00:10:41,140 after this database, I can start to think we could be in trouble 213 00:10:41,140 --> 00:10:42,940 here if we let this get out of hand. 214 00:10:43,680 --> 00:10:46,840 Instead of punishing the users or whoever is actually in the 215 00:10:46,840 --> 00:10:50,660 middle of a long transaction, we could proactively go about, 216 00:10:50,660 --> 00:10:52,260 can we optimize those things? 217 00:10:52,440 --> 00:10:53,440 What are they doing? 218 00:10:53,860 --> 00:10:57,260 Like before they cause us problems, instead of kicking them out, 219 00:10:57,260 --> 00:10:57,900 we could. 220 00:10:57,900 --> 00:10:58,340 Nikolay: Yeah. 221 00:10:58,340 --> 00:10:58,620 Yeah. 222 00:10:58,620 --> 00:11:01,040 Well, I see sometimes people hesitate. 223 00:11:01,860 --> 00:11:05,320 Like we're usually in OLTP, we recommend very low values for 224 00:11:05,320 --> 00:11:06,340 all timeouts. 225 00:11:07,200 --> 00:11:11,520 Because my usual approach is why HTTP server has like 30 second 226 00:11:11,520 --> 00:11:13,000 timeout or 60 second. 227 00:11:13,200 --> 00:11:18,180 But on database, which is like even more dangerous to do something 228 00:11:18,180 --> 00:11:20,940 very long when people already don't need it. 229 00:11:21,100 --> 00:11:22,360 Why don't you limit it? 230 00:11:22,360 --> 00:11:22,580 Right? 231 00:11:22,580 --> 00:11:25,820 So we advise to limit very aggressively. 232 00:11:26,680 --> 00:11:28,940 But sometimes people hesitate, oh what happens? 233 00:11:29,240 --> 00:11:32,940 So Logging would be first thing, right? 234 00:11:33,480 --> 00:11:34,400 Michael: Cool, I get it. 235 00:11:34,400 --> 00:11:38,300 So like selling it as a change, it's easier to encourage people 236 00:11:38,300 --> 00:11:39,560 to start logging those. 237 00:11:39,920 --> 00:11:43,820 Nikolay: Yeah, of course naming will be hard as usual because 238 00:11:43,820 --> 00:11:46,540 I don't session, transaction_timeout, I remember. 239 00:11:47,360 --> 00:11:51,060 Yeah, this was 1 of the longest, maybe the longest name. 240 00:11:52,120 --> 00:11:53,560 And this also triggered my thoughts. 241 00:11:53,560 --> 00:11:56,060 It's slightly off topic, but let me share quickly. 242 00:11:56,580 --> 00:11:59,280 I think lock timeout is named very wrong. 243 00:12:00,060 --> 00:12:00,820 Very wrong. 244 00:12:01,200 --> 00:12:05,900 Because it's not lock timeout, it's lock acquisition timeout. 245 00:12:06,820 --> 00:12:11,700 It doesn't define duration of waiting for lock being held. 246 00:12:12,280 --> 00:12:18,780 Because once lock is acquired, This timeout is not in effect 247 00:12:18,820 --> 00:12:19,780 anymore, right? 248 00:12:19,940 --> 00:12:22,400 So it should be a lock acquisition timeout. 249 00:12:22,660 --> 00:12:24,060 Do we want lock timeout? 250 00:12:25,240 --> 00:12:26,140 Maybe no. 251 00:12:26,920 --> 00:12:28,980 Maybe log timeout would be needed additionally. 252 00:12:29,180 --> 00:12:32,460 So it's an interesting area when you try to think about systematically, 253 00:12:33,640 --> 00:12:38,860 yeah, like there are not implemented areas also and naming things 254 00:12:38,860 --> 00:12:40,220 and so on, but it's interesting. 255 00:12:40,380 --> 00:12:44,640 Anyway, I not consider this as negative feedback, I'm just thinking 256 00:12:44,640 --> 00:12:49,920 how to improve And maybe we will come up with new proposals and 257 00:12:49,920 --> 00:12:51,380 patches and so on. 258 00:12:51,780 --> 00:12:54,400 Michael: By the way, I think I agree on your overall sentiment 259 00:12:54,400 --> 00:12:59,540 that there's no 1 huge feature in Postgres 17. 260 00:13:00,060 --> 00:13:03,040 There are some big ones, like I think incremental backup was 261 00:13:03,040 --> 00:13:03,980 a lot of work. 262 00:13:05,740 --> 00:13:09,340 But it's not necessarily, it's not like when we got parallel 263 00:13:09,340 --> 00:13:12,380 query or when we first got declarative partitioning. 264 00:13:12,440 --> 00:13:15,060 I guess there's no kind of headline feature. 265 00:13:15,060 --> 00:13:19,940 But there's a couple of themes I thought from the release notes. 266 00:13:20,200 --> 00:13:24,820 1, I think is in most Postgres releases, but there's a lot of 267 00:13:24,820 --> 00:13:25,900 performance improvements. 268 00:13:26,040 --> 00:13:29,800 A lot, maybe small, but some maybe large like we talked to Peter 269 00:13:29,800 --> 00:13:31,560 about and like have been benchmarked. 270 00:13:31,560 --> 00:13:33,900 For some workloads, those could be large improvements. 271 00:13:34,220 --> 00:13:35,740 But it's not just that 1. 272 00:13:35,740 --> 00:13:40,460 There's quite a lot of performance, like individual commits and 273 00:13:40,680 --> 00:13:42,080 features around performance. 274 00:13:42,080 --> 00:13:43,860 Again, not for the first time. 275 00:13:44,440 --> 00:13:48,780 And another theme is there seem to be quite a lot of improvements 276 00:13:49,140 --> 00:13:50,640 around logical replication. 277 00:13:51,240 --> 00:13:52,300 Nikolay: I agree as well. 278 00:13:52,300 --> 00:13:52,800 Yeah. 279 00:13:53,420 --> 00:13:57,100 And it's it continues to be so in 16. 280 00:13:57,100 --> 00:13:58,760 We had also a lot of improvements. 281 00:14:01,560 --> 00:14:04,860 Anything particularly you want to discuss in these 2 areas? 282 00:14:05,500 --> 00:14:08,240 Michael: So performance, I'm gonna point people towards the blog 283 00:14:08,240 --> 00:14:08,600 posts. 284 00:14:08,600 --> 00:14:12,760 I think the crunch data 1 in particular is brilliant and Basically, 285 00:14:12,780 --> 00:14:19,080 the summary of that is for certain I think largely They I think 286 00:14:19,080 --> 00:14:20,880 they might that might be a Rails application. 287 00:14:21,040 --> 00:14:24,100 If not, I think it's an ORM based 1. 288 00:14:24,280 --> 00:14:25,360 I need to check. 289 00:14:26,000 --> 00:14:30,140 But for certain workloads, even the preliminary work that Peter 290 00:14:30,140 --> 00:14:34,540 Geoghegan and Matthias van de Meent have done, Obviously, Peter said 291 00:14:34,540 --> 00:14:38,160 it could be any amount, but it could even be 20 to 30% on some 292 00:14:38,160 --> 00:14:40,960 workloads globally, like across the whole workload. 293 00:14:41,180 --> 00:14:45,320 I know individual queries could be no faster or thousands of 294 00:14:45,320 --> 00:14:47,860 times faster depending on the individual cases. 295 00:14:48,080 --> 00:14:53,000 But those kinds of numbers, 20 to 30% on a real endpoint, is 296 00:14:53,000 --> 00:14:53,500 massive. 297 00:14:53,500 --> 00:14:56,200 Like that, you don't need to do anything. 298 00:14:56,200 --> 00:14:59,380 As a reminder, other than upgrading Postgres, you don't need 299 00:14:59,380 --> 00:15:01,660 to change your code and you get that improvement. 300 00:15:02,280 --> 00:15:04,040 It's quite remarkable, I think. 301 00:15:04,300 --> 00:15:08,300 Nikolay: Yeah, there are optimizations in the area of aggregate 302 00:15:08,300 --> 00:15:12,940 functions and also parallel processing, right? 303 00:15:12,940 --> 00:15:16,860 And not to forget optimizations in the vacuum. 304 00:15:17,840 --> 00:15:20,240 Multiple optimizations from Melanie, right? 305 00:15:20,740 --> 00:15:24,120 Michael: Yes, so yes, on top of the work that we discussed with 306 00:15:24,120 --> 00:15:29,540 Peter around in-list optimizations, basically the starts of the 307 00:15:29,540 --> 00:15:30,640 skip-scan work. 308 00:15:30,720 --> 00:15:33,640 Yeah, we've got vacuum should be more efficient, not just through 309 00:15:33,640 --> 00:15:35,980 the work Melanie did, but a bunch of other ones. 310 00:15:35,980 --> 00:15:40,200 I think analyze as well can now use like the, like some defaults 311 00:15:40,200 --> 00:15:44,560 have been changed as 1 that's been bumped from 256 kilobytes 312 00:15:44,560 --> 00:15:48,840 to 2 megabytes on the shared buffer ring that can be used for 313 00:15:48,840 --> 00:15:49,760 vacuum and analyze. 314 00:15:49,760 --> 00:15:52,760 So in a bunch of cases, they'll run faster without you having 315 00:15:52,760 --> 00:15:53,800 to change anything. 316 00:15:54,240 --> 00:15:58,340 For huge vacuums, I think, I think was it, the men, what Melanie 317 00:15:58,340 --> 00:16:00,600 did should really help with huge tables. 318 00:16:01,640 --> 00:16:05,100 So yeah, there's, there's tons of improvements around that stuff. 319 00:16:05,280 --> 00:16:08,400 At the parallelism 1, I saw Tom Lane made a change that should 320 00:16:08,400 --> 00:16:11,800 allow more types of operation to run in parallel, which is pretty 321 00:16:11,800 --> 00:16:12,300 cool. 322 00:16:12,940 --> 00:16:13,440 Yeah. 323 00:16:14,100 --> 00:16:15,960 So yeah, I'm not sure. 324 00:16:16,680 --> 00:16:18,940 Obviously, it's going to depend on individual workloads, it'd 325 00:16:18,940 --> 00:16:21,820 be great to hear some real-world stories of people that do test 326 00:16:21,820 --> 00:16:24,320 these upgrades, and those are always really helpful and encourage 327 00:16:24,320 --> 00:16:25,680 others to upgrade as well. 328 00:16:25,680 --> 00:16:29,280 Nikolay: We already started talking to our AI assistant, like, 329 00:16:29,280 --> 00:16:30,980 let's benchmark something. 330 00:16:31,700 --> 00:16:37,280 And, like, I maybe should start this work earlier, but maybe 331 00:16:37,700 --> 00:16:40,360 now we will find some examples. 332 00:16:41,720 --> 00:16:46,160 I think we will maybe start from analysis of mailing lists and 333 00:16:46,300 --> 00:16:47,660 find some interesting examples. 334 00:16:47,660 --> 00:16:51,760 But if there are real workload scenarios, it's also interesting 335 00:16:51,780 --> 00:16:52,280 if... 336 00:16:53,200 --> 00:16:54,980 I'm hunting for this, honestly. 337 00:16:55,640 --> 00:17:00,100 What example could demonstrate the benefits from 17 so we could 338 00:17:00,100 --> 00:17:02,480 reproduce it in synthetic environment? 339 00:17:02,960 --> 00:17:04,680 It's always tricky, actually. 340 00:17:04,900 --> 00:17:09,100 When you have a live system, how to extract a piece of it and 341 00:17:09,100 --> 00:17:12,220 then have fake data and so on, it's like always tricky. 342 00:17:12,940 --> 00:17:17,200 But I think something in the area of vacuuming definitely should 343 00:17:17,200 --> 00:17:17,980 be analyzed. 344 00:17:18,200 --> 00:17:22,800 For example, I remember 1 of optimizations was how vacuuming 345 00:17:23,760 --> 00:17:27,260 of tables without indexes is done. 346 00:17:27,260 --> 00:17:31,480 But I doubt I will see, if I see some table without index in 347 00:17:31,480 --> 00:17:35,500 production, I will raise questions of course, but yeah, but this 348 00:17:35,500 --> 00:17:38,960 is 1 of the things to check maybe right? 349 00:17:39,220 --> 00:17:41,880 Michael: It must be rare to have tables without indexes, but 350 00:17:41,880 --> 00:17:45,620 I imagine that people have done that extremely deliberately, 351 00:17:45,620 --> 00:17:48,480 like Anybody that does have a table without indexes, that means 352 00:17:48,480 --> 00:17:49,760 there's no primary key. 353 00:17:49,760 --> 00:17:50,640 There's so many... 354 00:17:50,640 --> 00:17:51,960 Nikolay: Yeah, no logical... 355 00:17:52,040 --> 00:17:52,540 Michael: What? 356 00:17:52,700 --> 00:17:53,200 Yeah. 357 00:17:53,440 --> 00:17:57,280 Nikolay: If you don't use a replica identity full, which is not 358 00:17:57,280 --> 00:17:58,660 good sometimes, and so on. 359 00:17:58,660 --> 00:17:59,160 Yeah. 360 00:17:59,200 --> 00:18:03,540 So you mentioned some settings, and I must return to Andrei Borodin 361 00:18:03,680 --> 00:18:08,940 and another his contribution, which was under development many 362 00:18:08,940 --> 00:18:09,440 years. 363 00:18:10,240 --> 00:18:15,420 This is ability to configure the size of various SLRU caches. 364 00:18:17,520 --> 00:18:22,360 I think 2021 I wrote this article, right? 365 00:18:22,360 --> 00:18:26,820 It was related to incidents GitLab had with sub-transactions. 366 00:18:28,380 --> 00:18:32,060 At that time, this patch was, I think, version 13, 14. 367 00:18:32,380 --> 00:18:33,940 This patch had many versions. 368 00:18:34,540 --> 00:18:37,460 So the ability to increase some caches. 369 00:18:38,560 --> 00:18:42,660 And one of the problems we analyzed at that time was not a good 370 00:18:42,660 --> 00:18:47,280 problem when on the primary We had long transactions, we used 371 00:18:47,280 --> 00:18:49,260 sub-transactions, and suddenly we have... 372 00:18:49,540 --> 00:18:53,900 We see at some point, we see degradation on all standbys because 373 00:18:54,140 --> 00:18:55,740 SLRU cache is overflown. 374 00:18:57,040 --> 00:19:01,640 But I must admit, at that time, when We tested the patch and 375 00:19:01,640 --> 00:19:05,860 we tried to understand how the bigger size of SLRU cache will 376 00:19:05,860 --> 00:19:06,360 help. 377 00:19:06,600 --> 00:19:10,680 And in some cases it helped, but I made it 3 years ago already, 378 00:19:10,680 --> 00:19:14,500 so I don't remember all the details, but as I remember, it was 379 00:19:14,500 --> 00:19:15,000 tricky. 380 00:19:15,160 --> 00:19:16,900 Not like linear effect. 381 00:19:17,120 --> 00:19:21,980 You double this and you have 2 more seconds until the problem 382 00:19:22,640 --> 00:19:23,440 hits you. 383 00:19:23,800 --> 00:19:28,220 So I think this is a good area also to check, but it requires 384 00:19:28,380 --> 00:19:30,100 a two-node setup. 385 00:19:30,180 --> 00:19:31,760 You need to do it on replica. 386 00:19:31,960 --> 00:19:34,700 That particular problem you need to inspect on replica. 387 00:19:35,140 --> 00:19:38,040 Unfortunately, our AI cannot do such experiments yet. 388 00:19:38,040 --> 00:19:40,400 I think maybe we should implement this. 389 00:19:40,640 --> 00:19:44,940 But it's not only about sub-transaction SLRU, it's also about 390 00:19:45,140 --> 00:19:47,860 multi-exact buffers and so on. 391 00:19:48,400 --> 00:19:53,280 So it's interesting to see various performance cliffs and how 392 00:19:53,460 --> 00:19:59,640 we can mitigate them now in Postgres 17 because if we have some 393 00:19:59,640 --> 00:20:05,860 production incident related to multi-exact IDs and SLRU buffer 394 00:20:05,860 --> 00:20:08,820 overflow, we can just increase it, right? 395 00:20:08,940 --> 00:20:14,700 At least to postpone this, like to shift this cliff and think 396 00:20:14,700 --> 00:20:17,980 how to improve and avoid it completely. 397 00:20:18,720 --> 00:20:19,860 Michael: Yeah, it's really cool. 398 00:20:19,860 --> 00:20:22,700 I see that, reading the release notes, I see that it comes with 399 00:20:22,700 --> 00:20:24,300 7 new parameters. 400 00:20:24,920 --> 00:20:25,320 Nikolay: Yeah. 401 00:20:25,320 --> 00:20:28,220 Michael: But crucially and I think this I think this is really 402 00:20:28,220 --> 00:20:32,140 important 3 of those scale up automatically with shared buffers. 403 00:20:32,200 --> 00:20:34,640 Nikolay: Yeah that's really cool. 404 00:20:35,900 --> 00:20:40,680 Michael: But that means that means I think fewer people will 405 00:20:40,680 --> 00:20:42,560 run into this in the first place. 406 00:20:43,940 --> 00:20:47,220 Because we have to tune shared buffers, and all cloud providers 407 00:20:47,220 --> 00:20:51,700 do by default, that means that these are going to be increased 408 00:20:51,700 --> 00:20:53,600 by default as well, which is really cool. 409 00:20:53,920 --> 00:20:57,700 Nikolay: Yeah, I wanted to check why upgrade by depesz, which 410 00:20:58,040 --> 00:21:01,240 also provides the list of configuration changes, but unfortunately, 411 00:21:01,340 --> 00:21:03,440 it doesn't support 17 yet. 412 00:21:03,920 --> 00:21:06,780 Michael: I meant, well, we're bear in mind, we're recording a 413 00:21:06,780 --> 00:21:07,580 few days ago. 414 00:21:07,580 --> 00:21:11,960 So there's a very good chance that depesz is great at new version 415 00:21:11,960 --> 00:21:12,460 support. 416 00:21:12,900 --> 00:21:17,540 Nikolay: I always use that tool and that website and suggest 417 00:21:17,860 --> 00:21:19,820 our clients to use it. 418 00:21:19,840 --> 00:21:21,660 So it's very helpful indeed. 419 00:21:21,780 --> 00:21:22,280 Yeah. 420 00:21:22,720 --> 00:21:23,220 Yeah. 421 00:21:23,300 --> 00:21:24,100 So what else? 422 00:21:24,100 --> 00:21:26,760 What else do you want to discuss in this release? 423 00:21:26,980 --> 00:21:28,480 Michael: Well, I've got a couple of things. 424 00:21:28,480 --> 00:21:31,100 In fact, we didn't really talk that much about logical replication. 425 00:21:31,100 --> 00:21:32,380 There's a bunch of changes. 426 00:21:32,560 --> 00:21:38,200 And I feel like probably the most important is that logical slots 427 00:21:38,200 --> 00:21:41,420 will now still exist after failover. 428 00:21:41,780 --> 00:21:42,780 Nikolay: Yeah, that's great. 429 00:21:43,140 --> 00:21:47,640 In 16, it was already possible to use logical slots on physical 430 00:21:47,640 --> 00:21:48,140 standbys. 431 00:21:49,900 --> 00:21:53,160 But if failover happens or switchover actually, it doesn't matter 432 00:21:53,200 --> 00:21:54,740 if it's planned or not. 433 00:21:54,800 --> 00:21:58,440 The biggest problem with logical replication, you always lose 434 00:21:58,480 --> 00:21:58,980 it. 435 00:21:59,440 --> 00:22:02,940 You lose logical replication if failover happens or switchover. 436 00:22:03,480 --> 00:22:07,440 And this means that you need to start from scratch with initialization. 437 00:22:07,720 --> 00:22:08,420 It's nightmare. 438 00:22:09,060 --> 00:22:14,900 And of course recipes existed to solve this, but they were not 439 00:22:14,900 --> 00:22:15,400 official. 440 00:22:15,780 --> 00:22:19,260 For example, Patroni supported it for a couple of years already, 441 00:22:20,040 --> 00:22:22,180 and in Patroni this problem was solved. 442 00:22:22,740 --> 00:22:28,940 But now it's solved in core, in 17, so if a lower is not a headache 443 00:22:28,940 --> 00:22:29,440 anymore. 444 00:22:29,820 --> 00:22:32,600 But I must admit, logically it's tricky. 445 00:22:32,600 --> 00:22:37,160 There are many issues, and like, not obvious issues sometimes. 446 00:22:37,380 --> 00:22:40,360 As I remember, the problem with restarts is not yet solved. 447 00:22:40,520 --> 00:22:41,700 The problem with duplicates. 448 00:22:42,340 --> 00:22:47,460 During restart, you might see the same changes coming in CDC 449 00:22:47,640 --> 00:22:48,660 stream again. 450 00:22:49,400 --> 00:22:54,100 And if on the subscriber you don't have a unique key, nobody 451 00:22:54,100 --> 00:22:56,920 can check them and you end up having duplicates. 452 00:22:57,540 --> 00:23:01,280 I think this problem is not, it's maybe a very hard problem to 453 00:23:01,280 --> 00:23:06,440 solve because a slot position, LSN position may be lagging a 454 00:23:06,440 --> 00:23:09,100 little bit and during restart you might be losing. 455 00:23:09,140 --> 00:23:12,160 I might be mistaken but I remember some practical problems in 456 00:23:12,160 --> 00:23:12,880 this area. 457 00:23:13,380 --> 00:23:15,660 Michael: Well, and we were talking just before the call, like 458 00:23:15,660 --> 00:23:20,880 there are still the major limitations that are worth remembering 459 00:23:20,920 --> 00:23:26,400 are that sequences aren't synchronized and DDL, of course. 460 00:23:27,520 --> 00:23:33,080 But I can imagine, let's say, in a few versions time, if each 461 00:23:33,080 --> 00:23:35,460 of those gradually get ticked off, we're gonna... 462 00:23:35,460 --> 00:23:36,860 Nikolay: Because there's work in progress. 463 00:23:37,060 --> 00:23:41,340 Yeah, commit fests have it for quite some time and I hope... 464 00:23:41,420 --> 00:23:44,080 For example, a sequence, I just checked it. 465 00:23:45,060 --> 00:23:50,380 The patch for sequence replication and logical replication is 466 00:23:50,380 --> 00:23:51,640 waiting for review. 467 00:23:52,040 --> 00:23:53,300 So let's advertise it. 468 00:23:53,300 --> 00:23:56,620 If someone wants to help, this is a good point. 469 00:23:57,340 --> 00:23:58,560 And people need it. 470 00:23:58,780 --> 00:24:01,860 1 of the biggest concerns with logical is lack of replication 471 00:24:01,880 --> 00:24:06,920 of sequences DDL and the third 1 was fell over but finally 17 472 00:24:06,940 --> 00:24:10,760 solves it but we had some like 5 minutes of preparation before 473 00:24:10,760 --> 00:24:17,280 the show and you mentioned that this feature ah no there is 1 474 00:24:17,280 --> 00:24:20,440 more sorry upgrades right you mentioned about upgrades 475 00:24:20,460 --> 00:24:20,960 Michael: yeah 476 00:24:21,980 --> 00:24:25,760 Nikolay: so slot is not lost so it is not lost when I'm great 477 00:24:25,760 --> 00:24:26,500 you upgrade. 478 00:24:26,720 --> 00:24:31,500 This is another big thing, because usually if we perform a major 479 00:24:31,500 --> 00:24:34,200 upgrade on Postgres, again, logical slots are lost. 480 00:24:34,200 --> 00:24:36,460 We need to reinitialize it. 481 00:24:36,460 --> 00:24:37,360 Again, headache. 482 00:24:37,780 --> 00:24:41,500 But now it's supported in 17, and you mentioned very correctly 483 00:24:41,520 --> 00:24:46,440 that it's supported only for very distant future because the 484 00:24:47,100 --> 00:24:52,240 previous version should be 17, so it will be beneficial only 485 00:24:53,140 --> 00:24:56,080 in future upgrades to 18 and so on. 486 00:24:56,640 --> 00:24:57,380 Michael: Yeah, exactly. 487 00:24:57,380 --> 00:24:59,840 Or from 17, whichever, yeah. 488 00:25:00,420 --> 00:25:02,780 Nikolay: We cannot upgrade from 17 yet, anywhere. 489 00:25:03,640 --> 00:25:04,140 Michael: Exactly. 490 00:25:04,440 --> 00:25:07,200 Nikolay: So we cannot use this feature this year, basically, 491 00:25:07,200 --> 00:25:07,860 at all. 492 00:25:08,000 --> 00:25:11,180 Michael: But it makes sense, like, this is how it had to be implemented, 493 00:25:11,400 --> 00:25:11,680 right? 494 00:25:11,680 --> 00:25:13,320 Like, unless you backpatch it. 495 00:25:13,320 --> 00:25:15,220 Nikolay: Strategic move, right? 496 00:25:15,820 --> 00:25:18,840 Michael: Yeah, it makes perfect sense, and it means in the future. 497 00:25:20,020 --> 00:25:22,100 The world moves remarkably quickly. 498 00:25:22,660 --> 00:25:26,320 I remember when Postgres versions came out that are almost out 499 00:25:26,320 --> 00:25:27,320 of support now. 500 00:25:28,780 --> 00:25:31,560 It takes 5 years, but that can fly by. 501 00:25:31,560 --> 00:25:34,780 So I imagine we'll be using that quite soon. 502 00:25:35,260 --> 00:25:36,960 Nikolay: Yeah, can you imagine I like... 503 00:25:37,120 --> 00:25:37,620 Yeah. 504 00:25:37,740 --> 00:25:42,440 My contribution, XML, not mine, like I was a little bit... 505 00:25:42,440 --> 00:25:42,660 I know 506 00:25:42,660 --> 00:25:43,480 Michael: what you mean. 507 00:25:43,780 --> 00:25:44,850 Nikolay: But it was 8.3. 508 00:25:44,850 --> 00:25:45,820 Yeah, wow. 509 00:25:46,120 --> 00:25:46,620 Michael: 2008, 510 00:25:46,920 --> 00:25:48,220 Nikolay: so yeah. 511 00:25:48,520 --> 00:25:49,320 I'm already... 512 00:25:50,380 --> 00:25:52,220 Yeah, and there are people who? 513 00:25:52,680 --> 00:25:57,220 Remember even the beginning so yeah, it's interesting how many 514 00:25:57,340 --> 00:26:00,860 how much work is done and consistent work, right? 515 00:26:01,060 --> 00:26:01,860 It's great. 516 00:26:02,420 --> 00:26:05,260 But let me criticize a little bit once again. 517 00:26:05,320 --> 00:26:10,780 I have no idea why pg_wait_events view is called so. 518 00:26:11,480 --> 00:26:12,940 And this is a dictionary. 519 00:26:13,140 --> 00:26:14,960 This is like just a list of... 520 00:26:14,960 --> 00:26:19,320 Of course we need a dictionary because so many wait events, right? 521 00:26:20,140 --> 00:26:23,940 Michael: And crucially I think it allows external providers to 522 00:26:23,940 --> 00:26:26,920 add, they can now add wait events and name and like give you 523 00:26:26,920 --> 00:26:28,020 information about them. 524 00:26:28,020 --> 00:26:29,280 So if you're an extension, 525 00:26:29,440 --> 00:26:30,260 Nikolay: that's great, 526 00:26:30,420 --> 00:26:31,900 Michael: but that is a really cool. 527 00:26:32,120 --> 00:26:35,900 Nikolay: But it's like there is pglogs for example and it's not 528 00:26:35,900 --> 00:26:37,320 a list of logs. 529 00:26:38,180 --> 00:26:39,240 Michael: I see what you mean. 530 00:26:39,520 --> 00:26:39,720 So 531 00:26:39,720 --> 00:26:41,080 Nikolay: something is with naming. 532 00:26:41,080 --> 00:26:45,100 Like naming is 1 of the 2 hardest computer science problems we 533 00:26:45,100 --> 00:26:45,600 know. 534 00:26:45,600 --> 00:26:46,780 Okay, so that's it. 535 00:26:46,780 --> 00:26:47,720 Like this is my feedback. 536 00:26:47,720 --> 00:26:53,800 It's great to have a list, but maybe yeah, I expect some confusions 537 00:26:54,720 --> 00:26:55,620 in this area. 538 00:26:56,040 --> 00:26:59,860 Michael: I think naming is hard even in a project where you have 539 00:26:59,860 --> 00:27:04,000 like a benevolent dictator or only a single developer, naming 540 00:27:04,000 --> 00:27:04,940 is already difficult. 541 00:27:05,280 --> 00:27:11,120 Naming in a distributed project when you can't really expect 542 00:27:11,120 --> 00:27:14,140 everybody to be aware of, well I mean this might be an exception, 543 00:27:14,140 --> 00:27:17,240 this might be 1 that more people could have caught, But I think 544 00:27:17,240 --> 00:27:19,920 it's extremely hard in a distributed project. 545 00:27:19,920 --> 00:27:23,100 Nikolay: And in this area, another example of issues with naming 546 00:27:23,100 --> 00:27:27,620 is there is a wait event called pg_stat_activity, and it can be 547 00:27:27,620 --> 00:27:28,120 null. 548 00:27:28,600 --> 00:27:34,240 And many, Many monitoring systems visualize it as a green area. 549 00:27:35,340 --> 00:27:39,300 I think it came from Oracle maybe, but RDS does it with performance 550 00:27:39,320 --> 00:27:39,820 insights. 551 00:27:40,240 --> 00:27:41,520 PASH Viewer does it. 552 00:27:41,520 --> 00:27:45,080 This is Java application, which is very useful if you don't have 553 00:27:45,080 --> 00:27:47,080 persistent monitoring and just ad hoc. 554 00:27:47,080 --> 00:27:47,260 Michael: You 555 00:27:47,260 --> 00:27:49,140 Nikolay: can use that as an ad hoc tool. 556 00:27:49,540 --> 00:27:50,720 And they say CPU. 557 00:27:51,220 --> 00:27:52,440 Michael: Yeah, I remember that. 558 00:27:52,440 --> 00:27:53,560 Nikolay: We talked about this, right? 559 00:27:53,560 --> 00:27:54,060 CPU. 560 00:27:54,240 --> 00:27:57,940 So this very release is confirmation that I'm right. 561 00:27:58,300 --> 00:27:58,600 Okay. 562 00:27:58,600 --> 00:28:02,800 We have CPU when we see null, But Postgres documentation says 563 00:28:02,800 --> 00:28:05,240 null means not waiting. 564 00:28:05,900 --> 00:28:10,400 But from time to time, we see new wait event is added. 565 00:28:11,400 --> 00:28:15,740 And here we also see it for, I think, checkpoint waiting for 566 00:28:15,740 --> 00:28:16,900 checkpoint or something. 567 00:28:17,640 --> 00:28:20,120 Yeah, there is something, right? 568 00:28:21,220 --> 00:28:24,480 Custom, ah, yeah, extension to define custom wait events it's 569 00:28:24,480 --> 00:28:24,980 great 570 00:28:25,240 --> 00:28:25,740 Michael: yes 571 00:28:26,120 --> 00:28:31,580 Nikolay: but if we don't define it we have null right And monitoring 572 00:28:31,680 --> 00:28:38,360 systems visualize it as CPU but also this release adds Checkpoint 573 00:28:38,360 --> 00:28:38,860 delays. 574 00:28:39,240 --> 00:28:41,760 Michael: I blame the monitoring systems for that not yeah. 575 00:28:41,760 --> 00:28:41,880 Nikolay: Yeah. 576 00:28:41,880 --> 00:28:44,100 Yeah, of course, of course It's like this. 577 00:28:44,100 --> 00:28:48,280 I'm just explaining the problem with naming and it's it's very 578 00:28:48,280 --> 00:28:50,020 hard to escape from this problem. 579 00:28:50,020 --> 00:28:51,680 I recently participated again. 580 00:28:51,820 --> 00:28:52,780 So let me explain. 581 00:28:53,520 --> 00:28:57,320 Wait event is null means not waiting according to documentation. 582 00:28:57,780 --> 00:29:00,040 But it's not fully fair. 583 00:29:00,060 --> 00:29:02,180 And this is a problem of Postgres documentation. 584 00:29:02,440 --> 00:29:06,500 It should say, not waiting or waiting on wait event which is 585 00:29:06,500 --> 00:29:08,200 not yet defined. 586 00:29:09,280 --> 00:29:10,020 Michael: Or unknown. 587 00:29:10,920 --> 00:29:11,880 Nikolay: Unknown, right. 588 00:29:12,200 --> 00:29:13,760 This is null meaning unknown. 589 00:29:14,200 --> 00:29:16,120 Well, maybe, yeah, it's hard. 590 00:29:16,120 --> 00:29:20,140 But because it's about code coverage, we need to cover more and 591 00:29:20,140 --> 00:29:23,800 more code with weight event analysis, which is great. 592 00:29:25,720 --> 00:29:29,600 I was super skeptical 3, 4 years ago. 593 00:29:29,600 --> 00:29:33,640 Now I think weight event analysis should be in the center of 594 00:29:33,640 --> 00:29:34,400 many dashboards. 595 00:29:35,420 --> 00:29:41,120 So, then monitoring systems just make another step in this confusion 596 00:29:41,120 --> 00:29:43,760 and say CPU, green area, CPU. 597 00:29:44,580 --> 00:29:49,640 And when we sometimes try to find correlation between spikes 598 00:29:49,640 --> 00:29:58,080 of CPU usage and CPU areas when we also apply segmentation by 599 00:29:58,080 --> 00:30:00,860 query ID, we cannot find what's happening. 600 00:30:00,920 --> 00:30:06,260 And this release adds new wait events for checkpoint delays by 601 00:30:06,260 --> 00:30:07,180 Thomas Munro. 602 00:30:08,000 --> 00:30:13,760 So it means that before 17 it was null, right? 603 00:30:13,980 --> 00:30:15,780 And in monitoring it was CPU. 604 00:30:16,100 --> 00:30:17,620 There's big confusion here. 605 00:30:17,720 --> 00:30:20,580 So what should be done in documentation, probably it should be 606 00:30:20,580 --> 00:30:25,100 saying, as you said, or unknown, or unknown wait event. 607 00:30:25,520 --> 00:30:30,060 But in monitoring systems, it should be CPU or some unknown wait 608 00:30:30,060 --> 00:30:30,560 event. 609 00:30:31,780 --> 00:30:36,820 But recently I participated in some design of the board and like 610 00:30:36,820 --> 00:30:40,820 we discussed this and it's really hard like it's very tempting 611 00:30:40,960 --> 00:30:45,020 to pull just 3 letters CPU and that's it but at least it should 612 00:30:45,020 --> 00:30:48,780 be CPU like you know asterisk and some remark somewhere right? 613 00:30:50,660 --> 00:30:54,560 Michael: I think so it depends I find this is like a really difficult 614 00:30:54,560 --> 00:30:57,740 1 where you want to you either want to try and simplify things 615 00:30:57,740 --> 00:31:01,480 for people and sometimes it's okay to lose a bit of the truth 616 00:31:01,480 --> 00:31:04,860 when you're simplifying things, but in other cases it really 617 00:31:04,860 --> 00:31:08,420 isn't, or it's harmful to remove that detail. 618 00:31:08,480 --> 00:31:11,180 And I think this is 1 of those cases where it might be harmful. 619 00:31:11,460 --> 00:31:13,340 But yeah, tricky 1. 620 00:31:13,520 --> 00:31:15,860 Nikolay: But it's great that This is extended. 621 00:31:15,920 --> 00:31:19,120 It's great that we have a dictionary of all the events now. 622 00:31:19,120 --> 00:31:27,720 And it's also great that extensions can extend this wait event 623 00:31:27,720 --> 00:31:28,520 list additionally. 624 00:31:29,060 --> 00:31:32,380 We already saw pg_stat_statements wait event, I think, starting 625 00:31:32,380 --> 00:31:33,960 in 2016 or 2015. 626 00:31:34,740 --> 00:31:38,260 Because before it was also null and CPU, but then you see, oh, 627 00:31:38,260 --> 00:31:40,240 It's contention at pg_stat_statements. 628 00:31:40,240 --> 00:31:44,160 Remember this episode about 4 million TPS. 629 00:31:45,660 --> 00:31:50,720 So yeah, I expect more and more will be covered, but I encourage 630 00:31:50,760 --> 00:31:54,940 monitoring system developers to put at least asterisk and remark. 631 00:31:55,160 --> 00:31:57,380 That it's not a CPU maybe. 632 00:31:58,380 --> 00:32:01,620 We can name it CPU maybe in parentheses. 633 00:32:03,100 --> 00:32:03,600 Right? 634 00:32:04,280 --> 00:32:05,520 Michael: Yeah, I quite like that. 635 00:32:06,280 --> 00:32:07,280 Yeah, or probably. 636 00:32:09,240 --> 00:32:09,640 Nikolay: Yeah. 637 00:32:09,640 --> 00:32:12,620 But we will discuss that it's CPU and it's actually doing some 638 00:32:12,620 --> 00:32:15,780 work, but maybe it's some wait event which is not yet already 639 00:32:16,520 --> 00:32:17,440 defined, right? 640 00:32:17,700 --> 00:32:19,500 So yeah, it's interesting. 641 00:32:20,080 --> 00:32:24,140 Many small changes, including in observability related things, 642 00:32:24,140 --> 00:32:28,580 like I expect monitoring systems will deal with renaming and 643 00:32:28,580 --> 00:32:29,440 so on again. 644 00:32:30,460 --> 00:32:32,800 Michael: And Lots of changes there, to be fair. 645 00:32:32,800 --> 00:32:35,900 And even to the views, there's a new view, I think, as well, 646 00:32:35,900 --> 00:32:39,060 but I think it's mostly columns from a different view, because 647 00:32:39,060 --> 00:32:41,080 it didn't make sense for them to live there. 648 00:32:41,600 --> 00:32:43,940 Nikolay: I wanted to congratulate Andrei Borodin, and also I 649 00:32:43,940 --> 00:32:48,780 wanted to congratulate all checkpoints, which now have their 650 00:32:48,780 --> 00:32:51,320 own system view pgstat_checkpointer. 651 00:32:52,540 --> 00:32:56,280 The first to leave, like, information about checkpoints was always 652 00:32:56,280 --> 00:32:59,840 in pg_stat_buffercache, which was ridiculous and unclear. 653 00:33:00,200 --> 00:33:01,360 I saw many confusions. 654 00:33:01,860 --> 00:33:03,020 Finally, it's resolved. 655 00:33:03,240 --> 00:33:03,740 Great. 656 00:33:04,460 --> 00:33:06,300 Michael: Was it in bgwriter or... 657 00:33:06,300 --> 00:33:08,080 Nikolay: bgwriter, sorry, not buffercache. 658 00:33:08,080 --> 00:33:09,780 I was just looking at... 659 00:33:09,780 --> 00:33:13,760 Yeah, Background Writer, which is like additional mechanism, 660 00:33:14,220 --> 00:33:16,800 but checkpointer is the main mechanism, right? 661 00:33:17,320 --> 00:33:23,860 But stats for both until 17 was present in pg_stat_bgwriter. 662 00:33:24,600 --> 00:33:30,200 Now we have 2 separate system use and no more confusion, which 663 00:33:30,200 --> 00:33:30,820 is great. 664 00:33:31,440 --> 00:33:36,500 But more work on shoulders of monitoring system developers. 665 00:33:37,120 --> 00:33:40,900 Michael: Yeah, and I don't envy those folks that are having to 666 00:33:40,900 --> 00:33:45,200 update for Postgres 17, but there's definitely some already have 667 00:33:45,200 --> 00:33:45,940 done it. 668 00:33:46,020 --> 00:33:50,240 Like I was checking out pgwatch2 and they've got a branch for 669 00:33:50,240 --> 00:33:52,440 it, I don't know if it's been merged yet. 670 00:33:53,740 --> 00:33:54,880 Nikolay: pgwatch2 671 00:33:55,080 --> 00:33:55,780 is dead. 672 00:33:56,200 --> 00:33:59,800 pgwatch3 is being developed and it was renamed to pgwatch 673 00:33:59,800 --> 00:34:01,120 without numbers. 674 00:34:01,840 --> 00:34:04,780 Michael: Oh cool, well on their pgwatch2 repository. 675 00:34:05,500 --> 00:34:09,280 Nikolay: Okay it's not dead but the focus is yeah. 676 00:34:09,280 --> 00:34:14,340 It's not bad, like recently I guess Paolo Golub renamed it to 677 00:34:14,340 --> 00:34:18,280 pgwatch without numbers so no more confusion in the future but 678 00:34:18,280 --> 00:34:23,100 as I understand it's not yet fully released and it may be it's 679 00:34:23,100 --> 00:34:27,500 maybe what you talk about is in the old pgwatch2 and we 680 00:34:27,500 --> 00:34:31,160 maintain the fork of pgwatch with our own dashboards looking 681 00:34:31,160 --> 00:34:35,280 forward to merging efforts, uniting efforts in the future with 682 00:34:35,280 --> 00:34:36,100 main pgwatch. 683 00:34:37,420 --> 00:34:40,140 Michael: Before you have to add Postgres 17 support yourself? 684 00:34:41,040 --> 00:34:42,940 Nikolay: I don't know honestly I just... 685 00:34:44,320 --> 00:34:48,140 Thankfully with AI it's faster now you say this is my code this 686 00:34:48,140 --> 00:34:51,580 is changes like help me to develop the change and so on. 687 00:34:51,580 --> 00:34:52,060 Yeah, 688 00:34:52,060 --> 00:34:53,040 Michael: so it's because it's 689 00:34:53,040 --> 00:34:54,780 Nikolay: quite mechanical, this change. 690 00:34:55,520 --> 00:34:58,160 Michael: Actually, before we move on to support from various 691 00:34:58,620 --> 00:35:01,400 others, I actually haven't mentioned my favorite feature. 692 00:35:01,400 --> 00:35:02,080 Well, maybe not. 693 00:35:02,080 --> 00:35:04,900 I don't think it's the most important feature in Postgres 17 694 00:35:04,900 --> 00:35:05,500 by a long way. 695 00:35:05,500 --> 00:35:08,220 But I think it might be the 1 I end up using the most. 696 00:35:08,380 --> 00:35:12,080 And I don't know if you saw this, but you can now provide parameters 697 00:35:12,440 --> 00:35:14,640 to the random function. 698 00:35:15,100 --> 00:35:17,460 And you can do them, for example, in integers. 699 00:35:17,480 --> 00:35:22,980 So you could do random 1, 100, and you get a random integer between 700 00:35:23,080 --> 00:35:23,940 1 and 100. 701 00:35:23,940 --> 00:35:24,440 Nikolay: Yeah. 702 00:35:24,820 --> 00:35:27,660 But it's just syntax sugar, it's convenience. 703 00:35:27,840 --> 00:35:29,200 Michael: I know, I know. 704 00:35:29,200 --> 00:35:34,600 But I think the amount of times in example code and writing or 705 00:35:34,600 --> 00:35:38,460 even in real things, you're having to then multiply that by a 706 00:35:38,460 --> 00:35:42,620 number and then do like seal or floor or like, it's just like 707 00:35:42,620 --> 00:35:45,060 a load of cruft that you have to put around something. 708 00:35:45,060 --> 00:35:48,100 And I think it makes it less clear in like a blog post or something 709 00:35:48,160 --> 00:35:49,780 exactly what you're doing there. 710 00:35:49,780 --> 00:35:52,980 So I really I think I'll end up using that the most. 711 00:35:53,440 --> 00:35:59,180 Nikolay: Yeah, also, let me let me return us to the area of confusion 712 00:35:59,280 --> 00:35:59,780 resolution. 713 00:36:00,760 --> 00:36:05,820 I remember how I shoot several feet of myself in the past using 714 00:36:06,460 --> 00:36:10,560 hyphen D originally with psql because it's database right like 715 00:36:10,560 --> 00:36:14,360 it's it's not not convenient to specify database without any 716 00:36:14,360 --> 00:36:19,520 options. So I got used to using the "-d" database. 717 00:36:20,420 --> 00:36:25,620 And then, suddenly, I had some nasty situations when with pgbench, 718 00:36:26,200 --> 00:36:29,360 if you use "-d," something goes very wrong. 719 00:36:30,100 --> 00:36:32,140 Because there it means debug. 720 00:36:33,340 --> 00:36:33,840 Michael: Oh. 721 00:36:34,540 --> 00:36:35,040 Nikolay: Yeah. 722 00:36:35,200 --> 00:36:36,960 It was interesting, I remember. 723 00:36:36,960 --> 00:36:39,680 So Postgres 17 resolves this. 724 00:36:40,240 --> 00:36:44,610 "-d" means database in both utilities now. 725 00:36:44,610 --> 00:36:46,100 Michael: Even in pgbench now, wow. 726 00:36:46,100 --> 00:36:50,440 Nikolay: Yeah, and "--debug" is the new option for pgbench. 727 00:36:50,640 --> 00:36:53,180 So one more confusion to be resolved. 728 00:36:53,300 --> 00:36:54,100 That's great. 729 00:36:54,140 --> 00:36:54,640 Nice. 730 00:36:54,960 --> 00:36:55,460 Yeah. 731 00:36:56,680 --> 00:36:59,280 This release is confusion resolution release. 732 00:37:00,280 --> 00:37:00,700 Yeah. 733 00:37:00,700 --> 00:37:03,960 If we forget about pg_wait_events. 734 00:37:06,280 --> 00:37:07,480 Because yeah, okay. 735 00:37:07,480 --> 00:37:09,620 Michael: Well, it's reduction reduced still 736 00:37:09,720 --> 00:37:13,100 Nikolay: Balance is positive. That's great. 737 00:37:13,540 --> 00:37:14,420 So yeah. 738 00:37:15,060 --> 00:37:15,560 Michael: Awesome. 739 00:37:15,600 --> 00:37:17,620 Is there anything that you'd warn people? 740 00:37:18,260 --> 00:37:20,960 I like to encourage people to upgrade to the major versions. 741 00:37:21,280 --> 00:37:29,860 But mostly because I see people overall having a little bit too 742 00:37:29,860 --> 00:37:32,500 much reticence to do so. 743 00:37:32,500 --> 00:37:37,160 They treat Postgres like most projects and are very, very cautious 744 00:37:37,360 --> 00:37:40,220 on upgrades and often lag several versions behind. 745 00:37:40,560 --> 00:37:43,500 So I like to push people to upgrade a bit more. 746 00:37:43,500 --> 00:37:43,940 Nikolay: Of course. 747 00:37:43,940 --> 00:37:44,500 Of course. 748 00:37:44,500 --> 00:37:48,160 It's worth upgrading non-production environments already, like 749 00:37:48,340 --> 00:37:52,580 basically now, but on production and on production environments 750 00:37:52,580 --> 00:37:56,460 it's worth upgrading when it will be 17.1, 17.2. 751 00:37:58,680 --> 00:38:05,020 And if Everyone follows this rule, waiting for 17.1, it will 752 00:38:05,020 --> 00:38:07,320 be lacking improvements and fixes. 753 00:38:07,480 --> 00:38:12,660 So of course, if you can afford upgrading some less critical 754 00:38:12,780 --> 00:38:14,560 clusters, it should be done earlier. 755 00:38:15,220 --> 00:38:18,900 Michael: Or if the benefits are worth it, if there's a project 756 00:38:18,900 --> 00:38:22,240 that would massively benefit, then those ones might be worth 757 00:38:22,240 --> 00:38:23,040 a bit more risk. 758 00:38:23,040 --> 00:38:26,440 Nikolay: It happens sometimes, but still, my recommendation for 759 00:38:26,440 --> 00:38:31,300 critical, super important clusters, I would wait a little bit, 760 00:38:31,680 --> 00:38:37,260 or perform very thorough testing myself, I mean myself with my 761 00:38:37,260 --> 00:38:43,940 team and only then upgrade because jumping straight to 17.0 in 762 00:38:43,940 --> 00:38:50,800 critical production, it's sounds crazy and any SRE or DBRE will 763 00:38:50,800 --> 00:38:51,520 admit it. 764 00:38:52,300 --> 00:38:54,520 This is a conflicting recommendation. 765 00:38:54,720 --> 00:39:00,460 Let's upgrade faster and let's be very cautious that some bugs 766 00:39:00,460 --> 00:39:02,280 might happen because it's just released. 767 00:39:02,720 --> 00:39:04,460 There should be balance here as well. 768 00:39:04,860 --> 00:39:06,880 Michael: I'll be interested to see what people do. 769 00:39:06,880 --> 00:39:09,700 If you do upgrade soon, let us know how it goes. 770 00:39:09,960 --> 00:39:13,020 And let the Postgres community know as well. 771 00:39:13,020 --> 00:39:15,360 I think there's a lot of people that have worked hard on this. 772 00:39:15,360 --> 00:39:17,040 It's always great for them to get that 773 00:39:17,040 --> 00:39:17,320 Nikolay: feedback. 774 00:39:17,320 --> 00:39:17,820 Yeah. 775 00:39:18,080 --> 00:39:21,880 In our lab environments, we already have 17 and start using it 776 00:39:22,420 --> 00:39:23,640 since a few days ago. 777 00:39:23,680 --> 00:39:26,100 In all environments, we have 17 supported. 778 00:39:26,180 --> 00:39:28,860 So we benchmark and so on already on 17. 779 00:39:28,860 --> 00:39:30,400 And it's already something. 780 00:39:30,400 --> 00:39:31,860 It's not production, of course. 781 00:39:32,060 --> 00:39:34,740 But it's already something if some bugs happen. 782 00:39:34,940 --> 00:39:39,520 And what's good in our case, we, in many cases, we compare with 783 00:39:39,520 --> 00:39:43,740 previous versions so it's a super easy before AI and I hope we 784 00:39:43,740 --> 00:39:45,680 will find some problems and report 785 00:39:46,300 --> 00:39:51,680 Michael: yeah the main read like obviously there's risks but 786 00:39:51,680 --> 00:39:56,640 there's also it's well worth checking support from tools you 787 00:39:56,640 --> 00:40:01,260 use, like extensions often, a lot of ones I checked haven't yet 788 00:40:01,800 --> 00:40:04,100 announced Postgres 17. 789 00:40:04,400 --> 00:40:06,580 Many do, but many don't as well. 790 00:40:07,120 --> 00:40:08,500 Yeah, but many do. 791 00:40:09,640 --> 00:40:12,980 Nikolay: A few days ago I approved a merge request for DBLab engine. 792 00:40:12,980 --> 00:40:17,900 And we have extensions, a lot of extensions in our custom images 793 00:40:18,700 --> 00:40:19,360 for Postgres. 794 00:40:19,540 --> 00:40:23,340 And I saw on this, like I expected much longer list of unsupported 795 00:40:23,520 --> 00:40:26,260 in 17, but it was like 5 items only. 796 00:40:26,640 --> 00:40:30,780 We have dozens of good extensions, additionally, to contrib modules. 797 00:40:31,860 --> 00:40:34,740 Michael: Well, some that don't, for example, I didn't see, did 798 00:40:34,740 --> 00:40:36,420 you see Timescale or Citus? 799 00:40:36,500 --> 00:40:37,100 I didn't see that. 800 00:40:37,100 --> 00:40:37,780 Timescale was not 801 00:40:37,780 --> 00:40:38,420 Nikolay: there, I think. 802 00:40:38,420 --> 00:40:39,440 Yeah, it's big. 803 00:40:39,440 --> 00:40:40,200 It's big. 804 00:40:40,420 --> 00:40:40,840 Let me. 805 00:40:40,840 --> 00:40:43,340 Michael: pg_repack as well, I didn't see. 806 00:40:43,840 --> 00:40:44,160 Yeah. 807 00:40:44,160 --> 00:40:47,320 Like these are, these are ones that it doesn't matter to somebody 808 00:40:47,320 --> 00:40:49,200 if most of their extensions are supported. 809 00:40:49,200 --> 00:40:52,280 If there's 1 that they're using that is critical and it's not 810 00:40:52,280 --> 00:40:54,060 supported, then... 811 00:40:54,100 --> 00:40:57,420 Nikolay: Yeah, I'm looking at the list and yeah, indeed, 812 00:40:58,080 --> 00:41:05,180 pg_repack, pg_qualstats, it's PoWA, and Timescale, Citus, you're 813 00:41:05,180 --> 00:41:05,680 right. 814 00:41:06,460 --> 00:41:08,920 Michael: And again, hopefully in the next few days, a lot of 815 00:41:08,920 --> 00:41:10,180 these will change. 816 00:41:10,460 --> 00:41:13,480 They could easily be waiting hopefully as of Thursday which is 817 00:41:13,480 --> 00:41:15,980 yesterday if you're listening to this on the day it's released... 818 00:41:16,700 --> 00:41:17,980 Go on 819 00:41:17,980 --> 00:41:19,340 Nikolay: pgvector is fine. 820 00:41:19,680 --> 00:41:20,460 It's already 821 00:41:21,820 --> 00:41:24,780 supporting 17. 822 00:41:25,520 --> 00:41:28,500 Michael: Yeah, and lots of other tools have already, like we 823 00:41:28,500 --> 00:41:31,180 shipped Postgres 17 support the other day. 824 00:41:31,180 --> 00:41:33,760 Patroni as well, version 17 support already. 825 00:41:33,920 --> 00:41:36,220 Like loads of tools do, which is great. 826 00:41:36,220 --> 00:41:39,560 But yeah, I just thought it's worth saying it's worth people 827 00:41:39,560 --> 00:41:43,060 checking that the tools they rely on do support it already. 828 00:41:44,280 --> 00:41:44,780 Nikolay: Good. 829 00:41:45,060 --> 00:41:45,560 Okay. 830 00:41:45,980 --> 00:41:46,480 Michael: Nice. 831 00:41:46,720 --> 00:41:48,060 Anything else you wanted to say? 832 00:41:48,280 --> 00:41:49,700 Nikolay: No, I think that's it. 833 00:41:49,760 --> 00:41:51,540 Upgrade, but carefully. 834 00:41:53,400 --> 00:41:53,900 Michael: Yeah. 835 00:41:53,940 --> 00:41:56,200 Or at least check out the release notes and see if there's anything 836 00:41:56,200 --> 00:41:57,120 important in there for 837 00:41:57,120 --> 00:41:57,620 Nikolay: you. 838 00:41:57,740 --> 00:41:58,360 Right, right. 839 00:41:58,660 --> 00:41:59,160 Good. 840 00:41:59,160 --> 00:41:59,970 Thank you, Michael.