Re: Full Vacuum/Reindex vs autovacuum

On Mon, Nov 8, 2010 at 2:06 PM, Jason Long
<mailing(dot)lists(at)octgsoftware(dot)com> wrote:
> Every night when there is no activity I do a full vacuum, a reindex, and
> then dump a nightly backup.
>
> Is this optimal with regards to performance?  autovacuum is set to the
> default.

In the general case this seems way overkill. Do you suffer from a lot
of churn daily? That is, are there bunches of updates?

One thing you lose when running vacuum full is the space in the file
that is pre-allocated but empty. If you do lots of updates and
inserts, you'll be allocating pages and growing the underlying files
to hold your data. If you leave the unused space there, it is much
faster for postgres just to fill it. The key is keeping that unused
space from growing beyond reason... the trick is defining for your own
use case what "within reason" means.

As for re-index, don't bother. Unless you have some degenerate case
(something like a queue) where you always insert values at the tail
end of the index and delete from the front end of the index, and let
autovacuum do its work, you should remain in a fairly steady state.

There are queries you can run against the database to detect how
bloated your indexes are after a while, and then reindex if necessary.

I find that some of my data needs a reindex about every 4 to 6 months,
while others never benefit. I *never* run a vacuum full.