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 1tTj83-0097RR-TL for pgsql-admin@arkaria.postgresql.org; Fri, 03 Jan 2025 14:58:24 +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 1tTj82-002WwP-Ra for pgsql-admin@arkaria.postgresql.org; Fri, 03 Jan 2025 14:58:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tTj82-002WwD-8T for pgsql-admin@lists.postgresql.org; Fri, 03 Jan 2025 14:58:22 +0000 Received: from mail-pl1-x629.google.com ([2607:f8b0:4864:20::629]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tTj7z-001u7M-3C for pgsql-admin@postgresql.org; Fri, 03 Jan 2025 14:58:21 +0000 Received: by mail-pl1-x629.google.com with SMTP id d9443c01a7336-21634338cfdso247007025ad.2 for ; Fri, 03 Jan 2025 06:58:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1735916299; x=1736521099; 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=Yf1Ckb5avMFqT13oXSMmkm2wexi/35cDR33g4YCzypk=; b=mqlBRf6/GxijIBOVK+IsLZNV1z6/U6D400RE2JvHnUkPiUyI00NQzyO5z28b+sKJsH AIHRhuXtQy/I0pncajxXAef7n2rVy3mONv/Jcap2JsmnKOUYdrAEaSC791pDAlN50n5j QDNIA3k5R3qrTvxcpSfFAlNpp61YewQt+A69ufIdvP0ij1TtEO0coDlRXjvaqHuadjle nz+y6/hD1F/yjJ20q0WKV9HbArPZ8ZgeIkdEIYa/4rPCzwmLgEHsQgaCrg2FPL7HDwe4 TowBHjXnnL+zsnzg+q0fv5qOtNqLwlvfGwXDX2BxZR48BrJ+gYmuszaep3WNI/heEb3m +few== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1735916299; x=1736521099; 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=Yf1Ckb5avMFqT13oXSMmkm2wexi/35cDR33g4YCzypk=; b=Ts/Ivfo+zIlNUxLePmn7liwabybUfmxinsoYsSj3sgQe0mE1bPXG2YWn5NcN/DWs33 JMWdzZJJ9+Y93FIDKQ213SKWQ9tl/QZarn239Ahequ2V6xUoUE/us29lCyh8HEsH0Mgs YVqKGrau9OAeBktsjE3z0yqmM8dTdQP50n1eToaKmSWEYYYeBKoNHBplcMVsAA17lIcY 1Gc12ubr9pR1Amd2zwpNH/FxlzzEAX2cRSFFWYodVAYuruHzuHpA1bZssEnNoulOwUIl VYd6gXPEjPytYJRTrw3ntEfQgqXJikFWZG2eGLneVk1xCIocBiilx4LYISPhj7wF88VG J+Lw== X-Gm-Message-State: AOJu0YyOe32ganwAPlhz3sX+nGqVUuxd0PvgXBW0ldTrpKHxxAcZaiZC H8gPM8Ubj6701yRgiqMORB8VPm4tBP2ytuVpCcLQCDqYc2TGs7ELJb4CdScqYTZd81oOJIaY1Cs PedNElXRWjMY6XssALFvo75iR4qXEJw== X-Gm-Gg: ASbGncvrSiNDarh6ab2UX+/gS+HkwcoPuLdHPX7/Vw4Q25sv2QYFTEJTtlh3A3KdIgX H/HQpqLB4LZyDmarQXbpPdQwpNkUqVGs+e9Z8CSo= X-Google-Smtp-Source: AGHT+IFU2oNgmRv/9HjD1tHbo0dTt4x+sLUvKZrhYvA9RtVYXL/x/3bi1Yel2pUzmk14faLtIdqxIzDVodtPecm8qRc= X-Received: by 2002:a17:902:ec86:b0:216:7ee9:222c with SMTP id d9443c01a7336-219e6e8c980mr437137145ad.7.1735916298942; Fri, 03 Jan 2025 06:58:18 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: srijith s Date: Fri, 3 Jan 2025 09:57:41 -0500 Message-ID: Subject: Re: Do we perform maintenance on pg_catalog tables To: Ron Johnson Cc: pgsql-admin Content-Type: multipart/alternative; boundary="0000000000005bc51c062ace822a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005bc51c062ace822a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks Ron =E2=98=BA=EF=B8=8F On Fri, Jan 3, 2025 at 2:27=E2=80=AFAM Ron Johnson wrote: > > Percentages are not a reliable metric when dealing with small tables, and > pg_statistic is a pretty small table. > > Here, for example, are the stats for that table's only index. Oh, no! 57= % > fragmentation! But it's *less than 7MB*. I've got much bigger fish to > fry than worrying about tiny tables that are regularly autovacuumed. > > TAPb=3D# select * from pgstatindex('pg_statistic_relid_att_inh_index'); > -[ RECORD 1 ]------+-------- > version | 4 > tree_level | 2 > index_size | 6602752 > root_block_no | 412 > internal_pages | 4 > leaf_pages | 796 > empty_pages | 0 > deleted_pages | 5 > avg_leaf_density | 36.67 > leaf_fragmentation | 57.04 > > TAPb=3D# select relname, last_vacuum, last_autovacuum > from pg_stat_all_tables > where relname =3D 'pg_statistic'; > relname | last_vacuum | last_autovacuum > --------------+-------------+------------------------------- > pg_statistic | | 2025-01-02 23:40:17.647375-05 > (1 row) > > TAPb=3D# select * from pgstattuple('pg_statistic'); > -[ RECORD 1 ]------+--------- > table_len | 55762944 > tuple_count | 117619 > tuple_len | 40846337 > tuple_percent | 73.25 > dead_tuple_count | 1990 > dead_tuple_len | 446536 > dead_tuple_percent | 0.8 > free_space | 12954980 > free_percent | 23.23 > > > On Thu, Jan 2, 2025 at 8:58=E2=80=AFPM srijith s wr= ote: > >> Thanks Much for the explanation Ron >> >> I used a query (found on the internet) to find the bloat on all tables i= n >> the database, it showed 72% bloat_pct on *pg_catalog.pg_statistic* >> table, i ran a full vacuum on this table, instead of bringing >> the bloat_pct down it changed the bloat_pct 91%. Maybe I am not using >> the right query ? >> >> Query I used to find the bloat >> >> https://medium.com/@kemalozz/understanding-bloat-in-postgresql-and-how-t= o-manage-it-0ca1d6db0c78 >> >> On Thu, Jan 2, 2025 at 8:28=E2=80=AFPM Ron Johnson >> wrote: >> >>> On Thu, Jan 2, 2025 at 7:18=E2=80=AFPM Peter Gram >>> wrote: >>> >>>> Hi Ron >>>> >>>> How do I know if my AUTOVACUUM is Reasonably-tuned ? >>>> >>> >>> If your queries are as fast as they can be, given proper index >>> support. =F0=9F=98=89 >>> >>> Specifically, I'd look at pg_stat_all_tables >>> columns relname, n_dead_tup, n_mod_since_analyze, GREATEST(last_vacuum, >>> last_autovacuum) and GREATEST(last_analyze, last_autoanalyze). >>> >>> Run your system query a few times with EXPLAIN (BUFFERS), then "vacuumd= b >>> --all", and then run the queries again with EXPLAIN (BUFFERS). >>> >>> If they're the same speed, then your AUTOVACUUM settings are either Goo= d >>> Enough, or you got lucky and the tables were recently analyzed. If the= y're >>> faster, then you need to reduce the AUTOVACUUM config variables. >>> >>> These are *my* settings for *our* workload. YMMV. >>> autovacuum =3D on >>> autovacuum_max_workers =3D 4 >>> autovacuum_vacuum_scale_factor =3D 0.015 >>> autovacuum_vacuum_threshold =3D 50 >>> autovacuum_vacuum_cost_delay =3D 4ms >>> autovacuum_vacuum_cost_limit =3D 1000 >>> autovacuum_analyze_scale_factor =3D 0.015 >>> autovacuum_analyze_threshold =3D 50 >>> >>> >>>> Med venlig hilsen >>>> >>>> Peter Gram >>>> S=C3=A6byholmsvej 18 >>>> 2500 Valby >>>> >>>> Mobile: (+45) 5374 7107 >>>> Email: peter.m.gram@gmail.com >>>> >>>> >>>> >>>> On Fri, 3 Jan 2025 at 06.45, Ron Johnson >>>> wrote: >>>> >>>>> On Thu, Jan 2, 2025 at 2:50=E2=80=AFPM srijith s wrote: >>>>> >>>>>> >>>>>> Hello PostgreSQL Experts, >>>>>> >>>>>> Do we perform postgres maintenance (vacuum/analyze/reindex) on >>>>>> pg_catalog tables? >>>>>> >>>>> >>>>> Reasonably-tuned AUTOVACUUM config parameters should handle that for >>>>> you. >>>>> >>>> >>> -- >>> Death to , and butter sauce. >>> Don't boil me, I'm still alive. >>> lobster! >>> >> >> >> -- >> Thanks & Regards >> Srijith Sudhakaran >> > > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --=20 Thanks & Regards Srijith Sudhakaran --0000000000005bc51c062ace822a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks Ron=C2=A0=E2=98=BA=EF=B8=8F

On= Fri, Jan 3, 2025 at 2:27=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

Percentages are not a reliable metric when dealing with s= mall tables, and pg_statistic is a pretty small table.

=
Here, for=C2=A0example, are the stats for that=C2=A0table's only i= ndex.=C2=A0 Oh, no! 57% fragmentation!=C2=A0 But it's less than 7MB<= /b>.=C2=A0 I've got much bigger fish to fry than worrying about tiny ta= bles that are regularly autovacuumed.

TAPb=3D# select * from pgstatindex('pg_statistic_relid_a= tt_inh_index');
-[ RECORD 1 ]------+--------
version =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 4
tree_level =C2=A0 =C2=A0 =C2=A0 =C2= =A0 | 2
index_size =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 6602752
root_block_n= o =C2=A0 =C2=A0 =C2=A0| 412
internal_pages =C2=A0 =C2=A0 | 4
leaf_pag= es =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 796
empty_pages =C2=A0 =C2=A0 =C2=A0 = =C2=A0| 0
deleted_pages =C2=A0 =C2=A0 =C2=A0| 5
avg_leaf_density =C2= =A0 | 36.67
leaf_fragmentation | 57.04
=
TAPb=3D# select relname, last_vacuum, last_autovacuum
from pg_= stat_all_tables
where relname =3D 'pg_statistic';
=C2=A0 =C2= =A0relname =C2=A0 =C2=A0| last_vacuum | =C2=A0 =C2=A0 =C2=A0 =C2=A0last_aut= ovacuum =C2=A0 =C2=A0 =C2=A0 =C2=A0
--------------+-------------+-------= ------------------------
=C2=A0pg_statistic | =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | 2025-01-02 23:40:17.647375-05
(1 row)

TAPb=3D# select * from pgstattuple('pg_stat= istic');
-[ RECORD 1 ]------+---------
table_len =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| 55762944
tuple_count =C2=A0 =C2=A0 =C2=A0 =C2=A0| = 117619
tuple_len =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 40846337
tuple_p= ercent =C2=A0 =C2=A0 =C2=A0| 73.25
dead_tuple_count =C2=A0 | 1990
dea= d_tuple_len =C2=A0 =C2=A0 | 446536
dead_tuple_percent | 0.8
free_spac= e =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 12954980
free_percent =C2=A0 =C2=A0 =C2= =A0 | 23.23

On Thu, Jan 2, 2025 at 8:58=E2=80=AFPM srijith s &l= t;udbdoctor@gmail.= com> wrote:
Thanks Much for the ex= planation Ron

I used a query (found on the internet) to = find the bloat on all tables in the database, it showed 72% bloat_pct on pg_catalog.pg_statistic table,=C2=A0 i ran a full vacuum on this table= , instead of bringing the=C2=A0bloat_pct down it=C2=A0=C2=A0changed the=C2= =A0bloat_pct=C2=A0 91%.=C2=A0 Maybe I am not using the right=C2=A0query ?

Query I used to find the bloat

<= div class=3D"gmail_quote">
On Thu, Jan= 2, 2025 at 8:28=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Thu, Jan 2, 2025 at 7:18=E2=80=AFPM Peter Gram <peter.m.gram@gmail.com= > wrote:
Hi Ron

=
How do I know if my=C2=A0AUTOVACUUM is=C2=A0Reasonably-tuned ?=C2=A0
<= /blockquote>

If your queries are as fast as they can be,= given proper index support.=C2=A0=F0=9F=98=89
=C2=A0
S= pecifically, I'd look at=C2=A0pg_stat_all_tables columns=C2=A0relname,= =C2=A0n_dead_tup,=C2=A0n_mod_since_analyze, GREATEST(last_vacuum, last_auto= vacuum) and GREATEST(last_analyze, last_autoanalyze).

<= div>Run your system query a few times with EXPLAIN (BUFFERS), then "va= cuumdb --all", and then run the queries again with EXPLAIN (BUFFERS).<= /div>

If they're the same speed, then your AUTOVACUU= M settings=C2=A0are either Good Enough, or you got lucky and the tables wer= e recently analyzed.=C2=A0 If they're faster, then you need to reduce t= he AUTOVACUUM config variables.

These are my=C2=A0settings for our=C2=A0workload.=C2=A0 YMMV.
autovacuum =3D on
autovacuum_max_workers =3D 4
auto= vacuum_vacuum_scale_factor =3D 0.015
autovacuum_vacuum_threshold =3D 50<= br>autovacuum_vacuum_cost_delay =3D 4ms
autovacuum_vacuum_cost_limit =3D= 1000
autovacuum_analyze_scale_factor =3D 0.015
autovacuum_analyze_th= reshold =3D 50


=
Med venlig hilsen

Peter GramS=C3=A6byholmsvej 18=C2=A0
2500 Valby

Mobile: (+45) 5374 7107


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lob= ster!


--
Thanks & Regards
Srijith Sudhakaran


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!


--
Thanks & Regards
Srijith Sudhakaran
--0000000000005bc51c062ace822a--