Postgres Pro
Downloads
Home   >   mailing lists

autovacuum doesnt run on the pg_toast_id table - Mailing list pgsql-performance

From Mariel Cherkassky
Subject autovacuum doesnt run on the pg_toast_id table
Date
Msg-id CA+t6e1nd6odb2pzSASzzsUx6pTmTQ7EExjFuyRkmVh8cdm8QFw@mail.gmail.com
Whole thread Raw
Responses Re: autovacuum doesnt run on the pg_toast_id table
Re: autovacuum doesnt run on the pg_toast_id table
List pgsql-performance
Tree view
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 ? 

pgsql-performance by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: No matching tables have ever been vacuumed
Next
From: Justin Pryzby
Date:
Subject: Re: autovacuum doesnt run on the pg_toast_id table