Monitoring from Postgres AI

And we actually, we are releasing more, we, I hope we released by the time people are listening, I hope we released more than 5 things. So we have also concept of extra bonus releases. And we started on Monday, we started with DBLab version 4, which actually took 2 years to develop.

Every day, for example, in my team, it's prohibited to do anything without AI-first. It's prohibited to write code. It's already so. Well, you can write code, but only if your AI tools failed. And first people were resistant to this. Some people said, like, what the heck, I don't want it. It's bad, it's poor quality. But in my opinion, the Anthropic release of Cloud 4 a couple of months ago, it was a game changer. Quality already very acceptable in many cases.

Not always, sometimes, especially if you are expert in some narrow topic, you understand problems which were never discussed publicly. In this case, of course, you will see AI will be wrong 100%. We have these cases. For example, a perfect example is our guests last week. They experienced some problem which was not covered by public discussions. Obviously, in such cases, if you just apply AI in a straightforward way, it will hallucinate some bullshit.

But if you apply AI to brainstorm some ideas and ground it, pre-ground, post-ground with materials, reliable materials like source code, documentation, information from reliable sources and experiments, post-grounding, in this case it's different. You can explore, behave, reproduce it, and this is the path we chose. Not just this problem, take AI and create, I don't know, like change requests somewhere. This won't work. Ungrounded approach won't work. So this is 1 point.

Another point is that 6, 7 years I keep talking, I avoid building monitoring system. For example, when I met with Lukas Fittl in San Francisco 1 day a few years ago, I told him this, like, you're building great monitoring system, pganalyze. We build some checkup because we need for our customers to find problems and then to start exploring them, experimenting. Our key strength of Postgres AI team is experiments.

That's why we built DBLab to simplify specific type of experiments and benchmarks and so on. And I mentioned checkups and somehow pganalyze has checkup section since then, but it's a different story. And I said I'm not going to build monitoring system, but it was inevitably like I was pushed somehow to this point where it's inevitable that we need to do it because no other system, and not only open source system, not other system is providing abilities we need.

We need several specific abilities with our clients. We always show it. And some of them is for troubleshooting. So like high, very high level analysis of Postgres components. And then you can understand where you can dive deeper. And actually, I remember great talks from Percona, from Zaitsev, and so on. And I saw some bits of good stuff here and there, like Percona Monitoring and Management, or how is it called?

A few years ago, we started maintaining so-called pgwatch2 Postgres AI edition, which was a fork of pgwatch2 with our metrics and dashboards. But then Pavlo Golub decided to rewrite pgwatch. So we started lagging in terms of this vehicle we used as pgwatch itself. So time has come and we realized, okay, and we experimented a lot. We actually started with InfluxDB. It was insane. We eliminated it. We used TimescaleDB. And then we had a big thinking process.

Should we stay with PureSQL and TimescaleDB, or we should use PromQL? We decided to use PromQL, but with some nuances. We still have Postgres inside. So we have both Prometheus and Postgres. So the idea was we need to build a new monitoring system, which will be part of our process. And in consulting, we had maybe 30 clients over the last 1 year and a half. We had a few dozens of clients, right?

And to all of them, we installed always our pgwatch2 Postgres AI edition, and also used postgres-checkup tool, old tool we always used, to generate reports in static form, like markdown or HTML PDF. So this time we said, okay, we finally do monitoring, but the main part of it will be open source. So monitoring itself is open source, but it's a part of bigger, bigger picture where we perform health checks and help clients proactively using AI, of course.

1 of the key reasons is that we had already 2, actually 3 companies where we, in consulting mode, we contributed a lot to implementing our approach to their own monitoring system. So this is why we have confidence this monitoring system is great. And usually, bigger companies, they have some stack. Some people use Datadog, but you know, if you have 100 terabytes of data, or say, 1000 clusters, like microservices, Datadog is super expensive. Some people still keep paying.

But it's not only expensive, it has blind spots in terms of Postgres monitoring. A lot of them, for example, pg_stat_statements, they have some not all metrics. And for essential metrics, they, for example, can give you per call, but not per second, or per second, but not per call. There is no completeness there. If you check carefully, so they differentiate in 1 dimension, but not another. For another metric, it's vice versa. So it lacks many things. And also how dashboards are organized.

I don't understand why people put Tuple stats on the front page. It's not the main metric to observe. Not the main metric. It's not the number 1 thing you want to see when you have an incident and need to troubleshoot. And people don't put queries per second because Postgres doesn't have queries per second. But we can extract it from previous assignments with some asterisk that it's approximate because some things can be lost.

Sometimes we see queries per second are lower than transactions per second, but it cannot be so. Right? Yeah. In fact, because we don't see full picture, but in most cases it's vice versa. So we see vice versa. So this monitoring is open source fully, and it can be used in pieces if your organization already has Prometheus-based something. But lacks a systematic approach for dashboards. This is where we can, I mean, you can take this and improve, take pieces, maybe contribute back?

Also good idea to contribute back always. And I still think, although I personally invested like more than 5 years already to this. First few years I wanted to say, no, no, I'm not going to do it. But then I was forced. Well, well, life sometimes force you to do something right. And now this is my like, I resurrected to this idea, like, okay, I'm doing this.

And yeah, and not only I, we have really super experienced members in our team, who also spent a lot of time thinking and contributing to this. It's not only my own effort. So we have several very good team members who contributed a lot. So we decided to use PromQL also because of this, because it's more compatible with enterprise systems.

We also thought about Victoria Metrics. So it's still... We will switch in the next few releases to something else. And for query texts, it's Postgres, but it's a small 1. Because you know, by default, pg_stat_statements for each node, 5000 statements. And do we track all of them? I need to check. Maybe no. So 1 of the things we noticed, we had cases with our previous monitoring, that sometimes people have millions of objects in the database. And this is really requires optimization.

And we made this optimization for really large cases. I remember our checkup actually also had cases with 100,000 indexes. In case of monitoring pgwatch2, it was previous version, basically, right? Our Postgres AI edition, we had cases with more than 1 million indexes and hundreds of thousands of tables. Bloat analysis was not working. But like, it's and this is interesting and a separate question, how to perform index maintenance in such a situation, for example. But

I know, for example, the lipoge query, regardless of the members of in list, it will produce the same fingerprint.

Yeah. Although they could have different plans, like at a certain point it could flip the plan, but that's true of any query. That's true of any querying.

First dashboard is the troubleshooting dashboard. You have just 1 minute and you need to understand where to dig further, right? We use 4 golden signals there. So we have throughput, latencies, errors, what else? Saturation, right?

That's why we put it on the very first place on the very first dashboard. But it's only very high level, like 30,000 feet view. So only wait event types, not wait events. No query IDs, not particular wait events, only wait event types. So it's very, like, is it like CPU or I or something? And CPU, we mark with asterisk because it's not only CPU we know sometimes some parts of the code still are not covered by by wait events. So technically, it can be CPU or unknown wait event. Right?

Yeah. But and CPU is green is as everywhere is in performance insights, PASH Viewer, anywhere. So then we have very high level pieces to understand workload and various Postgres components. And we follow 4 golden signals there. So latency, traffic, situation, traffic throughput, right? Situation errors. Latencies, we extract from pg_stat_statements. I think it's a great idea actually to have it in the pg_stat_database. Probably there are reasons why it's still not there.

You can extract it if you have PgBouncer, sometimes I just check PgBouncer logs manually. To double check that I understand latency properly. But from pg_stat_statements, good enough. Of course, again, if you have a situation like we just discussed, and you see only the tip of the iceberg in those pg_stat_statements.max entries of pg_stat_statements, 5000 by default, then it will be a wrong value. So I think, yeah, we, we, we, we understand this, right? So it's like best efforts. It's kind

I say on modern Intel and AMD processors, single slot, Single publication subscription, it can saturate subscriber side single threaded process at levels like 1000, 2000, 3000 tuples per second, depending on various factors. So maybe tuples per second for writes, maybe we should put it, actually. Good idea, maybe. But this is very low level throughput. So in database you can send throughput in various levels. Transactions, queries, well, tuples next. Is tuples next? Or maybe rows?

Rows, tuples are different, right? Well, buffers, yeah, yeah. Actually, this is what we do have. We have hits and reads and writes, dirtied buffers. All those buffers we put it right on the front page, but only high level, we don't distinguish by query ID, this is the job for different dashboard. So you quickly can understand what's happening here. And of course, we translate it to bytes, right? So how many bytes per second of buffer reads your buffer pool is having right now?

And did we have spikes, right? So yeah. And actually, yeah, and errors. Yeah. So if we are errors, we will see it in transaction analysis, number of rollbacks. We currently don't work with logs, but it's in the roadmap, of course. So we extract errors from logs, aggregate, and so on. I still have hope that a tiny but very useful extension called logerrors. 1 day we'll make it into engine and we will have some pg_stat_errors or something.

It would be great to have some error code and how many times it occurred, right? Basic accumulative metric and we could start observing them in normal way, exporting and then alerting. Speaking of alerts, it's also in roadmap, definitely. We have very careful approach because talking to different customers, we see very different expectations, very like opposite sometimes. So yeah, I will discuss it. Yeah. In the past.

We had many debates about this, really. For example, we just talked about, for example, buffer reads per second. We translate them to bytes, so megabytes, mebibytes, gibibytes. We use lowercase I in the middle to emphasize it's gibibytes, mebibytes. So if we display it on X's Y, is it megabytes or mebibytes per second? And I don't see consistency in various monitoring systems. So I managed to convince we should display maybe bytes per second.

And to convince, I just said, okay, actually, you know what? We have all these metrics, it's differentiated by time. So if you display a raw metric, it will be constantly growing. It's a cumulative metric, it's just a number which always grows until you reset it. So since you differentiate it by time, it becomes already spiky or some kind of normal behavior. So it should be maybe by second.

But also, I said, notice we develop additional differentiating on the right side, almost always you can find a chart which is displaying the same metric per call, average per call.

We don't use mean exact time because we can have it from different place. Or you can divide it by calls. If you divide by calls, you have basically the same value as the mean exact time, but not Postgres calculated it, but PromQL calculated it. But if you divide it by time, it's differentiating by time. You will see how it changes over time. Every second we spend, for example, 10 seconds, give us at least 10 cores for this workload.

It's very roughly, it doesn't fare because this time can include waiting on lock acquisition, or I.O. It's not true CPU, user or system time. But roughly, it gives you a good understanding, what kind of, like the scale of your workload. If it's 10, don't run it on 2 core machines.

Dashboards is step number 1. And then API is exporting dashboards in form of static tables. Actually we didn't get there, but our dashboard with query analysis includes not only graph view charts, but also table view like our postgres-checkup has had. And table view is super beneficial because you can compare for each query ID, you can see many different metrics in 1 row. You can understand much better looking at very compact form of representation. It's for experts, it's not for non-experts.

Regular Backend engineer needs some time to understand how to work with this. It's possible. We did it, but it really needs some time. But since I said this is big step number 1, these dashboards and charts and tables and everything. Second step is analysis by experts, And experts can be humans or LLM. For efficient analysis, you need to transform raw observations, we call it, we transform observations to conclusions. You have some transformations where it's quite like we did with buffers.

Okay, 1000 buffers. Even for experts, it takes some time to understand is it big or not. But then we multiply it by 8 KB, and we have 8000 kB, roughly 8 MB. Not exactly, but roughly. We say, oh, okay, this I can feel already. I know how long does it take to get it from disk, how much of memory it takes. I already feel it. My point, my hypothesis in what we are doing is that both experts and LLMs work better with this transformed raw data.

If you present it better, This is what we do on second stage. Second stage, we've transformed observations very raw to conclusions, but we don't add thinking there. Maybe a little bit, you know, a little bit. Not debatable type of thinking, quite like straightforward logic, you know. Like We can calculate percentage, for example. This query ID in buffer hits takes like 25%. It's very noticeable. 1 fourth of whole hits traffic. 1 fourth of the whole hits traffic.

We just slightly transform what we see to better form for consumption of bioexperts still and LLMs. And final thing is recommendations and understanding problems. Not only recommendations, first understanding problems. So We see problems and we already start thinking. This is already sometimes if you take 2 DBAs, they have 2 different opinions. What they see. Here is already a tricky part. But our idea is, okay, we have a brainstorm phase, a collection of hypotheses.

And then, since we build very great lab environments, we have thin clones, we have thick clones, we can have different tests, and We can verify ideas on thin or thick clones. Or maybe in synthetic, sometimes it's just before you're sending bug report or raise some discussion on hackers, you need to have minimal viable representation of your problem in synthetic form, not to share personal data, right? From production. It should be minimal.

It's also a special type of art in database operations to extract from production some minimal stuff and inject it. And here is where LLM can be very helpful, to iterate much faster.

Right now, almost everywhere is human, because we don't trust. It will take a few years to start trusting more. Maybe LLMs should be... Well, they don't stop. They are improving. Right? And knowledge base is growing also. Our knowledge base is growing, which we use when we work with LLMs to improve quality of answers. This is how I see it as a component in the beginning.

It can be used directly if you're an expert, or you can spend time understanding some documents like to dive deeper to details, and then not becoming an expert, but already to start understanding how things work. And then we transform it to form which is easier to move faster basically, not to jump between various pieces we already. For specific analysis we compile multiple reports to small report which is for this specific analysis. For example our checkup has a report called Memory Analysis.

Memory Analysis, if you want to tune memory, not only you need to tune shared_buffers, maintenance work_mem, work_mem, max_connections, but also check what's happening with your number of autovacuum workers. And also remember that in Postgres 17, before Postgres 17, even if you raised maintenance work mem beyond 1 gigabyte and put it to like 8 gigabytes, thinking, okay, I'm going to create or recreate indexes only in 1 session.

But autovacuum workers, you have them, you also raise number of autovacuum max workers, you raise it to 10, for example. And you think, okay, I know that, well, people don't know mostly, but I also didn't know until recently. But before 17, If autovacuum work mem is minus 1 It means that inheritance from maintenance work mem but maintenance work mem if you rest beyond 1 gigabyte It's still autovacuum cannot use more than 1 gigabyte.

But this was changed in Postgres 17, it can use more than 1 gigabyte right now. So if you tuned it before and you then upgrade, you can run out of memory. Suddenly, if you perform the vacuum tuning in more, like we always say, raise the number of workers, give more memory to workers, to backends which build indexes. Now you upgrade to Postgres 17, mathematics changes. So we have a report which collects all the pieces and involves the version and knowledge about these changes.

It requires some expert work. And we should not live in a world where this work is done manually. So it should be done in an automated fashion, collecting pieces, analyzing things, not to miss some important parts of the knowledge. So LLMs there, definitely.

And there's some, like, I don't do monitoring tools, but doing performance advice, it's like something learn to like, try to get good at. And I thought we would end up wanting to write bespoke advice in lots of different cases, but it's amazing how you can word things so that they cover different cases or conditionally show different pieces of the advice in different scenarios and piece together the full response without risk of hallucinations.

Then most important, start hypothesizing about trying to understand root cause, trying to prove it, build experiment to prove it, and then how to solve it, build experiment how to solve it, prove that it's like drive this very hard work of root cause analysis and finding mitigation.

This can be done by LLMs because it can search similar results on our knowledge base and internet, find ideas, and using thin and thick clones and frameworks we build, it can start experimenting to check and to prove, oh, this is what's happening here. Of course, the problem we discussed last week with metronome guys, It will take time for us to reach a point where our system will perform full-fledged root cause analysis and experimentation part. But I would say it's like 1 or 2 years only.

We already had a lot of pieces built on this road.