On Wed, Sep 27, 2023 at 12:34 PM Andres Freund <and...@anarazel.de> wrote: > What do you mean with "always freeze aggressively" - do you mean 'aggressive' > autovacuums? Or opportunistic freezing being aggressive? I don't know why the > former would be the case?
I meant the latter. > > When it grows large enough, we suddenly stop freezing it aggressively, and > > now it starts experiencing vacuums that do a whole bunch of work all at > > once. A user who notices that is likely to be pretty confused about what > > happened, and maybe not too happy when they find out. > > Hm - isn't my proposal exactly the other way round? I'm proposing that a page > is frozen more aggressively if not already in shared buffers - which will > become more common once the table has grown "large enough"? OK, but it's counterintuitive either way, IMHO. > I think there were three main ideas that we discussed: > > 1) We don't need to be accurate in the freezing decisions for individual > pages, we "just" need to avoid the situation that over time we commonly > freeze pages that will be updated again "soon". > 2) It might be valuable to adjust the "should freeze page opportunistically" > based on feedback. > 3) We might need to classify the workload for a table and use different > heruristics for different workloads. I agree with all of that. Good summary. > One way to deal with that would be to not track the average age in > LSN-difference-bytes, but convert the value to some age metric at that > time. If we e.g. were to convert the byte-age into an approximate age in > checkpoints, with quadratic bucketing (e.g. 0 -> current checkpoint, 1 -> 1 > checkpoint, 2 -> 2 checkpoints ago, 3 -> 4 checkpoints ago, ...), using a mean > of that age would probably be fine. Yes. I think it's possible that we could even get by with just two buckets. Say current checkpoint and not. Or current-or-previous checkpoint and not. And just look at what percentage of accesses fall into this first bucket -- it should be small or we're doing it wrong. It seems like the only thing we actually need to avoid is freezing the same ages over and over again in a tight loop. -- Robert Haas EDB: http://www.enterprisedb.com