Re: pg_xmin_horizon: a system view of everything pinning the xmin horizon

Hi,

On Sat, Jul 11, 2026 at 5:22 PM Scott Ray <scott(at)scottray(dot)io> wrote:
>
> When VACUUM cannot remove dead tuples, working out what is pinning
> the xmin horizon means querying pg_stat_activity,
> pg_replication_slots, pg_prepared_xacts, and pg_stat_replication,
> and combining them correctly. A hand-rolled UNION can only
> approximate, because the xmin classification (shared/catalog/data)
> and the slots' effective xmins computed by ComputeXidHorizons() and
> ReplicationSlotsComputeRequiredXmin() are not exposed anywhere.
> Users who don't know all four sources exist sometimes fail to
> identify the holder at all [1].

Thanks for working on this. I'm trying to understand why we need this
implemented as a C function rather than a query sitting in
system_views.sql.

If effective xmins are emitted via the replication slots view,
wouldn't that solve the issue where slots holding xmins internally are
blocking the xmin horizon and vacuum overall?

Similarly, backends running vacuum and walsenders performing logical
decoding set special proc flags (PROC_IN_VACUUM /
PROC_IN_LOGICAL_DECODING) that cause vacuum to skip their xmins from
horizon computations. Since pg_stat_activity can't distinguish that
reliably today, can we think of adding such flags there?

If there are other missing pieces, why not emit those from the
existing views to make this feature simpler?

Would this make an SQL-only query joining pg_stat_activity,
pg_prepared_xacts, pg_replication_slots, and pg_database enough?

When the vacuum isn't removing dead tuples (bloat increasing, query
performance degrading) or freezing xids (xid warnings firing), the
user wants to know what's blocking. They check datfrozenxid, look for
long-running or idle transactions, prepared transactions, logical
slots holding catalog vacuum back, physical slots holding user table
vacuum back, and act accordingly. An SQL-only query might work well
for covering most cases, and after emitting the effective xmins and
proc flags it will also cover the case reported above.

Thoughts?

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com