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_g3qyb6nnzjtnstyuf87v...@mail.gmail.com -- Scott Ray
v1-0001-Add-pg_xmin_horizon-view-showing-per-input-horizo.patch
Description: Binary data
signature.asc
Description: OpenPGP digital signature
