Re: autovacuum for large periodic deletes

["Jim C. Nasby" <jnasby(at)pervasive(dot)com>]

On Wed, May 17, 2006 at 01:39:35PM -0700, Sriram Dandapani wrote:
> About 5-10 million rows stay after deletion. There are a few other
> tables where the daily deletion totals about 3-6 million. 

The default autovac settings will only vacuum a table after 40% of the
rows are dead. That seems pretty high to me, so I regularly recommend
cutting all the thresholds and scale factors in half.

> Would a vacuum full/cluster affect other operations. These tables have a
> 24x7 high data insertion rate.

VACUUM FULL and CLUSTER both aquire exclusive locks.

BTW, it sounds like table partitioning might be useful for you. It would
allow you to setup a partition for each day, and then drop one day's
worth of data very quickly.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461