Return of the BUFFERS

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?

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...

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.

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?

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

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

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.

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.

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.

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

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.

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.

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?

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...

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.

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.

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.

So yeah, there's some subtlety, but yeah, that's it essentially.

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.

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.

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?

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.

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.

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

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.