On Sat, 7 Mar 2020 at 03:45, Laurenz Albe <laurenz.a...@cybertec.at> wrote: > > Thanks, Justin, for the review. > I have applied the changes where still applicable. > > On Fri, 2020-03-06 at 10:52 +1300, David Rowley wrote: > > Lack of a scale_factor does leave people who regularly truncate their > > "append-only" tables out in the cold a bit. Perhaps they'd like > > index-only scans to kick in soon after they truncate without having to > > wait for 10 million tuples, or so. > > That point I don't see. > Truncating a table resets the counters to 0.
The scenario there is that if we don't have any autovacuum_vacuum_insert_scale_factor and we set the threshold to 10 million tuples. The user truncates the table on a monthly basis and nearer to the end of the month the tuples accumulates around 100 million tuples, roughly 3.2 million are inserted per day, so auto-vacuum kicks in for this table around once every 3 days. At the start of the month, the table is truncated and it begins refilling. The n_ins_since_vacuum is reset to 0 during the truncate. Meanwhile, the table is being queried constantly and it takes 3 days for us to vacuum the table again. Queries hitting the table are unable to use Index Only Scans for 3 days. The DBAs don't have a lot of control over this. I think we can help users with that by giving them a bit more control over when auto-vacuum will run for the table. scale_factor and threshold. > Updated patch attached. Great. I'll have a look.
