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