index bloat WAS: reindexing pg_shdepend

Tom Lane wrote:
> Joseph S <jks(at)selectacast(dot)net> writes:
>> Me too. I don't change my db schema that much, but I experience bloat
>> in the pg_tables that I don't expect. For instance pg_opclass needs a
>> VACUUM FULL/REINDEX once a week or I notice the indexes are larger than
>> the table itself. Could it be my heavy use of temp tables?
>
> pg_opclass? That's read-only for most people. What are you doing with
> operator classes?

I know. I can't figure it out. I barely know what operator classes
are, but I'm pretty sure I'm not modifying them in any way.
>
> Heavy use of temp tables would expand pg_class, pg_type, and especially
> pg_attribute, but as long as you have a decent vacuuming regimen (do you
> use autovac?) they shouldn't get out of hand.
>
I do use autovac. Like I said they don't get really out of hand, only
up to 20 megs or so before I noticed that it was weird. The large
indexes are what tipped me off that something strange was going on.

I only noticed this because I was making an effort to monitor index
bloat on my regular tables. It could be there are a lot of people out
there who are experiencing this but don't notice because 20 megs here
and there don't cause any noticeable problems.

So how about it list? Do you know how bloated your indexes are getting?
I use this sql:

select (select nspname FROM pg_catalog.pg_namespace where oid =
relnamespace) AS schema, relname, CASE c.relkind WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN
's' THEN 'special' END as "Type" ,CASE c.relkind IN ('i','r','S','')
WHEN true THEN pg_relation_size(relname) END AS bytes, CASE relpages >
0 WHEN true THEN reltuples/relpages END AS tuplesperpage FROM
pg_catalog.pg_class c WHERE pg_catalog.pg_table_is_visible(c.oid) order
by schema, relname;

... and when I notice that the tuplesperpage for the indexes is low (or
that the indexes are bigger then the tables themselves) I know it is
time for a VACUUM FULL and REINDEX on that table.

If you really want to get fancy you can save the results of that into a
table with a timestamp. Then every (insert time period here) run VACUUM
FULL/REINDEXs on the individual tables and store the new sizes with
timestamps.