Re: 64-bit XIDs in deleted nbtree pages - Mailing list pgsql-hackers

From Victor Yegorov
Subject Re: 64-bit XIDs in deleted nbtree pages
Date
Msg-id CAGnEbohkXwTuJ+cxCxs5c0EoAKsh_bQXR+hS9UHOwTptpV2MbA@mail.gmail.com
Whole thread Raw
In response to Re: 64-bit XIDs in deleted nbtree pages  (Masahiko Sawada <sawada.mshk@gmail.com>)
Responses Re: 64-bit XIDs in deleted nbtree pages
List pgsql-hackers
сб, 13 февр. 2021 г. в 05:39, Masahiko Sawada <sawada.mshk@gmail.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

pgsql-hackers by date:

Previous
From: japin
Date:
Subject: Re: Support ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION ... syntax
Next
From: "Joel Jacobson"
Date:
Subject: Re: [HACKERS] GSoC 2017: Foreign Key Arrays