Feedback-ID: i76984098:Fastmail
Message-ID: <632e3176-13e4-4863-b8b9-bc1aba778268@aklaver.com>
Date: Mon, 22 Apr 2024 12:14: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>
 <CAKFQuwYFHCf+cd=wxJmzRqezQKy8rAKv2_QyvipzE1qboBXXvw@mail.gmail.com>
 <CANzqJaConAabzE942KSQV2BtZdBaEjvY9Cz_A8xvwrgauUX=zA@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CANzqJaConAabzE942KSQV2BtZdBaEjvY9Cz_A8xvwrgauUX=zA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/632e3176-13e4-4863-b8b9-bc1aba778268%40aklaver.com>
Precedence: bulk


On 4/22/24 11:45 AM, Ron Johnson wrote:
> On Mon, Apr 22, 2024 at 12:29 PM David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
> 
> 
>     On Mon, Apr 22, 2024, 08:37 Ron Johnson <ronljohnsonjr@gmail.com
>     <mailto:ronljohnsonjr@gmail.com>> 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).
> 
>         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.

1) If they are already in enough of a PK order that the CLUSTER time vs 
VACUUM FULL time would not be material as there is not much or any 
sorting to do then what does the CLUSTER gain you? Unless this table 
then became read only whatever small gain arose from the CLUSTER would 
fade away as UPDATEs and DELETEs where done.

2) What evidence is there that the records where still in PK order just 
because you deleted based on CREATED_ON? I understand the correlation 
between CREATED_ON and the PK just not sure why that would necessarily 
translate to an on disk order by PK?

-- 
Adrian Klaver
adrian.klaver@aklaver.com