Re: Report buffer access strategies in EXPLAIN

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.

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

-Bulk writes work similarly to VACUUM. Currently this applies only to
-COPY IN and CREATE TABLE AS SELECT. (Might it be interesting to make
-seqscan UPDATE and DELETE use the bulkwrite strategy?) For bulk writes
-we use a ring size of 16MB (but not more than 1/8th of shared_buffers).
+Bulk writes work similarly to VACUUM. They are used for operations such as
+COPY FROM, CREATE TABLE AS, materialized view population, and some table
+rewrites. For bulk writes we use a ring size of 16MB (but not more than
+1/8th of shared_buffers).

Perhaps just drop the list of operations or be more vague? This
misses for example CREATE DATABASE.

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

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

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

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?

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

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

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

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