On Sun, Mar 31, 2019 at 1:11 AM Robert Haas <robertmh...@gmail.com> wrote: > > On Wed, Mar 27, 2019 at 5:32 PM Alvaro Herrera <alvhe...@2ndquadrant.com> > wrote: > > * certain tables would have some sort of partial scan that sets the > > visibility map. There's no reason to invoke the whole vacuuming > > machinery. I don't think this is limited to append-only tables, but > > rather those are just the ones that are affected the most. > > I think this is a really good idea, but in order for it to work well I > think we would need to have some kind of estimate of autovacuum > pressure. > > If we know that we're currently fairly on top of things, and there is > not much for autovacuum to do, periodically vacuuming a chunk of some > table that has a lot of unset visibility-map bits is probably a good > idea. However, we can't possibly guess how aggressively to do this if > we have no idea how long it's going to be before we need to vacuum > that table for real. If the number of XIDs remaining until the table > gets a wraparound vacuum is X, and the number of XIDs being consumed > per day is Y, we can estimate that in roughly X/Y days, we're going to > need to do a wraparound vacuum. That value might be in the range of > months, or in the range of hours. > > If it's months, we probably want limit vacuum to working at a pretty > slow rate, say 1% of the table size per hour or something. If it's in > hours, we need to be a lot more aggressive. Right now we have no > information to tell us which of those things is the case, so we'd just > be shooting in the dark.
Sawada-san presented some ideas in his PGCon 2018 talk that may be related. https://www.pgcon.org/2018/schedule/attachments/488_Vacuum_More_Efficient_Than_Ever (slide 32~) Thanks, Amit
