Re: index bloat WAS: reindexing pg_shdepend

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. 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