On Fri, 2005-09-16 at 10:57, Warren Snelling wrote:
> Guido,
>
> Thanks for the pointers. I have routinely vacuumed the db, but not
> using full.
>
> By itself, the "vacuum full" run overnight did not free the space I was
> expecting - du still showed the old database taking about 12 Gb disk,
> and the fresh databases taking about 6.5 Gb. Running "reindex" on one
> large table freed ~5 Gb, so the old db now takes 7 Gb. Reindexing the
> remaining tables should get the databases to the same size.
>
> Would you suggest scripting the vacuum full / reindex process to run
> periodically (once a week or so)? Right now disk space on this machine
> is not an issue, but vacuuming and reindexing occasionally should keep
> the db from consuming too much excess space.
Assuming that you run regular vacuums often enough, and your fsm
settings are high enough to hold on to all the references to the deleted
tuples, you shouldn't need to run either full vacuums or reindexes.
There are some usage patterns that can result in indexes with very
sparsely populated blocks, such as when you create indexes to the
numbers 1 to 1000, then delete 95% or so of those references, and all
the new ones will be >1000, then repeat, increasing the number all the
time. But that type of situation is the only that might require
occasional reindexing.