Nikolay: Hello, hello, this is
Postgres.FM.

My name is Nik, PostgresAI, and
as usual, my co-host is Michael

of pgMustard.

Hi, Michael.

Michael: Hi, Nik.

Nikolay: And we have a great guest
today, Dmitry, who is a very

experienced DBA, originally Oracle,
now Postgres, already many

years.

Hi, Dmitry.

Dmitry: Hi, thanks for inviting.

Nikolay: Thank you for coming.

So we have, I think, very, like
for me it was surprise when you

did it, when you built this tool,
and I didn't expect that it's

coming to Postgres ecosystem.

But obviously it's something new
and in the area which is in

my personal key interests lately.

So active session history analysis,
wait event analysis, but

you did it very differently compared
to what we usually did with

pg_wait_sampling or other tools,
performance insights in RDS.

So it's a great tool.

Where to start?

Dmitry: Yeah, so I think we can
start with the time when I first

started working with Postgres,
as I started switching from Oracle.

When Oracle, 1 of my favorite topics
always and tasks was performance

related investigations.

And when I switched to Postgres,
of course, I start looking for

similar tasks and understood that
the way of Postgres troubleshooting

is very different from how it's
done in Oracle.

And definitely the felt lack of
tooling, lack of a standard approach

and so on.

Okay.

So after some time, I realized
that there are some tools that

can bring similar kind of feeling,
let's say what I had in Oracle.

And I'm talking about the weight
event analysis because I know

in Oracle world, it's a given thing.

Yeah.

Yeah.

Nikolay: I'm going to interrupt.

So when did this switch happen
from Oracle to Postgres for you?

Dmitry: Recently I checked, it
happened 10 years ago.

Nikolay: 2016 roughly.

I remember very well a big stream
of, like, big wave of people

coming from Oracle in 2013-14 and
all of them were complaining

about lack of wait event analysis.

This is how 2 columns were added
to pg_stat_activity.

It was like maybe a couple of years
before basically your switch

and you also had this same feeling
like these tools are needed.

Dmitry: Yeah, Because when you
work with Oracle since, I don't

know, 2002 or something like that,
it's a given way of troubleshooting.

Any Oracle guideline, any book,
any training official, non-official

says that just open the active
session history.

And so it's a, it's a first, it's
a gate that you go through

and you start with.

And so I try to use the same approach
with Postgres because for

me it was so obvious why it's a
way to analyze the performance.

Because basically what's the wait
event says you say you so why

it was slow where the session spend
the time session or database

in general so you do not need to
guess was it's I or CPU basically

where it spent time.

So it just says very rarely.

So then why it happened that way.

So you need to investigate, but
at least you know where, because

the regular approach in progress
just can say that this query

was slow In average, or with some
hacking, you can get some precise

numbers of the execution, but it
does not give the idea why it

was slow.

Okay, yes, you can use the explain
analyze to see where in the

plan you spend the time, but again,
it does not explain why sometimes

this join takes I don't know 1
second or sometimes 10 seconds

from the plan perspective it looks
the same and the tooling

for wait event analysis evolved
year by year.

And so we have the pg_wait_sampling.

And since they introduced the new
event tracing as well, so it

became really a tool that gives
really good data, good source

for wait sampling analysis.

And I use it everywhere where I
manage Postgres.

And if it's self-hosted Postgres
for sure, I will, I will convince

the owner of this Postgres to install
pg_wait_sampling.

It's a default way of analyzing.

On all my dashboards, it's the
first panel you see when you open

any dashboard that I created.

So it's a wait event analysis.

I convinced all my colleagues to
use this approach.

You convinced me

Nikolay: To put this ASH graph
on the top of the first dashboard,

which I resisted initially because
the first dashboard for me

was seen as very shallow and very
wide, troubleshooting where

you identify areas, but you convinced
me to put ASH graph on

the very top.

And now I'm convinced.

Yeah, it's a great idea.

So it's very quickly helps you
understand like some pain points

for performance.

Dmitry: Yeah, I have a, I will
try to make it brief.

2 examples when it's the wait event
analysis shines.

So 1 was, there was a migration,
hardware migration from, from

let's call it this old hardware
to new hardware.

The data files stored in the storage
appliance.

So we just remounted.

So the data layout was the same.

Everything was the same in terms
of the data layer, storage layer,

but the hardware, the compute part
changed and changed to the

highest spec.

So the number of virtual cores
increased, the clock increased,

everything increased.

All tests were good.

When we start loading production,
so we see the really huge degradation

in performance and so what you
would think.

So the plan flip or something
like that, but you have the same,

absolutely everything is the same.

Just hardware is different.

So it basically cannot be planned,
because the parameters are

the same.

Nikolay: So your slide deck has
very interesting example, very

simple, so anyone can understand
about contractors doing some

work.

So in this very example, you right
now, describe right now, it's

like basically you hired a different
team and do, and say, do

the same work, but it does it very
differently.

Right.

Yeah.

So that example, I just to explain
what like those who haven't

seen.

So it's a simple, like there are
contractors, they say, we are

busy, we spend so much time, and
without wait event analysis,

you just know they are busy.

But what they exactly were busy
with, you don't know.

And in this case, okay, they were
busy with I/O or something

else, right?

Dmitry: And yeah, exactly.

Nikolay: Switching hardware, you
see different picture and so

what was in the end of that story?

Dmitry: So, the problem was LWLock
Manager, so you know, it's

1 of your favorite LWLocks.

Nikolay: A lot of dots are connected
here because we spoke about

so many problems which are explored
with wait event analysis

on this very podcast.

So, right Michael, you remember
lock manager?

Michael: Yeah, of course.

It feels like whenever there's
like a system-wide issue, it's

such a great starting point, right?

Dmitry: That's sorry, I...

That's the reason, next case, when
I would say that's on lower

level.

Nikolay: Let me first disagree
with Michael, because I agree

on 1 side, system-wide.

But on the other side, more and
more I think we just don't have

this tool yet, but it should exist.

On pgMustard we should have it.

We have plan analysis and for 1
call it would be great to see

wait event profile as well.

Because imagine we were holding,
like we were blocked by log

acquisition which were pending.

You will see a lot of time spent,
buffer numbers are low, and

that's it, you need to guess.

With wait event analysis for
a single query execution, you

will see, okay, this was lock wait
event type.

Michael: But wait, but I think
this is where we get into the

core of what Dmitry's done, which
is cool, which is moving from

sampling to tracing.

I think when it historically has
been reliant on...

I do think it's really important.

Maybe I'm wrong, but my understanding
is that sampling...

Nikolay: I also thought this, like
this is what expectation I

had as well.

It's about a single backend tracing.

But let's move on and hear what
Dmitry will say.

Exactly.

Dmitry: Yeah, just the next case
when the wait event really saved

a lot of time.

And when this single backend analysis,
I mean, it's single backend

analysis case.

It does not work only on the instance
level.

It's on the single backend that
also works.

I would have the case, I got the
complaint that sometimes query

takes from few milliseconds and
it's killed with 1 second timeout.

We have, I don't know, 100 calls,
10 of them failed with 1 second

timeout and the query is primary
key lookup.

So you cannot, they cannot mess
up with the plan for this case.

So it's just a B-tree primary key
lookup, very simple.

It's not join nothing, so just
very simple lookup.

Nikolay: And it's not lock manager
contention because exactly

lock manager contention, in many
cases, it's primary key lookup.

It was not that.

Dmitry: Yeah.

Okay.

So you open the dashboard so you
don't see any spikes of any

specific wait events.

So it's definitely very low, a
low level with some specific backend

problem.

So I also captured just in case
the explainer, the plan was the

same.

So everything was the same, But
sometimes it goes from a few

milliseconds to 1 second, and I
start sampling.

So I make some custom scripts that
will just catch the needed

backend and start sampling.

And I saw that when it's slow on
I/O, I mean, I saw the I/O,

I mean, look how it can be, just
a very simple read.

So it just reads very few blocks
from disk.

So next step, so I probably understood
with the wait event

analysis it's nothing else but
just slow I/O for some cases.

And with the eBPF and tracing these
backends, I understood that's

a problem with the long tail latency
on the storage appliance

that we use.

Sometimes it goes from 1 millisecond
to tens milliseconds.

And indeed if you're not lucky
enough and you need to read these

blocks from disk, not from memory,
not from OS cache, these even

few blocks become seconds.

And yeah, I think without wait
event analysis it will be just

guess game.

But this just in a couple of days
I found the problem.

Michael: A couple of quick questions.

1 is around, like you can get I/O
timings in explain analyze output.

Obviously it's a bit tricky if
they're always getting cancelled

I don't know how you got the explain
analyze for the queries

that were timing out actually how
did you do that

Dmitry: I just increased the time
out for some portion of them

to make this finish in a few seconds.

Nikolay: It's about 0, right?

Michael: Did you have I/O

Timings on the server?

Dmitry: Yes, I do.

But when you have 1k calls a minute
of this type and just very

few and it fails you cannot track
it it just so it's 1 of my

favorite topics that averages it's
every time so you have to

have to have P99 and better more
nines to see the actual picture

of the senior system?

Nikolay: Just to push back,

Michael: I think this is a really
interesting topic.

I think actually in this 1 specific
case auto-explain might have

been okay, like catching the...

That's where it can be really good
in terms of catching the outlier

queries that go beyond a set.

I think as long as you've got the
I/O timings on, in this 1 case,

we might have seen, oh, I/O timings
are the issue for these slow

queries.

But I'm sure there are other examples
where the wait events would

have shown something more interesting.

Nikolay: But wait event will analysis,
or not, AutoExplain will

show you the plan, will show you
timing if you enable it, will

show you buffer numbers if you
enable it.

Good.

But it won't show you WaitEventProfile.

Although now I think, why not?

It's a good idea, probably.

This is maybe, yeah, even maybe
better than for manual explain

to have it in auto explain.

Michael: I think you're right,
but I think, I was talking about

track I/O timing.

You know the parameters of track
I/O timing?

Nikolay: Yeah, I/O timing, great.

But I/O timing is working, first
of all, it's working at higher

level.

But what if it would be not I/O timing,
but IPC, or like with local

or log or something, like full
picture would be great to have,

right?

Also wait event, we talk like this
is a wait event type level,

we have how many like 7, 9 wait
event types, but how many wait

events?

Hundreds.

So it's very precise understanding
what the system was doing.

So I think, just imagine for auto-explain
to have a wait event

profile logged optionally.

I think it's a good idea for those
who want to hack Postgres

right now.

Michael: Yeah, me too.

And I think it probably transitions
onto like, why don't we have

this?

What's, what are the downsides?

What are the, what's the overhead?

What are the tricky parts of this?

Dmitry: And we will go there.

Yeah, because it's definitely a
thing that I cannot take out

of my mind for weeks already.

And so this last case with this
single backend problem, I reflected

this experience, I spent a couple
of days on it, and I thought,

so what tooling can help me with
it to next time to finish this

investigation in hours and better
probably even enable dev people

to do it instead of DBAs.

And the Oracle has a very simple
answer for this type of problems.

It's the 10046 event.

It's a tracing.

When you enable it on a certain
level, it writes everything in

the file.

Every single syscall, every single
tuple, I mean the raw joint,

everything I mean very detailed
like real tracing.

And I decided to create the pg_10046
extension that will do it in

Postgres.

And it was the prototype was really
good.

So I really proud of it.

But for wait events, Oracle also
traced the wait events.

And I did the same, but I had to
sample it because I didn't find

by that time any tracing approach
for wait events in Postgres.

I know when I started posting on
LinkedIn this, Jeremy Schneider

came and commented.

So yeah, while it's really nice,
you cannot call it 146 because

wait events are not tracing.

Very true, But there is no way
in Postgres to do it.

Nikolay: Yeah, yeah, 1 second,
1 second.

I just wanted to send a big hello
if Jeremy Schneider listens

to this.

And I hope 1 day we will have him
on this podcast.

And Jeremy Schneider is important
because he was making it popular,

blog posting about ASH a lot.

And he was in the RDS team when
they also started to do this

like performance insights and so
on.

So it's great that you connected
over LinkedIn with him.

Michael: I think was also involved
in the very good wait event

documentation that RDS and Aurora
have.

Nikolay: Oh yes, we discussed this
with Dmitry offline a lot.

Yeah.

Dmitry: Yes, I mean, it's really
a lot of kudos to them for this

work.

Okay.

And, and again, I was brainstorming
with all LLMs that I had

by that moment.

And I started complaining.

Sometimes I use, I talk to them
like to real people and I was

complaining.

So yeah, I had a really nice extension,
but lack of real tracing.

It's a big caveat.

And Claude said to me, yeah, but
you can trace.

And I said, no way.

How?

And he explained to me details,
how I can really trace the weight

events.

And for 2 days, I challenged this
idea a lot for reason.

It could not be me the first who
found this, not me, okay, the

Claude found, but I'm not the first
guy in the Postgres world

who used this.

Either this just does not work
or it was basically the only idea

so it just does not work because
otherwise someone should find

it before.

Anyway 2 days of testing this idea
showed me that it basically

works and I start prototyping the
tool.

It's still in a very immature state,
but it's already showing

nice pictures and already can help
to find some interesting,

how to say, correlations.

For example, I found that, you
know, that most of extensions

now still use extension wait event.

And when extension does something,
you really cannot distinguish

it from doing a real job or it's
just a wait loop.

When I use pg_wait_sampling, for
example, to cross-check the numbers

that I get from my tool and from
pg_wait_sampling, I understood

that the background of pg_wait_sampling,
just Even if it does

nothing, because it works in the
background and there is no job

for it, it's still in a waiting
state, in there.

And so, I think since PG17, you
can have custom wait events for

extension.

And I really hope if someone listens,
just please use the custom

wait events in your extensions,
that it will really help to understand

is your extension busy with doing
some valuable work or it's

just spinning loop.

Nikolay: Right.

This is why we, in my pg_ash also,
listening to Dmitry, I also

spent some time analyzing colors.

So green is what's usually called
CPU.

And this is why in pg_ash and in
our monitoring tool, CPU has

an asterisk.

So CPU star means that it's CPU
or maybe not.

And we also discussed it a few
times on our podcast.

And yeah, so this is exactly why.

So some extensions doing something.

Yeah.

Michael: I was just thinking whether
pg_ash would show up in pg_ash

with its own custom wait events.

But then I remembered it's not
really an extension.

So it can't.

Exactly.

Sorry.

Yeah.

Nikolay: True Postgres.

Dmitry: So that's basically the
story how this came to life.

So what are we to show it?

Nikolay: We cannot show it on podcast,
unfortunately, but just

to recap, like you complained to
LLM that something is impossible

And LLM said, let's do it.

Dmitry: Yeah.

And this is how it was.

Nikolay: This is crazy.

It's like, it's, it means that
it's good to sometimes complain.

It would be good to have, unfortunately
we cannot.

And then just, maybe you just don't
know how and it's possible.

It's great.

Dmitry: Yeah.

Brainstorm with the LLM, I think
it's 1 of the best features.

Yes.

Nikolay: Similarly, our monitoring.

I remember I was talking to you
and you said, oh, it's really

hard or maybe impossible to show
the beginning of query in query

analysis in Grafana.

And that's it.

Dmitry: We mean query, query text.

Nikolay: Yeah.

We, we, we see chart, we see query
IDs, but it would be good

to see query itself.

Is it select or something or in
source?

So I just went and did it with
AI And it's there right now.

Because we had limiting belief
like it's not possible.

It's interesting.

So yeah, AI helps here.

Sometimes it doesn't help, but
here definitely helps to explore

new options and yeah, create something.

Dmitry: Yeah.

The new idea of validation that
it's also 1 of fantastic features.

You can really fast, I mean, check
the idea if it basically working

or not.

Just of course, bring the mature
software will take time.

So, I mean, an LLM cannot do it
in 1 night, but prototyping the

idea, cross-check.

So it's, yes.

Nikolay: So this new tool is tracing.

So it's true, like it won't miss
any wait event or it still have

some sampling or because when you
showed me the pictures which

look amazing, I still think you
need to post those pictures to

add those pictures to read me because
this is like super impressive.

And it was microsecond precision.

I was like, my mind was blown.

So it's, it's really crazy.

But then we started talking and
Dmitry actually came to our hacking

sessions, which happened on Wednesdays
online on YouTube.

So a couple of times we sat and
thought about how to do something

and that something was about changing
Postgres, right?

So why is it needed to change Postgres
for this tool?

Dmitry: And here we, no, for this
tool, we do not need to change

Postgres.

But the thing is this tool has
overhead.

Nikolay: Observer effect, right?

Dmitry: Yes, in more or less, I
would say, if you can call it

regular load, not pathological,
let's call it unacceptable way,

level like 10% something plus minus.

So you pay overhead for every wait
event transition, so when

you get from 1 to another.

And if you get more transitions
per second, so you pay more overhead.

And The maximum that I got was
220K transitions per second.

And in this case, yeah, 220, 000
transitions.

Yeah.

And so in this case, my overhead
was basically 30% of the query

duration.

So.

Nikolay: Was it with very tiny
buffer pool, shared buffers, tiny

shared buffers?

Dmitry: Yes.

When it read, that's why it needs
to go to the OS to get the

block, but block is in OS cache.

So that's why it's really fast.

But you need to almost for every
block you need to go.

Yes.

And for even for evicting, so you
need to be pinning, unpinning,

so all this kind of stuff.

So it goes, I mean, in this case,
I was able to generate this

amount of transitions.

And while it's a kind of a, how
to say, not edge case, but kinder,

very synthetic.

But anyway, so it says that the
overhead, so I mean, you cannot

avoid it.

And when the Jeremy Schneider raised
this concern about it's

not tracing, He also said that
the patch for progress can be

very simple.

You just add 2 probes in this function
that's used for set and

unset wait events and basically
that's it.

Nikolay: And then you created this...

Dmitry: And then you can

Nikolay: use eBPF?

Yes.

Yeah, yeah.

Dmitry: And you created this patch
for...

Nikolay: I wanted to say that even
if, like you said, for regular

workload, it's not 30, like 10%
overhead observer effect, it

still sounds super useful to me
to use it in lab environment.

So if we reproduce some problem
and study it, not in production,

but on some clone or something,
right?

It's like I'm ready to pay 10%,
20%, maybe even 30% because it

gives me an exact understanding
of everything that happened,

right?

But of course it would be good
to have a very low observer effect

and have it in production right
away.

Dmitry: To be honest, I personally
would pay even 20% in production,

just will instrument me to find
the problem really fast.

Because once I spent, I don't know,
2 or 3 days finding where

the problem is because there was
some changing in the security

configuration of the server and
basically everything became very

slow.

And again, so I spent a lot writing
custom tooling, custom scripts

to find out and go to the problem.

But if I had really good instrumentation,
like we now are discussing,

so it would took a couple of hours
to find the problem.

And I, as the tunnel producer said
in some of the comments, also,

I mean, he was triggered by these
pictures, that the observability

is investment.

It's not for free.

And for example, in Oracle world,
you just pay it, you just don't

know it.

Because you cannot have the Oracle
without the instrumenting.

So I think they instrumented kernel
in version 7.

Nikolay: And you cannot strip it
out and test it without it.

AntennaPolar is just worth mentioning,
is a very noticeable expert

in the Oracle world.

It's great that also Paths connected
here.

Dmitry: Yeah, well...

And that's why I think the wait
event analysis should be in the

capabilities for that, should be
in the Postgres core.

And 2 ways of doing it.

A small patch that you did with
this...

Did

Nikolay: some patch?

I already forgot.

Dmitry: Yes.

Yeah, yeah.

But I wanted to go further and
build the patch that will bring

Oracle level of wait event analysis,
bring this system time

model, the tracing on the backend
level.

So without this, and at the beginning
I was skeptical and I thought

that overhead would be kind of
similar to this because why it

should be really less.

But in reality, way less.

I have this patch in my GitHub
and so last couple of weeks I

was iterating it, making it better
and better and I hope it will

be ready to propose to a community
just today.

But today I was analyzing, there
was a commit in the master that

breaks a lot.

Yes, they changed some memory management
and basically breaks

some parts in my code and I need
to rework some.

Nikolay: Yeah and it's like now
it's the heavy work of preparing

Postgres 19 and maybe like slightly
later it will be less like

super active in terms of code changes
I guess.

I wanted to mention not only 1
limiting belief was like, OK,

it's not possible.

Now you think, OK, observer effect
is too high.

And obviously, it's not so high.

And I remember the upgrade and
post-resume in PagerBouncer, I

had so big belief it won't work,
but we see now how it works

beautifully under huge loads and
huge clusters.

So we just go and check, right?

Now experimentation is cheap with
AI.

It's great to check instead of
thinking it's not possible or

hard.

It's great.

Like it's super inspiring to hear
what you say.

Dmitry: Yeah.

And back to the Tuli, I still think
it's very applicable in case

you can, you, I don't know if the
patch, my patch or similar

patch with a similar idea would
be committed any day upstream.

Or you cannot afford new versions,
so you will stick with some

vanilla Postgres without these
new capabilities.

Nice thing that it's fully independent
from Postgres.

It runs like a sidecar, so you
can switch on and off.

You do not need to recompile Postgres,
you don't need to change

anything in Postgres.

It just runs.

You can start to next to it.

Yes, you need to self-host Postgres,
you need to have access

to the box.

Nikolay: Root access.

Dmitry: Not root, but with elevated
access rights for running

eBPF, basically.

And so that's a nice thing about
the tracing.

As soon as it's pure tracing, it's
not sampling at any moment,

It opens doors for very interesting
things that are not even

implemented in Oracle world.

And here we can beat Oracle in
progress because it's tracing.

It means that we have a full history
of transitions from 1 state

to another state to the next state
and so on.

And you can reconstruct all performance
problems or clashes or

bursts in your system, not only
in time but also find the dependencies,

who started the problem.

Because even with a wait event
analysis, with a sampling approach,

for example, you cannot distinguish
between you had 10 short

I/O waits or 1 long.

With the tracing, you can do it
and you can find the session

who started the problem.

Because in the regular way, it's
really hard to distinguish the

session suffered from the problem
or it caused the problem.

So with this, I believe, with the
tracing, you can find the root

of the problem and all the consequences
of it.

And with all these transitions,
you can see it as the servers

and can analyze this from the queuing
theory perspective.

You can get the already really
mature mathematician approach

and everything is already there.

You can analyze it, how much you
can serve in a given period

of the time.

And you can find the real bottleneck,
for example, with a regular

wait event analysis you can see
that a lot of I/Os, for example,

that I/Os are bottlenecks.

But in reality, if you see from
the tracing that by I/O always

gets the LWLock, for example,
for any reason.

Even if you would be able to solve
the problem with I/O, giving,

for example, find the faster disks,
the I/O would be shorter,

but anyway, you would be locked
by LV locks.

So the bottleneck would be just
moved to the next stage.

And.

Nikolay: This is what makes benchmarking
so hard.

Yes.

And what you're saying, as I hear,
I don't know all the details,

but what I hear is there is a math,
mathematics, like apparatus

basically, which could help here
and analyze bottlenecks much

better and understand real bottleneck
reliably.

That sounds amazing.

I want this.

Dmitry: So it's not at all.

So not yet.

I have more ideas that's semi-implemented.

So I'm just playing with it because
collect data, it's not the...

Even analyze the data, it's not
the last step.

The last step is present data to
the human to make it consumable

from human to understand this data,
to make the action.

So this is the process analysis.

There is a, again, already people
spent a lot of time, really

smart people thinking and they
already have the really good approach

how to analyze the graphs and when
you move data from 1 state,

for example, tickets, when you
move from 1 state ticket to the

open in progress, I don't know,
test completed and same with

the, I don't know, the logistic
when you need to find the bottlenecks

on your graph.

And you can, with this math, you
can find the patterns that the

most frequent path is that, and
the bottleneck is this specific

state.

It means that the, I don't know,
every, most of your backends

goes through the buffer pin, for
example.

And buffer pin basically the limiting
factor.

You do not need to even look at
the dashboard with your eyes.

System, not LLM, simple code can
do it for you.

We analyze the backends, traces,
yes, everything goes through

the buffer pin.

And so we analyze that the buffer
pin and the system can make

100k buffer pins a second, but
your system demands 200.

So basically you need to solve
this problem here.

So the problem is here.

It's a very deterministic approach.

It can give the report really easy.

You do not need hallucinations.

You don't need to look at your
dashboards for that.

And so I think it opened doors
for really interesting stuff.

And 1 last, I think, idea that
I have and tried to implement

already.

System can find the deviations.

For example, now for Query ID,
the common wait event profile

is, I don't know, spent 1 millisecond
in CPU, 1 millisecond in

I/O, and after that, sometime in
the logs.

But instead of 40 milliseconds,
it took 50 milliseconds.

And it's because it's introduced
a new wait event in between.

So again, it's very easy to, I
think, to write the code to make

this analysis in a semi-automatic
way.

So you just can, like in Oracle,
you open the 1 hour AWR report,

but in the bottom you will have...

So I made analysis for you, so
the problem is here, the query

with the query id, change the wait
event profile from that to

that, and you have a problem with
this, I don't know, disk or

file or something like that.

So it's a kind of a project, but
I think it's doable nowadays.

Michael: Going back quickly, you
mentioned let's say we're self-managing

using Postgres today.

Presumably this tool already would
work for us.

So I'm wondering what the changes
in core would be that you want

to make.

What added benefit would that have?

Or is it mostly about getting it
to work on managed service providers

or something else?

Dmitry: From 1 hand, this tool
I think would enable a lot of

debates to make the perfect wait
event analysis.

But moving these capabilities to
the Postgres core will reduce

the overhead, from 1 hand.

Second hand, you will have it in
vanilla everywhere.

So you would not need to bring
any tool.

You would not need to have access
to the box.

You do not, because not all Postgres
DBAs have it, right?

Nikolay: You have interface, maybe
SQL interface or something.

Dmitry: Yeah.

So it's definitely going to be
available through the SQL views.

Yes.

Nikolay: But the question, we haven't
started any discussions

in hackers yet.

Right.

Yes.

So this is the interesting point.

If when, what will they say?

Because.

Dmitry: Yeah.

And why I still, how to say, want
to make this patch at least

from the first look really solid,
because I expect a lot of pushback

because it's a kind of a consensus
for some reason.

When I talk to on any conf on any
place to the production DBAs

everybody says yes we need it no
dubs but when you talk to the

people who has some weight in the
community who can really promote

something, they, how to say it,
I would say they spend less with

a real production issue, less time
with the real production issues

and that's why probably they pushing
back for any changes in

core that can introduce the performance
degradation, even if

it brings a lot of new, I don't
know, tooling for DBAs.

Because it really depends on your
perspective.

I look at the Postgres as the production
DBA, that's why I think

I would pay 20% overhead so I don't
care just give me a tool.

It's already

Nikolay: paid, so you don't need
to go to them.

As you said you're already working
with this like 10-20% or something

overhead.

Yeah but I guess...

Michael: Quick question on the
tool as it is, it's on the GitHub

repo, it says version 0.8 is the
last 1 I saw.

Are you working towards, what does
1.0 look like?

Is it a reliability thing?

Is it like a more tests thing?

What's missing that you don't want
to call it 1.0 yet?

Dmitry: Yes, I still in some tests
it gets out of memory so seems

to there is a I mean like so yes
I need the the more tests increase

the quality of the code and I want
to get to run it on real box,

not virtual machine from some cloud
provider on real box to make,

you know, to say to people, so
it's if not production ready,

but at least it's safe to run somewhere
on the test environment

because nowadays I still cannot
say it for sure.

But again, I thought I was really
close to the patch to make

this ready for community to start
a conversation.

I know that it's a really long
journey and I wanted to start

this.

And as soon as I had a feeling
that I'm really close to it.

I invested all my time last couple
of weeks in the patch and

I didn't commit anything to this
tool.

That's, I mean, kind of a post,
but I will get back to it for

sure.

But yes, the quality of the question.

A couple of

Nikolay: questions For those who
are interested in trying out

earlier, first of all, what are
best use cases for it in its

current state, not in future state?

And second question is how to start
quickly.

Just clone repo and that's it.

Dmitry: Yeah, I hope at least I
tried to make the readme up to

date and it should be sufficient
just to go through the readme

to compile it and just run it.

I tailored it for my use case,
I mean for my day-to-day life

and I hope that it's a good example
and people should be able

to use it just right after the
cloning.

Nikolay: I obviously see, for example,
the next big benchmark

actually probably should be worth
using it to see how it helps

to understand what's happening
with that benchmark.

But maybe what are the other cases,
like some troubleshooting

of complex situation in production
and try to reproduce it on

a clone or something else?

Dmitry: I think if you, for example,
you have a problem and fortunately

enough you can reproduce it, but
you just don't know in some

isolated environment, you just
don't know what happens during

the problem, you can run the tool
and it would be just look into

the problem with microscope.

It will track everything, it will
show everything to you.

So I think at this moment it's
good enough for that type of usage,

investigating some problem in isolated
environment.

But if you're brave enough, you
can try it on some close to some

load in production because you
just can kill it.

Just

Nikolay: brave enough or desperate
enough.

Sometimes you have a problem you
cannot reproduce and let's go

to production if it's self-managed.

I, in this case, someone goes and
needs it in production because

otherwise it's not like possible
to understand what's happening.

Do you recommend to attach to only
once backend or to analyze

all of them?

Like how's it better to use this
tool?

Dmitry: I built it with the trace
everything, including the auxiliary

processes, this postgres processes,
because sometimes that's

the problem.

And I build this tool with this
in mind, so just let's say everything

and have everything.

But

Nikolay: It's possible to narrow
to only 1 session, only 1 backend,

is it possible?

Possible as well,

Dmitry: right?

Nikolay: Yes.

Good.

So if like, it's just less risk
is lower if you decide analyze

only 1 backend, right?

Then...

Dmitry: Yeah, theoretically, yes.

Surface should be smaller, yes.

Nikolay: Yeah, that's great.

Yeah, I think I'm definitely will
plan to use it in some next

benchmarks I will be having.

I had some benchmarks last Friday.

I just realized I should involve
this tool, but I'm going to

revisit those benchmarks.

They are fully scripted, so I guess
I will just try and see what

it will say.

It's about this queuing Postgres tool.

I will definitely connect to you
about that.

Anyway, I'm excited to see this
work.

It's a very unexpected for me angle
of active session history

analysis, which actually dissolves
1 of the biggest concerns

about ASH methodology, which is
sampling.

Producer statements are precise,
unless query ID is evicted or

unless we talk about the part which
is like cancelled statements,

which statements don't see.

But they are precise, it's just
cumulative metrics counters incrementing,

like tracking all.

I really liked producer statements
when they were created because

before that we had only
pre-pgBadger, we had pgFouine, it's

a French name, and it was PHP script
and it was based on sampling

like everything related to logging
is painful and sampling is

painful.

So okay we have now we now have
exact precise instrument tool

to work with.

But then ASH, ASH is impressive,
great approach, but it's sampling

based.

Now you say ASH can be precise.

And this is like new horizon or
opening for me.

I'm super excited.

Thank you for making this work.

It's great.

Dmitry: And when I working on this
tool and working on the Python

for progress, I understood that
there are some, how to say gaps

in the attributing time to the
queries.

That's not really, how to say straightforward
to understand.

For example, the client reads the
wait event that we see really

often, but is it really database
time or it's still client time?

It's not that simple and easy,
but even if you go deeper, so

there are, for example, you ended
the query.

I mean, you open transaction, begin,
you made some changes, the

last update finished, and you issue
commit or end.

And really, you need to attribute
the time and waits to end,

to commit.

I think it's defaulted behavior.

But also, even after commit, there
are some work that Postgres

does, some cleaning up.

And from pg_stat_statements' perspective,
it's already not there.

And some part of the work is kind
of unattributed, and it goes

to nowhere.

It's not really a big amount of
work, but still, playing with

this precise tool will, if you
are curious enough, so will get

you to the next level of understanding
how Postgres actually

works to the internals.

Really interesting.

I really like for that because
I never thought about it until

I started working with it.

Nikolay: And it's true that not
only you can understand the like

exact sequence of wait events for
this particular load, but also

you can map it to source code,
right?

Dmitry: Of course, nowadays LLM
can explain it to you, so it's

not a foreign language anymore,
so you can speak to it, you can

really easy to understand.

Nikolay: Which opens doors for
more optimization of Postgres

itself?

Dmitry: I hope so, yes.

Nikolay: Like usually with GDB
or with perf, now with this tool

also it's possible.

If you have some pathological workload
you will reproduce it

in a synthetic environment.

Let's go, That's it, yeah, that's
great.

Michael, you wanted to add something,
right?

Michael: Only when you were talking
about unexplained overhead,

it often shows up in explain analyze
when you look at the difference

between the execution time at the
end of the query plan and the

actual total time of the like the
top level operation you almost

always see a small discrepancy
there as well and not accounted

for anywhere which is yes the same
thing you're talking about

I think.

Dmitry: Yeah a few percent there
and there and you lost 10 bucks.

Michael: Yeah well yeah I'd like
to echo what Nik's saying it's

really interesting work that you're
doing and thanks for sharing

and also I think you you published
the tool under the Postgres

license which is

Dmitry: really cool.

Nice 1.

Yeah I personally believe in it
so if you develop for Postgres

just then share it.

Nikolay: And what book on Queue Theory
you are reading like for quite

a long time as I know?

Dmitry: Yeah so it's a quite old
1 so let me...

Nikolay: Because it connects me
to my current work, like this

tool I just released.

And I guess I need to read it as
well.

Dmitry: I will send it so you can
put it to the description.

Nikolay: Okay, good luck with the
tool, definitely, I hope it

will be popular.

And I hope your patches will be
welcomed in hackers mailing list.

Dmitry: Yeah, we will see how it
goes.

Nikolay: Great, thank you so much
for coming again.

Michael: Thank you.