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 : 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 ?
