Re: CLUSTER vs. VACUUM FULL

On Mon, Apr 22, 2024 at 12:29 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

>
>
> On Mon, Apr 22, 2024, 08:37 Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:
>
>> On Mon, Apr 22, 2024 at 10:25 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>>> Marcos Pegoraro <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).
>>
>> 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.
>

Sure it does.

For example, I just deleted the oldest half of the records in 30 tables.
Tables who's CREATED_ON timestamp value strongly correlates to the
synthetic PK sequence values.

Thus, the remaining records were still mostly in PK order. CLUSTERs on the
PK values would have taken just about as much time as the VACUUM FULL
statements which I *did* run.