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 1ryyfX-001Mvr-DF for pgsql-general@arkaria.postgresql.org; Mon, 22 Apr 2024 18:45:35 +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 1ryyfV-00CXNQ-Jd for pgsql-general@arkaria.postgresql.org; Mon, 22 Apr 2024 18:45:33 +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 1ryyfV-00CXIp-4v for pgsql-general@lists.postgresql.org; Mon, 22 Apr 2024 18:45:33 +0000 Received: from mail-oa1-x2e.google.com ([2001:4860:4864:20::2e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1ryyfS-002Ny9-I6 for pgsql-general@postgresql.org; Mon, 22 Apr 2024 18:45:32 +0000 Received: by mail-oa1-x2e.google.com with SMTP id 586e51a60fabf-23a46c472feso544191fac.2 for ; Mon, 22 Apr 2024 11:45:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1713811528; x=1714416328; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=Kns40zxzI2fz07ZvgvxzejFKv0CdkNxxfIe8dKRSS2Y=; b=f7OJ42J15xZNPKORRN616kYqmMoSblvPxeGzJk/37ug3X5tnHiHGO7xYfWhhhALRWV LSizE2WwaXClVOyWdApjEx7heo2GT426l0jnDETKSRGPIUYlEMAu0bmE+bgm05y7DVx+ 3YpwhDmgBe4evWOhEyhGWcSRjr3yPmZfxACMKa7ys5jEk9Ap3lKvC0PufYaFiiwe8JIt dAEK8IH1p3h1s+ttwORBWK66WKuAW1iJh75PPf12vjYmPjvMjxFUlpLFs8w69Ho7EatG JZeDTDFRJfU4qLaj30qBLzTOh3Q+/NJzUigZmg5sV/lZG03Tj8LW+FFbNHNWt7ZKJqeY +meg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713811528; x=1714416328; h=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=Kns40zxzI2fz07ZvgvxzejFKv0CdkNxxfIe8dKRSS2Y=; b=dAdS/zB8tlwH3JbQlTZK/2lG6tAuL0kdtB7XTvMFX+VU+P6PkiZY8jGb/gU00ga+/K 42GMDTnKm6jU8IwF3ZrZod1sp+pGZCymfXVNo+AOv4hS2jwocWTASvpSLSx3j6FTgVen +oq2JxbiHxamjCA+O68AunI3XmhdVu3ch2IfdKG1huUFshMbDGEMe5EFmB2FrTIcEK/8 SA/ub6pVRNjABlR9X24S6LUnntahXfveuiqvEMhFhpcSDyodI5AfZSnz0MPPmnEWgWhS Lafyba04nTU2R7DmVjVKZABxTLuIigyjPQZt/4CuKyDmLQSX5D51dM/G2a9d7N/9dhT9 IuBA== X-Gm-Message-State: AOJu0YzTcmXGRxC18azG4vYYOEzUIcheDjSK0zayFrE3zM48KK51Img3 VBygsxd0EGauT4z+6S7ZPugZmL29odwWlsxU37ZCAXZmuDwfr09eByKTBm7K7N+EOJV2BPk2UUN KyLsx2Bs7Bcvg0dJArYV/H7DEMHf/Tg== X-Google-Smtp-Source: AGHT+IFV1bXwhGZfXutvkk0f4arqP2rQVTgazgOyMbV7CwEV32zBuVhXSgEaCLMf3hE8uJTusT2JnAyS/WITHRisByM= X-Received: by 2002:a05:6870:1482:b0:22e:bf56:7dc with SMTP id k2-20020a056870148200b0022ebf5607dcmr15468394oab.13.1713811527705; Mon, 22 Apr 2024 11:45:27 -0700 (PDT) MIME-Version: 1.0 References: <2870091.1713739514@sss.pgh.pa.us> <3043219.1713795953@sss.pgh.pa.us> In-Reply-To: From: Ron Johnson Date: Mon, 22 Apr 2024 14:45:16 -0400 Message-ID: Subject: Re: CLUSTER vs. VACUUM FULL To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000522bf10616b3d70e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000522bf10616b3d70e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Apr 22, 2024 at 12:29=E2=80=AFPM David G. Johnston < david.g.johnston@gmail.com> wrote: > > > On Mon, Apr 22, 2024, 08:37 Ron Johnson wrote: > >> On Mon, Apr 22, 2024 at 10:25=E2=80=AFAM Tom Lane wr= ote: >> >>> 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 timest= amp >> 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. --000000000000522bf10616b3d70e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Apr 22, 2024 at 12:29=E2=80=AFPM = David G. Johnston <david.g= .johnston@gmail.com> wrote:

On Mon, = Apr 22, 2024, 08:37 Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Mon, Apr 22, 2024 at 10:25=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us<= /a>> wrote:
Marcos Pegoraro <marcos@f10.com.br> writ= es:
> But wouldn't it be good that VACUUM FULL uses that index defined b= y
> Cluster, if it exists ?

No ... what would be the difference then?
=C2=A0
=
What the VACUUM docs "should" do, it seems, is suggest CLUST= ER on the PK, if the PK is a sequence (whether that be an actual sequence, = or a timestamp or something else that grows monotonically).

<= /div>
That's because the data is already roughly in PK order.
=

If things are bad enough to require a vacuum full that d= oesn't seem like 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 CREATE= D_ON timestamp value strongly correlates to the synthetic PK sequence value= s.=C2=A0

Thus, the remaining records were still mo= stly in PK order.=C2=A0 CLUSTERs on the PK values would have taken just abo= ut as much time as the VACUUM FULL statements which I did=C2=A0run.<= /div>
--000000000000522bf10616b3d70e--