Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Modernize and optimize pg_buffercache_pages()
- 257c8231bf97 19 (unreleased) landed
pg_buffercache: Add pg_buffercache_os_pages
- 4b203d499c61 19 (unreleased) cited
Don't bother to lock bufmgr partitions in pg_buffercache.
- 6e654546fb61 10.0 cited

pg_buffercache: Add per-relation summary stats

Lukas Fittl <lukas@fittl.com> — 2026-02-28T23:58:34Z

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

Re: pg_buffercache: Add per-relation summary stats

Jakub Wartak <jakub.wartak@enterprisedb.com> — 2026-03-02T10:16:40Z

On Sun, Mar 1, 2026 at 12:59 AM 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.
[..]
> (3 rows)
>
> Time: 20.991 ms
[..vs]
>
> Time: 2.912 ms

Hi Lukas, I have glanced at the patch briefly and couldn't find any
issues - patch looks solid, however I'm not sure if e.g. launching whole
NBuffers scan let's say every 5mins doesn't cause latency spikes on the
system? I mean introducing such function seems to invite users to use
pg_buffercache and I'm wondering if such regular pattern doesn't cause
issues? (this is not FUD :), just more like a question based on Your's
obervation)

Also have you quantified what was the breaking point of previous query?
(You wrote "larger database servers", but was that like 128GB+ shared_buffers?
and if so what would be the difference in terms of runtime there -- also
like ~7x?)

-J.

Re: pg_buffercache: Add per-relation summary stats

Lukas Fittl <lukas@fittl.com> — 2026-03-03T02:39:30Z

On Mon, Mar 2, 2026 at 2:16 AM Jakub Wartak
<jakub.wartak@enterprisedb.com> wrote:
> Hi Lukas, I have glanced at the patch briefly and couldn't find any
> issues - patch looks solid, however I'm not sure if e.g. launching whole
> NBuffers scan let's say every 5mins doesn't cause latency spikes on the
> system? I mean introducing such function seems to invite users to use
> pg_buffercache and I'm wondering if such regular pattern doesn't cause
> issues? (this is not FUD :), just more like a question based on Your's
> obervation)

Thanks for taking a look!

I think its the definitely the kind of thing you'd want to have people
opt-in to (we currently only do it when someone enables pg_buffercache
on their own, and allow turning it off completely), but on systems
that experience performance issues due to what's in cache (and have
some CPU capacity to spare), it seems to be helpful more than harmful,
I think. To be clear on the sample size, this is a subset of our user base
where we have experience with this, probably across 50-100 installs,
roughly speaking, of small to medium sized production systems (size as
in shared_buffers).

Also, FWIW, this isn't going not helpful if your cache contents change
completely once a minute, but in practice I think its more the
unexpected effects of e.g. large data loads or background processes
that mess with the cache, where tracking this over time can help you
find the root cause of slowness - we currently run this on a 10 minute
schedule when enabled, and that seems to work in terms of
understanding large swings in cache contents. I think even if you ran
this once an hour it could be helpful, and with the patch would give
you the data you're interested in ("whats in the cache") without
causing a large temporary file to be created.

>
> Also have you quantified what was the breaking point of previous query?
> (You wrote "larger database servers", but was that like 128GB+ shared_buffers?
> and if so what would be the difference in terms of runtime there -- also
> like ~7x?)

We've currently set the default limit of where we measure this with
our tool at 200GB, but that's mainly because the temporary file that
gets written out with pg_buffercache today to do the grouping just
becomes noticeably large.

I'll work on sharing more numbers in the following days for larger
servers, to show the benefit of the patch.

Thanks,
Lukas

--
Lukas Fittl

Re: pg_buffercache: Add per-relation summary stats

Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-03-09T09:15:03Z

Hi,

On Sat, Feb 28, 2026 at 03:58:34PM -0800, Lukas Fittl 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:

Thanks for the patch! 

A few comments:

=== 1

+typedef struct
+{
+   RelFileNumber relfilenumber;
+   Oid         reltablespace;
+   Oid         reldatabase;
+   ForkNumber  forknum;
+} BufferRelStatsKey;

What about making use of RelFileLocator (instead of 3 members relfilenumber,
reltablespace and reldatabase)?

=== 2

+  <para>
+   The <function>pg_buffercache_relation_stats()</function> function returns a
+   set of rows summarizing the state of all shared buffers, aggregated by
+   relation and fork number.  Similar and more detailed information is
+   provided by the <structname>pg_buffercache</structname> view, but
+   <function>pg_buffercache_relation_stats()</function> is significantly
+   cheaper.
+  </para>

I'm not 100% sure about the name of the function since the stats are "reset"
after a rewrite. What about pg_buffercache_relfilenode or 
pg_buffercache_aggregated?

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: pg_buffercache: Add per-relation summary stats

Haibo Yan <tristan.yim@gmail.com> — 2026-03-17T04:21:22Z

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>

Re: pg_buffercache: Add per-relation summary stats

Masahiko Sawada <sawada.mshk@gmail.com> — 2026-03-24T19:09:13Z

Hi Lukas,

On Sat, Feb 28, 2026 at 3:59 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.

Thank you for the proposal!

Paul A Jungwirth, Khoa Nguyen, and I reviewed this patch through the
Patch Review Workshop, and I'd like to share our comments.

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

Overall, we find that the proposed feature is useful. The proposed way
is much cheaper, especially when the number of per-relation stats is
not large.

Here are review comments on the v1 patch:

---
-   pg_buffercache--1.5--1.6.sql pg_buffercache--1.6--1.7.sql
+   pg_buffercache--1.5--1.6.sql pg_buffercache--1.6--1.7.sql \
+   pg_buffercache--1.7--1.8.sql

Since commit 4b203d499c6 bumped the version from 1.6 to 1.7 last
November, we think we don't need to bump the version again for this new
feature.

---
+/*
+ * Hash key for pg_buffercache_relation_stats — groups by relation identity.
+ */
+typedef struct
+{
+   RelFileNumber relfilenumber;
+   Oid         reltablespace;
+   Oid         reldatabase;
+   ForkNumber  forknum;
+} BufferRelStatsKey;
+
+/*
+ * Hash entry for pg_buffercache_relation_stats — accumulates per-relation
+ * buffer statistics.
+ */
+typedef struct
+{
+   BufferRelStatsKey key;      /* must be first */
+   int32       buffers;
+   int32       buffers_dirty;
+   int32       buffers_pinned;
+   int64       usagecount_total;
+} BufferRelStatsEntry;

Can we move these typedefs above function prototypes as other typedefs
are defined there?

---
+   relstats_hash = hash_create("pg_buffercache relation stats",
+                               128,
+                               &hash_ctl,
+                               HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);

It might be worth considering simplehash.h for even better performance.

---
+   while ((entry = (BufferRelStatsEntry *) hash_seq_search(&hash_seq)) != NULL)
+   {
+       if (entry->buffers == 0)
+           continue;
+

We might want to put CHECK_FOR_INTERRUPTS() here too as the number of
entries can be as many as NBuffers in principle.

---
We've discussed there might be room for improvement in the function
name. For example, pg_buffercache_relations instead of
pg_buffercache_relation_stats might be a good name, since everything
in this module
is stats.  if we drop "_stats" then "relation" should be plural, to
match other functions in the module ("pages", "os_pages",
"numa_pages", "usage_counts").

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Re: pg_buffercache: Add per-relation summary stats

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2026-03-25T06:24:24Z

On Wed, Mar 25, 2026 at 12:40 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> Hi Lukas,
>
> On Sat, Feb 28, 2026 at 3:59 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.
>
> Thank you for the proposal!
>
> Paul A Jungwirth, Khoa Nguyen, and I reviewed this patch through the
> Patch Review Workshop, and I'd like to share our comments.
>
> >
> > 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.
>
> Overall, we find that the proposed feature is useful. The proposed way
> is much cheaper, especially when the number of per-relation stats is
> not large.
>
> Here are review comments on the v1 patch:
>
> ---
> -   pg_buffercache--1.5--1.6.sql pg_buffercache--1.6--1.7.sql
> +   pg_buffercache--1.5--1.6.sql pg_buffercache--1.6--1.7.sql \
> +   pg_buffercache--1.7--1.8.sql
>
> Since commit 4b203d499c6 bumped the version from 1.6 to 1.7 last
> November, we think we don't need to bump the version again for this new
> feature.
>
> ---
> +/*
> + * Hash key for pg_buffercache_relation_stats — groups by relation identity.
> + */
> +typedef struct
> +{
> +   RelFileNumber relfilenumber;
> +   Oid         reltablespace;
> +   Oid         reldatabase;
> +   ForkNumber  forknum;
> +} BufferRelStatsKey;
> +
> +/*
> + * Hash entry for pg_buffercache_relation_stats — accumulates per-relation
> + * buffer statistics.
> + */
> +typedef struct
> +{
> +   BufferRelStatsKey key;      /* must be first */
> +   int32       buffers;
> +   int32       buffers_dirty;
> +   int32       buffers_pinned;
> +   int64       usagecount_total;
> +} BufferRelStatsEntry;
>
> Can we move these typedefs above function prototypes as other typedefs
> are defined there?
>
> ---
> +   relstats_hash = hash_create("pg_buffercache relation stats",
> +                               128,
> +                               &hash_ctl,
> +                               HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
>
> It might be worth considering simplehash.h for even better performance.
>
> ---
> +   while ((entry = (BufferRelStatsEntry *) hash_seq_search(&hash_seq)) != NULL)
> +   {
> +       if (entry->buffers == 0)
> +           continue;
> +
>
> We might want to put CHECK_FOR_INTERRUPTS() here too as the number of
> entries can be as many as NBuffers in principle.
>
> ---
> We've discussed there might be room for improvement in the function
> name. For example, pg_buffercache_relations instead of
> pg_buffercache_relation_stats might be a good name, since everything
> in this module
> is stats.  if we drop "_stats" then "relation" should be plural, to
> match other functions in the module ("pages", "os_pages",
> "numa_pages", "usage_counts").

I know we already have a couple of hand-aggregation functions but I am
hesitant to add more of these. Question is where do we stop? For
example, the current function is useless if someone wants to find the
parts of a relation which are hot since it doesn't include page
numbers. Do we write another function for the same? Or we add page
numbers to this function and then there's hardly any aggregation
happening. What if somebody wanted to perform an aggregation more
complex than just count() like average number of buffers per relation
or distribution of relation buffers in the cache, do they write
separate functions?

Another problem is the maintenance cost these functions bring. For
example, with the resizable shared buffer project we have another
function to stress test.

Looking at the function, I see it uses a hash table to aggregate the
data. To some extent it's duplicating the functionality we already
have - aggregates using hashing. Are we going to duplicate
functionality everywhere we require aggregation on top of a system
function? These functions will then be missing any optimizations we do
to hash aggregation in future. Can we instead investigate the reason
the aggregation on top of pg_buffercache output requires so much more
time than doing it in the function and fix that as much as we can? I
know some slowness will come from tuplestore APIs, tuple formation and
deformation but I won't expect it to be 10 times slower.

-- 
Best Wishes,
Ashutosh Bapat

Re: pg_buffercache: Add per-relation summary stats

Lukas Fittl <lukas@fittl.com> — 2026-03-25T06:46:25Z

Hi Ashutosh,

On Tue, Mar 24, 2026 at 11:24 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
> I know we already have a couple of hand-aggregation functions but I am
> hesitant to add more of these. Question is where do we stop? For
> example, the current function is useless if someone wants to find the
> parts of a relation which are hot since it doesn't include page
> numbers. Do we write another function for the same? Or we add page
> numbers to this function and then there's hardly any aggregation
> happening. What if somebody wanted to perform an aggregation more
> complex than just count() like average number of buffers per relation
> or distribution of relation buffers in the cache, do they write
> separate functions?

I think the problem this solves for, which is a very common question I
hear from end users, is "how much of this table/index is in cache" and
"was our query slow because the cache contents changed?".

It can't provide a perfect answer to all questions regarding what's in
the cache (i.e. it won't tell you which part of the table is cached),
but its in line with other statistics we do already provide in
pg_stat_user_tables etc., which are all aggregate counts, not further
breakdowns.

Its also a reasonable compromise on providing something usable that
can be shown on dashboards, as I've seen in collecting this
information using the existing methods from small production systems
in practice over the last ~1.5 years.

> Another problem is the maintenance cost these functions bring. For
> example, with the resizable shared buffer project we have another
> function to stress test.

Can you expand how your testing would be impacted? I hear you on not
adding many unnecessary functions, but the basic paradigm of how it
iterates over buffers here is very similar to the other functions in
pg_buffercache, its just shifting the aggregation to be at a different
level.

> Looking at the function, I see it uses a hash table to aggregate the
> data. To some extent it's duplicating the functionality we already
> have - aggregates using hashing. Are we going to duplicate
> functionality everywhere we require aggregation on top of a system
> function? These functions will then be missing any optimizations we do
> to hash aggregation in future. Can we instead investigate the reason
> the aggregation on top of pg_buffercache output requires so much more
> time than doing it in the function and fix that as much as we can? I
> know some slowness will come from tuplestore APIs, tuple formation and
> deformation but I won't expect it to be 10 times slower.

I don't think this is fixable outside the function, and I'd be
surprised if you could get comparable performance, unless you had an
extreme case where it was < 100 buffers per relation. There are just
too many layers involved where we'd keep the full set of buffer
entries vs the grouped version. I'm happy to be convinced otherwise,
but I won't be the one pushing forward that effort myself.

Thanks,
Lukas

-- 
Lukas Fittl

Re: pg_buffercache: Add per-relation summary stats

Lukas Fittl <lukas@fittl.com> — 2026-03-25T06:52:13Z

Hi Haibo,

Thanks for your review!

On Mon, Mar 16, 2026 at 9:21 PM Haibo Yan <tristan.yim@gmail.com> wrote:
> Could this use RelFileLocator plus ForkNumber instead of open-coding BufferRelStatsKey? That seems closer to existing PostgreSQL abstractions for physical relation identity.

Yes, that was noted by other reviewers as well, and makes sense.

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

Per the most recent feedback, I'll rename this to
"pg_buffercache_relations" for now.

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

This is consistent with how pg_buffercache_pages represents them - I
think those are the correct mappings of the int ForkNumber (which we
know to be small in practice) and RelFileNumber is a typedef of Oid.

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

Good point. I'll adapt this to "groups by relation file" for now.

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

I see your point, but I don't think a parallel plan would happen in
practice when just the function is being queried. Since other
pg_buffercache functions are also PARALLEL SAFE, I'll keep this as is
for now - if we want to adjust it we should be consistent I think.

Thanks,
Lukas

-- 
Lukas Fittl

Re: pg_buffercache: Add per-relation summary stats

Lukas Fittl <lukas@fittl.com> — 2026-03-25T06:54:18Z

Hi Bertrand,

Thanks for your review, and sorry it took me a while to get to it -
other patches took more of my attention unexpectedly.

On Mon, Mar 9, 2026 at 2:15 AM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:
> === 1
>
> +typedef struct
> +{
> +   RelFileNumber relfilenumber;
> +   Oid         reltablespace;
> +   Oid         reldatabase;
> +   ForkNumber  forknum;
> +} BufferRelStatsKey;
>
> What about making use of RelFileLocator (instead of 3 members relfilenumber,
> reltablespace and reldatabase)?

Agreed, and noted by others later too - will adjust.

> +  <para>
> +   The <function>pg_buffercache_relation_stats()</function> function returns a
> +   set of rows summarizing the state of all shared buffers, aggregated by
> +   relation and fork number.  Similar and more detailed information is
> +   provided by the <structname>pg_buffercache</structname> view, but
> +   <function>pg_buffercache_relation_stats()</function> is significantly
> +   cheaper.
> +  </para>
>
> I'm not 100% sure about the name of the function since the stats are "reset"
> after a rewrite. What about pg_buffercache_relfilenode or
> pg_buffercache_aggregated?

I'll gone with "pg_buffercache_relations" for now in v2, but I could
also see "pg_buffercache_relfilenodes" making sense, if we wanted to
be more clear about the fact that these are physical relation files,
not logical relations.

Thanks,
Lukas

-- 
Lukas Fittl

Re: pg_buffercache: Add per-relation summary stats

Lukas Fittl <lukas@fittl.com> — 2026-03-25T07:29:20Z

Hi Masahiko-san, Paul and Khoa,

Thanks for the review!

On Tue, Mar 24, 2026 at 12:09 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> ---
> -   pg_buffercache--1.5--1.6.sql pg_buffercache--1.6--1.7.sql
> +   pg_buffercache--1.5--1.6.sql pg_buffercache--1.6--1.7.sql \
> +   pg_buffercache--1.7--1.8.sql
>
> Since commit 4b203d499c6 bumped the version from 1.6 to 1.7 last
> November, we think we don't need to bump the version again for this new
> feature.

Makes sense, adjusted.

> Can we move these typedefs above function prototypes as other typedefs
> are defined there?

Makes sense, done.

> +   relstats_hash = hash_create("pg_buffercache relation stats",
> +                               128,
> +                               &hash_ctl,
> +                               HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
>
> It might be worth considering simplehash.h for even better performance.

Good point, adjusted.

> +   while ((entry = (BufferRelStatsEntry *) hash_seq_search(&hash_seq)) != NULL)
> +   {
> +       if (entry->buffers == 0)
> +           continue;
> +
>
> We might want to put CHECK_FOR_INTERRUPTS() here too as the number of
> entries can be as many as NBuffers in principle.

Sure, that makes sense.

> We've discussed there might be room for improvement in the function
> name. For example, pg_buffercache_relations instead of
> pg_buffercache_relation_stats might be a good name, since everything
> in this module
> is stats.  if we drop "_stats" then "relation" should be plural, to
> match other functions in the module ("pages", "os_pages",
> "numa_pages", "usage_counts").

I've renamed this to "pg_buffercache_relations", though per Bertrand's
earlier email, I could also see that it makes sense to incorporate the
fact more clearly that we're returning physical relfilenodes, not
logical relations.

See attached v2 that incorporates the review feedback.

Thank you all for reviewing!

Thanks,
Lukas

--
Lukas Fittl

Re: pg_buffercache: Add per-relation summary stats

Masahiko Sawada <sawada.mshk@gmail.com> — 2026-03-25T16:48:52Z

On Tue, Mar 24, 2026 at 11:47 PM Lukas Fittl <lukas@fittl.com> wrote:
>
> Hi Ashutosh,
>
> On Tue, Mar 24, 2026 at 11:24 PM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> > I know we already have a couple of hand-aggregation functions but I am
> > hesitant to add more of these. Question is where do we stop? For
> > example, the current function is useless if someone wants to find the
> > parts of a relation which are hot since it doesn't include page
> > numbers. Do we write another function for the same? Or we add page
> > numbers to this function and then there's hardly any aggregation
> > happening. What if somebody wanted to perform an aggregation more
> > complex than just count() like average number of buffers per relation
> > or distribution of relation buffers in the cache, do they write
> > separate functions?
>
> I think the problem this solves for, which is a very common question I
> hear from end users, is "how much of this table/index is in cache" and
> "was our query slow because the cache contents changed?".
>
> It can't provide a perfect answer to all questions regarding what's in
> the cache (i.e. it won't tell you which part of the table is cached),
> but its in line with other statistics we do already provide in
> pg_stat_user_tables etc., which are all aggregate counts, not further
> breakdowns.
>
> Its also a reasonable compromise on providing something usable that
> can be shown on dashboards, as I've seen in collecting this
> information using the existing methods from small production systems
> in practice over the last ~1.5 years.

Regarding the proposed statistics, I find them reasonably useful for
many users. I'm not sure we need to draw a strict line on what belongs
in the module. If a proposed function does exactly what most
pg_buffercache users want or are already writing themselves, that is
good enough motivation to include it.

I think pg_visibility is a good precedent here. In that module, we
have both pg_visibility_map() and pg_visibility_map_summary(), even
though we can retrieve the exact same results as the latter by simply
using the former:

select sum(all_visible::int), sum(all_frozen::int) from
pg_visibility_map('test') ;

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Re: pg_buffercache: Add per-relation summary stats

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2026-03-26T04:21:00Z

On Wed, Mar 25, 2026 at 10:19 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Tue, Mar 24, 2026 at 11:47 PM Lukas Fittl <lukas@fittl.com> wrote:
> >
> > Hi Ashutosh,
> >
> > On Tue, Mar 24, 2026 at 11:24 PM Ashutosh Bapat
> > <ashutosh.bapat.oss@gmail.com> wrote:
> > > I know we already have a couple of hand-aggregation functions but I am
> > > hesitant to add more of these. Question is where do we stop? For
> > > example, the current function is useless if someone wants to find the
> > > parts of a relation which are hot since it doesn't include page
> > > numbers. Do we write another function for the same? Or we add page
> > > numbers to this function and then there's hardly any aggregation
> > > happening. What if somebody wanted to perform an aggregation more
> > > complex than just count() like average number of buffers per relation
> > > or distribution of relation buffers in the cache, do they write
> > > separate functions?
> >
> > I think the problem this solves for, which is a very common question I
> > hear from end users, is "how much of this table/index is in cache" and
> > "was our query slow because the cache contents changed?".
> >
> > It can't provide a perfect answer to all questions regarding what's in
> > the cache (i.e. it won't tell you which part of the table is cached),
> > but its in line with other statistics we do already provide in
> > pg_stat_user_tables etc., which are all aggregate counts, not further
> > breakdowns.
> >
> > Its also a reasonable compromise on providing something usable that
> > can be shown on dashboards, as I've seen in collecting this
> > information using the existing methods from small production systems
> > in practice over the last ~1.5 years.
>
> Regarding the proposed statistics, I find them reasonably useful for
> many users. I'm not sure we need to draw a strict line on what belongs
> in the module. If a proposed function does exactly what most
> pg_buffercache users want or are already writing themselves, that is
> good enough motivation to include it.
>
> I think pg_visibility is a good precedent here. In that module, we
> have both pg_visibility_map() and pg_visibility_map_summary(), even
> though we can retrieve the exact same results as the latter by simply
> using the former:
>
> select sum(all_visible::int), sum(all_frozen::int) from
> pg_visibility_map('test') ;
>

A summary may still be ok, but this proposal is going a bit farther,
it's grouping by one subset which should really be done by GROUP BY in
SQL. And I do

I am afraid that at some point, we will start finding all of these to
be a maintenance burden. At that point, removing them will become a
real pain for the backward compatibility reason. For example
1. The proposed function is going to add one more test to an already
huge testing exercise for shared buffers resizing.
2. If we change the way to manage buffer cache e.g. use a tree based
cache instead of hash + array cache, each of the functions which
traverses the buffer cache array is going to add work - adjusting it
to the new data structure - and make a hard project even harder. In
this case we have other ways to get the summary, so the code level
scan of buffer cache is entirely avoidable.

If I am the only one opposing it, and there are more senior
contributors in favour of adding this function, we can accept it.

-- 
Best Wishes,
Ashutosh Bapat

Re: pg_buffercache: Add per-relation summary stats

Dmitry Dolgov <9erthalion6@gmail.com> — 2026-03-27T18:11:03Z

Thanks for the patch, I like the idea.

> On Sat, Feb 28, 2026 at 03:58:34PM -0800, Lukas Fittl wrote:
>
> 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.

I see how relation footprint on the buffer cache can be useful, e.g. how
many buffers per relation are used as well as how many of them are
dirty. But how one can benefit from number of pinned buffers and average
usage count per relation, is there a clear understanding of what to do
about those numbers?

> The <function>pg_buffercache_summary()</function> function returns a
> single row summarizing the state of all shared buffers. 

> The <function>pg_buffercache_relations()</function> function returns
> a set of rows summarizing the state of all shared buffers, aggregated by
> relation and fork number. 

Maybe it was already asked before, but given those two functions
(pg_buffercache_summary and pg_buffercache_relations) seems to have a
very similar goal and the summary is just happening in different ways,
is there a way to somehow unify them and have one function instead of
two separate? It could be a unified function implementation and still
two different interfaces to call, or even a single pg_buffercache_summary
with an argument, specifying how to summarize.

Also, would it be useful to have a numa-aware counterpart for the
per-relation summary? Something like: this relation has so and so many
buffers, and 50% of them are located on the node 1, while the other 50%
on the node 2.

Re: pg_buffercache: Add per-relation summary stats

Tomas Vondra <tomas@vondra.me> — 2026-03-27T22:58:52Z

On 3/26/26 05:21, Ashutosh Bapat wrote:
> On Wed, Mar 25, 2026 at 10:19 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>>
>> On Tue, Mar 24, 2026 at 11:47 PM Lukas Fittl <lukas@fittl.com> wrote:
>>>
>>> Hi Ashutosh,
>>>
>>> On Tue, Mar 24, 2026 at 11:24 PM Ashutosh Bapat
>>> <ashutosh.bapat.oss@gmail.com> wrote:
>>>> I know we already have a couple of hand-aggregation functions but I am
>>>> hesitant to add more of these. Question is where do we stop? For
>>>> example, the current function is useless if someone wants to find the
>>>> parts of a relation which are hot since it doesn't include page
>>>> numbers. Do we write another function for the same? Or we add page
>>>> numbers to this function and then there's hardly any aggregation
>>>> happening. What if somebody wanted to perform an aggregation more
>>>> complex than just count() like average number of buffers per relation
>>>> or distribution of relation buffers in the cache, do they write
>>>> separate functions?
>>>
>>> I think the problem this solves for, which is a very common question I
>>> hear from end users, is "how much of this table/index is in cache" and
>>> "was our query slow because the cache contents changed?".
>>>
>>> It can't provide a perfect answer to all questions regarding what's in
>>> the cache (i.e. it won't tell you which part of the table is cached),
>>> but its in line with other statistics we do already provide in
>>> pg_stat_user_tables etc., which are all aggregate counts, not further
>>> breakdowns.
>>>
>>> Its also a reasonable compromise on providing something usable that
>>> can be shown on dashboards, as I've seen in collecting this
>>> information using the existing methods from small production systems
>>> in practice over the last ~1.5 years.
>>
>> Regarding the proposed statistics, I find them reasonably useful for
>> many users. I'm not sure we need to draw a strict line on what belongs
>> in the module. If a proposed function does exactly what most
>> pg_buffercache users want or are already writing themselves, that is
>> good enough motivation to include it.
>>
>> I think pg_visibility is a good precedent here. In that module, we
>> have both pg_visibility_map() and pg_visibility_map_summary(), even
>> though we can retrieve the exact same results as the latter by simply
>> using the former:
>>
>> select sum(all_visible::int), sum(all_frozen::int) from
>> pg_visibility_map('test') ;
>>
> 
> A summary may still be ok, but this proposal is going a bit farther,
> it's grouping by one subset which should really be done by GROUP BY in
> SQL. And I do
> 
> I am afraid that at some point, we will start finding all of these to
> be a maintenance burden. At that point, removing them will become a
> real pain for the backward compatibility reason. For example
> 1. The proposed function is going to add one more test to an already
> huge testing exercise for shared buffers resizing.
> 2. If we change the way to manage buffer cache e.g. use a tree based
> cache instead of hash + array cache, each of the functions which
> traverses the buffer cache array is going to add work - adjusting it
> to the new data structure - and make a hard project even harder. In
> this case we have other ways to get the summary, so the code level
> scan of buffer cache is entirely avoidable.
> 
> If I am the only one opposing it, and there are more senior
> contributors in favour of adding this function, we can accept it.
> 

I understand this argument - we have SQL, which allows us to process the
data in a flexible way, without hard-coding all interesting groupings.
The question is whether this particular grouping is special enough to
warrant a custom *faster* function.

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.

I gave it a try on an azure VM with 32GB shared buffers, to make it a
bit more realistic, and my timings are 10ms vs. 700ms. But I also wonder
if the original timings really were from a cluster with 128MB, because
for me that shows 0.3ms vs. 3ms (so an order of magnitude faster than
what was reported). But I suppose that's also hw specific.

Nevertheless, it is much faster. I haven't profiled this but I assume
it's thanks to not having to write the entries into a tuplestore (and
possibly into a tempfile).

But is it actually needed / worth it? I wonder what timings does Lukas
observe when running this on larger clusters. Because in a later email
he says:

   ... we currently run this on a 10 minute schedule when enabled, and
   that seems to work in terms of understanding large swings in cache
   contents.

I'm all in for optimizing stuff, but if you're running a monitoring task
every 10 minutes, does it matter if it's running for 1 or 5 seconds? I
find that a bit hard to believe.

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 don't have clear opinion if we should do this. I kinda doubt it's a
significant maintenance burden. It'd add one more place the patch for
on-line resizing of shared buffers needs to worry about. Surely that
should not be very difficult, considering there are ~5 other places in
this very extension doing this already?

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.

A couple minor comments about the code:

1) Isn't this check unnecessary? All entries should have buffers > 0.

    if (entry->buffers == 0)
        continue;

2) Shouldn't this check BM_TAG_VALID too? Or is BM_VALID enough to look
at the bufHdr->tag?

    /* Skip unused/invalid buffers */
    if (!(buf_state & BM_VALID))
        continue;

3) I think "buffers" argument should be renamed to "buffers_unused" for
consistency with pg_buffercache_summary.

Overall, I'm -0.1 on this. I'm not opposed to doing this, but I'm also
not quite convinced it's worth it.

regards

-- 
Tomas Vondra

Re: pg_buffercache: Add per-relation summary stats

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2026-03-28T04:18:26Z

On Sat, Mar 28, 2026 at 4:28 AM Tomas Vondra <tomas@vondra.me> wrote:
>
> On 3/26/26 05:21, Ashutosh Bapat wrote:
> > On Wed, Mar 25, 2026 at 10:19 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >>
> >> On Tue, Mar 24, 2026 at 11:47 PM Lukas Fittl <lukas@fittl.com> wrote:
> >>>
> >>> Hi Ashutosh,
> >>>
> >>> On Tue, Mar 24, 2026 at 11:24 PM Ashutosh Bapat
> >>> <ashutosh.bapat.oss@gmail.com> wrote:
> >>>> I know we already have a couple of hand-aggregation functions but I am
> >>>> hesitant to add more of these. Question is where do we stop? For
> >>>> example, the current function is useless if someone wants to find the
> >>>> parts of a relation which are hot since it doesn't include page
> >>>> numbers. Do we write another function for the same? Or we add page
> >>>> numbers to this function and then there's hardly any aggregation
> >>>> happening. What if somebody wanted to perform an aggregation more
> >>>> complex than just count() like average number of buffers per relation
> >>>> or distribution of relation buffers in the cache, do they write
> >>>> separate functions?
> >>>
> >>> I think the problem this solves for, which is a very common question I
> >>> hear from end users, is "how much of this table/index is in cache" and
> >>> "was our query slow because the cache contents changed?".
> >>>
> >>> It can't provide a perfect answer to all questions regarding what's in
> >>> the cache (i.e. it won't tell you which part of the table is cached),
> >>> but its in line with other statistics we do already provide in
> >>> pg_stat_user_tables etc., which are all aggregate counts, not further
> >>> breakdowns.
> >>>
> >>> Its also a reasonable compromise on providing something usable that
> >>> can be shown on dashboards, as I've seen in collecting this
> >>> information using the existing methods from small production systems
> >>> in practice over the last ~1.5 years.
> >>
> >> Regarding the proposed statistics, I find them reasonably useful for
> >> many users. I'm not sure we need to draw a strict line on what belongs
> >> in the module. If a proposed function does exactly what most
> >> pg_buffercache users want or are already writing themselves, that is
> >> good enough motivation to include it.
> >>
> >> I think pg_visibility is a good precedent here. In that module, we
> >> have both pg_visibility_map() and pg_visibility_map_summary(), even
> >> though we can retrieve the exact same results as the latter by simply
> >> using the former:
> >>
> >> select sum(all_visible::int), sum(all_frozen::int) from
> >> pg_visibility_map('test') ;
> >>
> >
> > A summary may still be ok, but this proposal is going a bit farther,
> > it's grouping by one subset which should really be done by GROUP BY in
> > SQL. And I do
> >
> > I am afraid that at some point, we will start finding all of these to
> > be a maintenance burden. At that point, removing them will become a
> > real pain for the backward compatibility reason. For example
> > 1. The proposed function is going to add one more test to an already
> > huge testing exercise for shared buffers resizing.
> > 2. If we change the way to manage buffer cache e.g. use a tree based
> > cache instead of hash + array cache, each of the functions which
> > traverses the buffer cache array is going to add work - adjusting it
> > to the new data structure - and make a hard project even harder. In
> > this case we have other ways to get the summary, so the code level
> > scan of buffer cache is entirely avoidable.
> >
> > If I am the only one opposing it, and there are more senior
> > contributors in favour of adding this function, we can accept it.
> >
>
> I understand this argument - we have SQL, which allows us to process the
> data in a flexible way, without hard-coding all interesting groupings.
> The question is whether this particular grouping is special enough to
> warrant a custom *faster* function.
>
> 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.
>
> I gave it a try on an azure VM with 32GB shared buffers, to make it a
> bit more realistic, and my timings are 10ms vs. 700ms. But I also wonder
> if the original timings really were from a cluster with 128MB, because
> for me that shows 0.3ms vs. 3ms (so an order of magnitude faster than
> what was reported). But I suppose that's also hw specific.
>
> Nevertheless, it is much faster. I haven't profiled this but I assume
> it's thanks to not having to write the entries into a tuplestore (and
> possibly into a tempfile).

Parallely myself and Palak Chaturvedi developed a quick patch to
modernise pg_buffercache_pages() and use tuplestore so that it doesn't
have to rely on NBuffers being the same between start of the scan,
when memory allocated, when the scan ends - a condition possible with
resizing buffer cache. It seems to improve the timings by about 10-30%
on my laptop for 128MB buffercache size. Without this patch the time
taken to execute Lukas's query varies between 10-15ms on my laptop.
With this patch it varies between 8-9ms. So the timing is more stable
as a side effect. It's not a 10x improvement that we are looking for
but it looks like a step in the right direction. That improvement
seems to come purely because we avoid creating a heap tuple. I wonder
if there are some places up in the execution tree where full
heaptuples get formed again instead of continuing to use minimal
tuples or places where we perform some extra actions that are not
required.

I didn't dig into the history to find out why we didn't modernize
pg_buffercache_pages(). I don't see any hazard though.

Lukas's patch allocates the hash table in memory entirely, whereas
tuplestore restricts memory usage to work_mem, so it might cause the
function to use more memory than user expects it to use when size of
the hash table grows beyond work_mem.

-- 
Best Wishes,
Ashutosh Bapat

Re: pg_buffercache: Add per-relation summary stats

Masahiko Sawada <sawada.mshk@gmail.com> — 2026-03-28T05:36:51Z

On Fri, Mar 27, 2026 at 3:58 PM Tomas Vondra <tomas@vondra.me> wrote:
>
> On 3/26/26 05:21, Ashutosh Bapat wrote:
> > On Wed, Mar 25, 2026 at 10:19 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >>
> >> On Tue, Mar 24, 2026 at 11:47 PM Lukas Fittl <lukas@fittl.com> wrote:
> >>>
> >>> Hi Ashutosh,
> >>>
> >>> On Tue, Mar 24, 2026 at 11:24 PM Ashutosh Bapat
> >>> <ashutosh.bapat.oss@gmail.com> wrote:
> >>>> I know we already have a couple of hand-aggregation functions but I am
> >>>> hesitant to add more of these. Question is where do we stop? For
> >>>> example, the current function is useless if someone wants to find the
> >>>> parts of a relation which are hot since it doesn't include page
> >>>> numbers. Do we write another function for the same? Or we add page
> >>>> numbers to this function and then there's hardly any aggregation
> >>>> happening. What if somebody wanted to perform an aggregation more
> >>>> complex than just count() like average number of buffers per relation
> >>>> or distribution of relation buffers in the cache, do they write
> >>>> separate functions?
> >>>
> >>> I think the problem this solves for, which is a very common question I
> >>> hear from end users, is "how much of this table/index is in cache" and
> >>> "was our query slow because the cache contents changed?".
> >>>
> >>> It can't provide a perfect answer to all questions regarding what's in
> >>> the cache (i.e. it won't tell you which part of the table is cached),
> >>> but its in line with other statistics we do already provide in
> >>> pg_stat_user_tables etc., which are all aggregate counts, not further
> >>> breakdowns.
> >>>
> >>> Its also a reasonable compromise on providing something usable that
> >>> can be shown on dashboards, as I've seen in collecting this
> >>> information using the existing methods from small production systems
> >>> in practice over the last ~1.5 years.
> >>
> >> Regarding the proposed statistics, I find them reasonably useful for
> >> many users. I'm not sure we need to draw a strict line on what belongs
> >> in the module. If a proposed function does exactly what most
> >> pg_buffercache users want or are already writing themselves, that is
> >> good enough motivation to include it.
> >>
> >> I think pg_visibility is a good precedent here. In that module, we
> >> have both pg_visibility_map() and pg_visibility_map_summary(), even
> >> though we can retrieve the exact same results as the latter by simply
> >> using the former:
> >>
> >> select sum(all_visible::int), sum(all_frozen::int) from
> >> pg_visibility_map('test') ;
> >>
> >
> > A summary may still be ok, but this proposal is going a bit farther,
> > it's grouping by one subset which should really be done by GROUP BY in
> > SQL. And I do
> >
> > I am afraid that at some point, we will start finding all of these to
> > be a maintenance burden. At that point, removing them will become a
> > real pain for the backward compatibility reason. For example
> > 1. The proposed function is going to add one more test to an already
> > huge testing exercise for shared buffers resizing.
> > 2. If we change the way to manage buffer cache e.g. use a tree based
> > cache instead of hash + array cache, each of the functions which
> > traverses the buffer cache array is going to add work - adjusting it
> > to the new data structure - and make a hard project even harder. In
> > this case we have other ways to get the summary, so the code level
> > scan of buffer cache is entirely avoidable.
> >
> > If I am the only one opposing it, and there are more senior
> > contributors in favour of adding this function, we can accept it.
> >
>
> I understand this argument - we have SQL, which allows us to process the
> data in a flexible way, without hard-coding all interesting groupings.
> The question is whether this particular grouping is special enough to
> warrant a custom *faster* function.
>
> 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.
>
> I gave it a try on an azure VM with 32GB shared buffers, to make it a
> bit more realistic, and my timings are 10ms vs. 700ms. But I also wonder
> if the original timings really were from a cluster with 128MB, because
> for me that shows 0.3ms vs. 3ms (so an order of magnitude faster than
> what was reported). But I suppose that's also hw specific.
>
> Nevertheless, it is much faster. I haven't profiled this but I assume
> it's thanks to not having to write the entries into a tuplestore (and
> possibly into a tempfile).
>
> But is it actually needed / worth it? I wonder what timings does Lukas
> observe when running this on larger clusters. Because in a later email
> he says:
>
>    ... we currently run this on a 10 minute schedule when enabled, and
>    that seems to work in terms of understanding large swings in cache
>    contents.
>
> I'm all in for optimizing stuff, but if you're running a monitoring task
> every 10 minutes, does it matter if it's running for 1 or 5 seconds? I
> find that a bit hard to believe.

I imagined such a query is just one of many monitoring queries running
concurrently, so the cumulative overhead can still matter.

> I don't have clear opinion if we should do this. I kinda doubt it's a
> significant maintenance burden. It'd add one more place the patch for
> on-line resizing of shared buffers needs to worry about. Surely that
> should not be very difficult, considering there are ~5 other places in
> this very extension doing this already?

Yeah, I've not looked at the online shared buffer resizing patch, but
I hope that the patch somewhat abstructs the access to shared buffers
that might be being resized so that we don't need to worry about the
complex part when writing code accessing the shared buffers.

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

Good point. I agree that we should not introduce the function in a way
that there is a risk of using excessive memory while not respecting
work_mem or other GUC parameters.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Re: pg_buffercache: Add per-relation summary stats

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2026-03-28T16:12:07Z

On Sat, Mar 28, 2026 at 11:07 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Fri, Mar 27, 2026 at 3:58 PM Tomas Vondra <tomas@vondra.me> wrote:
> >
> > On 3/26/26 05:21, Ashutosh Bapat wrote:
> > > On Wed, Mar 25, 2026 at 10:19 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > >>
> > >> On Tue, Mar 24, 2026 at 11:47 PM Lukas Fittl <lukas@fittl.com> wrote:
> > >>>
> > >>> Hi Ashutosh,
> > >>>
> > >>> On Tue, Mar 24, 2026 at 11:24 PM Ashutosh Bapat
> > >>> <ashutosh.bapat.oss@gmail.com> wrote:
> > >>>> I know we already have a couple of hand-aggregation functions but I am
> > >>>> hesitant to add more of these. Question is where do we stop? For
> > >>>> example, the current function is useless if someone wants to find the
> > >>>> parts of a relation which are hot since it doesn't include page
> > >>>> numbers. Do we write another function for the same? Or we add page
> > >>>> numbers to this function and then there's hardly any aggregation
> > >>>> happening. What if somebody wanted to perform an aggregation more
> > >>>> complex than just count() like average number of buffers per relation
> > >>>> or distribution of relation buffers in the cache, do they write
> > >>>> separate functions?
> > >>>
> > >>> I think the problem this solves for, which is a very common question I
> > >>> hear from end users, is "how much of this table/index is in cache" and
> > >>> "was our query slow because the cache contents changed?".
> > >>>
> > >>> It can't provide a perfect answer to all questions regarding what's in
> > >>> the cache (i.e. it won't tell you which part of the table is cached),
> > >>> but its in line with other statistics we do already provide in
> > >>> pg_stat_user_tables etc., which are all aggregate counts, not further
> > >>> breakdowns.
> > >>>
> > >>> Its also a reasonable compromise on providing something usable that
> > >>> can be shown on dashboards, as I've seen in collecting this
> > >>> information using the existing methods from small production systems
> > >>> in practice over the last ~1.5 years.
> > >>
> > >> Regarding the proposed statistics, I find them reasonably useful for
> > >> many users. I'm not sure we need to draw a strict line on what belongs
> > >> in the module. If a proposed function does exactly what most
> > >> pg_buffercache users want or are already writing themselves, that is
> > >> good enough motivation to include it.
> > >>
> > >> I think pg_visibility is a good precedent here. In that module, we
> > >> have both pg_visibility_map() and pg_visibility_map_summary(), even
> > >> though we can retrieve the exact same results as the latter by simply
> > >> using the former:
> > >>
> > >> select sum(all_visible::int), sum(all_frozen::int) from
> > >> pg_visibility_map('test') ;
> > >>
> > >
> > > A summary may still be ok, but this proposal is going a bit farther,
> > > it's grouping by one subset which should really be done by GROUP BY in
> > > SQL. And I do
> > >
> > > I am afraid that at some point, we will start finding all of these to
> > > be a maintenance burden. At that point, removing them will become a
> > > real pain for the backward compatibility reason. For example
> > > 1. The proposed function is going to add one more test to an already
> > > huge testing exercise for shared buffers resizing.
> > > 2. If we change the way to manage buffer cache e.g. use a tree based
> > > cache instead of hash + array cache, each of the functions which
> > > traverses the buffer cache array is going to add work - adjusting it
> > > to the new data structure - and make a hard project even harder. In
> > > this case we have other ways to get the summary, so the code level
> > > scan of buffer cache is entirely avoidable.
> > >
> > > If I am the only one opposing it, and there are more senior
> > > contributors in favour of adding this function, we can accept it.
> > >
> >
> > I understand this argument - we have SQL, which allows us to process the
> > data in a flexible way, without hard-coding all interesting groupings.
> > The question is whether this particular grouping is special enough to
> > warrant a custom *faster* function.

Well-said. Thanks.

> >
> > 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.
> >
> > I gave it a try on an azure VM with 32GB shared buffers, to make it a
> > bit more realistic, and my timings are 10ms vs. 700ms. But I also wonder
> > if the original timings really were from a cluster with 128MB, because
> > for me that shows 0.3ms vs. 3ms (so an order of magnitude faster than
> > what was reported). But I suppose that's also hw specific.
> >
> > Nevertheless, it is much faster. I haven't profiled this but I assume
> > it's thanks to not having to write the entries into a tuplestore (and
> > possibly into a tempfile).
> >
> > But is it actually needed / worth it? I wonder what timings does Lukas
> > observe when running this on larger clusters. Because in a later email
> > he says:
> >
> >    ... we currently run this on a 10 minute schedule when enabled, and
> >    that seems to work in terms of understanding large swings in cache
> >    contents.
> >
> > I'm all in for optimizing stuff, but if you're running a monitoring task
> > every 10 minutes, does it matter if it's running for 1 or 5 seconds? I
> > find that a bit hard to believe.
>
> I imagined such a query is just one of many monitoring queries running
> concurrently, so the cumulative overhead can still matter.
>

What kind of cumulative overhead, do you see? Reduced TPS, increased
memory/CPU consumption? I think itd will be good to see some metric
evidence of this, rather than relying on the assumption.

> > I don't have a clear opinion if we should do this. I kinda doubt it's a
> > significant maintenance burden. It'd add one more place the patch for
> > on-line resizing of shared buffers needs to worry about. Surely that
> > should not be very difficult, considering there are ~5 other places in
> > this very extension doing this already?
>
> Yeah, I've not looked at the online shared buffer resizing patch, but
> I hope that the patch somewhat abstructs the access to shared buffers
> that might be being resized so that we don't need to worry about the
> complex part when writing code accessing the shared buffers.

The code to abstract isn't there in the patch yet, but I agree that
regular scan code shouldn't receive a lot of changes in the resizing
implementation patch. I have been toying with the idea that we provide
an abstraction to walk the buffer cache (foreach_buffer for example),
which may hide any complexity, if required. So, yes, there will be
some abstraction as you envision. However, testing will still be
required. It's the testing effort, increase in test time etc. which I
am worried about. However, I may be overestimating it.

>
> > 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.
>
> Good point. I agree that we should not introduce the function in a way
> that there is a risk of using excessive memory while not respecting
> work_mem or other GUC parameters.

+1.

-- 
Best Wishes,
Ashutosh Bapat

Re: pg_buffercache: Add per-relation summary stats

Lukas Fittl <lukas@fittl.com> — 2026-03-28T18:51:36Z

On Fri, Mar 27, 2026 at 10:37 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Fri, Mar 27, 2026 at 3:58 PM Tomas Vondra <tomas@vondra.me> wrote:
> > 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.
>
> Good point. I agree that we should not introduce the function in a way
> that there is a risk of using excessive memory while not respecting
> work_mem or other GUC parameters.

Yeah, I agree that is problematic regarding work_mem.

FWIW, I could see two methods to address that specifically, if we
wanted the special purpose function:

1) Error out if our hash table grows too large and require the user to
increase work_mem to get the data - seems inconvenient, but might be
okay if we are typically below work_mem limit anyway (I haven't run
the numbers on that yet)

2) Implement disk spill logic using a LogicalTapeSet or similar - I
think that'd be substantially more code, doesn't seem worth it just
for this (but if a situation like this recurs, we could consider a
more generalized facility)

Thanks,
Lukas

-- 
Lukas Fittl

Re: pg_buffercache: Add per-relation summary stats

Lukas Fittl <lukas@fittl.com> — 2026-03-28T19:14:14Z

On Sat, Mar 28, 2026 at 9:12 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Sat, Mar 28, 2026 at 11:07 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > On Fri, Mar 27, 2026 at 3:58 PM Tomas Vondra <tomas@vondra.me> wrote:
> > >
> > > On 3/26/26 05:21, Ashutosh Bapat 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.
> > >
> > > I gave it a try on an azure VM with 32GB shared buffers, to make it a
> > > bit more realistic, and my timings are 10ms vs. 700ms. But I also wonder
> > > if the original timings really were from a cluster with 128MB, because
> > > for me that shows 0.3ms vs. 3ms (so an order of magnitude faster than
> > > what was reported). But I suppose that's also hw specific.
> > >
> > > Nevertheless, it is much faster. I haven't profiled this but I assume
> > > it's thanks to not having to write the entries into a tuplestore (and
> > > possibly into a tempfile).
> > >
> > > But is it actually needed / worth it? I wonder what timings does Lukas
> > > observe when running this on larger clusters. Because in a later email
> > > he says:
> > >
> > >    ... we currently run this on a 10 minute schedule when enabled, and
> > >    that seems to work in terms of understanding large swings in cache
> > >    contents.
> > >
> > > I'm all in for optimizing stuff, but if you're running a monitoring task
> > > every 10 minutes, does it matter if it's running for 1 or 5 seconds? I
> > > find that a bit hard to believe.
> >
> > I imagined such a query is just one of many monitoring queries running
> > concurrently, so the cumulative overhead can still matter.
> >
>
> What kind of cumulative overhead, do you see? Reduced TPS, increased
> memory/CPU consumption? I think itd will be good to see some metric
> evidence of this, rather than relying on the assumption.

On my part, the overhead that I've specifically seen in the field,
besides CPU utilization (which isn't great, but could be worse) is
temporary file use for large shared_buffers, due to writing out one
row to the tuplestore per buffer entry.

Here is an example from a production database, running Postgres 16
with 200GB shared_buffers:

SHOW shared_buffers;

 shared_buffers
----------------
 207873040kB
(1 row)

EXPLAIN (ANALYZE, BUFFERS) SELECT reldatabase, relfilenode, count(*)
FROM pg_buffercache
WHERE reldatabase IS NOT NULL
GROUP BY 1, 2;

                                                                QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=17.46..19.46 rows=200 width=16) (actual
time=12999.311..12999.526 rows=1683 loops=1)
   Group Key: p.reldatabase, p.relfilenode
   Batches: 1  Memory Usage: 209kB
   Buffers: temp read=158595 written=158595
   I/O Timings: temp read=246.513 write=1013.565
   ->  Function Scan on pg_buffercache_pages p  (cost=0.00..10.00
rows=995 width=8) (actual time=5403.944..8864.129 rows=25984130
loops=1)
         Filter: (reldatabase IS NOT NULL)
         Buffers: temp read=158595 written=158595
         I/O Timings: temp read=246.513 write=1013.565
 Planning:
   Buffers: shared hit=5
 Planning Time: 0.101 ms
 Execution Time: 13200.972 ms
(13 rows)

In this case we used a ~1.2GB temporary file to write out 25 million
rows, what could have been a ~100kb allocation in memory instead (~40
bytes BufferRelStatsEntry in v2 * 2048 slots in simplehash).


Thanks,
Lukas

--
Lukas Fittl

Re: pg_buffercache: Add per-relation summary stats

Lukas Fittl <lukas@fittl.com> — 2026-03-28T19:38:04Z

Hi Tomas,

Thanks for reviewing - just a quick response on your code review
comments specifically:

On Fri, Mar 27, 2026 at 3:58 PM Tomas Vondra <tomas@vondra.me> wrote:
> I gave it a try on an azure VM with 32GB shared buffers, to make it a
> bit more realistic, and my timings are 10ms vs. 700ms. But I also wonder
> if the original timings really were from a cluster with 128MB, because
> for me that shows 0.3ms vs. 3ms (so an order of magnitude faster than
> what was reported). But I suppose that's also hw specific.

Yeah, those initial numbers were from my Apple Silicon M3 ARM laptop
without any special configuration, just for reference.

> A couple minor comments about the code:
>
> 1) Isn't this check unnecessary? All entries should have buffers > 0.
>
>     if (entry->buffers == 0)
>         continue;

Yeah, good point, that is there to protect the division for the avg
usage count, but I agree in practice this shouldn't be reached. I
could make it an assert, just in case.

> 2) Shouldn't this check BM_TAG_VALID too? Or is BM_VALID enough to look
> at the bufHdr->tag?
>
>     /* Skip unused/invalid buffers */
>     if (!(buf_state & BM_VALID))
>         continue;
>

Good point, I think that makes sense to check BM_TAG_VALID here as well.

FWIW, the function as-is does not lock the buffer header with
LockBufHdr (intentionally to lower overhead), which means we can read
a stale relation reference. I think that's okay for aggregate level /
monitoring type information, but just want to call it out in this
context.

> 3) I think "buffers" argument should be renamed to "buffers_unused" for
> consistency with pg_buffercache_summary.

I assume you meant "buffers_used" instead of "buffers_unused" -
assuming yes, that makes sense for consistency.

---

I'll hold off on posting a new version for now, since I think we'd
have to figure out a solution to the work_mem question at the very
least, and it sounds like right now its also a toss-up in terms of
overall interest to get this committed.

Thanks,
Lukas

--
Lukas Fittl

Re: pg_buffercache: Add per-relation summary stats

Heikki Linnakangas <hlinnaka@iki.fi> — 2026-04-07T13:07:45Z

On 28/03/2026 06:18, Ashutosh Bapat wrote:
> Parallely myself and Palak Chaturvedi developed a quick patch to
> modernise pg_buffercache_pages() and use tuplestore so that it doesn't
> have to rely on NBuffers being the same between start of the scan,
> when memory allocated, when the scan ends - a condition possible with
> resizing buffer cache. It seems to improve the timings by about 10-30%
> on my laptop for 128MB buffercache size. Without this patch the time
> taken to execute Lukas's query varies between 10-15ms on my laptop.
> With this patch it varies between 8-9ms. So the timing is more stable
> as a side effect. It's not a 10x improvement that we are looking for
> but it looks like a step in the right direction. That improvement
> seems to come purely because we avoid creating a heap tuple. I wonder
> if there are some places up in the execution tree where full
> heaptuples get formed again instead of continuing to use minimal
> tuples or places where we perform some extra actions that are not
> required.
> 
> I didn't dig into the history to find out why we didn't modernize
> pg_buffercache_pages(). I don't see any hazard though.

Committed this modernization patch, thanks!

It would be nice to have a proper row-at-a-time mode that would avoid 
materializing the result, but collecting all the data in a temporary 
array is clearly worse than just putting them to the tuplestore 
directly. The only reason I can think of why we'd prefer to use a 
temporary array like that is to get a more consistent snapshot of all 
the buffers, by keeping the time spent scanning the buffers as short as 
possible. But we're not getting a consistent view anyway, it's just a 
matter of degree.

I wondered about this in pg_buffercache_pages.c:

> 	/*
> 	 * To smoothly support upgrades from version 1.0 of this extension
> 	 * transparently handle the (non-)existence of the pinning_backends
> 	 * column. We unfortunately have to get the result type for that... - we
> 	 * can't use the result type determined by the function definition without
> 	 * potentially crashing when somebody uses the old (or even wrong)
> 	 * function definition though.
> 	 */
> 	if (get_call_result_type(fcinfo, NULL, &expected_tupledesc) != TYPEFUNC_COMPOSITE)
> 		elog(ERROR, "return type must be a row type");
> 
> 	if (expected_tupledesc->natts < NUM_BUFFERCACHE_PAGES_MIN_ELEM ||
> 		expected_tupledesc->natts > NUM_BUFFERCACHE_PAGES_ELEM)
> 		elog(ERROR, "incorrect number of output arguments");

I guess it's still needed, if you have pg_upgraded all the way from 1.0. 
To test that, I created this view to match the old 1.0 definition:

CREATE VIEW public.legacy_pg_buffercache AS
  SELECT bufferid,
     relfilenode,
     reltablespace,
     reldatabase,
     relforknumber,
     relblocknumber,
     isdirty,
     usagecount
    FROM public.pg_buffercache_pages() p(bufferid integer, relfilenode 
oid, reltablespace oid, reldatabase oid, relforknumber smallint, 
relblocknumber bigint, isdirty boolean, usagecount smallint);

"select * from public.legacy_pg_buffercache" still works, so all good.

- Heikki

Re: pg_buffercache: Add per-relation summary stats

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2026-04-07T13:23:46Z

On Tue, Apr 7, 2026 at 6:37 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>
> On 28/03/2026 06:18, Ashutosh Bapat wrote:
> > Parallely myself and Palak Chaturvedi developed a quick patch to
> > modernise pg_buffercache_pages() and use tuplestore so that it doesn't
> > have to rely on NBuffers being the same between start of the scan,
> > when memory allocated, when the scan ends - a condition possible with
> > resizing buffer cache. It seems to improve the timings by about 10-30%
> > on my laptop for 128MB buffercache size. Without this patch the time
> > taken to execute Lukas's query varies between 10-15ms on my laptop.
> > With this patch it varies between 8-9ms. So the timing is more stable
> > as a side effect. It's not a 10x improvement that we are looking for
> > but it looks like a step in the right direction. That improvement
> > seems to come purely because we avoid creating a heap tuple. I wonder
> > if there are some places up in the execution tree where full
> > heaptuples get formed again instead of continuing to use minimal
> > tuples or places where we perform some extra actions that are not
> > required.
> >
> > I didn't dig into the history to find out why we didn't modernize
> > pg_buffercache_pages(). I don't see any hazard though.
>
> Committed this modernization patch, thanks!
>
> It would be nice to have a proper row-at-a-time mode that would avoid
> materializing the result, but collecting all the data in a temporary
> array is clearly worse than just putting them to the tuplestore
> directly. The only reason I can think of why we'd prefer to use a
> temporary array like that is to get a more consistent snapshot of all
> the buffers, by keeping the time spent scanning the buffers as short as
> possible. But we're not getting a consistent view anyway, it's just a
> matter of degree.
>

Thanks a lot. Makes code in buffer resizing a bit simpler esp. code
changes in this module. Probably it won't need any code changes now in
the buffer resizing patches.

> I wondered about this in pg_buffercache_pages.c:
>
> >       /*
> >        * To smoothly support upgrades from version 1.0 of this extension
> >        * transparently handle the (non-)existence of the pinning_backends
> >        * column. We unfortunately have to get the result type for that... - we
> >        * can't use the result type determined by the function definition without
> >        * potentially crashing when somebody uses the old (or even wrong)
> >        * function definition though.
> >        */
> >       if (get_call_result_type(fcinfo, NULL, &expected_tupledesc) != TYPEFUNC_COMPOSITE)
> >               elog(ERROR, "return type must be a row type");
> >
> >       if (expected_tupledesc->natts < NUM_BUFFERCACHE_PAGES_MIN_ELEM ||
> >               expected_tupledesc->natts > NUM_BUFFERCACHE_PAGES_ELEM)
> >               elog(ERROR, "incorrect number of output arguments");
>
> I guess it's still needed, if you have pg_upgraded all the way from 1.0.
> To test that, I created this view to match the old 1.0 definition:
>
> CREATE VIEW public.legacy_pg_buffercache AS
>   SELECT bufferid,
>      relfilenode,
>      reltablespace,
>      reldatabase,
>      relforknumber,
>      relblocknumber,
>      isdirty,
>      usagecount
>     FROM public.pg_buffercache_pages() p(bufferid integer, relfilenode
> oid, reltablespace oid, reldatabase oid, relforknumber smallint,
> relblocknumber bigint, isdirty boolean, usagecount smallint);
>
> "select * from public.legacy_pg_buffercache" still works, so all good.

Yeah. At some point we should get rid of this code, but it would
require some "upgrade" action from the customer as well.

-- 
Best Wishes,
Ashutosh Bapat

Re: pg_buffercache: Add per-relation summary stats

Andres Freund <andres@anarazel.de> — 2026-04-07T13:47:26Z

Hi,

On 2026-04-07 16:07:45 +0300, Heikki Linnakangas wrote:
> On 28/03/2026 06:18, Ashutosh Bapat wrote:
> > Parallely myself and Palak Chaturvedi developed a quick patch to
> > modernise pg_buffercache_pages() and use tuplestore so that it doesn't
> > have to rely on NBuffers being the same between start of the scan,
> > when memory allocated, when the scan ends - a condition possible with
> > resizing buffer cache. It seems to improve the timings by about 10-30%
> > on my laptop for 128MB buffercache size. Without this patch the time
> > taken to execute Lukas's query varies between 10-15ms on my laptop.
> > With this patch it varies between 8-9ms. So the timing is more stable
> > as a side effect. It's not a 10x improvement that we are looking for
> > but it looks like a step in the right direction. That improvement
> > seems to come purely because we avoid creating a heap tuple. I wonder
> > if there are some places up in the execution tree where full
> > heaptuples get formed again instead of continuing to use minimal
> > tuples or places where we perform some extra actions that are not
> > required.

I don't think that's the reason for the improvement - tuplestore_putvalues()
forms a minimal tuple, and the cost to form a minimal tuple and a heap tuple
aren't meaningfully different.

I think the problem is that we materialize rowmode SRFs as a tuplestore if
they are in the from list.  You can easily see this even with just
generate_series():

postgres[1520825][1]=# SELECT count(*) FROM generate_series(1, 1000000);
┌─────────┐
│  count  │
├─────────┤
│ 1000000 │
└─────────┘
(1 row)

Time: 117.939 ms
postgres[1520825][1]=# SELECT count(*) FROM (SELECT generate_series(1, 1000000));
┌─────────┐
│  count  │
├─────────┤
│ 1000000 │
└─────────┘
(1 row)

Time: 58.914 ms


Of course, because pg_buffercache_pages() is archaicially defined without
defininig its output columns, you can't actually use it in the select list.

But that can be fixed:

CREATE FUNCTION pg_buffercache_pages_fast(OUT bufferid integer, OUT relfilenode oid, OUT reltablespace oid, OUT reldatabase oid,
         OUT relforknumber int2, OUT relblocknumber int8, OUT isdirty bool, OUT usagecount int2,
         OUT pinning_backends int4)
RETURNS SETOF RECORD
AS '$libdir/pg_buffercache', 'pg_buffercache_pages'
LANGUAGE C PARALLEL SAFE;

60GB of s_b, mostly filled, with 257c8231bf97a77378f6fedb826b1243f0a41612
reverted.

SELECT count(*) FROM (SELECT pg_buffercache_pages_fast());
Time: 1518.704 ms (00:01.519)

SELECT count(*) FROM pg_buffercache_pages_fast();
Time: 2008.101 ms (00:02.008)


> > I didn't dig into the history to find out why we didn't modernize
> > pg_buffercache_pages(). I don't see any hazard though.
>
> Committed this modernization patch, thanks!
>
> It would be nice to have a proper row-at-a-time mode that would avoid
> materializing the result, but collecting all the data in a temporary array
> is clearly worse than just putting them to the tuplestore directly. The only
> reason I can think of why we'd prefer to use a temporary array like that is
> to get a more consistent snapshot of all the buffers, by keeping the time
> spent scanning the buffers as short as possible. But we're not getting a
> consistent view anyway, it's just a matter of degree.

Seems like a reasonably large difference in degree whether you have a snapshot
collected in one loop, or you do things like spilling a tuplestore to disk in
between.

Greetings,

Andres Freund

Re: pg_buffercache: Add per-relation summary stats

Heikki Linnakangas <hlinnaka@iki.fi> — 2026-04-07T15:55:23Z

On 07/04/2026 16:47, Andres Freund wrote:
> On 2026-04-07 16:07:45 +0300, Heikki Linnakangas wrote:
>> On 28/03/2026 06:18, Ashutosh Bapat wrote:
>>> Parallely myself and Palak Chaturvedi developed a quick patch to
>>> modernise pg_buffercache_pages() and use tuplestore so that it doesn't
>>> have to rely on NBuffers being the same between start of the scan,
>>> when memory allocated, when the scan ends - a condition possible with
>>> resizing buffer cache. It seems to improve the timings by about 10-30%
>>> on my laptop for 128MB buffercache size. Without this patch the time
>>> taken to execute Lukas's query varies between 10-15ms on my laptop.
>>> With this patch it varies between 8-9ms. So the timing is more stable
>>> as a side effect. It's not a 10x improvement that we are looking for
>>> but it looks like a step in the right direction. That improvement
>>> seems to come purely because we avoid creating a heap tuple. I wonder
>>> if there are some places up in the execution tree where full
>>> heaptuples get formed again instead of continuing to use minimal
>>> tuples or places where we perform some extra actions that are not
>>> required.
> 
> I don't think that's the reason for the improvement - tuplestore_putvalues()
> forms a minimal tuple, and the cost to form a minimal tuple and a heap tuple
> aren't meaningfully different.

Yeah, I wasn't fully convinced of that part either, which is why I left 
it out of the commit message. I mostly wanted to get rid of the 
double-buffering where we first accumulated all the data in an array.

> I think the problem is that we materialize rowmode SRFs as a tuplestore if
> they are in the from list.  You can easily see this even with just
> generate_series():
> 
> postgres[1520825][1]=# SELECT count(*) FROM generate_series(1, 1000000);
> ┌─────────┐
> │  count  │
> ├─────────┤
> │ 1000000 │
> └─────────┘
> (1 row)
> 
> Time: 117.939 ms
> postgres[1520825][1]=# SELECT count(*) FROM (SELECT generate_series(1, 1000000));
> ┌─────────┐
> │  count  │
> ├─────────┤
> │ 1000000 │
> └─────────┘
> (1 row)
> 
> Time: 58.914 ms

Oh, to be honest I didn't remember that we *don't* materialize when it's 
in the target list.

> Of course, because pg_buffercache_pages() is archaicially defined without
> defininig its output columns, you can't actually use it in the select list.
> 
> But that can be fixed:
> 
> CREATE FUNCTION pg_buffercache_pages_fast(OUT bufferid integer, OUT relfilenode oid, OUT reltablespace oid, OUT reldatabase oid,
>           OUT relforknumber int2, OUT relblocknumber int8, OUT isdirty bool, OUT usagecount int2,
>           OUT pinning_backends int4)
> RETURNS SETOF RECORD
> AS '$libdir/pg_buffercache', 'pg_buffercache_pages'
> LANGUAGE C PARALLEL SAFE;
> 
> 60GB of s_b, mostly filled, with 257c8231bf97a77378f6fedb826b1243f0a41612
> reverted.
> 
> SELECT count(*) FROM (SELECT pg_buffercache_pages_fast());
> Time: 1518.704 ms (00:01.519)
> 
> SELECT count(*) FROM pg_buffercache_pages_fast();
> Time: 2008.101 ms (00:02.008)

Hmm, we could easily go back to ValuePerCall mode, while still getting 
rid of the temporary array and the other modernization. But "SELECT * 
FROM pg_buffercache_pages_fast()" doesn't actually produce the result we 
want:

postgres=# SELECT pg_buffercache_pages_fast() limit 1;
  pg_buffercache_pages_fast
---------------------------
  (1,1262,1664,0,0,0,f,5,0)
(1 row)

Can we turn that into the right shaep for the pg_buffercache view? This 
works:

postgres=# SELECT (pg_buffercache_pages_fast()).* limit 1;
  bufferid | relfilenode | reltablespace | reldatabase | relforknumber | 
relblocknumber | isdirty | usagecount | pinning_backends
----------+-------------+---------------+-------------+---------------+----------------+---------+------------+------------------
         1 |        1262 |          1664 |           0 |             0 | 
              0 | f       |          5 |                0
(1 row)

But that doesn't seem to be faster anymore, despite avoiding the 
tuplestore, because of overheads elsewhere in the executor.

>>> I didn't dig into the history to find out why we didn't modernize
>>> pg_buffercache_pages(). I don't see any hazard though.
>>
>> Committed this modernization patch, thanks!
>>
>> It would be nice to have a proper row-at-a-time mode that would avoid
>> materializing the result, but collecting all the data in a temporary array
>> is clearly worse than just putting them to the tuplestore directly. The only
>> reason I can think of why we'd prefer to use a temporary array like that is
>> to get a more consistent snapshot of all the buffers, by keeping the time
>> spent scanning the buffers as short as possible. But we're not getting a
>> consistent view anyway, it's just a matter of degree.
> 
> Seems like a reasonably large difference in degree whether you have a snapshot
> collected in one loop, or you do things like spilling a tuplestore to disk in
> between.

Looking at the original discussion when pg_buffercache was introduced 
[1], the first patch version didn't have the array, but it was added in 
v2 to avoid holding BufMappingLock for long. But we gave up on getting a 
consistent snapshot and stopped holding the lock in commit 6e654546fb.

To summarize my current thinking, I think this is fine as committed. I'm 
not worried about the more "stretched out" snapshot that you get. And it 
would be nice if we had a better, faster ValuePerCall mode that also 
worked with FunctionScans, but we don't.

[1] https://www.postgresql.org/message-id/42297D6E.3000505%40coretech.co.nz

- Heikki

Re: pg_buffercache: Add per-relation summary stats

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2026-04-08T06:36:32Z

On Tue, Apr 7, 2026 at 9:25 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>
> On 07/04/2026 16:47, Andres Freund wrote:
> > On 2026-04-07 16:07:45 +0300, Heikki Linnakangas wrote:
> >> On 28/03/2026 06:18, Ashutosh Bapat wrote:
> >>> Parallely myself and Palak Chaturvedi developed a quick patch to
> >>> modernise pg_buffercache_pages() and use tuplestore so that it doesn't
> >>> have to rely on NBuffers being the same between start of the scan,
> >>> when memory allocated, when the scan ends - a condition possible with
> >>> resizing buffer cache. It seems to improve the timings by about 10-30%
> >>> on my laptop for 128MB buffercache size. Without this patch the time
> >>> taken to execute Lukas's query varies between 10-15ms on my laptop.
> >>> With this patch it varies between 8-9ms. So the timing is more stable
> >>> as a side effect. It's not a 10x improvement that we are looking for
> >>> but it looks like a step in the right direction. That improvement
> >>> seems to come purely because we avoid creating a heap tuple. I wonder
> >>> if there are some places up in the execution tree where full
> >>> heaptuples get formed again instead of continuing to use minimal
> >>> tuples or places where we perform some extra actions that are not
> >>> required.
> >
> > I don't think that's the reason for the improvement - tuplestore_putvalues()
> > forms a minimal tuple, and the cost to form a minimal tuple and a heap tuple
> > aren't meaningfully different.
>
> Yeah, I wasn't fully convinced of that part either, which is why I left
> it out of the commit message. I mostly wanted to get rid of the
> double-buffering where we first accumulated all the data in an array.
>

Just because of the name of the function, I thought
tuplestore_puttuple stores virtual tuple. But looking at the function
it's clearly using minimal tuples. Sorry for my misunderstanding.

> > I think the problem is that we materialize rowmode SRFs as a tuplestore if
> > they are in the from list.  You can easily see this even with just
> > generate_series():
> >
> > postgres[1520825][1]=# SELECT count(*) FROM generate_series(1, 1000000);
> > ┌─────────┐
> > │  count  │
> > ├─────────┤
> > │ 1000000 │
> > └─────────┘
> > (1 row)
> >
> > Time: 117.939 ms
> > postgres[1520825][1]=# SELECT count(*) FROM (SELECT generate_series(1, 1000000));
> > ┌─────────┐
> > │  count  │
> > ├─────────┤
> > │ 1000000 │
> > └─────────┘
> > (1 row)
> >
> > Time: 58.914 ms
>
> Oh, to be honest I didn't remember that we *don't* materialize when it's
> in the target list.
>

IIUC, query using SRF in targetlist runs faster because it does "not"
use tuplestore. I consistently saw 10%-30% performance improvement
after using tuplestore in pg_buffercache_pages(). Is that purely
because of avoiding an in-memory array?

--
Best Wishes,
Ashutosh Bapat

Re: pg_buffercache: Add per-relation summary stats

Robert Haas <robertmhaas@gmail.com> — 2026-06-18T14:09:43Z

On Fri, Mar 27, 2026 at 6:59 PM Tomas Vondra <tomas@vondra.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.

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

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.

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

-- 
Robert Haas
EDB: http://www.enterprisedb.com