Feedback-ID: i76984098:Fastmail
Message-ID: <824158e3-58ac-42ec-b77e-60d19564284a@aklaver.com>
Date: Mon, 22 Apr 2024 08:51:38 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: CLUSTER vs. VACUUM FULL
To: Ron Johnson <ronljohnsonjr@gmail.com>,
 pgsql-general <pgsql-general@postgresql.org>
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>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CANzqJaDocQZNhD8CpGS7ae82tVeuL5-reoKy0_Y6=RKuTh6qwQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/824158e3-58ac-42ec-b77e-60d19564284a%40aklaver.com>
Precedence: bulk

On 4/22/24 08:37, Ron Johnson wrote:
> On Mon, Apr 22, 2024 at 10:25 AM Tom Lane <tgl@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> wrote:
> 
>     Marcos Pegoraro <marcos@f10.com.br <mailto: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 
> 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.klaver@aklaver.com