pg_xmin_horizon: a system view of everything pinning the xmin horizon

Hi all,

On Thu, Jul 16, 2026 at 3:18 PM Scott Ray <scott@scottray.io> wrote:

Hi,

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]. Commercial tooling, including
pganalyze's xmin-horizon check and RDS's postgres_get_av_diag(),
exists in part because this surface is missing from core. It is
also on Andres's wishlist: "Today it takes a lot of knowledge to
find all potential sources of holding back the xmin horizon. We
should have a view showing all the sources of the horizon being
held back." [2]

The attached patch adds a pg_xmin_horizon system view, readable by
pg_read_all_stats, with one row per horizon input:

- kind: 'backend', 'replication_slot', 'prepared_xact', or
'standby_feedback' (a slot-less hot-standby-feedback walsender)
- identity: pid, slot_name, gid (joined from pg_prepared_xacts),
datid, xact_start
- contribution: shared_xmin, catalog_xmin, data_xmin (the row's raw
per-class pin)

Here's sample output from a scenario in which an old prepared
transaction pins the horizon and blocks two logical slots. It also
includes an idle physical slot.

kind | pid | slot_name | gid | datid | shared_xmin | catalog_xmin | data_xmin | xact_start
------------------+-------+---------------+-----------------+-------+-------------+--------------+-----------+-------------------------------
backend | 33810 | | | 5 | 697 | 697 | 697 | 2026-07-07 14:28:05.846981-07
prepared_xact | | | orders_batch_42 | 5 | 697 | 697 | 697 | 2026-07-07 14:27:28.579493-07
replication_slot | | cdc_export | | 5 | 697 | 697 | 697 |
replication_slot | | standby1_slot | | | | | |
replication_slot | | cdc_slot | | 5 | 697 | 697 | |

The prepared transaction owns xid 697, and the backend row, which is
the querying session, shows the same xmin. The idle physical slot
pins nothing. The cdc_export row demonstrates a gap in
pg_replication_slots that this view fills: The cdc_export can't
obtain a snapshot until the prepared transaction resolves, so
although its effective_xmin is set, data.xmin is still invalid, and
pg_replication_slots shows this:

slot_name | xmin | catalog_xmin
---------------+------+--------------
cdc_export | | 697
cdc_slot | | 697
standby1_slot | |

A view like this one was discussed on the "Report oldest xmin source
when autovacuum cannot remove tuples" thread [3], where Shinya's
CF 6188 patches add a VACUUM log line naming the single
highest-priority blocker. The two are complementary, and several
people there (Shinya, Sami, Kyotaro) have anticipated a view like
this. The log line is the durable post-hoc record for one vacuum,
and the view is the live, complete picture. The view shows every
horizon input, including non-binding ones, with the raw values
needed to answer "and what would the horizon become if I removed
this one?" (leave-one-out).

Design notes:

1. The view includes all procs that could plausibly pin something,
including ones that pin nothing, like VACUUM backends,
logical-decoding backends, and idle or invalidated slots. There
is no built-in ranking.

2. A backend row's catalog_xmin and data_xmin apply within its
datid, and its shared_xmin applies cluster-wide. A slot or
standby_feedback row applies cluster-wide. The docs explain
datid scoping.

3. Procs are captured in one ProcArrayLock SHARED pass, and slots
are captured in a separate pass holding
ReplicationSlotControlLock and per-slot spinlocks. Holding both
would risk increasing ProcArrayLock contention. It would also
introduce a new locking pattern, and with it, the danger that a
future patch could acquire the locks in the opposite order and
cause deadlocks. The trade-off is that this view does not
guarantee cross-consistency.

4. The view shows the effective xmins for slots, which is what
ReplicationSlotsComputeRequiredXmin() aggregates, and shows
invalidated slots with NULL xmin columns. This aligns with two
CF 6188 fixes in v7.

5. If queried on a standby, the SRF reports an error.
KnownAssignedXids affect the standby's horizon but are not
available in the procarray. The error prevents providing
incomplete data that might be interpreted as complete. This
aligns with an Assert and comment in CF 6188 v8. A follow-up
patch could add a synthetic row with kind = 'known_assigned_xids'
to fill the gap and remove this error.

6. The patch includes an SQL recipe to compute leave-one-out. Given
the raw xmin values, it isn't always easy to predict how much
the xmin horizon would advance if a row were removed, even if it
has the lowest xmin. If another row's xmin is slightly higher,
then it might move just a little, or it might not move at all if
multiple rows share the lowest xmin. The recipe helps users plan
how to fix problems.

7. The classification logic mirrors ComputeXidHorizons' logic, and
if they are not kept synchronized, the view will lose fidelity.
The patch adds comments to warn future patch authors against
updating one without also updating the other.

8. I expect the CF 6188 patch will land first. We discussed on that
thread the possibility that the log and the view could use a
shared helper function, but whether this happens and when is
TBD.

Tests: a recovery TAP test covers the replication_slot and
standby_feedback rows, the recovery error, tied xmins via an
exported snapshot, and the all-NULL VACUUM backend row; a
regression test covers the view's ACL, backend rows, and the
prepared-xact row. There's an alternate expected file for
installcheck with max_prepared_transactions < 2.

The patch applies to master as of 8f7af125e03.

[1] https://trigger.dev/blog/stopping-xmin-horizon-blocking-postgres-vacuuming
[2] https://wiki.postgresql.org/wiki/User:Andresfreund/Desired_Changes
[3] /messages/by-id/CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com

Thank you for the patch. I reviewed and tested this patch on my tree.
I performed runtime testing of the new pg_xmin_horizon system view
covering different xmin horizon contributors with the following test
cases:

1. Verified that the new pg_xmin_horizon view is created and
accessible after applying the patch.
2. Tested backend transactions by creating long-running transactions
and confirmed that backend contributors are reported with the expected
shared_xmin, catalog_xmin, and data_xmin values.
3. Tested prepared transactions by enabling max_prepared_transactions,
creating prepared transactions using PREPARE TRANSACTION, and verified
that prepared_xact entries appear in pg_xmin_horizon and are removed
immediately after COMMIT PREPARED.
4. Tested physical and logical replication slots. Verified that
replication slot entries appear correctly in pg_xmin_horizon, and that
the reported horizon values are consistent with pg_replication_slots.
And for logical replication slots, I also generated WAL activity and
revalidated the reported horizon values.
5. Compared the information reported by pg_xmin_horizon with the
corresponding information from pg_stat_activity, pg_prepared_xacts,
and pg_replication_slots, and found the reported values to be
consistent.
6. Verified a scenario with multiple simultaneous contributors
(backend transaction, prepared transaction, and logical replication
slot). The view correctly displayed one row for each contributor,
providing a consolidated view of the xmin horizon state.
7. Verified cleanup by committing prepared transactions and dropping
replication slots, confirming that the corresponding entries were
removed from pg_xmin_horizon as expected.

During testing, I also observed that creating a logical replication
slot while an unresolved prepared transaction existed caused
pg_create_logical_replication_slot() to wait until the prepared
transaction was resolved. After completing the prepared transaction,
logical slot creation was completed successfully.
I would like to suggest that it may also be worth considering exposing
the database name (datname) in addition to datid for better
observability and easier interpretation by DBAs as a future
enhancement, although the current implementation is functionally
correct.
Based on my testing, the new view behaved as expected and seems good
as it simplifies the inspection of xmin horizon contributors by
consolidating information that would otherwise require querying
multiple system catalogs. Overall, the patch looks good to me.

Regards,
Solai

On Thursday, July 16th, 2026 at 11:56 PM, solai v <solai.cdac@gmail.com> wrote:

I would like to suggest that it may also be worth considering exposing
the database name (datname) in addition to datid for better
observability and easier interpretation by DBAs as a future
enhancement, although the current implementation is functionally
correct.

Thanks for testing the patch.

Adding datname is a good idea and follows the precedent of
pg_stat_activity, pg_replication_slots, and pg_prepared_xacts. The
attached v2 adds datname.

--
Scott Ray