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

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.

>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.

> 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.