Nikolay: Hello, hello, this is
PostgresFM.
I'm Nikolay from Postgres.AI.
And as usual, my co-host is Michael
from pgMustard.
Hi, Michael.
Hello, Nikolay.
So I chose a very boring topic.
And we talked about it a lot I
hope this is the last time we
were going to talk about it maybe
right BUFFERS or pages or blocks
or how how to name it depending
on the perspective you have, right
but BUFFERS episode 3, which
title are you going to choose?
Michael: Oh, it's like the third
in a trilogy, isn't it?
Maybe it's like Return of the BUFFERS
or something, I don't know.
Nikolay: Right, but we have a few
reasons to revisit this topic
today.
Of course, this topic has been
very heartwarming for me.
For a couple of years, we discussed
how important it is to consider
BUFFERS when optimizing queries
at micro-level, when we talk
about EXPLAIN ANALYZE, or at macro-level when we talk
about pg_stat_statements and other
extensions, and aggregate
everything and see parts of workload
or the whole workload.
And recently, finally, as we already
mentioned a few times, BUFFERS
were added to EXPLAIN ANALYZE
by default after a few unsuccessful
attempts from previous years.
I think it was David Rowley, right?
Michael: Yes, and Guillaume Lelarge
played a big part as well.
Nikolay: Right, and all previous
hackers also who made even unsuccessful
attempts made some shift in this
direction.
Of course, it's really a small
technical change.
So now you don't need to write
the word BUFFERS.
You just write EXPLAIN ANALYZE.
And as a reminder, EXPLAIN just
provides a plan for the query,
which planner is going to choose,
optimizer is going to choose.
And then EXPLAIN ANALYZE actually
provides both the plan and actually
execution.
It actually executes the query,
so you see more data.
And by default, buffer information,
buffer hits, reads, BUFFERS,
dirtied and written, we are not
present in the plan.
And that was a bummer for us who
dealt with queries at scale,
right?
So because this is important information,
as we discussed, and
we will discuss again, I think.
But now in Postgres 18, you don't
need to write BUFFERS explicitly.
This information will be included
by default.
And that's great, that's great.
I think it's a super important
small change which will affect
lives of many engineers.
Because...
Michael: Yeah, I think just worth,
I know you've alluded to this
already, is an upcoming version
of Postgres 18.
It's been committed, and things
that get committed have a very
good chance of making it to the final version.
But things do get reverted.
So there's a small chance that we'll still have to talk about
this more in future but yeah we already had the ability to request
BUFFERS we could already add EXPLAIN (ANALYZE, BUFFERS) and other
parameters but a lot of the time when people are asking for help
online or when people set up auto_explain although I see BUFFERS
more in auto_explain I don't know what your experience is but
yeah when people are asking for help online or when people set
up auto_explain, although I see BUFFERS more in auto_explain, I
don't know what your experience is, but yeah when people are
asking for help or people are writing blog posts or people are
just getting familiar with Postgres, they'll come across
EXPLAIN ANALYZE and then not know that BUFFERS exists or not use it when
it would actually have shown the issue and so there's a bunch
of cases where by having it on by default we'll be able to help
more people online when they first have an issue without having
to ask them to go back and get another query plan or it will
just be more likely to be in, well it will be in those query
plans by default if they're on Postgres 18 or newer.
Nikolay: Yeah, yeah.
And I know you have also opinion and I agree with you that some
other things should be included if we provide something like
all or verbose.
There is verbose but it doesn't include everything and this is
slightly strange and unexpected if you don't know details.
But let's focus only on BUFFERS.
It's really hard for me to imagine it will be reverted.
It might be, but I think, yeah.
Michael: I could just imagine this aging really, you know, aging
like milk, we say sometimes, just in case.
I just wanted to make people aware that might have happened.
Nikolay: Yeah, so we talk about Postgres 18, which is going to
be released this fall in many months and it will hit production
only in a couple of years, on average, maybe, or maybe even more.
So it's still in far future, in distant future.
But I wanted to just think in advance, since we are both building
some tools and methodologies and everything, I wanted to think,
is it going to have some positive, I think, positive effect on
how we are going to optimize queries in the future.
For example, defaults matter a lot at scale, and I learned it
building 3 social networks in the past.
The simple lesson I remember some spammer was sending on behalf
of administration was sending the message, send text message
to this number without any reasoning.
And if you send it to 1000000 people, there will be some people
who will follow.
Because, you know, like, why not?
Of course, if you add some reasoning, it will work even more.
So defaults matter a lot.
And we can put everywhere words
like don't forget BUFFERS, don't
forget BUFFERS, but if it's still
like if default behavior remains
without BUFFERS, there will be
a lot of people who will not use
it, right?
And this is what we see If you
go to explain.depesz.com, it has
history.
And, of course, actually, even
explain.depesz.com also advocates
for BUFFERS since not long ago,
which is great.
But if you just check history,
you see a lot of plans provided
without BUFFERS, right?
Because defaults.
Now we have good reason to expect
that after a couple of years
we will see majority of plans with
BUFFERS.
Do you agree?
Michael: Yes, maybe not in the
full, but maybe in like definitely
in 5 years time, maybe in 234 years
it will become the majority.
Nikolay: Well let's say if it will
be 18 plus version, this plan
like again 99% or even almost 100%
that will include BUFFERS
because only few people will bother
turning it off.
Michael: Oh, I meant actually people
still running very old versions.
Yeah, that was my understanding.
Nikolay: But imagine we're already
thinking about people with
new versions and I think we will
have BUFFERS.
What does it mean for analysis?
Are we going to highlight this
information or use this information
even more intensively?
What do you think?
Michael: Well, good question.
I think people will ask...
People already have lots of questions
when looking to
EXPLAIN ANALYZE output for the first time
and people are like what does
this statistic mean what does that
statistic mean and I think
BUFFERS are another kind of 1 that
at first people are a little
bit confused by what does it mean?
Like what is that number?
Is that a lot?
Is it not a lot?
What unit is it in?
All these kind of questions.
So I suspect it will immediately
in people's heads have a bit
of extra confusion.
But once they're familiar, I can't
see how it isn't helpful.
It's just going to be so helpful,
especially once people start,
like, people and tools start converting
that into data volume
sizes.
I know it's an imperfect, but let's
accept the imperfection of
timesing it by 8 kilobytes, so
each buffer being a page, and
multiplying it by 8 kilobytes,
you get an idea of the data volume
being read.
When people see a query and notice
it's reading a gigabyte of
data, even if it's from shared
memory, when it's not using an
index or when it's using a specific
index, and then it's only
using a few kilobytes when it's
using a different index, suddenly
they realize the index is helping
speed things up because it's
more efficient because Postgres
is only having to read far fewer
blocks, like much less in order
to serve that query.
I think it becomes a lot more intuitive
that indexes help because
they're a more efficient way of
running certain queries, not
their magic.
And I think this is currently not
amongst Postgres folks, but
definitely in the developer community
as a whole, there's this
perception that indexes are magic.
And I think BUFFERS being present
help is like a good step towards
no they're not magic they're just
a they're very clever idea
that makes things really simple
Nikolay: yeah yeah that's a good
point I was listening to you
and thought I'm contradicting to
myself when I say buffer, BUFFERS,
hit and read and written and dirted.
It's incorrect, it should be operations,
BUFFERS, buffer reads,
hits, writes, and I don't know
how to deal with dirt.
Michael: Dirties?
I don't know.
Nikolay: Yeah, yeah, yeah.
Pieces of dirt, right?
No, no, no, like actions of dirt.
But anyway, like I'm saying, these
are operations And if you
sum up some numbers, it might be
the same buffer which you hit
multiple times.
Not sure about other operations,
but hit, we definitely have
in the nested loop, we can have
hits for the same buffer many,
many, many times.
But when you say we need to convert,
I totally agree.
I did it for a long time.
I noticed that very long ago, once
you converted to bytes, megabytes,
gigabytes, sometimes terabytes
actually, tebibytes, right?
Gibibytes.
Once you convert it, engineers
have a ha moment.
Always.
I mean, those who see it first
time.
Because 100 BUFFERS, buffer hits,
or reads, 100 buffer reads,
there's nothing to them, actually.
It's what it is.
Hard.
But once you say, okay, we here
we read 800 kibibytes to return
a number of like a 8 byte number.
You can feel it already how much...
It's just...
To return 1 byte, you are reading
100 or 800...
How many?
KB.
Each KB is 1024 bytes.
Wow, like it's not very efficient, right?
How about having I don't like hash hash or hash table or something
to find this number and then Oh, okay, this is why like, because
it's a sequential scan.
And this sequential scan is just scanning whole table.
If we remember big O notation, sequential scan has terrible,
terrible performance, because so many operations.
And it also depends on the size of each tuple, how many tuples
are present in 1 8-kilobyte block, 8-kilobyte buffer, right?
So if only a few tuples there, we need to read a lot of data
from memory or from disk.
So once you switch to a B-tree index, it becomes only a few
buffer operations.
Even if you have a billion rows, it will be like 7.
I don't remember exactly, but
Michael: it's
Nikolay: like 7 buffer hits.
That's it.
And even, okay, they are still like 8 kilobytes, it's a lot,
but it's already much better than if it would be sequential scan,
which is insanely slow in this case.
And you think, okay, that's why it's slow, because we deal with
huge volumes of data.
And when we apply index, we suddenly deal with very low volumes
of data.
Right?
And this is number 1 reason why database becomes fast.
This is how indexing works.
Like, we just reduce the number of I/O
Operations.
And when I say I/0
Operations, I often include operations with memory hits.
Because, well, we know operations with memory versus operations
with disk, it's like 1, 000 times difference.
It's, of course, a lot, but still, like, we can compare it, and
1 read is roughly like 1000 hits to memory.
Unless this read is also from memory, from the page cache in
this case.
So they're very similar.
So what I'm trying to say, we talked about this reasoning and
I agree with you, we need to convert to bytes and present it.
At the same time, we need somehow like have some remark that
these buffer hits might be to the same buffer.
It's not only...
It's a volume...
It's some abstract volume of data.
We can have a kilobyte or megabyte of data heating the same 8-kB
buffer, right?
So it's okay.
But comparing volumes of data, it's so good.
Because first of all, you start thinking about data volumes,
which is the number 1 reason for slowness, especially if we forget
about concurrency.
Because concurrency is a different topic.
If we take just 1 query, there are cases when it can be a very
CPU-intensive workload, but in the majority of cases it will
be I/O
Intensive workload for 1 query, for databases, right?
And our goal is to help Postgres with indexes, with maybe some
redesign, to help Index to deal with as few operations with memory
and disk as possible, it means we need to focus on BUFFERS.
Michael: Yeah, are there any...
I think there might be a few other exceptions, but I think they're
more...
Yeah, I don't think that...
Well, maybe it's only 1 I think maybe a CPU as well actually
I was thinking JIT compilation what that's
Nikolay: probably recently we had a recent discussion about our
RLS and there we had inefficient memory work CPU work like check
some volatile function or stable function also, right?
So if it's in loop for all rows, it's CPU intensive work.
Michael: Yeah, there have been, I was just trying to think, there
have been a couple of other times I've really not been able to
spot issues by looking at BUFFERS.
But they're not actually, it's not because Postgres isn't reading
data, it's just it's not reporting it in EXPLAIN ANALYZE yet.
So actually, maybe getting it on by default will encourage some
more reporting of BUFFERS as well.
So, for example, I don't think triggers report the BUFFERS read.
Like, if you have, you know, the famous case of not indexing
a foreign key and then having on cascade delete.
Like that trigger might tell you it's taking many, many seconds
because it's having to do a sequential scan of the reference
table but no BUFFERS reported as part of that.
Same with in memory operations, like sorts or hashes.
They could be really slow, even though they're done maybe you've
got quite large work mem, and maybe it's the dominant part of
the query plan, but no BUFFERS reported as part of that.
Because it's in memory.
It will report the BUFFERS if it spills to disk.
For reads of tables and indexes, we get reports even if it's
from memory.
But for operations like sorts and hashes, we don't get.
So there are a few things that are still doing work that it would
be good to get those.
But it doesn't go against what you're saying in principle.
It just means I can't use what Postgres is actually giving me
in EXPLAIN (ANALYZE, BUFFERS) to actually diagnose the issue there.
Nikolay: Yeah, I remember also touched the topic that it would
be really great to see details
for BUFFERS.
For example, how many BUFFERS from
heap, from index, maybe distinguish
in each index, some details to
see.
Because if we go back to the original
approach for dealing with
EXPLAIN ANALYZE, and I see it like
even many hackers, blog posts,
provide plans, ANALYZE plans, but
they don't use BUFFERS still.
I hope this will change, of course,
over time.
So usually, okay, we have time,
we try to guess, okay, this time
is lost here, why?
Okay, because sequential scan.
But in terms of data volumes, what
do we have?
Only rows, right?
Expected and actual rows.
Logical rows.
And sometimes we think, okay, we
fetch 1 row here.
Michael: Oh, we get width as well.
Like an average estimated width.
Which is...
Times by rows gives you some idea,
but it's only the width of
what's being returned not the width
of what's being read
Nikolay: Exactly.
This is like, okay.
Yeah, we can multiply with by rows
get big basically number of
bytes But it will be like logical
level and an underlying level
might be very different, very different,
drastically different.
For example, if there is a lot
of dead tuples, which Postgres
checked to return this very row,
right?
Maybe it checked a thousand dead
tuples.
And it's hidden if we look only
at rows and timing.
And we have no idea why timing
is so bad, right?
Okay, we returned 1 row in the
index scan, but why so bad?
Here's why.
We go to BUFFERS and we see that
a lot of BUFFERS were hit or
read.
I don't know, it doesn't matter.
That's why timing is better.
And then, my point is that it's
already very useful, But imagine
if we saw, oh, actually, from this
particular index, from this
particular table, we got those
operations with BUFFERS.
It means that to return this row,
so many BUFFERS, even with
index scan, it's bad.
Oh, that's why, because it's bloat.
This row had many versions not
cleaned, and the index scan needs
to check heap table to get version
information.
So this would be useful to like
detail BUFFERS, to have detail
BUFFERS maybe, right?
And I
Michael: had this case recently,
I was in January, I was doing
some office hours calls just to
get an idea of what kind of issues
people were facing and I had some
really good conversations thank
you to everybody that jumped in on those and but 1 of them they
were they were using our tool pgMustard and it was 1 of the
tips we show people is, we call it read efficiency, to look for
exactly that.
When are you reading lots of data?
And we actually, for long-term users, used to call it table bloat
potential, or like bloat potential.
So it could be index bloat, could be table bloat.
But we renamed it read efficiency a few years ago because there
are other possible causes of it as well.
So it's a tricky 1.
And because we didn't, so this office that I was called, they
were hitting quite a few read efficiency issues but they couldn't
diagnose exactly where it was like was it index level was it
table level they looked at both it didn't they didn't seem bloated
they reindexed they they tried a lot of the things and it couldn't
reduce it so it was a it was a really interesting call but that
yeah that was my first port of call is in this used to be true
more in older versions of Postgres but indexes can get especially
can get extremely bloated so can tables in some cases but yeah
it's it's amazing that you can suddenly see that by turning on
BUFFERS.
And that is something I wasn't expecting to be true.
Because I see bloat as kind of like more of a system-wide issue,
But to be able to spot it in query licenses was really cool.
Nikolay: Oh, bloat can be very tricky.
Sometimes we have, with our consulting clients also, we have
like bloat is low, but some query suffers.
And it turns out to that, like, we call it usually local bloat.
So for particular IDs, the situation is super bad.
It can also be not a bloat, but some, you know, like sparsely
stored records.
They are distributed among many BUFFERS.
And you expect, okay, I have very narrow table, only like 4 or
5 columns.
I expect a lot of tuples to be fit inside 1 page.
But somehow reading 1, 000 records, rows, I deal with thousands
of buffer operations, what's happening here.
And if we just check CTID, we can understand that each row is
stored in each particular page, so we have a lack of data locality
here.
And so clustering with pg_repack without downtime could help, or
partitioning helps usually in such situations and so on.
Yeah, yeah, yeah.
So, and the buffer is exactly the way to fill these, all these
problems better.
But I agree, I like your idea to talk about read efficiency and
maybe write efficiency as well.
Because for end user, it's obvious.
Okay, I have, I return this data, I return only 25 rows on my
page.
So I expect not gigabytes of data to be fetched from buffer pool,
or even worse, from disk, right?
Or from page cache, which is between disk and the buffer pool.
So I expect maybe only like some kilobytes, right?
Maybe tens of kilobytes.
Even not megabytes, if it's quite a narrow table.
Postgres should not do a lot of work to show 25 rows.
If it does a lot of work, something is not right and we need
to optimize this query.
And without BUFFERS, we only can guess.
With BUFFERS, we see it, right?
With buffer details, it would be even better, right?
Someday maybe we will have detailed BUFFERS per each database
object.
Some statistics would be interesting, I think, to observe.
And there is criticism of this approach.
I recently had an interesting discussion on Twitter on X, And
somebody told me that in Oracle, there is ability to flush caches.
Right.
And in Postgres, it's tricky.
Usually it means you, we need to restart Postgres to flush the
buffer pool and also to echo 3 blah blah blah to flush Linux
caches if you want to go hardcore and very cold state, to check
very cold state.
And this is interesting.
Michael: I think you can restart.
Nikolay: Restart what?
Michael: The Database.
Like if you're testing locally, for example, or on a clone, if
you restart the Database, doesn't that reset caches?
Nikolay: Restart of Postgres will make empty only the buffer
pool.
But we also have, if it's Linux, We have also page cache, right?
We can flush page cache with simple command like we of course
if you have sudo
Michael: Yes,
Nikolay: so it's it's easy.
I always Google it echo 3 to some path and and then sink and
that's it
Michael: If you're a managed service, what's the best you can
do?
Nikolay: Well, managed service, you cannot do this, of course.
Michael: Restart is like, I
Nikolay: think the best
Michael: you can
Nikolay: do.
Reboot.
Reboot, yeah.
Yeah, yeah, reboot.
Well, this is hardcore.
So let's talk about this.
I also see a constant desire to
check the cold state in the Database
Lab we built.
Some users use it for query optimization
with some bot we have,
drawbot, very old stuff, but it
works really well for query optimization
and also like experiments, right?
So you can check on thinkloan,
on branch, you can check various
ideas And there people say, okay,
I see the hot state.
Data is already cached.
I want to check the cold state.
And I think it's natural desire,
but yeah.
Well, every time we say there is
no such easy way.
And interesting that, I didn't
know, but interesting that in
PostgreSQL 17, in pg_buffercache,
there is a new function, pg_buffercache_evict,
and you can try to use it.
I think we are going to try it
at some point when Postgres 17
will be more present on production
systems.
We probably will consider adding
this to make the buffer pool
empty, right?
But unfortunately, this function,
I suspect it won't work really
well.
It's definitely not for production,
first of all, right?
It's for like lab environments
we build.
And unfortunately, per documentation,
it won't evict BUFFERS
which are pinned by, for example,
autovacuum, vacuum process
and so on.
So in some cases it will fail,
we will need to think how to deal
with it.
Of course, restart is definitely
working in this case.
So if we think about why do people
need cold case at all, What's
your opinion on this?
Michael: Well, I think it's coming
from a good engineering rationale.
I want to make sure the worst case
isn't too bad.
It's definitely, you want good
engineers to be thinking about
edge cases, to be thinking about
the worst possible case.
But I also think that in practice,
the kinds of optimizations
we're doing here, the whole point
of the discussion here is we're
saying try and reduce the amount
of data being read to as minimal
as possible.
If it's an important query, try
and get a very, very good access
path for that query.
That will involve getting the buffer
numbers down as much as
possible, whether they're cached
or not.
Maybe you want to explain to people
in the PR, or you need to
explain to your team, how much
faster has this made it?
So I can understand wanting to
compare cold cache state to cold
cache state, and then having these
kind of relative differences.
But I would encourage going the
opposite route.
It's much easier to work with warm
cache, especially when you're
doing query optimization, you're naturally going to warm up the
cache if you're running the same query over and over again.
So I would encourage more going the opposite direction and say
compare...
Yes, it definitely applies for OLTP, but even for OLAP or OLAP
queries, I don't see the downside of saying to your team, these,
I mean, it's still EXPLAIN ANALYZE, right?
If you're showing query execution plans before and after, which
is often what people are doing, kind of show this is what it
was, this is what it will be, it's already imperfect in terms
of reporting numbers.
It's not exactly the same as what the client's seeing.
So If we're already imperfect, I don't see the downside of comparing
warm cache to warm cache, so I tend to run things a few times,
get the...
Nikolay: Before optimization and after optimization, we compare
2 cases, both should be warm, and we focus on not...
Even if we have reads still, for example, buffer pool may be
smaller in lab environment.
We usually have much smaller buffer pool because we run multiple
Postgres instances on the same machine.
It's a shared environment.
So if you deal with large volumes of data, it might not fit the
buffer pool, so you are going to see reads.
But the idea is let's just summarize reads and hits, maybe even
writes and dirties, how to name it, right?
So all 4 buffer operations, if we just summarize it and use it
like as universal metric, this is now our number of...
Or just maybe reads and hits, depends, right?
So but overall, we just see this is overall volume of data.
And this is what you return, 25 rows.
Michael: I like that a lot that's exactly what we do but I get
the sense that what people really want is to compare timings
so they want to say this query that used to take Because sometimes
it's coming from a complaint, right, or something.
This query that used to take 20 seconds now is 500 milliseconds.
Or this query that used to be 200 milliseconds is now less than
a millisecond.
So they want this kind of difference in timing, even though behind
the scenes it's a difference in BUFFERS.
So for that, if you want kind of apples to apples comparison,
instead of trying to get cold and be fair on the cold side, I'd
just say it's easier to be fair and do it on the warmer side.
I know it's not perfect, and it might be that your real users
are hitting a cold cache state, but if that's what you want,
that's the direction I would go.
Nikolay: Yeah, well, we have slightly different methodologies
here.
I usually say, okay, we start from the statement, the query is
slow, it takes like 30 seconds or 1 minute.
It's super slow, unacceptable, we want it below 100 milliseconds
or 10 milliseconds.
And then I say, forget about timing for now, completely.
Focus only on BUFFERS.
If we check BUFFERS and see a low volume, 100 BUFFERS, 100 buffer
hits and reads, to return 25 rows, we know our micro-optimization
is over.
We need to go and see the whole picture of what's happening with
concurrency.
Probably we'll have some...
Maybe this query is waiting on lock acquisition of all those seconds.
That's it, right?
So it's already this.
But if we confirm large volumes of data, we forget about time.
We focus only on obtaining sane numbers for BUFFERS.
Sane means, okay, hundreds, thousands, not more.
Even thousands to return 25 rows, it's already quite a lot.
And we must do it.
Sometimes, of course, it's not easy.
Sometimes we need to do denormalization, some complex surgery
on our database involving long-lasting operations with backfilling
and so on, but once we achieve this, in most cases we can have
same number of BUFFERS and then we say, okay, now we can compare
timing.
And Comparing timing in lab environment, it still might differ
from production.
Michael: Yeah, of course.
Nikolay: But of course, I know, I know, I use it as well.
I say, we improve timing hundred, like 10000 times.
It happens, right?
And this is good for final comment you know in the end but only
when we did work on BUFFERS fully right this is me
Michael: yeah given it's you you you could with your reputation
and your teachings you could leave that last line as we improved
buffer read or read efficiency for example.
Read efficiency and it would be probably almost the exact same
number.
Nikolay: Not necessarily, but yeah, maybe.
Michael: You'd be surprised how often it's close.
Nikolay: Okay, good, good, good.
Yeah, that's good.
Maybe I should think about it and also present this information
during consulting activities and in tools as well.
Yeah, I think it's good.
So yeah, read efficiency.
And you take number of rows and width and just multiply, get
bytes from there and then...
Michael: Yeah, it's a tricky 1 because like different scan types
you expect different amounts of efficiency so yes it's slightly
Nikolay: more involved also interesting right
Michael: yeah but I think I think the main differences are sequential
scan like sequential scans very different from well sequential
Scan and Bitmap Scan have some similarities.
Index Scan and Index Only Scan have some similarities, but then
like looped index scans are somewhat different to like range
index scans in terms of how many tuples, like in a loop for example,
you can't get fewer than the number of loops, like it has to
do at least that many.
So yeah, there's some subtlety, but yeah, that's it essentially.
Nikolay: Yeah, But back to the cold state, I also agree.
I can imagine you would need it.
And unfortunately, it's possible, as I see, only in lab environment
where you are alone.
Because this is a global operation to flash operational system
page cache, for example.
Michael: And when you control the hardware, right?
Or like the, you know, the VMs.
Nikolay: Well, if it's a managed situation, again, restart, as
you said, also an option, but again, this only works if you're
alone, and this is expensive.
We aim to make experiments super, super, super, extremely fast
and cost-efficient.
So it means a managed environment.
Many people work on the same machine, so if 1 decides to flush
everything, others will notice, right?
And With this function, pg_buffercache_evict, I think if we manage
to make it work, I see it's useful.
Even imagine if we have page cache, or in the case of DBLab,
it's ZFS-Arc, which is common.
If 1 block is cached, it's cached for all clones, all branches,
it's very good in terms of efficiency.
But sometimes, indeed, engineers want to check inefficient situation.
Okay, in this case, I think what we will do, we try to okay,
if user requests for their particular database, particular Postgres
instance running on this machine, we can ask to evict maybe everything
from buffer pool or for particular tables and indexes, say everything.
And if we succeeded, there are chances that these buffers are
cached in underlying cache, right?
Page cache or arc.
In this case, query timing is not going to be very different,
but plans will be different.
We will see reads instead of hits.
Observing buffer numbers, we will see the difference.
Okay, reads now, not hits.
And what I'm thinking, In this case, we could consider it relatively
cold, this situation.
Michael: Lukewarm.
Nikolay: Yeah, yeah, yeah.
And we could just say, okay, we know that reading from disk is
roughly a thousand times slower than from memory.
So we could do some estimation
of real call state.
Michael: Well, or there's that...
How often do you see clients having
track_io_timing on?
Nikolay: Oh, very often.
Michael: Great.
Okay, that's really positive.
But that because that gives us
an idea of how long those reads
rather than hits took.
And you could do some division
of reads by I/O timing to get an
idea of where they're coming from.
I think there might also be some
work.
I think I saw something either
in a commit for a century or a
hackers thread to try and get
Nikolay: the details.
It's like, there's just that cache
for micro level, right?
Yeah.
Well, it's possible if you have
access to everything, it's possible
from proc, process number, I/O,
you can get it from there, I think.
Michael: But it would be good to
have it in Postgres core so
that we could even get it from
managed services, for example.
Nikolay: Yeah, yeah.
Well, there is something that can
be done here and improved observability
of part and work with particular
query if you have control.
Yeah.
But I'm trying to say, like, even
if you with this evict, we
can get some interesting information,
even if we cannot allow
ourselves to reset the whole, everything,
including page cache.
So it already can be useful in
non-production environments to
study the query behavior if it's
a clone.
Yeah, yeah.
So I feel there's a lot of things
that can be improved in this
area to work with queries.
And especially it's important to
continue this work Because I
suspect we will use some tools
to work on this at massive scale.
Like for example, if we fetched
from...
We already know during consulting,
we did it with a few clients.
With auto_explain, we fetched
a lot of queries, hundreds usually,
sometimes thousands.
It's insane.
Nice.
Examples with plans.
And then, well, actually, you know,
because we partnered, in
some cases we use pgMustard to provide
additional insights, which
is great.
And then we have a lot of stuff.
Unfortunately, you know, unfortunately,
I still don't see how
to avoid human here.
Well, not like, I like to involve
human there, But I would like
to involve less, you know because
usually Right now we have a
lot of cases of query analysis
like that and then we need to
draw some common picture from it right and this This like going
above all those plans and say, okay, we have a pattern here.
For example, bloat is obvious or something like this, like, or
index health is not good.
And to draw this picture, human is involved heavily right now.
I'm thinking like over time, probably we will build some additional
macro-level analysis for micro-level cases of hundreds or thousands
of them.
This is going to be great.
And then looking at BUFFERS, and actually we have BUFFERS there
as well.
You were right in how to explain timing is not present.
People are afraid of overhead BUFFERS also have overhead, but
kind of smaller.
Right.
And yeah, we had articles, you had articles about this as well.
Yeah.
So yeah, and so with cold cache it's tricky, and there is this
function, that's it.
So Postgres 17 plus.
What else?
There is macro level, right?
And at macro level we can talk about pg_stat_statements, blocks,
read, hit.
By the way, there it's, yeah, also naming, we talked about it.
It's also naming like it's data volumes, but it's okay.
And so read, hit, written, dirtied.
For shared buffer blocks.
In pg_stat_statements, also there is pg_stat_kcache if we want
real disk I-O to be observed.
And again, I advertise to include pg_stat_kcache in any setup.
It's a great extension.
And there we can talk about some macro level, like, okay, this
query ID has this volume per second, or this volume per query,
or it's responsible for 90% of all hits to the buffer pool happening
in database.
Maybe it doesn't look super slow, but it's so intensive with
shared buffer pool.
Michael: That's a good point.
I almost always encourage people to look at their top queries
by total time.
Unfortunately, with pg_stat_statements, at least, there's no
kind of total blocks.
Because it's split into these many, many steps, you could order
by shared hit plus shared read and get a good sense of 1 type
or by dirtied for a different, but yeah, you'd have to sum in
most cases I'm thinking of that would give a good system-wide
aggregation, you need to sum 2 columns, I think, to get a good...
Nikolay: Reads and hits, right?
Michael: Ordering.
Yeah.
Nikolay: Yes, yes.
So we usually...
It's underestimated approach with
pg_stat_statements to order by
sum of reads and hits.
Michael: Yeah.
Nikolay: It's super important.
This is how you identify I/O-intensive
queries.
And now they can be mostly hits,
but if database grows, they
can be converted more and more
into reads.
Or vice versa, you're going to
double your memory size and the
buffer pool size, so reads will
be gone.
But just sum them and consider
them together to understand how
IO intensive the query is.
And think, oh, this query probably
needs optimization, especially
if it returns only 1 row or 0 rows.
So, yeah, in this case, we can
also consider buffer-focused optimization,
which is important.
I think it's underestimated.
But also, it's worth mentioning
this interesting blog post from
Andrei Lepikhov, who gave, I would
say, a hacker researcher perspective
on this, saying, actually, I didn't
mention that this is my point
for sure for lab environments we
build it's important to say
timing is super volatile it's unpredictable
you can have timing
1 in 1 today another tomorrow 1
on production another on clone
of production Very different timing.
And it will be a big question to
track all reasons why timing
is unpredictable.
And there is a concept, there is
a simple methodology, if we
deal with a complex problem, let's
split it to pieces and analyze
pieces separately.
So when we take a clone and bring
a single query and optimize
it, we already get rid of concurrency
issues, heavy locks, lightweight
locks, everything, like forgetting
about them.
And we deal with a single query
alone in 1 clone and it's already
super good.
But we still need to have something
reliable in terms of metrics
to optimize.
And timing is not reliable because
it's unpredictable.
It depends on the states of caches.
If it's a shared environment, maybe
CPU and disk are super busy,
and there's noise from neighbors
when we optimize in this case,
right?
But once you focus on BUFFERS inside
optimization, you have invariant
metric.
Okay, it can be reads versus hits,
but some of it will be constant
if you repeat the query.
For this particular clone, for
this particular query, these parameters,
everything is the same.
Plan is the same, right?
So you have the same thing.
It's repeatable, it's reproducible.
This gives you a foundation for
optimization because without
it you have very shaky foundation,
like not solid foundation,
right?
Timing.
So, and then you optimize and you...
I like your idea about optimization,
like, actually I used it.
I used it.
I said, okay, 10,000 times timing.
And I remember I mentioned BUFFERS
as well, but I didn't expect
people will value this comment
a lot.
Maybe with BUFFERS by default,
we should push on this methodology
more and more.
But what Andrei is talking about
in this article is that for
research purposes and so on, instead
of timing, maybe we should
focus only on reads.
And reads is something that could
be a target itself for optimization
and decision if the system works
efficiently or not efficiently.
Because we are talking about databases
and I always the number
1 thing in terms of what takes
time.
And I like this idea.
Yes.
So like this is very close to what
I see for many years building
lab environments, non-production
environments, which can help
people scale their teams and databases
and processes and so on.
Yeah, so what's your opinion about
this article?
Michael: Yeah, I thought it was
good.
I think all of, I think this blog's
great, but it's very, I think
it's very hacker focused.
And I think that's good, right?
Like This is a good topic for hackers
as well.
There's a lot of blog posts I see
from people often explaining
performance issues and not including
BUFFERS, and it seems like
a missed opportunity to educate
on that front.
So this is a great argument for
the people doing the educating,
I think, or even doing the implementations
of why it can be helpful
to see this.
I think he makes some good points
as well about hardware changing
over time and his initial paragraph
is even about I can't reproduce
this paper.
Nikolay: I can't reproduce timing, yes.
Michael: Yeah, because all this
and it's not just that it's also
there was and this is a different
point slightly but there's
insufficient setup information
which is really common in benchmarks
it's really common to not include
which exact machines were being
used or which exact.
So without that information, BUFFERS
can still be used as like,
oh, this is interesting.
Sure, there might be optimizations
over time that Postgres can
use fewer BUFFERS for certain operations
or certain indexes become
more efficient.
There's been a bunch of work to
optimize B-trees in Postgres
over the years, and I would expect
that to lead to slightly fewer
buffer reads in a bunch of cases.
But they would be easier to see,
and easier to see the differences
in than timings.
And I think he's also got a really
good graph here that shows
that there's not 0 variance in
buffer numbers, but it's much,
much lower and it's way more stable
than timings.
Nearly completely stable with some
exceptions, which is my experience
as well.
Nikolay: Yeah, yeah, yeah.
I agree, especially for example,
if you run the query first time
real cache is not there, it will
give you,
Michael: yeah, planning
Nikolay: time, it will give you
huge overhead and huge buffer
numbers, but second execution will
fully shave it off.
Michael: Yeah, planning BUFFERS.
Nikolay: Planning BUFFERS is a
separate topic.
It's a very important topic.
I like to have it.
Michael: Me too, but I don't fully
understand them yet.
Like why do we get so many more
in the first execution and then
why do we sometimes get none reported
at all?
Nikolay: Ah, okay.
You mean, yeah, yeah, yeah.
I think we discussed it maybe,
not sure if you, I saw some very
weird behavior in planning BUFFERS.
Yeah.
Maybe something is not tracked
as well, as you've mentioned.
Michael: I think it
Nikolay: must be.
Foreign keys, yeah, yeah, yeah.
So maybe we should dig into this
topic and raise it, the discussion
about that.
Yeah.
So back to Andrei's blog post.
Michael: Yes.
Nikolay: I agree.
And like I cannot reproduce and
we built lab environments sometimes
very different from production
just for the sake of cost optimization.
Like if you have 360 core Epic,
5th generation Epic on production,
and almost terabyte or more of
memory, it doesn't mean you cannot
optimize your queries on Raspberry
Pi.
You can on magnetic disks, very
cheap.
And that query fully cached might
be slow for you on production,
might be like a second.
You go and optimize, you understand
that you have shitty hardware.
In this case, you just see, okay,
it takes minutes or hours.
But BUFFERS are the same, and plan
is the same.
You can make decisions on super
cheap hardware with smaller caches,
everything is smaller, very slow
disk, you can still make cautious
decisions and move forward with
optimization, find solution,
see that you optimize buffer numbers
like a thousand times and
expect similar effect, maybe the
same effect on production, affecting
time accordingly.
Right.
And this is the, this is the power
of lab environments.
I think everyone should have them
super cheap, shared.
So many people and CI pipelines
work together and focusing on
BUFFERS is magic.
Right?
Yeah, I
Michael: think it's easy.
I think you're completely right.
I think it's easy for people to
make the mistake of then also
not using sufficient data volumes.
I know you don't.
I know that's not what you're advocating
for.
But yeah, it's the data volumes
that matter way more than the
power of the disks or the specific
CPU.
Nikolay: Yeah, usually people say,
okay, we are going to have
weaker hardware for non-production,
and we are going to focus
on relative optimization.
So if it was a second in production,
10 seconds in this non-production,
which is weak, we are going to
optimize from there.
But it's still weak because of
the status of cache and so on.
Just focus on BUFFERS and then
you can understand it.
Again, there are exceptions, I
agree, such as we discussed with
RLS and so on.
It's possible that your particular
case is CPU-intensive, but
these are exclusions from the main
rule.
Absolute majority of cases, you
will be dealing with BUFFERS
during optimization, right?
So congrats with Postgres 18 change.
And I also want to thank Andrei
Lepikhov for this blog post,
which is very welcome.
I think we need more posts about
the importance of I/O Metrics
at various levels, like 1 query
level, micro level, or macro
level like pg_stat_statements, pg_stat_kcache.
So yeah, I hope this topic will
grow and be more popular, this
methodology.
Michael: Yeah, agreed.
Another thank you to the people
involved in getting this committed
to Postgres 18 and big thanks oh
and I think this might be our
first episode since we both got
Postgres Contributor status Nikolay
so congratulations to you and thank
you to whoever nominated
us or whatever the process is there.
Thank you to everyone involved.
Nikolay: Good, see you next time.
Michael: See you soon, bye.
