Re: Report buffer access strategies in EXPLAIN

Hi,

On Thu, Nov 05, 2026 at 04:48:04PM +0900, Michael Paquier wrote:
> On Tue, Nov 03, 2026 at 10:15:48AM +0000, Bertrand Drouvot wrote:
> > 0001 updates the buffer README to describe the current bulk-read ring sizing and
> > bulk-write users.
>
> I have looked at v1~3, as these are actual doc-"bug" fixes.

Thanks for looking at it!

> Perhaps not strictly mandatory for a README, but I'm wondering if this
> should be backpatched down to v18.. If that applies without
> conflicts.

Yeah that does not hurt and that apply without conflicts (just checked).

> Perhaps just drop the list of operations or be more vague?

Yeah, good point. I've dropped the list in v4, which also addresses Sami's
previous comment.

> +For bulk reads, the requested ring size is at least 256KB. The 256KB target is
> +small enough to fit in L2 cache, which makes transferring pages from OS cache
> +to shared buffer cache efficient.
>
> The ring can be enlarged, meaning that the optimization applies only
> for a minimum size?

Right, that's why the text refers specifically to the 256KB target. Do you
mean that the L2 cache rationale should be dropped now that it does not
necessarily apply to the resulting ring size?

> > 0002 documents the available buffer access strategy types.
>
> Specifies the size of the
> + <literal>vacuum</literal>
> <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
> [...]
> maintenance tasks (such as index vacuuming) are bypassed, and any
> + <literal>vacuum</literal>
> <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
> in use will be disabled resulting in <command>VACUUM</command> being
> [...]
> Specifies the
> + <literal>vacuum</literal>
> <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
> ring buffer size for <command>ANALYZE</command>. This size is used to
> [...]
> ring buffer size for <command>VACUUM</command>. This size is used to
> calculate the number of shared buffers which will be reused as part of
> - this strategy. <literal>0</literal> disables use of a
> + this strategy. <literal>0</literal> disables use of a <literal>vacuum</literal>
> <literal>Buffer Access Strategy</literal>. If <option>ANALYZE</option>
>
> These changes seem a bit weird to me. Okay, the BAS is named
> "vacuum", and the contexts refer to VACUUM, ANALYZE or vacuumdb, but
> that feels duplicated wording to me. I'm wondering if it's not
> simpler to just drop this level of details.

I don't have a strong opinion about it so I removed them.

> - Buffer Access Strategies are used for various operations such as
> - sequential scans of large tables, <command>VACUUM</command>,
> - <command>COPY</command>, <command>CREATE TABLE AS SELECT</command>,
> - <command>ALTER TABLE</command>, <command>CREATE DATABASE</command>,
> - <command>CREATE INDEX</command>, and <command>CLUSTER</command>.
> - </para>
>
> We are loosing some information here. Shouldn't the various
> strategies still list at least these commands?

That makes sense. I've listed the commands under each applicable strategy. Note
that some appear in both lists: for example, CREATE DATABASE uses bulkread for
the source and bulkwrite for the destination. I did not go as far as explaining
why in the glossary.

> On the glossary page, we have this sentence:
> A Buffer Access Strategy sets up references to a limited number of
> <glossterm linkend="glossary-shared-memory">shared buffers</glossterm> and
> reuses them circularly.
>
> GetAccessStrategy() returns NULL for BAS_NORMAL, so that feels
> slightly inconsistent, but I get that you've done so to be more in
> line with pg_stat_io that lists a "normal". Perhaps just say that no
> strategy is returned, switching to a normal clock sweep, would be more
> precise?

Yeah. I've clarified that normal uses the normal clock sweep buffer replacement
policy without a strategy ring and adjusted the preceding text accordingly.

> + little reuse, such as large sequential scans. Reusing a limited set of
> + buffers reduces eviction of unrelated pages from shared buffers.
>
> That's in the description of bulkread, reads like a duplicate of the
> paragraph above.

Yeah, I see what you mean. Removed.

>
> > 0003 clarifies that strategy rings reference shared buffers in the pg_stat_io
> > documentation.
>
> Perhaps that should be merged with 0002. That's kind of the same set
> of incorrect descriptions to me.

I'm not sure as the pg_stat_io wording is a pre existing issue that can be
backpatched, while I think that 0002 adds documentation on master.

>
> + <literal>vacuum</literal>: I/O operations performed using the
> + <literal>vacuum</literal>
> + <glossterm linkend="glossary-buffer-access-strategy">buffer access
> + strategy</glossterm> while vacuuming and analyzing permanent
> + relations. Temporary table vacuums use the same local buffer pool as
> + other temporary table I/O operations and are tracked in
> + <varname>context</varname> <literal>normal</literal>.
>
> BAS_VACUUM can also be found in the checksum code. Just saying.

Good point. That wording predates f19c0eccae9 (which added BAS_VACUUM to the
checksum code), so it became stale. Fixed. This part should be kept for a v19
backpatch, but omitted for v16 through v18 (if we decide to backpatch it).

> > 0004 extracts the lookup of leader and per-worker table scan instrumentation.
> > 0005 adds optional buffer access strategy instrumentation for table scans and
> > preserves every strategy observed across rescans.
> > 0006 adds per-worker table scan instrumentation for sample and TID scans.
> > 0007 reports the strategies used by table scans with EXPLAIN (ANALYZE, BUFFERS),
> > including parallel workers.
> > 0008 reports the strategies used by index, index-only, and bitmap index scans.
>
> Hmm. These touch the same area of the code as some of the stuff Lukas
> Fittl is working on..

Yeah, this may require some rebasing. That said, I think their goals are largely
orthogonal.

Regards,

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