Re: index bloat WAS: reindexing pg_shdepend

Joseph S <jks@selectacast.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.  For instance, in a
freshly initdb'd database:

postgres=# vacuum verbose pg_opclass;
INFO:  vacuuming "pg_catalog.pg_opclass"
INFO:  index "pg_opclass_am_name_nsp_index" now contains 107 row versions in 4 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_opclass_oid_index" now contains 107 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_opclass": found 0 removable, 107 nonremovable row versions in 2 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.01s/0.00u sec elapsed 0.00 sec.
VACUUM
postgres=#

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

            regards, tom lane