public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v2] pg_dump: fix dependencies on FKs to multi-level partitioned tables
27+ messages / 11 participants
[nested] [flat]
* [PATCH v2] pg_dump: fix dependencies on FKs to multi-level partitioned tables
@ 2020-08-14 17:26 Alvaro Herrera <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Alvaro Herrera @ 2020-08-14 17:26 UTC (permalink / raw)
Parallel-restoring a foreign key that references a partitioned table
with several levels of partitions can fail:
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 6684; 2606 29166 FK CONSTRAINT fk fk_a_fkey postgres
pg_restore: error: could not execute query: ERROR: there is no unique constraint matching given keys for referenced table "pk"
Command was: ALTER TABLE fkpart3.fk
ADD CONSTRAINT fk_a_fkey FOREIGN KEY (a) REFERENCES fkpart3.pk(a);
This happens in parallel restore mode because some index partitions
aren't yet attached to the topmost partitioned index that the FK uses,
and so the index is still invalid. The current code marks the FK as
dependent on the first level of index-attach dump objects; the bug is
fixed by recursively marking the FK on their children.
Reported-by: Tom Lane <[email protected]>
Author: �lvaro Herrera <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/bin/pg_dump/pg_dump.c | 39 ++++++++++++++++++++++++++++++++-------
1 file changed, 32 insertions(+), 7 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 9c8436dde6..2cb3f9b083 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -235,6 +235,7 @@ static DumpableObject *createBoundaryObjects(void);
static void addBoundaryDependencies(DumpableObject **dobjs, int numObjs,
DumpableObject *boundaryObjs);
+static void addConstrChildIdxDeps(DumpableObject *dobj, IndxInfo *refidx);
static void getDomainConstraints(Archive *fout, TypeInfo *tyinfo);
static void getTableData(DumpOptions *dopt, TableInfo *tblinfo, int numTables, char relkind);
static void makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo);
@@ -7517,25 +7518,20 @@ getConstraints(Archive *fout, TableInfo tblinfo[], int numTables)
reftable = findTableByOid(constrinfo[j].confrelid);
if (reftable && reftable->relkind == RELKIND_PARTITIONED_TABLE)
{
- IndxInfo *refidx;
Oid indexOid = atooid(PQgetvalue(res, j, i_conindid));
if (indexOid != InvalidOid)
{
for (int k = 0; k < reftable->numIndexes; k++)
{
- SimplePtrListCell *cell;
+ IndxInfo *refidx;
/* not our index? */
if (reftable->indexes[k].dobj.catId.oid != indexOid)
continue;
refidx = &reftable->indexes[k];
- for (cell = refidx->partattaches.head; cell;
- cell = cell->next)
- addObjectDependency(&constrinfo[j].dobj,
- ((DumpableObject *)
- cell->ptr)->dumpId);
+ addConstrChildIdxDeps(&constrinfo[j].dobj, refidx);
break;
}
}
@@ -7548,6 +7544,35 @@ getConstraints(Archive *fout, TableInfo tblinfo[], int numTables)
destroyPQExpBuffer(query);
}
+/*
+ * addConstrChildIdxDeps
+ *
+ * Recursive subroutine for getConstraints
+ *
+ * Given an object representing a foreign key constraint and an index on the
+ * partitioned table it references, mark the constraint object as dependent
+ * on the DO_INDEX_ATTACH object of each index partition, recursively
+ * drilling down to their partitions if any. This ensures that the FK is not
+ * restored until the index is fully marked valid.
+ */
+static void
+addConstrChildIdxDeps(DumpableObject *dobj, IndxInfo *refidx)
+{
+ SimplePtrListCell *cell;
+
+ Assert(dobj->objType == DO_FK_CONSTRAINT);
+
+ for (cell = refidx->partattaches.head; cell; cell = cell->next)
+ {
+ IndexAttachInfo *attach = (IndexAttachInfo *) cell->ptr;
+
+ addObjectDependency(dobj, attach->dobj.dumpId);
+
+ if (attach->partitionIdx->partattaches.head != NULL)
+ addConstrChildIdxDeps(dobj, attach->partitionIdx);
+ }
+}
+
/*
* getDomainConstraints
*
--
2.20.1
--azLHFNyN32YCQGCU--
^ permalink raw reply [nested|flat] 27+ messages in thread
* pg_buffercache: Add per-relation summary stats
@ 2026-02-28 23:58 Lukas Fittl <[email protected]>
0 siblings, 4 replies; 27+ messages in thread
From: Lukas Fittl @ 2026-02-28 23:58 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>
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
Attachments:
[application/octet-stream] v1-0001-pg_buffercache-Add-pg_buffercache_relation_stats-.patch (17.2K, ../../CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com/2-v1-0001-pg_buffercache-Add-pg_buffercache_relation_stats-.patch)
download | inline diff:
From 5e5a97dfd280b31c1cc2e6f04f5efd6b50a895b7 Mon Sep 17 00:00:00 2001
From: Lukas Fittl <[email protected]>
Date: Sat, 28 Feb 2026 15:33:56 -0800
Subject: [PATCH v1] pg_buffercache: Add pg_buffercache_relation_stats()
function
This function returns an aggregation of buffer contents, grouped on a
per-relfilenode basis. This is often useful to understand which tables
or indexes are currently in cache, and can show cache disruptions due
to query activity when sampled over time. The existing pg_buffercache()
function can be utilized for this by grouping the result, but due to
the amount of buffer entries (one per page) this can be prohibitively
expensive on large machines. Even on a small shared buffers (128MB) the
new function is 10x faster. Similar to the existing summary functions
this new function does not hold a lock whilst gathering its statistics.
Author: Lukas Fittl <[email protected]>
Reviewed by:
Discussion:
---
contrib/pg_buffercache/Makefile | 3 +-
.../expected/pg_buffercache.out | 14 ++
contrib/pg_buffercache/meson.build | 1 +
.../pg_buffercache--1.7--1.8.sql | 20 +++
contrib/pg_buffercache/pg_buffercache.control | 2 +-
contrib/pg_buffercache/pg_buffercache_pages.c | 134 ++++++++++++++++++
contrib/pg_buffercache/sql/pg_buffercache.sql | 4 +
doc/src/sgml/pgbuffercache.sgml | 130 +++++++++++++++++
src/tools/pgindent/typedefs.list | 2 +
9 files changed, 308 insertions(+), 2 deletions(-)
create mode 100644 contrib/pg_buffercache/pg_buffercache--1.7--1.8.sql
diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile
index 0e618f66aec..7fd5cdfc43d 100644
--- a/contrib/pg_buffercache/Makefile
+++ b/contrib/pg_buffercache/Makefile
@@ -9,7 +9,8 @@ EXTENSION = pg_buffercache
DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \
pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \
pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql \
- 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
PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time"
REGRESS = pg_buffercache pg_buffercache_numa
diff --git a/contrib/pg_buffercache/expected/pg_buffercache.out b/contrib/pg_buffercache/expected/pg_buffercache.out
index 886dea770f6..cb5507a0d92 100644
--- a/contrib/pg_buffercache/expected/pg_buffercache.out
+++ b/contrib/pg_buffercache/expected/pg_buffercache.out
@@ -33,6 +33,12 @@ SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
t
(1 row)
+SELECT count(*) > 0 FROM pg_buffercache_relation_stats() WHERE buffers >= 0;
+ ?column?
+----------
+ t
+(1 row)
+
-- Check that the functions / views can't be accessed by default. To avoid
-- having to create a dedicated user, use the pg_database_owner pseudo-role.
SET ROLE pg_database_owner;
@@ -46,6 +52,8 @@ SELECT * FROM pg_buffercache_summary();
ERROR: permission denied for function pg_buffercache_summary
SELECT * FROM pg_buffercache_usage_counts();
ERROR: permission denied for function pg_buffercache_usage_counts
+SELECT * FROM pg_buffercache_relation_stats();
+ERROR: permission denied for function pg_buffercache_relation_stats
RESET role;
-- Check that pg_monitor is allowed to query view / function
SET ROLE pg_monitor;
@@ -73,6 +81,12 @@ SELECT count(*) > 0 FROM pg_buffercache_usage_counts();
t
(1 row)
+SELECT count(*) > 0 FROM pg_buffercache_relation_stats();
+ ?column?
+----------
+ t
+(1 row)
+
RESET role;
------
---- Test pg_buffercache_evict* and pg_buffercache_mark_dirty* functions
diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build
index e681205abb2..361628b8bea 100644
--- a/contrib/pg_buffercache/meson.build
+++ b/contrib/pg_buffercache/meson.build
@@ -25,6 +25,7 @@ install_data(
'pg_buffercache--1.4--1.5.sql',
'pg_buffercache--1.5--1.6.sql',
'pg_buffercache--1.6--1.7.sql',
+ 'pg_buffercache--1.7--1.8.sql',
'pg_buffercache.control',
kwargs: contrib_data_args,
)
diff --git a/contrib/pg_buffercache/pg_buffercache--1.7--1.8.sql b/contrib/pg_buffercache/pg_buffercache--1.7--1.8.sql
new file mode 100644
index 00000000000..9619d1c3e85
--- /dev/null
+++ b/contrib/pg_buffercache/pg_buffercache--1.7--1.8.sql
@@ -0,0 +1,20 @@
+/* contrib/pg_buffercache/pg_buffercache--1.7--1.8.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.8'" to load this file. \quit
+
+CREATE FUNCTION pg_buffercache_relation_stats(
+ OUT relfilenode oid,
+ OUT reltablespace oid,
+ OUT reldatabase oid,
+ OUT relforknumber int2,
+ OUT buffers int4,
+ OUT buffers_dirty int4,
+ OUT buffers_pinned int4,
+ OUT usagecount_avg float8)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_buffercache_relation_stats'
+LANGUAGE C PARALLEL SAFE;
+
+REVOKE ALL ON FUNCTION pg_buffercache_relation_stats() FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pg_buffercache_relation_stats() TO pg_monitor;
diff --git a/contrib/pg_buffercache/pg_buffercache.control b/contrib/pg_buffercache/pg_buffercache.control
index 11499550945..d2fa8ba53ba 100644
--- a/contrib/pg_buffercache/pg_buffercache.control
+++ b/contrib/pg_buffercache/pg_buffercache.control
@@ -1,5 +1,5 @@
# pg_buffercache extension
comment = 'examine the shared buffer cache'
-default_version = '1.7'
+default_version = '1.8'
module_pathname = '$libdir/pg_buffercache'
relocatable = true
diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c
index 89b86855243..b16a421cb77 100644
--- a/contrib/pg_buffercache/pg_buffercache_pages.c
+++ b/contrib/pg_buffercache/pg_buffercache_pages.c
@@ -15,6 +15,7 @@
#include "port/pg_numa.h"
#include "storage/buf_internals.h"
#include "storage/bufmgr.h"
+#include "utils/hsearch.h"
#include "utils/rel.h"
@@ -22,6 +23,7 @@
#define NUM_BUFFERCACHE_PAGES_ELEM 9
#define NUM_BUFFERCACHE_SUMMARY_ELEM 5
#define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4
+#define NUM_BUFFERCACHE_RELATION_STATS_ELEM 8
#define NUM_BUFFERCACHE_EVICT_ELEM 2
#define NUM_BUFFERCACHE_EVICT_RELATION_ELEM 3
#define NUM_BUFFERCACHE_EVICT_ALL_ELEM 3
@@ -107,8 +109,33 @@ PG_FUNCTION_INFO_V1(pg_buffercache_evict_all);
PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty);
PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty_relation);
PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty_all);
+PG_FUNCTION_INFO_V1(pg_buffercache_relation_stats);
+/*
+ * 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;
+
/* Only need to touch memory once per backend process lifetime */
static bool firstNumaTouch = true;
@@ -958,3 +985,110 @@ pg_buffercache_mark_dirty_all(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(result);
}
+
+/*
+ * pg_buffercache_relation_stats
+ *
+ * Produces a set of rows that summarize buffer cache usage per relation-fork
+ * combination. This enables monitoring scripts to only get the summary stats,
+ * instead of accumulating in a query with the full buffer information.
+ */
+Datum
+pg_buffercache_relation_stats(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ HTAB *relstats_hash;
+ HASHCTL hash_ctl;
+ HASH_SEQ_STATUS hash_seq;
+ BufferRelStatsEntry *entry;
+ Datum values[NUM_BUFFERCACHE_RELATION_STATS_ELEM];
+ bool nulls[NUM_BUFFERCACHE_RELATION_STATS_ELEM] = {0};
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ /* Create a hash table to aggregate stats by relation-fork */
+ hash_ctl.keysize = sizeof(BufferRelStatsKey);
+ hash_ctl.entrysize = sizeof(BufferRelStatsEntry);
+ hash_ctl.hcxt = CurrentMemoryContext;
+
+ relstats_hash = hash_create("pg_buffercache relation stats",
+ 128,
+ &hash_ctl,
+ HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
+
+ /* Single pass over all buffers */
+ for (int i = 0; i < NBuffers; i++)
+ {
+ BufferDesc *bufHdr;
+ uint64 buf_state;
+ BufferRelStatsKey key;
+ bool found;
+
+ CHECK_FOR_INTERRUPTS();
+
+ /*
+ * Read buffer state without locking, same as pg_buffercache_summary
+ * and pg_buffercache_usage_counts. Locking wouldn't provide a
+ * meaningfully more consistent result since buffers can change state
+ * immediately after we release the lock.
+ */
+ bufHdr = GetBufferDescriptor(i);
+ buf_state = pg_atomic_read_u64(&bufHdr->state);
+
+ /* Skip unused/invalid buffers */
+ if (!(buf_state & BM_VALID))
+ continue;
+
+ key.relfilenumber = BufTagGetRelNumber(&bufHdr->tag);
+ key.reltablespace = bufHdr->tag.spcOid;
+ key.reldatabase = bufHdr->tag.dbOid;
+ key.forknum = BufTagGetForkNum(&bufHdr->tag);
+
+ entry = (BufferRelStatsEntry *) hash_search(relstats_hash,
+ &key,
+ HASH_ENTER,
+ &found);
+
+ if (!found)
+ {
+ entry->buffers = 0;
+ entry->buffers_dirty = 0;
+ entry->buffers_pinned = 0;
+ entry->usagecount_total = 0;
+ }
+
+ entry->buffers++;
+ entry->usagecount_total += BUF_STATE_GET_USAGECOUNT(buf_state);
+
+ if (buf_state & BM_DIRTY)
+ entry->buffers_dirty++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ entry->buffers_pinned++;
+ }
+
+ /* Emit one row per hash entry */
+ hash_seq_init(&hash_seq, relstats_hash);
+ while ((entry = (BufferRelStatsEntry *) hash_seq_search(&hash_seq)) != NULL)
+ {
+ if (entry->buffers == 0)
+ continue;
+
+ values[0] = ObjectIdGetDatum(entry->key.relfilenumber);
+ values[1] = ObjectIdGetDatum(entry->key.reltablespace);
+ values[2] = ObjectIdGetDatum(entry->key.reldatabase);
+ values[3] = Int16GetDatum(entry->key.forknum);
+ values[4] = Int32GetDatum(entry->buffers);
+ values[5] = Int32GetDatum(entry->buffers_dirty);
+ values[6] = Int32GetDatum(entry->buffers_pinned);
+ values[7] = Float8GetDatum((double) entry->usagecount_total /
+ entry->buffers);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+ values, nulls);
+ }
+
+ hash_destroy(relstats_hash);
+
+ return (Datum) 0;
+}
diff --git a/contrib/pg_buffercache/sql/pg_buffercache.sql b/contrib/pg_buffercache/sql/pg_buffercache.sql
index 127d604905c..ea5950855d2 100644
--- a/contrib/pg_buffercache/sql/pg_buffercache.sql
+++ b/contrib/pg_buffercache/sql/pg_buffercache.sql
@@ -18,6 +18,8 @@ from pg_buffercache_summary();
SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
+SELECT count(*) > 0 FROM pg_buffercache_relation_stats() WHERE buffers >= 0;
+
-- Check that the functions / views can't be accessed by default. To avoid
-- having to create a dedicated user, use the pg_database_owner pseudo-role.
SET ROLE pg_database_owner;
@@ -26,6 +28,7 @@ SELECT * FROM pg_buffercache_os_pages;
SELECT * FROM pg_buffercache_pages() AS p (wrong int);
SELECT * FROM pg_buffercache_summary();
SELECT * FROM pg_buffercache_usage_counts();
+SELECT * FROM pg_buffercache_relation_stats();
RESET role;
-- Check that pg_monitor is allowed to query view / function
@@ -34,6 +37,7 @@ SELECT count(*) > 0 FROM pg_buffercache;
SELECT count(*) > 0 FROM pg_buffercache_os_pages;
SELECT buffers_used + buffers_unused > 0 FROM pg_buffercache_summary();
SELECT count(*) > 0 FROM pg_buffercache_usage_counts();
+SELECT count(*) > 0 FROM pg_buffercache_relation_stats();
RESET role;
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index 1e9aee10275..921ba9b5306 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -31,6 +31,10 @@
<primary>pg_buffercache_usage_counts</primary>
</indexterm>
+ <indexterm>
+ <primary>pg_buffercache_relation_stats</primary>
+ </indexterm>
+
<indexterm>
<primary>pg_buffercache_evict</primary>
</indexterm>
@@ -63,6 +67,7 @@
<structname>pg_buffercache_numa</structname> views), the
<function>pg_buffercache_summary()</function> function, the
<function>pg_buffercache_usage_counts()</function> function, the
+ <function>pg_buffercache_relation_stats()</function> function, the
<function>pg_buffercache_evict()</function> function, the
<function>pg_buffercache_evict_relation()</function> function, the
<function>pg_buffercache_evict_all()</function> function, the
@@ -102,6 +107,12 @@
count.
</para>
+ <para>
+ The <function>pg_buffercache_relation_stats()</function> function returns a
+ set of rows summarizing buffer cache usage aggregated by relation and fork
+ number.
+ </para>
+
<para>
By default, use of the above functions is restricted to superusers and roles
with privileges of the <literal>pg_monitor</literal> role. Access may be
@@ -564,6 +575,125 @@
</para>
</sect2>
+ <sect2 id="pgbuffercache-relation-stats">
+ <title>The <function>pg_buffercache_relation_stats()</function> Function</title>
+
+ <para>
+ The definitions of the columns exposed by the function are shown in
+ <xref linkend="pgbuffercache_relation_stats-columns"/>.
+ </para>
+
+ <table id="pgbuffercache_relation_stats-columns">
+ <title><function>pg_buffercache_relation_stats()</function> Output Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>relfilenode</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>relfilenode</structfield>)
+ </para>
+ <para>
+ Filenode number of the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>reltablespace</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ Tablespace OID of the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>reldatabase</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ Database OID of the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>relforknumber</structfield> <type>smallint</type>
+ </para>
+ <para>
+ Fork number within the relation; see
+ <filename>common/relpath.h</filename>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of buffers for the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_dirty</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of dirty buffers for the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_pinned</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of pinned buffers for the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>usagecount_avg</structfield> <type>float8</type>
+ </para>
+ <para>
+ Average usage count of the relation's buffers
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <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>
+
+ <para>
+ Like the <structname>pg_buffercache</structname> view,
+ <function>pg_buffercache_relation_stats()</function> does not acquire buffer
+ manager locks. Therefore concurrent activity can lead to minor inaccuracies
+ in the result.
+ </para>
+ </sect2>
+
<sect2 id="pgbuffercache-pg-buffercache-evict">
<title>The <function>pg_buffercache_evict()</function> Function</title>
<para>
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 77e3c04144e..19a87f702ee 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -357,6 +357,8 @@ BufferHeapTupleTableSlot
BufferLockMode
BufferLookupEnt
BufferManagerRelation
+BufferRelStatsEntry
+BufferRelStatsKey
BufferStrategyControl
BufferTag
BufferUsage
--
2.47.1
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-02 10:16 Jakub Wartak <[email protected]>
parent: Lukas Fittl <[email protected]>
3 siblings, 1 reply; 27+ messages in thread
From: Jakub Wartak @ 2026-03-02 10:16 UTC (permalink / raw)
To: Lukas Fittl <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Sun, Mar 1, 2026 at 12:59 AM Lukas Fittl <[email protected]> wrote:
>
> Hi,
>
> See attached a patch that implements a new function,
> pg_buffercache_relation_stats(), which returns per-relfilenode
> statistics on the number of buffers, how many are dirtied/pinned, and
> their avg usage count.
>
> This can be used in monitoring scripts to know which relations are
> kept in shared buffers, to understand performance issues better that
> occur due to relations getting evicted from the cache. In our own
> monitoring tool (pganalyze) we've offered a functionality like this
> based on the existing pg_buffercache() function for a bit over a year
> now [0], and people have found this very valuable - but it doesn't
> work for larger database servers.
[..]
> (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.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-03 02:39 Lukas Fittl <[email protected]>
parent: Jakub Wartak <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Lukas Fittl @ 2026-03-03 02:39 UTC (permalink / raw)
To: Jakub Wartak <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Mon, Mar 2, 2026 at 2:16 AM Jakub Wartak
<[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-09 09:15 Bertrand Drouvot <[email protected]>
parent: Lukas Fittl <[email protected]>
3 siblings, 1 reply; 27+ messages in thread
From: Bertrand Drouvot @ 2026-03-09 09:15 UTC (permalink / raw)
To: Lukas Fittl <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-17 04:21 Haibo Yan <[email protected]>
parent: Lukas Fittl <[email protected]>
3 siblings, 1 reply; 27+ messages in thread
From: Haibo Yan @ 2026-03-17 04:21 UTC (permalink / raw)
To: Lukas Fittl <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Hi Lukas,
I have read the patch, and I have a few questions/comments while going through it:
Could this use RelFileLocator plus ForkNumber instead of open-coding BufferRelStatsKey? That seems closer to existing PostgreSQL abstractions for physical relation identity.
I wonder whether pg_buffercache_relation_stats() is the best name here. The function is really aggregating by relation file identity plus fork, and it is producing a summary of the current buffer contents rather than what many readers might assume from “relation stats”. Would something with summary be clearer than stats?
Why are OUT relforknumber and OUT relfilenode exposed as int2 and oid respectively? Internally these are represented as ForkNumber and RelFileNumber, so I wonder whether the SQL interface should reflect that more clearly, or at least whether the current choice should be explained.
The comment says, “Hash key for pg_buffercache_relation_stats — groups by relation identity”, but that seems imprecise. It is really grouping by relfilenode plus fork, i.e. physical relation-file identity rather than relation identity in a more logical sense.
Is PARALLEL SAFE actually desirable here, as opposed to merely technically safe? A parallel query could cause multiple workers to perform full shared-buffer scans independently, which does not seem obviously desirable for this kind of diagnostic function.
Best regards,
Haibo Yan
> On Feb 28, 2026, at 3:58 PM, Lukas Fittl <[email protected]> wrote:
>
> Hi,
>
> See attached a patch that implements a new function,
> pg_buffercache_relation_stats(), which returns per-relfilenode
> statistics on the number of buffers, how many are dirtied/pinned, and
> their avg usage count.
>
> This can be used in monitoring scripts to know which relations are
> kept in shared buffers, to understand performance issues better that
> occur due to relations getting evicted from the cache. In our own
> monitoring tool (pganalyze) we've offered a functionality like this
> based on the existing pg_buffercache() function for a bit over a year
> now [0], and people have found this very valuable - but it doesn't
> work for larger database servers.
>
> Specifically, performing a query that gets this information can be
> prohibitively expensive when using large shared_buffers, and even on
> the default 128MB shared buffers there is a measurable difference:
>
> postgres=# WITH pg_buffercache_relation_stats AS (
> SELECT relfilenode, reltablespace, reldatabase, relforknumber,
> COUNT(*) AS buffers,
> COUNT(*) FILTER (WHERE isdirty) AS buffers_dirty,
> COUNT(*) FILTER (WHERE pinning_backends > 0) AS buffers_pinned,
> AVG(usagecount) AS usagecount_avg
> FROM pg_buffercache
> WHERE reldatabase IS NOT NULL
> GROUP BY 1, 2, 3, 4
>
> )
> SELECT * FROM pg_buffercache_relation_stats WHERE relfilenode = 2659;
>
> relfilenode | reltablespace | reldatabase | relforknumber | buffers |
> buffers_dirty | buffers_pinned | usagecount_avg
> -------------+---------------+-------------+---------------+---------+---------------+----------------+--------------------
> 2659 | 1663 | 5 | 0 | 8 |
> 0 | 0 | 5.0000000000000000
> 2659 | 1663 | 1 | 0 | 7 |
> 0 | 0 | 5.0000000000000000
> 2659 | 1663 | 229553 | 0 | 7 |
> 0 | 0 | 5.0000000000000000
> (3 rows)
>
> Time: 20.991 ms
>
> postgres=# SELECT * FROM pg_buffercache_relation_stats() WHERE
> relfilenode = 2659;
> relfilenode | reltablespace | reldatabase | relforknumber | buffers |
> buffers_dirty | buffers_pinned | usagecount_avg
> -------------+---------------+-------------+---------------+---------+---------------+----------------+----------------
> 2659 | 1663 | 1 | 0 | 7 |
> 0 | 0 | 5
> 2659 | 1663 | 229553 | 0 | 7 |
> 0 | 0 | 5
> 2659 | 1663 | 5 | 0 | 8 |
> 0 | 0 | 5
> (3 rows)
>
> Time: 2.912 ms
>
> With the new function this gets done before putting the data in the
> tuplestore used for the set-returning function.
>
> Thanks,
> Lukas
>
> [0]: https://pganalyze.com/blog/tracking-postgres-buffer-cache-statistics
>
> --
> Lukas Fittl
> <v1-0001-pg_buffercache-Add-pg_buffercache_relation_stats-.patch>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-24 19:09 Masahiko Sawada <[email protected]>
parent: Lukas Fittl <[email protected]>
3 siblings, 2 replies; 27+ messages in thread
From: Masahiko Sawada @ 2026-03-24 19:09 UTC (permalink / raw)
To: Lukas Fittl <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
Hi Lukas,
On Sat, Feb 28, 2026 at 3:59 PM Lukas Fittl <[email protected]> wrote:
>
> Hi,
>
> See attached a patch that implements a new function,
> pg_buffercache_relation_stats(), which returns per-relfilenode
> statistics on the number of buffers, how many are dirtied/pinned, and
> their avg usage count.
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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-25 06:24 Ashutosh Bapat <[email protected]>
parent: Masahiko Sawada <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Ashutosh Bapat @ 2026-03-25 06:24 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Lukas Fittl <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
On Wed, Mar 25, 2026 at 12:40 AM Masahiko Sawada <[email protected]> wrote:
>
> Hi Lukas,
>
> On Sat, Feb 28, 2026 at 3:59 PM Lukas Fittl <[email protected]> wrote:
> >
> > Hi,
> >
> > See attached a patch that implements a new function,
> > pg_buffercache_relation_stats(), which returns per-relfilenode
> > statistics on the number of buffers, how many are dirtied/pinned, and
> > their avg usage count.
>
> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-25 06:46 Lukas Fittl <[email protected]>
parent: Ashutosh Bapat <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Lukas Fittl @ 2026-03-25 06:46 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
Hi Ashutosh,
On Tue, Mar 24, 2026 at 11:24 PM Ashutosh Bapat
<[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-25 06:52 Lukas Fittl <[email protected]>
parent: Haibo Yan <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Lukas Fittl @ 2026-03-25 06:52 UTC (permalink / raw)
To: Haibo Yan <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Hi Haibo,
Thanks for your review!
On Mon, Mar 16, 2026 at 9:21 PM Haibo Yan <[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-25 06:54 Lukas Fittl <[email protected]>
parent: Bertrand Drouvot <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Lukas Fittl @ 2026-03-25 06:54 UTC (permalink / raw)
To: Bertrand Drouvot <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
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
<[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-25 07:29 Lukas Fittl <[email protected]>
parent: Masahiko Sawada <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Lukas Fittl @ 2026-03-25 07:29 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
Hi Masahiko-san, Paul and Khoa,
Thanks for the review!
On Tue, Mar 24, 2026 at 12:09 PM Masahiko Sawada <[email protected]> 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
Attachments:
[application/octet-stream] v2-0001-pg_buffercache-Add-pg_buffercache_relations-funct.patch (16.0K, ../../CAP53PkyoA2Thy8p04gL8d965DjUxX2=WcJzY6PbK-21v5fzTfA@mail.gmail.com/2-v2-0001-pg_buffercache-Add-pg_buffercache_relations-funct.patch)
download | inline diff:
From 1611911dc9aef2f87db1bcb68442c2dd483ae0ff Mon Sep 17 00:00:00 2001
From: Lukas Fittl <[email protected]>
Date: Sat, 28 Feb 2026 15:33:56 -0800
Subject: [PATCH v2] pg_buffercache: Add pg_buffercache_relations() function
This function returns an aggregation of buffer contents, grouped on a
per-relfilenode basis. This is often useful to understand which tables or
indexes are currently in cache, and can show cache disruptions due to query
activity when sampled over time.
The existing pg_buffercache() function can be utilized for this by
grouping the result, but passing a large amount of buffer entries
(one per page) back to the tuplestore and then aggregating it can be
prohibitively expensive with large buffer counts. Even on a small shared
buffers (128MB) the new function is 10x faster. Similar to the existing
summary functions this new function does not hold buffer partition or
buffer header locks whilst gathering its statistics.
Author: Lukas Fittl <[email protected]>
Reviewed by: Jakub Wartak <[email protected]>
Reviewed by: Bertrand Drouvot <[email protected]>
Reviewed by: Haibo Yan <[email protected]>
Reviewed by: Masahiko Sawada <[email protected]>
Reviewed by: Paul A Jungwirth <[email protected]>
Reviewed by: Khoa Nguyen <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com
---
.../expected/pg_buffercache.out | 14 ++
.../pg_buffercache--1.6--1.7.sql | 16 +++
contrib/pg_buffercache/pg_buffercache_pages.c | 136 +++++++++++++++++-
contrib/pg_buffercache/sql/pg_buffercache.sql | 4 +
doc/src/sgml/pgbuffercache.sgml | 130 +++++++++++++++++
src/tools/pgindent/typedefs.list | 2 +
6 files changed, 301 insertions(+), 1 deletion(-)
diff --git a/contrib/pg_buffercache/expected/pg_buffercache.out b/contrib/pg_buffercache/expected/pg_buffercache.out
index 886dea770f6..452f6ca6f58 100644
--- a/contrib/pg_buffercache/expected/pg_buffercache.out
+++ b/contrib/pg_buffercache/expected/pg_buffercache.out
@@ -33,6 +33,12 @@ SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
t
(1 row)
+SELECT count(*) > 0 FROM pg_buffercache_relations() WHERE buffers >= 0;
+ ?column?
+----------
+ t
+(1 row)
+
-- Check that the functions / views can't be accessed by default. To avoid
-- having to create a dedicated user, use the pg_database_owner pseudo-role.
SET ROLE pg_database_owner;
@@ -46,6 +52,8 @@ SELECT * FROM pg_buffercache_summary();
ERROR: permission denied for function pg_buffercache_summary
SELECT * FROM pg_buffercache_usage_counts();
ERROR: permission denied for function pg_buffercache_usage_counts
+SELECT * FROM pg_buffercache_relations();
+ERROR: permission denied for function pg_buffercache_relations
RESET role;
-- Check that pg_monitor is allowed to query view / function
SET ROLE pg_monitor;
@@ -73,6 +81,12 @@ SELECT count(*) > 0 FROM pg_buffercache_usage_counts();
t
(1 row)
+SELECT count(*) > 0 FROM pg_buffercache_relations();
+ ?column?
+----------
+ t
+(1 row)
+
RESET role;
------
---- Test pg_buffercache_evict* and pg_buffercache_mark_dirty* functions
diff --git a/contrib/pg_buffercache/pg_buffercache--1.6--1.7.sql b/contrib/pg_buffercache/pg_buffercache--1.6--1.7.sql
index 9a7bf66dab5..99b8e37a81c 100644
--- a/contrib/pg_buffercache/pg_buffercache--1.6--1.7.sql
+++ b/contrib/pg_buffercache/pg_buffercache--1.6--1.7.sql
@@ -54,3 +54,19 @@ CREATE FUNCTION pg_buffercache_mark_dirty_all(
OUT buffers_skipped int4)
AS 'MODULE_PATHNAME', 'pg_buffercache_mark_dirty_all'
LANGUAGE C PARALLEL SAFE VOLATILE;
+
+CREATE FUNCTION pg_buffercache_relations(
+ OUT relfilenode oid,
+ OUT reltablespace oid,
+ OUT reldatabase oid,
+ OUT relforknumber int2,
+ OUT buffers int4,
+ OUT buffers_dirty int4,
+ OUT buffers_pinned int4,
+ OUT usagecount_avg float8)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_buffercache_relations'
+LANGUAGE C PARALLEL SAFE;
+
+REVOKE ALL ON FUNCTION pg_buffercache_relations() FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pg_buffercache_relations() TO pg_monitor;
diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c
index db4d711cce7..6f2fc7a69af 100644
--- a/contrib/pg_buffercache/pg_buffercache_pages.c
+++ b/contrib/pg_buffercache/pg_buffercache_pages.c
@@ -15,6 +15,7 @@
#include "port/pg_numa.h"
#include "storage/buf_internals.h"
#include "storage/bufmgr.h"
+#include "common/hashfn.h"
#include "utils/rel.h"
#include "utils/tuplestore.h"
@@ -23,6 +24,7 @@
#define NUM_BUFFERCACHE_PAGES_ELEM 9
#define NUM_BUFFERCACHE_SUMMARY_ELEM 5
#define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4
+#define NUM_BUFFERCACHE_RELATIONS_ELEM 8
#define NUM_BUFFERCACHE_EVICT_ELEM 2
#define NUM_BUFFERCACHE_EVICT_RELATION_ELEM 3
#define NUM_BUFFERCACHE_EVICT_ALL_ELEM 3
@@ -92,6 +94,29 @@ typedef struct
BufferCacheOsPagesRec *record;
} BufferCacheOsPagesContext;
+/*
+ * Hash key for pg_buffercache_relations — groups by relation file.
+ */
+typedef struct
+{
+ RelFileLocator locator;
+ ForkNumber forknum;
+} BufferRelStatsKey;
+
+/*
+ * Hash entry for pg_buffercache_relations — accumulates per-relation
+ * buffer statistics.
+ */
+typedef struct
+{
+ BufferRelStatsKey key;
+ uint32 status; /* for simplehash */
+ int32 buffers;
+ int32 buffers_dirty;
+ int32 buffers_pinned;
+ int64 usagecount_total;
+} BufferRelStatsEntry;
+
/*
* Function returning data from the shared buffer cache - buffer number,
@@ -108,7 +133,20 @@ PG_FUNCTION_INFO_V1(pg_buffercache_evict_all);
PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty);
PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty_relation);
PG_FUNCTION_INFO_V1(pg_buffercache_mark_dirty_all);
-
+PG_FUNCTION_INFO_V1(pg_buffercache_relations);
+
+#define SH_PREFIX relstats
+#define SH_ELEMENT_TYPE BufferRelStatsEntry
+#define SH_KEY_TYPE BufferRelStatsKey
+#define SH_KEY key
+#define SH_HASH_KEY(tb, key) \
+ hash_bytes((const unsigned char *) &(key), sizeof(BufferRelStatsKey))
+#define SH_EQUAL(tb, a, b) \
+ (memcmp(&(a), &(b), sizeof(BufferRelStatsKey)) == 0)
+#define SH_SCOPE static inline
+#define SH_DECLARE
+#define SH_DEFINE
+#include "lib/simplehash.h"
/* Only need to touch memory once per backend process lifetime */
static bool firstNumaTouch = true;
@@ -961,3 +999,99 @@ pg_buffercache_mark_dirty_all(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(result);
}
+
+/*
+ * pg_buffercache_relations
+ *
+ * Produces a set of rows that summarize buffer cache usage per relation-fork
+ * combination. This enables monitoring scripts to only get the summary stats,
+ * instead of accumulating in a query with the full buffer information.
+ */
+Datum
+pg_buffercache_relations(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ relstats_hash *relstats;
+ relstats_iterator iter;
+ BufferRelStatsEntry *entry;
+ Datum values[NUM_BUFFERCACHE_RELATIONS_ELEM];
+ bool nulls[NUM_BUFFERCACHE_RELATIONS_ELEM] = {0};
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ /* Create a hash table to aggregate stats by relation-fork */
+ relstats = relstats_create(CurrentMemoryContext, 128, NULL);
+
+ /* Single pass over all buffers */
+ for (int i = 0; i < NBuffers; i++)
+ {
+ BufferDesc *bufHdr;
+ uint64 buf_state;
+ BufferRelStatsKey key = {0};
+ bool found;
+
+ CHECK_FOR_INTERRUPTS();
+
+ /*
+ * Read buffer state without locking, same as pg_buffercache_summary
+ * and pg_buffercache_usage_counts. Locking wouldn't provide a
+ * meaningfully more consistent result since buffers can change state
+ * immediately after we release the lock.
+ */
+ bufHdr = GetBufferDescriptor(i);
+ buf_state = pg_atomic_read_u64(&bufHdr->state);
+
+ /* Skip unused/invalid buffers */
+ if (!(buf_state & BM_VALID))
+ continue;
+
+ key.locator = BufTagGetRelFileLocator(&bufHdr->tag);
+ key.forknum = BufTagGetForkNum(&bufHdr->tag);
+
+ entry = relstats_insert(relstats, key, &found);
+
+ if (!found)
+ {
+ entry->buffers = 0;
+ entry->buffers_dirty = 0;
+ entry->buffers_pinned = 0;
+ entry->usagecount_total = 0;
+ }
+
+ entry->buffers++;
+ entry->usagecount_total += BUF_STATE_GET_USAGECOUNT(buf_state);
+
+ if (buf_state & BM_DIRTY)
+ entry->buffers_dirty++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ entry->buffers_pinned++;
+ }
+
+ /* Emit one row per hash entry */
+ relstats_start_iterate(relstats, &iter);
+ while ((entry = relstats_iterate(relstats, &iter)) != NULL)
+ {
+ CHECK_FOR_INTERRUPTS();
+
+ if (entry->buffers == 0)
+ continue;
+
+ values[0] = ObjectIdGetDatum(entry->key.locator.relNumber);
+ values[1] = ObjectIdGetDatum(entry->key.locator.spcOid);
+ values[2] = ObjectIdGetDatum(entry->key.locator.dbOid);
+ values[3] = Int16GetDatum(entry->key.forknum);
+ values[4] = Int32GetDatum(entry->buffers);
+ values[5] = Int32GetDatum(entry->buffers_dirty);
+ values[6] = Int32GetDatum(entry->buffers_pinned);
+ values[7] = Float8GetDatum((double) entry->usagecount_total /
+ entry->buffers);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+ values, nulls);
+ }
+
+ relstats_destroy(relstats);
+
+ return (Datum) 0;
+}
diff --git a/contrib/pg_buffercache/sql/pg_buffercache.sql b/contrib/pg_buffercache/sql/pg_buffercache.sql
index 127d604905c..b7f4d14afc2 100644
--- a/contrib/pg_buffercache/sql/pg_buffercache.sql
+++ b/contrib/pg_buffercache/sql/pg_buffercache.sql
@@ -18,6 +18,8 @@ from pg_buffercache_summary();
SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
+SELECT count(*) > 0 FROM pg_buffercache_relations() WHERE buffers >= 0;
+
-- Check that the functions / views can't be accessed by default. To avoid
-- having to create a dedicated user, use the pg_database_owner pseudo-role.
SET ROLE pg_database_owner;
@@ -26,6 +28,7 @@ SELECT * FROM pg_buffercache_os_pages;
SELECT * FROM pg_buffercache_pages() AS p (wrong int);
SELECT * FROM pg_buffercache_summary();
SELECT * FROM pg_buffercache_usage_counts();
+SELECT * FROM pg_buffercache_relations();
RESET role;
-- Check that pg_monitor is allowed to query view / function
@@ -34,6 +37,7 @@ SELECT count(*) > 0 FROM pg_buffercache;
SELECT count(*) > 0 FROM pg_buffercache_os_pages;
SELECT buffers_used + buffers_unused > 0 FROM pg_buffercache_summary();
SELECT count(*) > 0 FROM pg_buffercache_usage_counts();
+SELECT count(*) > 0 FROM pg_buffercache_relations();
RESET role;
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index 1e9aee10275..5c50a130c4f 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -31,6 +31,10 @@
<primary>pg_buffercache_usage_counts</primary>
</indexterm>
+ <indexterm>
+ <primary>pg_buffercache_relations</primary>
+ </indexterm>
+
<indexterm>
<primary>pg_buffercache_evict</primary>
</indexterm>
@@ -63,6 +67,7 @@
<structname>pg_buffercache_numa</structname> views), the
<function>pg_buffercache_summary()</function> function, the
<function>pg_buffercache_usage_counts()</function> function, the
+ <function>pg_buffercache_relations()</function> function, the
<function>pg_buffercache_evict()</function> function, the
<function>pg_buffercache_evict_relation()</function> function, the
<function>pg_buffercache_evict_all()</function> function, the
@@ -102,6 +107,12 @@
count.
</para>
+ <para>
+ The <function>pg_buffercache_relations()</function> function returns a
+ set of rows summarizing buffer cache usage aggregated by relation and fork
+ number.
+ </para>
+
<para>
By default, use of the above functions is restricted to superusers and roles
with privileges of the <literal>pg_monitor</literal> role. Access may be
@@ -564,6 +575,125 @@
</para>
</sect2>
+ <sect2 id="pgbuffercache-relation-stats">
+ <title>The <function>pg_buffercache_relations()</function> Function</title>
+
+ <para>
+ The definitions of the columns exposed by the function are shown in
+ <xref linkend="pgbuffercache_relation_stats-columns"/>.
+ </para>
+
+ <table id="pgbuffercache_relation_stats-columns">
+ <title><function>pg_buffercache_relations()</function> Output Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>relfilenode</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>relfilenode</structfield>)
+ </para>
+ <para>
+ Filenode number of the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>reltablespace</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ Tablespace OID of the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>reldatabase</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ Database OID of the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>relforknumber</structfield> <type>smallint</type>
+ </para>
+ <para>
+ Fork number within the relation; see
+ <filename>common/relpath.h</filename>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of buffers for the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_dirty</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of dirty buffers for the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_pinned</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of pinned buffers for the relation
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>usagecount_avg</structfield> <type>float8</type>
+ </para>
+ <para>
+ Average usage count of the relation's buffers
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ 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. Similar and more detailed information is
+ provided by the <structname>pg_buffercache</structname> view, but
+ <function>pg_buffercache_relations()</function> is significantly
+ cheaper.
+ </para>
+
+ <para>
+ Like the <structname>pg_buffercache</structname> view,
+ <function>pg_buffercache_relations()</function> does not acquire buffer
+ manager locks. Therefore concurrent activity can lead to minor inaccuracies
+ in the result.
+ </para>
+ </sect2>
+
<sect2 id="pgbuffercache-pg-buffercache-evict">
<title>The <function>pg_buffercache_evict()</function> Function</title>
<para>
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 8df23840e57..4ae3ef9103b 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -362,6 +362,8 @@ BufferHeapTupleTableSlot
BufferLockMode
BufferLookupEnt
BufferManagerRelation
+BufferRelStatsEntry
+BufferRelStatsKey
BufferStrategyControl
BufferTag
BufferUsage
--
2.47.1
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-25 16:48 Masahiko Sawada <[email protected]>
parent: Lukas Fittl <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Masahiko Sawada @ 2026-03-25 16:48 UTC (permalink / raw)
To: Lukas Fittl <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
On Tue, Mar 24, 2026 at 11:47 PM Lukas Fittl <[email protected]> wrote:
>
> Hi Ashutosh,
>
> On Tue, Mar 24, 2026 at 11:24 PM Ashutosh Bapat
> <[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-26 04:21 Ashutosh Bapat <[email protected]>
parent: Masahiko Sawada <[email protected]>
0 siblings, 4 replies; 27+ messages in thread
From: Ashutosh Bapat @ 2026-03-26 04:21 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Lukas Fittl <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
On Wed, Mar 25, 2026 at 10:19 PM Masahiko Sawada <[email protected]> wrote:
>
> On Tue, Mar 24, 2026 at 11:47 PM Lukas Fittl <[email protected]> wrote:
> >
> > Hi Ashutosh,
> >
> > On Tue, Mar 24, 2026 at 11:24 PM Ashutosh Bapat
> > <[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-27 18:11 Dmitry Dolgov <[email protected]>
parent: Lukas Fittl <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Dmitry Dolgov @ 2026-03-27 18:11 UTC (permalink / raw)
To: Lukas Fittl <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
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.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-28 04:18 Ashutosh Bapat <[email protected]>
parent: Ashutosh Bapat <[email protected]>
3 siblings, 1 reply; 27+ messages in thread
From: Ashutosh Bapat @ 2026-03-28 04:18 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; [email protected]; +Cc: Masahiko Sawada <[email protected]>; Lukas Fittl <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
On Sat, Mar 28, 2026 at 4:28 AM Tomas Vondra <[email protected]> wrote:
>
> On 3/26/26 05:21, Ashutosh Bapat wrote:
> > On Wed, Mar 25, 2026 at 10:19 PM Masahiko Sawada <[email protected]> wrote:
> >>
> >> On Tue, Mar 24, 2026 at 11:47 PM Lukas Fittl <[email protected]> wrote:
> >>>
> >>> Hi Ashutosh,
> >>>
> >>> On Tue, Mar 24, 2026 at 11:24 PM Ashutosh Bapat
> >>> <[email protected]> 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
Attachments:
[text/x-patch] v20260328-0001-pg_buffercache_pages-modernization-and-opt.patch (11.3K, ../../CAExHW5sMsaz1j+hrdhyo-DJp7JCgJx87=q2iJfOc_9mwYWyvmw@mail.gmail.com/2-v20260328-0001-pg_buffercache_pages-modernization-and-opt.patch)
download | inline diff:
From 9112536d59124057f81b01c9e48feed31f0cc98d Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <[email protected]>
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 protected]>
Author: Palak Chaturvedi <[email protected]>
---
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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-28 05:36 Masahiko Sawada <[email protected]>
parent: Ashutosh Bapat <[email protected]>
3 siblings, 2 replies; 27+ messages in thread
From: Masahiko Sawada @ 2026-03-28 05:36 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; Lukas Fittl <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
On Fri, Mar 27, 2026 at 3:58 PM Tomas Vondra <[email protected]> wrote:
>
> On 3/26/26 05:21, Ashutosh Bapat wrote:
> > On Wed, Mar 25, 2026 at 10:19 PM Masahiko Sawada <[email protected]> wrote:
> >>
> >> On Tue, Mar 24, 2026 at 11:47 PM Lukas Fittl <[email protected]> wrote:
> >>>
> >>> Hi Ashutosh,
> >>>
> >>> On Tue, Mar 24, 2026 at 11:24 PM Ashutosh Bapat
> >>> <[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-28 16:12 Ashutosh Bapat <[email protected]>
parent: Masahiko Sawada <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Ashutosh Bapat @ 2026-03-28 16:12 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Tomas Vondra <[email protected]>; Lukas Fittl <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
On Sat, Mar 28, 2026 at 11:07 AM Masahiko Sawada <[email protected]> wrote:
>
> On Fri, Mar 27, 2026 at 3:58 PM Tomas Vondra <[email protected]> wrote:
> >
> > On 3/26/26 05:21, Ashutosh Bapat wrote:
> > > On Wed, Mar 25, 2026 at 10:19 PM Masahiko Sawada <[email protected]> wrote:
> > >>
> > >> On Tue, Mar 24, 2026 at 11:47 PM Lukas Fittl <[email protected]> wrote:
> > >>>
> > >>> Hi Ashutosh,
> > >>>
> > >>> On Tue, Mar 24, 2026 at 11:24 PM Ashutosh Bapat
> > >>> <[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-28 18:51 Lukas Fittl <[email protected]>
parent: Masahiko Sawada <[email protected]>
1 sibling, 0 replies; 27+ messages in thread
From: Lukas Fittl @ 2026-03-28 18:51 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Tomas Vondra <[email protected]>; Ashutosh Bapat <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
On Fri, Mar 27, 2026 at 10:37 PM Masahiko Sawada <[email protected]> wrote:
>
> On Fri, Mar 27, 2026 at 3:58 PM Tomas Vondra <[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-28 19:14 Lukas Fittl <[email protected]>
parent: Ashutosh Bapat <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Lukas Fittl @ 2026-03-28 19:14 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; Tomas Vondra <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
On Sat, Mar 28, 2026 at 9:12 AM Ashutosh Bapat
<[email protected]> wrote:
>
> On Sat, Mar 28, 2026 at 11:07 AM Masahiko Sawada <[email protected]> wrote:
> >
> > On Fri, Mar 27, 2026 at 3:58 PM Tomas Vondra <[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-03-28 19:38 Lukas Fittl <[email protected]>
parent: Ashutosh Bapat <[email protected]>
3 siblings, 0 replies; 27+ messages in thread
From: Lukas Fittl @ 2026-03-28 19:38 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; Masahiko Sawada <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
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 <[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-04-07 13:07 Heikki Linnakangas <[email protected]>
parent: Ashutosh Bapat <[email protected]>
0 siblings, 2 replies; 27+ messages in thread
From: Heikki Linnakangas @ 2026-04-07 13:07 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; Tomas Vondra <[email protected]>; [email protected]; +Cc: Masahiko Sawada <[email protected]>; Lukas Fittl <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-04-07 13:23 Ashutosh Bapat <[email protected]>
parent: Heikki Linnakangas <[email protected]>
1 sibling, 0 replies; 27+ messages in thread
From: Ashutosh Bapat @ 2026-04-07 13:23 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: Tomas Vondra <[email protected]>; [email protected]; Masahiko Sawada <[email protected]>; Lukas Fittl <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
On Tue, Apr 7, 2026 at 6:37 PM Heikki Linnakangas <[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-04-07 13:47 Andres Freund <[email protected]>
parent: Heikki Linnakangas <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Andres Freund @ 2026-04-07 13:47 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; Tomas Vondra <[email protected]>; [email protected]; Masahiko Sawada <[email protected]>; Lukas Fittl <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-04-07 15:55 Heikki Linnakangas <[email protected]>
parent: Andres Freund <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Heikki Linnakangas @ 2026-04-07 15:55 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; Tomas Vondra <[email protected]>; [email protected]; Masahiko Sawada <[email protected]>; Lukas Fittl <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-04-08 06:36 Ashutosh Bapat <[email protected]>
parent: Heikki Linnakangas <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Ashutosh Bapat @ 2026-04-08 06:36 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: Andres Freund <[email protected]>; Tomas Vondra <[email protected]>; [email protected]; Masahiko Sawada <[email protected]>; Lukas Fittl <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
On Tue, Apr 7, 2026 at 9:25 PM Heikki Linnakangas <[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: pg_buffercache: Add per-relation summary stats
@ 2026-06-18 14:09 Robert Haas <[email protected]>
parent: Ashutosh Bapat <[email protected]>
3 siblings, 0 replies; 27+ messages in thread
From: Robert Haas @ 2026-06-18 14:09 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; Masahiko Sawada <[email protected]>; Lukas Fittl <[email protected]>; PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>; Khoa Nguyen <[email protected]>
On Fri, Mar 27, 2026 at 6:59 PM Tomas Vondra <[email protected]> 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
^ permalink raw reply [nested|flat] 27+ messages in thread
end of thread, other threads:[~2026-06-18 14:09 UTC | newest]
Thread overview: 27+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-14 17:26 [PATCH v2] pg_dump: fix dependencies on FKs to multi-level partitioned tables Alvaro Herrera <[email protected]>
2026-02-28 23:58 pg_buffercache: Add per-relation summary stats Lukas Fittl <[email protected]>
2026-03-02 10:16 ` Re: pg_buffercache: Add per-relation summary stats Jakub Wartak <[email protected]>
2026-03-03 02:39 ` Re: pg_buffercache: Add per-relation summary stats Lukas Fittl <[email protected]>
2026-03-09 09:15 ` Re: pg_buffercache: Add per-relation summary stats Bertrand Drouvot <[email protected]>
2026-03-25 06:54 ` Re: pg_buffercache: Add per-relation summary stats Lukas Fittl <[email protected]>
2026-03-17 04:21 ` Re: pg_buffercache: Add per-relation summary stats Haibo Yan <[email protected]>
2026-03-25 06:52 ` Re: pg_buffercache: Add per-relation summary stats Lukas Fittl <[email protected]>
2026-03-24 19:09 ` Re: pg_buffercache: Add per-relation summary stats Masahiko Sawada <[email protected]>
2026-03-25 06:24 ` Re: pg_buffercache: Add per-relation summary stats Ashutosh Bapat <[email protected]>
2026-03-25 06:46 ` Re: pg_buffercache: Add per-relation summary stats Lukas Fittl <[email protected]>
2026-03-25 16:48 ` Re: pg_buffercache: Add per-relation summary stats Masahiko Sawada <[email protected]>
2026-03-26 04:21 ` Re: pg_buffercache: Add per-relation summary stats Ashutosh Bapat <[email protected]>
2026-03-28 04:18 ` Re: pg_buffercache: Add per-relation summary stats Ashutosh Bapat <[email protected]>
2026-04-07 13:07 ` Re: pg_buffercache: Add per-relation summary stats Heikki Linnakangas <[email protected]>
2026-04-07 13:23 ` Re: pg_buffercache: Add per-relation summary stats Ashutosh Bapat <[email protected]>
2026-04-07 13:47 ` Re: pg_buffercache: Add per-relation summary stats Andres Freund <[email protected]>
2026-04-07 15:55 ` Re: pg_buffercache: Add per-relation summary stats Heikki Linnakangas <[email protected]>
2026-04-08 06:36 ` Re: pg_buffercache: Add per-relation summary stats Ashutosh Bapat <[email protected]>
2026-03-28 05:36 ` Re: pg_buffercache: Add per-relation summary stats Masahiko Sawada <[email protected]>
2026-03-28 16:12 ` Re: pg_buffercache: Add per-relation summary stats Ashutosh Bapat <[email protected]>
2026-03-28 19:14 ` Re: pg_buffercache: Add per-relation summary stats Lukas Fittl <[email protected]>
2026-03-28 18:51 ` Re: pg_buffercache: Add per-relation summary stats Lukas Fittl <[email protected]>
2026-03-28 19:38 ` Re: pg_buffercache: Add per-relation summary stats Lukas Fittl <[email protected]>
2026-06-18 14:09 ` Re: pg_buffercache: Add per-relation summary stats Robert Haas <[email protected]>
2026-03-25 07:29 ` Re: pg_buffercache: Add per-relation summary stats Lukas Fittl <[email protected]>
2026-03-27 18:11 ` Re: pg_buffercache: Add per-relation summary stats Dmitry Dolgov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox