Subject: pg_buffercache: Add per-relation summary stats

From: Lukas Fittl <[email-masked]>
Date: Sat, 28 Feb 2026 15:58:34 -0800

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

---

From: Jakub Wartak <[email-masked]>
Date: Mon, 2 Mar 2026 11:16:40 +0100

> work for larger database servers.
[..]
> 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.

---

From: Lukas Fittl <[email-masked]>
Date: Mon, 2 Mar 2026 18:39:30 -0800

> issues? (this is not FUD :), just more like a question based on Your's

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.

> and if so what would be the difference in terms of runtime there -- also

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

---

From: Bertrand Drouvot <[email-masked]>
Date: Mon, 9 Mar 2026 09:15:03 +0000

Hi,

> 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

---

From: Haibo Yan <[email-masked]>
Date: Mon, 16 Mar 2026 21:21:22 -0700

Hi Lukas,

I have read the patch, and I have a few questions/comments while going through it:

Could this use RelFileLocator plus ForkNumber instead of open-coding BufferRelStatsKey? That seems closer to existing PostgreSQL abstractions for physical relation identity.

I wonder whether pg_buffercache_relation_stats() is the best name here. The function is really aggregating by relation file identity plus fork, and it is producing a summary of the current buffer contents rather than what many readers might assume from “relation stats”. Would something with summary be clearer than stats?

Why are OUT relforknumber and OUT relfilenode exposed as int2 and oid respectively? Internally these are represented as ForkNumber and RelFileNumber, so I wonder whether the SQL interface should reflect that more clearly, or at least whether the current choice should be explained.

The comment says, “Hash key for pg_buffercache_relation_stats — groups by relation identity”, but that seems imprecise. It is really grouping by relfilenode plus fork, i.e. physical relation-file identity rather than relation identity in a more logical sense.

Is PARALLEL SAFE actually desirable here, as opposed to merely technically safe? A parallel query could cause multiple workers to perform full shared-buffer scans independently, which does not seem obviously desirable for this kind of diagnostic function.

Best regards,

Haibo Yan

> On Feb 28, 2026, at 3:58 PM, Lukas Fittl <[email-masked]> wrote:
> 
> Hi,
> 
> <v1-0001-pg_buffercache-Add-pg_buffercache_relation_stats-.patch>

---

From: Masahiko Sawada <[email-masked]>
Date: Tue, 24 Mar 2026 12:09:13 -0700

Hi Lukas,

> statistics on the number of buffers, how many are dirtied/pinned, and

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.

> buffers_dirty | buffers_pinned |   usagecount_avg

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

---

From: Ashutosh Bapat <[email-masked]>
Date: Wed, 25 Mar 2026 11:54:24 +0530

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

---

From: Lukas Fittl <[email-masked]>
Date: Tue, 24 Mar 2026 23:46:25 -0700

Hi Ashutosh,

> or distribution of relation buffers in the cache, do they write

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.

> example, with the resizable shared buffer project we have another

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.

> 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

---

From: Lukas Fittl <[email-masked]>
Date: Tue, 24 Mar 2026 23:52:13 -0700

Hi Haibo,

Thanks for your review!

> 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 c…

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 re…

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 i…

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…

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

---

From: Lukas Fittl <[email-masked]>
Date: Tue, 24 Mar 2026 23:54:18 -0700

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.

> reltablespace and reldatabase)?

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

> 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

---

From: Lukas Fittl <[email-masked]>
Date: Wed, 25 Mar 2026 00:29:20 -0700

Hi Masahiko-san, Paul and Khoa,

Thanks for the review!

> November, we think we don't need to bump the version again for this new

Makes sense, adjusted.

> Can we move these typedefs above function prototypes as other typedefs

Makes sense, done.

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

Good point, adjusted.

> entries can be as many as NBuffers in principle.

Sure, that makes sense.

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

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 25 Mar 2026 09:48:52 -0700

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

---

From: Ashutosh Bapat <[email-masked]>
Date: Thu, 26 Mar 2026 09:51:00 +0530

> 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

---

From: Dmitry Dolgov <[email-masked]>
Date: Fri, 27 Mar 2026 19:11:03 +0100

Thanks for the patch, I like the idea.

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

---

From: Ashutosh Bapat <[email-masked]>
Date: Sat, 28 Mar 2026 09:48:26 +0530

On Sat, Mar 28, 2026 at 4:28 AM Tomas Vondra <[email-masked]> wrote:
>
> On 3/26/26 05:21, Ashutosh Bapat wrote:
> 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
From 9112536d59124057f81b01c9e48feed31f0cc98d Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <[email-masked]>
Date: Wed, 11 Mar 2026 15:57:02 +0530
Subject: [PATCH v20260328] pg_buffercache_pages() modernization and
 optimization

Many of the set returning functions (SRFs) use InitMaterializedSRF() and
tuplestore to store the result. But pg_buffercache_pages() uses its own
code to initialize SRF state and does not use tuplestore. Because of the
later it has to create a full heap tuple when not necessary. A
tuplestore on the other hand has ability to store minimal tuple which
saves some CPU cycles forming and deforming a full heap tuple. Modernize
pg_buffercache_pages() to use SRF infrastructure and tuplestore.

Author: Ashutosh Bapat <[email-masked]>
Author: Palak Chaturvedi <[email-masked]>
---
 contrib/pg_buffercache/pg_buffercache_pages.c | 257 ++++++------------
 1 file changed, 81 insertions(+), 176 deletions(-)

diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c
index db4d711cce7..fae531573cb 100644
--- a/contrib/pg_buffercache/pg_buffercache_pages.c
+++ b/contrib/pg_buffercache/pg_buffercache_pages.c
@@ -37,39 +37,6 @@ PG_MODULE_MAGIC_EXT(
 					.version = PG_VERSION
 );
 
-/*
- * Record structure holding the to be exposed cache data.
- */
-typedef struct
-{
-	uint32		bufferid;
-	RelFileNumber relfilenumber;
-	Oid			reltablespace;
-	Oid			reldatabase;
-	ForkNumber	forknum;
-	BlockNumber blocknum;
-	bool		isvalid;
-	bool		isdirty;
-	uint16		usagecount;
-
-	/*
-	 * An int32 is sufficiently large, as MAX_BACKENDS prevents a buffer from
-	 * being pinned by too many backends and each backend will only pin once
-	 * because of bufmgr.c's PrivateRefCount infrastructure.
-	 */
-	int32		pinning_backends;
-} BufferCachePagesRec;
-
-
-/*
- * Function context for data persisting over repeated calls.
- */
-typedef struct
-{
-	TupleDesc	tupdesc;
-	BufferCachePagesRec *record;
-} BufferCachePagesContext;
-
 /*
  * Record structure holding the to be exposed cache data for OS pages.  This
  * structure is used by pg_buffercache_os_pages(), where NUMA information may
@@ -117,142 +84,90 @@ static bool firstNumaTouch = true;
 Datum
 pg_buffercache_pages(PG_FUNCTION_ARGS)
 {
-	FuncCallContext *funcctx;
-	Datum		result;
-	MemoryContext oldcontext;
-	BufferCachePagesContext *fctx;	/* User function context. */
-	TupleDesc	tupledesc;
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
 	TupleDesc	expected_tupledesc;
-	HeapTuple	tuple;
-
-	if (SRF_IS_FIRSTCALL())
-	{
-		int			i;
-
-		funcctx = SRF_FIRSTCALL_INIT();
-
-		/* Switch context when allocating stuff to be used in later calls */
-		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
-
-		/* Create a user function context for cross-call persistence */
-		fctx = palloc_object(BufferCachePagesContext);
-
-		/*
-		 * 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");
-
-		/* Construct a tuple descriptor for the result rows. */
-		tupledesc = CreateTemplateTupleDesc(expected_tupledesc->natts);
-		TupleDescInitEntry(tupledesc, (AttrNumber) 1, "bufferid",
-						   INT4OID, -1, 0);
-		TupleDescInitEntry(tupledesc, (AttrNumber) 2, "relfilenode",
-						   OIDOID, -1, 0);
-		TupleDescInitEntry(tupledesc, (AttrNumber) 3, "reltablespace",
-						   OIDOID, -1, 0);
-		TupleDescInitEntry(tupledesc, (AttrNumber) 4, "reldatabase",
-						   OIDOID, -1, 0);
-		TupleDescInitEntry(tupledesc, (AttrNumber) 5, "relforknumber",
-						   INT2OID, -1, 0);
-		TupleDescInitEntry(tupledesc, (AttrNumber) 6, "relblocknumber",
-						   INT8OID, -1, 0);
-		TupleDescInitEntry(tupledesc, (AttrNumber) 7, "isdirty",
-						   BOOLOID, -1, 0);
-		TupleDescInitEntry(tupledesc, (AttrNumber) 8, "usage_count",
-						   INT2OID, -1, 0);
-
-		if (expected_tupledesc->natts == NUM_BUFFERCACHE_PAGES_ELEM)
-			TupleDescInitEntry(tupledesc, (AttrNumber) 9, "pinning_backends",
-							   INT4OID, -1, 0);
-
-		TupleDescFinalize(tupledesc);
-		fctx->tupdesc = BlessTupleDesc(tupledesc);
-
-		/* Allocate NBuffers worth of BufferCachePagesRec records. */
-		fctx->record = (BufferCachePagesRec *)
-			MemoryContextAllocHuge(CurrentMemoryContext,
-								   sizeof(BufferCachePagesRec) * NBuffers);
-
-		/* Set max calls and remember the user function context. */
-		funcctx->max_calls = NBuffers;
-		funcctx->user_fctx = fctx;
+	Datum		values[NUM_BUFFERCACHE_PAGES_ELEM];
+	bool		nulls[NUM_BUFFERCACHE_PAGES_ELEM];
+	int			i;
 
-		/* Return to original context when allocating transient memory */
-		MemoryContextSwitchTo(oldcontext);
-
-		/*
-		 * Scan through all the buffers, saving the relevant fields in the
-		 * fctx->record structure.
-		 *
-		 * We don't hold the partition locks, so we don't get a consistent
-		 * snapshot across all buffers, but we do grab the buffer header
-		 * locks, so the information of each buffer is self-consistent.
-		 */
-		for (i = 0; i < NBuffers; i++)
-		{
-			BufferDesc *bufHdr;
-			uint64		buf_state;
+	/*
+	 * 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");
 
-			CHECK_FOR_INTERRUPTS();
+	if (expected_tupledesc->natts < NUM_BUFFERCACHE_PAGES_MIN_ELEM ||
+		expected_tupledesc->natts > NUM_BUFFERCACHE_PAGES_ELEM)
+		elog(ERROR, "incorrect number of output arguments");
 
-			bufHdr = GetBufferDescriptor(i);
-			/* Lock each buffer header before inspecting. */
-			buf_state = LockBufHdr(bufHdr);
+	InitMaterializedSRF(fcinfo, 0);
 
-			fctx->record[i].bufferid = BufferDescriptorGetBuffer(bufHdr);
-			fctx->record[i].relfilenumber = BufTagGetRelNumber(&bufHdr->tag);
-			fctx->record[i].reltablespace = bufHdr->tag.spcOid;
-			fctx->record[i].reldatabase = bufHdr->tag.dbOid;
-			fctx->record[i].forknum = BufTagGetForkNum(&bufHdr->tag);
-			fctx->record[i].blocknum = bufHdr->tag.blockNum;
-			fctx->record[i].usagecount = BUF_STATE_GET_USAGECOUNT(buf_state);
-			fctx->record[i].pinning_backends = BUF_STATE_GET_REFCOUNT(buf_state);
+	/*
+	 * Scan through all the buffers, adding one row for each of the buffers to
+	 * the tuplestore.
+	 *
+	 * We don't hold the partition locks, so we don't get a consistent
+	 * snapshot across all buffers, but we do grab the buffer header locks, so
+	 * the information of each buffer is self-consistent.
+	 */
+	for (i = 0; i < NBuffers; i++)
+	{
+		BufferDesc *bufHdr;
+		uint64		buf_state;
+		uint32		bufferid;
+		RelFileNumber relfilenumber;
+		Oid			reltablespace;
+		Oid			reldatabase;
+		ForkNumber	forknum;
+		BlockNumber blocknum;
+		bool		isvalid;
+		bool		isdirty;
+		uint16		usagecount;
+		int32		pinning_backends;
 
-			if (buf_state & BM_DIRTY)
-				fctx->record[i].isdirty = true;
-			else
-				fctx->record[i].isdirty = false;
+		CHECK_FOR_INTERRUPTS();
 
-			/* Note if the buffer is valid, and has storage created */
-			if ((buf_state & BM_VALID) && (buf_state & BM_TAG_VALID))
-				fctx->record[i].isvalid = true;
-			else
-				fctx->record[i].isvalid = false;
+		bufHdr = GetBufferDescriptor(i);
+		/* Lock each buffer header before inspecting. */
+		buf_state = LockBufHdr(bufHdr);
+
+		bufferid = BufferDescriptorGetBuffer(bufHdr);
+		relfilenumber = BufTagGetRelNumber(&bufHdr->tag);
+		reltablespace = bufHdr->tag.spcOid;
+		reldatabase = bufHdr->tag.dbOid;
+		forknum = BufTagGetForkNum(&bufHdr->tag);
+		blocknum = bufHdr->tag.blockNum;
+		usagecount = BUF_STATE_GET_USAGECOUNT(buf_state);
+		pinning_backends = BUF_STATE_GET_REFCOUNT(buf_state);
 
-			UnlockBufHdr(bufHdr);
-		}
-	}
+		if (buf_state & BM_DIRTY)
+			isdirty = true;
+		else
+			isdirty = false;
 
-	funcctx = SRF_PERCALL_SETUP();
+		/* Note if the buffer is valid, and has storage created */
+		if ((buf_state & BM_VALID) && (buf_state & BM_TAG_VALID))
+			isvalid = true;
+		else
+			isvalid = false;
 
-	/* Get the saved state */
-	fctx = funcctx->user_fctx;
+		UnlockBufHdr(bufHdr);
 
-	if (funcctx->call_cntr < funcctx->max_calls)
-	{
-		uint32		i = funcctx->call_cntr;
-		Datum		values[NUM_BUFFERCACHE_PAGES_ELEM];
-		bool		nulls[NUM_BUFFERCACHE_PAGES_ELEM];
+		/* Build the tuple and add it to tuplestore */
+		memset(nulls, 0, sizeof(nulls));
 
-		values[0] = Int32GetDatum(fctx->record[i].bufferid);
-		nulls[0] = false;
+		values[0] = Int32GetDatum(bufferid);
 
 		/*
 		 * Set all fields except the bufferid to null if the buffer is unused
 		 * or not valid.
 		 */
-		if (fctx->record[i].blocknum == InvalidBlockNumber ||
-			fctx->record[i].isvalid == false)
+		if (blocknum == InvalidBlockNumber || isvalid == false)
 		{
 			nulls[1] = true;
 			nulls[2] = true;
@@ -262,37 +177,27 @@ pg_buffercache_pages(PG_FUNCTION_ARGS)
 			nulls[6] = true;
 			nulls[7] = true;
 			/* unused for v1.0 callers, but the array is always long enough */
-			nulls[8] = true;
+			if (expected_tupledesc->natts == NUM_BUFFERCACHE_PAGES_ELEM)
+				nulls[8] = true;
 		}
 		else
 		{
-			values[1] = ObjectIdGetDatum(fctx->record[i].relfilenumber);
-			nulls[1] = false;
-			values[2] = ObjectIdGetDatum(fctx->record[i].reltablespace);
-			nulls[2] = false;
-			values[3] = ObjectIdGetDatum(fctx->record[i].reldatabase);
-			nulls[3] = false;
-			values[4] = Int16GetDatum(fctx->record[i].forknum);
-			nulls[4] = false;
-			values[5] = Int64GetDatum((int64) fctx->record[i].blocknum);
-			nulls[5] = false;
-			values[6] = BoolGetDatum(fctx->record[i].isdirty);
-			nulls[6] = false;
-			values[7] = UInt16GetDatum(fctx->record[i].usagecount);
-			nulls[7] = false;
+			values[1] = ObjectIdGetDatum(relfilenumber);
+			values[2] = ObjectIdGetDatum(reltablespace);
+			values[3] = ObjectIdGetDatum(reldatabase);
+			values[4] = Int16GetDatum(forknum);
+			values[5] = Int64GetDatum((int64) blocknum);
+			values[6] = BoolGetDatum(isdirty);
+			values[7] = UInt16GetDatum(usagecount);
 			/* unused for v1.0 callers, but the array is always long enough */
-			values[8] = Int32GetDatum(fctx->record[i].pinning_backends);
-			nulls[8] = false;
+			if (expected_tupledesc->natts == NUM_BUFFERCACHE_PAGES_ELEM)
+				values[8] = Int32GetDatum(pinning_backends);
 		}
 
-		/* Build and return the tuple. */
-		tuple = heap_form_tuple(fctx->tupdesc, values, nulls);
-		result = HeapTupleGetDatum(tuple);
-
-		SRF_RETURN_NEXT(funcctx, result);
+		tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
 	}
-	else
-		SRF_RETURN_DONE(funcctx);
+
+	return (Datum) 0;
 }
 
 /*

base-commit: 999dec9ec6a81668057427c2e9312b20635fba02
-- 
2.34.1

---

From: Masahiko Sawada <[email-masked]>
Date: Fri, 27 Mar 2026 22:36:51 -0700

On Fri, Mar 27, 2026 at 3:58 PM Tomas Vondra <[email-masked]> wrote:
> 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,

---

From: Ashutosh Bapat <[email-masked]>
Date: Sat, 28 Mar 2026 21:42:07 +0530

> > warrant a custom *faster* function.

Well-said. Thanks.

> 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

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.

> work_mem or other GUC parameters.

+1.

-- 
Best Wishes,
Ashutosh Bapat

---

From: Lukas Fittl <[email-masked]>
Date: Sat, 28 Mar 2026 11:51:36 -0700

> 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

---

From: Lukas Fittl <[email-masked]>
Date: Sat, 28 Mar 2026 12:14:14 -0700

> 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

---

From: Lukas Fittl <[email-masked]>
Date: Sat, 28 Mar 2026 12:38:04 -0700

Hi Tomas,

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

> 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

---

From: Heikki Linnakangas <[email-masked]>
Date: Tue, 7 Apr 2026 16:07:45 +0300

> 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

---

From: Ashutosh Bapat <[email-masked]>
Date: Tue, 7 Apr 2026 18:53:46 +0530

> possible. But we're not getting a consistent view anyway, it's just a

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.

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

---

From: Andres Freund <[email-masked]>
Date: Tue, 7 Apr 2026 09:47:26 -0400

Hi,

> > tuples or places where we perform some extra actions that are not

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)

> 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

---

From: Heikki Linnakangas <[email-masked]>
Date: Tue, 7 Apr 2026 18:55:23 +0300

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

> postgres[1520825][1]=# SELECT count(*) FROM generate_series(1, 1000000);

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

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

> collected in one loop, or you do things like spilling a tuplestore to disk in

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

---

From: Ashutosh Bapat <[email-masked]>
Date: Wed, 8 Apr 2026 12:06:32 +0530

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

> Oh, to be honest I didn't remember that we *don't* materialize when it's

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

---

From: Robert Haas <[email-masked]>
Date: Thu, 18 Jun 2026 10:09:43 -0400

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

> 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