1 00:00:00,180 --> 00:00:02,560 Michael: Hello and welcome to Postgres.FM, a weekly show about 2 00:00:02,560 --> 00:00:03,460 all things PostgreSQL. 3 00:00:03,580 --> 00:00:06,060 I am Michael, founder of pgMustard and I'm joined as usual 4 00:00:06,060 --> 00:00:07,720 by Nikolay, founder of Postgres AI. 5 00:00:07,720 --> 00:00:08,820 Hey Nik, how you doing? 6 00:00:09,620 --> 00:00:10,240 Nikolay: Hi, Michael. 7 00:00:10,240 --> 00:00:11,180 I'm doing great. 8 00:00:11,320 --> 00:00:12,140 How are you? 9 00:00:12,840 --> 00:00:13,200 Michael: Good. 10 00:00:13,200 --> 00:00:13,880 Thank you. 11 00:00:14,440 --> 00:00:19,260 And this week is launch week or was maybe if people are listening 12 00:00:19,740 --> 00:00:20,580 in the future. 13 00:00:21,040 --> 00:00:24,080 Nikolay: Yeah, I can tell you, I can assure you people are listening 14 00:00:24,080 --> 00:00:26,260 to us in the future because we are not live. 15 00:00:26,280 --> 00:00:27,180 Michael: Yeah, of course. 16 00:00:27,260 --> 00:00:30,900 So yes, so launch week for your company, Postgres AI. 17 00:00:31,020 --> 00:00:32,140 Nikolay: Yeah, for the first time. 18 00:00:32,140 --> 00:00:32,820 I'm excited. 19 00:00:33,060 --> 00:00:33,380 Yeah. 20 00:00:33,380 --> 00:00:36,460 So we decided enough observing how others do it. 21 00:00:36,460 --> 00:00:40,540 First of all, Supabase, they just had launch week before this. 22 00:00:40,960 --> 00:00:42,600 I think number 15 already. 23 00:00:43,380 --> 00:00:44,120 So many. 24 00:00:45,300 --> 00:00:50,800 And since our team has grown and we had stuff accumulated to 25 00:00:50,800 --> 00:00:55,200 be released, so instead of releasing it earlier, I said let's 26 00:00:55,200 --> 00:00:58,460 just accumulate a little bit and release in 1 week. 27 00:00:58,940 --> 00:01:02,180 It's like sprint 5 days in a row. 28 00:01:02,180 --> 00:01:06,540 And we actually, we are releasing more, we, I hope we released 29 00:01:06,580 --> 00:01:09,960 by the time people are listening, I hope we released more than 30 00:01:09,960 --> 00:01:10,740 5 things. 31 00:01:11,200 --> 00:01:14,200 So we have also concept of extra bonus releases. 32 00:01:15,040 --> 00:01:18,340 And we started on Monday, we started with DBLab version 4, which 33 00:01:18,340 --> 00:01:20,140 actually took 2 years to develop. 34 00:01:20,540 --> 00:01:22,160 Michael: Yeah, that's a long time. 35 00:01:22,200 --> 00:01:25,460 Nikolay: Yeah, finally we polished it and many users already 36 00:01:26,000 --> 00:01:26,980 installed it. 37 00:01:26,980 --> 00:01:28,160 I mean release candidates. 38 00:01:28,820 --> 00:01:30,780 We needed to have 5 release candidates. 39 00:01:33,580 --> 00:01:35,320 Michael: That's more than Postgres. 40 00:01:36,220 --> 00:01:36,600 Nikolay: Yeah, yeah. 41 00:01:36,600 --> 00:01:40,680 Well, because a lot of new problems appeared because we changed 42 00:01:40,680 --> 00:01:41,580 a lot there. 43 00:01:41,580 --> 00:01:43,040 Yeah, but it's another story. 44 00:01:43,100 --> 00:01:46,880 Today we talk about our second big release, which is called Postgres AI 45 00:01:46,880 --> 00:01:47,700 Monitoring. 46 00:01:47,980 --> 00:01:50,280 Michael: Yeah, so this is 1 you're doing, so we're recording 47 00:01:50,280 --> 00:01:50,840 on Monday. 48 00:01:50,840 --> 00:01:52,320 This is 1 you're doing tomorrow. 49 00:01:52,540 --> 00:01:53,800 So what is it? 50 00:01:53,800 --> 00:01:54,880 What are you up to? 51 00:01:54,960 --> 00:01:56,820 Nikolay: Yes, we are doing this tomorrow. 52 00:01:56,820 --> 00:01:58,360 Everything is ready already. 53 00:01:58,520 --> 00:02:01,360 So I'm pretty confident we are doing this. 54 00:02:02,480 --> 00:02:02,980 Yeah. 55 00:02:03,280 --> 00:02:06,620 And only a few people know that we are releasing this kind of 56 00:02:06,620 --> 00:02:07,120 monitoring. 57 00:02:07,720 --> 00:02:10,840 And it's called Postgres AI monitoring, but right now there is 58 00:02:10,840 --> 00:02:12,140 a little AI there. 59 00:02:12,440 --> 00:02:13,740 And this is intentional. 60 00:02:15,040 --> 00:02:19,200 Let me explain this first, because of course we had a short chat 61 00:02:19,200 --> 00:02:23,740 before recording and you noticed this discrepancy. 62 00:02:26,400 --> 00:02:27,820 We use AI a lot. 63 00:02:27,860 --> 00:02:33,340 Every day, for example, in my team, it's prohibited to do anything 64 00:02:33,480 --> 00:02:34,500 without AI-first. 65 00:02:34,860 --> 00:02:36,580 It's prohibited to write code. 66 00:02:36,580 --> 00:02:37,660 It's already so. 67 00:02:37,660 --> 00:02:42,420 Well, you can write code, but only if your AI tools failed. 68 00:02:43,520 --> 00:02:47,020 And first people were resistant to this. 69 00:02:50,460 --> 00:02:55,020 Some people said, like, what the heck, I don't want it. 70 00:02:55,760 --> 00:02:58,040 It's bad, it's poor quality. 71 00:02:58,280 --> 00:03:02,780 But in my opinion, the Anthropic release of Cloud 4 a couple 72 00:03:02,780 --> 00:03:04,700 of months ago, it was a game changer. 73 00:03:06,500 --> 00:03:09,020 Quality already very acceptable in many cases. 74 00:03:09,020 --> 00:03:13,780 Not always, sometimes, especially if you are expert in some narrow 75 00:03:13,780 --> 00:03:19,700 topic, you understand problems which were never discussed publicly. 76 00:03:20,740 --> 00:03:25,460 In this case, of course, you will see AI will be wrong 100%. 77 00:03:26,600 --> 00:03:27,760 We have these cases. 78 00:03:28,140 --> 00:03:33,100 For example, a perfect example is our guests last week. 79 00:03:33,700 --> 00:03:38,640 They experienced some problem which was not covered by public 80 00:03:38,640 --> 00:03:39,140 discussions. 81 00:03:40,200 --> 00:03:46,420 Obviously, in such cases, if you just apply AI in a straightforward 82 00:03:46,480 --> 00:03:48,740 way, it will hallucinate some bullshit. 83 00:03:49,540 --> 00:03:54,240 But if you apply AI to brainstorm some ideas and ground it, pre-ground, 84 00:03:54,360 --> 00:03:58,300 post-ground with materials, reliable materials like source code, 85 00:03:58,300 --> 00:04:03,680 documentation, information from reliable sources and experiments, 86 00:04:03,860 --> 00:04:06,740 post-grounding, in this case it's different. 87 00:04:07,560 --> 00:04:11,440 You can explore, behave, reproduce it, and this is the path we 88 00:04:11,440 --> 00:04:11,940 chose. 89 00:04:12,600 --> 00:04:18,700 Not just this problem, take AI and create, I don't know, like 90 00:04:18,700 --> 00:04:20,040 change requests somewhere. 91 00:04:21,060 --> 00:04:21,960 This won't work. 92 00:04:21,960 --> 00:04:23,860 Ungrounded approach won't work. 93 00:04:24,340 --> 00:04:26,620 So this is 1 point. 94 00:04:26,680 --> 00:04:32,420 Another point is that 6, 7 years I keep talking, I avoid building 95 00:04:32,560 --> 00:04:33,660 monitoring system. 96 00:04:36,100 --> 00:04:39,680 For example, when I met with Lukas Fittl in San Francisco 1 97 00:04:39,680 --> 00:04:43,740 day a few years ago, I told him this, like, you're building great 98 00:04:43,740 --> 00:04:45,740 monitoring system, pganalyze. 99 00:04:46,640 --> 00:04:49,680 We build some checkup because we need for our customers to find 100 00:04:49,680 --> 00:04:53,000 problems and then to start exploring them, experimenting. 101 00:04:54,960 --> 00:04:58,360 Our key strength of Postgres AI team is experiments. 102 00:04:58,860 --> 00:05:03,920 That's why we built DBLab to simplify specific type of experiments 103 00:05:03,940 --> 00:05:05,640 and benchmarks and so on. 104 00:05:05,820 --> 00:05:11,780 And I mentioned checkups and somehow pganalyze has checkup section 105 00:05:11,780 --> 00:05:13,760 since then, but it's a different story. 106 00:05:14,340 --> 00:05:17,840 And I said I'm not going to build monitoring system, but it was 107 00:05:17,840 --> 00:05:21,740 inevitably like I was pushed somehow to this point where it's 108 00:05:21,740 --> 00:05:26,680 inevitable that we need to do it because no other system, and 109 00:05:26,680 --> 00:05:32,920 not only open source system, not other system is providing abilities 110 00:05:33,160 --> 00:05:33,840 we need. 111 00:05:33,840 --> 00:05:36,660 We need several specific abilities with our clients. 112 00:05:36,660 --> 00:05:37,920 We always show it. 113 00:05:38,320 --> 00:05:40,820 And some of them is for troubleshooting. 114 00:05:41,040 --> 00:05:45,520 So like high, very high level analysis of Postgres components. 115 00:05:46,020 --> 00:05:48,980 And then you can understand where you can dive deeper. 116 00:05:49,540 --> 00:05:53,660 And actually, I remember great talks from Percona, from Zaitsev, 117 00:05:53,760 --> 00:05:54,520 and so on. 118 00:05:54,520 --> 00:05:58,820 And I saw some bits of good stuff here and there, like 119 00:05:59,060 --> 00:06:02,060 Percona Monitoring and Management, or how is it called? 120 00:06:02,440 --> 00:06:02,940 Michael: PMM. 121 00:06:04,540 --> 00:06:05,040 Nikolay: Yeah. 122 00:06:05,060 --> 00:06:06,140 Yeah, yeah, yeah. 123 00:06:07,260 --> 00:06:09,360 And things like use and 4 golden signals approaches like methodologies. 124 00:06:10,840 --> 00:06:12,680 Some great pieces there. 125 00:06:12,840 --> 00:06:15,200 But then you see something is really missing. 126 00:06:15,200 --> 00:06:17,120 Like For example, proper query analysis. 127 00:06:17,120 --> 00:06:19,060 We talked about it so many times. 128 00:06:20,020 --> 00:06:23,740 Comprehensive analysis using pg_stat_statements, pg_stat_kcache, 129 00:06:23,740 --> 00:06:28,900 pg_wait_sampling, top-down, single query with query texts with 130 00:06:28,900 --> 00:06:30,580 proper characteristics. 131 00:06:31,370 --> 00:06:36,420 And We didn't see any system like this. 132 00:06:36,420 --> 00:06:40,840 A few years ago, we started maintaining so-called pgwatch2 Postgres AI 133 00:06:41,000 --> 00:06:48,960 edition, which was a fork of pgwatch2 with our metrics and dashboards. 134 00:06:50,220 --> 00:06:53,580 But then Pavlo Golub decided to rewrite pgwatch. 135 00:06:53,940 --> 00:06:58,680 So we started lagging in terms of this vehicle we used as pgwatch 136 00:06:58,740 --> 00:06:59,240 itself. 137 00:07:00,900 --> 00:07:04,700 So time has come and we realized, okay, and we experimented a 138 00:07:04,700 --> 00:07:04,960 lot. 139 00:07:04,960 --> 00:07:07,700 We actually started with InfluxDB. 140 00:07:07,960 --> 00:07:08,760 It was insane. 141 00:07:09,140 --> 00:07:10,380 We eliminated it. 142 00:07:10,380 --> 00:07:11,400 We used TimescaleDB. 143 00:07:12,360 --> 00:07:15,140 And then we had a big thinking process. 144 00:07:15,400 --> 00:07:19,660 Should we stay with PureSQL and TimescaleDB, or we should use 145 00:07:19,660 --> 00:07:20,160 PromQL? 146 00:07:20,740 --> 00:07:23,160 We decided to use PromQL, but with some nuances. 147 00:07:23,160 --> 00:07:24,640 We still have Postgres inside. 148 00:07:24,840 --> 00:07:27,080 So we have both Prometheus and Postgres. 149 00:07:27,940 --> 00:07:32,380 So the idea was we need to build a new monitoring system, which 150 00:07:32,380 --> 00:07:35,360 will be part of our process. 151 00:07:36,040 --> 00:07:42,960 And in consulting, we had maybe 30 clients over the last 1 year 152 00:07:42,960 --> 00:07:43,640 and a half. 153 00:07:44,280 --> 00:07:47,180 We had a few dozens of clients, right? 154 00:07:47,440 --> 00:07:51,840 And to all of them, we installed always our pgwatch2 Postgres AI 155 00:07:51,840 --> 00:07:55,460 edition, and also used 156 00:07:55,460 --> 00:08:00,220 postgres-checkup tool, old tool we 157 00:08:00,320 --> 00:08:01,580 always used, to generate reports in static form, like markdown 158 00:08:01,640 --> 00:08:04,100 or HTML PDF. 159 00:08:04,960 --> 00:08:09,920 So this time we said, okay, we finally do monitoring, but the 160 00:08:09,920 --> 00:08:12,040 main part of it will be open source. 161 00:08:12,080 --> 00:08:15,100 So monitoring itself is open source, but it's a part of bigger, 162 00:08:15,100 --> 00:08:20,020 bigger picture where we perform health checks and help clients 163 00:08:20,020 --> 00:08:22,980 proactively using AI, of course. 164 00:08:24,240 --> 00:08:26,260 Michael: So I'm going to ask your question always. 165 00:08:26,260 --> 00:08:28,760 When you say open source, what do you mean? 166 00:08:30,220 --> 00:08:31,660 Nikolay: License is Apache 2.0. 167 00:08:33,340 --> 00:08:38,980 As I remember, pgwatch is a BSD or Postgres license? 168 00:08:40,080 --> 00:08:43,860 1 of these 2, but our dashboards are Apache 2.0, so anyone can 169 00:08:43,860 --> 00:08:44,620 use it. 170 00:08:44,680 --> 00:08:46,200 Majority of that is... 171 00:08:47,980 --> 00:08:53,340 So we couldn't use a so-called traditional Postgres exporter 172 00:08:53,680 --> 00:08:57,680 for Prometheus because it's so much stuff and it's really slow 173 00:08:59,140 --> 00:09:01,120 to put pieces there. 174 00:09:01,180 --> 00:09:05,100 So basically we have our own exporter inside, I mean, metrics 175 00:09:05,090 --> 00:09:06,680 and dashboards. 176 00:09:07,280 --> 00:09:08,080 And it's Prometheus. 177 00:09:08,500 --> 00:09:09,220 Why Prometheus? 178 00:09:09,440 --> 00:09:14,180 Because we just see that in many cases, well, several reasons. 179 00:09:15,060 --> 00:09:21,740 1 of the key reasons is that we had already 2, actually 3 companies 180 00:09:21,900 --> 00:09:26,760 where we, in consulting mode, we contributed a lot to implementing 181 00:09:26,960 --> 00:09:29,720 our approach to their own monitoring system. 182 00:09:30,040 --> 00:09:34,820 So this is why we have confidence this monitoring system is great. 183 00:09:34,900 --> 00:09:38,100 And usually, bigger companies, they have some stack. 184 00:09:39,000 --> 00:09:43,440 Some people use Datadog, but you know, if you have 100 terabytes 185 00:09:43,440 --> 00:09:48,380 of data, or say, 1000 clusters, like microservices, Datadog is 186 00:09:48,380 --> 00:09:49,120 super expensive. 187 00:09:49,440 --> 00:09:52,180 Some people still keep paying. 188 00:09:53,040 --> 00:09:58,440 But it's not only expensive, it has blind spots in terms of Postgres 189 00:09:58,440 --> 00:09:58,820 monitoring. 190 00:09:58,820 --> 00:10:04,040 A lot of them, for example, pg_stat_statements, they have some 191 00:10:04,540 --> 00:10:05,520 not all metrics. 192 00:10:05,740 --> 00:10:10,460 And for essential metrics, they, for example, can give you per 193 00:10:10,460 --> 00:10:13,880 call, but not per second, or per second, but not per call. 194 00:10:13,940 --> 00:10:15,980 There is no completeness there. 195 00:10:16,080 --> 00:10:20,880 If you check carefully, so they differentiate in 1 dimension, 196 00:10:20,900 --> 00:10:21,840 but not another. 197 00:10:21,980 --> 00:10:23,660 For another metric, it's vice versa. 198 00:10:23,660 --> 00:10:25,860 So it lacks many things. 199 00:10:26,280 --> 00:10:28,440 And also how dashboards are organized. 200 00:10:30,040 --> 00:10:35,060 I don't understand why people put Tuple stats on the front page. 201 00:10:35,060 --> 00:10:37,040 It's not the main metric to observe. 202 00:10:37,360 --> 00:10:38,440 Not the main metric. 203 00:10:38,740 --> 00:10:42,260 It's not the number 1 thing you want to see when you have an 204 00:10:42,260 --> 00:10:43,940 incident and need to troubleshoot. 205 00:10:45,920 --> 00:10:50,500 And people don't put queries per second because Postgres doesn't 206 00:10:50,500 --> 00:10:51,680 have queries per second. 207 00:10:52,380 --> 00:10:56,260 But we can extract it from previous assignments with some asterisk 208 00:10:56,380 --> 00:11:00,360 that it's approximate because some things can be lost. 209 00:11:00,560 --> 00:11:05,780 Sometimes we see queries per second are lower than transactions 210 00:11:05,820 --> 00:11:07,620 per second, but it cannot be so. 211 00:11:07,740 --> 00:11:08,240 Right? 212 00:11:09,140 --> 00:11:09,230 Yeah. 213 00:11:09,230 --> 00:11:13,140 In fact, because we don't see full picture, but in most cases 214 00:11:13,140 --> 00:11:13,940 it's vice versa. 215 00:11:13,940 --> 00:11:15,180 So we see vice versa. 216 00:11:15,520 --> 00:11:20,760 So this monitoring is open source fully, and it can be used in 217 00:11:20,760 --> 00:11:25,060 pieces if your organization already has Prometheus-based something. 218 00:11:28,840 --> 00:11:31,880 But lacks a systematic approach for dashboards. 219 00:11:32,420 --> 00:11:38,420 This is where we can, I mean, you can take this and improve, 220 00:11:38,940 --> 00:11:41,080 take pieces, maybe contribute back? 221 00:11:41,920 --> 00:11:43,940 Also good idea to contribute back always. 222 00:11:45,440 --> 00:11:50,220 And I still think, although I personally invested like more than 223 00:11:50,220 --> 00:11:51,980 5 years already to this. 224 00:11:53,300 --> 00:11:56,580 First few years I wanted to say, no, no, I'm not going to do 225 00:11:56,580 --> 00:11:56,760 it. 226 00:11:56,760 --> 00:11:57,940 But then I was forced. 227 00:11:58,040 --> 00:12:00,740 Well, well, life sometimes force you to do something right. 228 00:12:01,120 --> 00:12:05,500 And now this is my like, I resurrected to this idea, like, okay, 229 00:12:05,500 --> 00:12:06,520 I'm doing this. 230 00:12:08,180 --> 00:12:13,120 And yeah, and not only I, we have really super experienced members 231 00:12:13,140 --> 00:12:18,120 in our team, who also spent a lot of time thinking and contributing 232 00:12:18,260 --> 00:12:18,960 to this. 233 00:12:19,340 --> 00:12:22,880 It's not only my own effort. 234 00:12:23,800 --> 00:12:27,740 So we have several very good team members who contributed a lot. 235 00:12:28,100 --> 00:12:31,360 So we decided to use PromQL also because of this, because it's 236 00:12:31,360 --> 00:12:33,340 more compatible with enterprise systems. 237 00:12:34,820 --> 00:12:37,980 Michael: Yeah, you mentioned using Prometheus, but also Postgres. 238 00:12:38,320 --> 00:12:40,560 Nikolay: But also Postgres, because in Prometheus there is a 239 00:12:40,560 --> 00:12:41,900 problem with query texts. 240 00:12:43,080 --> 00:12:47,240 In query analysis, you do want, like you have to just have statements, 241 00:12:48,160 --> 00:12:52,440 But in Prometheus, it's not normal to get query and store it. 242 00:12:53,420 --> 00:12:54,560 It's very inefficient. 243 00:12:55,580 --> 00:12:59,040 So you need secondary storage, and we chose Postgres, obviously. 244 00:13:00,040 --> 00:13:01,580 Basically we have 2 data stores. 245 00:13:02,220 --> 00:13:04,020 1 is the main 1, Prometheus. 246 00:13:05,280 --> 00:13:06,920 And the second 1 is... 247 00:13:07,640 --> 00:13:09,280 Actually we are debating right now. 248 00:13:09,280 --> 00:13:10,140 We probably will... 249 00:13:10,140 --> 00:13:11,660 Prometheus, it will be Mimir. 250 00:13:12,120 --> 00:13:14,200 We also thought about Victoria Metrics. 251 00:13:14,200 --> 00:13:15,420 So it's still... 252 00:13:15,660 --> 00:13:19,260 We will switch in the next few releases to something else. 253 00:13:19,820 --> 00:13:23,140 And for query texts, it's Postgres, but it's a small 1. 254 00:13:23,260 --> 00:13:27,080 Because you know, by default, pg_stat_statements for each node, 255 00:13:27,160 --> 00:13:29,420 5000 statements. 256 00:13:30,060 --> 00:13:31,320 And do we track all of them? 257 00:13:31,320 --> 00:13:32,260 I need to check. 258 00:13:32,260 --> 00:13:33,060 Maybe no. 259 00:13:33,260 --> 00:13:37,780 So 1 of the things we noticed, we had cases with our previous 260 00:13:37,780 --> 00:13:41,760 monitoring, that sometimes people have millions of objects in 261 00:13:41,760 --> 00:13:42,340 the database. 262 00:13:43,660 --> 00:13:46,460 And this is really requires optimization. 263 00:13:47,160 --> 00:13:51,360 And we made this optimization for really large cases. 264 00:13:51,580 --> 00:13:55,080 I remember our checkup actually also had cases with 100,000 265 00:13:56,100 --> 00:13:56,600 indexes. 266 00:13:57,280 --> 00:14:02,860 In case of monitoring pgwatch2, it was previous version, basically, 267 00:14:02,860 --> 00:14:03,360 right? 268 00:14:04,260 --> 00:14:07,760 Our Postgres AI edition, we had cases with more than 1 million 269 00:14:07,900 --> 00:14:11,300 indexes and hundreds of thousands of tables. 270 00:14:11,780 --> 00:14:13,760 Bloat analysis was not working. 271 00:14:14,540 --> 00:14:18,560 But like, it's and this is interesting and a separate question, 272 00:14:19,540 --> 00:14:24,120 how to perform index maintenance in such a situation, for example. 273 00:14:24,580 --> 00:14:25,080 But 274 00:14:25,080 --> 00:14:29,040 Michael: it's also a weakness of pg_stat_statements, right? 275 00:14:29,040 --> 00:14:33,260 Because often, I don't know if this is the case always, but often 276 00:14:33,260 --> 00:14:37,920 when you see that number of objects, it's because somebody's 277 00:14:38,000 --> 00:14:40,620 doing per tenant, but by schema. 278 00:14:40,760 --> 00:14:46,580 So a lot of the tables are the same dozen or several dozen tables 279 00:14:47,120 --> 00:14:47,860 and indexes. 280 00:14:48,600 --> 00:14:53,100 And then pg_stat_statements kind of falls flat on its face because 281 00:14:53,300 --> 00:14:56,520 it sees objects as unique and it tracks them all individually. 282 00:14:56,580 --> 00:14:58,380 So you can't look at. 283 00:14:58,380 --> 00:14:59,240 It's flooded. 284 00:14:59,340 --> 00:15:01,220 Yeah, and 5000 is not enough. 285 00:15:01,220 --> 00:15:02,660 Nikolay: It's not aggregating properly. 286 00:15:02,720 --> 00:15:03,860 Michael: Exactly, yeah. 287 00:15:03,860 --> 00:15:06,860 Nikolay: Yeah, we discussed this recently also, there is a problem 288 00:15:06,860 --> 00:15:08,580 with temporary tables, for example, 289 00:15:09,460 --> 00:15:09,960 Michael: sometimes 290 00:15:10,840 --> 00:15:16,100 Nikolay: it's like the same schema, same table, but somehow you 291 00:15:16,100 --> 00:15:19,260 end up having different queries, 2 different queries and pages 292 00:15:19,260 --> 00:15:20,480 are set in the same. 293 00:15:21,340 --> 00:15:26,300 I would personally prefer some control how aggregation is done. 294 00:15:28,320 --> 00:15:34,700 There is a compute_query_id mechanism. 295 00:15:35,500 --> 00:15:39,400 So basically it would be great to define a rule, for example, 296 00:15:39,400 --> 00:15:42,180 ignore namespace, ignore schema name, right? 297 00:15:42,720 --> 00:15:43,580 Michael: Yeah, wow. 298 00:15:43,780 --> 00:15:44,940 Yeah, interesting idea. 299 00:15:45,540 --> 00:15:46,160 Nikolay: Yeah, maybe. 300 00:15:46,160 --> 00:15:48,360 Michael: Because the object, the other table names will probably, 301 00:15:48,640 --> 00:15:51,340 for most people, the table names would stay the same, it's the 302 00:15:51,340 --> 00:15:52,700 schema name that would change. 303 00:15:53,100 --> 00:15:56,160 Nikolay: When analyzing logs, I still prefer, I know since Postgres 304 00:15:56,160 --> 00:15:58,540 16 we have query ID in the logs. 305 00:15:58,780 --> 00:16:03,580 We can't have, we need to enable it for auto-explain, but I still 306 00:16:04,180 --> 00:16:09,440 often prefer Lukas Fittl's, already mentioned him, his second 307 00:16:09,440 --> 00:16:17,420 time, his great library libpgquery and Qt fingerprints. 308 00:16:17,840 --> 00:16:24,020 I know it works purely based on syntax, no OIDs from pg_class 309 00:16:24,120 --> 00:16:27,740 or something, so I know how it will aggregate. 310 00:16:28,185 --> 00:16:33,480 And it's mostly often enough. 311 00:16:36,540 --> 00:16:38,220 Michael: Do you know if it can handle that? 312 00:16:38,760 --> 00:16:41,020 The different schemas, same object name? 313 00:16:41,740 --> 00:16:44,440 Nikolay: Well no, I think it distinguishes them. 314 00:16:44,440 --> 00:16:48,840 If query has fully qualified names involving schema name, I think 315 00:16:48,840 --> 00:16:51,100 it will produce different fingerprints. 316 00:16:51,820 --> 00:16:55,580 But I guess it's easier to tweak it than to try to change. 317 00:16:56,120 --> 00:16:56,620 Michael: Maybe. 318 00:16:56,980 --> 00:16:59,600 Nikolay: pg_stat_statements approach and so on. 319 00:16:59,760 --> 00:17:00,520 I don't know. 320 00:17:01,020 --> 00:17:04,080 I just feel, yeah, it would be great to have some more flexible, 321 00:17:04,740 --> 00:17:09,560 some default path, but some exotic paths to tune it, how we aggregate. 322 00:17:10,400 --> 00:17:14,940 Because otherwise you end up with 5000 slots in pg_stat_statements 323 00:17:14,940 --> 00:17:21,060 by default, And then maybe 5 different queries, each 1000 times, 324 00:17:21,060 --> 00:17:21,560 right? 325 00:17:21,740 --> 00:17:23,980 But just with some differences. 326 00:17:24,020 --> 00:17:27,440 You know, the in problem, I don't know, was it fixed already? 327 00:17:27,440 --> 00:17:31,780 I know, for example, the lipoge query, regardless of the members 328 00:17:31,780 --> 00:17:35,140 of in list, it will produce the same fingerprint. 329 00:17:35,840 --> 00:17:36,340 Michael: Nice. 330 00:17:36,420 --> 00:17:36,920 Okay. 331 00:17:37,420 --> 00:17:40,180 I don't know if the feature set statements got fixed, but yeah, 332 00:17:40,180 --> 00:17:40,840 you're right. 333 00:17:40,840 --> 00:17:45,300 If there's like the difference in having 1 in list versus 2 versus 334 00:17:45,300 --> 00:17:47,340 3, they were all considered unique. 335 00:17:49,400 --> 00:17:54,480 Nikolay: Some people implemented some regexps to convert it before 336 00:17:54,520 --> 00:17:55,740 calculating something. 337 00:17:56,600 --> 00:18:00,460 Usually you want, if you have in-listed, regardless of number 338 00:18:00,460 --> 00:18:04,960 of members in the list, it should be considered the same. 339 00:18:06,200 --> 00:18:07,660 Semantically it's the same. 340 00:18:07,660 --> 00:18:10,280 2 members, 3 members, it doesn't matter. 341 00:18:10,280 --> 00:18:14,740 But pg_stat_statements, that was until, like, I thought I saw some discussion. 342 00:18:15,060 --> 00:18:17,420 I like my memory tricks me sometimes. 343 00:18:17,860 --> 00:18:18,120 Maybe it 344 00:18:18,120 --> 00:18:19,500 Michael: was definitely been discussions. 345 00:18:19,660 --> 00:18:21,600 I just don't know if there's any commit. 346 00:18:21,600 --> 00:18:25,140 I think gut feeling is it might be in a very recent version, 347 00:18:25,320 --> 00:18:29,540 but I'm not I'm not 100% sure without checking the but the like, 348 00:18:29,540 --> 00:18:33,380 for example, any if you if you if you transform it into a query 349 00:18:33,380 --> 00:18:36,540 with any, you're going to get the same entry in pg_stat_statements 350 00:18:36,540 --> 00:18:37,300 every time. 351 00:18:37,300 --> 00:18:39,880 So why should it be different with in lists? 352 00:18:39,880 --> 00:18:41,540 Like it's, it doesn't make sense. 353 00:18:41,640 --> 00:18:42,140 Yeah. 354 00:18:42,280 --> 00:18:45,060 Although they could have different plans, like at a certain point 355 00:18:45,060 --> 00:18:47,800 it could flip the plan, but that's true of any query. 356 00:18:47,800 --> 00:18:49,100 That's true of any querying. 357 00:18:49,840 --> 00:18:53,680 Nikolay: How work, how they work with not in and nulls, right. 358 00:18:53,680 --> 00:18:55,140 Remember this danger, right. 359 00:18:56,760 --> 00:18:58,480 So yeah, let's discuss. 360 00:18:58,660 --> 00:19:02,560 So yeah, open source, Apache 2.0, open source components, PG 361 00:19:02,560 --> 00:19:05,940 version 3 as like engine for this. 362 00:19:06,100 --> 00:19:07,000 So we just, 363 00:19:07,200 --> 00:19:08,320 Michael: pgwatch 364 00:19:08,320 --> 00:19:12,780 Nikolay: pgwatch version 3, which was written by Pavlo 365 00:19:12,780 --> 00:19:17,080 Golub, CYBERTEC, great work, a lot of optimizations, a lot of 366 00:19:17,080 --> 00:19:21,040 things like how we control, they call it sinks, right? 367 00:19:21,040 --> 00:19:24,260 Like how data streams are controlled and so on. 368 00:19:25,020 --> 00:19:27,000 And what else? 369 00:19:28,260 --> 00:19:31,020 Very fresh Grafana, obviously, right? 370 00:19:31,500 --> 00:19:33,980 And there we have several interesting tricks there. 371 00:19:34,280 --> 00:19:38,700 So where does this dashboards shine? 372 00:19:38,860 --> 00:19:41,680 If we talk about only dashboards, as I said, it's a part of bigger 373 00:19:41,680 --> 00:19:45,720 vision, but just talking about dashboards and manual use of them. 374 00:19:46,060 --> 00:19:49,140 First dashboard is the troubleshooting dashboard. 375 00:19:49,140 --> 00:19:52,080 You have just 1 minute and you need to understand where to dig 376 00:19:52,080 --> 00:19:53,040 further, right? 377 00:19:53,360 --> 00:19:56,180 We use 4 golden signals there. 378 00:19:56,780 --> 00:20:02,180 So we have throughput, latencies, errors, what else? 379 00:20:03,400 --> 00:20:04,680 Saturation, right? 380 00:20:04,940 --> 00:20:06,340 Michael: I didn't see that 1. 381 00:20:06,340 --> 00:20:07,580 Dashboard, okay, dashboards. 382 00:20:08,680 --> 00:20:09,840 Nikolay: Dashboard number 1. 383 00:20:10,380 --> 00:20:11,600 Michael: Active session history? 384 00:20:12,660 --> 00:20:13,160 Nikolay: Yeah. 385 00:20:13,860 --> 00:20:16,520 No, active session history, This is where it starts. 386 00:20:16,780 --> 00:20:20,820 But it's a single node performance overview, number 1. 387 00:20:21,280 --> 00:20:24,640 Active session history, in our opinion, we're convinced after 388 00:20:24,640 --> 00:20:29,480 many years observing how RDS does it, others are also convinced. 389 00:20:29,480 --> 00:20:32,860 Wait event analysis is the king in monitoring. 390 00:20:33,280 --> 00:20:39,180 If you want 1 single graph or how to say chart, this is to understand 391 00:20:39,220 --> 00:20:41,240 like quickly performance and what's wrong. 392 00:20:41,380 --> 00:20:43,260 This is what you need to look at. 393 00:20:43,260 --> 00:20:46,000 That's why we put it on the very first place on the very first 394 00:20:46,000 --> 00:20:46,500 dashboard. 395 00:20:46,880 --> 00:20:50,640 But it's only very high level, like 30,000 feet view. 396 00:20:51,100 --> 00:20:54,340 So only wait event types, not wait events. 397 00:20:54,620 --> 00:20:58,080 No query IDs, not particular wait events, only wait event types. 398 00:20:58,080 --> 00:21:02,560 So it's very, like, is it like CPU or I or something? 399 00:21:02,560 --> 00:21:06,900 And CPU, we mark with asterisk because it's not only CPU we know 400 00:21:06,900 --> 00:21:10,440 sometimes some parts of the code still are not covered by by 401 00:21:10,440 --> 00:21:11,180 wait events. 402 00:21:11,200 --> 00:21:14,700 So technically, it can be CPU or unknown wait event. 403 00:21:14,860 --> 00:21:15,360 Right? 404 00:21:15,400 --> 00:21:15,900 Yeah. 405 00:21:17,860 --> 00:21:21,760 But and CPU is green is as everywhere is in performance insights, 406 00:21:21,780 --> 00:21:23,040 PASH Viewer, anywhere. 407 00:21:23,960 --> 00:21:29,520 So then we have very high level pieces to understand workload 408 00:21:30,180 --> 00:21:31,660 and various Postgres components. 409 00:21:32,020 --> 00:21:34,460 And we follow 4 golden signals there. 410 00:21:34,460 --> 00:21:38,400 So latency, traffic, situation, traffic throughput, right? 411 00:21:38,940 --> 00:21:39,640 Situation errors. 412 00:21:40,840 --> 00:21:43,760 Latencies, we extract from pg_stat_statements. 413 00:21:43,780 --> 00:21:46,740 I think it's a great idea actually to have it in the 414 00:21:46,740 --> 00:21:47,240 pg_stat_database. 415 00:21:47,660 --> 00:21:50,040 Probably there are reasons why it's still not there. 416 00:21:50,460 --> 00:21:54,440 You can extract it if you have PgBouncer, sometimes I just check 417 00:21:54,440 --> 00:21:55,820 PgBouncer logs manually. 418 00:21:56,320 --> 00:21:58,640 To double check that I understand latency properly. 419 00:22:01,020 --> 00:22:02,660 But from pg_stat_statements, good enough. 420 00:22:02,660 --> 00:22:06,920 Of course, again, if you have a situation like we just discussed, 421 00:22:07,740 --> 00:22:11,340 and you see only the tip of the iceberg in those pg_stat_statements.max 422 00:22:12,700 --> 00:22:16,640 entries of pg_stat_statements, 5000 by default, then it will 423 00:22:16,640 --> 00:22:18,160 be a wrong value. 424 00:22:18,160 --> 00:22:22,100 So I think, yeah, we, we, we, we understand this, right? 425 00:22:22,100 --> 00:22:23,500 So it's like best efforts. 426 00:22:23,500 --> 00:22:23,860 It's kind 427 00:22:23,860 --> 00:22:25,520 Michael: of an edge case as well, right? 428 00:22:25,520 --> 00:22:25,840 Yeah. 429 00:22:25,840 --> 00:22:28,680 It's not many people that have hundreds of thousands of objects. 430 00:22:29,280 --> 00:22:29,780 Nikolay: Yeah. 431 00:22:30,040 --> 00:22:30,300 Yeah. 432 00:22:30,300 --> 00:22:35,520 And throughput, we like in various ways, like calls per second, 433 00:22:35,860 --> 00:22:37,320 transactions per second. 434 00:22:37,740 --> 00:22:39,260 So these are key ones. 435 00:22:39,260 --> 00:22:42,360 And we didn't put tuples per second, as you can find in Datadog 436 00:22:42,360 --> 00:22:43,540 and some other places. 437 00:22:43,820 --> 00:22:46,920 Maybe we should, but I'm not convinced this is super low level 438 00:22:46,920 --> 00:22:49,500 throughput metric should be on the very front. 439 00:22:49,740 --> 00:22:52,860 Maybe actually, because I'm contradicting with myself. 440 00:22:53,240 --> 00:22:55,900 I'm always discussing logical replication. 441 00:22:56,040 --> 00:23:02,040 I say on modern Intel and AMD processors, single slot, Single 442 00:23:02,040 --> 00:23:09,020 publication subscription, it can saturate subscriber side single 443 00:23:09,320 --> 00:23:14,800 threaded process at levels like 1000, 2000, 3000 tuples 444 00:23:14,800 --> 00:23:17,180 per second, depending on various factors. 445 00:23:17,420 --> 00:23:20,600 So maybe tuples per second for writes, maybe we should put it, 446 00:23:20,600 --> 00:23:21,100 actually. 447 00:23:21,100 --> 00:23:22,240 Good idea, maybe. 448 00:23:22,840 --> 00:23:24,820 But this is very low level throughput. 449 00:23:24,840 --> 00:23:28,480 So in database you can send throughput in various levels. 450 00:23:29,060 --> 00:23:33,460 Transactions, queries, well, tuples next. 451 00:23:33,460 --> 00:23:35,120 Is tuples next? 452 00:23:35,460 --> 00:23:36,160 Or maybe rows? 453 00:23:37,920 --> 00:23:39,900 Rows, tuples are different, right? 454 00:23:39,960 --> 00:23:41,680 Well, buffers, yeah, yeah. 455 00:23:42,040 --> 00:23:43,920 Actually, this is what we do have. 456 00:23:44,440 --> 00:23:48,040 We have hits and reads and writes, dirtied buffers. 457 00:23:48,040 --> 00:23:52,640 All those buffers we put it right on the front page, but only 458 00:23:52,640 --> 00:23:55,520 high level, we don't distinguish by query ID, this is the job 459 00:23:55,520 --> 00:23:57,100 for different dashboard. 460 00:23:57,660 --> 00:24:00,780 So you quickly can understand what's happening here. 461 00:24:01,020 --> 00:24:03,520 And of course, we translate it to bytes, right? 462 00:24:04,540 --> 00:24:08,680 So how many bytes per second of buffer reads your buffer pool 463 00:24:08,680 --> 00:24:09,860 is having right now? 464 00:24:09,960 --> 00:24:11,700 And did we have spikes, right? 465 00:24:12,380 --> 00:24:13,120 So yeah. 466 00:24:13,500 --> 00:24:15,480 And actually, yeah, and errors. 467 00:24:15,480 --> 00:24:15,780 Yeah. 468 00:24:15,780 --> 00:24:19,120 So if we are errors, we will see it in transaction analysis, 469 00:24:19,120 --> 00:24:20,100 number of rollbacks. 470 00:24:20,900 --> 00:24:24,320 We currently don't work with logs, but it's in the roadmap, of 471 00:24:24,320 --> 00:24:24,620 course. 472 00:24:24,620 --> 00:24:28,160 So we extract errors from logs, aggregate, and so on. 473 00:24:28,740 --> 00:24:33,960 I still have hope that a tiny but very useful extension called 474 00:24:33,960 --> 00:24:34,800 logerrors. 475 00:24:35,660 --> 00:24:40,020 1 day we'll make it into engine and we will have some pg_stat_errors 476 00:24:40,260 --> 00:24:41,260 or something. 477 00:24:44,340 --> 00:24:48,040 It would be great to have some error code and how many times 478 00:24:48,040 --> 00:24:49,300 it occurred, right? 479 00:24:49,530 --> 00:24:55,160 Basic accumulative metric and we could start observing them in 480 00:24:55,160 --> 00:24:58,880 normal way, exporting and then alerting. 481 00:24:59,440 --> 00:25:02,000 Speaking of alerts, it's also in roadmap, definitely. 482 00:25:03,740 --> 00:25:07,060 We have very careful approach because talking to different customers, 483 00:25:07,060 --> 00:25:10,360 we see very different expectations, very like opposite sometimes. 484 00:25:11,480 --> 00:25:13,440 So yeah, I will discuss it. 485 00:25:13,440 --> 00:25:13,940 Yeah. 486 00:25:14,120 --> 00:25:14,900 In the past. 487 00:25:14,900 --> 00:25:16,220 Michael: Well, I think it's interesting. 488 00:25:16,820 --> 00:25:22,660 I think alerting is fascinating and like balancing risks of false 489 00:25:22,660 --> 00:25:24,940 positives versus risks of false negatives. 490 00:25:25,080 --> 00:25:27,180 Like I think it's a really hard problem. 491 00:25:27,440 --> 00:25:28,540 Nikolay: It's a hard problem. 492 00:25:29,280 --> 00:25:32,540 Michael: And configurationally, People always just end up making 493 00:25:32,540 --> 00:25:34,440 everything configurable and it's... 494 00:25:35,740 --> 00:25:38,080 And picking defaults is hard or like... 495 00:25:38,300 --> 00:25:42,400 Anyway, it's not envious of people that make monitoring tools. 496 00:25:42,400 --> 00:25:43,620 They quite like making 497 00:25:43,620 --> 00:25:44,280 Nikolay: it simple. 498 00:25:44,340 --> 00:25:49,520 Yeah, I think we will avoid a single default set, set of defaults, 499 00:25:49,760 --> 00:25:52,980 and we will just give some, basically, a questionnaire. 500 00:25:53,640 --> 00:25:55,800 What matters for you more? 501 00:25:55,800 --> 00:25:56,980 More like this or that? 502 00:25:56,980 --> 00:26:00,140 And based on that we adjust the setting right away. 503 00:26:01,560 --> 00:26:06,380 Michael: Like when people first install it, there's like the 504 00:26:06,380 --> 00:26:08,860 old school wizards that you go through and answer a bunch 505 00:26:08,860 --> 00:26:09,340 Nikolay: of questions 506 00:26:09,340 --> 00:26:10,020 Michael: and then... 507 00:26:11,400 --> 00:26:13,600 Nikolay: Old school Typeform, you know? 508 00:26:14,440 --> 00:26:15,420 I'm joking, I'm joking. 509 00:26:15,420 --> 00:26:16,980 Typeform is already old school. 510 00:26:18,680 --> 00:26:22,800 Because I see dental clinics use Typeform already. 511 00:26:24,520 --> 00:26:27,480 It's not cool already, it's old, ancient. 512 00:26:28,520 --> 00:26:30,460 I like actually, I love Typeform. 513 00:26:30,920 --> 00:26:34,540 I love the fact that you can use only keyboard, no mouse involved, 514 00:26:35,020 --> 00:26:37,400 navigate all the questions, answer them. 515 00:26:37,540 --> 00:26:40,700 So anyway, yeah, alerts is a separate topic, it's in the roadmap, 516 00:26:40,920 --> 00:26:41,780 not right now. 517 00:26:42,560 --> 00:26:46,400 We also paid very careful attention to metrics we display. 518 00:26:47,900 --> 00:26:50,320 We had many debates about this, really. 519 00:26:51,100 --> 00:26:56,160 For example, we just talked about, for example, buffer reads 520 00:26:56,160 --> 00:26:56,880 per second. 521 00:26:57,040 --> 00:27:01,960 We translate them to bytes, so megabytes, mebibytes, gibibytes. 522 00:27:02,040 --> 00:27:06,920 We use lowercase I in the middle to emphasize it's gibibytes, 523 00:27:07,040 --> 00:27:07,540 mebibytes. 524 00:27:07,960 --> 00:27:15,600 So if we display it on X's Y, is it megabytes or mebibytes 525 00:27:15,700 --> 00:27:16,420 per second? 526 00:27:17,220 --> 00:27:20,140 And I don't see consistency in various monitoring systems. 527 00:27:20,380 --> 00:27:23,940 So I managed to convince we should display maybe bytes per second. 528 00:27:24,780 --> 00:27:29,020 And to convince, I just said, okay, actually, you know what? 529 00:27:29,020 --> 00:27:32,060 We have all these metrics, it's differentiated by time. 530 00:27:32,080 --> 00:27:36,600 So if you display a raw metric, it will be constantly growing. 531 00:27:36,600 --> 00:27:39,560 It's a cumulative metric, it's just a number which always grows 532 00:27:39,840 --> 00:27:41,260 until you reset it. 533 00:27:41,680 --> 00:27:45,800 So since you differentiate it by time, it becomes already spiky 534 00:27:45,940 --> 00:27:48,960 or some kind of normal behavior. 535 00:27:49,020 --> 00:27:51,320 So it should be maybe by second. 536 00:27:51,620 --> 00:27:56,780 But also, I said, notice we develop additional differentiating 537 00:27:57,980 --> 00:28:03,480 on the right side, almost always you can find a chart which is 538 00:28:03,640 --> 00:28:06,600 displaying the same metric per call, average per call. 539 00:28:06,600 --> 00:28:10,620 Michael: So flipping instead of per second, per call, per query. 540 00:28:11,740 --> 00:28:12,120 Nikolay: Exactly. 541 00:28:12,120 --> 00:28:18,080 So if it's, for example, execution time per call, it's average 542 00:28:18,080 --> 00:28:19,700 execution part of the latency. 543 00:28:28,940 --> 00:28:30,400 Michael: That 1 you can't do. 544 00:28:30,800 --> 00:28:32,900 Nikolay: Total exact time per second you can do. 545 00:28:32,900 --> 00:28:37,400 This is the key metric, this is the central metric in our approach. 546 00:28:38,960 --> 00:28:40,820 It's measured in seconds per second. 547 00:28:42,500 --> 00:28:44,280 We discussed this, yes. 548 00:28:45,060 --> 00:28:47,320 I'm glad we're repeating this topic. 549 00:28:47,420 --> 00:28:48,160 This is key metric. 550 00:28:48,160 --> 00:28:48,830 Michael: Yeah, me too. 551 00:28:48,830 --> 00:28:49,960 I've clearly forgotten it. 552 00:28:49,960 --> 00:28:53,380 Nikolay: Seconds per second is a key metric. 553 00:28:53,860 --> 00:28:55,160 My favorite metric. 554 00:28:55,320 --> 00:28:58,860 It shows you how many seconds database spends to execute your 555 00:28:58,860 --> 00:29:00,420 queries per each second. 556 00:29:01,120 --> 00:29:04,160 Michael: And then do you have to divide it by cause to like understand 557 00:29:04,160 --> 00:29:05,280 you to like say shut up? 558 00:29:05,280 --> 00:29:05,540 No, no, no, 559 00:29:05,540 --> 00:29:05,913 Nikolay: no, no, no, no, no. 560 00:29:05,913 --> 00:29:06,607 Wait, wait, wait, wait. 561 00:29:06,607 --> 00:29:07,840 It's 2 different things. 562 00:29:08,180 --> 00:29:12,440 We have some metric, for example, number of buffers, which is 563 00:29:12,440 --> 00:29:13,600 also always growing. 564 00:29:14,180 --> 00:29:16,220 Across all query ideas, here it's top level. 565 00:29:16,220 --> 00:29:20,020 We don't, we just summarize all of them, all 5000, right? 566 00:29:20,020 --> 00:29:22,940 Or we have total exact time, right? 567 00:29:24,140 --> 00:29:25,620 These are 2 raw metrics. 568 00:29:25,840 --> 00:29:27,740 They are always growing, growing, growing. 569 00:29:27,880 --> 00:29:33,820 You can let Grafana, basically PromQL using irate, You can let 570 00:29:33,820 --> 00:29:35,220 it differentiate by time. 571 00:29:36,420 --> 00:29:41,980 We don't use mean exact time because we can have it from different 572 00:29:41,980 --> 00:29:42,480 place. 573 00:29:42,700 --> 00:29:44,600 Or you can divide it by calls. 574 00:29:44,600 --> 00:29:47,200 If you divide by calls, you have basically the same value as 575 00:29:47,200 --> 00:29:50,500 the mean exact time, but not Postgres calculated it, but PromQL 576 00:29:50,600 --> 00:29:51,600 calculated it. 577 00:29:52,740 --> 00:29:55,900 But if you divide it by time, it's differentiating by time. 578 00:29:55,900 --> 00:29:58,160 You will see how it changes over time. 579 00:29:58,260 --> 00:30:03,840 Every second we spend, for example, 10 seconds, give us at least 580 00:30:03,840 --> 00:30:05,460 10 cores for this workload. 581 00:30:05,460 --> 00:30:09,780 It's very roughly, it doesn't fare because this time can include 582 00:30:10,300 --> 00:30:13,880 waiting on lock acquisition, or I.O. 583 00:30:14,720 --> 00:30:18,280 It's not true CPU, user or system time. 584 00:30:19,000 --> 00:30:23,060 But roughly, it gives you a good understanding, what kind of, 585 00:30:23,060 --> 00:30:24,720 like the scale of your workload. 586 00:30:25,080 --> 00:30:28,580 If it's 10, don't run it on 2 core machines. 587 00:30:29,540 --> 00:30:30,040 Michael: Yeah. 588 00:30:30,060 --> 00:30:34,300 Well, more to the point, is it growing over time or are we reaching 589 00:30:34,300 --> 00:30:38,140 some, you know, when are the spikes, that kind of thing. 590 00:30:38,740 --> 00:30:38,820 Yeah. 591 00:30:38,820 --> 00:30:39,320 Interesting. 592 00:30:39,780 --> 00:30:42,320 Nikolay: And if you divide it by cost, you have average. 593 00:30:44,340 --> 00:30:48,500 So that's why we say metric slash S per second. 594 00:30:49,540 --> 00:30:55,440 And if it's seconds per second, we transparently say S slash 595 00:30:55,440 --> 00:30:55,920 S. 596 00:30:55,920 --> 00:30:57,140 Seconds per second. 597 00:30:58,840 --> 00:30:59,580 Why not? 598 00:31:00,400 --> 00:31:04,120 Michael: Well, I just think it hurts my head even thinking about 599 00:31:04,120 --> 00:31:04,240 it. 600 00:31:04,240 --> 00:31:09,440 I think there's all sometimes a case in products for being accurate 601 00:31:09,600 --> 00:31:15,480 and sometimes the case of being simple or like easy to understand 602 00:31:15,480 --> 00:31:18,660 at first glance, and it depends who you're building for, right? 603 00:31:18,660 --> 00:31:23,340 Like accuracy is extremely helpful, especially for deep experts. 604 00:31:23,360 --> 00:31:26,940 They want to know precisely what happened all the time, but anything 605 00:31:26,940 --> 00:31:29,440 that could be distracting for folks who don't necessarily know 606 00:31:29,440 --> 00:31:30,260 what they're doing. 607 00:31:30,800 --> 00:31:32,020 Nikolay: That's a great point. 608 00:31:32,460 --> 00:31:32,780 Michael: Yeah. 609 00:31:32,780 --> 00:31:35,860 So, or it was the pre you've answered pretty much all the questions 610 00:31:35,860 --> 00:31:40,600 I had already, except for who is your, I know yourselves are 611 00:31:40,600 --> 00:31:43,600 your ideal user, but like, who is your ideal user for this 612 00:31:43,600 --> 00:31:44,100 Nikolay: product? 613 00:31:45,660 --> 00:31:47,340 99% ourselves. 614 00:31:47,700 --> 00:31:54,460 We do it for our own work, for consulting, but we see it beneficial 615 00:31:54,720 --> 00:31:58,060 to publish it as open source, so first of all, our clients could 616 00:31:58,060 --> 00:31:59,660 verify what we are collecting. 617 00:32:00,520 --> 00:32:03,980 Even Datadog publishes exporters, so it's fair. 618 00:32:04,080 --> 00:32:08,600 But for us, these dashboards, it's not final product. 619 00:32:10,300 --> 00:32:13,280 By the time we publish this release, it's already obvious. 620 00:32:13,280 --> 00:32:19,440 We already included this to workflow, where meta collection is 621 00:32:19,440 --> 00:32:21,680 step number 1 of big step number 1. 622 00:32:22,660 --> 00:32:24,780 It's just first half of the step number 1. 623 00:32:25,240 --> 00:32:26,980 Dashboards is step number 1. 624 00:32:27,520 --> 00:32:31,740 And then API is exporting dashboards in form of static tables. 625 00:32:31,740 --> 00:32:35,520 Actually we didn't get there, but our dashboard with query analysis 626 00:32:35,580 --> 00:32:39,640 includes not only graph view charts, but also table view like 627 00:32:39,640 --> 00:32:42,400 our postgres-checkup has had. 628 00:32:42,400 --> 00:32:45,220 And table view is super beneficial because you can compare for 629 00:32:45,220 --> 00:32:48,780 each query ID, you can see many different metrics in 1 row. 630 00:32:49,140 --> 00:32:53,300 You can understand much better looking at very compact form of 631 00:32:53,300 --> 00:32:53,800 representation. 632 00:32:54,320 --> 00:32:56,980 It's for experts, it's not for non-experts. 633 00:32:58,060 --> 00:33:01,280 Regular Backend engineer needs some time to understand how to 634 00:33:01,280 --> 00:33:02,180 work with this. 635 00:33:02,220 --> 00:33:02,920 It's possible. 636 00:33:03,280 --> 00:33:06,080 We did it, but it really needs some time. 637 00:33:06,140 --> 00:33:10,020 But since I said this is big step number 1, these dashboards 638 00:33:10,060 --> 00:33:14,240 and charts and tables and everything. 639 00:33:15,340 --> 00:33:19,900 Second step is analysis by experts, And experts can be humans 640 00:33:19,940 --> 00:33:20,820 or LLM. 641 00:33:21,580 --> 00:33:27,540 For efficient analysis, you need to transform raw observations, 642 00:33:27,880 --> 00:33:30,980 we call it, we transform observations to conclusions. 643 00:33:31,640 --> 00:33:36,240 You have some transformations where it's quite like we did with 644 00:33:36,380 --> 00:33:36,880 buffers. 645 00:33:37,940 --> 00:33:39,360 Okay, 1000 buffers. 646 00:33:40,160 --> 00:33:42,920 Even for experts, it takes some time to understand is it big 647 00:33:42,920 --> 00:33:43,620 or not. 648 00:33:43,620 --> 00:33:51,860 But then we multiply it by 8 KB, and we have 8000 kB, roughly 649 00:33:51,860 --> 00:33:52,860 8 MB. 650 00:33:53,040 --> 00:33:54,740 Not exactly, but roughly. 651 00:33:55,240 --> 00:33:57,500 We say, oh, okay, this I can feel already. 652 00:33:57,500 --> 00:34:01,460 I know how long does it take to get it from disk, how much of 653 00:34:01,460 --> 00:34:02,080 memory it takes. 654 00:34:02,080 --> 00:34:03,300 I already feel it. 655 00:34:04,840 --> 00:34:10,360 My point, my hypothesis in what we are doing is that both experts 656 00:34:10,560 --> 00:34:14,080 and LLMs work better with this transformed raw data. 657 00:34:14,540 --> 00:34:20,040 If you present it better, This is what we do on second stage. 658 00:34:20,080 --> 00:34:24,020 Second stage, we've transformed observations very raw to conclusions, 659 00:34:24,280 --> 00:34:26,400 but we don't add thinking there. 660 00:34:26,580 --> 00:34:28,760 Maybe a little bit, you know, a little bit. 661 00:34:29,700 --> 00:34:35,340 Not debatable type of thinking, quite like straightforward logic, 662 00:34:35,600 --> 00:34:36,140 you know. 663 00:34:36,140 --> 00:34:39,320 Like We can calculate percentage, for example. 664 00:34:40,280 --> 00:34:43,480 This query ID in buffer hits takes like 25%. 665 00:34:44,200 --> 00:34:45,180 It's very noticeable. 666 00:34:45,280 --> 00:34:46,767 1 fourth of whole hits traffic. 667 00:34:46,767 --> 00:34:48,600 1 fourth of the whole hits traffic. 668 00:34:49,940 --> 00:34:53,500 Michael: So, yeah, like, it sounds like the kind of arithmetic 669 00:34:53,520 --> 00:34:57,440 that's difficult to do on the fly accurately when you're in the 670 00:34:57,440 --> 00:34:59,540 middle of an outage or something. 671 00:35:00,020 --> 00:35:03,660 Nikolay: It includes everything, like, arithmetic, like formulas. 672 00:35:03,780 --> 00:35:08,600 It also can include some representation, transformations, like 673 00:35:09,400 --> 00:35:11,400 put these numbers closer to each other. 674 00:35:12,380 --> 00:35:16,420 Just by putting this, you highlight that they have some correlation 675 00:35:16,500 --> 00:35:17,580 or something, you know? 676 00:35:17,800 --> 00:35:18,300 Yeah. 677 00:35:18,840 --> 00:35:21,080 Also, take specific charts. 678 00:35:21,100 --> 00:35:24,720 This is already like methodology we develop over many years, 679 00:35:24,720 --> 00:35:27,240 how we approach analysis of various problems. 680 00:35:27,560 --> 00:35:32,160 But still, we don't put conclusions there because we just observe. 681 00:35:32,160 --> 00:35:36,400 We just slightly transform what we see to better form for consumption 682 00:35:37,360 --> 00:35:39,180 of bioexperts still and LLMs. 683 00:35:39,960 --> 00:35:44,080 And final thing is recommendations and understanding problems. 684 00:35:44,640 --> 00:35:47,200 Not only recommendations, first understanding problems. 685 00:35:47,200 --> 00:35:50,400 So We see problems and we already start thinking. 686 00:35:50,740 --> 00:35:54,440 This is already sometimes if you take 2 DBAs, they have 2 different 687 00:35:54,440 --> 00:35:54,940 opinions. 688 00:35:56,120 --> 00:35:57,000 What they see. 689 00:35:57,120 --> 00:35:58,940 Here is already a tricky part. 690 00:35:59,060 --> 00:36:03,480 But our idea is, okay, we have a brainstorm phase, a collection 691 00:36:03,900 --> 00:36:04,580 of hypotheses. 692 00:36:05,320 --> 00:36:09,560 And then, since we build very great lab environments, we have 693 00:36:10,120 --> 00:36:14,540 thin clones, we have thick clones, we can have different tests, 694 00:36:15,860 --> 00:36:19,500 and We can verify ideas on thin or thick clones. 695 00:36:20,220 --> 00:36:23,980 Or maybe in synthetic, sometimes it's just before you're sending 696 00:36:24,280 --> 00:36:28,520 bug report or raise some discussion on hackers, you need to have 697 00:36:28,520 --> 00:36:33,780 minimal viable representation of your problem in synthetic form, 698 00:36:33,780 --> 00:36:35,640 not to share personal data, right? 699 00:36:35,860 --> 00:36:36,560 From production. 700 00:36:38,260 --> 00:36:39,380 It should be minimal. 701 00:36:39,380 --> 00:36:45,660 It's also a special type of art in database operations to extract 702 00:36:45,660 --> 00:36:49,440 from production some minimal stuff and inject it. 703 00:36:49,640 --> 00:36:54,040 And here is where LLM can be very helpful, to iterate much faster. 704 00:36:55,580 --> 00:36:58,320 Michael: I find this difficult for blog posts as well, like coming 705 00:36:58,320 --> 00:37:00,900 up with an example that you can share. 706 00:37:01,380 --> 00:37:05,280 Anyway, it's an art and very impressive and takes way longer 707 00:37:05,280 --> 00:37:06,240 than it looks. 708 00:37:07,300 --> 00:37:08,560 Well, at least it does for me. 709 00:37:08,560 --> 00:37:09,660 Nikolay: It should take less. 710 00:37:09,720 --> 00:37:10,820 And I hope AI... 711 00:37:11,480 --> 00:37:12,660 I don't hope AI. 712 00:37:12,660 --> 00:37:17,360 AI is already improving this, but I think we are on the edge 713 00:37:17,360 --> 00:37:19,620 of massive improvements in this area. 714 00:37:20,280 --> 00:37:21,580 Michael: I really hope so. 715 00:37:21,580 --> 00:37:27,420 I've tried it for a few, I've tried using even Claude 4 with 716 00:37:27,720 --> 00:37:28,940 limited success. 717 00:37:29,440 --> 00:37:30,520 Nikolay: Yeah, I know it. 718 00:37:30,520 --> 00:37:30,780 Yeah. 719 00:37:30,780 --> 00:37:34,140 I know, I know some problems like you just give up. 720 00:37:35,920 --> 00:37:38,800 We spent whole last year experimenting so much. 721 00:37:40,520 --> 00:37:41,480 We still have it. 722 00:37:41,480 --> 00:37:46,600 We still have credits from Google Cloud, Gemini. 723 00:37:47,680 --> 00:37:53,320 Gemini excels in some parts of work, but not in all. 724 00:37:53,480 --> 00:37:57,940 And of course, Claude is not also not the final answer to everything. 725 00:37:58,180 --> 00:38:03,460 But I see many, like, There is a substantial improvement with 726 00:38:03,460 --> 00:38:04,280 Claude 4. 727 00:38:06,760 --> 00:38:13,260 It gives me a much more level of excitement of how we can move 728 00:38:13,260 --> 00:38:13,760 forward. 729 00:38:15,320 --> 00:38:16,760 This is our whole system. 730 00:38:16,960 --> 00:38:22,160 Right now, almost everywhere is human, because we don't trust. 731 00:38:22,660 --> 00:38:25,120 It will take a few years to start trusting more. 732 00:38:25,460 --> 00:38:26,820 Maybe LLMs should be... 733 00:38:26,820 --> 00:38:27,980 Well, they don't stop. 734 00:38:27,980 --> 00:38:28,820 They are improving. 735 00:38:28,860 --> 00:38:29,340 Right? 736 00:38:29,340 --> 00:38:31,100 And knowledge base is growing also. 737 00:38:31,800 --> 00:38:36,500 Our knowledge base is growing, which we use when we work with 738 00:38:36,500 --> 00:38:39,120 LLMs to improve quality of answers. 739 00:38:40,440 --> 00:38:44,720 This is how I see it as a component in the beginning. 740 00:38:45,040 --> 00:38:47,920 It can be used directly if you're an expert, or you can spend 741 00:38:47,920 --> 00:38:51,520 time understanding some documents like to dive deeper to details, 742 00:38:51,820 --> 00:38:56,600 and then not becoming an expert, but already to start understanding 743 00:38:56,640 --> 00:38:57,860 how things work. 744 00:38:59,060 --> 00:39:03,740 And then we transform it to form which is easier to move faster 745 00:39:03,740 --> 00:39:06,800 basically, not to jump between various pieces we already. 746 00:39:07,080 --> 00:39:11,880 For specific analysis we compile multiple reports to small report 747 00:39:12,100 --> 00:39:13,820 which is for this specific analysis. 748 00:39:13,820 --> 00:39:17,860 For example our checkup has a report called Memory Analysis. 749 00:39:18,340 --> 00:39:22,580 Memory Analysis, if you want to tune memory, not only you need 750 00:39:22,580 --> 00:39:27,900 to tune shared_buffers, maintenance work_mem, work_mem, max_connections, 751 00:39:28,380 --> 00:39:32,180 but also check what's happening with your number of autovacuum 752 00:39:32,320 --> 00:39:32,820 workers. 753 00:39:33,120 --> 00:39:38,960 And also remember that in Postgres 17, before Postgres 17, even 754 00:39:38,960 --> 00:39:42,340 if you raised maintenance work mem beyond 1 gigabyte and put it 755 00:39:42,340 --> 00:39:47,900 to like 8 gigabytes, thinking, okay, I'm going to create or recreate 756 00:39:47,900 --> 00:39:51,360 indexes only in 1 session. 757 00:39:51,660 --> 00:39:55,280 But autovacuum workers, you have them, you also raise number 758 00:39:55,280 --> 00:39:59,180 of autovacuum max workers, you raise it to 10, for example. 759 00:39:59,180 --> 00:40:04,260 And you think, okay, I know that, well, people don't know mostly, 760 00:40:04,260 --> 00:40:06,060 but I also didn't know until recently. 761 00:40:06,300 --> 00:40:12,980 But before 17, If autovacuum work mem is minus 1 It means that 762 00:40:12,980 --> 00:40:16,220 inheritance from maintenance work mem but maintenance work mem 763 00:40:16,220 --> 00:40:20,600 if you rest beyond 1 gigabyte It's still autovacuum cannot use 764 00:40:20,600 --> 00:40:21,660 more than 1 gigabyte. 765 00:40:22,060 --> 00:40:27,040 But this was changed in Postgres 17, it can use more than 1 766 00:40:27,040 --> 00:40:27,940 gigabyte right now. 767 00:40:27,940 --> 00:40:32,360 So if you tuned it before and you then upgrade, you can run out 768 00:40:32,360 --> 00:40:33,020 of memory. 769 00:40:33,400 --> 00:40:38,180 Suddenly, if you perform the vacuum tuning in more, like we always 770 00:40:38,180 --> 00:40:42,420 say, raise the number of workers, give more memory to workers, 771 00:40:42,620 --> 00:40:44,340 to backends which build indexes. 772 00:40:45,060 --> 00:40:48,360 Now you upgrade to Postgres 17, mathematics changes. 773 00:40:49,120 --> 00:40:52,900 So we have a report which collects all the pieces and involves 774 00:40:52,900 --> 00:40:55,320 the version and knowledge about these changes. 775 00:40:56,200 --> 00:40:57,940 It requires some expert work. 776 00:40:59,600 --> 00:41:03,900 And we should not live in a world where this work is done manually. 777 00:41:05,800 --> 00:41:10,920 So it should be done in an automated fashion, collecting pieces, 778 00:41:10,920 --> 00:41:15,600 analyzing things, not to miss some important parts of the knowledge. 779 00:41:16,800 --> 00:41:18,360 So LLMs there, definitely. 780 00:41:19,620 --> 00:41:20,560 Michael: Well not definitely. 781 00:41:21,540 --> 00:41:22,920 You can get versioned. 782 00:41:25,080 --> 00:41:28,920 I'm intrigued as to how much you'd end up using LLMs on this 783 00:41:28,920 --> 00:41:32,520 journey, because things like the version information, you can, 784 00:41:32,560 --> 00:41:34,740 like, you can get directly from Postgres, right? 785 00:41:34,740 --> 00:41:37,380 You can run a query and find out what version it is. 786 00:41:37,380 --> 00:41:41,740 You don't have to work that out or it's not something where like 787 00:41:41,740 --> 00:41:43,780 a language model is going to be beneficial. 788 00:41:45,060 --> 00:41:48,700 And there's some, like, I don't do monitoring tools, but doing 789 00:41:48,700 --> 00:41:52,700 performance advice, it's like something learn to like, try to 790 00:41:52,700 --> 00:41:53,600 get good at. 791 00:41:54,020 --> 00:41:59,180 And I thought we would end up wanting to write bespoke advice 792 00:41:59,180 --> 00:42:02,040 in lots of different cases, but it's amazing how you can word 793 00:42:02,040 --> 00:42:07,560 things so that they cover different cases or conditionally show 794 00:42:08,480 --> 00:42:11,960 different pieces of the advice in different scenarios and piece 795 00:42:11,960 --> 00:42:17,120 together the full response without risk of hallucinations. 796 00:42:18,380 --> 00:42:19,220 Nikolay: I agree with you. 797 00:42:19,220 --> 00:42:23,160 That's why on the first 2 steps we don't almost don't involve 798 00:42:23,160 --> 00:42:29,180 LLM because we don't want them to, to, to like, III cannot let 799 00:42:29,180 --> 00:42:31,520 LLM to summarize the numbers. 800 00:42:32,360 --> 00:42:34,940 I better use a regular calculator, right? 801 00:42:35,860 --> 00:42:36,360 Michael: Yes. 802 00:42:39,060 --> 00:42:43,020 There were some things previous generations of computing were 803 00:42:43,020 --> 00:42:44,360 fantastic at. 804 00:42:45,100 --> 00:42:48,360 Nikolay: That's why jumping from observations to conclusions, 805 00:42:48,620 --> 00:42:50,820 we basically don't involve LLM. 806 00:42:50,900 --> 00:42:55,760 We involve our methodologies we developed, how we analyze things, 807 00:42:55,760 --> 00:42:56,880 and we just transform. 808 00:42:57,040 --> 00:42:58,600 We don't need LLM there. 809 00:42:58,620 --> 00:43:03,340 But when we have problems, First of all, we need to understand 810 00:43:03,340 --> 00:43:05,460 which problems look most critical. 811 00:43:06,820 --> 00:43:10,060 This, I believe, can be done at least preliminarily. 812 00:43:10,520 --> 00:43:12,540 LLM can highlight what's the most critical. 813 00:43:12,720 --> 00:43:19,040 Then most important, start hypothesizing about trying to understand 814 00:43:19,040 --> 00:43:22,800 root cause, trying to prove it, build experiment to prove it, 815 00:43:22,800 --> 00:43:26,040 and then how to solve it, build experiment how to solve it, prove 816 00:43:26,040 --> 00:43:32,300 that it's like drive this very hard work of root cause analysis 817 00:43:32,500 --> 00:43:34,080 and finding mitigation. 818 00:43:35,380 --> 00:43:39,140 This can be done by LLMs because it can search similar results 819 00:43:39,140 --> 00:43:44,160 on our knowledge base and internet, find ideas, and using thin 820 00:43:44,160 --> 00:43:49,900 and thick clones and frameworks we build, it can start experimenting 821 00:43:50,080 --> 00:43:53,040 to check and to prove, oh, this is what's happening here. 822 00:43:53,740 --> 00:43:56,640 Of course, the problem we discussed last week with metronome 823 00:43:57,980 --> 00:44:03,620 guys, It will take time for us to reach a point where our system 824 00:44:03,620 --> 00:44:08,980 will perform full-fledged root cause analysis and experimentation 825 00:44:09,240 --> 00:44:09,720 part. 826 00:44:09,720 --> 00:44:12,260 But I would say it's like 1 or 2 years only. 827 00:44:12,280 --> 00:44:15,780 We already had a lot of pieces built on this road. 828 00:44:16,300 --> 00:44:16,720 Michael: IM. 829 00:44:16,720 --> 00:44:19,200 Well, I look forward to discussing it again then. 830 00:44:19,600 --> 00:44:20,100 Yeah. 831 00:44:20,440 --> 00:44:20,860 Yeah. 832 00:44:20,860 --> 00:44:21,340 Nice. 833 00:44:21,340 --> 00:44:22,280 Nice 1, Nikolai. 834 00:44:22,360 --> 00:44:23,240 Looking forward to it. 835 00:44:23,240 --> 00:44:26,260 Good luck with the rest of your launch week as well. 836 00:44:27,080 --> 00:44:27,720 Nikolay: Thank you. 837 00:44:27,720 --> 00:44:28,080 Thank you. 838 00:44:28,080 --> 00:44:28,840 Michael: In the past. 839 00:44:29,700 --> 00:44:30,520 Nikolay: In the past. 840 00:44:31,360 --> 00:44:32,420 Back to the future. 841 00:44:32,860 --> 00:44:33,980 Or how to say. 842 00:44:34,740 --> 00:44:35,240 Good. 843 00:44:35,280 --> 00:44:36,060 Thank you so much. 844 00:44:36,060 --> 00:44:37,320 Michael: Nice 1, take care.