Re: autovacuum maintenance_work_mem

Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> writes:
> On Wed, Nov 17, 2010 at 01:12, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>> So for the initial implementation, we could just have each worker set
>> its local maintenance_work_mem to autovacuum_maintenance_memory / max_workers.
>> That way there's never excessive memory usage.

> It sounds reasonable, but is there the same issue for normal connections?
> We can limit max connections per user, but there are no quota for total
> memory consumed by the user. It might not be an autovacuum-specifix issue.

I agree with Itagaki-san: this isn't really autovacuum's fault.

Another objection to the above approach is that anytime you have fewer
than max_workers AV workers, you're not using the memory well. And not
using the memory well has a *direct* cost in terms of increased effort,
ie, extra indexscans. So this isn't something to mess with lightly.

I can see the possible value of decoupling autovacuum's setting from
foreground operations, though. What about creating
autovacuum_maintenance_mem but defining it as being the
maintenance_work_mem setting that each AV worker can use? If people
can't figure out that the total possible hit is maintenance_work_mem
times max_workers, their license to use a text editor should be revoked.

regards, tom lane