Re: autovacuum vacuums entire database...is this right?

"richyen3(at)gmail(dot)com" <richyen3(at)gmail(dot)com> writes:
> I'm just wondering if autovacuum is ever supposed to vacuum the entire
> database during one of its runs. As far as I remember, it's supposed
> to vacuum one table at a time, based on the
> autovacuum_vacuum_threshold, autovacuum_analyze_threshold, etc.
> settings.
>
> For some reason, autovacuum decided to run a vacuum on my entire
> database (29GB large), and it's taking forever:
>
> select now(), query_start, current_query, backend_start, procpid,
> usename from pg_stat_activity where current_query <> '<IDLE>';
> now | query_start |
> current_query | backend_start | procpid | usename
> -------------------------------+-------------------------------
> +---------------+-------------------------------+---------+----------
> 2007-04-15 06:34:27.925042-07 | 2007-04-14 22:23:31.283894-07 |
> VACUUM | 2007-04-14 22:23:31.274121-07 | 9406 | postgres
>
> Is this expected behavior?

It is, for the case where, pre-8.2, a database is getting towards the
point where it will soon see XID roll-over, which has the potential
for data to disappear (the data's there; it's just invisible). In
that case, a vacuum of the whole database is necessary.

In version 8.2 and later, XID rollover is tracked on a per-table
basis, not on a per-database basis, which eliminates the need to ever
vacuum "the whole thang."

Compare the 8.1 and 8.2 documentation, as needed:

http://www.postgresql.org/docs/8.1/static/maintenance.html#VACUUM-FOR-WRAPAROUND

http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
--
select 'cbbrowne' || '@' || 'acm.org';
http://linuxfinances.info/info/x.html
"I'd crawl over an acre of 'Visual This++' and 'Integrated Development
That' to get to gcc, Emacs, and gdb. Thank you."
-- Vance Petree, Virginia Power