The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/11/runtime-config-autovacuum.html
Description:
The `autovacuum_vacuum_cost_delay` setting changes to 2ms in PostgreSQL 12
but in the old Postgresql version, the default setting is still 20ms. I
would suggest adding a suggestion in the old document
to lower the autovacuum_vacuum_cost_delay as:
> The default value of autovacuum_vacuum_cost_delay is reduced to 2ms in
PostgreSQL 12. Reducing the autovacuum_vacuum_cost_delay will make the
autovacuum more aggressive and might reduce the vacuum cost for
write-intensive workload on big table.
Detail:
I run the PostgreSQL 11 on a table with 1 million record under a
write-instensive workload by sysbench. The result shows that when the
autovacuum_vacuum_cost_delay is 20ms, the automatic vacuum takes 15 seconds.
If I reduce the autovacuum_vacuum_cost_delay to 2ms, the automatic vacuum
just uses 3 seconds.
When autovacuum_vacuum_cost_delay = 20ms
LOG: automatic vacuum of table "test.public.sbtest1": index scans: 1
pages: 0 removed, 27172 remain, 0 skipped due to pins, 0 skipped
frozen
tuples: 2787 removed, 993634 remain, 387 are dead but not yet
removable, oldest xmin: 1358042
buffer usage: 21557 hits, 9160 misses, 1262 dirtied
avg read rate: 4.783 MB/s, avg write rate: 0.659 MB/s
system usage: CPU: user: 1.21 s, system: 0.04 s, elapsed: 14.96 s
When autovacuum_vacuum_cost_delay = 2ms
LOG: automatic vacuum of table "test.public.sbtest1": index scans: 1
pages: 0 removed, 27172 remain, 0 skipped due to pins, 0 skipped
frozen
tuples: 2817 removed, 994303 remain, 85 are dead but not yet
removable, oldest xmin: 1329367
buffer usage: 23129 hits, 6987 misses, 3873 dirtied
avg read rate: 20.532 MB/s, avg write rate: 11.381 MB/s
system usage: CPU: user: 0.92 s, system: 0.02 s, elapsed: 2.65 s