On Thu, Mar 28, 2019 at 6:32 PM David Rowley <david.row...@2ndquadrant.com> wrote: > > On Thu, 28 Mar 2019 at 22:04, Darafei "Komяpa" Praliaskouski > <m...@komzpa.net> wrote: > > > > On Thu, Mar 28, 2019 at 2:36 AM David Rowley <david.row...@2ndquadrant.com> > > wrote: > >> I thought recently that it would be good to have some sort of > >> pro-active auto-vacuum mode that made use of idle workers. > > > > Problem with "idle" is that it never happens on system that are going to > > wraparound on their lifetime. This has to be a part of normal database > > functioning. > > I'd say auto-vacuum is configured to run too slowly if you never have > an idle worker. The chances that it happens to be running at exactly > the right speed to keep up with demand must be about close to nil. > > > Why not select a table that has inserts, updates and deletes for autovacuum > > just like we do for autoanalyze, not only deletes and updates like we do > > now? > > Sounds like a good idea, although I do agree with Alvaro when he > mentions that it would be good to only invoke a worker that was only > going to freeze tuples and not look at the indexes.
The invoking autovacuum on table based on inserts, not only deletes and updates, seems good idea to me. But in this case, I think that we can not only freeze tuples but also update visibility map even when setting all-visible. Roughly speaking I think vacuum does the following operations. 1. heap vacuum 2. HOT pruning 3. freezing tuples 4. updating visibility map (all-visible and all-frozen) 5. index vacuum/cleanup 6. truncation With the proposed patch[1] we can control to do 5 or not. In addition to that, another proposed patch[2] allows us to control 6. For append-only tables (and similar tables), what we periodically want to do would be 3 and 4 (possibly we can do 2 as well). So maybe we need to have both an option of (auto)vacuum to control whether to do 1 and something like a new autovacuum threshold (or an option) to invoke the vacuum that disables 1, 5 and 6. The vacuum that does only 2, 3 and 4 would be much cheaper than today's vacuum and anti-wraparound vacuum would be able to skip almost pages. [1] https://commitfest.postgresql.org/22/1817/ [2] https://commitfest.postgresql.org/22/1981/ Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
