Re: HELP: Urgent, Vacuum problem

On Mon, 2006-12-04 at 09:56, Schwenker, Stephen wrote:
> Hello,
>
> I'm having a major Vacuuming problem. I used to do a full vacuum
> every morning on my postgres database to clean up empty space on a
> table but because of it's size, the locking of the database causes my
> application server to max out the database connections and causes
> database errors. To fix that problem, I have turned off the full
> vacuum and are just doing a standard analyze vacuum. No I'm getting
> very close to running out of space on my disks because the table keeps
> on growing and the database is not re-using deleted record space. I
> know this because I delete 99% of the records from the table after I
> have exported them but the size of the database tables are not
> decreasing. Now I can't shrink the size of the tables because the
> full vacuum takes too long to run Over 2 hours and locks the table
> for too long.
>
> Can anyone help me with fixing my problem with vacuuming and disk
> space?
>
> I'm using version 7.4.2 on solaris.

A few points:

1: UPGRADE YOUR DATABASE to the latest 7.4 version. There were, if I
remember correctly, data eating bugs in 7.4.2 that were fixed later. Of
all the pieces of software I've ever used, none has ever been more
reliable to upgrade than postgresql. Whatever conservative philosophy
might be keeping you from updating is playing against you here. You're
far more likely to suffer catastrophic failure from running a buggy
version than from upgrading.

2: STOP THE FULL VACUUMS! Full vacuums should not be necessary. If
they are, something else is wrong. You're using a sledge hammer to swat
a fly. Plus in 7.4.xx series, vacuum fulls can cause problems with
index bloat, iffin I remember correctly.

3: Use vacuum verbose to see how many pages / entries you need in your
fsm, and adjust accordingly.

4: Look at migrating to 8.1 or even 8.2 (due out real soon now). There
have been a lot of advances in pg since 7.4, and the upgrade is pretty
painless as long as the dump / restore isn't too much of a burden.