On Wed, Apr 1, 2020 at 7:17 PM Andres Freund <andres@anarazel.de> wrote:
FWIW, with autovacuum=off the query does not get killed until a manual vacuum, nor if fewer rows are deleted and the table has previously been vacuumed.
The vacuum in the second session isn't required. There just needs to be something consuming an xid, so that oldSnapshotControl->latest_xmin is increased. A single SELECT txid_current(); or such in a separate session is sufficient.
Agreed. I don't see that part as a problem; if no xids are being consumed, it's hard to see how we could be heading into debilitating levels of bloat, so there is no need to perform the early pruning. It would not be worth consuming any cycles to ensure that pruning happens sooner than it does in this case. It's OK for it to happen any time past the moment that the snapshot hits the threshold, but it's also OK for it to wait until a vacuum of the table or until some activity consumes an xid.