Re: BUG #13750: Autovacuum slows down with large numbers of tables. More workers makes it slower.

daveg(at)sonic(dot)net wrote:

> With more than a few tens of thousands of tables in one database
> autovacuuming slows down radically and becomes ineffective. Increasing the
> number of autovacuum workers makes the slow down worse.

Yeah, you need to decrease autovacuum_vacuum_cost_delay if you want to
make them go faster. (As more workers are started, the existing ones
slow down. The intent is that the I/O bandwidth allocation is kept
constant regardless of how many workers there are.)

> When they updated to 9.4.4 they also increased the number of autovacuum
> workers in anticipation of the multixact fix causing extra vacuuming.
> Several days later they observed massive catalog bloat, eg pg_attribute was
> over 200GB of mostly empty pages. This caused new connections to get stuck
> in startup as the catalogs no longer fit in the buffer cache.

Oh crap.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services