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

On Fri, 18 Mar 2016 09:39:34 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> writes:
> > I actually wonder if instead of doing all the the hard way in C whether
> > we should just use SPI for each worker to build it's list of tables. The
> > big advantage that would provide is the ability for users to customize
> > the scheduling, but I suspect it'd make the code simpler too.
>
> By that you mean "user can write a SQL query that determines autovacuum
> targets"? -1. That would bring us back to the bad old days where a
> poorly-thought-out vacuum cron job would miss tables and lead to a
> database shutdown. Not to mention SQL injection risks.
>
> If we need to improve autovac's strategy, let's do that, but not by
> deeming it the user's problem.

I have some thoughts for a different approach. In short, the stats collector
actually knows what needs vacuuming because queries that create dead tuples
tell it. I'm considering have the stats collector maintain a queue of
vacuum work and that autovacuum request work from the stats collector. When I
have something more concrete I'll post it on hackers.

-dg

--
David Gould 510 282 0869 daveg(at)sonic(dot)net
If simplicity worked, the world would be overrun with insects.