Y Sidhu wrote:
I cannot answer that question on the grounds that it may incriminate me. Hehe. I am really trying to get our vacuum times down. The cause of the problem, I believe, are daily mass deletes. Yes, I am working on performing vacuums more than once a day. No, I am not considering partitioning the offending table because a few scripts have to be changed. I am also turning the knobs as I find them.
Yudhvir, I don't think the tuning options are going to make any difference to your vacuum times.
I don't know if this been brought up already, but the way vacuum works in 8.1 and 8.2 is that when it scans the table for the second time, it does a WAL flush for every block that had deleted tuples on it. That's really expensive, in particular if you don't have a separate drive for the WAL, and/or you don't have a battery backed up cache in your controller.
You could try turning fsync=off to see if it helps, but be warned that that's dangerous. If you have a power failure etc. while the database is busy, you can get data corruption. So do that to see if it helps on a test matchine, and if it does, put WAL on another drive or get a controller with battery backed up cache. Or wait until release 8.3, which should fix that issue.
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
