Re: Do we perform maintenance on pg_catalog tables - Mailing list pgsql-admin

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@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, 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@gmail.com> wrote:

On Thu, Jan 2, 2025 at 7:18 PM Peter Gram <peter.m.gram@gmail.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.m.gram@gmail.com

On Fri, 3 Jan 2025 at 06.45, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

On Thu, Jan 2, 2025 at 2:50 PM srijith s <udbdoctor@gmail.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!