Re: Add LSN <-> time conversion functionality

On Thu, Aug 8, 2024 at 9:02 PM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
>
> On Thu, Aug 8, 2024 at 2:34 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
> >
> > Maybe it'd be good to approach this from the opposite direction, say
> > what "accuracy guarantees" we want to provide, and then design the
> > structure / algorithm to ensure that. Otherwise we may end up with an
> > infinite discussion about algorithms with unclear idea which one is the
> > best choice.
> >
> > And I'm sure "users" of the LSN/Timestamp mapping may get confused about
> > what to expect, without reasonably clear guarantees.
> >
> > For example, it seems to me a "good" accuracy guarantee would be:
> >
> > Given a LSN, the age of the returned timestamp is less than 10% off
> > the actual timestamp. The timestamp precision is in seconds.
> >
> > This means that if LSN was written 100 seconds ago, it would be OK to
> > get an answer in the 90-110 seconds range. For LSN from 1h ago, the
> > acceptable range would be 3600s +/- 360s. And so on. The 10% is just
> > arbitrary, maybe it should be lower - doesn't matter much.
>
> I changed this patch a bit to only provide ranges with an upper and
> lower bound from the SQL callable functions. While the size of the
> range provided could be part of our "accuracy guarantee", I'm not sure
> if we have to provide that.

Okay, so as I think about evaluating a few new algorithms, I realize
that we do need some sort of criteria. I started listing out what I
feel is "reasonable" accuracy and plotting it to see if the
relationship is linear/exponential/etc. I think it would help to get
input on what would be "reasonable" accuracy.

I thought that the following might be acceptable:
The first column is how old the value I am looking for actually is,
the second column is how off I am willing to have the algorithm tell
me it is (+/-):

1 second, 1 minute
1 minute, 10 minute
1 hour, 1 hour
1 day, 6 hours
1 week, 12 hours
1 month, 1 day
6 months, 1 week

Column 1 over column 2 produces a line like in the attached pic. I'd
be interested in others' opinions of error tolerance.

- Melanie