Re: pg_class (system) table increasing size.

dhaval jaiswal wrote:
> select * from pg_stat_sys_tables where relname = 'pg_class';
>
> -[ RECORD 1 ]-------+-----------
> relid | 1259
> schemaname | pg_catalog
> relname | pg_class
> seq_scan | 1838
> seq_tup_read | 3177416
> idx_scan | 1027456557
> idx_tup_fetch | 959682909
> n_tup_ins | 0
> n_tup_upd | 0
> n_tup_del | 0
> n_tup_hot_upd | 0
> n_live_tup | 0
> n_dead_tup | 0
> n_mod_since_analyze | 0
> last_vacuum |
> last_autovacuum |
> last_analyze |
> last_autoanalyze |
> vacuum_count | 0
> autovacuum_count | 0
> analyze_count | 0
> autoanalyze_count | 0
>
>
> Yes, the size of pg_class table is of 5 GB. However, the existing row is only 2380 only. It's got fragmented.

Looks like you lost the stat data awhile ago (probably due to a server
crash, or pg_stats_reset()) and it never got updated. I suggest doing
"ANALZYE pg_class" to create initial stats; that might prompt autovacuum
to vacuum the table. If the bloat is excessive, vacuuming might take a
very long time, in which case perhaps consider VACUUM FULL (but be very
aware of its consequences first).

I think it's likely that this has happened to other catalogs as well, so
check the pg_stat_sys_tables view for other entries with all zeroes in
the n_tup_* columns.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services