Re: index bloat WAS: reindexing pg_shdepend

Tom Lane wrote:
> Joseph S <jks(at)selectacast(dot)net> writes:
>> ... 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 are taking the latter as a blind must-be-wrong condition, you are
> fooling yourself -- it's not true for small tables.

I know it isn't true for small tables. Tables can have a size of 0 but
the minimum size for an index seems to be two pages. Indexes can also
rival the size of the table when the table when the index is on all the
columns of the table. But most of the time having an index bigger than
the table itself mean I need a REINDEX.

>
> Have you checked whether the VACUUM FULL + REINDEX actually makes
> anything smaller?

Yes. I'm mostly seeing the problem on tables of counts that are updated
frequently by triggers on other tables. It seems autovacuum can't keep
up with the frequency of updates. The table size itself can shrink by
50%, but the indexes can shrink by 90%.

I just ran my VACUUM FULL/REINDEX script at 11am. Last time I ran it
was 930pm last night. Some before/afters:

BEFORE

pg_catalog pg_class table 172,032 19.476
pg_catalog pg_class_oid_index index 57,344 58.429
pg_catalog pg_class_relname_nsp_index index 180,224 18.591

AFTER

pg_catalog pg_class table 90,112 41.3
pg_catalog pg_class_oid_index index 32,768 103
pg_catalog pg_class_relname_nsp_index index 73,728 59

BEFORE

public acount table 434,176 119.302
public acount_pkey index 172,032 301.095
public ad_x_idx index 638,976 36.551

AFTER

public acount table 335,872 155.561
public acount_pkey index 163,840 318.9
public a_x_idx index 131,072 221.143