On Sun, Apr 14, 2019 at 9:59 PM Darafei "Komяpa" Praliaskouski <m...@komzpa.net> wrote: >> >> >> >I don't think it's helpful to force emergency vacuuming more >> >frequently; >> >quite the contrary, it's likely to cause even more issues. We should >> >tweak autovacuum to perform freezing more preemtively instead. >> >> I still think the fundamental issue with making vacuum less painful is that >> the all indexes have to be read entirely. Even if there's not much work (say >> millions of rows frozen, hundreds removed). Without that issue we could >> vacuum much more frequently. And do it properly in insert only workloads. > > > Deletion of hundreds of rows on default settings will cause the same behavior > now. > If there was 0 updates currently the index cleanup will be skipped. > > https://commitfest.postgresql.org/22/1817/ got merged. This means Autovacuum > can have two separate thresholds - the current, on dead tuples, triggering > the VACUUM same way it triggers it now, and a new one, on inserted tuples > only, triggering VACUUM (INDEX_CLEANUP FALSE)? >
Agreed. To invoke autovacuum even on insert-only tables we would need check the number of inserted tuples since last vacuum. I think we can keep track of the number of inserted tuples since last vacuum to the stats collector and add the threshold to invoke vacuum with INDEX_CLEANUP = false. If an autovacuum worker confirms that the number of inserted tuples exceeds the threshold it invokes vacuum with INDEX_CLEANUP = false. However if the number of dead tuples also exceeds the autovacuum thresholds (autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor) it should invoke vacuum with INDEX_CLEANUP = true. Therefore new threshold makes sense only when it's lower than the autovacuum thresholds. I guess we can have one new GUC parameter to control scale factor. Since only relatively large tables will require this feature we might not need the threshold based the number of tuples. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
