Re: autovacuum big table taking hours and sometimes seconds

On Thu, Feb 7, 2019 at 6:55 AM Mariel Cherkassky <
mariel(dot)cherkassky(at)gmail(dot)com> wrote:

I have 3 questions :
> 1)To what value do you recommend to increase the vacuum cost_limit ? 2000
> seems reasonable ? Or maybe its better to leave it as default and assign a
> specific value for big tables ?
>

That depends on your IO hardware, and your workload. You wouldn't want
background vacuum to use so much of your available IO that it starves your
other processes.

> 2)When the autovacuum reaches the cost_limit while trying to vacuum a
> specific table, it wait nap_time seconds and then it continue to work on
> the same table ?
>

No, it waits for autovacuum_vacuum_cost_delay before resuming within the
same table. During this delay, the table is still open and it still holds a
lock on it, and holds the transaction open, etc. Naptime is entirely
different, it controls how often the vacuum scheduler checks to see which
tables need to be vacuumed again.

> 3)So in case I have a table that keeps growing (not fast because I set the
> vacuum_scale_factor to 0 and the autovacuum_vacuum_threshold to 10000). If
> the table keep growing it means I should try to increase the cost right ?
> Do you see any other option ?
>

You can use pg_freespacemap to see if the free space is spread evenly
throughout the table, or clustered together. That might help figure out
what is going on. And, is it the table itself that is growing, or the
index on it?

Cheers,

Jeff