Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ryzi4-001RSP-40 for pgsql-general@arkaria.postgresql.org; Mon, 22 Apr 2024 19:52:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ryzi2-00EZGW-MA for pgsql-general@arkaria.postgresql.org; Mon, 22 Apr 2024 19:52:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ryzi2-00EZGO-9X for pgsql-general@lists.postgresql.org; Mon, 22 Apr 2024 19:52:14 +0000 Received: from mail-oa1-x2c.google.com ([2001:4860:4864:20::2c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1ryzhv-002OQ2-HD for pgsql-general@postgresql.org; Mon, 22 Apr 2024 19:52:13 +0000 Received: by mail-oa1-x2c.google.com with SMTP id 586e51a60fabf-22ec61aaf01so2104833fac.2 for ; Mon, 22 Apr 2024 12:52:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1713815526; x=1714420326; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=zG4KoQG2NocxORlnTux7GfSrEwO9JkTRHdYagFdEdTc=; b=aEki8pGzNJx+PkyKU1NgbaH1oRm6Tfj8LIs9imrDdpHj45pFRaM6f4+Q5tush4Auc4 8x0GU0sD5o6Wvpsc/9VfDl7MvzcB2L8qWVLHsRYn7IzU0FkKtf/GwvwUQYrgQwKYuIM5 80tuYmxsav4hE4UxUk65yESHNAav3B6TQ6iWZcuD8aOKkMBKXCvP8L4BveyRpMDYuHF0 1bbw9NYr7CPG7z3nKd1xmCFkRUZbDM7kKWTVsUmhStTqDNzExZohM88i0lQkp+xepv02 JMZ8herMTsTTsQruuyn4294XoUYFYT4EVsKcYeu1J9FbgVOZuwEfRQ28SlayrX73ecm5 MlHQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713815526; x=1714420326; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=zG4KoQG2NocxORlnTux7GfSrEwO9JkTRHdYagFdEdTc=; b=M/DUkuZ3IW5mFfgjm4LNakBeB0XKoGagBmTkZhysFiXXAB6My2/AV/Vga/cIxTevLU ENJl11RxAfmwmPkWP80z2M7N4rkOrSye/Dr3+f0CjJ7VTHyr/4Z729k1ndtRmRJpf2r4 ORWmoR75axmymgafDDsEvFeDkuSE0mNkza7Pgre2ytNkoLZBl8MYxflIeEd6lsMIa5I8 UbtBene9L02ZqdRHL0/jn2fhmZf7tZranYihOLDmN98EmNMb095y549xp5kRooRhOSqM tVCu3nixC52j5JMomxUNBrPG8GPHYuQ7oS9gnmcJKqp0l+HwWlimS+5FXyJSWt9UYdFf Ge3Q== X-Gm-Message-State: AOJu0Yx6R5AzW4PXye1O7eszpQFAhc1RFnd1Ee8ZVdKOphxVUctAhzfM vRmwUyu6nBfl8L9KiEBanHzzdrr8nva3jbVTCkJdHxIzbwGJNBEEMHJjPoH3Yg2IQPHG8+hn++w YxCllHHEFygavxabOdaT+oKLuZRXPFw== X-Google-Smtp-Source: AGHT+IFI3sglnJVct5JxY4XKhEU6m7gBZHLFzXa7HeU3foqvHn1D9Gg0r9cWlSu/v3I2U++QOKVPuX4Uf29OSueqC5I= X-Received: by 2002:a05:6870:c6a8:b0:239:61f7:51a7 with SMTP id cv40-20020a056870c6a800b0023961f751a7mr12168878oab.13.1713815525711; Mon, 22 Apr 2024 12:52:05 -0700 (PDT) MIME-Version: 1.0 References: <2870091.1713739514@sss.pgh.pa.us> <3043219.1713795953@sss.pgh.pa.us> <632e3176-13e4-4863-b8b9-bc1aba778268@aklaver.com> In-Reply-To: <632e3176-13e4-4863-b8b9-bc1aba778268@aklaver.com> From: Ron Johnson Date: Mon, 22 Apr 2024 15:51:54 -0400 Message-ID: Subject: Re: CLUSTER vs. VACUUM FULL To: Adrian Klaver Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000009ee5d60616b4c564" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009ee5d60616b4c564 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Apr 22, 2024 at 3:14=E2=80=AFPM Adrian Klaver wrote: > > > On 4/22/24 11:45 AM, Ron Johnson wrote: > > On Mon, Apr 22, 2024 at 12:29=E2=80=AFPM David G. Johnston > > > wrote: > > > > > > > > On Mon, Apr 22, 2024, 08:37 Ron Johnson > > wrote: > > > > On Mon, Apr 22, 2024 at 10:25=E2=80=AFAM Tom Lane > > wrote: > > > > Marcos Pegoraro > > 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? 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. 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. --0000000000009ee5d60616b4c564 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Apr 22, 2024 at 3:14=E2=80=AFPM A= drian Klaver <adrian.klaver= @aklaver.com> wrote:


On 4/22/24 11:45 AM, Ron Johnson wrote:
> On Mon, Apr 22, 2024 at 12:29=E2=80=AFPM David G. Johnston
> <da= vid.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:<= br> >
>
>
>=C2=A0 =C2=A0 =C2=A0On Mon, Apr 22, 2024, 08:37 Ron Johnson <ronljohnsonjr@gmail.c= om
>=C2=A0 =C2=A0 =C2=A0<mailto:ronljohnsonjr@gmail.com>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0On Mon, Apr 22, 2024 at 10:25=E2=80= =AFAM Tom Lane <t= gl@sss.pgh.pa.us
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<mailto:tgl@sss.pgh.pa.us>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Marcos Pegoraro <marcos@f10.com.br >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<mailto:marcos@f10.com.br>> wr= ites:
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > But wouldn't = it be good that VACUUM FULL uses that index
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0defined by
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > Cluster, if it ex= ists ?
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0No ... what would be th= e difference then?
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0What the VACUUM docs "should&quo= t; do, it seems, is suggest CLUSTER
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0on the PK, if the PK is a sequence (w= hether that be an actual
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0sequence, or a timestamp or something= else that grows
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0monotonically).
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0That's because the data is alread= y roughly in PK order.
>
>
>=C2=A0 =C2=A0 =C2=A0If things are bad enough to require a vacuum full t= hat doesn't seem
>=C2=A0 =C2=A0 =C2=A0like a good assumption.
>
>
> Sure it does.
>
> For example, I just deleted the oldest half of the records in 30
> tables.=C2=A0 Tables who's CREATED_ON timestamp value strongly cor= relates to
> the synthetic PK sequence values.
>
> Thus, the remaining records were still mostly in PK order.=C2=A0 CLUST= ERs on
> the PK values would have taken just about as much time as the VACUUM <= br> > FULL statements which I /did/=C2=A0run.

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.=C2=A0 Now they're just "slightly more ordered&q= uot; instead=C2=A0of "slightly less ordered" for little if any ex= tra effort.
=C2=A0
2) What evidence is there that the records where still in PK ord= er 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=C2=A0PK, by the nature of sequences.
2. = My original email showed that CLUSTER took just as long as VACUUM FULL.=C2= =A0 That means not many records had to be sorted, because... the on-disk or= der was strongly correlated to PK and CREATED_ON.

= Will that happen every time=C2=A0in every circumstance=C2=A0i= n every database?=C2=A0 No, and I never said it would.=C2=A0 But it = does in my database in this application.

=
--0000000000009ee5d60616b4c564--