Skip site navigation (1) Skip section navigation (2)

Re: vacuum questions

From: Robert Treat <robert(at)omniti(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Cc: Juliann Meyer <Julie(dot)Meyer(at)noaa(dot)gov>
Subject: Re: vacuum questions
Date: 2007-08-22 16:53:06
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
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

In response to

pgsql-admin by date

Next:From: Kevin GrittnerDate: 2007-08-22 20:17:08
Subject: "Stand-in" server recovery techniques
Previous:From: Tena SakaiDate: 2007-08-22 15:55:48
Subject: Re: tar, but not gnu tar

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group