Re: Report oldest xmin source when autovacuum cannot remove tuples

(A colleague pointed me at this thread just now, because I somehow
missed it, but I've also been playing around with a solution for this
recently. Your patches are much farther along than mine -- hooray!)

On Tue, Jun 2, 2026 at 6:11 PM Shinya Kato <shinya11(dot)kato(at)gmail(dot)com> wrote:
> 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.

+1. I'd *really* like to have this information logged at the point
that VACUUM is blocked; it'll help during support escalations (or
prevent them!) in a way that a view cannot.

That's not to say we couldn't also have a view, of course.

> 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?

I don't want to weigh in very strongly here; you've all been thinking
about it for longer than I have. So, taking off the committer hat and
putting my user hat on:

I ran into the same decision between 1) tracking the origin during
horizon calculation and 2) attempting to reconstruct it after the
fact. I decided, for my own patch, that I'd rather track them during
horizon calculation. The cases where I really need these logs are
high-stress, chaotic situations where things are falling apart, and if
the database's answer to "what's causing the problem?" is ever "I
don't know, you better run VACUUM again", I think that's likely to
spike my blood pressure.

As discussed above, though, this approach leads to a collision issue.
So I put the patch down before PGConf.dev, trying to figure out how
best to solve that.

(That said, there's nothing wrong with getting a good solution in and
working on a better one, as long as the first patch doesn't make the
second one harder. Thank you for working on this!)

--Jacob