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

Hi,

On Fri, Sep 11, 2026 at 5:40 PM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> I plan to review the v6 patch soon.

I reviewed the v6 patch and here are some comments. Apologies if some
of these have already been discussed. Feel free to point me to the
related discussions on any of these. I haven't reviewed the tests yet.
I will do so after the code-related comments are addressed.

1/ Good catch, and the new wording looks right. datfrozenxid can end
up older than the oldest relfrozenxid in the database, because the
computation starts from the oldest xid vacuum is allowed to remove,
not from the tables. I recommend proposing this separately and
probably back-patch it, but fine to keep it here too.

aggressive VACUUM). Similarly, the
<structfield>datfrozenxid</structfield> column of a database's
<structname>pg_database</structname> row is a lower bound on the
unfrozen XIDs
- appearing in that database &mdash; it is just the minimum of the
- per-table <structfield>relfrozenxid</structfield> values within
the database.
+ appearing in that database &mdash; it is no newer than, and normally
+ equal to, the minimum of the per-table
+ <structfield>relfrozenxid</structfield> values within the database.

2/ In both of the following places, can we have a sample query and
output with all the per-class rows present? IMO, that works better and
readily usable for those looking at it.

+ including rows for backends in other databases. Sort all rows by
+ <literal>age(shared_xmin)</literal> descending
+ to find the oldest pinners. Use <literal>NULLS LAST</literal>, because
+ a row with a null <structfield>shared_xmin</structfield> pins
+ nothing.</simpara>

+ To find the row binding a horizon class, order the applicable rows by
+ descending <function>age()</function> of the class column, placing
+ null values last.
+ <type>xid</type> has no ordering operators, so
+ <function>age()</function> serves as the wraparound-safe sort key.

3/ A few wording nits, mostly about matching what the docs already
say. The steps that follow say "old prepared transactions",
"long-running open transactions" and "old replication slots".
system-views.sgml says "backend currently holding a snapshot or
transaction ID". monitoring.sgml calls this the xmin horizon, and
describes the fourth kind as a standby's xmin horizon reported by
hot_standby_feedback, written without hyphens as the docs do
everywhere. And for shared, the docs' usual phrase is shared catalogs.

How about changing "snapshot-holding backend" to "backend holding a
snapshot or transaction ID", "per-class xmin contribution" to the
column names shared_xmin, catalog_xmin and data_xmin (or to xmin
horizons), "hot-standby-feedback sender" to "hot standby feedback
sender", and "cluster-wide shared horizon" to the horizon for shared
catalogs? Same comments applicable elsewhere in the patch.

+ <simpara>Identify the source. Query
+ <link linkend="view-pg-xmin-horizon"><structname>pg_xmin_horizon</structname></link>,
+ which reports one row per snapshot-holding backend, replication slot,
+ prepared transaction, or hot-standby-feedback sender with each row's
+ per-class <structfield>xmin</structfield> contribution. The warning
+ is driven by the database's <structfield>datfrozenxid</structfield>,
+ which can never advance past the cluster-wide shared horizon, and
+ every row's <structfield>shared_xmin</structfield> feeds that horizon,
+ including rows for backends in other databases. Sort all rows by
+ <literal>age(shared_xmin)</literal> descending
+ to find the oldest pinners. Use <literal>NULLS LAST</literal>, because

4/ The steps for finding the cause of the wraparound warnings
currently point at pg_stat_activity, pg_prepared_xacts and
pg_replication_slots, and the patch replaces those with the new view.
The intent makes sense, since the whole point of pg_xmin_horizon is to
be the first place to look. But the existing methods are not wrong
either, and there are scripts and runbooks built around them. So how
about mentioning both, something like "You can find these in
pg_xmin_horizon or existing views"? There will be no surprises for
those relying on the existing views, and they can move to the new one
if they wish.

- <simpara>End long-running open transactions. You can find these
by checking
- <link linkend="monitoring-pg-stat-activity-view"><structname>pg_stat_activity</structname></link>
for rows where
- <literal>age(backend_xid)</literal> or
<literal>age(backend_xmin)</literal> is
- large. Such transactions should be committed or rolled back,
or the session
- can be terminated using
<literal>pg_terminate_backend</literal>.</simpara>

+ <simpara>End long-running open transactions by committing, rolling back,
+ or terminating the session using
+ <literal>pg_terminate_backend</literal>.</simpara>
+ </listitem>
+ <listitem>

5/ How about "sources holding back the cluster's xmin horizon,
preventing VACUUM from removing dead row versions and clearing old
XIDs"? Matches what monitoring.sgml and maintenance.sgml say. Same
comment applies to the other places in the patch that say removal
horizon.

+ <row>
+ <entry><link
linkend="view-pg-xmin-horizon"><structname>pg_xmin_horizon</structname></link></entry>
+ <entry>sources holding back the cluster's VACUUM removal horizon</entry>
+ </row>

+ and reports each row's raw per-class <structfield>xmin</structfield>
+ contributions to the cluster's <command>VACUUM</command> removal horizon.
+ A row whose per-class <structfield>xmin</structfield> columns are all
+ null does not currently pin the horizon.

6/ "physical-replication sender" is a new word here, and calling it a
sender is confusing when what it holds is an xmin the standby sent to
it. How about "a standby_feedback row represents a WAL sender process
without a replication slot, holding the xmin horizon sent by its
standby through hot_standby_feedback"?

+ The row's kind: <literal>backend</literal>,
+ <literal>replication_slot</literal>, <literal>prepared_xact</literal>,
+ or <literal>standby_feedback</literal>. A
+ <literal>standby_feedback</literal> row represents a
physical-replication
+ sender propagating <link
linkend="guc-hot-standby-feedback"><varname>hot_standby_feedback</varname></link>
+ without a replication slot. A sender that does use a slot is reported
+ through that slot's <literal>replication_slot</literal> row instead.

7/ Why join with pg_prepared_xacts here? The point of pg_xmin_horizon
was to collect most of the things in C to avoid joining existing
views. The join also means gid reads null if the prepared transaction
ends in between, for a row the view still reports. Why not return gid
from the function itself, same as xact_start?

+ FROM pg_get_xmin_horizon() c
+ LEFT JOIN pg_prepared_xacts p
+ ON c.kind = 'prepared_xact' AND p.transaction = c.xid

+ <structfield>gid</structfield> <type>text</type>
+ </para>
+ <para>
+ The global transaction identifier for <literal>prepared_xact</literal>
+ rows, joined in from
+ <link linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link>.
+ Null for all other kinds.
+ </para></entry>
+ </para></entry>

8/ How about using "<literal>replication_slot</literal> and
<literal>standby_feedback</literal> rows pin the horizon ..."?

+ Slot and <literal>standby_feedback</literal> rows pin the horizon
+ cluster-wide regardless of

9/ This seems unnecessary. The previous sentence already says these
rows pin cluster-wide regardless of datid, so the note about filtering
on datid adds little.

+ <structfield>datid</structfield>, and a query filtering
+ by <structfield>datid</structfield> will exclude them.
+ </para></entry>

10/ "The transaction ID this row owns, or null if it owns none" reads
a bit confusing. How about "Transaction identifier of this row's
source, if any; see <xref linkend="transaction-id"/>" similar to
pg_stat_activity.backend_xid?

+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>xid</structfield> <type>xid</type>
+ </para>
+ <para>
+ The transaction ID this row owns, or null if it owns none; see
+ <xref linkend="transaction-id"/>. For

11/ How about listing all the columns here, shared_xmin, catalog_xmin
and data_xmin, for clarity?

+ <literal>standby_feedback</literal> rows. A row with a null
+ <structfield>xid</structfield> can still pin the horizon through
+ the <structfield>*_xmin</structfield> columns.

12/ Same point as the per-class one above. How about "The transaction
ID this row pins for the shared xmin horizon. Null if it does not pin
that horizon", and likewise for catalog_xmin and data_xmin? The
data_xmin entry then reads "pins none of the three horizons and shows
null in shared_xmin, catalog_xmin and data_xmin".

+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>shared_xmin</structfield> <type>xid</type>
+ </para>
+ <para>
+ The transaction ID this row pins for the shared horizon class.
+ Null if it does not pin that class.
+ </para></entry>
+ </row>

13/ How about following the existing columns here, something like
"Time when this row's transaction was started, or, for prepared_xact
rows, the time at which the transaction was prepared for commit",
similar to pg_stat_activity.xact_start and pg_prepared_xacts.prepared?

+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>xact_start</structfield> <type>timestamptz</type>
+ </para>
+ <para>
+ Time when the row's transaction was started, or, for
+ <literal>prepared_xact</literal> rows, when the transaction was
+ prepared. Null for <literal>replication_slot</literal> rows,
+ <literal>standby_feedback</literal> rows, and
+ <literal>backend</literal> rows belonging to walsender processes.
+ </para></entry>
+ </row>

14/ This paragraph took me a couple of passes to follow although I
understand how these contribute to overall XID horizon in
ComputeXidHorizons(). How about something shorter, in the order of
what a reader needs?

For replication_slot rows, shared_xmin and catalog_xmin report the
older of the slot's xmin and catalog_xmin, while data_xmin reports the
slot's xmin alone, because a slot's catalog_xmin applies to catalog
rows only, not to dead row versions in user tables. These are the
slot's effective xmins (the values the server uses when it computes
the horizon), so while a slot is being created they can briefly differ
from the ones shown in pg_replication_slots. The server also keeps a
single oldest value across all slots, recomputed only when a slot
changes, so for a short while it can be older than every value shown
here, and a horizon can be held back by a value that no row reports.
An invalidated slot holds back nothing and reports null in all three
columns.

+ <para>
+ For <literal>replication_slot</literal> rows,
+ <structfield>shared_xmin</structfield> and
+ <structfield>catalog_xmin</structfield> both report the older of the
+ slot's <structfield>xmin</structfield> and
+ <structfield>catalog_xmin</structfield>. The
+ <structfield>data_xmin</structfield> column reports the slot's

15/ This one is hard to follow. How about a short example query
instead of describing the sort in prose, as in comment 3, and
splitting the rest so each paragraph does one thing?

To find the row holding back an xmin horizon, use a query such as:
<< query here >>
xid has no ordering operators, so the sort uses age(), which is
wraparound-safe. Which rows apply depends on the column and on datid,
as described above.

When several rows report the same oldest value, that xmin horizon
cannot advance until all of them release it, so resolving only one of
them has no effect. If one of those rows has an xid equal to that
value, the horizon has to wait for that transaction to finish.

When no row reports a value for an xmin horizon, it advances on its
own to a point just past the newest completed transaction, which this
view does not show. Note also that backend rows and replication slot
rows are gathered at slightly different times, so a single query is
not a snapshot of the whole cluster at one instant. See <xref
linkend="vacuum-for-wraparound"/> for the steps to take when an xmin
horizon is held back.

+ <para>
+ To find the row binding a horizon class, order the applicable rows by
+ descending <function>age()</function> of the class column, placing
+ null values last.
+ <type>xid</type> has no ordering operators, so

16/ How about "Querying pg_xmin_horizon on a standby is not supported
and raises an error, because the view cannot report the primary's
running transactions during recovery"?

+ <para>
+ Querying <structname>pg_xmin_horizon</structname> on a hot standby is not
+ supported and will raise an error.
+ </para>

17/ How about defining a macro for this flag test similar to PROC_*
and using it in all three places, instead of a comment asking future
authors to keep them in sync?

- * decoding (which manages xmin separately, check below).
+ * decoding (which manages xmin separately, check
below). Keep this
+ * filter in sync with pg_get_xmin_horizon()'s classifier.
*/
if (statusFlags & (PROC_IN_VACUUM | PROC_IN_LOGICAL_DECODING))
continue;

18/ GetXidHorizonProcs() acquires ProcArrayLock once and gathers all
procs in a single pass, whereas pg_stat_activity takes the lock once
per backend, so each snapshot does N acquisitions and releases. I
measured this by keeping ~500 backends connected with pgbench, then
running each view repeatedly from one backend while a separate
4-client pgbench write workload ran alongside. Query exec time is
0.304 ms vs 1.335 ms for pg_xmin_horizon and pg_stat_activity
respectively (4.4x), and the write workload TPS reduces by 11.7% vs
27.4%. Connection latency is also lower since connection setup takes
the lock exclusive, measuring 2.21 ms with no polling, 2.55 ms with
pg_xmin_horizon polling, and 3.09 ms with pg_stat_activity polling.

19/ The comment mostly says what the exact code does incl. variable
names, and the one thing it adds, that the rows are mutually
consistent, is not what the shared lock gives. How about the
following?

/*
* Get the xid, xmin and other horizon inputs of every proc, in one pass over
* the proc array.
*
* These are the same per-proc inputs that ComputeXidHorizons() uses. Processes
* cannot enter or leave the array during the pass, but a process can install a
* new xmin or get an xid while the pass is still running, see
* GetSnapshotData() and GetNewTransactionId(), so the values returned can be
* from different moments.
*
* There is a race here. The lock is released before returning, so by the time
* the caller reads an entry the process may have ended its transaction or
* exited altogether.
*
* The caller is responsible for freeing the returned array.
*/

20/ How about returning both the array and its length as out
parameters, void GetXidHorizonProcs(XidHorizonProc **procs, int
*nprocs)?

21/ The count variable looks redundant. Nothing is skipped in the
loop, so count always ends up equal to arrayP->numProcs, which could
be used directly.

22/ With pgstat_get_beentry_by_proc_number(), the new function is not
needed unless I'm missing something else. It does the change count
retry internally, so something like the following is enough.

PgBackendStatus *beentry = pgstat_get_beentry_by_proc_number(p->procNumber);

if (beentry != NULL && beentry->st_procpid == pid &&
beentry->st_backendType != B_WAL_SENDER)
r->xact_start = beentry->st_xact_start_timestamp;

23/ Nit: How about using consistent wording here?
int pid; /* Backend's process ID; 0 if prepared xact */
int procNumber; /* ProcNumber, which is also the
* BackendStatusArray index; not valid for
* prepared xact dummy procs */
24/ Nit: proisstrict has no effect for a function with no arguments,
and pg_get_aios(), pg_lock_status() and pg_prepared_xact() all leave
it out. How about dropping it? The descr could also read like its
neighbours, something such as 'information about sources holding back
the cluster's xmin horizons'.

+{ oid => '8054',
+ descr => 'per-source inputs to the cluster xmin horizons',
+ proname => 'pg_get_xmin_horizon', prorows => '100', proisstrict => 'f',
+ proretset => 't', provolatile => 'v', proparallel => 'r',
prorettype => 'record',
+ proargtypes => '',
+ proallargtypes => '{text,int4,name,oid,xid,xid,xid,xid,timestamptz}',
+ proargmodes => '{o,o,o,o,o,o,o,o,o}',
+ proargnames =>
'{kind,pid,slot_name,datid,xid,shared_xmin,catalog_xmin,data_xmin,xact_start}',
+ prosrc => 'pg_get_xmin_horizon', proacl =>
'{POSTGRES=X,pg_read_all_stats=X}' },

25/ How about merging the second and third passes in
pg_get_xmin_horizon(), something like the attached diff on top of v6?
Emitting rows while holding ReplicationSlotControlLock seems okay, the
same way pg_get_replication_slots() already does it. Is there a reason
not to do this?

26/ Nit: How about something like the following to be clearer?

* procarrayfuncs.c
* SQL-callable functions related to POSTGRES process array code.
*

27/ For a parallel query, and for concurrent REPACK with decoding
workers, the leader and every worker gets its own pinning row. Nothing
in the view itself links a worker row back to its leader, though
joining pg_stat_activity on pid gives leader_pid for anyone who wants
it. How about a doc note saying so?

28/ Decoding through pg_logical_slot_get_changes() shows up as an
ordinary backend row with an xmin of its own, which is worth knowing
for anyone looking at what holds the horizon back. How about a doc
note saying so?

29/ The note in the docs about a backend running VACUUM showing null
in shared_xmin, catalog_xmin and data_xmin holds only for lazy VACUUM,
which is the one path that sets PROC_IN_VACUUM. VACUUM FULL and
CLUSTER report an xid and all three xmins, and ANALYZE reports all
three xmins with a null xid. How about clarifying this in the docs?

statement xid shared_xmin catalog_xmin data_xmin
lazy VACUUM null null, null, null
VACUUM FULL 668 668, 668, 668
CLUSTER 681 681, 681, 681
ANALYZE null 681, 681, 681

Thanks for reading this far.

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