Subject: Report oldest xmin source when autovacuum cannot remove tuples

From: Shinya Kato <[email-masked]>
Date: Fri, 31 Oct 2025 15:31:40 +0900

Hi hackers,

I am proposing to add the reason for the oldest xmin to VACUUM logs.
This feature would be useful for identifying why dead tuples cannot be
removed, thereby helping to diagnose and prevent table bloat.

The current logs only indicate that dead tuples could not be reclaimed
due to the oldest xmin, but they do not reveal the underlying reason.
To identify the cause, it is necessary to query multiple views:
pg_stat_activity (for active transactions), pg_prepared_xacts (for
prepared statements), pg_replication_slots (for replication slots),
and pg_stat_replication (for hot standby feedback). However, because
the data in these views is volatile, it is difficult to retroactively
determine what was holding the oldest xmin at the specific time the
log message was generated.

This PoC patch addresses this problem. The implementation now outputs
the reason for the oldest xmin and, where applicable, the backend PID.
This information was originally discarded when calculating the oldest
xmin horizon, and the computation required to retrieve these reasons
is considered reasonable.

The patch is attached. What do you think?

-- 
Best regards,
Shinya Kato
NTT OSS Center

---

From: wenhui qiu <[email-masked]>
Date: Fri, 31 Oct 2025 16:00:54 +0800

HI
  Thank you for your path ,This path is extremely helpful.
> +/*
> + * Identifies what determined a relation's OldestXmin horizon.
> + * Used by autovacuum to report why dead tuples were not removable.
> + */
> +typedef enum OldestXminSource
> +{
> + OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION,
> + OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK,
> + OLDESTXMIN_SOURCE_PREPARED_TRANSACTION,
> + OLDESTXMIN_SOURCE_REPLICATION_SLOT,
> + OLDESTXMIN_SOURCE_OTHER
> +} OldestXminSource;
> +
> +typedef struct OldestXminInfo
> +{
> + OldestXminSource source;
> + int backend_pid;
> +} OldestXminInfo;
I have a question for like this
one session
begin;
select * from table_a
not commit or not closed session
It is in OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION type ?

Thank

---

From: Shinya Kato <[email-masked]>
Date: Tue, 4 Nov 2025 10:29:26 +0900

>   Thank you for your path ,This path is extremely helpful.

Thank you!

> It is in OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION type ?

Exactly. Looking at 010_autovacuum_oldest_xmin_reason.pl should make
it clear which logs are output in which cases. (I just noticed there
seems to be a case where the test fails. I need to fix that.)

-- 
Best regards,
Shinya Kato
NTT OSS Center

---

From: Fujii Masao <[email-masked]>
Date: Wed, 5 Nov 2025 00:58:51 +0900

> removed, thereby helping to diagnose and prevent table bloat.

+1

I like this idea. Thanks for working on this!

> The patch is attached. What do you think?

According to cfbot, the 010_autovacuum_oldest_xmin_reason regression test
passes on some platforms but fails on others (see [1]), so it doesn't
appear stable.

When I set up a primary and standby with hot_standby_feedback enabled,
then created an old prepared transaction expected to prevent dead tuples
from being vacuumed, VACUUM VERBOSE reported "hot standby feedback"
instead of "prepared transaction" as the oldest xmin source. This isn't a bug
since both xmins are the same in this case. But it may be confusing?
Would it be better to report "prepared transaction" in such cases?

+ case OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION:
+ msgfmt = include_pid ?
+ _("oldest xmin source: active transaction (pid=%d)\n") :
+ _("oldest xmin source: active transaction\n");
+ break;
+ case OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK:
+ msgfmt = include_pid ?
+ _("oldest xmin source: hot standby feedback (pid=%d)\n") :
+ _("oldest xmin source: hot standby feedback\n");

In these two cases, the PID should always be non-zero, so the message
formats without (pid=%d) might not be necessary.

+ /* Identify what forced each of the horizons above. */
+ OldestXminInfo shared_oldest_nonremovable_info;
+ OldestXminInfo catalog_oldest_nonremovable_info;
+ OldestXminInfo data_oldest_nonremovable_info;
+ OldestXminInfo temp_oldest_nonremovable_info;

It might be good to add a comment explaining why we track
sources only for these four oldest xmins, and not for others
like oldest_considered_running.

+ TransactionId old;
+ TransactionId new_horizon;
+
+ if (!TransactionIdIsValid(candidate))
+ return;

The TransactionIdIsValid(candidate) check may be redundant,
since TransactionIdOlder(old, candidate) already performs
the same validation.

- switch (GlobalVisHorizonKindForRel(rel))
+ kind = GlobalVisHorizonKindForRel(rel);
+ switch (kind)

This change doesn't seem necessary.

Regards,

[1] https://cirrus-ci.com/task/6063548834512896

-- 
Fujii Masao

---

From: wenhui qiu <[email-masked]>
Date: Tue, 11 Nov 2025 18:40:04 +0800

Hi
Thank you for your response , In this context, that session  state is idle
in transaction .If we could further distinguish between active sessions
that are still running and idle sessions, that would be clearer.

Thanks

---

From: Sami Imseih <[email-masked]>
Date: Fri, 14 Nov 2025 18:25:36 -0600

Thanks for starting this thread! This is a very useful
feature that users will find beneficial to easily narrow
down the reason the xmin horizon is being held back,
and take action.

Adding this information to the vacuum logging is useful, but
I can see this information being exposed in a view as well in
the future.

I have a few comments:

A few minor ones:

1/ pid should be declared as "pid_t"

2/ last value of an enum should be have a traling comma

More importantly:

3/ As mentioned earlier in the thread, the "idle-in-transaction"
transactions is not being reported correctly, particularly for write
tansactions. I think that is an important missing case. The reason
for this is the cutoff xmin is not being looked up against the current
list of xid's, so we are not blaming the correct pid.

4/
Thinking about point 3 above, I began to wonder if this
whole thing can be simplified with inspiration. Looking at the
existing  BackendXidGetPid(), I think it can.

Based on BackendXidGetPid(), I tried a new routine called
BackendXidFindCutOffReason() which can take in the cutoff xmin,
passed in by vacuum and can walk though the proc array and
determine the reason. We don't need to touch ComputeXidHorizons()
to make this work, it seems to me. This comes with an additional
walk though the procarray holding a shared lock, but I don't think
this will be an issue.

Attached is a rough sketch of BackendXidFindCutOffReason()
For now, I just added NOTICE messages which will log with
VACUUM (verbose) for testing.

This takes what you are doing in v1 inside ComputeXidHorizons()
into a new routine. I think this is a cleaner approach.

5/ Also, I think we should also include tests for serializable
transactions

What do you think?

--

Sami Imseih
Amazon Web Services (AWS)
From 53915bc1fd06790fc112cb2ac9e4b9caa742cf92 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email-masked]>
Date: Fri, 14 Nov 2025 18:15:25 -0600
Subject: [PATCH 1/1] sketch of cutoff reasons

---
 src/backend/access/heap/vacuumlazy.c |  5 +++
 src/backend/storage/ipc/procarray.c  | 60 ++++++++++++++++++++++++++++
 src/include/storage/procarray.h      |  1 +
 3 files changed, 66 insertions(+)

diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index deb9a3dc0d1..df1df6b0733 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -152,6 +152,7 @@
 #include "storage/bufmgr.h"
 #include "storage/freespace.h"
 #include "storage/lmgr.h"
+#include "storage/procarray.h"
 #include "storage/read_stream.h"
 #include "utils/lsyscache.h"
 #include "utils/pg_rusage.h"
@@ -1047,6 +1048,10 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
 			appendStringInfo(&buf,
 							 _("removable cutoff: %u, which was %d XIDs old when operation ended\n"),
 							 vacrel->cutoffs.OldestXmin, diff);
+
+			if (vacrel->recently_dead_tuples > 0)
+				BackendXidFindCutOffReason(vacrel->cutoffs.OldestXmin);
+
 			if (frozenxid_updated)
 			{
 				diff = (int32) (vacrel->NewRelfrozenXid -
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index 200f72c6e25..45dfe8a9be2 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -3244,6 +3244,66 @@ BackendXidGetPid(TransactionId xid)
 	return result;
 }
 
+void
+BackendXidFindCutOffReason(TransactionId xid)
+{
+	ProcArrayStruct *arrayP = procArray;
+	TransactionId *other_xids = ProcGlobal->xids;
+	bool		found_reason = false;
+
+	Assert(xid != InvalidTransactionId);
+
+	LWLockAcquire(ProcArrayLock, LW_SHARED);
+
+	elog(NOTICE, ">>>>>> looking up reason for %d", xid);
+
+	for (int index = 0; index < arrayP->numProcs; index++)
+	{
+		int			pgprocno = arrayP->pgprocnos[index];
+		PGPROC	   *proc = &allProcs[pgprocno];
+
+		/* Case 1: xid matches the session's backend XID */
+		if (other_xids[index] == xid)
+		{
+			if (proc->pid == 0)
+				/* with a prepared transaction */
+				elog(NOTICE, ">>>>>> prepared transaction proc->statusFlags %u", proc->statusFlags);
+			else
+				/* or a write transaction */
+				elog(NOTICE, ">>>>>> xid: transaction BackendXidGetPid = %d proc->statusFlags %u", proc->pid, proc->statusFlags);
+
+			found_reason = true;
+			break;
+		}
+
+		/* Case 2: xid matches xmin */
+		if (proc->xmin == xid)
+		{
+			/* or affects horizons, which is due to hot_standby_feedback */
+			if (proc->statusFlags & PROC_AFFECTS_ALL_HORIZONS)
+			{
+				elog(NOTICE, ">>>>>> hot_standby_feedback == pid of walreceiver %d proc->statusFlags %u", proc->pid, proc->statusFlags);
+				found_reason = true;
+				break;
+			}
+
+			/* or a read-only transaction */
+			elog(NOTICE, ">>>>>> xmin: transaction BackendXidGetPid = %d proc->statusFlags = %u", proc->pid, proc->statusFlags);
+			found_reason = true;
+			break;
+		}
+	}
+
+	/*
+	 * we failed to find reason, so it's likely a logical replication slot, or
+	 * some other reason
+	 */
+	if (!found_reason)
+		elog(NOTICE, ">>>>>> other reasons, including logical replication slot");
+
+	LWLockRelease(ProcArrayLock);
+}
+
 /*
  * IsBackendPid -- is a given pid a running backend
  *
diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h
index 2f4ae06c279..b0cdeedb848 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -71,6 +71,7 @@ extern void ProcNumberGetTransactionIds(int procNumber, TransactionId *xid,
 extern PGPROC *BackendPidGetProc(int pid);
 extern PGPROC *BackendPidGetProcWithLock(int pid);
 extern int	BackendXidGetPid(TransactionId xid);
+extern void BackendXidFindCutOffReason(TransactionId xid);
 extern bool IsBackendPid(int pid);
 
 extern VirtualTransactionId *GetCurrentVirtualXIDs(TransactionId limitXmin,
-- 
2.50.1 (Apple Git-155)

---

From: wenhui qiu <[email-masked]>
Date: Mon, 17 Nov 2025 09:43:05 +0800

Hi Sami

> VACUUM (verbose) for testing.
I like your idea , I think we also could consider introducing a GUC
parameter in the future, which would terminate sessions blocking vacuum
operations when the table's age reaches vacuum_failsafe_age.

Thanks

---

From: Dilip Kumar <[email-masked]>
Date: Mon, 17 Nov 2025 08:21:04 +0530

> down the reason the xmin horizon is being held back,

+1 for the idea.  In BackendXidFindCutOffReason() you have directly
reported using NOTICE I believe that is just to show the idea and you
are planning to append this to the main message?  Apart from that we
are looping the whole pgprocarray, however it is only done when we are
vacuuming with verbose mode so might not be that bad.

-- 
Regards,
Dilip Kumar
Google

---

From: Sami Imseih <[email-masked]>
Date: Mon, 17 Nov 2025 12:35:46 -0600

> are planning to append this to the main message?

Yes, the NOTICE is just for demonstration. Some more work is needed
to integrate the output into the vacuum log.

> Apart from that we are looping the whole pgprocarray, however it is
> only done when we are vacuuming with verbose mode so might not
> be that bad.

The extra procarray loop will occur during VACUUM VERBOSE
or when a vacuum exceeds log_autovacuum_min_duration, 10
minutes by default.

I do think however, we should be more selective when to
do this work. We should only care about emitting this
information in autovacuum logging if the cutoffs->OldestXmid
did not advance between consecutive vacuums. This will
mean we we will need to track the last cutoff value in relation stats
(pg_stat_user_tables), but I think having this value in
stats will be useful on its own actually, as users can use
it to track tables with stalled cutoffs even if they don't
have sufficient autovacuum logging.

What do you think?

---

From: Andres Freund <[email-masked]>
Date: Wed, 17 Dec 2025 19:30:04 -0500

Hi,

> The patch is attached. What do you think?

The added tests never seem to pass in CI:
https://cirrus-ci.com/github/postgresql-cfbot/postgresql/cf%2F6188

Greetings,

Andres Freund

---

From: Shinya Kato <[email-masked]>
Date: Fri, 26 Dec 2025 14:34:01 +0900

Thank you all for the review comments, and sorry for the late reply.
I will address the review comments in order.

> VACUUM (verbose) for testing.

Thanks for the revised proposal! Your approach is clear and makes the
code easier to read. However, I’m hesitant to proceed with this idea
for the following reasons:

- The original proposal extends ComputeXidHorizons(), which is always
calculated, so there is almost no additional overhead.

- Your proposal incurs additional cost. Furthermore, the time lag
between the execution of ComputeXidHorizons() and
BackendXidFindCutOffReason() could lead to inaccurate logging.

- I don't believe it is necessary to distinguish between active
transactions and "idle in transaction." These states can change
rapidly, and as long as we have the PID, we can check the current
status via pg_stat_activity.

- Your comment made me realize that it might be appropriate to expose
the oldest xmin in the pg_stat_{all,user,sys}_tables views, rather
than just logging it. In that case, we would need to calculate the
oldest xmin horizon every time. This might be a topic for a separate
thread, but we could consider adding columns such as:
  - pg_stat_{all,user,sys}_tables.last_vacuum_oldest_xmin (xid)
  - pg_stat_{all,user,sys}_tables.last_vacuum_oldest_xmin_source (text)

--
Best regards,
Shinya Kato
NTT OSS Center

---

From: wenhui qiu <[email-masked]>
Date: Thu, 22 Jan 2026 14:58:29 +0800

Hi Sami
> Alternatively, this information might be better exposed in a new system
> view, showing the "PID", "XID," and "reason" a transaction is blocking
VACUUM.
> This approach is more proactive as a DBA can continuously monitor for
> blocking reasons and take action before it becomes an issue.
Yes ,If this approach is acceptable, then as a reference for the standby
conflict-handling and query replay mechanism, we could consider introducing
a GUC parameter to terminate PID that blocks VACUUM freeze when the
relation age is approaching the vacuum_failsafe_age threshold.

Thanks

On Tue, Jan 6, 2026 at 6:02 AM Sami Imseih <[email-masked]> wrote:

> > code easier to read.
> My approach is focused on correctness rather than simplicity.
>
> The current logic in ComputeXidHorizons can report the wrong PID for
> blocking transactions. For example:
>
> 1. start a pgbench with 5 clients for some time ( i.e. 5 minutes )
> ```
> pgbench -i -s50
> pgbench -c5 -T300
> ````
>
> 2. start a long running transaction that consumes an XID
> ```
> postgres=# begin;
> BEGIN
> postgres=*# SELECT txid_current(), pg_backend_pid();
>  txid_current | pg_backend_pid
> --------------+----------------
>       3665231 |         266601
> (1 row)
> ```
>
> 3. run a vacuum
> ```
> postgres=# vacuum verbose pgbench_accounts ;
> INFO:  vacuuming "postgres.public.pgbench_accounts"
> INFO:  finished vacuuming "postgres.public.pgbench_accounts": index scans:
> 0
> pages: 0 removed, 59833 remain, 59043 scanned (98.68% of total), 0
> eagerly scanned
> tuples: 0 removed, 1045578 remain, 35425 are dead but not yet removable
> removable cutoff: 4301694, which was 35981 XIDs old when operation ended
> oldest xmin source: active transaction (pid=267064)
> frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
> visibility map: 0 pages set all-visible, 0 pages set all-frozen (0
> were all-visible)
> index scan bypassed: 405 pages from table (0.68% of total) have 1494
> dead item identifiers
> avg read rate: 1320.425 MB/s, avg write rate: 2.404 MB/s
> buffer usage: 67745 hits, 49974 reads, 91 dirtied
> WAL usage: 1 records, 0 full page images, 299 bytes, 0 full page image
> bytes, 0 buffers full
> system usage: CPU: user: 0.22 s, system: 0.06 s, elapsed: 0.29 s
> VACUUM
> ```
>
> VACUUM reports the oldest-XID source PID as 267090 , but the correct
> PID is 267064. This happens because the ComputeXidHorizons loop picks
> the first PID it encounters with the cutoff XID, even if other backends
> have the same XID. There’s no reliable way within that loop to identify
> the actual blocking transaction.
>
> ```
> postgres=# SELECT pid, datname, usename, state, backend_xmin,
> backend_xid, substr(query, 1, 20) as query
> FROM pg_stat_activity;
>   pid   | datname  | usename  |        state        | backend_xmin |
> backend_xid |        query
>
> --------+----------+----------+---------------------+--------------+-------------+----------------------
>  267064 | postgres | postgres | active              |      4301694 |
>   4351291 | UPDATE pgbench_branc
>  267069 | postgres | postgres | active              |      4301694 |
>   4351332 | UPDATE pgbench_telle
>  267067 | postgres | postgres | active              |      4301694 |
>   4351299 | UPDATE pgbench_branc
>  267070 | postgres | postgres | active              |      4301694 |
>   4351279 | UPDATE pgbench_branc
>  267068 | postgres | postgres | active              |              |
>   4351325 | UPDATE pgbench_telle
>  267066 | postgres | postgres | active              |      4301694 |
>   4351327 | UPDATE pgbench_branc
>  267065 | postgres | postgres | active              |      4301694 |
>   4351292 | UPDATE pgbench_branc
>  267077 | postgres | postgres | active              |      4301694 |
>   4351303 | UPDATE pgbench_branc
>  266606 | postgres | postgres | active              |      4301694 |
>           | SELECT pid, datname,
>  267071 | postgres | postgres | active              |              |
>           | BEGIN;
>  267072 | postgres | postgres | active              |      4301694 |
>   4351300 | UPDATE pgbench_telle
>  267073 | postgres | postgres | active              |      4301694 |
>   4351258 | UPDATE pgbench_branc
>  267075 | postgres | postgres | idle                |              |
>           | END;
>  267074 | postgres | postgres | active              |      4301694 |
>   4351319 | UPDATE pgbench_branc
>  267076 | postgres | postgres | active              |              |
>   4351248 | END;
>  267084 | postgres | postgres | active              |      4301694 |
>   4351330 | UPDATE pgbench_telle
>  267078 | postgres | postgres | active              |      4301694 |
>   4351260 | UPDATE pgbench_branc
>  267082 | postgres | postgres | active              |              |
>   4351309 | END;
>  267081 | postgres | postgres | active              |              |
>   4351270 | UPDATE pgbench_branc
>  267083 | postgres | postgres | active              |              |
>   4351313 | END;
>  267080 | postgres | postgres | active              |      4301694 |
>   4351311 | UPDATE pgbench_branc
>  267079 | postgres | postgres | active              |      4301694 |
>   4351318 | UPDATE pgbench_branc
>  267086 | postgres | postgres | active              |      4301694 |
>   4351335 | UPDATE pgbench_branc
>  267085 | postgres | postgres | active              |              |
>           | BEGIN;
>  267090 | postgres | postgres | idle in transaction |              |
>   4301694 | SELECT txid_current( ************
> ```
>
> While scanning the proc array adds some overhead, it could be limited
> to cases where multiple VACUUMs are stuck on the same cutoff XID, but
> we will need to track the last cutoff-xmin to make that possible.
>
> Alternatively, this information might be better exposed in a new system
> view, showing the "PID", "XID," and "reason" a transaction is blocking
> VACUUM.
> This approach is more proactive as a DBA can continuously monitor for
> blocking reasons and take action before it becomes an issue.
>
> --
> Sami Imseih

---

From: Shinya Kato <[email-masked]>
Date: Thu, 5 Feb 2026 13:39:27 +0900

HI,

Sorry for the late reply. I've updated the patch to follow Sami's
recommended approach.

Overview:
- Instead of modifying ComputeXidHorizons(), this patch introduces two
new functions: GetXidHorizonBlockers() and GetXidHorizonBlocker().
- GetXidHorizonBlockers() retrieves all potential blockers. This API
design leaves open the possibility of exposing this information
through a dynamic statistics view in the future [0].
- GetXidHorizonBlocker() selects the highest-priority blocker from the
candidates returned by GetXidHorizonBlockers().
- Priority is defined in the XidHorizonBlockerType enum. By
distinguishing whether the blocker matches the horizon via xid or
xmin, the appropriate blocker is selected.

Changes addressed from review comments:
- Fixed unstable regression test (Fujii-san's and Andres's comments).
- When multiple blockers share the same horizon, the blocker with the
highest priority is now selected for output (Fujii-san's comment).
- Removed unnecessary code (Fujii-san's comment).
- Distinguished between active transactions and idle-in-transaction
sessions, and added tests for both (Sami's and Wenhui's comments).
- Added a trailing comma to the last value of the enum (Sami's comment).
- Added a new function GetXidHorizonBlockers(), modeled after
BackendXidGetPid(), instead of modifying ComputeXidHorizons() (Sami's
comment).
- Added a test for a SERIALIZABLE transaction (Sami's comment).

Not addressed:
- Did not switch from int to pid_t for the pid type, because int is
used consistently throughout the PostgreSQL codebase for this purpose
(Sami's comment).

Other changes:
- Changed the TAP test to use VACUUM (VERBOSE) instead of autovacuum.

[0] https://www.postgresql.org/message-id/CAAaqYe9Dy9sicKg3xzCQUMK3VLdEP39g9nMGZheqtFYfNiO5Bg%40mail.gmail.com

--
Best regards,
Shinya Kato
NTT OSS Center

---

From: wenhui qiu <[email-masked]>
Date: Mon, 16 Mar 2026 15:59:36 +0800

HI Shinya
> typedef enum XidHorizonBlockerType
> {
>     XHB_NONE = 0,
>     XHB_ACTIVE_TRANSACTION,
>     XHB_IDLE_IN_TRANSACTION,
>     XHB_PREPARED_TRANSACTION,
>     XHB_XMIN_ACTIVE_TRANSACTION,
>     XHB_XMIN_IDLE_IN_TRANSACTION,
>     XHB_HOT_STANDBY_FEEDBACK,
>     XHB_REPLICATION_SLOT,
> }
Thank you for your working on this ,I have another small suggestion
The priority ordering encoded in XidHorizonBlockerType determines which
blocker gets reported when multiple candidates exist. In particular:

ACTIVE_TRANSACTION
IDLE_IN_TRANSACTION
PREPARED_TRANSACTION

Prepared transactions are currently ranked after idle-in-transaction
sessions. Operationally, prepared transactions are often harder for DBAs to
resolve than idle sessions, so it might be worth clarifying the rationale
behind this ordering or reconsidering whether prepared transactions should
have higher priority.
> typedef enum XidHorizonBlockerType
> {
>     XHB_NONE = 0,
>     XHB_ACTIVE_TRANSACTION,
>     XHB_PREPARED_TRANSACTION,
>     XHB_IDLE_IN_TRANSACTION,
>     XHB_XMIN_ACTIVE_TRANSACTION,
>     XHB_XMIN_IDLE_IN_TRANSACTION,
>     XHB_HOT_STANDBY_FEEDBACK,
>     XHB_REPLICATION_SLOT,
> }
Another one:
Currently GetXidHorizonBlocker() selects only one blocker (based on the
enum priority) even though multiple independent sources could hold back the
xmin horizon simultaneously. For example, it is possible to have both a
prepared transaction and a replication slot preventing the horizon from
advancing.
Have you considered reporting all detected blockers instead of just the
highest-priority one? Returning only a single entry might hide other
relevant blockers from the user.

Thanks

On Thu, Feb 5, 2026 at 12:40 PM Shinya Kato <[email-masked]> wrote:

> [0]
> https://www.postgresql.org/message-id/CAAaqYe9Dy9sicKg3xzCQUMK3VLdEP39g9nMGZheqtFYfNiO5Bg%40mail.gmail.com
>
>
>
> --
> Best regards,
> Shinya Kato
> NTT OSS Center
>

---

From: Japin Li <[email-masked]>
Date: Mon, 16 Mar 2026 19:19:34 +0800

On Mon, 16 Mar 2026 at 15:59, wenhui qiu <[email-masked]> wrote:
> The priority ordering encoded in XidHorizonBlockerType determines which blocker gets reported when multiple candidates
> exist. In particular:
>
> ACTIVE_TRANSACTION
> IDLE_IN_TRANSACTION
> PREPARED_TRANSACTION
>
> Prepared transactions are currently ranked after idle-in-transaction sessions. Operationally, prepared transactions are
> often harder for DBAs to resolve than idle sessions, so it might be worth clarifying the rationale behind this ordering
> or reconsidering whether prepared transactions should have higher priority.

Agreed.  Explaining the reason for this priority is very helpful.
> Currently GetXidHorizonBlocker() selects only one blocker (based on the enum priority) even though multiple independent
> sources could hold back the xmin horizon simultaneously. For example, it is possible to have both a prepared transaction
> and a replication slot preventing the horizon from advancing.
> Have you considered reporting all detected blockers instead of just the highest-priority one? Returning only a single
> entry might hide other relevant blockers from the user.
>

I'm also curious — why don't we list all the blockers? Did I miss anything?

-- 
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.

---

From: Shinya Kato <[email-masked]>
Date: Fri, 22 May 2026 22:05:31 +0900

Thank you for your feedback!

> Agreed.  Explaining the reason for this priority is very helpful.

We always pick a blocker from the xid-match group first (it is the
transaction actually holding the horizon, while the xmin-match entries
are just held back by it). Within the xid-match group, the
active/idle/prepared order never matters: a given xid is owned by only
one backend, so when the horizon equals a proc's xid there is only one
matching entry, and it is exactly one of active, idle, or prepared. So
moving prepared ahead of idle would not change which blocker we
report.

> I'm also curious — why don't we list all the blockers? Did I miss anything?

I did think about this, but I would like to keep reporting one blocker
in the VACUUM log, for two reasons.

First, the log can get very large. In Sami's earlier example [0], a
pgbench run had many backends all sharing the same xmin while only one
idle-in-transaction backend actually owned the cutoff xid. Reporting
every blocker would print 20+ lines, almost all of them just victims
of the same root cause, which makes the log harder to read, not
easier.

Second, the one blocker we report is the root cause (the xid owner).
Once the DBA resolves it, the next VACUUM will show the next blocker
if one remains.

This is also why the code is split into GetXidHorizonBlockers(), which
already collects every candidate, and GetXidHorizonBlocker(), which
picks the highest-priority one for the log. The "show everything" case
is what I would like to expose later through a dynamic statistics
view, where a full list makes more sense than in a VACUUM log line.

I've rebased the patch.

P.S. It might be better to use bottom posting instead of top posting [1].

[0] https://www.postgresql.org/message-id/CAA5RZ0sjMgMo4Xg-niyyF-CpkQ_CK6uOfNKYT%3D9RmiBkAxQkbQ%40mail.gmail.com
[1] https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics

-- 
Best regards,
Shinya Kato
NTT OSS Center

---

From: Japin Li <[email-masked]>
Date: Sat, 23 May 2026 09:40:39 +0800

> view, where a full list makes more sense than in a VACUUM log line.

Thanks for updating the patch. LGTM. Just one nitpick.

+	int			pid;			/* backend pid (0 for slots) */

Per the code, the prepared transaction is also associated with a PID of zero.

---

From: Shinya Kato <[email-masked]>
Date: Sat, 23 May 2026 14:22:48 +0900

> Per the code, the prepared transaction is also associated with a PID of zero.

Thanks for the review! You're right, prepared transactions also have
pid 0. Fixed the comment to mention that.

Additionally, I forgot to update meson.build, which caused the tests
to fail. I have fixed that in the attached patch.

-- 
Best regards,
Shinya Kato
NTT OSS Center

---

From: Shinya Kato <[email-masked]>
Date: Sat, 23 May 2026 15:00:33 +0900

> to fail. I have fixed that in the attached patch.

Oops, I made a slight mistake. Fixed.

-- 
Best regards,
Shinya Kato
NTT OSS Center

---

From: wenhui qiu <[email-masked]>
Date: Sat, 23 May 2026 18:17:26 +0800

Hi
Thank you updating the path,LGTM

Thanks

---

From: Laurenz Albe <[email-masked]>
Date: Sat, 23 May 2026 06:35:37 -0700

> Oops, I made a slight mistake. Fixed.

I think that is a great feature that will improve users' lives!

The patch looks fine to me too.
I guess the overhead of one more scan of the process array for every autovacuum
run if "log_autovacuum_min_duration" is non-zero (which is the default)
is acceptable.

Yours,
Laurenz Albe

---

From: Shinya Kato <[email-masked]>
Date: Thu, 28 May 2026 14:34:54 +0900

> The patch looks fine to me too.

Thanks all for the reviews and LGTMs!

Before moving this forward, I noticed there is one thing in the patch
that I should fix first.

When hot_standby_feedback=on, the location where the standby's xmin is
recorded on the primary depends on whether physical replication slot
is used:

- Without a replication slot: the xmin is held on the walsender's PGPROC
- With a replication slot: the xmin is held on the replication slot itself

I summarized this behavior in my blog:
https://dev.to/shinyakato_/4-causes-of-table-bloat-in-postgresql-and-how-to-address-them-3ec9

The current patch reports the latter case as XHB_REPLICATION_SLOT with
the message "logical replication slot", which is misleading because it
is actually a physical slot used by a standby with
hot_standby_feedback=on. I will fix this and post an updated patch
later.

--
Best regards,
Shinya Kato
NTT OSS Center

---

From: Scott Ray <[email-masked]>
Date: Sat, 30 May 2026 02:31:27 +0000

> run if "log_autovacuum_min_duration" is non-zero (which is the default)

Could vacuum compute the blocker during ComputeXidHorizons and consume it at log time?
Avoids the extra scan, and binds the blocker to the horizon vacuum used for pruning.

Scott Ray

---

From: Shinya Kato <[email-masked]>
Date: Sun, 31 May 2026 22:06:36 +0900

> Avoids the extra scan, and binds the blocker to the horizon vacuum used for pruning.

As Sami suggested in [0], I think this design is a cleaner approach
because it separates the responsibilities of ComputeXidHorizons and
the calculation of vacuum blockers.

[0] https://www.postgresql.org/message-id/CAA5RZ0s%2BUUXekbeGcC-H71kW%3DMfeaUCOV%3DyEWX94NXViO2-%3DpA%40mail.gmail.com

-- 
Best regards,
Shinya Kato
NTT OSS Center

---

From: Kyotaro Horiguchi <[email-masked]>
Date: Tue, 02 Jun 2026 16:57:55 +0900 (JST)

Hello.

This may be just my personal preference, but I'd rather see
information about xid horizon retention exposed through a SQL-visible
interface than added to the VACUUM log.

VACUUM VERBOSE already reports the removable cutoff and how old it was
when the operation ended.  That seems sufficient as a signal that the
xid horizon is being held back.

What I would want to investigate after seeing such a signal is not
necessarily what happened to be blocking that particular VACUUM
operation at some earlier point, but what is holding the xid horizon
back now.  For that purpose, a view exposing the current xid/xmin
holders seems more useful and less misleading than adding a
best-effort blocker guess to the VACUUM log.

Also, VACUUM output has traditionally reported facts observed during
the operation.  Since the blocker information is reconstructed
afterwards and is explicitly best-effort, it feels somewhat different
in nature from the rest of the information reported there.

Regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

---

From: Sami Imseih <[email-masked]>
Date: Tue, 2 Jun 2026 18:34:32 -0500

Hi,

> best-effort blocker guess to the VACUUM log.

+1, I also had a similar comment at the bottom of [1], where this information
is better to be exposed in SQL for proactive monitoring.

[1] https://www.postgresql.org/message-id/CAA5RZ0sjMgMo4Xg-niyyF-CpkQ_CK6uOfNKYT%3D9RmiBkAxQkbQ%40mail.gmail.com

---

From: Scott Ray <[email-masked]>
Date: Wed, 03 Jun 2026 01:05:13 +0000

> best-effort blocker guess to the VACUUM log.

I've been working on a view like this.  It shows the horizon
contribution for each backend, prepared xact, replication slot, and
HSF walsender, broken down by class.  It also shows - for each
contributor - how the horizon would shift if that holder were
removed.

Shinya said [1] that we could have a view in the future.  We could
have both the logging and the view call a single function that reads
the procArray and other sources to gather the horizon information.  I
think the logging and the view would complement each other.

Should I start another thread?

[1] https://www.postgresql.org/message-id/[email-masked]

--
Scott Ray

---

From: Shinya Kato <[email-masked]>
Date: Wed, 3 Jun 2026 10:11:01 +0900

> is better to be exposed in SQL for proactive monitoring.

I agree that exposing xid horizon retention information via a
SQL-visible interface is valuable. However, I believe reporting it
in the VACUUM log is also important: a view only shows the current
state, so once a blocker has gone away there is no way to determine,
after the fact, what was holding the horizon back at the time a
particular VACUUM ran. Logs are the only durable record we have for
that kind of post-hoc analysis.

That said, I share the concern about emitting best-effort
information in the VACUUM log, which otherwise reports facts
observed during the operation. Would it be acceptable if we
reported the exact blocker -- captured at the moment OldestXmin is
computed -- rather than a best-effort guess reconstructed
afterwards?

-- 
Best regards,
Shinya Kato
NTT OSS Center

---

From: Shinya Kato <[email-masked]>
Date: Wed, 3 Jun 2026 10:24:29 +0900

> Should I start another thread?

My mild preference would be to keep the discussion on this thread,
since the shared function design is central to both the log and the
view and may be easier to keep aligned in one place. That said, I'm
not strongly attached to that, so please pick whichever feels more
convenient.

-- 
Best regards,
Shinya Kato
NTT OSS Center

---

From: wenhui qiu <[email-masked]>
Date: Wed, 3 Jun 2026 10:32:07 +0800

HI Shinya
> that kind of post-hoc analysis.
Agree +1,There's no denying that checking SQL is easier than checking logs.
Logs are also important though. In fact, I think we should apply this patch
first and implement the SQL later.

Thanks

---

From: Japin Li <[email-masked]>
Date: Wed, 03 Jun 2026 13:58:13 +0800

On Wed, 03 Jun 2026 at 10:32, wenhui qiu <[email-masked]> wrote:
> Agree +1,There's no denying that checking SQL is easier than checking logs. Logs are also important though. In fact, I
> think we should apply this patch first and implement the SQL later.
>

+1 for this idea.  I don't think they conflict with each other — they just
provide the same content from different perspectives.

---

From: Jacob Champion <[email-masked]>
Date: Wed, 3 Jun 2026 10:36:14 -0700

(A colleague pointed me at this thread just now, because I somehow
missed it, but I've also been playing around with a solution for this
recently. Your patches are much farther along than mine -- hooray!)

> that kind of post-hoc analysis.

+1. I'd *really* like to have this information logged at the point
that VACUUM is blocked; it'll help during support escalations (or
prevent them!) in a way that a view cannot.

That's not to say we couldn't also have a view, of course.

> computed -- rather than a best-effort guess reconstructed

I don't want to weigh in very strongly here; you've all been thinking
about it for longer than I have. So, taking off the committer hat and
putting my user hat on:

I ran into the same decision between 1) tracking the origin during
horizon calculation and 2) attempting to reconstruct it after the
fact. I decided, for my own patch, that I'd rather track them during
horizon calculation. The cases where I really need these logs are
high-stress, chaotic situations where things are falling apart, and if
the database's answer to "what's causing the problem?" is ever "I
don't know, you better run VACUUM again", I think that's likely to
spike my blood pressure.

As discussed above, though, this approach leads to a collision issue.
So I put the patch down before PGConf.dev, trying to figure out how
best to solve that.

(That said, there's nothing wrong with getting a good solution in and
working on a better one, as long as the first patch doesn't make the
second one harder. Thank you for working on this!)

--Jacob

---

From: Scott Ray <[email-masked]>
Date: Sat, 06 Jun 2026 18:40:31 +0000

> not strongly attached to that, so please pick whichever feels more

I attached a patch that uses a helper function that could also be used
for a view, but I don't think it's necessary immediately, because
refactoring later wouldn't be difficult.

--
Scott Ray

---

From: Sami Imseih <[email-masked]>
Date: Mon, 8 Jun 2026 11:35:53 -0500

> Oops, I made a slight mistake. Fixed.

Thanks for the updated patch!

I agree there is value in logging this information during
vacuum/autovacuum, as unlike a view, it provides the ability to
investigate historically what was blocking vacuum. Having both the
vacuum logging and the view seems like a good combination to me.

In terms of v5, I have some comments:

1/ Reporting the GID of a prepared transaction.

This seems unnecessary. The logged xid is sufficient since a DBA
can look up the GID via pg_prepared_xacts using the "transaction"
column. I think we can remove GetPreparedTransactionGid() and
just report the xid.

2/ Reporting the application_name of the standby.

This could be valuable assuming the user sets application_name in the
primary_conninfo string to make the standby distinguishable. The
documentation [1] says this "should be set" for physical standbys,
and in practice most deployments do so since
synchronous_standby_names matches on it.

However, instead of GetStandbyAppname(), can we use
pgstat_get_beentry_by_proc_number() to get the application name and
set it in dst->name while holding the shared ProcArrayLock? This is
safe if we call pgstat_fetch_stat_numbackends() beforehand to prime
the local backend status cache. The subsequent lookup is just a
bsearch on local memory, so it adds negligible time under the lock.

3/ Over-allocation in GetXidHorizonBlockers().

+    /*
+     * Allocate enough space for every PGPROC plus all replication slots. This
+     * is a generous upper bound (typically only 0-2 entries are returned),
+     * but keeps the logic simple for a diagnostic function that runs
+     * infrequently.
+     */
+    max_blockers = arrayP->maxProcs + max_replication_slots;
+    result = palloc0_array(XidHorizonBlocker, max_blockers);

This looks wasteful, especially with large max_connections. In
practice only 1-3 entries are returned. Can we start with a small
allocation (say 8) and repalloc when needed?

4/ Simplify the loop body by removing candidate_* temporaries.

The values we set in dst don't need to be tracked in separate
variables. If dst gets populated for the proc, we can just do:

```
if (dst)
{
    dst->pid = proc->pid;
    dst->xid = horizon;
    dst->proc_number = pgprocno;
}
```

at the end of each iteration.

5/ GetXidHorizonBlocker() priority scan.

+/*
+ * Get the highest-priority blocker holding back the xid horizon.
+ *
+ * Returns true and stores the blocker in *blocker if any are found.
+ */

The priority scan makes sense and addresses the concern I raised
earlier [2] about reporting the wrong blocker due to ProcArray
ordering. Without it we report whichever blocker happens to come
first in ProcArray order. If an xmin-holder sits at a lower index
than the xid-holder, we would report the less actionable one. The
xid-holder is more useful to surface because killing that single
transaction advances the horizon, whereas xmin-holders only matter
once all of them release.

One small optimization: exit the loop early once we find an xid-match
type, since nothing can beat it:

    for (int i = 0; i < nblockers; i++)
    {
        if (best == NULL || blockers[i].type < best->type)
        {
            best = &blockers[i];

            /* xid-match types can't be beaten, stop early */
            if (best->type <= XHB_PREPARED_TRANSACTION)
                break;
        }
    }

Finally, the infrastructure added here (GetXidHorizonBlockers,
XidHorizonBlocker struct, the enum-based priority) will also underpin
the future SQL-callable view for real-time blocker reporting, so I
think what is being done here is good for that case also. I would
also think that GetXidHorizonBlockers() and friends should be a
separate patch, and the vacuum logging and future SQL view work can
build off of it.

[1] https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-PRIMARY-CONNINFO
[2] https://www.postgresql.org/message-id/CAA5RZ0sjMgMo4Xg-niyyF-CpkQ_CK6uOfNKYT%3D9RmiBkAxQkbQ%40mail.gmail.com