Re: 64-bit XIDs in deleted nbtree pages

On Fri, Feb 12, 2021 at 10:27 PM Victor Yegorov <vyegorov(at)gmail(dot)com> wrote:
> I'd like to outline one relevant case.
>
> Quite often bulk deletes are done on a time series data (oldest) and effectively
> removes a continuous chunk of data at the (physical) beginning of the table,
> this is especially true for the append-only tables.
> After the delete, planning queries takes a long time, due to MergeJoin estimates
> are using IndexScans ( see https://postgr.es/m/17467.1426090533@sss.pgh.pa.us )
> Right now we have to disable MergeJoins via the ALTER SYSTEM to mitigate this.
>
> So I would, actually, like it very much for VACUUM to kick in sooner in such cases.

Masahiko was specifically concerned about workloads with
bursty/uneven/mixed VACUUM triggering conditions -- he mentioned
autovacuum_vacuum_insert_scale_factor/threshold as being applied to
trigger a second VACUUM (which follows from an initial VACUUM that
performs deletions following a bulk DELETE).

A VACUUM that needs to delete index tuples will do its btvacuumscan()
through the btbulkdelete() path, not through the btvacuumcleanup()
"cleanup only" path. The btbulkdelete() path won't ever call
_bt_vacuum_needs_cleanup() in the first place, and so there can be no
risk that the relevant changes (changes that the patch makes to that
function) will have some new bad effect. The problem that you have
described seems very real, but it doesn't seem relevant to the
specific scenario that Masahiko expressed concern about. Nor does it
seem relevant to this patch more generally.

--
Peter Geoghegan