public inbox for [email protected]
help / color / mirror / Atom feedFrom: Haibo Yan <[email protected]>
To: Lukas Fittl <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: pg_buffercache: Add per-relation summary stats
Date: Mon, 16 Mar 2026 21:21:22 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com>
References: <CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com>
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 <[email protected]> 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>
view thread (27+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: pg_buffercache: Add per-relation summary stats
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox