Re: Postgres: 7.4 or 8.0?

Tom Copeland
2005-11-07T16:40:26Z

On Mon, 2005-11-07 at 10:37 -0500, Grant Johnson wrote:
> >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.

Excellent, thanks for posting.  That was all new stuff to me, thanks for
the info.

> 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.

Cool, this is the first I''ve heard of this command.  Seems to run
pretty
quickly, too; at least, on RubyForge''s 100+ tables and 3.5M+ records it
ran in about 3 seconds.

Thanks,

Tom