Nikolay: Hello, hello, this is Postgres.FM.
I'm Nik, Postgres.AI, and as usual with me, Michael, pgMustard.
Hello, Michael.
Michael: Hello, Nik.
How's it going?
Nikolay: Everything is all right.
A lot of work.
There is a progress on many fronts.
So, yeah, excited to discuss a quite important topic.
And maybe let's see what we can learn from this discussion.
I don't know, like mutually learn or something.
Michael: Yeah, I'm glad you think it's interesting.
Nikolay: It's interesting because it's always with us.
When we deal with performance, we need to work with many numbers
and this topic pops up all the time.
Michael: Yeah.
So specifically, the topic is when we're looking at latencies
or monitoring or troubleshooting, looking at latencies or you
know monitoring or troubleshooting, maybe prioritization, we
often need to look at queries by some measure of their duration.
Like often, and I see this in blog posts, in tools, various places,
often ordered by the mean or the average of their latencies.
Very easy to get from pg_stat_statements for example, it has
a dedicated column, but in a lot of other places, a lot of other,
you know, different types of monitoring tools and I'd say more
on the application developer side.
I'm seeing increasingly people moving to more monitoring the
p95 p99 type latencies, so percentile based and kind of the tail
latency is not the average, so wondering about that.
And yeah, I saw recently another feature request to have the
ability to see these in pg_stat_statements and I've realized it's
something we can approximate but it's not something we can get
from that so I wrote a post about it recently and I'm glad you
find interesting too.
Nikolay: Yeah.
So that's important topic, I think, because we all work with
averages or mean values all the time.
And, it's not only about positives, right?
Mean value, meaning of average is often misunderstood and it's
often misleading.
I remember the idea that in any organization, if you check what
is the mean salary or average salary, most people might find
out it's like in some organizations, in some organizations, oh
wow, it's like this average salary is much above my own salary,
what's happening here?
Well, what's happening, a couple of dudes have huge salary, that's
why, right?
So it means that we like, when we back to Postgres, back to queries,
you see average or mean time is like 1 millisecond.
Oh, not bad, not bad, right?
But how many queries are much about that?
It might be a lot.
And I must notice that mean time, mean exact time, mean planned
time, these columns are redundant.
They could be avoided because we always can calculate them dividing
total by number of calls, right?
Or plans in case of planning time.
By the way, I read your article, I noticed you optimistically
involve planning metrics.
Yeah.
Unfortunately, 99% of cases, we don't see them because they are
off by default.
And I remember last year or previous year, it took some effort
for us to prove that basically you can turn them on, but unfortunately,
as we had episode about this, right, unfortunately, performance
cliff will be 2 times closer to you if you turn it on, right,
but again, like, default recommendation is to turn it on because
it's very valuable information.
So again, back to averages, we could always calculate them dynamically
or something, it's not a big deal.
But I guess it was just a matter of convenience to add them and
keep them already pre-calculated in pg_stat_statements and other
similar extensions and so on.
But would be really great to see percentiles, right?
Or better to see whole histogram analysis of what's happening.
But unfortunately, I guess it's a very complex topic.
I mean, I know pg_stat_monitor implements this, but we need to
check overhead.
Michael: That's the critical part.
And also, just it's more columns.
We've got already a lot of columns in pg_stat_statements.
So it has additional overhead both to tracking, but also in terms
of user experience.
And I mean, I think at this point we have so many columns that
why not have a few more?
But it is a problem.
Like, there are, I think, just in time compilation, we've got
a bunch of additional columns for every single part of that and
there's there has been Discussion about whether that should be
collapsed down because do we really need granular?
Reporting there But yeah, I I would be in favor of some histograms.
I also haven't yet seen any analysis of...
So pg_stat_monitor is an alternative to pg_stat_statements, relatively
new in the grand scheme of things, probably a few years old at
this point.
But I don't see it in many installations and I haven't yet seen
anybody publish benchmarks on its performance relative to, yeah.
Nikolay: It was created in Percona and I remember I discussed
it with Peter Zaitsev who was the founder of Percona when this
extension was created and they said instead of contributing to
pg_stat_statements, they wanted a separate, independent release
cycle.
And as we know, pg_stat_statements is a part of the set of so-called
contrib modules.
It means that they are, like, basically, this cycle, it's Postgres's
own release cycle, so once per year.
And if you want a few times per year to release something, it's
hard.
And then I know the original creator left Percona and I don't
know what's happening with development of this extension but
when it was created, our first look, we did, our team, I remember
we did some analysis and early versions looked promising but
challenging in terms of performance overhead.
And of course, if you talk about RDS and others, they don't have
it.
I saw it only maybe on 1 or 2 platforms.
So Yeah, and performance overhead is a big question.
That being said, I don't know the current performance overhead.
And it would be great to have histograms and so on, right?
So percentiles.
So yeah, and another thing is 2 aspects here additionally in
my approach.
First is that in many cases, I don't care about average at all,
because this is, as we also discussed several times, depending
on our goals, it might be relevant or not, like important or
not.
For example, if we target to optimize how much resource utilization
happens on this server, we care about total time and total values
because this is what exactly shows the picture because a query
might have very like tiny average, tiny standard deviation so
they all like similar all query executions are similar and planning
occurrences are similar but so many of them like 10,000 QPS,
queries per second.
This is going to load our server a lot and we need to optimize
this query in spite of good values in terms of average.
Total will be high.
So averages, as I feel it, it's mostly, you mostly need it when
you target to deliver good experience to your users.
Michael: So I think so.
Yeah, that you're completely right.
Obviously, if we're trying to reduce utilization, there's no
point looking at things by how slow they are on average, or even
at the extremes, at the p99 level, because if they don't run
that often, they're not things taking the most time.
So total, ordering by totals, whether that's time, I still like
to do time, or some combination of buffers, if for example your
resource that you're constrained by is I O, which could be the
case, or like if you're paying by I O, like if you're on Aurora
or something, ordering by something else could make sense.
But yeah, totals always make sense for those cases.
But yeah, this is for those other cases where you care about
user experience, perhaps, like how long a dashboard's taken to
load for a large customer or the boss has complained or that
kind of thing.
Or we've talked a few times about the issues with long running
queries.
So you might be investigating what queries do I even have that
run long sometimes?
So I think that's the other use case I thought of as like, why
might you want to order by some version of either average or
percentile?
Nikolay: Yeah, let me argue with myself.
My favorite thing.
So yeah, when we optimize for resource consumption, lower resource
consumption, we look at totals, but at the second step, we should
look at average, because we need to decide Average and frequency,
which multiplied bring us to total, right?
So we need to go down and see is it about too frequent query
or is it about not very frequent but very slow query and decide
which optimization methods to apply, right?
And in this case, I'm wondering, does it really matter to understand
that, like, to look at standard deviation, basically, right?
To think about percentiles?
Michael: I think you're conflating 2 things here, looking at
something versus ordering by it.
And yes, when you're looking at resource consumption, you want
to look at average time sometimes, but you don't order by it.
Crucially, you need to order by total time.
And the same is true for standard deviation, I think.
I'm talking about adding maybe 1, maybe 2, depending on exactly
what percentile you want, adding a couple of standard deviations
to the mean to order by it.
Not necessarily to look at it, but just to get the initial prioritization
order, which things should I start looking at, what are my top
10, because the order will be different, assuming you have some
variants and you know, distribution of your queries and some
of them have a longer tail than others, the order will be different
if you order by mean plus a couple of standard deviations than
it would be if you just order by mean.
So it's about the prioritization order.
And then you care about looking at a lot of the other columns,
but you don't order by them.
So I think that's a subtle difference.
Nikolay: Yeah, yeah, yeah, I agree.
Order by standard deviation, it's interesting.
Michael: I'm joking.
I'm joking.
Yeah.
Nikolay: Yeah.
I understand you are thinking about ordering, for example, average
plus some factor times standard deviation.
This is a smart idea, I think.
And before we move to that idea, which is explained in your blog
post.
Michael: Yeah.
Nikolay: So, so again, like, okay, we order by total time, and
then we, we look to decide which approach to use for optimization.
And still maybe there it also makes some sense to look, to have
percentiles for example, because we would understand, oh, this
query has this average, which is not that bad, but for example,
we don't have percentiles in pg_stat_statements, no histogram.
So, but we see, Oh, standard deviation is so huge, it means that
some occurrences are okay, some are not at all.
Right?
And this brings us to the idea, oh, it means that we have instability
here, right?
Depending on maybe data volumes, or maybe we were blocked by
someone.
By the way, I would rather prefer to have this information for,
you know what, different metrics and time, right?
Yeah.
But for hits and reads, this is where I would really love to
see that piece, standard deviation or even better, percentiles.
Michael: Oh my goodness.
I'm just imagining the number of columns at that point.
Nikolay: Yeah, because it always, in pg_stat_statements approach, it
always concerns me how much of those slowness was associated
with query being blocked by another query, just wait time.
We don't know and it's hidden here.
Michael: Yeah, I mean, it's a good point that these aggregates
are always going to hide information.
You know, it's the nature of aggregations is we don't get all
the information.
It's the trade-off, right?
We get some useful information on aggregate, but we don't get
all of the information.
I quite often see people include min, mean, and max in their
queries, their querying pg_stat_statements form.
So you get kind of the full distribution.
I could imagine adding a couple, you know, like either side of
the mean to say what's the mean minus the standard deviation,
what's the mean plus the standard deviation.
Nikolay: Yeah, without standard deviation, mean and max, they
can be some, like you have million calls, but just 1 of them
was so far and like, okay, it broke your whole picture, affected
your whole picture, while standard deviation brings some good
sense, right?
Michael: Yeah, I mean, I guess min is possibly more interesting
than max, but yeah of course sometimes there'll be 1 parameter
that causes the query to run very differently.
Nikolay: 0 rows returned, we have min close to 0, it doesn't
make any sense, right?
Michael: Good point, yeah.
But by including more you get a better picture of the distribution
you get more points on the on the graph to get an idea of quite
how is this distributed is that max and outlier or are there
quite a few queries running nearly that long, you know, so without
some measure close to p95, p99 that kind of thing, I think it's
really hard to know that and I think possibly what's happening
at the moment is quite a few of these dashboards, monitoring
tools, diagnosis tools, I'll give you an order when you look
at it by mean that's fine, it's useful, but could be more useful
and you could maybe start at the more problematic queries if
you ordered them by a higher percentile and then started at the
top from there.
Nikolay: Yeah.
Because it's not it's like we say, oh, on average, we deliver
everything like 10 milliseconds, everything is good.
Or 1 millisecond again, like for a queries, we want to be below
10 milliseconds ideally, definitely not 100 milliseconds, it's
too much.
So we say it's good, but what if almost 50% of that was 1 millisecond,
and almost 50% of that was how many, like, not 1 millisecond.
Michael: What did you say the average was?
Nikolay: My math is going to be really bad.
I was trying to say you can, you might have 2 segments or clusters
of cases.
1 is very close to 0 and other is very bad.
And
Michael: they call it like a bimodal bimodal distribution.
Yeah.
Because perhaps because of a plan like different plan for different
amounts of data, maybe like
Nikolay: parameters, maybe, or maybe this locking issues.
And then you observe something in the middle.
Well, maybe, okay, maybe 50-50 is a bad example, because we are
going to mean the middle.
What if, what if like 70%, very close to 0, 30% is very far from
0, and they really struggle with performance, those users, right?
And we see average is not bad it's going to be shifted towards
to 0 because it's average not and oh no it's not going to look
okay depends right So but but average will tell us it's like,
it's not that bad.
We can leave it.
Michael: It's very, yeah, it's very easy to imagine distributions
that on average are less than some threshold that we might not
care about, like 50 milliseconds or something.
Maybe we set our threshold there.
On average, they're 40, 45.
But for a non-zero, you know, like a decent number, maybe if
it's p95, p99, and we're talking about either 5% or 1% of times,
it's running for hundreds of milliseconds.
And we've talked in the
Nikolay: past about how...
Michael: Or even seconds, yeah, exactly.
So those are noticeable to human durations, even though on average
it's running in 45 milliseconds.
So it's very easy, I think, to hide things that on average look
like they maybe aren't perceptible to humans but if the tail
is long enough then for quite a lot of people especially you
know let's say it's 5% of people that's 1 in 20 times somebody's
loading that dashboard or using that feature.
1 in 20 sounds quite often.
Nikolay: Yeah, yeah.
Yeah, if it's 1 in a thousand, well, things
happen, right?
Yeah, we can say, okay, we have a very large user base,
some of them, like, we need to take care of everyone, but we
cannot, like, it happens, right?
But this approach works only if your p99 is good.
Michael: Yes.
Nikolay: Okay, 1% is not that good, okay, but if it happens with
20% of your users, they suffer, and you missed this fact just
looking at the average, it's not good, it's not good.
Michael: Yeah, exactly.
Well, imagine, go back to our example.
If you've got 2 queries, most, they look the same in terms of
average, let's say one's 45 milliseconds on average; the other's
50 milliseconds on average, but the one that's 45 has a p99 that's
10 times that 450 milliseconds, and the one that's 50 milliseconds
has a p99 of only 60 milliseconds, so only a little bit higher.
I would much, much, much rather start, have it ranked higher,
the one that had the higher p99 in that case, even though it has
a lower average duration.
So that's kind of the whole point.
Nikolay: Yeah, let's explain the idea you had.
The idea is simple.
We don't have buckets, we don't have percentiles, we don't have
histogram, we cannot draw it, but we have standard deviation.
And the idea is how I learn, how I hear it or read it.
In all analysis we have over the last years with pg_stat_statements
and other things, pg_stat_kcache, pg_wait_sampling maybe and so
on, we always look at throughput and average, throughput and
average latency, that's it.
Yeah, we know that from, you mentioned, the article mentioned
that from application side, some monitoring systems, they bring
you p99, p99.5, it's great, or 2 values even better than just
1 here.
Yeah.
And sometimes colorful, good looking graphs.
But in Postgres we don't do it somehow.
But we have standard deviation and in which Postgres monitoring
standard deviation is really appreciated?
I don't know.
Michael: I don't know any, yeah.
Nikolay: We don't have p99 or p95, p90, nothing like that.
But what if we assume we like distribution, well, your assumption,
it's normal, it's not normal, like half normal.
But idea is let's take average and standard deviation and have
some multipliers, so we have formulas, average plus some multiplier
times standard deviation, and we have a kind of p something,
p 90 something, kind of, right?
Michael: Yeah, I called it approximate, just only because it
really does assume a normal distribution, like standard deviation
only makes sense in a, like, not that it only makes sense, you
can only approximate if you assume a normal distribution and
I made the case that I think it would be quite rare for you to
have queries, or at least problematic queries that are normally
distributed.
I suspect a lot of the problematic ones are going to have long
tails but it definitely isn't a safe assumption that they're
all going to be normally distributed, but once you factor that
in, I think this is still useful.
I think queries that have a low average and a high standard deviation,
where this ordering difference, where this change makes a difference,
are probably going to have, well, are definitely going to have
high p99s as well.
It's still directionally correct, I think I used that phrase,
so it still points you probably in the right direction.
This order is probably not going to be that different to the
order by true p99.
So it's a step in the right direction, it's not necessarily accurate,
but it's directionally correct.
Nikolay: S. Yeah, I agree.
And since I suck at statistics, as every listener already should
understand by this point.
I talked to my daughter who is learning data science at UCSD
right now.
And she told me, oh, this is quite known, like p95 or something
for normal distribution we just standard deviation times 2 to
both sides this is how you can get p90 I should say or p95 I
forgot like yeah so this is reasonable she said but then like
of course it's not normal distribution, and it's a big assumption.
And what I thought, idea number 1 I had, and maybe I will encourage
some, I don't know, some students or some people who are willing
to do something here.
What if we had some raw data from somewhere?
I don't know who can afford logging all queries with duration.
Since we talked last week, we talked how dangerous it can be
to log all queries.
But imagine we collected some raw data and we see what's present
in persistent statements.
And what I'm trying to say, we could find, we could understand
distribution nature, maybe observing many, many cases from OLTP,
like web apps, mobile apps.
And then we could probably find some heuristic based multiplier,
right?
Which would serve like closer to reality, I don't know, like
to avoid this assumption that it's normal, but it's a big work.
I know like it's, you need to collect a lot of data process it
and so on.
What do you think?
Michael: Well, and it would be different for different queries
like unless we find out that it's just I think it's a tricky
problem unless you actually approximate it in while you're monitoring.
Nikolay: Look, I understand what you said.
So if we take assumption, we like, it's kind of normal.
It sounds for me like fragile assumption.
If we learn how some OLTP system, social media or something,
with all these queries, like select 2 users, select 2 something,
join some tables, and so on, how these queries behave, at least,
I don't know, a few dozens of them, how they behave.
This like practice learned multiplier, I'm pretty sure it will
be in another system it will be very close, you know?
Michael: Yeah, actually it's a good point.
You could get more accurate with the number, like the exact multiple
to get a more likely to be p99 measure.
It might turn out that the number I've suggested based on normal
distribution is actually more likely to be a p96 than a p99.
Or p97 instead of p99.
Does it make that much difference?
No, no.
Nikolay: If it's just order by, no.
I agree with you.
And what I'm proposing breaks Pareto principle, right?
Michael: Yeah, It's a lot of work for arguably not that
much additional, like, I think if someone's going to put a lot
of effort into this, it would be much better to actually track
this and put it into P statements.
Nikolay: This will be, again, overhead question.
It would be great to have, but again, I understand.
What I'm proposing is going to be outside of p80 of the effort
we're going to have.
Or p20.
I like it.
p20, p20.
I don't know.
p80 of the value, p20 of...
Okay, so another thing, another thought I have.
I noticed over time that when I run some benchmarks, very often
with pgbench, I'm absolutely unsatisfied with...
So I use, we have it in automation as well, like all my team
members use it.
So I have lowercase r to report results in the end for each statement
used in transactions, right?
Sometimes we use multiple files with different transactions and
like with some weights assigned to them.
It can be achieved with option F and add sign.
It's great feature, by the way, underappreciated.
But anyway, I see these reports and we discuss them and like,
Oh, this looks great.
Oh, it looks not that great, but I realized it's not enough.
I want histograms.
Right.
Michael: Yeah.
Nikolay: And to mitigate it, at least somehow, we started to
use capital P option.
For example, P30, it means like every 30 seconds, it will be
reported. Oh,
Michael: interesting.
Different P notation.
Yeah.
Nikolay: Yeah, yeah.
So it will be reported as progress.
So it reports latencies every 30 seconds, for
Michael: example.
Nikolay: Nice. Or every 10 seconds if benchmark is very fast, or every minute
if it's longer, depends.
And from there, we already understand, oh, distribution is like
this, right?
So we can feel some distribution.
It reports by the way a standard deviation as well, but it's
still not enough.
There is another aspect here, not only about distribution of
np90, np95, It's also about the distribution in time, over time,
because there might be a spike of latency, right?
And then it's better.
True.
So, yeah, yeah.
But still, I think what I was trying to say, actually, there's
an idea to implement histograms or percentiles.
Maybe it's better to start in pgbench.
Yeah, not in pg_stat_statements first.
Michael: Yeah, interesting idea.
I hadn't thought, I get where you're coming from.
I think it'd be easier, right?
You don't worry so much about overhead.
Nikolay: Well, you worry about overhead, but this is a tool which
used to make decisions and so on.
Well, pgbench also is used.
But anyway, maybe it's easier because it's less risky.
The idea is it's not production.
So...
Michael: Are you thinking it's more like, are you thinking educationally,
you could see the idea that this is a useful thing and once people
start seeing it, like, oh, this is really helpful, Why can't
I have it elsewhere?
So it kind of builds.
Nikolay: I think most of people understand that this is helpful,
but it's just risky because pg_stat_statements have overhead.
We know it.
And it's probably increasing because more and more things are
being added.
And just again, pg_stat_statements is not about production, usually, depends
of course.
And I think-
Michael: pgbench is not about-
Nikolay: It's not used in production, it's a learning tool for
experiments.
Yeah, pg_stat_statements is for production, so it's harder to bring something
heavy there.
pgbench, again, like optionally, and see how it works, like
it's easier in my opinion.
And it's also, like, first of all, it's needed there.
It's needed.
It's very useful to have it there, it would be very useful.
Do you know if Sysbench is bringing histograms?
Michael: No, I don't know.
Nikolay: I don't remember.
Yeah, it brings percentiles.
Michael: Yeah.
How many buckets or which?
I don't know.
Just report a couple.
Nikolay: It brings p95.
Yeah.
And I don't know, I don't see any more.
I don't know.
Anyway, it's already better than just standard deviation I guess,
right?
But I agree with your point if we order by just like maybe just
2 times standard deviation.
Michael: Okay, I
did, yeah, in the blog post I did consider rounding
just to 2 instead of using the 1 that's actually an approximation
Yeah exactly because at that point I'm saying look it's
this is approximate already why are you multiplying by such a
specific number but then what are we approximating it's like
a different p value and I just didn't really I thought it would
just be confusing as a kind of to people like people reading
the Results rather than people looking at the query.
I figured more people would probably end up If you've got if
you put these into dashboards and things, you're gonna have more
people reading the dashboards than looking at the queries behind
the dashboards.
So I figured I'd rather have something confusing in the query
than something confusing in the like column header.
Nikolay: Makes sense, makes sense, Yeah.
Michael: So yeah, quite strange to use such a precise figure
to calculate an approximation.
Nikolay: You know what, I will think about next time we will,
we will maybe do some reports or dashboards, maybe we will implement
this approach.
We'll call it p??
Michael: I suggested calling it approx_p99, just so that
it's really clearly, Like, you're leading with the word approximate.
Nikolay: So it's Mysterious percentile.
Yes.
Michael: And even if you don't include, like, it's probably confusing
to look at a dashboard that doesn't include it in the results,
but still include all the other columns you're going to include,
just ordering by that is then, I think, a useful
Nikolay: ordering means in monitoring, it means another, chart
you, you like exactly top end, top end by what, and this is ordering
and, instead of top end by average or in addition to top end
by average?
This would be helpful to bring sense of how users feel about
your service, right?
Michael: And I really respect monitoring tools and reports that
try and limit the number of pages they or dashboards they show
but most I come across don't don't seem to try and limit it too
much so I think another what's another chart but if you are worried
about quite how many charts you have, I might argue that I'd
rather have, well, my argument is I'd rather have this than the
1 ordered by me, which all of, pretty much all of them include.
So if you include that 1, I would rather have this 1.
Nikolay: Yeah.
And Do you think it's worth keeping a chart for average?
For pure average?
Michael: Again, it depends on the constraints, right?
If you've already got a hundred charts and you're showing it's
99 versus 100, why not include the average still?
But if you're talking about only having 2, then 2 versus 3 is
quite a big difference.
So I think it depends on the content.
Nikolay: And this chart is going to be regular, like not non-stacked,
because you cannot summarize it, like it's not cumulative thing,
averages, right?
Yeah.
Yeah, And probably it should include only like 10 or something.
Michael: Well, it's not really a chart, it's just like a top
end.
Nikolay: Ah, table.
Michael: Yeah, like a table.
Yeah, I think so.
Nikolay: Chart also matters, like again, table is good, but it
loses historical information.
Michael: Yeah, okay, so maybe your chart is, yeah, your most
important queries looking at their p99 over time, but then you
can include the mean on the same chart, can't you?
I've seen those in monitoring tools where you're looking at the
same...
Nikolay: Interesting thing, for the chart we can get average
for specific period just dealing with total and calls.
We have delta for total, delta for calls, we can have pure average
for specific period of time.
But what about standard deviation?
We don't have it.
Michael: You can calculate it using deltas, but well, the same
way pg_stat_statements recalculates it every time it has a new
query come in.
Nikolay: Oh, yeah.
Michael: So, Indeed.
Yeah, I was looking at the source code, I was like, oh, that's
clever.
Nikolay: Yeah, yeah, yeah, yeah, yeah, exactly.
Otherwise, yeah, exactly.
Michael: But you also have the option of snapshotting pg_stat_statements.
Some people do that, right?
Reset snapshot, like some monitoring tools or some providers
reset it regularly.
Nikolay: It's not a good idea.
Because to do it often you're going to have troubles under load
because it doesn't come for free to add new entries.
Michael: But then we have deltas.
Deltas is a good idea.
Nikolay: Yeah, yeah, yeah.
Well, and standardization can be also understood for a specific
period of time.
And in this case, you can have this metric with like magic, mysterious
percentile.
And you can have a chart of, for example, 10, the top 10 queries.
Yeah, and maybe also average of everything.
Michael: Well, it's a good point of like there are a couple of
I don't even know if I mentioned this in the blog post, but there's
at least 1 major caveat here is a lot of people have a timeout,
often like 30 second, like statement_timeout.
It won't be registered.
So it does quite seriously affect those metrics like the max,
the standard deviation and
Nikolay: Affects in which way?
It's not registered, so you miss it there.
Michael: You miss it.
You miss an outlier.
So it's bringing the average down.
It's bringing the p19
Nikolay: looks more positive than it is.
Yes.
But you know, like if a query was cancelled, job is not done,
we don't count it.
As I remember, this is another point, pg_stat_monitor aims to solve.
Yes.
Like, it's a common problem with pg_stat_statements and all its
friends, like pg_stat_kcache, that canceled queries are not counted.
And I think also a big missing piece here, honestly, is that
in pg_stat_database we have TPS, 2 metrics committed rolled back,
right?
But we don't have QPS, we don't have latencies, standard deviation,
it should be there, actually.
This is another idea to register, like, these metrics, calls,
mean, max, mean, max, and min, average, and standard deviation
at the database level for everything, regardless of the statements.
And maybe involving error doubt queries, Maybe.
This is I'm not sure because this is like, if we involve them,
the values observed by producer statements and at this high level,
they won't match at all.
Michael: Yeah.
I was unsure what ideal looks like here.
I did wonder about, in an ideal world, would you add the timeout?
Like, would you add a query to 30 seconds?
Let's say 1 got canceled at 30 seconds.
Would you add an instance of a query that ran 30 seconds and
add that to the average, add that to the standard deviation?
Would you want that or would you not want that?
Nikolay: It's easy.
Let's just have another pg_stat_statements.
We'll call it pg_stat_error_statements and have a whole set of
metrics.
I'm joking, of course.
Michael: Well, it's not a bad idea, though, right?
Like how many of each error type do we have?
Like it's
Nikolay: kind of cool.
And could it be canceled before statement_timeout?
It can be canceled due to deadlock_timeout, for example, or just
something else happens, I don't know.
Kill.
Term, a pg_cancel_backend, for example, came, canceled us.
So yeah, and it would be great to count all those things like
buffer hits, reads,
Michael: and so on.
So yeah, that's the long and short of it.
Was there anything else
Nikolay: you wanted to talk about?
I feel we went to some very dark corner of Postgres observability,
you know?
Michael: Yeah, yeah, yeah.
It definitely seems less explored than I was expecting.
As your daughter said, this is not a novel, like this is not
an out there revelation.
I was just surprised When I saw the feature request for it, and
I've heard it a few times, I was thinking, why don't we add,
like, why don't more people have that in their queries?
Why don't I see this in blog posts or in monitoring tools?
Nikolay: And also for buffer operations.
And WAL operations.
Michael: Well, I know why we don't have that.
It's because the standard deviation is not even available there.
Nikolay: Well, it should be available.
Let's add it.
Michael: So, yeah, my summary is I'd love to see these in pg_stat_statements.
But in the meantime, like you mentioned, if you're doing a consulting
report or if you've got a dashboard internally consider adding
this like or replacing the ordering by mean.
Nikolay: Just don't call it fake p95 call it mysterious pn
Michael: fake news p99 yeah I liked your mysterious what was
it MMP
Nikolay: magic P
Michael: yeah
Nikolay: okay
Michael: yep absolutely
Nikolay: good