I stopped using autovacuum months ago because of similar problems
(version 8.1.4). Because we do some major inserts and updates about four
times a day, there were a few tables that I didn't want autovacuumed.
Even after I turned autovacuum off for these tables it still tried to
vacuum them while the updates were running. Instead, I just created cron
jobs to vacuum the two tables with the most updates daily, and the one
with the most inserts weekly. Performance has been pretty good.
Most other tables don't have nearly as many updates, however when I
checked the transaction IDs, I see that a problem is creeping closer as
we slowly approach the 2 billion mark.
imp=# select age(datfrozenxid) from pg_database where datname = 'imp';
age
------------
1571381411
(1 row)
Time to start VACUUM FULL ANALYZE over the weekend.
Ron
-------- Original Message --------
select age(datfrozenxid) from pg_database where datname = 'your database'
2 billions and you are screwed. Autovacuum starts panicking way before
that, to have enough slack.
dbname=# select age(datfrozenxid) from pg_database where datname = 'dbname';
age
------------
1648762992
(1 row)
Sooo... looks like time to quickly upgrade to 8.1 head.
Thanks for the help,
Csaba.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
