"richyen3@xxxxxxxxx" <richyen3@xxxxxxxxx> 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
