David Schnur <dnschnur@gmail.com> wrote:
> Regular VACUUM is fine most of the time; it frees up space for
> re-use, the space gets re-used, and the disk size stays constant.
> But at certain non-predictable points in time, the database may
> expire several times more rows than usual, and in that case I want
> to reclaim the space for the OS, since it may not be used by the
> database again for some time.
If you actually expect it to be re-used by the database sometime
later, I would just stick with normal VACUUM (with adequate fsm
settings). The only counter-argument the jumps out at me is that
you have some actual need to use that space in the interim and you
can ensure that you're done with it and free it up before the
database needs it again.
Well, I guess if there could be another reason: is there a
performance improvement from the VACUUM FULL and REINDEX which makes
it worth the cost of such aggressive maintenance? Even if so,
CLUSTER might help more (because of ordering the data) if you have
the disk space to support it. Otherwise, you might want to try
pg_dump and a restore to see if you can get the equivalent of the
VACUUM FULL faster.
-Kevin