Feedback-ID: i76984098:Fastmail
Message-ID: <4e81951f-4666-400d-b836-a020e55e168a@aklaver.com>
Date: Mon, 22 Apr 2024 13:21:33 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: CLUSTER vs. VACUUM FULL
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: 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>
 <632e3176-13e4-4863-b8b9-bc1aba778268@aklaver.com>
 <CANzqJaDtterO9DEswkJOpgpizjLchYK7jOiJ0Pct0TEuc4Wv3g@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CANzqJaDtterO9DEswkJOpgpizjLchYK7jOiJ0Pct0TEuc4Wv3g@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/4e81951f-4666-400d-b836-a020e55e168a%40aklaver.com>
Precedence: bulk

On 4/22/24 12:51, Ron Johnson wrote:
> On Mon, Apr 22, 2024 at 3:14 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
> 
> 

> 
>     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? 
> 
> 
> Not much.  Now they're just "slightly more ordered" instead of "slightly 
> less ordered" for little if any extra effort.
> 
>     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?
> 
> 
> 1. Records are appended to tables in INSERT order, and INSERT order is 
> highly correlated to synthetic PK, by the nature of sequences.

Not something I would count on, see:

https://www.postgresql.org/docs/current/sql-createsequence.html

Notes

for how that may not always be the case.

Also any UPDATE or DELETE is going to change that. There is no guarantee 
of order for the data in the table. If there where you would not need to 
run CLUSTER.

> 2. My original email showed that CLUSTER took just as long as VACUUM 
> FULL.  That means not many records had to be sorted, because... the 
> on-disk order was strongly correlated to PK and CREATED_ON. >
> Will that happen *every time* in *every circumstance* in *every 
> database*?  No, and I never said it would.  But it does in *my *database 
> in *this *application.
> 

Which gets us back to your comment upstream:

"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)."

This is a case specific to you and this particular circumstance, not a 
general rule for VACUUM FULL. If for no other reason then it might make 
more sense for the application that the CLUSTER be done on some other 
index then the PK.


-- 
Adrian Klaver
adrian.klaver@aklaver.com