Re: 64-bit XIDs in deleted nbtree pages

сб, 13 февр. 2021 г. в 05:39, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>:

> > (BTW, I've been using txid_current() for my own "laptop testing", as a
> > way to work around this issue.)
> >
> > * More generally, if you really can't do recycling of pages that you
> > deleted during the last VACUUM during this VACUUM (perhaps because of
> > the presence of a long-running xact that holds open a snapshot), then
> > you have lots of *huge* problems already, and this is the least of
> > your concerns. Besides, at that point an affected VACUUM will be doing
> > work for an affected index through a btbulkdelete() call, so the
> > behavior of _bt_vacuum_needs_cleanup() becomes irrelevant.
> >
>
> I agree that there already are huge problems in that case. But I think
> we need to consider an append-only case as well; after bulk deletion
> on an append-only table, vacuum deletes heap tuples and index tuples,
> marking some index pages as dead and setting an XID into btpo.xact.
> Since we trigger autovacuums even by insertions based on
> autovacuum_vacuum_insert_scale_factor/threshold autovacuum will run on
> the table again. But if there is a long-running query a "wasted"
> cleanup scan could happen many times depending on the values of
> autovacuum_vacuum_insert_scale_factor/threshold and
> vacuum_cleanup_index_scale_factor. This should not happen in the old
> code. I agree this is DBA problem but it also means this could bring
> another new problem in a long-running query case.
>

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.

--
Victor Yegorov