Обсуждение: Vacuuming

We're having a problem with database maintenance.  If we do a full vacuum, it takes down the app until it completes
(whichis a bit to be expected as a full vacuum locked the app out of the database).  However, if we don't do a periodic
fullvacuum, our drive gets filled up. 

The question is, is there a way to keep availability of the app 100% yet keep the database maintained without the drive
fillingup?  Or is it just the nature of the beast that a full vacuum needs to be done periodically? 

BTW- This is PostgreSQL 8.1.23 as part of RHEL 5.6.

Thanks, any tips on good practices for cron scripts doing Postgres maintenance would be great!


Phil

On Wed, Feb 22, 2012 at 7:08 PM, Philip Edelbrock
<phil@studiogeologie.com> wrote:
> The question is, is there a way to keep availability of the app 100% yet keep the database maintained without the
drivefilling up?  Or is it just the nature of the beast that a full vacuum needs to be done periodically? 

Short answer, no.

Are you doing ANY vacuuming on this database?

> BTW- This is PostgreSQL 8.1.23 as part of RHEL 5.6.
> Thanks, any tips on good practices for cron scripts doing Postgres maintenance would be great!

I have a client using this same version, and they use a number of
scripts in cron to vacuum various tables
that get modified at a high rate. Based on the clients needs, they're
vacuuming every ten minutes.
Note they're not doing VACUUM FULL.
Your mileage may vary, and you'll need to determine what best fits your needs.

Moving to postgres 9 would help alleviate some of your performance and
maintenance issues.