сб, 13 февр. 2021 г. в 05:39, Masahiko Sawada <sawada.m...@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.1426090...@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
