Re: Report oldest xmin source when autovacuum cannot remove tuples

On Wed, Jun 3, 2026 at 8:34 AM Sami Imseih <samimseih(at)gmail(dot)com> wrote:
>
> Hi,
>
> > This may be just my personal preference, but I'd rather see
> > information about xid horizon retention exposed through a SQL-visible
> > interface than added to the VACUUM log.
> >
> > VACUUM VERBOSE already reports the removable cutoff and how old it was
> > when the operation ended. That seems sufficient as a signal that the
> > xid horizon is being held back.
> >
> > What I would want to investigate after seeing such a signal is not
> > necessarily what happened to be blocking that particular VACUUM
> > operation at some earlier point, but what is holding the xid horizon
> > back now. For that purpose, a view exposing the current xid/xmin
> > holders seems more useful and less misleading than adding a
> > best-effort blocker guess to the VACUUM log.
>
> +1, I also had a similar comment at the bottom of [1], where this information
> is better to be exposed in SQL for proactive monitoring.

I agree that exposing xid horizon retention information via a
SQL-visible interface is valuable. However, I believe reporting it
in the VACUUM log is also important: a view only shows the current
state, so once a blocker has gone away there is no way to determine,
after the fact, what was holding the horizon back at the time a
particular VACUUM ran. Logs are the only durable record we have for
that kind of post-hoc analysis.

That said, I share the concern about emitting best-effort
information in the VACUUM log, which otherwise reports facts
observed during the operation. Would it be acceptable if we
reported the exact blocker -- captured at the moment OldestXmin is
computed -- rather than a best-effort guess reconstructed
afterwards?

--
Best regards,
Shinya Kato
NTT OSS Center