Re: vacuum questions

On Monday 20 August 2007 14:33, Juliann Meyer wrote:
> Currently running v7.4.8 postgres on a RHE Linux 4.0 box. Will be
> upgraded to postgres 8.2.x sometime this fall.
>
> Several of the tables in the database that resides on this system have
> lots of inserts and updates. Very little is deleted at this time, as
> the database is intended for archiving.
>

Be aware that an update is equivilant to a delete+insert, so even if you
aren't "deleting", your updates will still cause accumlation of dead rows in
your table.

> Currently a cron job runs a vacuum analyze 3 times a day. Is there any
> need to run a vacuum full every so often?

There could be. If the number of updates your doing is greater than your
free_space_map settings, then you wont be recovering all of the space you
would want to be. Granted, a better solution is to increase free_space_map
settings or vacuum analyze frequency, but you might still need an initial
vacuum full to recover space. I *think* on 7.4 the output of vacuum analyze
will tell you what your free space map settings need to be, so examine it's
output the next few runs.

> If so, how often should a vacuum full be done? And do all other
> processes that "hit" the database need to shut off when a vacuum full is
> done?

Again, ideally you dont ever want to run vacuum full, so I'd look into getting
more regular vacuum runs if needed, rather than doing regular vacuum fulls.
Other things don't neccessarily need to be shut off, but vacuum full will
lock tables exclusivly, so it will block other processes while it runs. How
bad that is depends on your situation. Also, be aware that vacuum full may
take considerably longer than a regular vacuum.

>
> Also another office that has a similar setup has switched to running
> pg_autovacuum instead of the vacuum analyze 3 times/day, are there any
> advantages and/or disadvantages to this approach?
>

The main advantage is that you will get more accurate vacuum/analyze
information from your system while reducing overall load, as tables will only
be vacuum as needed. (Think of it as a sniper approach, rather than the
bazooka method your using now) The downside is that 7.4's pg_autovacuum is a
little more complex than what you'll find in more recent versions of
postgres, but if you're having trouble getting a handle on a good vacuum
scheme, it might be worth it.

--
Robert Treat
Database Architect
http://www.omniti.com/