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

Hi Sami,

On Monday, September 28th, 2026 at 12:36 PM, Sami Imseih <samimseih(dot)pg(at)gmail(dot)com> wrote:

> After thinking about this some more, I think the right level for the
> shared part is the small procarray rules next to ComputeXidHorizons(),
> rather than a new procarrayfuncs.c file.

ComputeXidHorizons() and GetXidHorizonProcs() already share what they
can. The patch adds procarrayfuncs.c for SQL access, following
lockfuncs.c, slotfuncs.c, and xlogfuncs.c.

> returns latestCompletedXid + 1, the recovery KnownAssignedXids xmin, and
> PGPROC entries that can affect horizons, with their classification
> (backend, prepared transaction, or standby feedback), database OID, and
> effective xmin.

Bharath asked for standby support as well [4], and v8 adds it.

> I did not read "sources" here as necessarily meaning one row per PID. I
> went digging into older conversations and found [2], where Andres lays
> out a potential shape for this view, where we key every row by "datname"
> and "horizon", and where the shared horizon itself is a NULL datname.

Andres sketched a different view in [2] than "all the sources" [1]
suggests.

> So I think we can start out with a function like this which just returns
> the data for each datid/horizon, such as:
> ...
> And using the base function, we can construct a system view, or document
> a way to use it with joins on other system views to come up with a view
> that looks like:

Your base function returns no pid, slot_name or gid, but your derived
view shows all three. Please see previous discussion in this thread
about limitations of joining existing system views.

> So I wonder if this is a
> better starting point than exposing one row per source, while keeping
> the source-attribution details simpler for the first version?

Your summary achieves simplicity by hiding information that DBAs need,
including the other sources and gaps between their xmins. The horizon
moves only when all sources stop pinning it to its current value, and
the next pinner determines how much it moves. A DBA might cancel a
long-running query only to observe the horizon move by one XID and
become pinned by a pg_dump the summary hid.

> What I like about something like this is that it is condensed and easy
> for a DBA to act on.

Cybertec [5], Citus [6], pganalyze [7] and Azure [8] all attempt to
list every source, which suggests DBAs do not need a "condensed"
summary.

[4] https://www.postgresql.org/message-id/CALj2ACWiuAh0BmePj1J7U_KtK1KE8rY89kN5BvWD+qUjmzAsAg@mail.gmail.com
[5] https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/
[6] https://www.citusdata.com/blog/2022/07/28/debugging-postgres-autovacuum-problems-13-tips/
[7] https://pganalyze.com/docs/checks/vacuum/xmin_horizon
[8] https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-autovacuum-tuning

--
Scott Ray