Re: pg_buffercache: Add per-relation summary stats - Mailing list pgsql-hackers
| From | Haibo Yan |
|---|---|
| Subject | Re: pg_buffercache: Add per-relation summary stats |
| Date | |
| Msg-id | 809814F5-0CD1-4FA1-83C4-E04A27633664@gmail.com Whole thread Raw |
| In response to | pg_buffercache: Add per-relation summary stats (Lukas Fittl <lukas@fittl.com>) |
| List | pgsql-hackers |
Hi Lukas,
I have read the patch, and I have a few questions/comments while going through it:
Could this use RelFileLocator plus ForkNumber instead of open-coding BufferRelStatsKey? That seems closer to existing PostgreSQL abstractions for physical relation identity.
I wonder whether pg_buffercache_relation_stats() is the best name here. The function is really aggregating by relation file identity plus fork, and it is producing a summary of the current buffer contents rather than what many readers might assume from “relation stats”. Would something with summary be clearer than stats?
Why are OUT relforknumber and OUT relfilenode exposed as int2 and oid respectively? Internally these are represented as ForkNumber and RelFileNumber, so I wonder whether the SQL interface should reflect that more clearly, or at least whether the current choice should be explained.
The comment says, “Hash key for pg_buffercache_relation_stats — groups by relation identity”, but that seems imprecise. It is really grouping by relfilenode plus fork, i.e. physical relation-file identity rather than relation identity in a more logical sense.
Is PARALLEL SAFE actually desirable here, as opposed to merely technically safe? A parallel query could cause multiple workers to perform full shared-buffer scans independently, which does not seem obviously desirable for this kind of diagnostic function.
Best regards,
Haibo Yan
On Feb 28, 2026, at 3:58 PM, Lukas Fittl <lukas@fittl.com> wrote:Hi,
See attached a patch that implements a new function,
pg_buffercache_relation_stats(), which returns per-relfilenode
statistics on the number of buffers, how many are dirtied/pinned, and
their avg usage count.
This can be used in monitoring scripts to know which relations are
kept in shared buffers, to understand performance issues better that
occur due to relations getting evicted from the cache. In our own
monitoring tool (pganalyze) we've offered a functionality like this
based on the existing pg_buffercache() function for a bit over a year
now [0], and people have found this very valuable - but it doesn't
work for larger database servers.
Specifically, performing a query that gets this information can be
prohibitively expensive when using large shared_buffers, and even on
the default 128MB shared buffers there is a measurable difference:
postgres=# 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 = 2659;
relfilenode | reltablespace | reldatabase | relforknumber | buffers |
buffers_dirty | buffers_pinned | usagecount_avg
-------------+---------------+-------------+---------------+---------+---------------+----------------+--------------------
2659 | 1663 | 5 | 0 | 8 |
0 | 0 | 5.0000000000000000
2659 | 1663 | 1 | 0 | 7 |
0 | 0 | 5.0000000000000000
2659 | 1663 | 229553 | 0 | 7 |
0 | 0 | 5.0000000000000000
(3 rows)
Time: 20.991 ms
postgres=# SELECT * FROM pg_buffercache_relation_stats() WHERE
relfilenode = 2659;
relfilenode | reltablespace | reldatabase | relforknumber | buffers |
buffers_dirty | buffers_pinned | usagecount_avg
-------------+---------------+-------------+---------------+---------+---------------+----------------+----------------
2659 | 1663 | 1 | 0 | 7 |
0 | 0 | 5
2659 | 1663 | 229553 | 0 | 7 |
0 | 0 | 5
2659 | 1663 | 5 | 0 | 8 |
0 | 0 | 5
(3 rows)
Time: 2.912 ms
With the new function this gets done before putting the data in the
tuplestore used for the set-returning function.
Thanks,
Lukas
[0]: https://pganalyze.com/blog/tracking-postgres-buffer-cache-statistics
--
Lukas Fittl
<v1-0001-pg_buffercache-Add-pg_buffercache_relation_stats-.patch>
pgsql-hackers by date: