Re: database 1.2G, pg_dump 73M?!

Ross Boylan <RossBoylan(at)stanfordalumni(dot)org> writes:
> reindexing had a huge effect.

So the indexes were indeed bloated. There are some known usage patterns
in which regular vacuum isn't very good at reclaiming space in b-tree
indexes. For example if you make daily entries in an index by date and
later remove all but the last-of-the-month entry --- this leaves a few
entries on every index page and we don't have code to collapse that,
short of reindexing.

However what seems more likely is that you're getting burnt by excessive
use of VACUUM FULL. V.F., far from shrinking indexes, tends to bloat
them. Recommended practice is to use plain VACUUM often enough that you
don't need VACUUM FULL.

regards, tom lane