public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-admin <[email protected]>
Subject: Re: Do we perform maintenance on pg_catalog tables
Date: Fri, 3 Jan 2025 02:27:20 -0500
Message-ID: <CANzqJaC6zYyJxNFyT6cEi6yWxWK+zCokzYQhKy1F=rt65oWchg@mail.gmail.com> (raw)
In-Reply-To: <CACReZzyFn=EbgxM_aEv=EmbfJHs6eodrgKKJj9XOXset6XusGw@mail.gmail.com>
References: <CACReZzxJ0WfcJC9APM4so63aUP0JfpO0+SEHfeHu2i1vTmRB5A@mail.gmail.com>
<CANzqJaBj=Js=nu==8PafL_Vk729_C+TixPgWm+7vSm8Qa1xS0Q@mail.gmail.com>
<CAJ=80GU6HZE9XKc4vysXC21+Ou3svsN-q5_k12=fEL0_Up=6Dg@mail.gmail.com>
<CANzqJaD7wvwfMJ9mi=RVAF9KwtMf5_9ksz8xdgp_rD1pTwhGfA@mail.gmail.com>
<CACReZzyFn=EbgxM_aEv=EmbfJHs6eodrgKKJj9XOXset6XusGw@mail.gmail.com>
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=# 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=# select relname, last_vacuum, last_autovacuum
from pg_stat_all_tables
where relname = 'pg_statistic';
relname | last_vacuum | last_autovacuum
--------------+-------------+-------------------------------
pg_statistic | | 2025-01-02 23:40:17.647375-05
(1 row)
TAPb=# 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 PM srijith s <[email protected]> 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, 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 PM Ron Johnson <[email protected]>
> wrote:
>
>> On Thu, Jan 2, 2025 at 7:18 PM Peter Gram <[email protected]> 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. 😉
>>
>> 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 = on
>> autovacuum_max_workers = 4
>> autovacuum_vacuum_scale_factor = 0.015
>> autovacuum_vacuum_threshold = 50
>> autovacuum_vacuum_cost_delay = 4ms
>> autovacuum_vacuum_cost_limit = 1000
>> autovacuum_analyze_scale_factor = 0.015
>> autovacuum_analyze_threshold = 50
>>
>>
>>> Med venlig hilsen
>>>
>>> Peter Gram
>>> Sæbyholmsvej 18
>>> 2500 Valby
>>>
>>> Mobile: (+45) 5374 7107
>>> Email: [email protected]
>>>
>>>
>>>
>>> On Fri, 3 Jan 2025 at 06.45, Ron Johnson <[email protected]>
>>> wrote:
>>>
>>>> On Thu, Jan 2, 2025 at 2:50 PM srijith s <[email protected]> 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 <Redacted>, 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> lobster!
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Do we perform maintenance on pg_catalog tables
In-Reply-To: <CANzqJaC6zYyJxNFyT6cEi6yWxWK+zCokzYQhKy1F=rt65oWchg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox