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/ ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
