Schwenker, Stephen wrote:
> 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.
You probably need to vacuum some tables more often than once a day;
and/or increase the FSM settings.
After a deletion of 99% of records, you probably should do a VACUUM FULL
anyway (or maybe CLUSTER); plain VACUUM won't be able to recover from
such a shock.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support