Re: Do we perform maintenance on pg_catalog tables

Thanks Ron ☺️

On Fri, Jan 3, 2025 at 2:27 AM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> 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=# 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 <udbdoctor(at)gmail(dot)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, 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 <ronljohnsonjr(at)gmail(dot)com>
>> wrote:
>>
>>> On Thu, Jan 2, 2025 at 7:18 PM Peter Gram <peter(dot)m(dot)gram(at)gmail(dot)com>
>>> 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: peter(dot)m(dot)gram(at)gmail(dot)com
>>>>
>>>>
>>>>
>>>> On Fri, 3 Jan 2025 at 06.45, Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
>>>> wrote:
>>>>
>>>>> On Thu, Jan 2, 2025 at 2:50 PM srijith s <udbdoctor(at)gmail(dot)com> 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!
>

--
Thanks & Regards
Srijith Sudhakaran