1 0:0:0,14 --> 0:0:2,3600001 Nikolay: Hello, hello, this is PostgresFM. 2 0:0:2,54 --> 0:0:6,14 My name is Nik, PostgresAI, and as usual with me is Michael, 3 0:0:6,14 --> 0:0:6,64 pgMustard. 4 0:0:6,96 --> 0:0:7,98 Hello, Michael. 5 0:0:8,54 --> 0:0:9,44 Michael: Hello, Nik. 6 0:0:9,96 --> 0:0:10,679999 Nikolay: How are you? 7 0:0:10,679999 --> 0:0:11,54 What's new? 8 0:0:12,44 --> 0:0:13,2 Michael: I'm good. 9 0:0:13,259999 --> 0:0:15,179999 Not as much new as you, I think. 10 0:0:15,42 --> 0:0:17,54 Wrote a blog post recently, been working 11 0:0:17,54 --> 0:0:18,18 Nikolay: on some 12 0:0:18,74 --> 0:0:21,3 Michael: little improvements here and there, doing some research 13 0:0:21,3 --> 0:0:24,02 in the background as to what people want and need. 14 0:0:24,02 --> 0:0:26,82 But yeah, you've got some much more exciting news I think. 15 0:0:27,72 --> 0:0:29,380001 Nikolay: It's small but maybe good. 16 0:0:29,380001 --> 0:0:33,38 I don't know, I'm experimenting a lot lately with AIs as well, 17 0:0:33,4 --> 0:0:34,9 constantly learning. 18 0:0:35,58 --> 0:0:36,54 It's crazy times. 19 0:0:36,58 --> 0:0:41,739998 People reconsider workflows, obviously, how we work. 20 0:0:42,739998 --> 0:0:46,4 So it's easier right now to ship some stuff. 21 0:0:47,14 --> 0:0:51,82 And all the ideas you had during a long time, it's quite easy 22 0:0:51,82 --> 0:0:53,64 right now to implement. 23 0:0:54,62 --> 0:1:0,44 If you just shift from coding to engineering and organize design 24 0:1:0,44 --> 0:1:5,64 and verification of all the details, like benchmarks and you 25 0:1:5,64 --> 0:1:8,32 focus on like architecture more and so on. 26 0:1:8,32 --> 0:1:14,72 It's great time to have like helpers and honestly Opus 4.6 is 27 0:1:14,72 --> 0:1:17,54 quite good, even better than 4.5. 28 0:1:19,64 --> 0:1:20,32 So yeah. 29 0:1:21,0 --> 0:1:24,8 And others also good, I use them for reviews, other models including 30 0:1:24,8 --> 0:1:26,42 Gemini and GPT of course. 31 0:1:27,18 --> 0:1:33,24 Anyway, I wanted to discuss this small toy tool I just created 32 0:1:33,82 --> 0:1:34,58 over weekend. 33 0:1:35,94 --> 0:1:40,46 And today I'm releasing version 1.2 and publishing it finally. 34 0:1:41,04 --> 0:1:45,02 I have good feedback from a couple of folks, not only from my 35 0:1:45,02 --> 0:1:46,48 team, but also external ones. 36 0:1:46,48 --> 0:1:50,58 And it's working on our production already and yeah and I think 37 0:1:51,18 --> 0:1:55,94 it's it gives me the same feeling as I had when I proposed the 38 0:1:55,94 --> 0:2:0,0 transaction_timeout to Andrey to implement during our Postgres 39 0:2:0,06 --> 0:2:4,54 life hacking sessions on YouTube because it's feeling is why 40 0:2:4,54 --> 0:2:6,64 doesn't this thing exist yet? 41 0:2:6,76 --> 0:2:7,28 Yeah, do you want 42 0:2:7,28 --> 0:2:8,5 Michael: to say what it is? 43 0:2:8,94 --> 0:2:11,44 Nikolay: Yeah, pgsentinel, active session history. 44 0:2:11,6 --> 0:2:18,38 I believe with all the new setups and growing existing setups 45 0:2:18,38 --> 0:2:20,9 of Postgres, millions of database clusters. 46 0:2:21,54 --> 0:2:27,94 I believe wait event analysis has huge potential and is heavily 47 0:2:28,98 --> 0:2:29,48 underappreciated. 48 0:2:30,72 --> 0:2:34,56 And performance insights in RDS is great. 49 0:2:34,82 --> 0:2:37,56 Other tools like pg_wait_sampling, all tools are great. 50 0:2:37,7 --> 0:2:44,18 But there is huge potential for various tools and people to understand 51 0:2:44,48 --> 0:2:45,4 what it is. 52 0:2:45,72 --> 0:2:50,14 And I think a lot of non-experts don't understand what it is 53 0:2:50,28 --> 0:2:55,16 and they are going to explore it soon I hope because it's a great 54 0:2:55,16 --> 0:2:59,38 tool to troubleshoot database issues You know like Brendan Gregg 55 0:2:59,38 --> 0:3:5,78 books and his presentations He says if if somebody asks him which 56 0:3:5,86 --> 0:3:9,44 Linux performance observability, like Linux performance analysis 57 0:3:9,48 --> 0:3:12,84 tool in console, he says it would be IOstat. 58 0:3:13,86 --> 0:3:20,88 Because IOstat shows database metrics, disk metrics, right? 59 0:3:20,98 --> 0:3:23,5 Latency, throughput, queue size. 60 0:3:23,6 --> 0:3:26,62 And also it shows a little bit about CPU as well. 61 0:3:26,68 --> 0:3:31,02 If you have 1 tool, quickly understand as much as possible. 62 0:3:31,08 --> 0:3:31,72 This is it. 63 0:3:31,72 --> 0:3:35,18 Of course, he also mentions SAR, SAR is great and so on. 64 0:3:35,58 --> 0:3:37,26 But the same thing here. 65 0:3:37,36 --> 0:3:44,14 If you choose just 1 approach or tool, which will give you as 66 0:3:44,14 --> 0:3:47,52 much understanding of problems like during troubleshooting, like 67 0:3:47,52 --> 0:3:51,3 why database is slow, This is going to be a wait event analysis 68 0:3:51,54 --> 0:3:53,54 or active session history analysis. 69 0:3:53,68 --> 0:3:57,68 We had a separate episode about it, maybe a couple of them. 70 0:3:58,14 --> 0:4:4,66 And I think inside me there's a huge feeling it's hugely underappreciated. 71 0:4:5,82 --> 0:4:10,08 When I talk to people who are not Postgres experts, if they are 72 0:4:10,08 --> 0:4:13,66 on RDS, this is default tool for them, but outside of RDS, they 73 0:4:13,66 --> 0:4:14,94 don't know what it is. 74 0:4:15,06 --> 0:4:18,9 And then even with RDS, performance insights, or Cloud SQL database 75 0:4:18,9 --> 0:4:22,92 insights, or how it's called, query insights, it's still like 76 0:4:22,96 --> 0:4:25,84 a huge lack of use of it all the time. 77 0:4:25,84 --> 0:4:27,94 And I think it should be central in all troubleshooting. 78 0:4:28,14 --> 0:4:29,66 This should be starting point. 79 0:4:30,12 --> 0:4:30,42 Michael: So. 80 0:4:30,42 --> 0:4:30,7 Okay. 81 0:4:30,7 --> 0:4:31,42 So why? 82 0:4:31,42 --> 0:4:32,34 Okay, go on. 83 0:4:32,86 --> 0:4:33,26 Nikolay: Yes. 84 0:4:33,26 --> 0:4:36,92 So I've been a big fan of pg_wait_sampling for many years. 85 0:4:37,36 --> 0:4:42,84 pg_wait_sampling is an extension which provides you very precise 86 0:4:43,08 --> 0:4:44,16 sampling, 10 milliseconds. 87 0:4:44,44 --> 0:4:48,08 Internally, but you need additional tool like monitoring to export 88 0:4:48,08 --> 0:4:49,42 that data for analysis. 89 0:4:49,82 --> 0:4:54,84 It's hard to analyze it inside because you still need some persistent 90 0:4:54,86 --> 0:4:59,76 storage because history it has, it's not covering a lot. 91 0:4:59,76 --> 0:5:2,26 It's just some tail and you need to export it. 92 0:5:2,52 --> 0:5:6,14 But it gives you 10 millisecond sampling. 93 0:5:6,14 --> 0:5:7,04 It's very precise. 94 0:5:7,68 --> 0:5:11,48 The problem with pg_wait_sampling, and we had it for long in our 95 0:5:11,48 --> 0:5:19,12 monitoring stack, but slowly we moved attention from it away 96 0:5:19,28 --> 0:5:22,74 and we implemented, we call it lazy sampling, where every 15 97 0:5:22,74 --> 0:5:25,36 seconds our monitoring tool collects it. 98 0:5:25,52 --> 0:5:29,06 Because of availability, a lot of customers, they don't have 99 0:5:29,06 --> 0:5:30,04 pg_wait_sampling. 100 0:5:30,3 --> 0:5:32,14 It's present only in Cloud SQL. 101 0:5:32,54 --> 0:5:35,5 I wish it was installed everywhere, but no. 102 0:5:35,5 --> 0:5:38,8 Unfortunately, the reality, you know, like, so we shifted to 103 0:5:38,8 --> 0:5:39,72 lazy sampling. 104 0:5:40,38 --> 0:5:44,4 And then something like it's working, it's enough if database 105 0:5:44,44 --> 0:5:45,12 is huge. 106 0:5:45,62 --> 0:5:48,0 15 seconds sampling already good enough. 107 0:5:48,34 --> 0:5:52,36 And we cannot do 1 second sampling because it's too much to big 108 0:5:52,36 --> 0:5:54,82 frequency observer effect, like it's too much. 109 0:5:54,82 --> 0:5:58,02 Because we are external, we're pooling this data to some external 110 0:5:58,14 --> 0:6:2,38 tool, connecting to the instance on Supabase or RDS anywhere. 111 0:6:3,04 --> 0:6:6,76 There is a feeling that lazy, this approach is not lazy, infrequent. 112 0:6:7,08 --> 0:6:7,96 It's not okay. 113 0:6:7,96 --> 0:6:11,14 We need something internally to sample and then we need to afford. 114 0:6:11,14 --> 0:6:14,32 This was number 1 reason why I thought we need something else. 115 0:6:14,68 --> 0:6:18,16 Number 2 reason, there are more and more cases when databases 116 0:6:18,28 --> 0:6:23,74 are small and it's hard to justify having full-fledged monitoring 117 0:6:24,02 --> 0:6:28,44 for them because you need to pay like basically at least 100 118 0:6:28,44 --> 0:6:30,92 bucks for this monitoring setup. 119 0:6:30,92 --> 0:6:33,94 But if your database is tiny and you pay 50 bucks, it's like 120 0:6:33,94 --> 0:6:39,24 some pet project, there's no way you will decide to spend a few 121 0:6:39,24 --> 0:6:41,78 hundred bucks per month for this pet project. 122 0:6:42,24 --> 0:6:45,38 But you still need this because if something happened a couple 123 0:6:45,38 --> 0:6:47,54 of hours ago, you need to troubleshoot, investigate. 124 0:6:48,28 --> 0:6:50,28 And Postgres internally has everything, right? 125 0:6:50,28 --> 0:6:53,0 But it's just, you need to, it doesn't have memory. 126 0:6:53,86 --> 0:6:56,9 So wait events, there are 2 columns, wait event type, wait event, 127 0:6:57,16 --> 0:6:58,3 in persistent activity. 128 0:6:58,68 --> 0:7:1,72 But nobody is sampling them without, like by default. 129 0:7:2,24 --> 0:7:5,92 And it was also the third reason, understanding that current 130 0:7:5,92 --> 0:7:12,18 monitoring tools are going to be changed to provide information 131 0:7:12,18 --> 0:7:15,1 in better form for LLMs during troubleshooting. 132 0:7:15,36 --> 0:7:16,98 This was reason number 3. 133 0:7:17,02 --> 0:7:19,9 That's how I came to the idea, okay, let's implement something. 134 0:7:20,58 --> 0:7:25,38 And this something must be low footprint in terms of storage 135 0:7:25,38 --> 0:7:26,5 and observer effect. 136 0:7:27,18 --> 0:7:31,88 It must work everywhere and it must be LLM friendly in terms 137 0:7:31,88 --> 0:7:33,98 of how information is provided. 138 0:7:34,1 --> 0:7:39,88 Not a lot of JSONs or you need to write PromQL all the time, 139 0:7:39,88 --> 0:7:45,48 but some compact form you can feed it to your AI agent or something 140 0:7:45,48 --> 0:7:48,92 and ask to explain what's happening here and where to dig further. 141 0:7:49,12 --> 0:7:51,18 And this is how pg_ash was created. 142 0:7:51,54 --> 0:7:55,58 Because of the first requirement, I couldn't create it as an 143 0:7:55,68 --> 0:7:56,18 extension. 144 0:7:57,98 --> 0:7:59,34 So I called it anti-extension. 145 0:8:0,12 --> 0:8:3,46 And remember we discussed pg_index_pilot, which is automation for 146 0:8:3,46 --> 0:8:4,62 recreation of indexes. 147 0:8:5,08 --> 0:8:7,0 It was also created in the same fashion. 148 0:8:7,78 --> 0:8:14,84 And also, like some sneak peek, we talked with some Supabase 149 0:8:14,9 --> 0:8:19,12 folks, and they seemed to understand that as well. 150 0:8:19,12 --> 0:8:23,08 And there is also a new project called pg-flight-recorder. 151 0:8:23,08 --> 0:8:27,26 I hope we will discuss it maybe separately, which actually is 152 0:8:27,26 --> 0:8:30,8 very similar to my pg_ash, but it covers more. 153 0:8:31,22 --> 0:8:34,14 So we are thinking how to maybe unite our efforts. 154 0:8:34,2 --> 0:8:35,52 It's an ongoing discussion. 155 0:8:36,22 --> 0:8:40,02 But yeah, it samples like pg_stat_statements, pg_stat_io, and so 156 0:8:40,02 --> 0:8:41,76 on, like all the stuff, SLRU and 157 0:8:41,76 --> 0:8:41,94666 Michael: so on. 158 0:8:41,94666 --> 0:8:42,54 Oh, interesting. 159 0:8:42,91003 --> 0:8:46,32 Nikolay: So there was an old project called pg_profile. 160 0:8:46,92 --> 0:8:50,1 Andrey Zubkov created it and for quite some time it exists. 161 0:8:50,28 --> 0:8:54,48 It's great, it's AWR from Oracle but for Postgres, right? 162 0:8:54,48 --> 0:8:59,34 It records all statistics also inside, but it's extension. 163 0:8:59,68 --> 0:9:3,3 So since it's extension, it's not available on most managed Postgres 164 0:9:4,08 --> 0:9:4,58 platforms. 165 0:9:5,2 --> 0:9:8,88 That's why I like the idea of anti-extension, because it's just 166 0:9:8,88 --> 0:9:11,34 pure SQL and PL/pgSQL, that's it. 167 0:9:11,34 --> 0:9:11,4 — 168 0:9:11,4 --> 0:9:15,2 Michael: So installation is just a case of running an SQL script 169 0:9:15,2 --> 0:9:18,14 that you've got, you know, creates a schema, some functions. 170 0:9:18,52 --> 0:9:19,32 Nikolay: Yeah, exactly. 171 0:9:19,6 --> 0:9:20,36 Yeah, yeah. 172 0:9:20,9 --> 0:9:22,28 So, I think this... 173 0:9:22,68 --> 0:9:24,84 Michael: Which you could bundle as an extension, right? 174 0:9:24,84 --> 0:9:26,5 But the problem then becomes... 175 0:9:26,98 --> 0:9:28,14 Nikolay: Let me be transparent. 176 0:9:28,14 --> 0:9:28,86 Why not? 177 0:9:29,34 --> 0:9:33,52 Like, yesterday we discussed it and there is an idea that yes, 178 0:9:33,52 --> 0:9:37,5 it can be like extension, like non-extension, like just a bunch 179 0:9:37,5 --> 0:9:38,0 of... 180 0:9:38,88 --> 0:9:41,34 There is an idea, okay, this could be... 181 0:9:41,82 --> 0:9:45,26 I honestly have some thought that maybe Postgres could have some 182 0:9:45,26 --> 0:9:47,14 concept of packages or something. 183 0:9:47,72 --> 0:9:48,74 There was trusted language. 184 0:9:48,74 --> 0:9:49,78 Trusted extensions. 185 0:9:49,9 --> 0:9:50,88 Michael: Yes, exactly. 186 0:9:50,98 --> 0:9:53,18 Nikolay: Yes, Rust is, exactly. 187 0:9:53,6 --> 0:9:57,16 So David Ventimiglia, I hope I pronounced last name right. 188 0:9:57,18 --> 0:9:59,16 I apologize David if I did wrong. 189 0:9:59,34 --> 0:10:4,46 From Supabase said, and who is developing pg-flight-recorder. 190 0:10:4,82 --> 0:10:5,52 Michael: Makes sense. 191 0:10:5,92 --> 0:10:6,42 Nikolay: Yes. 192 0:10:6,66 --> 0:10:10,64 So he said, this is the direction he's thinking to package as 193 0:10:10,64 --> 0:10:13,34 a TLE extension, right? 194 0:10:13,48 --> 0:10:16,64 Because extensions, if it's available, I think it's available 195 0:10:16,64 --> 0:10:18,4 on Supabase or RDS, right? 196 0:10:18,4 --> 0:10:21,6 It's a straightforward concept, right? 197 0:10:21,6 --> 0:10:24,24 But in some cases we need to think about all platforms. 198 0:10:24,48 --> 0:10:27,18 And in this case we want to be maximum flexibility. 199 0:10:27,28 --> 0:10:30,24 So I'm thinking now to package it in future versions, to package 200 0:10:30,24 --> 0:10:34,26 it in both ways, like pure SQL installation or TLE installation, 201 0:10:34,28 --> 0:10:34,98 why not? 202 0:10:35,32 --> 0:10:36,32 Michael: Yeah, absolutely. 203 0:10:36,6 --> 0:10:39,48 It's just a bit sad that extensions were... 204 0:10:39,48 --> 0:10:43,22 The idea of having an extension framework was to make distribution 205 0:10:43,32 --> 0:10:47,56 easier and now we've gone full circle and it makes it harder 206 0:10:47,56 --> 0:10:50,16 because everyone's on managed service providers that pick which 207 0:10:50,16 --> 0:10:51,34 extensions are available. 208 0:10:51,34 --> 0:10:52,92 So yeah, it's just a bit of a shame. 209 0:10:52,92 --> 0:10:55,68 Nikolay: This sentiment you just shared, like it's so deep in 210 0:10:55,68 --> 0:10:56,0 me. 211 0:10:56,0 --> 0:11:1,02 I remember 2005, 6, 7 when like Postgres is Extensible, then 212 0:11:1,02 --> 0:11:6,0 extensions concept was created by Dimitri Fontaine, right? 213 0:11:6,2 --> 0:11:6,9 Oh, really? 214 0:11:7,06 --> 0:11:7,56 Yeah. 215 0:11:8,1 --> 0:11:8,6 Yeah. 216 0:11:8,94 --> 0:11:10,4 He created extension as his thing. 217 0:11:11,58 --> 0:11:13,72 Maybe I'm not mistaken, I hope. 218 0:11:14,16 --> 0:11:15,82 I can hallucinate often. 219 0:11:16,0 --> 0:11:18,3 This happens because I'm overloaded with information. 220 0:11:18,9 --> 0:11:20,46 And then everyone was excited. 221 0:11:20,46 --> 0:11:21,68 Postgres is so extensible. 222 0:11:22,18 --> 0:11:27,14 But we should blame managed Postgres platforms, starting with 223 0:11:27,14 --> 0:11:29,12 Heroku and RDS and all others. 224 0:11:29,54 --> 0:11:32,72 Why extensions became an extensible concept? 225 0:11:32,8 --> 0:11:36,18 Because it's in their hands to decide which extension to add. 226 0:11:36,18 --> 0:11:37,94 They have their own reasons, obviously. 227 0:11:38,6 --> 0:11:41,38 Every time they add some extension, it means they need to support 228 0:11:41,38 --> 0:11:44,68 it and to have some responsibility if it's secure and so on. 229 0:11:44,68 --> 0:11:45,6 I understand that. 230 0:11:45,74 --> 0:11:48,8 But extensions became unextendable. 231 0:11:50,54 --> 0:11:53,32 Every time I thought, oh, I want to implement this in Postgres, 232 0:11:53,32 --> 0:11:57,08 every time I said, no way I will decide to make this extension. 233 0:11:57,66 --> 0:12:0,98 Of course, the concept of trusted language extensions is great, 234 0:12:1,0 --> 0:12:2,86 but still it's not adopted everywhere. 235 0:12:3,4 --> 0:12:4,5 It's limited, right? 236 0:12:4,54 --> 0:12:4,82 — 237 0:12:4,82 --> 0:12:8,56 Michael: Yeah, so this is not an extension, but could be, but 238 0:12:8,56 --> 0:12:13,78 it's not to start with, and it installs a couple of tables, some 239 0:12:13,78 --> 0:12:15,82 functions, what does it do exactly? 240 0:12:16,22 --> 0:12:18,72 Nikolay: — Yeah, let's talk about this, what exactly it does. 241 0:12:18,76 --> 0:12:22,74 The idea is let's just sample, collect wait event samples from 242 0:12:22,74 --> 0:12:28,12 pg_stat_activity with query IDs and have some history. 243 0:12:28,58 --> 0:12:29,68 It's quite simple. 244 0:12:30,22 --> 0:12:31,3 I didn't want... 245 0:12:31,64 --> 0:12:37,96 So the problem is we need some help to invoke function which 246 0:12:37,96 --> 0:12:39,26 will sample, right? 247 0:12:39,64 --> 0:12:42,42 And I don't want any lambda functions or anything. 248 0:12:42,44 --> 0:12:45,82 I want everything in Postgres, but I cannot build a Background 249 0:12:45,82 --> 0:12:48,78 Worker or anything because I don't want to create extension. 250 0:12:49,28 --> 0:12:55,06 pg_cron was chosen as a dependency, and pg_cron is present everywhere. 251 0:12:55,84 --> 0:12:59,98 And I remember I was driving at that time, I was brainstorming 252 0:13:0,18 --> 0:13:0,92 with ChatGPT. 253 0:13:1,88 --> 0:13:4,9 Unfortunately, Claude, the voice abilities are not as good as 254 0:13:4,9 --> 0:13:5,5 ChatGPT. 255 0:13:5,5 --> 0:13:9,94 So I was brainstorming and we agreed both that pg_cron is going 256 0:13:9,94 --> 0:13:14,06 to be a problem because it has, as any cron, it has only 1-minute 257 0:13:14,06 --> 0:13:14,56 precision. 258 0:13:15,32 --> 0:13:19,0 I was driving, I reached my destination, I was starting to doubt. 259 0:13:19,0 --> 0:13:22,58 I always doubt when working with AI and humans, actually. 260 0:13:22,66 --> 0:13:24,04 It's not only about AI. 261 0:13:24,06 --> 0:13:25,9 Humans also can make mistakes. 262 0:13:26,2 --> 0:13:30,16 So I was in doubt and I said, let's verify, check the documentation, 263 0:13:30,34 --> 0:13:31,34 check with me. 264 0:13:31,36 --> 0:13:37,06 And thankfully, starting pg_cron 1.5, per second precision is 265 0:13:37,06 --> 0:13:37,56 available. 266 0:13:39,96 --> 0:13:42,98 And this was moment, okay, I'm going to build this because I 267 0:13:42,98 --> 0:13:44,48 need per second precision 268 0:13:44,96 --> 0:13:47,58 Michael: So is 1 second the minimum though? 269 0:13:47,78 --> 0:13:49,08 Nikolay: It's minimum, yeah. 270 0:13:51,56 --> 0:13:54,52 Michael: But I was going to ask why 1 second as the default and 271 0:13:54,52 --> 0:13:55,74 now it makes sense. 272 0:13:56,12 --> 0:13:59,18 Nikolay: Yes, then I was thinking, okay, we are going to write 273 0:13:59,18 --> 0:13:59,84 a lot. 274 0:14:0,24 --> 0:14:3,0 We need to write query ID and the wait event. 275 0:14:3,34 --> 0:14:6,0 So how to optimize storage? 276 0:14:6,76 --> 0:14:8,14 And there are 2 big problems. 277 0:14:8,16 --> 0:14:11,68 First, how much we are going to spend in terms of bytes if we're 278 0:14:11,68 --> 0:14:12,48 just writing. 279 0:14:12,66 --> 0:14:15,56 And second, we know Postgres and MVCC and bloat issues. 280 0:14:16,62 --> 0:14:19,9 So if you implement some ring buffer for your storage... 281 0:14:21,46 --> 0:14:23,24 Anyway, let's jump to solution. 282 0:14:23,24 --> 0:14:28,08 I knew the proper solution from Skype, PgQ, SkyTools PgQ, 283 0:14:28,52 --> 0:14:30,3 3 partitions and rotation. 284 0:14:30,84 --> 0:14:35,52 So I just created partition yesterday, today and tomorrow, logically. 285 0:14:36,04004 --> 0:14:41,52 So yesterday is read-only partition fully filled, 24x60x60 286 0:14:41,52 --> 0:14:42,76 right? 287 0:14:42,98 --> 0:14:43,48 Records. 288 0:14:43,82 --> 0:14:49,38 Actually no, we need to think how do we store 1 row per event 289 0:14:49,38 --> 0:14:51,56 pair or we somehow store it differently. 290 0:14:51,56 --> 0:14:52,96 Let's return to this point. 291 0:14:52,96 --> 0:14:56,54 Now we have current partition today and tomorrow is truncated 292 0:14:56,64 --> 0:14:59,62 because truncated is super efficient as we all know, right? 293 0:15:0,06 --> 0:15:3,66 No deletes, no updates, inserts and truncate, that's it. 294 0:15:4,3 --> 0:15:5,82 So this is how we implement. 295 0:15:5,94 --> 0:15:8,86 And we have visibility right now in current version, we have 296 0:15:8,86 --> 0:15:13,58 visibility only for yesterday and today. 297 0:15:14,24 --> 0:15:16,4 And you can export it to your monitoring tool. 298 0:15:16,4 --> 0:15:20,34 This is we are going to implement compatibility with this extension, 299 0:15:20,46 --> 0:15:24,6 not anti-extension in our monitoring stack, but also there is 300 0:15:24,6 --> 0:15:28,1 a plan, maybe when you listen to this podcast, I already implemented 301 0:15:28,1 --> 0:15:32,8 this, there is a plan to have roll-up storage for longer term. 302 0:15:33,16 --> 0:15:38,2 I plan to have 1 year of precision, maybe 1 hour or 1 minute, 303 0:15:38,2 --> 0:15:38,86 it depends. 304 0:15:38,86 --> 0:15:39,72 It's not a lot. 305 0:15:39,72 --> 0:15:44,28 So this is how we solved MVCC, just borrowed an idea from old 306 0:15:44,28 --> 0:15:46,3 good Skype from 20 years ago. 307 0:15:46,38 --> 0:15:48,72 I still think it's a great, it's a great idea. 308 0:15:49,38 --> 0:15:50,28 Michael: So no bloat. 309 0:15:50,28 --> 0:15:52,46 So that solves the bloat issue, yeah exactly. 310 0:15:52,54 --> 0:15:53,0 Nikolay: Fully. 311 0:15:53,0 --> 0:15:55,2 And second, how to find optimized storage. 312 0:15:55,2 --> 0:15:58,9 We had several ideas so we decided, like, I actually decided, 313 0:15:58,9 --> 0:16:2,5 but yeah I agreed, like with a lot of benchmarks, many iterations, 314 0:16:2,86 --> 0:16:7,0 we chose the idea to store 1 row per second. 315 0:16:7,58 --> 0:16:13,22 So it's timestamp, it's database ID, because, oh, not 1 row. 316 0:16:13,5 --> 0:16:17,44 If you have multiple logical databases, then it will be as many 317 0:16:17,44 --> 0:16:21,42 databases as are currently active in terms of workload at this 318 0:16:21,42 --> 0:16:22,28 very second. 319 0:16:22,9 --> 0:16:23,6 So at this very moment. 320 0:16:23,6 --> 0:16:25,74 Michael: 1 row per database per second. 321 0:16:26,32 --> 0:16:29,56 Nikolay: But only those which receive, which have active sessions 322 0:16:29,56 --> 0:16:30,06 right now. 323 0:16:30,06 --> 0:16:30,56 Michael: Okay. 324 0:16:30,56 --> 0:16:31,06 Nikolay: Yeah. 325 0:16:31,12 --> 0:16:32,36 I thought about it. 326 0:16:32,83997 --> 0:16:37,0 If you, if we have 1000 databases, it's insane, but it's unlikely 327 0:16:37,16 --> 0:16:40,24 all of them have right now active sessions because it might be 328 0:16:40,24 --> 0:16:44,34 if you have huge machine and a lot of things happening, but still, 329 0:16:44,34 --> 0:16:48,18 if you have huge machine, you can afford the, maybe like thousand 330 0:16:48,18 --> 0:16:49,16 rows per second. 331 0:16:49,2 --> 0:16:50,46 Maybe no, it depends. 332 0:16:50,82 --> 0:16:52,74 There is room for improvement here. 333 0:16:52,74 --> 0:16:57,38 But anyway, right now it's 1 row per second if it's only 1 database 334 0:16:57,38 --> 0:16:58,58 active right now. 335 0:16:58,66 --> 0:17:1,42 And then we encode everything, but how? 336 0:17:1,44 --> 0:17:5,1 The thought about JSON-B, first thing, JSON or JSON-B. 337 0:17:6,1 --> 0:17:10,2 I proposed correlated arrays and it proved to me that it's better 338 0:17:10,2 --> 0:17:11,54 in this case than JSON-B. 339 0:17:12,34 --> 0:17:13,76 2 arrays, so 2 rows. 340 0:17:13,94 --> 0:17:18,48 And then I proposed this encoded 1 array and benchmarks proved 341 0:17:18,48 --> 0:17:19,12 it's better. 342 0:17:19,12 --> 0:17:25,44 So we encode wait event, number of sessions, wait event, for 343 0:17:25,44 --> 0:17:30,74 example, I or, for example, lightweight lock manager, our favorite. 344 0:17:31,5 --> 0:17:34,86 And then there is number how many active sessions we are present, 345 0:17:34,86 --> 0:17:38,4 like for example, 5, and then 5 query IDs, right? 346 0:17:38,74 --> 0:17:43,14 So 7 numbers, and then next wait event, and so on. 347 0:17:43,14 --> 0:17:45,12 And so we have a bunch of numbers. 348 0:17:45,72 --> 0:17:49,1 And I didn't want to use 8-byte integers. 349 0:17:49,82 --> 0:17:53,46 I wanted to use 2-byte integers, because it should be enough. 350 0:17:54,52 --> 0:17:56,92 So we created 2 dictionaries. 351 0:17:57,18 --> 0:17:59,18 1 to encode all wait events. 352 0:17:59,34 --> 0:18:4,08 Actually, if you write every time, if you write LWLock manager, 353 0:18:4,08 --> 0:18:5,26 it's a lot of bytes. 354 0:18:6,18 --> 0:18:6,68 Why? 355 0:18:6,7 --> 0:18:10,96 We can encode it and to support, as in recent couple of major 356 0:18:10,96 --> 0:18:16,24 versions, there is a pg_wait_event, wait events dictionary, right? 357 0:18:16,24 --> 0:18:16,84 In Postgres. 358 0:18:16,88 --> 0:18:17,22 Yeah. 359 0:18:17,22 --> 0:18:22,16 Which is just a static list of them But I wanted to support all 360 0:18:22,16 --> 0:18:26,12 the versions with this thing supports Postgres 14+ so that's 361 0:18:26,12 --> 0:18:29,84 why we created some our dictionary like to propagate it to back, 362 0:18:30,24 --> 0:18:30,74 yeah 363 0:18:31,38 --> 0:18:35,06 Michael: 1 thing I didn't understand is you mentioned not using 364 0:18:35,06 --> 0:18:36,08 8-byte integers. 365 0:18:36,42 --> 0:18:40,14 Does that include, because query IDs by default are 8-byte, right? 366 0:18:40,36 --> 0:18:44,24 Nikolay: Yeah, so query ID is 8, and that's why we have 2 dictionaries. 367 0:18:44,28 --> 0:18:48,74 1 to encode wait events, and second is to encode query IDs, to 368 0:18:48,74 --> 0:18:50,78 map, to two-byte integers. 369 0:18:51,1 --> 0:18:51,84 That's it. 370 0:18:52,28 --> 0:18:56,5 And, yeah, and we also, I don't remember, but we somehow solved 371 0:18:56,5 --> 0:19:0,82 the problem that it can grow without limits. 372 0:19:0,82 --> 0:19:3,34 We solved this, So it cannot grow without limits. 373 0:19:3,34 --> 0:19:5,22 We keep it short. 374 0:19:5,22 --> 0:19:6,78 I know how we solved it. 375 0:19:6,88 --> 0:19:9,3 Can I remember because I proposed this solution? 376 0:19:9,84 --> 0:19:15,58 By the way, I forgot to say, I created it using 3 AI plus me. 377 0:19:15,58 --> 0:19:17,22 So a team of 4 worked. 378 0:19:17,72 --> 0:19:22,38 1 AI was an engineer and 1 was focusing on benchmarks, another 379 0:19:22,38 --> 0:19:26,06 was focusing only just on quality and documentation and reviews. 380 0:19:28,08 --> 0:19:31,72 And benchmark engineer also focused on reviews, but with specific 381 0:19:31,72 --> 0:19:36,06 goal like storage efficiency and performance and so low Observer 382 0:19:36,06 --> 0:19:40,24 effect and so on and we worked the many iterations Like crazy 383 0:19:40,24 --> 0:19:43,64 and I did it every I the whole thing is coded in Telegram. 384 0:19:43,94 --> 0:19:47,62 So I didn't touch anything Yeah, 385 0:19:47,68 --> 0:19:49,24 Michael: but you reviewed it right 386 0:19:49,66 --> 0:19:51,96 Nikolay: I reviewed what I didn't review code 387 0:19:52,2 --> 0:19:52,7 Michael: really 388 0:19:53,06 --> 0:19:56,1 Nikolay: really I did I reviewed only results of benchmarks. 389 0:19:56,84 --> 0:20:0,9 I cross-checked from 2 engineers, AI engineers, I cross-checked 390 0:20:0,92 --> 0:20:2,68 results, like they work. 391 0:20:3,18 --> 0:20:4,76 Code, I trust this code actually. 392 0:20:4,76 --> 0:20:6,38 It works in my production already. 393 0:20:7,24 --> 0:20:11,08 Yeah, why should I look at this code if I know that it was very 394 0:20:11,14 --> 0:20:14,92 thoroughly tested and benchmarked and reviewed many times by 395 0:20:14,92 --> 0:20:15,42 AI? 396 0:20:15,9 --> 0:20:15,94 — 397 0:20:15,94 --> 0:20:17,78 Michael: Oh yeah, that's the why for me. 398 0:20:17,78 --> 0:20:20,64 But yeah, anyway, it's interesting how different, yeah. 399 0:20:20,66 --> 0:20:22,84 Nikolay: I understand your question, but in this case I think 400 0:20:22,84 --> 0:20:23,94 it's quite solid. 401 0:20:23,94 --> 0:20:27,02 I actually checked the code and it follows my style. 402 0:20:27,4 --> 0:20:29,48 Michael: Wait, you just said you didn't check the code. 403 0:20:30,04 --> 0:20:31,92 Nikolay: I checked pieces of code just to ensure. 404 0:20:31,92 --> 0:20:36,46 Before I decided not to look into code, I made sure it's written 405 0:20:36,46 --> 0:20:39,88 according to our style guide, and I like what I see. 406 0:20:39,88 --> 0:20:44,72 So style corrected, how we write PL/pgSQL, I have already very 407 0:20:44,72 --> 0:20:49,54 well established, because PL/pgSQL is the most popular code language 408 0:20:49,54 --> 0:20:52,86 for me in the last 10 years, so I wrote a lot in it myself. 409 0:20:53,04 --> 0:20:56,94 So that's why, when I made sure it's producing good code, I didn't 410 0:20:56,94 --> 0:20:59,32 see the final version of everything. 411 0:20:59,38 --> 0:21:2,06 But I know it was thoroughly tested in many aspects. 412 0:21:3,1 --> 0:21:4,1 So back to storage. 413 0:21:4,1 --> 0:21:8,6 This is how we encode and how we solve this unbounded growth 414 0:21:8,62 --> 0:21:10,58 of query ID dictionary. 415 0:21:11,84 --> 0:21:12,94 My idea, actually. 416 0:21:13,08 --> 0:21:16,8 I'm excited because you can engineer solutions thinking about 417 0:21:16,92 --> 0:21:21,92 algorithms and data structures and you don't need to code everything. 418 0:21:22,06 --> 0:21:22,9 AI is coding. 419 0:21:22,9 --> 0:21:26,88 So I decided to have 3 tables, 1 pair every day, that's it. 420 0:21:27,18 --> 0:21:30,04 And the same like rotation, like we have dictionary for today, 421 0:21:30,04 --> 0:21:31,58 we have dictionary for tomorrow. 422 0:21:32,32 --> 0:21:37,36 AI said there is a doubt because query ID might not match, but 423 0:21:37,36 --> 0:21:42,08 we don't care because if the same query ID receives different 424 0:21:42,52 --> 0:21:46,58 encoded ID tomorrow, it doesn't matter because we don't work 425 0:21:46,58 --> 0:21:49,48 with this data directly, we work with this with some interface 426 0:21:49,48 --> 0:21:54,64 functions which expose it to user, and user doesn't see the encoded 427 0:21:54,64 --> 0:21:55,52 numbers at all. 428 0:21:55,52 --> 0:21:58,14 Michael: Yeah, and once you look them up, then it's the same 429 0:21:58,14 --> 0:21:59,16 query ID anyway. 430 0:21:59,2 --> 0:22:0,9 Okay, yeah, that makes sense, great. 431 0:22:0,9 --> 0:22:3,54 Nikolay: And it cannot grow unbounded anymore, right? 432 0:22:3,92 --> 0:22:3,96 Yeah. 433 0:22:3,96 --> 0:22:7,98 Yeah, And I guess for a lab approach, we also will need a separate 434 0:22:8,0 --> 0:22:8,5 dictionary. 435 0:22:8,74 --> 0:22:9,44 That's it. 436 0:22:10,08 --> 0:22:11,5801 Which might be bigger, but- 437 0:22:11,5801 --> 0:22:12,38 Michael: Wait, why? 438 0:22:13,68 --> 0:22:16,72 Nikolay: Because I want to have history for 1 year at least, 439 0:22:16,84 --> 0:22:17,5 or maybe half a year. 440 0:22:17,5 --> 0:22:18,68 Michael: Good, okay, fine. 441 0:22:18,68 --> 0:22:24,44 Nikolay: But it will be already compacted, and query IDs won't, 442 0:22:24,72 --> 0:22:28,76 not all query IDs will go there, only most important, which 443 0:22:28,94 --> 0:22:32,06 are most popular, or participating in spikes. 444 0:22:32,68 --> 0:22:38,22 Michael: Yeah, so make sure I've understood we've got 3 main 445 0:22:38,36 --> 0:22:42,8 tables, only 2 of which will contain data each time a day's worth 446 0:22:42,8 --> 0:22:48,32 of data, like all of yesterday's data and then yeah and then 447 0:22:48,32 --> 0:22:52,54 today's data until now like it's it's like yeah 1 at 1 and a 448 0:22:52,54 --> 0:22:57,4 bit days data at any point in time and that's fine because the 449 0:22:57,4 --> 0:23:2,14 main point of this is something was slow recently like we had 450 0:23:2,14 --> 0:23:6,7 an incident 10 minutes ago or an hour ago or over the weekend. 451 0:23:7,28 --> 0:23:9,76 In fact over the weekend is interesting, but there was something 452 0:23:9,76 --> 0:23:13,2 fairly recently we want to look into, and normally that's within 453 0:23:13,2 --> 0:23:14,16 a couple of days. 454 0:23:14,24 --> 0:23:17,12 Nikolay: Yeah, I think Maybe we should allow to configure how 455 0:23:17,12 --> 0:23:18,9 many days raw data is stored. 456 0:23:19,18 --> 0:23:23,04 Anyway, I know this approach with 3, it's from PgQ as I said, 457 0:23:23,04 --> 0:23:25,46 this is 3 partitions in rotation. 458 0:23:25,64 --> 0:23:29,34 You cannot see day before yesterday with it until we implement 459 0:23:29,38 --> 0:23:32,48 rollup approach for longer term storage. 460 0:23:32,98 --> 0:23:36,76 And I forgot to mention that how much of data it is. 461 0:23:36,82 --> 0:23:41,92 So, per 1 row, if it's 5 backends active, it's roughly 100 bytes 462 0:23:41,92 --> 0:23:42,42 only. 463 0:23:43,58 --> 0:23:47,62 I forgot to mention also that I decided, knowing alignment padding, 464 0:23:48,58 --> 0:23:50,78 I decided to also encode timestamps. 465 0:23:51,58 --> 0:23:55,12 So it's 4 bytes instead of 8, right? 466 0:23:55,64 --> 0:23:58,02 4 bytes Unix timestamp, but it's shifted. 467 0:23:58,38 --> 0:24:3,9 It starts with January 1st this year, so it will be enough until 468 0:24:3,9 --> 0:24:5,28 the end of the century almost. 469 0:24:6,34 --> 0:24:10,02 And the database ID, it's 4 bytes, it was already, it's all ID, 470 0:24:10,02 --> 0:24:10,94 so 4 bytes. 471 0:24:11,68 --> 0:24:16,98 8 bytes plus this encoded data, roughly 100 bytes if you have 472 0:24:16,98 --> 0:24:18,06 5 active sessions. 473 0:24:18,68 --> 0:24:23,6 And for example, if you have a more loaded machine, it will be, 474 0:24:23,76 --> 0:24:27,14 for example, 50 average sessions on on average, it will be producing 475 0:24:27,88 --> 0:24:32,3 30 to 50 megabytes per day only, which is acceptable for us, 476 0:24:32,3 --> 0:24:32,8 absolutely. 477 0:24:33,34 --> 0:24:36,92 I also forgot to mention that you cannot use it on replicas. 478 0:24:37,2 --> 0:24:38,04 This is downside. 479 0:24:38,56 --> 0:24:41,52 We forgot to mention that in README, but I thought about it before 480 0:24:41,52 --> 0:24:42,74 we implemented it. 481 0:24:42,88 --> 0:24:44,94 So it's obvious because we need to write. 482 0:24:45,1 --> 0:24:49,5 But it's fine because we observe more and more even bigger clusters 483 0:24:49,54 --> 0:24:52,44 coming to our consulting which don't have replicas. 484 0:24:53,26 --> 0:24:53,79 And they run... 485 0:24:53,79 --> 0:24:55,46 Michael: Or only HA replicas. 486 0:24:56,18 --> 0:24:57,72 Nikolay: Yeah, this is a good point. 487 0:24:57,72 --> 0:25:1,24 Actually yes, they recognize HA is needed but they are okay to 488 0:25:1,24 --> 0:25:5,16 live with 1 somehow quite long, they live with it, like it's 489 0:25:5,16 --> 0:25:5,88 so unusual. 490 0:25:6,58 --> 0:25:9,86 I still think classic 3 node setup is much better. 491 0:25:10,04 --> 0:25:14,04 But reality just showing different thing because cloud resources 492 0:25:14,06 --> 0:25:17,98 as we discussed multiple times, they are so reliable already 493 0:25:17,98 --> 0:25:19,46 that people are okay. 494 0:25:21,14 --> 0:25:24,24 And also Postgres' performance, you don't need to read replicas 495 0:25:24,24 --> 0:25:26,54 for quite some time because they add complexity. 496 0:25:26,76 --> 0:25:29,52 So that's why I thought, okay, this is only for primary. 497 0:25:29,88 --> 0:25:30,6 It's enough. 498 0:25:31,22 --> 0:25:35,44 And I'm satisfied with results like 50 megabytes per day for 499 0:25:35,44 --> 0:25:39,76 a loaded cluster it's great and I'm thinking maybe we'll like 500 0:25:39,76 --> 0:25:43,94 let's keep 7 days of raw data or 14 days of raw data maybe. 501 0:25:44,64 --> 0:25:48,08 Michael: Yeah I could easily see at least at least the weekend 502 0:25:48,08 --> 0:25:49,28 thing makes sense to me, right? 503 0:25:49,28 --> 0:25:51,56 You come in on a Monday and it turns out there was some blip 504 0:25:51,56 --> 0:25:52,12 on Saturday. 505 0:25:52,12 --> 0:25:54,4 It would be a shame if that data's gone. 506 0:25:54,64 --> 0:25:55,24 Nikolay: I agree. 507 0:25:55,24 --> 0:25:55,74 Yeah. 508 0:25:57,12 --> 0:25:57,19 I agree. 509 0:25:57,19 --> 0:26:0,86 Yeah, so, yeah, and we have a bunch of functions to which allow 510 0:26:0,86 --> 0:26:3,46 you to see what's happening. 511 0:26:4,02 --> 0:26:8,04 Give me like overview of last few hours, for example, or previous 512 0:26:8,04 --> 0:26:13,22 day, which top wait events happened and which query IDs are 513 0:26:13,84 --> 0:26:15,12 participating there. 514 0:26:15,42 --> 0:26:15,92 Yeah. 515 0:26:16,74 --> 0:26:20,22 And it joins with pg_stat_statements if it's available to present 516 0:26:20,22 --> 0:26:23,68 some high-level settings, macro-level settings for each query 517 0:26:23,68 --> 0:26:27,08 ID which is participating in some wait events. 518 0:26:27,28 --> 0:26:30,54 We also implemented visualization with bars. 519 0:26:31,38 --> 0:26:35,52 It can be monochrome visualization or even colorful for psql 520 0:26:35,66 --> 0:26:37,08 if you do some trick. 521 0:26:37,48 --> 0:26:39,3 And I enjoy it. 522 0:26:39,52 --> 0:26:42,28 You see basically performance insights right inside psql. 523 0:26:42,66 --> 0:26:43,72 It's quite fun. 524 0:26:44,06 --> 0:26:47,82 And you can, For example, overview last 24 hours, then you think 525 0:26:47,88 --> 0:26:52,4 you can zoom to specific area, you can understand which queries. 526 0:26:53,76 --> 0:26:58,5 And of course it requires some typing work, but since how it's 527 0:26:58,5 --> 0:27:2,9 organized, quite straightforward, You can let your LLM to do 528 0:27:2,9 --> 0:27:4,4 it, right? 529 0:27:4,46 --> 0:27:9,5 If you connect using some read-only user role, and let it troubleshoot, 530 0:27:9,58 --> 0:27:12,22 it can quickly find which bottlenecks. 531 0:27:12,34 --> 0:27:16,76 Is it like I-O bound workload, you need to increase disks or 532 0:27:16,76 --> 0:27:17,72 give more memory. 533 0:27:18,22 --> 0:27:22,0 Or it's like there is a heavyweight lock contention, which queries 534 0:27:22,0 --> 0:27:22,5 participate. 535 0:27:23,42 --> 0:27:26,1 It's quite good in terms of this kind of troubleshooting. 536 0:27:27,04 --> 0:27:28,78 Michael: Yeah, definitely macro level stuff. 537 0:27:28,78 --> 0:27:32,8 But I saw there was even some micro level abilities as well, 538 0:27:32,8 --> 0:27:36,6 like looking at a specific query's waits by the query ID. 539 0:27:36,76 --> 0:27:37,72 Nikolay: That's pretty cool. 540 0:27:37,72 --> 0:27:41,2 Yeah, you can ask, for example, for a specific query ID you can 541 0:27:41,2 --> 0:27:42,6 say which wait events. 542 0:27:44,02 --> 0:27:47,3 It mimics a little bit what we have in full-fledged monitoring, 543 0:27:47,32 --> 0:27:48,62 because we have all this. 544 0:27:49,02 --> 0:27:53,4 And here is just like in textual form you can do this kind of 545 0:27:53,4 --> 0:27:53,8 thing. 546 0:27:53,8 --> 0:27:57,1 For this query ID, what are wait events during last hour or previous 547 0:27:57,1 --> 0:27:57,6 day? 548 0:27:57,9 --> 0:28:2,58 And for this wait event type or specific wait event, what are 549 0:28:2,58 --> 0:28:6,6 like top 5 query IDs and their macro level characteristics. 550 0:28:7,2 --> 0:28:10,84 Yeah, it's not, it's not, it's still macro level actually, because 551 0:28:10,84 --> 0:28:14,54 you're still like a lot of aggregation is happening here, but 552 0:28:14,54 --> 0:28:19,2 yeah, You can jump to micro level specific query ID and see plans 553 0:28:19,2 --> 0:28:20,78 and start working with this. 554 0:28:20,84 --> 0:28:22,36 So there is a bridge here, right? 555 0:28:22,36 --> 0:28:23,4 Yeah, there is a bridge. 556 0:28:23,4 --> 0:28:26,76 So anyway, it's early days. 557 0:28:26,76 --> 0:28:28,22 I'm releasing this week. 558 0:28:28,26 --> 0:28:29,68 Let's see how it works. 559 0:28:30,06 --> 0:28:31,5 Is it useful or no? 560 0:28:31,72 --> 0:28:36,36 I wanted to just to add that this thing feels to me as... 561 0:28:36,56 --> 0:28:38,5 Oh, yeah, 1 more thing. 562 0:28:39,18 --> 0:28:43,36 So this is poor man's monitoring, self-monitoring, right? 563 0:28:44,44 --> 0:28:45,48 Michael: I think it's not... 564 0:28:45,48 --> 0:28:47,14 I don't think so, but yeah, sure. 565 0:28:47,5 --> 0:28:49,14 Nikolay: — It's quite straightforward. 566 0:28:50,02 --> 0:28:53,04 This trick with pg_cron — I forgot to mention, by the way, that 567 0:28:53,2 --> 0:28:57,48 if we sample every second, pg_cron by default writes logs to 568 0:28:57,48 --> 0:28:58,12 a table. 569 0:28:58,28 --> 0:28:58,48 — 570 0:28:58,48 --> 0:28:59,2 Michael: Oh, yeah. 571 0:28:59,28 --> 0:28:59,54 Yeah. 572 0:28:59,54 --> 0:29:2,44 Nikolay: — Yeah, so I think pg_cron can be improved here. 573 0:29:2,44 --> 0:29:5,34 For example, I would turn it off for this specific job. 574 0:29:6,22 --> 0:29:7,42 I don't need logging here. 575 0:29:7,42 --> 0:29:11,18 I just need to see errors and I don't need errors to be stored 576 0:29:11,82 --> 0:29:12,88 with full EcID. 577 0:29:13,7 --> 0:29:16,62 Postgres log itself would be enough for me to troubleshoot this. 578 0:29:17,04 --> 0:29:20,88 Anyway, there's a potential for pg_cron here to be slightly improved 579 0:29:20,9 --> 0:29:22,86 for these high-frequency jobs. 580 0:29:23,3 --> 0:29:26,94 But what I wanted to say, this is a self-contained, self-monitored 581 0:29:27,34 --> 0:29:27,84 system. 582 0:29:29,02 --> 0:29:32,54 Even without LLM, you can write some analysis inside Postgres 583 0:29:32,56 --> 0:29:35,74 so it could produce some reports and analyze what's happening. 584 0:29:35,74 --> 0:29:39,52 Maybe it's time to add more, increase shared_buffers, for example, 585 0:29:39,52 --> 0:29:42,6 because we see a lot of I/O data file read or something. 586 0:29:42,98 --> 0:29:46,32 Or maybe we see a lot of time spent on heavyweight locks, so 587 0:29:46,32 --> 0:29:50,14 we need to signal that workload must be redesigned to avoid contention 588 0:29:50,18 --> 0:29:51,68 on heavyweight locks, right? 589 0:29:51,92 --> 0:29:54,18 But there is a trade-off. 590 0:29:54,56 --> 0:29:56,82 Should we monitor inside or outside? 591 0:29:57,1 --> 0:29:58,48 And I think there are pros and cons. 592 0:29:58,48 --> 0:29:59,94 We discussed it, right, already? 593 0:30:0,1 --> 0:30:0,88 Michael: We did. 594 0:30:1,86 --> 0:30:3,26 I had 1 last question though. 595 0:30:3,26 --> 0:30:7,2 I think, by the way, the reason I disagree is I think it's actually 596 0:30:7,2 --> 0:30:9,52 just another tool, right, it's another tool in the tool belt. 597 0:30:9,52 --> 0:30:13,26 If you self-manage or use a provider that doesn't have wait 598 0:30:13,26 --> 0:30:17,2 sampling, this is a valuable tool in its own right, like regardless 599 0:30:17,2 --> 0:30:19,9 of monitoring, especially when you mentioned the overhead from 600 0:30:19,9 --> 0:30:23,22 monitoring externally, you'd only get let's say 15 second frequency 601 0:30:23,4 --> 0:30:25,28 or something of that order of magnitude. 602 0:30:25,28 --> 0:30:30,82 To get 1 second frequencies is an upgrade, not like a poor man's 603 0:30:30,82 --> 0:30:31,32 solution. 604 0:30:31,36 --> 0:30:33,44 Nikolay: Yeah, it's not 10 milliseconds. 605 0:30:34,46 --> 0:30:36,7 Michael: It's true, good point, good point. 606 0:30:36,86 --> 0:30:39,84 So the last thing I wanted to ask, cause you always ask our guests 607 0:30:39,84 --> 0:30:43,22 this, the, on the license front, you've chosen a really permissive 608 0:30:43,26 --> 0:30:44,94 license, Apache 2.0. 609 0:30:45,78 --> 0:30:46,28 Why? 610 0:30:46,92 --> 0:30:47,58 Nikolay: Why not? 611 0:30:47,78 --> 0:30:49,78 It can be embeddable to some stuff. 612 0:30:49,84 --> 0:30:53,8 And I even think, honestly, let me to be transparent here. 613 0:30:54,18 --> 0:30:58,58 I think it will contribute to my business because if we have 614 0:30:58,58 --> 0:31:1,68 this everywhere, it's easier for us to explain which problems 615 0:31:1,68 --> 0:31:5,28 and what should be done about it and improve health and support 616 0:31:5,28 --> 0:31:7,4 people like we do in Postgres. 617 0:31:7,8 --> 0:31:12,1 So we're right now not in quite a good position because Cloud 618 0:31:12,1 --> 0:31:16,86 SQL, this is 1 of not many but quite great things they have, 619 0:31:16,94 --> 0:31:19,74 is that they have pg_wait_sampling, super great. 620 0:31:20,16 --> 0:31:23,88 But RDS, they have their own proprietary tool, Performance Insights, 621 0:31:23,88 --> 0:31:28,4 which is great if you only own AWS, but if you think about interoperability 622 0:31:28,74 --> 0:31:31,82 or how to say, like you want to be able to work with any Postgres, 623 0:31:32,22 --> 0:31:35,34 performance insights, database insights, they call it right now. 624 0:31:35,34 --> 0:31:37,88 It's some proprietary API and it's not transparent. 625 0:31:38,0 --> 0:31:39,68 And also external dependency. 626 0:31:40,16 --> 0:31:42,5 I always think, okay, how good is it? 627 0:31:42,5 --> 0:31:43,24 Is it trustworthy? 628 0:31:43,42 --> 0:31:44,58 Maybe there are also bugs. 629 0:31:44,58 --> 0:31:47,02 I don't see a code, right? 630 0:31:47,64 --> 0:31:47,988 I cannot know. 631 0:31:47,988 --> 0:31:48,42 I don't know. 632 0:31:48,42 --> 0:31:50,26 Maybe there are bugs there. 633 0:31:50,46 --> 0:31:54,52 RDS quality is great, like let's agree, RDS quality is great, 634 0:31:54,52 --> 0:31:59,08 but still like sometimes you doubt like how exactly it works. 635 0:31:59,6 --> 0:32:3,4 This is fully transparent, works everywhere, right, and that's 636 0:32:3,4 --> 0:32:3,9 it. 637 0:32:4,28 --> 0:32:5,1 Michael: Yeah, nice. 638 0:32:5,22 --> 0:32:5,94 All right. 639 0:32:6,54 --> 0:32:7,76 Thanks so much, Nikolay. 640 0:32:8,56 --> 0:32:9,38 Nikolay: Yeah, thank you. 641 0:32:9,38 --> 0:32:14,56 So yeah, everyone is welcome to try and contribute and fork anything. 642 0:32:15,06 --> 0:32:18,66 Michael: Yeah, I will stick it in the show notes, obviously. 643 0:32:19,28 --> 0:32:19,46 Nikolay: Right. 644 0:32:19,46 --> 0:32:20,44 Michael: Make it nice and easy. 645 0:32:20,44 --> 0:32:21,02 Nikolay: Thank you. 646 0:32:21,02 --> 0:32:21,22 Michael: Yeah. 647 0:32:21,22 --> 0:32:21,72 Cool. 648 0:32:22,2 --> 0:32:23,1 All right, take care. 649 0:32:23,1 --> 0:32:23,98 Catch you in a bit.