On Wed, Sep 6, 2023 at 1:09 AM Andres Freund <and...@anarazel.de> wrote: > Yea, it'd be useful to have a reasonably approximate wall clock time for the > last modification of a page. We just don't have infrastructure for determining > that. We'd need an LSN->time mapping (xid->time wouldn't be particularly > useful, I think). > > A very rough approximate modification time can be computed by assuming an even > rate of WAL generation, and using the LSN at the time of the last vacuum and > the time of the last vacuum, to compute the approximate age. > > For a while I thought that'd not give us anything that just using LSNs gives > us, but I think it might allow coming up with a better cutoff logic: Instead > of using a cutoff like "page LSN is older than 10% of the LSNs since the last > vacuum of the table", it would allow us to approximate "page has not been > modified in the last 15 seconds" or such. I think that might help avoid > unnecessary freezing on tables with very frequent vacuuming.
Yes. I'm uncomfortable with the last-vacuum-LSN approach mostly because of the impact on very frequently vacuumed tables, and secondarily because of the impact on very infrequently vacuumed tables. Downthread, I proposed using the RedoRecPtr of the latest checkpoint rather than the LSN of the previou vacuum. I still like that idea. It's a value that we already have, with no additional bookkeeping. It varies over a much narrower range than the interval between vacuums on a table. The vacuum interval could be as short as tens of seconds as long as years, while the checkpoint interval is almost always going to be between a few minutes at the low end and some tens of minutes at the high end, hours at the very most. That's quite appealing. Also, I think the time between checkpoints actually matters here, because in some sense we're looking to get dirty, already-FPI'd pages frozen before they get written out, or before a new FPI becomes necessary, and checkpoints are one way for the first of those things to happen and the only way for the second one to happen. -- Robert Haas EDB: http://www.enterprisedb.com
