Re: Report oldest xmin source when autovacuum cannot remove tuples

Hi Sami

> Thinking about point 3 above, I began to wonder if this
> whole thing can be simplified with inspiration. Looking at the
> existing BackendXidGetPid(), I think it can.

> Based on BackendXidGetPid(), I tried a new routine called
> BackendXidFindCutOffReason() which can take in the cutoff xmin,
> passed in by vacuum and can walk though the proc array and
> determine the reason. We don't need to touch ComputeXidHorizons()
> to make this work, it seems to me. This comes with an additional
> walk though the procarray holding a shared lock, but I don't think
> this will be an issue.

> Attached is a rough sketch of BackendXidFindCutOffReason()
> For now, I just added NOTICE messages which will log with
> VACUUM (verbose) for testing.
I like your idea , I think we also could consider introducing a GUC
parameter in the future, which would terminate sessions blocking vacuum
operations when the table's age reaches vacuum_failsafe_age.

Thanks

On Sat, Nov 15, 2025 at 8:25 AM Sami Imseih <samimseih(at)gmail(dot)com> wrote:

> 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.
>
> Adding this information to the vacuum logging is useful, but
> I can see this information being exposed in a view as well in
> the future.
>
> I have a few comments:
>
> A few minor ones:
>
> 1/ pid should be declared as "pid_t"
>
> 2/ last value of an enum should be have a traling comma
> +typedef enum OldestXminSource
> +{
> + OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION,
> + OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK,
> + OLDESTXMIN_SOURCE_PREPARED_TRANSACTION,
> + OLDESTXMIN_SOURCE_REPLICATION_SLOT,
> + OLDESTXMIN_SOURCE_OTHER
> +} OldestXminSource;
>
> More importantly:
>
> 3/ As mentioned earlier in the thread, the "idle-in-transaction"
> transactions is not being reported correctly, particularly for write
> tansactions. I think that is an important missing case. The reason
> for this is the cutoff xmin is not being looked up against the current
> list of xid's, so we are not blaming the correct pid.
>
> 4/
> Thinking about point 3 above, I began to wonder if this
> whole thing can be simplified with inspiration. Looking at the
> existing BackendXidGetPid(), I think it can.
>
> Based on BackendXidGetPid(), I tried a new routine called
> BackendXidFindCutOffReason() which can take in the cutoff xmin,
> passed in by vacuum and can walk though the proc array and
> determine the reason. We don't need to touch ComputeXidHorizons()
> to make this work, it seems to me. This comes with an additional
> walk though the procarray holding a shared lock, but I don't think
> this will be an issue.
>
> Attached is a rough sketch of BackendXidFindCutOffReason()
> For now, I just added NOTICE messages which will log with
> VACUUM (verbose) for testing.
>
> This takes what you are doing in v1 inside ComputeXidHorizons()
> into a new routine. I think this is a cleaner approach.
>
> 5/ Also, I think we should also include tests for serializable
> transactions
>
>
> What do you think?
>
> --
>
> Sami Imseih
> Amazon Web Services (AWS)
>