Full Vacuum/Reindex vs autovacuum

On Monday 8. November 2010 20.06.13 Jason Long wrote:

I currently have Postgres 9.0 install after an upgrade. My database is
relatively small, but complex. The dump is about 90MB.

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.

I've got a database about the same size order (65 MB on disk, 5 MB dump.tgz)
and I never bother with neither full vacuum nor reindexing. I run the default
autovacuum, and if the db becomes bloated for some reason, I just do a
dump/drop/reload cycle. It's done in a few seconds.

regards,
Leif B. Kristensen
http://solumslekt.org/

On Mon, Nov 8, 2010 at 2:06 PM, Jason Long
<mailing.lists@octgsoftware.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.