Re: Do we perform maintenance on pg_catalog tables

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!