Michael: Hello and welcome to Postgres.FM, a weekly show about
all things PostgreSQL.
I am Michael, founder of pgMustard, and this is Nikolay, founder
of Postgres.AI.
Hey Nikolay, how's it going?
Nikolay: Hello, Michael.
Going great.
How are you?
Michael: Yeah, good.
Thank you.
So what are we talking about this week?
Nikolay: Let's talk about why we can have writes on replicas.
Michael: Yeah, or the thing I've seen people get confused by
is why they can get writes even on the primary for SELECT queries,
like for read queries.
But yeah, I like your framing.
Nikolay: Yeah, it can happen on primary as well.
Yeah.
I mean, this surprise can happen on the primary as well.
Michael: And specifically where people might notice this is seeing
if they get buffers in query plans like shared
Nikolay: dirty.
Exactly.
Michael: But also, I think the place I've seen people spot it,
at least when they mention it in blog posts, is pg_stat_statements
or monitoring tools that are looking at pg_stat_statements
and seeing that some of the top offending queries for buffers
dirtied could even be SELECT queries, and that really surprised
them.
So it's kind of one of those surprising moments where you think,
ah, is something badly wrong?
And then you look into it and realize maybe not.
Nikolay: Yeah, actually, to be fully clear, writes can happen
during SELECTs because of temporary files as well, right?
And if you think about writes to disk.
But this is pretty straightforward.
We see, again, in pg_stat_statements, we can see it as
well as temporary bytes or blocks written and read and...
Michael: Yeah, is it local or is it temp?
I think it's local.
Nikolay: No, local is different.
I always forget what local
Michael: is.
No, you're right.
You're right.
Local is for temporary tables, that's why I get confused.
Local is for temporary tables and temporary objects.
But temp is for like, if for example, sort or hash spills to
disk because it's too big for work_mem or work_mem times hash
mem multiplier then you get temp blocks read pretend blocks written
But I think what people are spotting is the dirtied and being
like, wait, that should...
Dirtied means, you know, an Update or Delete, normally.
Nikolay: It's so confusing, right?
I'm checking pg_stat_statements documentation and for local blocks,
read, dirtied, written, and local block read time, write time.
It only says total number of local blocks read by statement.
Total number of local blocks, like everyone understands what
local block is.
Well, local block, okay, I think you're right.
It's a block.
I also saw like the more and more I deal with it in Postgres,
almost how many years, like 20 years, right?
The more I dislike the fact that we call operations blocks.
These are operations, not blocks.
Because if we say 10 blocks written, it's actually 10 block writes.
Maybe it's the same block which we've written 10 times, right?
Or rare.
So it should be block reads, block writes, block...
Dirty, I don't know the proper word how to say.
Dirtying...
Dirtyings, I don't know.
But anyway, here, of course I think it would be great to have
understanding what local block is.
Local block, I think, it's, again, operation.
Local block operation, it's what is caused when we work with
temporary tables, which I always try to avoid because of different
reasons.
Maybe we should talk 1 day about temporary tables.
But temporary tables, temporary blocks, temporary blocks is not
about temporary tables, but rather about temporary files which
can happen during execution of a Query dealing with normal tables
anytime because work memory is not enough, right?
So again, this is easy.
This part is easy.
If we see some SELECT or Update or anything is producing a lot
of temporary blocks, read or written.
We need to just consider raising work memory or optimize Queries
so it deals with lower volumes, smaller volumes of data, right?
For example, I don't know, like, just increase selectivity of
our, I don't know, it's a separate topic anyway.
Let's just point that writes can happen because of temporary
files.
Of course, SELECTs can produce temporary files because work_mem
is not enough.
That's it.
But then sometimes we have huge work memory, definitely, which
is enough, and SELECT is still writing to disk.
Or, well, technically dirty, blocks dirtied.
These operations, I will keep calling these operations, not data.
These events, these operations can happen during SELECTs.
And if it's just dirtied, it not necessarily goes to disk immediately,
because this is the work for Checkpointer and background writer.
But it's already like, it's not good.
Like SELECT is producing some writes to the buffer pool, to shared_buffers
area.
And of course, if it's written, it's even worse, because I'm
pretty sure this makes SELECT also slow similarly as it happens
when it creates temporary file and writes to it right
Michael: yeah good point although I think in the 2 cases that
we're going to be talking about, actually, no, no, there could
be a lot of them.
I was thinking, I was thinking it's generally only in a lot of
the cases I've seen, it's only 1 block at a time.
But actually, you could easily have a scan doing a lot of these.
So yeah, could be a lot of data.
Nikolay: Yeah, and actually to be even like absolutely precise,
a block written not necessarily goes to disk because this is
written to the page cache.
And then like pdflush or something, this is already about Linux,
it should write to disk at some point.
Written doesn't necessarily mean that it goes to disk.
And I think to understand that, we should use different extension.
Because pg_stat_statements is great, it's available everywhere.
Because it's like, I think this is the most popular extension,
should be at least among observers.
And there is opinion that it should go to core at some point.
It should stop being an extension, especially after query ID
propagated to pg_stat_activity to auto_explain, right?
Definitely, pg_stat_statements should be in core.
But what I'm saying is that there is a KCache which extends
the predecessor statements to allow users to see metrics related
to actual physical resources such as CPU, system CPU, and also
disk I/O.
Real disk writes, real disk reads how many bytes are written,
how many bytes are read for each query ID, which is great.
I think We talked that finally buffers are committed to EXPLAIN
analyze.
By default, you run EXPLAIN ANALYZE in Postgres 18 unless it's
reverted.
I hope it won't be reverted.
In Postgres 18, EXPLAIN ANALYZE will always include BUFFERS in
output, which is great victory.
We talked about it a couple of years on this podcast, I think
almost since the very beginning.
And I was thinking what topic to choose next to complain about
occasionally, regularly.
Michael: Or maybe campaign rather than complaint.
Nikolay: Campaign, okay.
Michael: More positive, yeah.
Nikolay: Right, so what kind of campaign to launch after this?
Like, I was happy, but I was like, oh, I will be missing us to
talk about buffers and explain the lies and so on.
I chose this topic.
I think we should focus on all managed Postgres platform builders
and advertise to include pg_wait_sampling and pg_stat_kcache
to extensions.
Because this is super important to have them both to be able
to do proper query analysis in different aspects.
pg_stat_statements is great.
It has a lot of metrics.
It keeps growing in terms of number of metrics, but it doesn't
have what pg_stat_kcache and pg_wait_sampling provide.
So we need those extensions on each serious database, especially
heavily loaded, and pg_stat_kcache is great here.
And this would allow us to see, oh, actually this query being
SELECT caused actual physical disk writes.
Michael: Yeah, I'm going to have a think.
If we only get 1 campaign, I'm not sure that's mine, but maybe
I like it.
It's a good idea.
Nikolay: Okay, let's please consider this.
I'm definitely launching campaign at least on my Twitter and
LinkedIn and actually I already launched it and I saw a
reaction from So actually I mentioned that these extensions are
present on all serious setups, which we helped to maintain for
quite some time.
And pg_stat_kcache has been used like in serious databases I'm dealing
with for a very long time, many years.
And I know some platforms use it, but in different countries,
right?
And the only case I see is pg_wait_sampling is available on Google
Cloud SQL.
pg_stat_kcache is not available there, but pg_wait_sampling is,
which is great.
Michael: Are you sure?
Nikolay: I'm sure.
pg_wait_sampling is available, so you can have...
Imagine RDS, they have performance insights, which is great.
But this information is not available through SQL interface.
I'm not sure about CloudWatch, maybe it's available there, but
it's a different API.
I want to see details right in SQL because I have tools to analyze
pg_stat_statements and I want extension to ActiveSession history
analysis and also to physical metrics, which pg_stat_kcache provides.
So Cloud SQL has pg_wait_sampling, but it doesn't have pg_stat_kcache.
Others don't have any of these 2.
And the reaction was from one of Cloud SQL engineers I saw who
promised to think about it.
I mean, it would be great and Cloud SQL could be much better
if they have pg_stat_kcache.
I think they're already much better in terms of observability,
better than RDS because pg_wait_sampling is better than performance
insights.
Usually we combine multiple tools for observability and you go
to for active session history for RDS or Aurora you go to one place,
to their own place, for everything else, you go to another place.
It's not convenient.
So in the case of Google, they have a code, I think, query insights
or something like this.
They have it exposed in their own monitoring interface, but they
also expose it for anyone through SQL interface, which is great,
right?
Through pg_wait_sampling.
If they have pg_stat_kcache, I will be happy, but I also I'm
going to ask others to consider these two extensions.
For example, Crunchy Bridge, Supabase, who else?
Everyone.
I actually think RDS should consider pg_wait_sampling as well,
and pg_stat_kcache as well.
Why not?
So, yeah, it should be just an option.
Users could decide.
So back to our question.
I think if you have pg_stat_kcache, you can see actual writes
happening from queries and you can see SELECTs and you know work
memory is enough, so it's not temporary files.
You can confirm it through pg_stat_statements or also through
individual query execution via EXPLAIN ANALYZE, BUFFERS, and that's
it, you're thinking what's happening, right?
Why SELECT is writing to disk?
Or at least, is it writing to the buffer pool, which is already
quite a surprise, right?
Michael: Yeah.
And well, until recently, I knew this was a phenomenon.
And I'd read about it a couple of times seen on the main list
and in a post or two. But I only knew of one of the potential
reasons and the reason I think this came up recently is there
was a good blog post by Alex Jesipow.
I'm not sure how to pronounce that.
Sorry.
Who mentioned a second reason as well.
So the first reason is related to setting of hint bits and maybe
we can go into detail on that one first.
Cause that's the one I've seen most often and it comes up the most.
Do you want to discuss that one first?
Nikolay: Yeah, let's do it
Michael: So why do why do we have hint bits and what are hint bits?
Nikolay: By the way, maybe it's Alex Jesipow but Alex
is from Germany I see and works at Luminovo, which is interesting
because We we work together last year.
I mean with his colleagues.
It's interesting.
I just realized.
It's cool.
Yeah.
Yeah, I'm going to send him this episode for sure.
So we have 2 cases, right?
2 cases.
And I'm not sure which 1 is the easiest.
Let's start with easiest.
Michael: I think Hint Bits is easiest to understand, and it's
most common.
So my understanding of this is when new data is written to a
page, at that moment, Postgres doesn't yet know if that could
be part of a much larger, longer transaction, and we don't yet
know if that transaction is going to commit or get aborted and
rolled back.
So we at that moment cannot set, we cannot say on the data page
that this transaction has been committed and therefore should
be visible to new reads of this page.
So that information is in a separate log once the transaction
does commit.
And that means when you go, if a, if somebody's reading the page,
they need to check which of these row versions has already been
committed for sure.
And if there's any ambiguous, any ones where we don't know yet,
because, but based on these hint bits, based on these 4 different
flags, we can tell what status it is.
Is it unknown?
Is it definitely committed?
Is it already out of date?
Like is it already being replaced?
So if in the case of these reads causing writes, it's a subsequent
read coming along, seeing a new row version that hasn't yet,
that we don't know yet whether it's been committed, checking
the commit log and then setting, like having read it once, it
sets that and dirties the page, writes the page again to wow,
so that any future reads now don't have to check.
Nikolay: Right, yeah, so yeah, I think this can be like invisible,
dead, frozen, right, for tuple and the thing is that the most
interesting part of it that this this write can happen on the
replica which is kind of surprise for people, right?
How come?
Michael: There's a really good, oh, sorry, just to go back to
hint bits, there's a really good page on the Postgres wiki that
describes it really succinctly, and there are 4 hint bits.
xmin committed, xmin aborted, xmax committed, xmax aborted.
Nikolay: Yeah.
So, yeah, that's it actually.
So we just know about this phenomenon and that's it.
It should not be a surprise that it happens.
And subsequently...
Michael: And it's healthy, right?
It's not a sign of anything having gone wrong in healthy like
it will happen Quite often.
Nikolay: Yeah, and in case it is subsequent.
So it already is not causing this, right?
So so it's it's because it's already updated and it's kind of
you can consider it a kind of like dealing with warming up caches,
but it's vice versa because it's writes.
So it's only, this overhead is only present on the first very
call with the same parameters.
Of course, different parameters can cause such writes in different
pages.
Michael: And it's a necessary result of us wanting to be able
to serve things concurrently.
Because of MVCC, we need versions of rows.
And we need to then know which 1 should be visible to which transactions.
So in order to do that, this is a necessary part of that.
Nikolay: Right.
So yeah, let's talk about the second case.
Michael: Yeah, this.
Yeah.
The second was more interesting to me.
Like I hadn't come across this before.
Nikolay: Yeah.
Let's talk about it.
Michael: So in Alex's post, I think you're probably right on
the surname pronunciation, but I'm not going to try it again.
He describes these as page pruning he calls it which I have not
heard it called that before and when I think of pruning I think
my mind naturally goes to for example the mechanism for removing
an empty page at the end of the heap, for example.
Is that also called pruning?
But this is different.
Nikolay: I think it's truncation.
If you talk about removing last page when vacuum does it, it's
truncation.
Michael: That makes sense.
So this is in almost like a, I think you just, before the call
you mentioned it was described as a kind of in-page vacuum.
Yeah.
So it's like.
Nikolay: And it can happen during SELECTs on the fly, like it's
interesting, right?
Which is like also strange.
But if we, if we recall how HOT updates are organized, which
is great, a feature, unfortunately, not available always because
it requires 2 special conditions to be met.
First is we are updating only, we are changing values of the
columns which are not indexed.
And second is there is enough empty space in the same page where
our old tuple is stored.
In this case, so-called hot chains are created, and it can be
multiple versions in the same page of the same tuple.
In this case, what happens when, if we have index scan, we only
know the page and offset for the first.
Indexes are not updated in this case because it's hot update.
This is optimization to fight index write amplification, which
is terrible Postgres MVCC behavior many projects are suffering
from.
When updating 1 row, having many indexes on the table.
We deal with...
We need to update all of indexes, producing a lot of WAL writes
and just making updates slow, heavy, and so on.
In case of hot updates, those 2 conditions I mentioned are met.
Postgres writes only to the same page where tuple is already
stored because there is enough space and it doesn't update indexes
at all because we are changing the value which is not indexed.
And it produces a new version tuple, new raw version inside the
same page, creating chain, it can be new, new, new, and when
we have index scan, index scan points to the first version in
the page, and then it's quick to jump between versions and find
the actual 1 inside the same page.
Michael: 1 crucial thing that I think becomes important later
is that that chain information is stored in the header of the
page, whereas the row, like the data from the row version is
stored at the end of the page.
Nikolay: Right, right.
This is true.
Yes, it's so.
And if we already have old versions, at some point they need
to be deleted.
It can happen during vacuuming or it can happen earlier if during
dealing with this page Postgres says, okay, we can clean up old
versions right now, why not?
And can happen during SELECT as well, which is very strange.
Yeah.
Michael: Well, it's so cool.
But I think, again, there's like a condition where it will only
do this if there is not that much space left on the page.
So I think the number is 10% of the page is left in terms of
free space.
And there's some subtlety around if you've changed fill factor.
Nikolay: And so which means that this is an effort to maintain
hotness of updates further, because if without this, we would
need to go to another page.
And this will definitely lead to updating all indexes, right?
Michael: Yes.
But I think the optimization of not doing it unless the page
is quite full means we don't have to do it that often.
So for example, if we have maybe like say a really quick 10-15
updates of the same row, and they all fit on the same page, and
then we're having reads of that row in between, we're not cleaning
up each version each time we do a read, we're waiting until we
get full and then doing it in 1 go.
So I think it's quite a pretty smart optimization.
Nikolay: It's a kind of trade-off, balance between 2 worst situations
and so on.
So We don't do it too often, but also we try not to allow this
tuple to drift to another page, which would cause the need to
update indexes because indexes point to the old page.
This is quite interesting.
Again this can happen during SELECT and yeah but it can happen
only on the primary right yes or no it's my guess I don't know
honestly why because because can happen on replica
Michael: yeah well I actually I don't know well let us know in
the comments.
Nikolay: Yeah, this is an interesting question, because if it's
happening on replica, it means we have different, very different
content of pages on both replica and primary.
vacuum happening on a replica, please no, because this changes
the content of page.
I cannot understand how page content should be synchronized in
terms of
Michael: tuples.
It's physical replica, yeah.
Nikolay: Yeah, so it should happen only on the primary because...
Michael: Okay, yeah.
Nikolay: And the replica should just get this content.
Now, with Hint Bits it's different.
Hint Bits gets additional information and it doesn't change how
tuples are stored inside the page.
This thing is changing.
It's cleaning up the space for new tuples.
So it should happen on the primary, I think.
Michael: That makes sense.
Yeah.
Logically on a physical replica.
Nikolay: Right.
Okay.
Well, good.
Michael: There's this 1 more open question.
Yeah.
I saw when this blog, this latest blog post was shared to Reddit
there was a really interesting comment that didn't that no 1
has replied to and there's almost no activity on it but somebody
posed the question I think it's a good 1 whether there might
be a third possible cause of this.
And that's, so it's more of a question to the listeners because
I'm not actually sure, I don't know myself and haven't had, you
know, to confirm for sure.
But there's a PD_ALL_VISIBLE flag on pages.
And that might be a third way if if SELECTs can flip that based
on visibility of all the rows in the or the tuples in the page
then that might be a third case but I don't I don't know if SELECTs
can so yeah again if you know I'll be really interested to hear
Nikolay: yeah yeah so I guess that's it right yeah so yeah
Michael: so yeah great great blog post there's also an old 1
from Nikolay Sivko on the Okmeter blog that I'll share So there's
a few articles to read more about this.
Nikolay: Correction, Nikolay currently has a new startup which
is called Coroot.
Yes.
Yeah, so then Okmeter is his old startup.
And Peter Zaitsev from, like, founder of Percona joined Nikolay
recently, not very long ago, and I'm like, Coroot is a great
project.
But this, I guess this blog post was created while Nikolay was
working on the previous project and just started observing the
writes happening from SELECTs and was very surprised and decided
to blog about it.
Michael: Yeah, well it was a monitoring project as well, right?
Nikolay: Yeah, it had very good Postgres related features, which
I guess in some practices were inherited by Coroot.
But Coroot is amazing, just a couple of words about it.
If you, if you like flame graphs, Coroot is, can show you dynamically,
like imagine you have a dashboard, you choose time for Postgres
and you see flame graphs but hanging down so they are like 180
degrees rotated or mirrored, right?
Horizontal mirrored.
You can see details like If you have debug symbols installed,
I think everyone should have debug symbols always installed.
You see deep details what's happening, where time is spent.
Dynamic diagnostics like this, it's crazy.
I think probably I should add this to recommendations for Postgres
platform builders, managed Postgres platform builders, because
to consider Coroot is also like a good thing for observability.
Michael: It's open source, right?
Nikolay: It has open source.
It has, I think, it's like an open core model.
I'm not sure.
But the main thing is definitely open source.
Again, I'm not sure.
Michael: Thanks so much, Nikolay.
Thanks everyone for listening.
We'll catch you next week.
Nikolay: Thank you, Michael.
See you soon.