Re: autovacuum big table taking hours and sometimes seconds

On Wed, 2019-02-06 at 12:29 +0200, Mariel Cherkassky wrote:
> Hi,
> I have a table with a bytea column and its size is huge and thats why postgres created a toasted table for that column.
> The original table contains about 1K-10K rows but the toasted can contain up to 20M rows.
> I assigned the next two settings for the toasted table :
> alter table orig_table set (toast.autovacuum_vacuum_scale_factor = 0);
> alter table orig_table set (toast.autovacuum_vacuum_threshold =10000);
>
> Therefore I hoped that after deletion of 10K rows from the toasted table autovacuum will launch vacuum on the toasted table.
> From the logs I see that sometimes the autovacuum is running once in a few hours (3-4 hours) and sometimes it runs even every few minutes.
> Now I wanted to check if only depends on the thresholds and on the frequency of the deletes/updates on the table ?
> In some cases the autovacuum is taking a few hours (4+) it finishes and then immediatly is starting to run vacuum again on the table :
>
> Now the question is how to handle or tune it ? Is there any change that I need to increase the cost_limit / cost_delay ?

Maybe configuring autovacuum to run faster will help:

alter table orig_table set (toast.autovacuum_vacuum_cost_limit = 2000);

Or, more extreme:

alter table orig_table set (toast.autovacuum_vacuum_cost_delay = 0);

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com