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 1tTc5u-006wiJ-4o for pgsql-admin@arkaria.postgresql.org; Fri, 03 Jan 2025 07:27:42 +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 1tTc5t-007EK5-6r for pgsql-admin@arkaria.postgresql.org; Fri, 03 Jan 2025 07:27:40 +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 1tTc5s-007EJx-Px for pgsql-admin@lists.postgresql.org; Fri, 03 Jan 2025 07:27:40 +0000 Received: from mail-oa1-x30.google.com ([2001:4860:4864:20::30]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tTc5m-002xC8-6m for pgsql-admin@postgresql.org; Fri, 03 Jan 2025 07:27:39 +0000 Received: by mail-oa1-x30.google.com with SMTP id 586e51a60fabf-29fe83208a4so5782378fac.0 for ; Thu, 02 Jan 2025 23:27:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1735889252; x=1736494052; 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=3lc1WjYFfb0jyR95hl/0ZXIfIkH1yWEr+osxKdRAPug=; b=iLENnTReeVoa4MfkxVF9EhoYzgSTAWBnzvH4WM6vpxfExB15Ber8OB4ilERVHRUjnu ULJ0Cp0llwBx4NfK39urbGQjg5q3XnDosonQ5n60G/1UqkJ8FgbkpPKbPoIoNAme7A10 Jju5F+u/gZt7kDa9xWIv/Tz6KUJOhYmXIXdGYbuCdhEEwsVqpHbvwLaffk+rs115cJTF JRe4zjtsy748ZnGsj3N94qsq/FCFkPkyy16PxKLU8VEWo7xEBghypaYsIIRIay+XQHmo gwrz+TYEh/H5k/JeOOKtFDqCPolYpXwi+dJyoQ7SV3FT+eImu7h6caeQomjM4GBS5tpS F05A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1735889252; x=1736494052; 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=3lc1WjYFfb0jyR95hl/0ZXIfIkH1yWEr+osxKdRAPug=; b=WoKbi82F/taW4QSgS0Q9U5s1hgkxxgv2E15G01Ap5hb8+XcVcjszzCaSlmjbPi0RLH BzV7q95INEybVacIQABi17xeW735TilMjSDZKzkqZSLc9hIDebpHTKMqMqLkTUY7sXa6 RbXHhHxckhS9W02kcFwEpKabrjmSxIJ0gS9gCC1W8gLGDXTkcF6IHrfowjpHwgtlXP8W ZfUA4hoZmAqz9+YcS376v7TAC1Laf13Z/rOY7BzS7m0NaDZdVn9BnWk5tpEXkxiyzs56 rYZAlDZzI2QYSoBb5fGhULzDON0wsSFGXNU8OXlpxQ8PKt8tZppZNa0rTuCCNsyDA2Bw qlmA== X-Gm-Message-State: AOJu0YxCmkz8JqhYLbEPBqvEgpB/pxL+1uxaleMtagZmfsglx6/UZEqY QiqW5Vo57k7MLG/FjCJZ8MxeYsLRNznFsN2CxyCJpT0yFULrRnEpjGHN43MzzjgMilgSCgJFhzL lO2JKH1cQq+ouGyuA1LJi70hgDhQSP71w X-Gm-Gg: ASbGncuqaYaGzXTRzDknroJguzbd1Pad9mcmbmdns+iFEpAGAA8Q+Z1WVAtLjXcLsoe pL1twWJh7vzEnnthFqz+MzxnjWVGlyEQroxmNIu0g4vdqUzgYhms4F+9bbf9lhjgFgjj+IbFS X-Google-Smtp-Source: AGHT+IEL9l7raF3nBmB3B5EBYWMdxkuYZ8m2/rUJlYrZF3mtb08ks6SG1w9qUt/rkSVH9eUSMWhCcj+8AlfjBee6wDs= X-Received: by 2002:a05:6871:8082:b0:297:2955:b009 with SMTP id 586e51a60fabf-2a7fca463bbmr28256485fac.1.1735889251630; Thu, 02 Jan 2025 23:27:31 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Fri, 3 Jan 2025 02:27:20 -0500 Message-ID: Subject: Re: Do we perform maintenance on pg_catalog tables To: pgsql-admin Content-Type: multipart/alternative; boundary="000000000000368c22062ac836b3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000368c22062ac836b3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 wrot= e: > Thanks Much for the explanation 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, 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-to= -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 "vacuumdb >> --all", and then run the queries again with EXPLAIN (BUFFERS). >> >> If they're the same speed, then your AUTOVACUUM settings are either Good >> Enough, or you got lucky and the tables were recently analyzed. If they= '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 > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000368c22062ac836b3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Percentages are not a reli= able metric when dealing with small tables, and pg_statistic is a pretty sm= all table.

Here, for=C2=A0example, are the stats f= or that=C2=A0table's only index.=C2=A0 Oh, no! 57% fragmentation!=C2=A0= But it's less than 7MB.=C2=A0 I've got much bigger fish to = fry than worrying about tiny tables that are regularly autovacuumed.
<= div>
TAPb=3D# select * from pgstatin= dex('pg_statistic_relid_att_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_no =C2=A0 =C2=A0 =C2=A0| 412
internal_pages =C2= =A0 =C2=A0 | 4
leaf_pages =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 796
empty_pag= es =C2=A0 =C2=A0 =C2=A0 =C2=A0| 0
deleted_pages =C2=A0 =C2=A0 =C2=A0| 5<= br>avg_leaf_density =C2=A0 | 36.67
leaf_fragmentation | 57.04
=

TAPb=3D# select relname, last_vacuum, las= t_autovacuum
from pg_stat_all_tables
where relname =3D 'pg_stat= istic';
=C2=A0 =C2=A0relname =C2=A0 =C2=A0| last_vacuum | =C2=A0 =C2= =A0 =C2=A0 =C2=A0last_autovacuum =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_statistic');
-[ RECORD 1 ]------+---------
ta= ble_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_percent =C2=A0 =C2=A0 =C2=A0| 73.25
dead_tuple_co= unt =C2=A0 | 1990
dead_tuple_len =C2=A0 =C2=A0 | 446536
dead_tuple_pe= rcent | 0.8
free_space =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 12954980
free_pe= rcent =C2=A0 =C2=A0 =C2=A0 | 23.23


On Thu, Jan 2, 2025 at 8:58= =E2=80=AFPM srijith s <udbdoctor@= gmail.com> wrote:
Thanks Much for the explanation Ron

I used a query= (found on the internet) to find the bloat on all tables in the database, i= t 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 i= t=C2=A0=C2=A0changed the=C2=A0bloat_pct=C2=A0 91%.=C2=A0 Maybe I am not usi= ng the right=C2=A0query ?

Query I used to find the= bloat

On Thu, Jan 2, 2025 at 8:28=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmai= l.com> wrote:
On Thu, Jan 2, 2025 at 7:18=E2=80=AF= PM Peter Gram <peter.m.gram@gmail.com> wrote:
Hi Ron

How do I know if my=C2=A0<= span style=3D"font-family:-apple-system,helveticaneue">AUTOVACUUM is=C2=A0<= /span>Reasonably-tu= ned ?=C2=A0

If your queries ar= e as fast as they can be, given proper index support.=C2=A0=F0=9F=98=89
=C2=A0
Specifically, I'd look at=C2=A0pg_stat_all_tabl= es columns=C2=A0relname,=C2=A0n_dead_tup,=C2=A0n_mod_since_analyze, GREATES= T(last_vacuum, last_autovacuum) and GREATEST(last_analyze, last_autoanalyze= ).

Run your system query a few times with EXPLAIN = (BUFFERS), then "vacuumdb --all", and then run the queries again = with EXPLAIN (BUFFERS).

If they're the same sp= eed, then your AUTOVACUUM settings=C2=A0are either Good Enough, or you got = lucky and the tables were recently analyzed.=C2=A0 If they're faster, t= hen you need to reduce the AUTOVACUUM config variables.

These are my=C2=A0settings for our=C2=A0workload.=C2=A0= YMMV.
autovacuum =3D on
autovacuum_m= ax_workers =3D 4
autovacuum_vacuum_scale_factor =3D 0.015
autovacuum_= vacuum_threshold =3D 50
autovacuum_vacuum_cost_delay =3D 4ms
autovacu= um_vacuum_cost_limit =3D 1000
autovacuum_analyze_scale_factor =3D 0.015<= br>autovacuum_analyze_threshold =3D 50


Med venlig hilse= n

Peter Gram
S=C3=A6byholmsvej 18=C2=A0
2500 Valby

Mobile: (+45) 5374 7= 107
<= div dir=3D"ltr">



On Fri, 3 Jan 2025 at 06.45, Ron Johnson <= ronljohnsonjr@= gmail.com> wrote:
On Thu, Jan 2, 2025 at 2:50=E2= =80=AFPM srijith s <udbdoctor@gmail.com> wrote:

Hello PostgreSQL Experts,

Do we perform postgres maintenance (vacuum/analyze/reindex)= on pg_catalog tables?=C2=A0

<= div>
Reasonably-tuned AUTOVACUUM config parameters should h= andle that for you.
=

--
Death to <Redacted&= gt;, and butter sauce.
Don't boil me, I'm still alive.
=
<Redacted> lobster!


--
Thanks & Regards
Srijith Sudhakaran


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