On Sun, Apr 14, 2019 at 4:51 AM Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > > On Thu, Apr 11, 2019 at 11:25:29AM +0200, Chris Travers wrote: > > On Wed, Apr 10, 2019 at 5:21 PM Andres Freund <and...@anarazel.de> wrote: > > > > Hi, > > > > On April 10, 2019 8:13:06 AM PDT, Alvaro Herrera > > <alvhe...@2ndquadrant.com> wrote: > > >On 2019-Mar-31, Darafei "Komяpa" Praliaskouski wrote: > > > > > >> Alternative point of "if your database is super large and actively > > >written, > > >> you may want to set autovacuum_freeze_max_age to even smaller values > > >so > > >> that autovacuum load is more evenly spread over time" may be needed. > > > > > >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. > > > > So I see a couple of issues here and wondering what the best approach is. > > The first is to just skip lazy_cleanup_index if no rows were removed. Is > > this the approach you have in mind? Or is that insufficient? > > I don't think that's what Andres had in mind, as he explicitly mentioned > removed rows. So just skipping lazy_cleanup_index when there were no > deleted would not help in that case. > > What I think we could do is simply leave the tuple pointers in the table > (and indexes) when there are only very few of them, and only do the > expensive table/index cleanup once there's anough of them.
Yeah, we now have an infrastructure that skips index vacuuming by leaving the tuples pointers. So we then can have a threshold for autovacuum to invoke index vacuuming. Or an another idea is to delete index entries more actively by index looking up instead of scanning the whole index. It's proposed[1]. [1] I couldn't get the URL of the thread right now for some reason but the thread subject is " [WIP] [B-Tree] Retail IndexTuple deletion". Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
