MIME-Version: 1.0
References: 
 <CAAKRu_ZMw6Npd_qm2KM+FwQ3cMOMx1Dh3VMhp8-V7SOLxdK9-g@mail.gmail.com>
 <CAAKRu_YXP1Us41fkZJeBK5SSj5whNC0C9fZNT6Brdtm+F0agpg@mail.gmail.com>
In-Reply-To: 
 <CAAKRu_YXP1Us41fkZJeBK5SSj5whNC0C9fZNT6Brdtm+F0agpg@mail.gmail.com>
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Wed, 9 Jul 2025 17:59:26 -0400
Message-ID: 
 <CAAKRu_bAR5uCfjuc06vc_xrZjNCJLs493NgHjTOUDso9qGdE0w@mail.gmail.com>
Subject: Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM
 on-access)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Andres Freund <andres@anarazel.de>, Robert Haas <robertmhaas@gmail.com>
Content-Type: multipart/mixed; boundary="0000000000007eb0740639863165"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAKRu_bAR5uCfjuc06vc_xrZjNCJLs493NgHjTOUDso9qGdE0w%40mail.gmail.com>
Precedence: bulk

--0000000000007eb0740639863165
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Jun 26, 2025 at 6:04=E2=80=AFPM Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> Rebased in light of recent changes on master:

This needed another rebase, and, in light of the discussion in [1],
I've also removed the patch to add heap wrappers for setting pages
all-visible.

More notably, the final patch (0012) in attached v3 allows on-access
pruning to set the VM.

To do this, it plumbs some information down from the executor to the
table scan about whether or not the table is modified by the query. We
don't want to set the VM only to clear it while scanning pages for an
UPDATE or while locking rows in a SELECT FOR UPDATE.

Because we only do on-access pruning when pd_prune_xid is valid, we
shouldn't need much of a heuristic for deciding when to set the VM
on-access -- but I've included one anyway: we only do it if we are
actually pruning or if the page is already dirty and no FPI would be
emitted.

You can see it in action with the following:

create extension pg_visibility;
create table foo (a int, b int) with (autovacuum_enabled=3Dfalse, fillfacto=
r=3D90);
insert into foo select generate_series(1,300), generate_series(1,300);
create index on foo (a);
update foo set b =3D 51 where b =3D 50;
select * from foo where a =3D 50;
select * from pg_visibility_map_summary('foo');

The SELECT will set a page all-visible in the VM.
In this patch set, on-access pruning is enabled for sequential scans
and the underlying heap relation in index scans and bitmap heap scans.
This example can exercise any of the three if you toggle
enable_indexscan and enable_bitmapscan appropriately.