autovacuum_vacuum_cost_delay

Hi Shenvail,

Based on what I could understand,

Lower value will cause more frequent run of Autovacuum. Will have
less delay between processing data chunks but will put more load on the I/O
system. Autovacuum tasks will complete faster. Cleanup of dead tuples and
blots will be quicker thus increasing performance in the cases where tables
are heavily updated/deleted.

Higher values will result in longer delays between processing data chunks
thus slowing down the completion of autovacuum tasks. Autovacuum will have
less I/O load for its tasks. Database performance will be degraded if
operations are more update/delete intensive. Dead tuples and bloat will
take longer to be cleaned up.
It can be beneficial for performance of other database operations,
especially if your system is I/O-bound. Can also be helpful when system
resources are less

Regards,
Muhammad Ikram
Bitnine Global.

On Tue, Jul 9, 2024 at 4:38 PM Shenavai, Manuel <manuel.shenavai@sap.com>
wrote:

Hi everyone,

We are doing some tests with different autovacuum settings.

Looking at autovacuum_vacuum_cost_delay:

https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY

Can someone help to understand what a high or low value of this setting
really means? Would it be OK to set this to 0? If not, why not?

Thanks in advance &
Best regards,

Manuel

--
Muhammad Ikram

## Shenavai, Manuel (manuel.shenavai@sap.com):

Looking at autovacuum_vacuum_cost_delay:
https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY

Can someone help to understand what a high or low value of this setting really means? Would it be OK to set this to 0? If not, why not?

The autovacuum settings link to the docs of the regular vacuum settings:
https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST
which explains the parameters at least as good as I could do here.
You can set autovacuum_vacuum_cost_delay to 0 - that would give you
"unlimited" autovacuum bandwidth (limited by whatever your system and
your other configuration can give you). That might create very
noticeable performance impact on the actual workload, which is most
often rather undesirable.
In short: it's possible to set that parameter to 0, but that could
well be a not-so-good idea.

Regards,
Christoph

--
Spare Space