Re: Add LSN <-> time conversion functionality - Mailing list pgsql-hackers

On 8/9/24 03:29, Melanie Plageman wrote:
> On Thu, Aug 8, 2024 at 3:00 PM Robert Haas <robertmhaas@gmail.com> wrote:
>>
>> On Thu, Aug 8, 2024 at 2:34 PM Tomas Vondra <tomas@vondra.me> wrote:
>>> A-D is already enough to cover 30h, with A-E it'd be ~300h. Do we need
>>> (or want) to keep a longer history?
>>
>> I think there is a difference of opinion about this between Melanie
>> and I. I feel like we should be designing something that does the
>> exact job we need done for the freezing stuff, and if anyone else can
>> use it, that's a bonus. For that, I feel that 300h is more than
>> plenty. The goal of the freezing stuff, roughly speaking, is to answer
>> the question "will this be unfrozen real soon?". "Real soon" could
>> arguably mean a minute or an hour, but I don't think it makes sense
>> for it to be a week. If we're freezing data now that has a good chance
>> of being unfrozen again within 7 days, we should just freeze it
>> anyway. The cost of freezing isn't really all that high. If we keep
>> freezing pages that are going to be unfrozen again within seconds or
>> minutes, we pay those freezing costs enough times that they become
>> material, but I have difficulty imagining that it ever matters if we
>> re-freeze the same page every week. It's OK to be wrong as long as we
>> aren't wrong too often, and I think that being wrong once per page per
>> week isn't too often.
>>
>> But I think Melanie was hoping to create something more general, which
>> on one level is understandable, but on the other hand it's unclear
>> what the goals are exactly. If we limit our scope to specifically
>> VACUUM, we can make reasonable guesses about how much precision we
>> need and for how long. But a hypothetical other client of this
>> infrastructure could need anything at all, which makes it very unclear
>> what the best design is, IMHO.
> 
> I'm fine with creating something that is optimized for use with
> freezing. I proposed this LSNTimeStream patch as a separate project
> because 1) Andres suggested it would be useful for other things 2) it
> would make the adaptive freezing project smaller if this goes in
> first. The adaptive freezing has two different fuzzy bits (this
> LSNTimeStream and then the accumulator which is used to determine if a
> page is older than most  pages which were unfrozen too soon). I was
> hoping to find an independent use for one of the fuzzy bits to move it
> forward.
> 
> But, I do think we should optimize the data thinning strategy for
> vacuum's adaptive freezing.
> 

+1 to this

IMHO if Andres thinks this would be useful for something else, it'd be
nice if he could explain what the other use cases are. Otherwise it's
not clear how to make it work for them.

The one other use case I can think of is monitoring - being able to look
at WAL throughput over time. Which seems OK, but it also can accept very
low resolution in distant past.

FWIW it still makes sense to do this as a separate patch, before the
main "freezing" one.


regards

-- 
Tomas Vondra