MIME-Version: 1.0
References: <CAKkG4_mX+yO6oigW7YY6rXy0HTFj_bmrQ0_ktcxLKCxDbVaf4Q@mail.gmail.com>
In-Reply-To: <CAKkG4_mX+yO6oigW7YY6rXy0HTFj_bmrQ0_ktcxLKCxDbVaf4Q@mail.gmail.com>
From: Kashif Zeeshan <kashi.zeeshan@gmail.com>
Date: Tue, 11 Jun 2024 08:27:23 +0500
Message-ID: <CAAPsdhc+d6KMH_4PP4X1rn4Fqj_HsMWPOeMM9wh5xwrhdXzuFQ@mail.gmail.com>
Subject: Re: Vacuum backend with backend_xmin?
To: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b90813061a94d818"
Archived-At: <https://www.postgresql.org/message-id/CAAPsdhc%2Bd6KMH_4PP4X1rn4Fqj_HsMWPOeMM9wh5xwrhdXzuFQ%40mail.gmail.com>
Precedence: bulk

--000000000000b90813061a94d818
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi

On Mon, Jun 10, 2024 at 5:07=E2=80=AFPM Torsten F=C3=B6rtsch <tfoertsch123@=
gmail.com>
wrote:

> Hi,
>
> This is a VACUUM FREEZE process.
>
> -[ RECORD 1 ]------+--------------
> pid                | 129471
> datid              | 16401
> datname            | feed
> relid              | 1889166
> phase              | scanning heap
> heap_blks_total    | 1254901
> heap_blks_scanned  | 1017524
> heap_blks_vacuumed | 0
> index_vacuum_count | 0
> max_dead_tuples    | 11184809
> num_dead_tuples    | 0
> backend_xid        | <NULL>
> backend_xmin       | 3267908740
> age                | 8572
>
> The query is:
>
> select v.*, a.backend_xid, a.backend_xmin, age(a.backend_xmin)
> from pg_stat_progress_vacuum as v join pg_stat_activity as a on a.pid=3Dv=
.pid
>
> Now, my question is why does a vacuum backend have a backend_xmin? I am
> just curious.
>
it is the oldest transaction ID whose effects may not be
visible to the transaction running in the backend.

Since transaction IDs are stored in each row to determine its visibility,
the minimum of the "backend_xmin" of all backends determines the cut-off
point beyond which all backends will agree on the visibility of tuples.

This is for example relevant for VACUUM: it cannot remove any dead tuples
that contain a transaction ID that is not older than any backend's
Regards
Kashif Zeeshan
Bitnine Global

>
> Thanks,
> Torsten
>

--000000000000b90813061a94d818
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi</div><br><div class=3D"gmail_quote"><d=
iv dir=3D"ltr" class=3D"gmail_attr">On Mon, Jun 10, 2024 at 5:07=E2=80=AFPM=
 Torsten F=C3=B6rtsch &lt;<a href=3D"mailto:tfoertsch123@gmail.com">tfoerts=
ch123@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex"><div dir=3D"ltr">Hi,<div><br></div><div>This is a VACUUM FRE=
EZE process.</div><div><br></div><div><div><span style=3D"font-family:monos=
pace"><span style=3D"color:rgb(0,0,0)">-[ RECORD 1 ]------+--------------
</span><br>pid =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| 129471
<br>datid =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0| 16401
<br>datname =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0| feed
<br>relid =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0| 1889166
<br>phase =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0| scanning heap
<br>heap_blks_total =C2=A0=C2=A0=C2=A0| 1254901
<br>heap_blks_scanned =C2=A0| 1017524
<br>heap_blks_vacuumed | 0
<br>index_vacuum_count | 0
<br>max_dead_tuples =C2=A0=C2=A0=C2=A0| 11184809
<br>num_dead_tuples =C2=A0=C2=A0=C2=A0| 0
<br>backend_xid =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| &lt;NULL&gt;
<br>backend_xmin =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| 3267908740
<br>age =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0| 8572<br>
</span><div><br>The query is:</div><div><br></div><div><span style=3D"font-=
family:monospace"><span style=3D"color:rgb(0,0,0)">select v.*, a.backend_xi=
d, a.backend_xmin, age(a.backend_xmin)</span></span></div><div><span style=
=3D"font-family:monospace"><span style=3D"color:rgb(0,0,0)">from pg_stat_pr=
ogress_vacuum as v join pg_stat_activity as a on a.pid=3Dv.pid</span><br></=
span><div><br></div></div></div><div>Now, my question is why does a vacuum =
backend have a backend_xmin? I am just curious.</div></div></div></blockquo=
te><p>it is the oldest transaction ID whose effects may not be<br>visible t=
o the transaction running in the backend.</p>

<p>Since transaction IDs are stored in each row to determine its visibility=
,<br>the minimum of the &quot;backend_xmin&quot; of all backends determines=
 the cut-off<br>point beyond which all backends will agree on the visibilit=
y of tuples.</p>

<p>This is for example relevant for VACUUM: it cannot remove any dead tuple=
s<br>that contain a transaction ID that is not older than any backend&#39;s=
<br></p><div>Regards</div><div>Kashif Zeeshan</div><div>Bitnine Global</div=
><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border=
-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div><b=
r></div><div>Thanks,</div><div>Torsten</div></div>
</blockquote></div></div>

--000000000000b90813061a94d818--