On Fri, Sep 24, 2021 at 7:44 PM Peter Geoghegan <p...@bowt.ie> wrote: > The scheduling of autovacuum is itself a big problem for the two big > BenchmarkSQL tables I'm always going on about -- though it did get a > lot better with the introduction of the > autovacuum_vacuum_insert_scale_factor stuff in Postgres 13. I recently > noticed that the tables have *every* autovacuum driven by inserts > (i.e. by the new autovacuum_vacuum_scale_factor stuff), and never by > updates -- even though updates obviously produce significant bloat in > the two tables. BenchmarkSQL on Postgres was far worse than it is now > a few releases ago [1], and I think that this stats business was a big > factor (on top of everything else). I can clearly see that > autovacuum_vacuum_scale_factor is certainly accidentally protective > with BenchmarkSQL today, in a way that wasn't particularly anticipated > by anybody.
> So if this was a real app, the DBA would > somehow have to work out that they should aggressively tune > autovacuum_vacuum_scale_factor to clean up bloat from updates. I doubt > any DBA could ever figure that out, because it doesn't make any sense. Correction: I meant that the autovacuum_vacuum_insert_scale_factor GUC is accidentally protective with the BenchmarkSQL tables, and that no DBA could be expected to figure this out. That is, it helps to lower autovacuum_vacuum_insert_scale_factor from its default of 0.20, just to get autovacuum to better handle bloat from *updates*. This has nothing to do with inserts, or with freeze or set VM bits -- and so overall it doesn't make any sense. -- Peter Geoghegan
