Re: Do we perform maintenance on pg_catalog tables

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