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 :
2019-01-29 07:10:58 EST 14083 LOG: automatic vacuum of table "db.pg_toast.pg_toast_14430": index scans: 3
pages: 1672 removed, 7085056 remain
tuples: 6706885 removed, 2023847 remain
buffer usage: 4808221 hits, 6404148 misses, 6152603 dirtied
avg read rate: 2.617 MiB/s, avg write rate: 2.514 MiB/s
system usage: CPU 148.65s/70.06u sec elapsed 19119.55 sec
This run took 19119 sec ~ 5 hours
2019-01-29 10:05:45 EST 11985 LOG: automatic vacuum of table "db.pg_toast.pg_toast_14430": index scans: 2
pages: 2752 removed, 7082304 remain
tuples: 3621620 removed, 1339540 remain
buffer usage: 2655076 hits, 3506964 misses, 3333423 dirtied
avg read rate: 2.638 MiB/s, avg write rate: 2.508 MiB/s
system usage: CPU 71.22s/37.65u sec elapsed 10384.93 sec
this run took 10384 sec ~ 2.88 hours.
the diff between the summaries is 3 hours and the second run took 2.88 hours which means that the autovacuum launched vacuum on the table a few minutes after the first vacuum has finished.
In addition, as I said sometimes if runs very often :
2019-02-04 09:26:23 EST 14735 LOG: automatic vacuum of table "db.pg_toast.pg_toast_14430": index scans: 1
pages: 1760 removed, 11149568 remain
tuples: 47870 removed, 4929452 remain
buffer usage: 200575 hits, 197394 misses, 24264 dirtied
avg read rate: 5.798 MiB/s, avg write rate: 0.713 MiB/s
system usage: CPU 1.55s/1.38u sec elapsed 265.96 sec
2019-02-04 09:32:57 EST 26171 LOG: automatic vacuum of table "db.pg_toast.pg_toast_14430": index scans: 1
pages: 2144 removed, 11147424 remain
tuples: 55484 removed, 4921526 remain
buffer usage: 196811 hits, 209267 misses, 34471 dirtied
avg read rate: 5.459 MiB/s, avg write rate: 0.899 MiB/s
system usage: CPU 1.73s/1.54u sec elapsed 299.50 sec
Now the question is how to handle or tune it ? Is there any change that I need to increase the cost_limit / cost_delay ?
