Hey,
I have a table with 3 columns and one of those columns is bytea type
A(int,int,bytea).
Every row that I insert is pretty big and thats why postgresql decided to
save that column in a toasted table(pg_toasted_Aid). I had a lot of bloat
issues with that table so I set the vacuum_threshold of the original
table(A) into 0.05. Usually the A table has about 1000+ rows but the
toasted table has more then 25M . Now, I realized from the autovacuum
logging, that when autovacuum runs on the original table (A) it doesn't
necessary run on the toasted table and this is very weird.
I tried to set the same threshold for the toasted table but got an error
that it is a catalog table and therefore permission is denied.
2019-01-17 12:04:15 EST db116109 ERROR: permission denied:
"pg_toast_13388392" is a system catalog
2019-01-17 12:04:15 EST db116109 STATEMENT: alter table
pg_toast.pg_toast_13388392 set (autovacuum_vacuum_scale_factor=0.05);
An example for the autovacuum run :
2019-01-17 00:00:51 EST 15652 LOG: automatic vacuum of table
"db1.public.A": index scans: 1
pages: 0 removed, 117 remain
tuples: 142 removed, 1466 remain
buffer usage: 162 hits, 34 misses, 29 dirtied
avg read rate: 1.356 MiB/s, avg write rate: 1.157 MiB/s
--
2019-01-17 00:07:51 EST 25666 LOG: automatic vacuum of table
"db1.public.A": index scans: 1
pages: 0 removed, 117 remain
tuples: 144 removed, 1604 remain
buffer usage: 157 hits, 41 misses, 27 dirtied
avg read rate: 1.651 MiB/s, avg write rate: 1.087 MiB/s
--
*2019-01-17 00:12:39 EST 3902 LOG: automatic vacuum of table
"db1.pg_toast.pg_toast_13388392": index scans: 17*
* pages: 459 removed, 25973888 remain*
* tuples: 45130560 removed, 54081616 remain*
* buffer usage: 30060044 hits, 43418591 misses, 37034834 dirtied*
* avg read rate: 2.809 MiB/s, avg write rate: 2.396 MiB/s*
--
2019-01-17 00:13:51 EST 2684 LOG: automatic vacuum of table
"db1.public.A": index scans: 1
pages: 0 removed, 117 remain
tuples: 122 removed, 1470 remain
buffer usage: 152 hits, 41 misses, 30 dirtied
avg read rate: 2.981 MiB/s, avg write rate: 2.181 MiB/s
--
2019-01-17 00:19:51 EST 10935 LOG: automatic vacuum of table
"db1.public.A": index scans: 1
pages: 0 removed, 117 remain
tuples: 120 removed, 1471 remain
buffer usage: 145 hits, 41 misses, 28 dirtied
avg read rate: 3.637 MiB/s, avg write rate: 2.484 MiB/s
--
2019-01-17 00:42:51 EST 24385 LOG: automatic vacuum of table
"db1.public.A": index scans: 1
pages: 0 removed, 117 remain
tuples: 130 removed, 1402 remain
buffer usage: 175 hits, 76 misses, 34 dirtied
Any idea why the autovacuum doesnt vacuum both tables ?
