On Wed, 2019-02-06 at 12:29 +0200, Mariel Cherkassky wrote: > 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 : > > Now the question is how to handle or tune it ? Is there any change that I > need to increase the cost_limit / cost_delay ?
Maybe configuring autovacuum to run faster will help: alter table orig_table set (toast.autovacuum_vacuum_cost_limit = 2000); Or, more extreme: alter table orig_table set (toast.autovacuum_vacuum_cost_delay = 0); Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
