Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w2Lw4-000DO7-07 for pgsql-hackers@arkaria.postgresql.org; Tue, 17 Mar 2026 04:21:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w2Lw2-00Fh4n-2p for pgsql-hackers@arkaria.postgresql.org; Tue, 17 Mar 2026 04:21:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w2Lw2-00Fh4f-1d for pgsql-hackers@lists.postgresql.org; Tue, 17 Mar 2026 04:21:38 +0000 Received: from mail-oi1-x22a.google.com ([2607:f8b0:4864:20::22a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w2Lvz-00000000XjX-2nDj for pgsql-hackers@lists.postgresql.org; Tue, 17 Mar 2026 04:21:38 +0000 Received: by mail-oi1-x22a.google.com with SMTP id 5614622812f47-467161c4b89so3323299b6e.3 for ; Mon, 16 Mar 2026 21:21:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1773721294; x=1774326094; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=w6OEX9pA/P/N4F1MBkY7Ad2srEFrKIDz83ZfphG+E3c=; b=ISSfzo8FXqsxkx/I53jqUWSA8W2+aC60o+0DCLOKewh+QeVEpiny3gmgt45Hkj4BXj tKVlo6qbiwMsl3GiPG7Es86UkXn8uMskWD3J4V1zP1auv13HnoSdkzW62hPaeQK1hqNw /smAOaP0I3ojb5thTv94OcpILaOcIqkc8MtqkzamB3mNSZL3rMWOEEeVgUQXpSeBJszU JYM33+cF/21QqGEhO/puCcTrlYqtsOBaf/zzd3T+MTCqvZrbW/Pv0oG2jJzC6Hnk9KqQ 7ScbnH9rcne8a4LNTxQ9/FruZnT+UhkwKQ+ivBHIZKkH9uDyGNkYi4s1oEdO+Vbhgmen CKQA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1773721294; x=1774326094; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=w6OEX9pA/P/N4F1MBkY7Ad2srEFrKIDz83ZfphG+E3c=; b=mNhVLcoUWLH5wtcRlLcXuqRhvczSdP9ct0NZZggtYpApBAhXTDbeICUNauvcqgFK72 wr/VVq7iPfoGTHTgKPB1HITAvTJp5YD5OJDpzLCHt4V7zjPINvnbgloqizXx2UX2nWal P4JToLaV3qr2efSWSUR+g+KNGot7LR6ItuAifmq45jgmaW4LKWydupJxGKrQDHtrPs7J a/UhAtzdPnLujSzR0IHuH3W+3fzxhVehndp+KQY7cmStqo/E+YPCWFPzUAc+1ms63ssb TAxCELTpQeaj3W/+2ai3567X80ABYrEW6huR01cvJKRTDk14aByhoSoaO6Yi29M8w5qU xC2Q== X-Gm-Message-State: AOJu0Yx7nKKjab/XX1XxiOe0xPX8M/o2L3b0OypTl+wbMOJhsauvcwJd NGI6b9oBXpzW5el/corNJMbQdSPI3ic6LKVHaVRbgjb79chfUbHAS8F19yPPfg== X-Gm-Gg: ATEYQzy+7tNy4yOa3lLArFLcyVZgWip/phGf+xRPRLhQJyqAH/CClOs7M8ANRvlROfI UbAYqfZ+JXSsyhrnKxUVh4eN+DDzdMU/sp31ANgasImInGf9cuOej6tHxFJpGKHMPxfexJs3Aop wVEuKQx4Zt6OwB18nqtdLzghXCDVA7wPlqLcZzUXaQFGmObaowmcNfKyipGw4Ba6ZdDUdt2b3n/ h4W4MI+ES1XtKksXKO/Igy0S8crl64CyPsS4Tp68vnvpTb69SWPRNRt8lHY0slcN9hcSWpe3lap aJ4J7UduNzb1p0W7SJB6ZVDS2Qs1F2VXhVcfZ9r7wo127FgMWxg+EeZVrN8iSNwVsIE8ODBy80K QFQvnU8O+pUl3lUOFXhXZVr2573SNHT3H3CJEIL4KhTgX8ZMkOBJCyGCCHhsg3xnuslU0RfrqDG 7s7tVXMsg790TxJArAqm3IY4JhUsX3tOOZxMkphTVz87AyIzoYrOksVNfGmY5i5yZWtHo= X-Received: by 2002:a05:6808:3008:b0:460:f435:2a70 with SMTP id 5614622812f47-467575c2331mr8766695b6e.47.1773721294207; Mon, 16 Mar 2026 21:21:34 -0700 (PDT) Received: from smtpclient.apple ([2620:149:13d0:100::102]) by smtp.gmail.com with ESMTPSA id 586e51a60fabf-41791432d87sm18922259fac.1.2026.03.16.21.21.33 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 16 Mar 2026 21:21:33 -0700 (PDT) From: Haibo Yan X-Google-Original-From: Haibo Yan Message-Id: <809814F5-0CD1-4FA1-83C4-E04A27633664@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_FBB886F4-D7CF-40D8-9254-BE13889150A3" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\)) Subject: Re: pg_buffercache: Add per-relation summary stats Date: Mon, 16 Mar 2026 21:21:22 -0700 In-Reply-To: Cc: PostgreSQL Hackers To: Lukas Fittl References: X-Mailer: Apple Mail (2.3864.400.21) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_FBB886F4-D7CF-40D8-9254-BE13889150A3 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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 =E2=80=9Crelation stats=E2=80=9D. = 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, =E2=80=9CHash key for pg_buffercache_relation_stats = =E2=80=94 groups by relation identity=E2=80=9D, 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=E2=80=AFPM, Lukas Fittl = wrote: >=20 > Hi, >=20 > 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. >=20 > 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. >=20 > 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: >=20 > postgres=3D# 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 >=20 > ) > SELECT * FROM pg_buffercache_relation_stats WHERE relfilenode =3D = 2659; >=20 > 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) >=20 > Time: 20.991 ms >=20 > postgres=3D# SELECT * FROM pg_buffercache_relation_stats() WHERE > relfilenode =3D 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) >=20 > Time: 2.912 ms >=20 > With the new function this gets done before putting the data in the > tuplestore used for the set-returning function. >=20 > Thanks, > Lukas >=20 > [0]: = https://pganalyze.com/blog/tracking-postgres-buffer-cache-statistics >=20 > --=20 > Lukas Fittl > --Apple-Mail=_FBB886F4-D7CF-40D8-9254-BE13889150A3 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

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 =E2=80=9Crelation = stats=E2=80=9D. 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, =E2=80=9CHash key for = pg_buffercache_relation_stats =E2=80=94 groups by relation identity=E2=80=9D= , 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=E2=80=AFPM, Lukas Fittl <lukas@fittl.com> wrote:

Hi,

See attached a = patch that implements a new = function,
pg_buffercache_relation_stats(), which returns = per-relfilenode
statistics on the number of buffers, how many are = dirtied/pinned, and
their avg usage count.

This can be used in = monitoring scripts to know which relations are
kept in shared = buffers, to understand performance issues better that
occur due to = relations getting evicted from the cache. In our own
monitoring tool = (pganalyze) we've offered a functionality like this
based on the = existing pg_buffercache() function for a bit over a year
now [0], and = people have found this very valuable - but it doesn't
work for larger = database servers.

Specifically, performing a query that gets this = information can be
prohibitively expensive when using large = shared_buffers, and even on
the default 128MB shared buffers there is = a measurable difference:

postgres=3D# WITH = pg_buffercache_relation_stats AS (
SELECT relfilenode, reltablespace, = reldatabase, relforknumber,
=             &n= bsp;           &nbs= p;            =           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 =3D 2659;

relfilenode | reltablespace | reldatabase = | relforknumber | buffers |
buffers_dirty | buffers_pinned | =   usagecount_avg
-------------+---------------+-------------+= ---------------+---------+---------------+----------------+---------------= -----
       2659 | =          1663 | =           5 | =             0 = |       8 |
=            0 | =             &n= bsp;0 | 5.0000000000000000
=        2659 | =          1663 | =           1 | =             0 = |       7 |
=            0 | =             &n= bsp;0 | 5.0000000000000000
=        2659 | =          1663 | =      229553 | =             0 = |       7 |
=            0 | =             &n= bsp;0 | 5.0000000000000000
(3 rows)

Time: 20.991 = ms

postgres=3D# SELECT * FROM pg_buffercache_relation_stats() = WHERE
relfilenode =3D 2659;
relfilenode | reltablespace | = reldatabase | relforknumber | buffers |
buffers_dirty | = buffers_pinned | = usagecount_avg
-------------+---------------+-------------+------------= ---+---------+---------------+----------------+----------------
=        2659 | =          1663 | =           1 | =             0 = |       7 |
=            0 | =             &n= bsp;0 | =             &n= bsp;5
       2659 | =          1663 | =      229553 | =             0 = |       7 |
=            0 | =             &n= bsp;0 | =             &n= bsp;5
       2659 | =          1663 | =           5 | =             0 = |       8 |
=            0 | =             &n= bsp;0 | =             &n= bsp;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_relat= ion_stats-.patch>

= --Apple-Mail=_FBB886F4-D7CF-40D8-9254-BE13889150A3--