Re: CLUSTER vs. VACUUM FULL

On 4/22/24 08:37, Ron Johnson wrote:
> On Mon, Apr 22, 2024 at 10:25 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> Marcos Pegoraro <marcos(at)f10(dot)com(dot)br <mailto:marcos(at)f10(dot)com(dot)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
> timestamp or something else that grows monotonically).

Why?

That would, per David Rowley's comments, impose a sort cost on top of
the cost of hitting every heap page and rewriting it. You end up with
sorted table granted, until such time as you start making changes to it.
If you are to the point of running VACUUM FULL that indicates to me the
table has seen a heavy load of changes that you want to clean out. Given
the temporary nature of the effects of a CLUSTER under a change load I
don't see why it would be the way to go to clean up a changing table.

>
> That's because the data is already roughly in PK order.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com