Re: 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(at)scottray(dot)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] https://www.postgresql.org/message-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