MIME-Version: 1.0
References: <6634e1b0b5b74e5c2e3b06201120a81e11d82a55.camel@cybertec.at>
 <3bnBUxwx2npXqvHL0trI11LOOvzQ7LI0GzWqbaj5SJnk7DTb1uzStGveKwj0JJmBW4ebzGIF3az7of4I4rQeaO_PRqDnnClCduPyjM6gPgM=@scottray.io>
 <CAOzEurTC+5xuH_X2EBWjW0Fs9iuTq3OMA=1FfT4xx1wYbCAR6g@mail.gmail.com>
 <20260602.165755.1084850050958933598.horikyota.ntt@gmail.com>
 <CAA5RZ0tF3B-NASWX8kFQ6jbXyRzkpkQh=CuWs6Zmd+Fw3Wu+Fg@mail.gmail.com>
In-Reply-To: 
 <CAA5RZ0tF3B-NASWX8kFQ6jbXyRzkpkQh=CuWs6Zmd+Fw3Wu+Fg@mail.gmail.com>
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Wed, 3 Jun 2026 10:11:01 +0900
Message-ID: 
 <CAOzEurTXNzS8C1_pE3ijwhDt-YZgs_H+JCR3dBg9GzKJszdg1Q@mail.gmail.com>
Subject: Re: Report oldest xmin source when autovacuum cannot remove tuples
To: Sami Imseih <samimseih@gmail.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>, scott@scottray.io,
 laurenz.albe@cybertec.at,
	japinli@hotmail.com, qiuwenhuifx@gmail.com,
	pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAOzEurTXNzS8C1_pE3ijwhDt-YZgs_H%2BJCR3dBg9GzKJszdg1Q%40mail.gmail.com>
Precedence: bulk

On Wed, Jun 3, 2026 at 8:34=E2=80=AFAM Sami Imseih <samimseih@gmail.com> wr=
ote:
>
> 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 informa=
tion
> 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?

--=20
Best regards,
Shinya Kato
NTT OSS Center