On Sat, Apr 6, 2019 at 9:56 AM Darafei "Komяpa" Praliaskouski <me@komzpa.net> wrote:
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.
[1] is committed, [2] nears commit. Seems we have now all the infra to teach autovacuum to run itself based on inserts and not hurt anybody?
Reading the thread and the patch, I generally agree that:
1. With the current infrastructure having auto vacuum periodically scan append-only tables for freezing would be good, and
2. I can't think of any cases where this would be a bad thing.
Also I am not 100% convinced that the problems are avoidable by setting the wraparound prevention thresholds low enough. In cases where one is doing large bulk inserts all the time, vacuum freeze could have a lot of work to do, and in some cases I could imagine IO storms making that difficult.
I plan to run some benchmarks on this to try to assess performance impact of this patch in standard pgbench scenarios.I will also try to come up with some other benchmarks in append only workloads.