MIME-Version: 1.0
References: <CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com>
 <CAHGQGwFVdccfhN7PFHU5gZcMNdQuyL8DhZSks2FuuTcZ5V_pGQ@mail.gmail.com>
 <CAGjGUAL-qZ6Na-cH4RY5raF4NwuF47ZVNyP0uMYQcks3w5b7Dw@mail.gmail.com>
 <CAA5RZ0s+UUXekbeGcC-H71kW=MfeaUCOV=yEWX94NXViO2-=pA@mail.gmail.com> <CAOzEurQVUsRa45aKdi1Qrc4wVsQKSmbLcN+2A9Eu1W1jm2Y5fw@mail.gmail.com>
In-Reply-To: <CAOzEurQVUsRa45aKdi1Qrc4wVsQKSmbLcN+2A9Eu1W1jm2Y5fw@mail.gmail.com>
From: Sami Imseih <samimseih@gmail.com>
Date: Mon, 5 Jan 2026 16:02:37 -0600
Message-ID: <CAA5RZ0sjMgMo4Xg-niyyF-CpkQ_CK6uOfNKYT=9RmiBkAxQkbQ@mail.gmail.com>
Subject: Re: Report oldest xmin source when autovacuum cannot remove tuples
To: Shinya Kato <shinya11.kato@gmail.com>
Cc: wenhui qiu <qiuwenhuifx@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAA5RZ0sjMgMo4Xg-niyyF-CpkQ_CK6uOfNKYT%3D9RmiBkAxQkbQ%40mail.gmail.com>
Precedence: bulk

> Thank you all for the review comments, and sorry for the late reply.
> I will address the review comments in order.
>
> On Sat, Nov 15, 2025 at 9:25=E2=80=AFAM Sami Imseih <samimseih@gmail.com>=
 wrote:
> > 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.
>
> Thanks for the revised proposal! Your approach is clear and makes the
> 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=3D# begin;
BEGIN
postgres=3D*# SELECT txid_current(), pg_backend_pid();
 txid_current | pg_backend_pid
--------------+----------------
      3665231 |         266601
(1 row)
```

3. run a vacuum
```
postgres=3D# 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=3D267064)
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=E2=80=99s no reliable way within that loop to iden=
tify
the actual blocking transaction.

```
postgres=3D# 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( ************
```

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

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 VACU=
UM.
This approach is more proactive as a DBA can continuously monitor for
blocking reasons and take action before it becomes an issue.

--
Sami Imseih
Amazon Web Services (AWS)