Re: Database-wide VACUUM ANALYZE

Steven Flatt escribió:

> Most of our large (partitioned) tables are insert-only (truncated
> eventually) so will not be touched by autovacuum until wraparound prevention
> kicks in. However the tables are partitioned by timestamp so tables will
> cross the 1.9 billion marker at different times (some not at all, as the
> data will have been truncated).

Note that as of 8.3, tables that are truncated do not need vacuuming for
Xid wraparound purposes, because the counter is updated on TRUNCATE (as
it is on CLUSTER and certain forms of ALTER TABLE).

> Do you still think the 250 million transactions away from shutdown is
> cutting it too close? Recall that the unintentional db-wide vacuum analyze
> that was going on last week on our system took less than two days to
> complete.

Is this 8.1 or 8.2? In the latter you don't ever need db-wide vacuums
at all, because Xid wraparound is tracked per table, so only tables
actually needing vacuum are processed. To answer your question, the
followup question is how many transactions normally take place in two
days. If they are way less than 250 million then you don't need to
worry. Otherwise, the database may shut itself down to protect from Xid
wraparound.

--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"La soledad es compañía"