Thank you Terry. You get the gold star. :) I was waiting for that to come up.
Success means handling this condition. A whole database vacuum and dump-restore is out of the question. Can a properly tuned autovacuum prevent the situation?
-Greg
Hi!
With millions of tables you have to set autovacuum_max_workers sky-high =). We have some situation when at thousands of tables autovacuum can’t vacuum all tables that need it. Simply it vacuums some of most modified table and never reach others.
Any autovacuum worker should vacuum all tables in its assigned database which it perceives need vacuuming, as long as it can get the lock. Unless the worker is interrupted, for example by frequent database shutdowns, it should reach all tables in that database before it exits. Unless there is a bug, or you are constantly restarting the database before autovacuum can finish or doing something else to kill them off, what you describe should not happen.
If it is a bug, we should fix it. Can you give more details?
There is a known bug when you multiple active databases in the same cluster. Once one database reaches the age where anti-wrap around vacuums kick in, then all future autovacuum workers are directed to that one database, starving all other databases of auto-vacuuming. But that doesn't sound like what you are describing.