Re: Report oldest xmin source when autovacuum cannot remove tuples

> The way I think about approaching this problem is: 1/ we can only ever
> report a possible cause, whether we capture it at the start or
> reconstruct it at the end with a separate scan, since a transaction or
> slot holding the xmin back may go away right after; given that it is
> best-effort either way, I would rather report it as soon as VACUUM
> computes its cutoffs, where it is most useful, so the user can act
> while the VACUUM is still running and fix things for the next relation
> in the same cycle or the next cycle, rather than learn at the end that
> the run achieved nothing, 2/ the reporting stays cheap and adds no
> contention on hot locks such as ProcArrayLock, and 3/ it carries the
> minimum actionable detail, namely the backend PID for a running
> transaction, the GID for a prepared transaction, the slot name for a
> replication slot, and the walsender PID for standby feedback.

I have not looked at v1-, but I am wondering why don't we detach this
logging from the per-table vacuum logs as an alternative? Meaning that
we log the oldest blocker every X amount of time (i.e. 1 minute) if
the horizon has not advanced and the same blocker persists? This can
be tracked by the autovacuum launcher which will be responsible for
emitting this info.

I think this is better because we are no longer just emitting this
information when autovacuum logging is enabled, which to me is still
quite a real limitation of per-table logging. If I have many tables with
quick vacuums that are getting blocked, I have to set autovacuum
logging quite aggressively to get any information, even though dead
tuples are piling up.

Also, we don't need to compute the blocker for every table, which will
just be the same for every table, most of the time.

Additionally, monitoring tools only need to watch for a single recurring log
line, rather than parsing per-table vacuum output.

We can keep the infrastructure separate from ComputeXidHorizons as we
have done in v9-0001. Since the check is throttled (at most once per
minute), the concern about ProcArrayLock contention is effectively
eliminated.

--
Sami Imseih
Amazon Web Services (AWS)