MIME-Version: 1.0
References: <CA+taBv8ce8KtXLcD3sHWRuvPfb4scHU6oVSz8NwcUX=F=ECZBA@mail.gmail.com>
 <19c4824f-24b8-4561-81c3-a2f4a7949803@aklaver.com>
In-Reply-To: <19c4824f-24b8-4561-81c3-a2f4a7949803@aklaver.com>
From: Matthew Tice <mjtice@gmail.com>
Date: Fri, 23 Aug 2024 10:33:38 -0600
Message-ID: <CA+taBv9QcWyPZDcXh03spn9MJkUrGD5UnH=4i3Zt_5mp8awnfA@mail.gmail.com>
Subject: Re: dead tuple difference between pgstattuple and pg_stat_user_tables
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000007f78d06205c5754"
Archived-At: <https://www.postgresql.org/message-id/CA%2BtaBv9QcWyPZDcXh03spn9MJkUrGD5UnH%3D4i3Zt_5mp8awnfA%40mail.gmail.com>
Precedence: bulk

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

On Fri, Aug 23, 2024 at 10:26=E2=80=AFAM Adrian Klaver <adrian.klaver@aklav=
er.com>
wrote:

> On 8/23/24 09:14, Matthew Tice wrote:
> > Hi All,
> >
> > I'm trying to understand why there's a difference between what
> > pgstattuple reports and pg_stat_user_tables reports (for the number of
> > dead tuples).
> >
> > As I understand, pgstattuple and pgstattuple_approx return the exact
> > number of dead tuples (as noted in the documentation) and based on an
>
> https://www.postgresql.org/docs/current/pgstattuple.html
>
> pgstattuple_approx(regclass) returns record
>
>      pgstattuple_approx is a faster alternative to pgstattuple that
> returns approximate results.
>
> Not sure how you get exact count out of that?
>

Maybe the wording is a little confusing to me. Under the section
for pgstattuple_approx:
"pgstattuple_approx tries to avoid the full-table scan and returns exact
dead tuple statistics along with an approximation of the number and size of
live tuples and free space."


>
> > This is a Google Alloy DB instance running:
>
> https://cloud.google.com/alloydb/docs/overview
>
> "AlloyDB for PostgreSQL is a fully managed, PostgreSQL-compatible
> database service that's designed for your most demanding workloads,
> including hybrid transactional and analytical processing. AlloyDB pairs
> a Google-built database engine with a cloud-based, multi-node
> architecture to deliver enterprise-grade performance, reliability, and
> availability."
>
> Where the important parts are 'PostgreSQL-compatible' and 'Google-built
> database engine'. You probably need to reach out to Google to see what
> that means for this situation.
>
> Got it, thanks Adrian.


>
> >  > select version();
> > -[ RECORD 1 ]-------------------------
> > version | PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by Debian
> > clang version 12.0.1, 64-bit
> > SELECT 1
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Fri, Aug 23, 2024 at 10:26=E2=80=
=AFAM Adrian Klaver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian=
.klaver@aklaver.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quot=
e" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-styl=
e:solid;border-left-color:rgb(204,204,204);padding-left:1ex">On 8/23/24 09:=
14, Matthew Tice wrote:<br>
&gt; Hi All,<br>
&gt; <br>
&gt; I&#39;m trying to understand why there&#39;s a difference between what=
 <br>
&gt; pgstattuple reports and pg_stat_user_tables reports (for the number of=
 <br>
&gt; dead tuples).<br>
&gt; <br>
&gt; As I understand, pgstattuple and pgstattuple_approx return the exact <=
br>
&gt; number of dead tuples (as noted in the documentation) and based on an =
<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/pgstattuple.html" rel=3D=
"noreferrer" target=3D"_blank">https://www.postgresql.org/docs/current/pgst=
attuple.html</a><br>
<br>
pgstattuple_approx(regclass) returns record<br>
<br>
=C2=A0 =C2=A0 =C2=A0pgstattuple_approx is a faster alternative to pgstattup=
le that <br>
returns approximate results.<br>
<br>
Not sure how you get exact count out of that?<br></blockquote><div><br></di=
v><div>Maybe the wording is a little confusing to me. Under the section for=
=C2=A0pgstattuple_approx:</div><div>&quot;pgstattuple_approx tries to avoid=
 the full-table scan and returns exact dead tuple statistics along with an =
approximation of the number and size of live tuples and free space.&quot;</=
div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px =
0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-col=
or:rgb(204,204,204);padding-left:1ex"><br>
&gt; This is a Google Alloy DB instance running:<br>
<br>
<a href=3D"https://cloud.google.com/alloydb/docs/overview" rel=3D"noreferre=
r" target=3D"_blank">https://cloud.google.com/alloydb/docs/overview</a><br>
<br>
&quot;AlloyDB for PostgreSQL is a fully managed, PostgreSQL-compatible <br>
database service that&#39;s designed for your most demanding workloads, <br=
>
including hybrid transactional and analytical processing. AlloyDB pairs <br=
>
a Google-built database engine with a cloud-based, multi-node <br>
architecture to deliver enterprise-grade performance, reliability, and <br>
availability.&quot;<br>
<br>
Where the important parts are &#39;PostgreSQL-compatible&#39; and &#39;Goog=
le-built <br>
database engine&#39;. You probably need to reach out to Google to see what =
<br>
that means for this situation.<br>
<br></blockquote><div>Got it, thanks Adrian.</div><div>=C2=A0</div><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-wid=
th:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-l=
eft:1ex">
<br>
&gt;=C2=A0 &gt; select version();<br>
&gt; -[ RECORD 1 ]-------------------------<br>
&gt; version | PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by Debian =
<br>
&gt; clang version 12.0.1, 64-bit<br>
&gt; SELECT 1<br>
<br>
-- <br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank">adrian.klave=
r@aklaver.com</a><br>
<br>
</blockquote></div></div>

--00000000000007f78d06205c5754--