Re: pg_buffercache: Add per-relation summary stats

On Thu, Jun 18, 2026 at 7:10 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Fri, Mar 27, 2026 at 6:59 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
> > The main argument here seems to be the performance, and the initial
> > message demonstrates a 10x speedup (2ms vs. 20ms) on a cluster with
> > 128MB shared buffers. Unless I misunderstood what config it uses.
>
> So, my opinion on this point is that the results Lukas shows later in
> the thread are compelling. The query takes 13s and writes 1.2GB for
> what should be a trivial monitoring query. That seems like it's pretty
> clearly enough overhead to be a problem for a monitoring query. The
> problem isn't even just that you can't afford to wait 13s for a query
> you run every 10m -- it's that the query itself is consuming enough
> system resources to skew your other monitoring. For example, if you're
> monitoring your system load average or CPU usage or disk usage over
> time, you're going to see spikes when this query runs. That's not the
> worst thing that has ever happened to anyone, but it's definitely not
> great, and I can totally understand someone not being willing to incur
> that much overhead. I don't believe we should accept the argument that
> this patch doesn't save enough to matter; I think it does.

+1
With the v2 patch, I was able to reproduce an even more drastic
difference, ~25x, on a system with 400GB shared_buffers with only
56.5% filled.

SELECT buffers_used,
buffers_unused,
buffers_dirty,
buffers_pinned,
round(usagecount_avg::numeric, 2) AS avg_usagecount,
pg_size_pretty(buffers_used * 8192::bigint) AS used_size,
pg_size_pretty(buffers_unused * 8192::bigint) AS free_size,
pg_size_pretty(buffers_dirty * 8192::bigint) AS dirty_size
FROM pg_buffercache_summary();
buffers_used | buffers_unused | buffers_dirty | buffers_pinned |
avg_usagecount | used_size | free_size | dirty_size
--------------+----------------+---------------+----------------+----------------+-----------+-----------+------------
29595726 | 22833074 | 0 | 0 |
3.15 | 226 GB | 174 GB | 0 bytes

WITH pg_buffercache_relation_stats AS (
SELECT relfilenode, reltablespace, reldatabase, relforknumber,
COUNT(*) AS buffers,
COUNT(*) FILTER (WHERE isdirty) AS buffers_dirty,
COUNT(*) FILTER (WHERE pinning_backends>0) AS buffers_pinned,
AVG(usagecount) AS usagecount_avg
FROM pg_buffercache
WHERE reldatabase IS NOT NULL
GROUP BY 1, 2, 3, 4
)
SELECT * FROM pg_buffercache_relation_stats WHERE relfilenode = 22996;
relfilenode | reltablespace | reldatabase | relforknumber | buffers |
buffers_dirty | buffers_pinned | usagecount_avg
-------------+---------------+-------------+---------------+---------+---------------+----------------+--------------------
22996 | 1663 | 22942 | 1 | 21 |
0 | 0 | 1.5714285714285714
22996 | 1663 | 22942 | 2 | 3 |
0 | 0 | 5.0000000000000000
22996 | 1663 | 22942 | 0 | 75901 |
0 | 0 | 2.0469690781412630
(3 rows)

Time: 9664.117 ms (00:09.664)

SELECT * FROM pg_buffercache_relations() WHERE
relfilenode = 22996;
relfilenode | reltablespace | reldatabase | relforknumber | buffers |
buffers_dirty | buffers_pinned | usagecount_avg
-------------+---------------+-------------+---------------+---------+---------------+----------------+--------------------
22996 | 1663 | 22942 | 0 | 75901 |
0 | 0 | 2.046969078141263
22996 | 1663 | 22942 | 2 | 3 |
0 | 0 | 5
22996 | 1663 | 22942 | 1 | 21 |
0 | 0 | 1.5714285714285714
(3 rows)

Time: 388.158 ms

Even though this query runs once every 10 minutes, the resource cost
of a 10-second query is not something we should overlook. For this
level of savings, I think it is worth maintaining a specialized
function.

Here's another way to frame the "is this too specific" question: if
pg_buffercache had shipped as a per-relation view first, and someone
now proposed replacing it with a more generic, reusable per-page
implementation that required SQL-side aggregation at 25x the cost,
would we accept that as an improvement?

>
> > Let's assume it's worth it. I wonder what similar summaries might be
> > interesting for users. I'd probably want to see a per-database summary,
> > especially on a shared / multi-tenant cluster. But AFAICS I can
> > calculate that from the pg_buffercache_relations() result, except that
> > I'll have to recalculate the usagecount.
>
> I think it would be better to return the total usagecount and let the
> caller divide if they want, rather the average.
>
+1

> But more generally, I agree that we don't want something that is
> overly specific to one person's use case. Thinking about how to make a
> function like this useful to as many people as possible is a
> worthwhile activity. I don't know what more we can do that makes
> sense. For instance, we could add a database OID argument that can be
> NULL or the OID of a database and it filters out everything else. I'm
> not sure that would pull its weight -- the big gains are probably
> coming from doing the aggregation using bespoke code, rather than
> filtering out rows beforehand -- but maybe.
>
> On the whole, I'm inclined to think we should accept this. There's
> plenty of cases where it won't save much, and it is also true that it
> would be nice to improve the core infrastructure so that queries like
> this can be better-optimized. But I don't think that's going to happen
> right away, and even when it does happen I bet the savings from a
> patch like this will still be pretty significant. I also believe that
> aggregating the pg_buffercache results by relation is probably a very
> common use case, so it's doesn't seem to me as though there would be
> ten other equally-compelling versions of this.
>

+1
One thing I'd add to the utility discussion. Cheaper sampling opens up
additional usability. If we can sample every minute
instead of every 10 minutes, we can start to see how the cache is churning.
To Dmitry's point about what a user actually does with these numbers,
churn lets us connect query latency spikes to the working set, and tells
us whether shared_buffers is undersized (if the same relation keeps being
paged in and out).

> > One thing we lose by doing ad hoc aggregation (instead of just relying
> > on the regular SQL aggregation operators) is lack of memory limit.
> > There's a simple in-memory hash table, no spilling to disk etc. The
> > simple pg_buffercache view does not have this issue, because the
> > tuplestore will spill to disk after hitting work_mem. Simplehash won't.
> >
> > The entries are ~48B, so there would need to be buffers for ~100k
> > (relfilenode,forknum) combinations to overflow 4MB. It's not very
> > common, but I've seen systems with more relations that this. Would be
> > good to show some numbers showing it's not an issue.
>
> This is a good point, but I'm not sure I believe there's a real issue
> here. It seems as though the kinds of systems where this function is
> likely to be important for performance are probably those with 100GB+
> of shared_buffers, so 12m+ buffers, so ... half a gigabyte? Maybe
> somewhat more with memory allocation overheads and so forth? I feel
> like if you have 100GB of shared_buffers, you probably have work_mem
> set to 1GB+, or at least have that much memory free. And even then you
> only need that if every single shared buffer belongs to a different
> relation, which seems like a thing that will not occur in practice.
>
> Obviously, there are things we could do to limit memory consumption
> here. I think the easiest thing might be to just write out the entire
> hash table in hash value order to a temporary file every time we
> exhaust work_mem, and then do a merge pass over all those temporary
> files at the end. I don't think we can plausibly need more than one
> merge pass to keep memory usage within acceptable limits, and I don't
> think this would need to be a crazy amount of code. But I'm also not
> sure I believe we really need it. The concern about code maintenance
> that has been raised is valid here as it is for all patches, so we
> shouldn't bloat the patch with code that it doesn't really need, and I
> think it's worth considering whether spill-to-disk code falls into
> that category.

The case of 12M+ buffers all from different relations is possible but
seems unlikely to me in reality. I don't have data on this beyond
not having seen such a customer deployment.
Given that, I think building a spill-to-disk solution for it would be
over-engineering to implement. I think erroring out when the
estimated hash size exceeds work_mem is sufficient. We can rely on
the DBA’s call to increase this session’s work_mem for this particular
query to go through without affecting all backends.

We can revisit spill-to-disk when there's field evidence that the
memory requirement is too high. Reaching that point would itself be a
good
problem to have for Postgres in general.

I did some experiments looking at MemoryContextMemAllocated and the
result is very close to what I'm getting with relstats_estimate_space.
LOG:after fill = 90160 bytes, 1062 entries, 52428800 pages ~85B per entry.
LOG:after fill = 83894320 bytes, 1001062 entries, 52428800 pages ~84B per entry.

These are interesting numbers to see but it's hard for us to rely on.
Simplehash has a max load factor of .9 and a min load factor of .45
(after doubling). Given that our entry is 40B, amortizing for load
factor, we are looking at 45B - 89B. The number above seems to fall
in line with .45 load factor.

relstats_estimate_space factors in .9 load factor and pg_nextpower2_64
so it is a good simplehash API to use. It gives an estimate of 80B
per entry which is within 11% of the worst case, 89B. Worst case, we
overshoot work_mem by 11%.

Lukas has been quiet, perhaps busy, on this thread since March 28th.
His patch has a lot of value so I'd like to help move it forward. The
original design and implementation are Lukas's. Attached is the v3
patch with work_mem cap and usagecount_avg changed to
usagecount_total. It uses relstats_estimate_space to estimate the
maximum number of members before erroring out. I also added a test to
validate the memory cap.

-- 2000 tables each with 80B per entry will need at least ~157kB of
work_mem. Expect to fail with 64kB
set work_mem='64kB';
SELECT count(*) FROM pg_buffercache_relations();
ERROR: number of relations exceeds "work_mem" limit
DETAIL: Aggregating buffer cache statistics required more than 64 kB.
HINT: Increase "work_mem" and retry.

-- Plenty of headroom to pass unless there are other tables, beyond
6500, in shared_buffers.
set work_mem='512kB';
SELECT count(*) > 2000 FROM pg_buffercache_relations();
?column?
----------
t
(1 row)