MIME-Version: 1.0
References: <CANzqJaD8o0UuK42F==BXZA=L380n=aXt+VJ93PN3waFbNhZHBg@mail.gmail.com>
 <2870091.1713739514@sss.pgh.pa.us> <CANzqJaBvcGKdhqaZz+b0jNc8WLK74UCDW+zk=Ja78duOjWUBRA@mail.gmail.com>
 <CAApHDvraS7EkYZsgZnAWc-L4_jbie5UkPAtedKSqN6s1j+PJiQ@mail.gmail.com>
 <CAB-JLwYPrJpYGqsQFDpiMf6UFKeT1zwagabdb-2V3x0G8DqNPQ@mail.gmail.com>
 <3043219.1713795953@sss.pgh.pa.us> <CANzqJaDocQZNhD8CpGS7ae82tVeuL5-reoKy0_Y6=RKuTh6qwQ@mail.gmail.com>
In-Reply-To: <CANzqJaDocQZNhD8CpGS7ae82tVeuL5-reoKy0_Y6=RKuTh6qwQ@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Mon, 22 Apr 2024 09:29:21 -0700
Message-ID: <CAKFQuwYFHCf+cd=wxJmzRqezQKy8rAKv2_QyvipzE1qboBXXvw@mail.gmail.com>
Subject: Re: CLUSTER vs. VACUUM FULL
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000026d6920616b1f1a0"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwYFHCf%2Bcd%3DwxJmzRqezQKy8rAKv2_QyvipzE1qboBXXvw%40mail.gmail.com>
Precedence: bulk

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

On Mon, Apr 22, 2024, 08:37 Ron Johnson <ronljohnsonjr@gmail.com> wrote:

> On Mon, Apr 22, 2024 at 10:25=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wro=
te:
>
>> Marcos Pegoraro <marcos@f10.com.br> writes:
>> > But wouldn't it be good that VACUUM FULL uses that index defined by
>> > Cluster, if it exists ?
>>
>> No ... what would be the difference then?
>>
>
> What the VACUUM docs "should" do, it seems, is suggest CLUSTER on the PK,
> if the PK is a sequence (whether that be an actual sequence, or a timesta=
mp
> or something else that grows monotonically).
>
> That's because the data is already roughly in PK order.
>

If things are bad enough to require a vacuum full that doesn't seem like a
good assumption.  Any insert-only table or one with a reduced fill-factor
maybe.

David J

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

<div dir=3D"auto"><div><br><br><div class=3D"gmail_quote"><div dir=3D"ltr" =
class=3D"gmail_attr">On Mon, Apr 22, 2024, 08:37 Ron Johnson &lt;<a href=3D=
"mailto:ronljohnsonjr@gmail.com">ronljohnsonjr@gmail.com</a>&gt; wrote:<br>=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-l=
eft:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On M=
on, Apr 22, 2024 at 10:25=E2=80=AFAM Tom Lane &lt;<a href=3D"mailto:tgl@sss=
.pgh.pa.us" target=3D"_blank" rel=3D"noreferrer">tgl@sss.pgh.pa.us</a>&gt; =
wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex">Marcos Pegoraro &lt;<a href=3D"mailto:marcos@f10.com.br" =
target=3D"_blank" rel=3D"noreferrer">marcos@f10.com.br</a>&gt; writes:<br>
&gt; But wouldn&#39;t it be good that VACUUM FULL uses that index defined b=
y<br>
&gt; Cluster, if it exists ?<br>
<br>
No ... what would be the difference then?<br></blockquote><div>=C2=A0</div>=
<div>What the VACUUM docs &quot;should&quot; do, it seems, is suggest CLUST=
ER on the PK, if the PK is a sequence (whether that be an actual sequence, =
or a timestamp or something else that grows monotonically).</div><div><br><=
/div><div>That&#39;s because the data is already roughly in PK order.</div>=
<div></div></div></div></blockquote></div></div><div dir=3D"auto"><br></div=
><div dir=3D"auto">If things are bad enough to require a vacuum full that d=
oesn&#39;t seem like a good assumption.=C2=A0 Any insert-only table or one =
with a reduced fill-factor maybe.</div><div dir=3D"auto"><br></div><div dir=
=3D"auto">David J=C2=A0</div></div>

--00000000000026d6920616b1f1a0--