Re: Postgres: 7.4 or 8.0?

Grant Johnson
2005-11-07T15:37:45Z

>Same here, 8.0.4 is working quite well.  Just be sure to run "vacuumdb
>--full --analyze --all" nightly to keep things snappy...
>
>
With autovacuum, you may not need this at all.   Additionally, vacuum
full may or may not be a good idea, depending on the nature of your
changes.   If you have a one time, big data update or delete, vacuum
full is your friend, as it frees up all of the unused space, however, if
you have normal, regular updates, deletes, and inserts, the free space
gets reused, and it is faster than allocating new space.  If you find
your space usage growing over time faster than your actual data is
growing, you need an occasional vacuum full, but usually regular vacuum
is better.   Also remember that while vacuum full is running, you cannot
access your tables because they have table locks on them, unlike regular
vacuum which cleans up stale rows without a lock.

Analyze is your friend any time you have made statistically significant
data changes, but otherwise may be just a waste of cycles.

More tips:
Reindex can be good, especially if you have indexed fields which have
ever ascending values that will cause the trees to get off balance.  It
will reclaim space and make indexes faster, but it can cause the index
to not be used while it is being rebuilt, so be careful about when to
use it.

If you are having insert/update performance issues, move your xlog and
clog to separate spindles from your index and data files.

Select performance can be helped with indexing fields in your where
clauses.   Look for opportunities to do partial indexes.  They will save
space and run faster than full indexes if you only look at a small part
of the table most of the time anyway.

Remember that unless a condition will reduce the number of rows by at
least half, an index will not help.