Re: autovacuum suggestions for 500,000,000+ row tables?

On 20 Jun 2005, at 15:59, Jacques Caron wrote:

> Hi,
>
> At 16:44 20/06/2005, Alex Stapleton wrote:
>
>> We never delete
>> anything (well not often, and not much) from the tables, so I am not
>> so worried about the VACUUM status
>>
>
> DELETEs are not the only reason you might need to VACUUM. UPDATEs
> are important as well, if not more. Tables that are constantly
> updated (statistics, session data, queues...) really need to be
> VACUUMed a lot.

We UPDATE it even less often.

>
>> but I am wary of XID wraparound
>> nuking us at some point if we don't sort vacuuming out so we VACUUM
>> at least once every year ;)
>>
>
> That would give you a maximum average of 31 transactions/sec...
> Don't know if that's high or low for you.

It's high as far as inserts go for us. It does them all at the end of
each minute.

>
>> However not running ANALYZE for such huge
>> periods of time is probably impacting the statistics accuracy
>> somewhat, and I have seen some unusually slow queries at times.
>> Anyway, does anyone think we might benefit from a more aggressive
>> autovacuum configuration?
>>
>
> ANALYZE is not a very expensive operation, however VACUUM can
> definitely be a big strain and take a looooong time on big tables,
> depending on your setup. I've found that partitioning tables (at
> the application level) can be quite helpful if you manage to keep
> each partition to a reasonable size (under or close to available
> memory), especially if the partitioning scheme is somehow time-
> related. YMMV.
>
> Jacques.

That's not currently an option as it would require a pretty large
amount of work to implement. I think we will have to keep that in
mind though.