MIME-Version: 1.0
References: 
 <CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com>
 <CAHGQGwFVdccfhN7PFHU5gZcMNdQuyL8DhZSks2FuuTcZ5V_pGQ@mail.gmail.com>
 <CAGjGUAL-qZ6Na-cH4RY5raF4NwuF47ZVNyP0uMYQcks3w5b7Dw@mail.gmail.com>
 <CAA5RZ0s+UUXekbeGcC-H71kW=MfeaUCOV=yEWX94NXViO2-=pA@mail.gmail.com>
 <CAFiTN-te-2RBhz3AdcyQYVqyxkLKgK6xnvLaXgdo=Jc2ywFd9A@mail.gmail.com>
In-Reply-To: 
 <CAFiTN-te-2RBhz3AdcyQYVqyxkLKgK6xnvLaXgdo=Jc2ywFd9A@mail.gmail.com>
From: Sami Imseih <samimseih@gmail.com>
Date: Mon, 17 Nov 2025 12:35:46 -0600
Message-ID: 
 <CAA5RZ0vsipTWNhnsUgjg-G-SxUjgAiN8ksg62RH+hqNbrazxDQ@mail.gmail.com>
Subject: Re: Report oldest xmin source when autovacuum cannot remove tuples
To: Dilip Kumar <dilipbalaut@gmail.com>
Cc: wenhui qiu <qiuwenhuifx@gmail.com>, Shinya Kato <shinya11.kato@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA5RZ0vsipTWNhnsUgjg-G-SxUjgAiN8ksg62RH%2BhqNbrazxDQ%40mail.gmail.com>
Precedence: bulk

> > Thanks for starting this thread! This is a very useful
> > feature that users will find beneficial to easily narrow
> > down the reason the xmin horizon is being held back,
> > and take action.
>
> +1 for the idea.  In BackendXidFindCutOffReason() you have directly
> reported using NOTICE I believe that is just to show the idea and you
> are planning to append this to the main message?

Yes, the NOTICE is just for demonstration. Some more work is needed
to integrate the output into the vacuum log.

> Apart from that we are looping the whole pgprocarray, however it is
> only done when we are vacuuming with verbose mode so might not
> be that bad.

The extra procarray loop will occur during VACUUM VERBOSE
or when a vacuum exceeds log_autovacuum_min_duration, 10
minutes by default.

I do think however, we should be more selective when to
do this work. We should only care about emitting this
information in autovacuum logging if the cutoffs->OldestXmid
did not advance between consecutive vacuums. This will
mean we we will need to track the last cutoff value in relation stats
(pg_stat_user_tables), but I think having this value in
stats will be useful on its own actually, as users can use
it to track tables with stalled cutoffs even if they don't
have sufficient autovacuum logging.

What do you think?

--
Sami Imseih
Amazon Web Services (AWS)