Subject: Eager page freeze criteria clarification

From: Melanie Plageman <[email-masked]>
Date: Fri, 28 Jul 2023 11:12:48 -0400

Hi,

While hacking on the pruning and page-level freezing code and am a bit
confused by the test guarding eager freezing  [1]:

    /*
     * Freeze the page when heap_prepare_freeze_tuple indicates that at least
     * one XID/MXID from before FreezeLimit/MultiXactCutoff is present.  Also
     * freeze when pruning generated an FPI, if doing so means that we set the
     * page all-frozen afterwards (might not happen until final heap pass).
     */
    if (pagefrz.freeze_required || tuples_frozen == 0 ||
        (prunestate->all_visible && prunestate->all_frozen &&
         fpi_before != pgWalUsage.wal_fpi))
    {

I'm trying to understand the condition fpi_before !=
pgWalUsage.wal_fpi -- don't eager freeze if pruning emitted an FPI.

Is this test meant to guard against unnecessary freezing or to avoid
freezing when the cost is too high? That is, are we trying to
determine how likely it is that the page has been recently modified
and avoid eager freezing when it would be pointless (because the page
will soon be modified again)? Or are we trying to determine how likely
the freeze record is to emit an FPI and avoid eager freezing when it
isn't worth the cost? Or something else?

The commit message says:

> Also teach VACUUM to trigger page-level freezing whenever it detects
> that heap pruning generated an FPI.  We'll have already written a large
> amount of WAL just to do that much, so it's very likely a good idea to
> get freezing out of the way for the page early.

And I found the thread where it was discussed [2]. Several possible
explanations are mentioned in the thread.

But, the final rationale is still not clear to me. Could we add a
comment above the if condition specifying both:
a) what the test is a proxy for
b) the intended outcome (when do we expect to eager freeze)
And perhaps we could even describe a scenario where this heuristic is effective?

- Melanie

[1] https://github.com/postgres/postgres/blob/master/src/backend/access/heap/vacuumlazy.c#L1802
[2] https://www.postgresql.org/message-id/flat/CAH2-Wzm_%3DmrWO%2BkUAJbR_gM_6RzpwVA8n8e4nh3dJGHdw_urew%40mail.gmail.com#c5aae6ea65e07de92a24a35445473448

---

From: Peter Geoghegan <[email-masked]>
Date: Fri, 28 Jul 2023 14:59:53 -0400

> pgWalUsage.wal_fpi -- don't eager freeze if pruning emitted an FPI.

You mean "prunestate->all_visible && prunestate->all_frozen", which is
a condition of applying FPI-based eager freezing, but not traditional
lazy freezing?

Obviously, the immediate impact of that is that the FPI trigger
condition is not met unless we know for sure that the page will be
marked all-visible and all-frozen in the visibility map afterwards. A
page that's eligible to become all-visible will also be seen as
eligible to become all-frozen in the vast majority of cases, but there
are some rare and obscure cases involving MultiXacts that must be
considered here. There is little point in freezing early unless we
have at least some chance of not having to freeze the same page again
in the future (ideally forever).

There is generally no point in freezing most of the tuples on a page
when there'll still be one or more not-yet-eligible unfrozen tuples
that get left behind -- we might as well not bother with freezing at
all when we see a page where that'll be the outcome from freezing.
However, there is (once again) a need to account for rare and extreme
cases -- it still needs to be possible to do that. Specifically, we
may be forced to freeze a page that's left with some remaining
unfrozen tuples when VACUUM is fundamentally incapable of freezing
them due to its OldestXmin/removable cutoff being too old. That can
happen when VACUUM needs to freeze according to the traditional
age-based settings, and yet the OldestXmin/removable cutoff gets held
back (by a leaked replication slot or whatever).

(Actually, VACUUM FREEZE freeze will freeze only a subset of the
tuples from some heap pages far more often. VACUUM FREEZE seems like a
bad design to me, though -- it uses the most aggressive possible XID
cutoff for freezing when it should probably hold off on freezing those
individual pages where we determine that it makes little sense. We
need to focus more on physical pages and their costs, and less on XID
cutoffs.)

> will soon be modified again)?

Sort of. This cost of freezing over time is weirdly nonlinear, so it's
hard to give a simple answer.

The justification for the FPI trigger optimization is that FPIs are
overwhelmingly the cost that really matters when it comes to freezing
(and vacuuming in general) -- so we might as well make the best out of
a bad situation when pruning happens to get an FPI. There can easily
be a 10x or more cost difference (measured in total WAL volume)
between freezing without an FPI and freezing with an FPI.

> Or are we trying to determine how likely
> isn't worth the cost?

No, that's not something that we're doing right now (we just talked
about doing something like that). In 16 VACUUM just "makes the best
out of a bad situation" when an FPI was already required during
pruning. We have already "paid for the privilege" of writing some WAL
for the page at that point, so it's reasonable to not squander a
window of opportunity to avoid future FPIs in future VACUUM
operations, by freezing early.

We're "taking a chance" on being able to get freezing out of the way
early when an FPI triggers freezing. It's not guaranteed to work out
in each individual case, of course, but even if we assume it's fairly
unlikely to work out (which is very pessimistic) it's still very
likely a good deal.

This strategy (the 16 strategy of freezing eagerly because we already
got an FPI) seems safer than a strategy involving freezing eagerly
because we won't get an FPI as a result. If for no other reason than
this: with the approach in 16 we already know for sure that we'll have
written an FPI anyway. It's hard to imagine somebody being okay with
the FPIs, but not being okay with the other extra WAL.

> And perhaps we could even describe a scenario where this heuristic is effective?

There are lots of scenarios where it'll be effective. I agree that
there is a need to document this stuff a lot better. I have a pending
doc patch that overhauls the user-facing docs in this area.

My latest draft is here:

https://postgr.es/m/CAH2-Wz=UUJz+MMb1AxFzz-HDA=[email-masked]
https://www.postgresql.org/message-id/attachment/146830/routine-vacuuming.html

I've been meaning to get back to that, but other commitments have kept
me from it. I'd welcome your involvement with that effort.

--
Peter Geoghegan

---

From: Melanie Plageman <[email-masked]>
Date: Fri, 28 Jul 2023 15:27:11 -0400

> a condition of applying FPI-based eager freezing, but not traditional

Ah no, a very key word in my sentence was off:
I meant "don't eager freeze *unless* pruning emitted an FPI"
I was asking about the FPI-trigger optimization specifically.
I'm clear on the all_frozen/all_visible criteria.

> between freezing without an FPI and freezing with an FPI.
...
> In 16 VACUUM just "makes the best

I see. I don't have an opinion on the "best of a bad situation"
argument. Though, I think it is worth amending the comment in the code
to include this explanation.

But, ISTM that there should also be some independent heuristic to
determine whether or not it makes sense to freeze the page. That could
be related to whether or not it will be cheap to do so (in which case
we can check if we will have to emit an FPI as part of the freeze
record) or it could be related to whether or not the freezing is
likely to be pointless (we are likely to update the page again soon).

It sounds like it was discussed before, but I'd be interested in
revisiting it and happy to test out various ideas.

- Melanie

---

From: Peter Geoghegan <[email-masked]>
Date: Fri, 28 Jul 2023 15:45:01 -0400

> to include this explanation.

I think that the user-facing docs should be completely overhauled to
make that clear, and reasonably accessible. It's hard to talk about in
code comments because it's something that can only be effective over
time, across multiple VACUUM operations.

> likely to be pointless (we are likely to update the page again soon).

It sounds like you're interested in adding additional criteria to
trigger page-level freezing. That's something that the current
structure anticipates.

To give a slightly contrived example: it would be very easy to add
another condition, where (say) we call random() to determine whether
or not to freeze the page. You'd very likely want to gate this new
trigger criteria in the same way as the FPI criteria (i.e. only do it
when "prunestate->all_visible && prunestate->all_frozen" hold). We've
decoupled the decision to freeze from what it means to execute
freezing itself.

Having additional trigger criteria makes a lot of sense. I'm sure that
it makes sense to add at least one more, and it seems possible that
adding several more makes sense. Obviously, that will have to be new
work targeting 17, though. I made a decision to stop working on
VACUUM, though, so I'm afraid I won't be able to offer much help with
any of this. (Happy to give more background information, though.)

-- 
Peter Geoghegan

---

From: Melanie Plageman <[email-masked]>
Date: Mon, 28 Aug 2023 10:00:14 -0400

> revisiting it and happy to test out various ideas.

Hi, in service of "testing various ideas", I've benchmarked the
heuristics proposed in this thread, as well a few others that Andres and
I considered, for determining whether or not to opportunistically freeze
a page during vacuum. Note that this heuristic would be in addition to
the existing criterion that we only opportunistically freeze pages that
can be subsequently set all frozen in the visibility map.

I believe that there are two goals that should dictate whether or not we
should perform opportunistic freezing:

  1. Is it cheap? For example, if the buffer is already dirty, then no
  write amplification occurs, since it must be written out anyway.
  Freezing is also less expensive if we can do it without emitting an
  FPI.

  2. Will it be effective; that is, will it stay frozen?
  Opportunistically freezing a page that will immediately be modified is
  a waste.

The current heuristic on master meets neither of these goals: it freezes
a page if pruning emitted an FPI for it. This doesn't evaluate whether
or not freezing itself would be cheap, but rather attempts to hide
freezing behind an expensive operation. Furthermore, it often fails to
freeze cold data and may indiscriminately freeze hot data.

For the second goal, I've relied on past data to predict future
behavior, so I tried several criteria to estimate the likelihood that a
page will not be imminently modified. What was most effective was
Andres' suggestion of comparing the page LSN to the insert LSN at the
end of the last vacuum of that table; this approximates whether the page
has been recently modified, which is a decent proxy for whether it'll be
modified in the future. To do this, we need to save that insert LSN
somewhere. In the attached WIP patch, I saved it in the table stats, for
now -- knowing that those are not crash-safe.

Other discarded heuristic ideas included comparing the next transaction
ID at the end of the vacuum of a relation to the visibility cutoff xid
in the page -- but that wasn't effective for freezing data from bulk
loads.

The algorithms I evaluated all attempt to satisfy goal (1) by freezing
only if the buffer is already dirty and also by considering whether or
not an FPI would be emitted. Those that attempt to satisfy goal (2) do
so using the LSN comparison with varying thresholds. I ended up testing
master and the following five alternatives:

  1. Dirty buffer, no FPI required

  2. Dirty buffer, no FPI required OR page LSN is older than 10% of the
  LSNs since the last vacuum of the table.

  3. Dirty buffer, no FPI required AND page LSN is older than 10% of the
  LSNs since the last vacuum of the table.

  4. Dirty buffer, no FPI required OR page LSN is older than 33% of the
  LSNs since the last vacuum of the table.

  5. Dirty buffer, no FPI required AND page LSN is older than 33% of the
  LSNs since the last vacuum of the table.

I ran several benchmarks and compared these based on two metrics:

  1. Percentage of pages frozen at the end of the benchmark. For
  workloads with a working set much smaller than their data set, this
  metric should be high. Conversely, for workloads with a working set
  that is more-or-less their entire data set, this metric should be low.

  2. Page freezes per page frozen at the end of the benchmark. This
  should be as low as possible. Since each benchmark starts with zero
  frozen pages, a metric of 1 indicates that each frozen page was frozen
  only once.

Some of the benchmarks were run for a fixed number of transactions.
Those that do not specify a number of transactions were run for 45
minutes. I collected metrics from OS utilities and Postgres statistics
to examine throughput, FPIs emitted, and many other performance metrics
over the course of the benchmark. Below, I've summarized the results and
pointed out any notable negative performance impacts.

Overall, the two algorithms that seem to strike the best balance are (4)
and (5).

The OR condition in algorithm (4), as you might expect, results in
freezing much more of the cold data in workloads with a smaller working
set than data set. It tends to cause more FPIs to be emitted, since the
age criteria alone can trigger freezing -- even when the freeze record
would contain an FPI. Though, these FPIs may happen when the cold data
is eventually frozen in a wraparound vacuum. That is, the absence of
FPIs tracks the absence of frozen data quite closely.

For a workload in which only 10% of the data is being updated, master
often freezes the wrong data and still emits FPIs. For this kind of
workload, algorithms 1 and 2 also did not perform well and emitted more
FPIs than the other algorithms. In my examples, I found that the 10%
cutoff froze data too aggressively -- freezing data that was modified
soon after.

The workloads I benchmarked were as follows:

A. gaussian tpcb-like + select-only:
   pgbench scale 600 (DB < SB) with indexes on updated columns
   WL 1: 2 clients tpcb-like pgbench with gaussian access distribution
   WL 2: 16 clients select-only pgbench
   freezing more is better

B. tpcb-like
   pgbench scale 600, 16 clients
   freezing less is better

C. shifting hot set, autovacuum off, vacuum at end
   1 client inserting a single row and updating an indexed column of that
   row. 2 million transactions.
   freezing more is better

D. shifting hot set, delete old data
   10 MB table with index on updated column
   WL 1: 1 client inserting one row, updating that row
   WL 2: 1 client, rate limited to 0.02 TPS, delete old data keeping
   table at 5000 rows
   freezing less is better

E. shifting hot set, delete new data, access new data
   WL 1: 1 client cycling through 2 inserts of a single row each,
   updating an indexed column of the most recently inserted row, and then
   deleting that row
   WL 2: rate-limited to 0.2 TPS, selecting data from the last 300
   seconds
   freezing more is better

F. many COPYs, autovacuum on
   1 client, copying a total of 50 GB of data, autovacuum will run ~2x,
   ~2 checkpoints
   freezing more is better

G. several COPYs, autovacuum off, vacuum at end
   1 client, copying a total of 10 GB of data, no checkpoints
   freezing more is better

H. append only table, autovacuum off, vacuum at end
   1 client, inserting a single row at a time for 3million transactions
   freezing more is better

I. work queue
   1 client, inserting a row, sleep for half a second, delete that row
   for 5000 transactions.
   freezing less is better

Note that the page freezes/page frozen metric can be misleading when the
overall number of pages freezes is low. This is the case for master. It
did few page freezes but those tended to be pages that were modified
again soon after.

           Page Freezes/Page Frozen (less is better)

|   | Master |     (1) |     (2) |     (3) |     (4) |     (5) |
|---+--------+---------+---------+---------+---------+---------|
| A |  28.50 |    3.89 |    1.08 |    1.15 |    1.10 |    1.10 |
| B |   1.00 |    1.06 |    1.65 |    1.03 |    1.59 |    1.00 |
| C |    N/A |    1.00 |    1.00 |    1.00 |    1.00 |    1.00 |
| D |   2.00 | 5199.15 | 5276.85 | 4830.45 | 5234.55 | 2193.55 |
| E |   7.90 |    3.21 |    2.73 |    2.70 |    2.69 |    2.43 |
| F |    N/A |    1.00 |    1.00 |    1.00 |    1.00 |    1.00 |
| G |    N/A |    1.00 |    1.00 |    1.00 |    1.00 |    1.00 |
| H |    N/A |    1.00 |    1.00 |    1.00 |    1.00 |    1.00 |
| I |    N/A |   42.00 |   42.00 |     N/A |   41.00 |     N/A |

           % Frozen at end of run

|   | Master | (1) | (2) | (3) |  (4) | (5) |
|---+--------+-----+-----+-----+------+-----+
| A |      0 |   1 |  99 |   0 |   81 |   0 |
| B |     71 |  96 |  99 |   3 |   98 |   2 |
| C |      0 |   9 | 100 |   6 |   92 |   5 |
| D |      0 |   1 |   1 |   1 |    1 |   1 |
| E |      0 |  63 | 100 |  68 |  100 |  67 |
| F |      0 |   5 |  14 |   6 |   14 |   5 |
| G |      0 | 100 | 100 |  92 |  100 |  67 |
| H |      0 |  11 | 100 |   9 |   86 |   5 |
| I |      0 | 100 | 100 |   0 |  100 |   0 |

I can provide exact pgbench commands, configurations, or detailed
results upon request.

- Melanie

---

From: Robert Haas <[email-masked]>
Date: Mon, 28 Aug 2023 12:26:01 -0400

> now -- knowing that those are not crash-safe.

I wonder what the real plan here is for where to store this. It's not
obvious that we need this to be crash-safe; it's after all only for
use by a heuristic, and there's no actual breakage if the heuristic
goes wrong. At the same time, it doesn't exactly feel like a
statistic.

Then there's the question of whether it's the right metric. My first
reaction is to think that it sounds pretty good. One thing I really
like about it is that if the table is being vacuumed frequently, then
we freeze less aggressively, and if the table is being vacuumed
infrequently, then we freeze more aggressively. That seems like a very
desirable property. It also seems broadly good that this metric
doesn't really care about reads. If there are a lot of reads on the
system, or no reads at all, it doesn't really change the chances that
a certain page is going to be written again soon, and since reads
don't change the insert LSN, here again it seems to do the right
thing. I'm a little less clear about whether it's good that it doesn't
really depend on wall-clock time. Certainly, that's desirable from the
point of view of not wanting to have to measure wall-clock time in
places where we otherwise wouldn't have to, which tends to end up
being expensive. However, if I were making all of my freezing
decisions manually, I might be more freeze-positive on a low-velocity
system where writes are more stretched out across time than on a
high-velocity system where we're blasting through the LSN space at a
higher rate. But maybe that's not a very important consideration, and
I don't know what we'd do about it anyway.

> |   | Master |     (1) |     (2) |     (3) |     (4) |     (5) |

Hmm. I would say that the interesting rows here are A, D, and I, with
rows C and E deserving honorable mention. In row A, master is bad. In
row D, your algorithms are all bad, really bad. I don't quite
understand how it can be that bad, actually. Row I looks bad for
algorithms 1, 2, and 4: they freeze pages because it looks cheap, but
the work doesn't really pay off.

> |   | Master | (1) | (2) | (3) |  (4) | (5) |

So all of the algorithms here, but especially 1, 2, and 4, freeze a
lot more often than master.

If I understand correctly, we'd like to see small numbers for B, D,
and I, and large numbers for the other workloads. None of the
algorithms seem to achieve that. (3) and (5) seem like they always
behave as well or better than master, but they produce small numbers
for A, C, F, and H. (1), (2), and (4) regress B and I relative to
master but do better than (3) and (5) on A, C, and the latter two also
on E.

B is such an important benchmarking workload that I'd be loathe to
regress it, so if I had to pick on the basis of this data, my vote
would be (3) or (5), provided whatever is happening with (D) in the
previous metric is not as bad as it looks. What's your reason for
preferring (4) and (5) over (2) and (3)? I'm not clear that these
numbers give us much of an idea whether 10% or 33% or something else
is better in general.

To be honest, having now spent more time looking at the benchmark
results, I feel slightly less good about using the LSN as a metric
here. These results, to me, clearly suggest that some recency metric
is needed. But they don't seem to make a compelling case for this
particular one. Neither do they make a case that this is the wrong
one. They just don't seem that revealing either way.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 28 Aug 2023 12:09:32 -0700

On Mon, Aug 28, 2023 at 7:00 AM Melanie Plageman
<[email-masked]> wrote:
> To do this, we need to save that insert LSN

What patch? You didn't actually attach one.

> in the page -- but that wasn't effective for freezing data from bulk

I've long emphasized the importance of designs that just try to avoid
disaster. With that in mind, I wonder: have you thought about
conditioning page freezing on whether or not there are already some
frozen tuples on the page? You could perhaps give some weight to
whether or not the page already has at least one or two preexisting
frozen tuples when deciding on whether to freeze it once again now.
You'd be more eager about freezing pages that have no frozen tuples
whatsoever, compared to what you'd do with an otherwise equivalent
page that has no unfrozen tuples.

Small mistakes are inevitable here. They have to be okay. What's not
okay is a small mistake that effectively becomes a big mistake because
it gets repeated across each VACUUM operation, again and again,
ceaselessly. You can probably be quite aggressive about freezing, to
good effect -- provided you can be sure that the downside when it
turns out to have been a bad idea is self-limiting, in whatever way.
Making more small mistakes might actually be a good thing --
especially if it can dramatically lower the chances of ever making any
really big mistakes.

VACUUM is not a passive observer of the system. It's just another
component in the system. So what VACUUM sees in the table depends in
no small part on what previous VACUUMs actually did. It follows that
VACUUM should be concerned about how what it does might either help or
hinder future VACUUMs. My preexisting frozen tuples suggestion is
really just an example of how that principle might be applied. Many
variations on the same general idea are possible.

There are already various extremely weird feedback loops where what
VACUUM did last time affects what it'll do this time. These are
vicious circles. So ISTM that there is a lot to be said for disrupting
those vicious circles, and even deliberately engineering heuristics
that have the potential to create virtuous circles for the right
workload.

-- 
Peter Geoghegan

---

From: Robert Haas <[email-masked]>
Date: Mon, 28 Aug 2023 16:17:22 -0400

> page that has no unfrozen tuples.

I'm sure this could be implemented, but it's unclear to me why you
would expect it to perform well. Freezing a page that has no frozen
tuples yet isn't cheaper than freezing one that does, so for this idea
to be a win, the presence of frozen tuples on the page would have to
be a signal that the page is likely to be modified again in the near
future. In general, I don't see any reason why we should expect that
to be the case. One could easily construct a workload where it is the
case -- for instance, set up one table T1 where 90% of the tuples are
repeatedly updated and the other 10% are never touched, and another
table T2 that is insert-only. Once frozen, the never-updated tuples in
T1 become sentinels that we can use to know that the table isn't
insert-only. But I don't think that's very interesting: you can
construct a test case like this for any proposed criterion, just by
structuring the test workload so that whatever criterion is being
tested is a perfect predictor of whether the page will be modified
soon.

What really matters here is finding a criterion that is likely to
perform well in general, on a test case not known to us beforehand.
This isn't an entirely feasible goal, because just as you can
construct a test case where any given criterion performs well, so you
can also construct one where any given criterion performs poorly. But
I think a rule that has a clear theory of operation must be preferable
to one that doesn't. The theory that Melanie and Andres are advancing
is that a page that has been modified recently (in insert-LSN-time) is
more likely to be modified again soon than one that has not i.e. the
near future will be like the recent past. I'm not sure what the theory
behind the rule you propose here might be; if you articulated it
somewhere in your email, I seem to have missed it.

---

From: Melanie Plageman <[email-masked]>
Date: Mon, 28 Aug 2023 16:30:15 -0400

> I don't know what we'd do about it anyway.

By low-velocity, do you mean lower overall TPS? In that case, wouldn't you be
less likely to run into xid wraparound and thus need less aggressive
opportunistic freezing?

> rows C and E deserving honorable mention. In row A, master is bad.

So, this is where the caveat about absolute number of page freezes
matters. In algorithm A, master only did 57 page freezes (spread across
the various pgbench tables). At the end of the run, 2 pages were still
frozen.

> In row D, your algorithms are all bad, really bad. I don't quite
> understand how it can be that bad, actually.

So, I realize now that this test was poorly designed. I meant it to be a
worst case scenario, but I think one critical part was wrong. In this
example one client is going at full speed inserting a row and then
updating it. Then another rate-limited client is deleting old data
periodically to keep the table at a constant size. I meant to bulk load
the table with enough data that the delete job would have data to delete
from the start. With the default autovacuum settings, over the course of
45 minutes, I usually saw around 40 autovacuums of the table. Due to the
rate limiting, the first autovacuum of the table ends up freezing many
pages that are deleted soon after. Thus the total number of page freezes
is very high.

I will redo benchmarking of workload D and start the table with the
number of rows which the DELETE job seeks to maintain. My back of the
envelope math says that this will mean ratios closer to a dozen (and not
5000).

Also, I had doubled checkpoint timeout, which likely led master to
freeze so few pages (2 total freezes, neither of which were still frozen
at the end of the run). This is an example where master's overall low
number of page freezes makes it difficult to compare to the alternatives
using a ratio.

I didn't initially question the numbers because it seems like freezing
data and then deleting it right after would naturally be one of the
worst cases for opportunistic freezing, but certainly not this bad.

> Row I looks bad for algorithms 1, 2, and 4: they freeze pages because
> it looks cheap, but the work doesn't really pay off.

Yes, the work queue example looks like it is hard to handle.

> numbers give us much of an idea whether 10% or 33% or something else

(1) seems bad to me because it doesn't consider whether or not freezing
will be useful -- only if it will be cheap. It froze very little of the
cold data in a workload where a small percentage of it was being
modified (especially workloads A, C, H). And it froze a lot of data in
workloads where it was being uniformly modified (workload B).

I suggested (4) and (5) because I think the "older than 33%" threshold
is better than the "older than 10%" threshold. I chose both because I am
still unclear on our values. Are we willing to freeze more aggressively
at the expense of emitting more FPIs? As long as it doesn't affect
throughput? For pretty much all of these workloads, the algorithms which
froze based on page modification recency OR FPI required emitted many
more FPIs than those which froze based only on page modification
recency.

I've attached the WIP patch that I forgot in my previous email.

I'll rerun workload D in a more reasonable way and be back with results.

- Melanie
From 51786ef91cba43bbb4985dc8ab86f2cdcbf54369 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <[email-masked]>
Date: Sat, 26 Aug 2023 20:16:00 -0400
Subject: [PATCH v1 1/3] Opportunistically freeze cold data when it is cheap

Instead of relying on whether or not pruning emitted an FPI while
vacuuming to determine whether or not to opportunistically freeze,
determine whether or not doing so will be cheap and useful. Determine if
it is cheap by checking if the buffer is already dirty and if freezing
the page will require emitting an FPI. Then, predict if the page will
stay frozen by comparing the page LSN to the insert LSN at the end of
the last vacuum of the relation. This gives us some idea of whether or
not the page has been recently modified, and, hopefully insight into
whether or not it will soon be modified again.
---
 src/backend/access/heap/vacuumlazy.c         | 26 ++++++++++++++----
 src/backend/storage/buffer/bufmgr.c          | 19 +++++++++++++
 src/backend/storage/buffer/localbuf.c        | 20 ++++++++++++++
 src/backend/utils/activity/pgstat_relation.c | 29 +++++++++++++++++++-
 src/include/pgstat.h                         |  8 +++++-
 src/include/storage/buf_internals.h          |  2 ++
 src/include/storage/bufmgr.h                 |  2 ++
 7 files changed, 98 insertions(+), 8 deletions(-)

diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 6a41ee635d..e4d5b402c2 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -210,6 +210,7 @@ typedef struct LVRelState
 	int64		live_tuples;	/* # live tuples remaining */
 	int64		recently_dead_tuples;	/* # dead, but not yet removable */
 	int64		missed_dead_tuples; /* # removable, but not removed */
+	XLogRecPtr last_vac_lsn;
 } LVRelState;
 
 /*
@@ -364,6 +365,9 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 	errcallback.previous = error_context_stack;
 	error_context_stack = &errcallback;
 
+	vacrel->last_vac_lsn = pgstat_get_last_vac_lsn(RelationGetRelid(rel),
+			rel->rd_rel->relisshared);
+
 	/* Set up high level stuff about rel and its indexes */
 	vacrel->rel = rel;
 	vac_open_indexes(vacrel->rel, RowExclusiveLock, &vacrel->nindexes,
@@ -597,7 +601,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 						 rel->rd_rel->relisshared,
 						 Max(vacrel->new_live_tuples, 0),
 						 vacrel->recently_dead_tuples +
-						 vacrel->missed_dead_tuples);
+						 vacrel->missed_dead_tuples, ProcLastRecPtr);
+
 	pgstat_progress_end_command();
 
 	if (instrument)
@@ -1511,6 +1516,7 @@ lazy_scan_new_or_empty(LVRelState *vacrel, Buffer buf, BlockNumber blkno,
 	return false;
 }
 
+#define FREEZE_CUTOFF_DIVISOR 3
 /*
  *	lazy_scan_prune() -- lazy_scan_heap() pruning and freezing.
  *
@@ -1551,9 +1557,11 @@ lazy_scan_prune(LVRelState *vacrel,
 				recently_dead_tuples;
 	int			nnewlpdead;
 	HeapPageFreeze pagefrz;
-	int64		fpi_before = pgWalUsage.wal_fpi;
 	OffsetNumber deadoffsets[MaxHeapTuplesPerPage];
 	HeapTupleFreeze frozen[MaxHeapTuplesPerPage];
+	XLogRecPtr current_lsn;
+	XLogRecPtr lsns_since_last_vacuum;
+	XLogRecPtr freeze_lsn_cutoff;
 
 	Assert(BufferGetBlockNumber(buf) == blkno);
 
@@ -1795,13 +1803,19 @@ retry:
 
 	/*
 	 * Freeze the page when heap_prepare_freeze_tuple indicates that at least
-	 * one XID/MXID from before FreezeLimit/MultiXactCutoff is present.  Also
-	 * freeze when pruning generated an FPI, if doing so means that we set the
-	 * page all-frozen afterwards (might not happen until final heap pass).
+	 * one XID/MXID from before FreezeLimit/MultiXactCutoff is present.  Also,
+	 * if freezing would allow us to set the whole page all-frozen, then freeze
+	 * if the buffer is already dirty, freezing won't emit an FPI and the page
+	 * hasn't been modified recently.
 	 */
+	current_lsn = GetXLogInsertRecPtr();
+	lsns_since_last_vacuum = current_lsn - vacrel->last_vac_lsn;
+	freeze_lsn_cutoff = current_lsn - (lsns_since_last_vacuum / FREEZE_CUTOFF_DIVISOR);
+
 	if (pagefrz.freeze_required || tuples_frozen == 0 ||
 		(prunestate->all_visible && prunestate->all_frozen &&
-		 fpi_before != pgWalUsage.wal_fpi))
+		(BufferIsProbablyDirty(buf) && !XLogCheckBufferNeedsBackup(buf) &&
+		PageGetLSN(page) < freeze_lsn_cutoff)))
 	{
 		/*
 		 * We're freezing the page.  Our final NewRelfrozenXid doesn't need to
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 3bd82dbfca..9110b686ae 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -2098,6 +2098,25 @@ ExtendBufferedRelShared(BufferManagerRelation bmr,
 	return first_block;
 }
 
+bool
+BufferIsProbablyDirty(Buffer buffer)
+{
+	BufferDesc *bufHdr;
+	uint32		buf_state;
+
+	if (!BufferIsValid(buffer))
+		elog(ERROR, "bad buffer ID: %d", buffer);
+
+	if (BufferIsLocal(buffer))
+		return LocalBufferIsProbablyDirty(buffer);
+
+	bufHdr = GetBufferDescriptor(buffer - 1);
+
+	buf_state = pg_atomic_read_u32(&bufHdr->state);
+
+	return buf_state & BM_DIRTY;
+}
+
 /*
  * MarkBufferDirty
  *
diff --git a/src/backend/storage/buffer/localbuf.c b/src/backend/storage/buffer/localbuf.c
index 1735ec7141..f2d4f91336 100644
--- a/src/backend/storage/buffer/localbuf.c
+++ b/src/backend/storage/buffer/localbuf.c
@@ -436,6 +436,26 @@ ExtendBufferedRelLocal(BufferManagerRelation bmr,
 	return first_block;
 }
 
+bool
+LocalBufferIsProbablyDirty(Buffer buffer)
+{
+	int			bufid;
+	BufferDesc *bufHdr;
+	uint32		buf_state;
+
+	Assert(BufferIsLocal(buffer));
+
+	bufid = -buffer - 1;
+
+	Assert(LocalRefCount[bufid] > 0);
+
+	bufHdr = GetLocalBufferDescriptor(bufid);
+
+	buf_state = pg_atomic_read_u32(&bufHdr->state);
+
+	return buf_state & BM_DIRTY;
+}
+
 /*
  * MarkLocalBufferDirty -
  *	  mark a local buffer dirty
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 9876e0c1e8..cac0a4bc49 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -205,12 +205,37 @@ pgstat_drop_relation(Relation rel)
 	}
 }
 
+
+XLogRecPtr
+pgstat_get_last_vac_lsn(Oid tableoid, bool shared)
+{
+	PgStat_EntryRef *entry_ref;
+	PgStatShared_Relation *shtabentry;
+	PgStat_StatTabEntry *tabentry;
+	XLogRecPtr result;
+	Oid			dboid = (shared ? InvalidOid : MyDatabaseId);
+
+	if (!pgstat_track_counts)
+		return InvalidXLogRecPtr;
+
+	entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
+											dboid, tableoid, false);
+
+	shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
+	tabentry = &shtabentry->stats;
+
+	result = tabentry->last_vac_lsn;
+	pgstat_unlock_entry(entry_ref);
+	return result;
+}
+
 /*
  * Report that the table was just vacuumed and flush IO statistics.
  */
 void
 pgstat_report_vacuum(Oid tableoid, bool shared,
-					 PgStat_Counter livetuples, PgStat_Counter deadtuples)
+					 PgStat_Counter livetuples, PgStat_Counter deadtuples,
+					 XLogRecPtr last_vac_lsn)
 {
 	PgStat_EntryRef *entry_ref;
 	PgStatShared_Relation *shtabentry;
@@ -257,6 +282,8 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
 		tabentry->vacuum_count++;
 	}
 
+	tabentry->last_vac_lsn = last_vac_lsn;
+
 	pgstat_unlock_entry(entry_ref);
 
 	/*
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 57a2c0866a..9fe607c961 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -11,6 +11,7 @@
 #ifndef PGSTAT_H
 #define PGSTAT_H
 
+#include "access/xlogdefs.h"
 #include "datatype/timestamp.h"
 #include "portability/instr_time.h"
 #include "postmaster/pgarch.h"	/* for MAX_XFN_CHARS */
@@ -424,6 +425,7 @@ typedef struct PgStat_StatTabEntry
 	PgStat_Counter analyze_count;
 	TimestampTz last_autoanalyze_time;	/* autovacuum initiated */
 	PgStat_Counter autoanalyze_count;
+	XLogRecPtr last_vac_lsn;
 } PgStat_StatTabEntry;
 
 typedef struct PgStat_WalStats
@@ -589,7 +591,11 @@ extern void pgstat_assoc_relation(Relation rel);
 extern void pgstat_unlink_relation(Relation rel);
 
 extern void pgstat_report_vacuum(Oid tableoid, bool shared,
-								 PgStat_Counter livetuples, PgStat_Counter deadtuples);
+								 PgStat_Counter livetuples, PgStat_Counter deadtuples,
+								 XLogRecPtr last_vac_lsn);
+
+extern XLogRecPtr pgstat_get_last_vac_lsn(Oid tableoid, bool shared);
+
 extern void pgstat_report_analyze(Relation rel,
 								  PgStat_Counter livetuples, PgStat_Counter deadtuples,
 								  bool resetcounter);
diff --git a/src/include/storage/buf_internals.h b/src/include/storage/buf_internals.h
index bc79a329a1..aaa7882099 100644
--- a/src/include/storage/buf_internals.h
+++ b/src/include/storage/buf_internals.h
@@ -430,6 +430,8 @@ extern BlockNumber ExtendBufferedRelLocal(BufferManagerRelation bmr,
 										  BlockNumber extend_upto,
 										  Buffer *buffers,
 										  uint32 *extended_by);
+
+extern bool LocalBufferIsProbablyDirty(Buffer buffer);
 extern void MarkLocalBufferDirty(Buffer buffer);
 extern void DropRelationLocalBuffers(RelFileLocator rlocator,
 									 ForkNumber forkNum,
diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h
index b379c76e27..7458d5fd83 100644
--- a/src/include/storage/bufmgr.h
+++ b/src/include/storage/bufmgr.h
@@ -180,6 +180,8 @@ extern Buffer ReadBufferWithoutRelcache(RelFileLocator rlocator,
 extern void ReleaseBuffer(Buffer buffer);
 extern void UnlockReleaseBuffer(Buffer buffer);
 extern void MarkBufferDirty(Buffer buffer);
+
+extern bool BufferIsProbablyDirty(Buffer buffer);
 extern void IncrBufferRefCount(Buffer buffer);
 extern void CheckBufferIsPinnedOnce(Buffer buffer);
 extern Buffer ReleaseAndReadBuffer(Buffer buffer, Relation relation,
-- 
2.37.2

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 28 Aug 2023 14:05:57 -0700

On Mon, Aug 28, 2023 at 1:17 PM Robert Haas <[email-masked]> wrote:
> to be the case.

What I've described in a scheme for deciding to not freeze where that
would usually happen -- a scheme for *vetoing* page freezing -- rather
than a scheme for deciding to freeze. On its own, what I suggested
cannot help at all. It assumes a world in which we're already deciding
to freeze much more frequently, based on whatever other criteria. It's
intended to complement something like the LSN scheme.

> near future will be like the recent past.

I don't think that it's all that useful on its own. You just cannot
ignore the fact that the choice to not freeze now doesn't necessarily
mean that you get to rereview that choice in the near future.
Particularly with large tables, the opportunities to freeze at all are
few and far between -- if for no other reason than the general design
of autovacuum scheduling. Worse still, any unfrozen all-visible pages
can just accumulate as all-visible pages, until the next aggressive
VACUUM happens whenever. How can that not be extremely important?

That isn't an argument against a scheme that uses LSNs (many kinds of
information might be weighed) -- it's an argument in favor of paying
attention to the high level cadence of VACUUM. That much seems
essential. I think that there might well be room for having several
complementary schemes like the LSN scheme. Or one big scheme that
weighs multiple factors together, if you prefer. That all seems
basically reasonable to me.

Adaptive behavior is important with something as complicated as this.
Adaptive schemes all seem to involve trial and error. The cost of
freezing too much is relatively well understood, and can be managed
sensibly. So we should err in that direction -- a direction that is
relatively easy to understand, to notice, and to pull back from having
gone too far. Putting off freezing for a very long time is a source of
much of the seemingly intractable complexity in this area.

Another way of addressing that is getting rid of aggressive VACUUM as
a concept. But I'm not going to revisit that topic now, or likely
ever.

-- 
Peter Geoghegan

---

From: Melanie Plageman <[email-masked]>
Date: Mon, 28 Aug 2023 19:15:39 -0400

> intended to complement something like the LSN scheme.

I like the direction of this idea. It could avoid repeatedly freezing
a page that is being modified over and over. I tried it out with a
short-running version of workload I (approximating a work queue) --
and it prevents unnecessary freezing.

The problem with this criterion is that if you freeze a page and then
ever modify it again -- even once, vacuum will not be allowed to
freeze it again until it is forced to. Perhaps we could use a very
strict LSN cutoff for pages which contain any frozen tuples -- for
example: only freeze a page containing a mix of frozen and unfrozen
tuples if it has not been modified since before the last vacuum of the
relation ended.

- Melanie

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 28 Aug 2023 18:43:46 -0700

> and it prevents unnecessary freezing.

I see a lot of value in it as an enabler of freezing at the earliest
opportunity, which is usually the way to go.

> freeze it again until it is forced to.

Right. Like you, I was thinking of something that dampened VACUUM's
newfound enthusiasm for freezing, in whatever way -- not a discrete
rule. A mechanism with a sense of proportion about what it meant for
the page to look a certain way. The strength of the signal would
perhaps be highest (i.e. most discouraging of further freezing) on a
page that has only a few relatively recent unfrozen tuples. XID age
could actually be useful here!

> Perhaps we could use a very

You might also need to account for things like interactions with the
FSM. If the new unfrozen tuple packs the page to the brim, and the new
row is from an insert, then maybe the mechanism shouldn't dampen our
enthusiasm for freezing the page at all. Similarly, on a page that has
no unfrozen tuples at all just yet, it would make sense for the
algorithm to be most enthusiastic about freezing those pages that can
fit no more tuples. Perhaps we'd be willing to accept the cost of an
extra FPI with such a page, for example.

It will take time to validate these sorts of ideas thoroughly, of
course. I agree with what you said upthread about it also being a
question of values and priorities.

-- 
Peter Geoghegan

---

From: Robert Haas <[email-masked]>
Date: Tue, 29 Aug 2023 10:22:32 -0400

> less likely to run into xid wraparound and thus need less aggressive

Yes. But it also means that we've got slack capacity to do extra work
now without really hurting anything. If we can leverage that capacity
to reduce the pain of future bulk operations, that seems good. When
resources are tight, doing speculative work immediately becomes less
appealing. It's pretty hard to take such things into account, though.
I was just mentioning it.

> the various pgbench tables). At the end of the run, 2 pages were still

I'm increasingly feeling that it's hard to make sense of the ratio.
Maybe show the number of pages frozen, the number that are frozen at
the end, and the number of pages in the database at the end of the run
as three separate values.

> workloads where it was being uniformly modified (workload B).

Sure, but if the cost of being wrong is low, you can be wrong a lot
and still be pretty happy. It's unclear to me to what extent we should
gamble on making only inexpensive mistakes and to what extent we
should gamble on making only infrequent mistakes, but they're both
valid strategies.

> more FPIs than those which froze based only on page modification

Let's assume for a moment that the rate at which the insert LSN is
advancing is roughly constant over time, so that it serves as a good
proxy for wall-clock time. Consider four tables A, B, C, and D that
are, respectively, vacuumed once per minute, once per hour, once per
day, and once per week. With a 33% threshold, pages in table A will be
frozen if they haven't been modified in 20 seconds, page in table B
will be frozen if they haven't been modified in 20 minutes, pages in
table C will be frozen if they haven't been modified in 8 hours, and
pages in table D will be frozen if they haven't been modified in 2
days, 8 hours. My intuition is that this feels awfully aggressive for
A and awfully passive for D.

To expand on that: apparently D doesn't actually get much write
activity, else there would be more vacuuming happening. So it's very
likely that pages in table D are going to get checkpointed and evicted
before they get modified again. Freezing them therefore seems like a
good bet: it's a lot cheaper to freeze those pages when they're
already in shared_buffers and dirty than it is if we have to read and
write them specifically for freezing. It's less obvious that what
we're doing in table A is wrong, and it could be exactly right, but
workloads where a row is modified, a human thinks about something
(e.g. whether to complete the proposed purchase), and then the same
row is modified again are not uncommon, and human thinking times can
easily exceed 20 seconds. On the other hand, workloads where a row is
modified, a computer thinks about something, and then the row is
modified again are also quite common, and computer thinking times can
easily be less than 20 seconds. It feels like a toss-up whether we get
it right. For this kind of table, I suspect we'd be happier freezing
pages that are about to be evicted or about to be written as part of a
checkpoint rather than freezing pages opportunistically in vacuum.

Maybe that's something we need to think harder about. If we froze
dirty pages that wouldn't need a new FPI just before evicting them,
and just before they would be written out for a checkpoint, under what
circumstances would we still want vacuum to opportunistically freeze?
I think the answer might be "only when it's very cheap." If it's very
cheap to freeze now, it's appealing to gamble on doing it before a
checkpoint comes along and makes the same operation require an extra
FPI. But if it isn't too cheap, then why not just wait and see what
happens? If the buffer gets modified again before it gets written out,
then freeing immediately is a waste and freeze-on-evict is better. If
it doesn't, freezing immediately and freeze-on-evict are the same
price.

I'm hand-waving a bit here because maybe freeze-on-evict is subject to
some conditions and freezing-immediately is more unconditional or
something like that. But I think the basic point is valid, namely,
sometimes it might be better to defer the decision to the last point
in time at which we can reasonably make it, and by focusing on what
vacuum (or even HOT pruning) do, we're pulling that decision forward
in time, which is good if it makes it cheaper by piggybacking on a
previous FPI, but not good if it means that we're guessing whether the
page will be accessed again soon when we could just as well wait and
see whether that happens or not.

---

From: Robert Haas <[email-masked]>
Date: Fri, 1 Sep 2023 15:34:16 -0400

On Tue, Aug 29, 2023 at 10:22 AM Robert Haas <[email-masked]> wrote:
> [ discussion of freeze-on-evict ]

Another way of thinking about this is: instead of replacing this
heuristic with a complicated freeze-on-evict system, maybe we just
need to adjust the heuristic. Maybe using an LSN is a good idea, but
maybe the LSN of the last table vacuum isn't the right one to be
using, or maybe it shouldn't be the only one that we use. For
instance, what about using the redo LSN of the last checkpoint, or the
checkpoint before the last checkpoint, or something like that?
Somebody might find that unprincipled, but it seems to me that the
checkpoint cycle has a lot to do with whether or not opportunistic
freezing makes sense. If a page is likely to be modified again before
a checkpoint forces it to be written, then freezing it is likely a
waste. If it's likely to be written out of shared_buffers before it's
modified again, then freezing it now is a pretty good bet. A given
page could be evicted from shared_buffers, and thus written, sooner
than the next checkpoint, but if it's dirty now, it definitely won't
be written any later than the next checkpoint. By looking at the LSN
of a page that I'm about to modify just before I modify it, I can make
some kind of a guess as to whether this is a page that is being
modified more or less than once per checkpoint cycle and adjust
freezing behavior accordingly.

One could also use a hybrid of the two values e.g. normally use the
insert LSN of the last VACUUM, but if that's newer than the redo LSN
of the last checkpoint, then use the latter instead, to avoid doing
too much freezing of pages that may have been quiescent for only a few
tens of seconds. I don't know if that's better or worse. As I think
about it, I realize that I don't really know why Andres suggested a
last-vacuum-LSN-based heuristic in the first place. Before, I wrote of
this that "One thing I really like about it is that if the table is
being vacuumed frequently, then we freeze less aggressively, and if
the table is being vacuumed infrequently, then we freeze more
aggressively." But actually, I don't think it does that. If the table
is being vacuumed frequently, then the last-vacuum-LSN will be newer,
which means we'll freeze *more* aggressively. And I'm not sure why we
want to do that. If the table is being vacuumed a lot, it's probably
also being modified a lot, which suggests that we ought to be more
cautious about freezing, rather than the reverse.

Just spitballing here.

---

From: Peter Geoghegan <[email-masked]>
Date: Fri, 1 Sep 2023 18:07:09 -0700

On Fri, Sep 1, 2023 at 12:34 PM Robert Haas <[email-masked]> wrote:
> If the table
> which means we'll freeze *more* aggressively.

Sort of like how if you set autovacuum_vacuum_scale_factor low enough,
with standard pgbench, with heap fill factor tuned, autovacuum will
never think that the table doesn't need to be vacuumed. It will
continually see enough dead heap-only tuples to get another autovacuum
each time. Though there won't be any LP_DEAD items at any point --
regardless of when VACUUM actually runs.

When I reported this a couple of years ago, I noticed that autovacuum
would spin whenever I set autovacuum_vacuum_scale_factor to 0.02. But
autovacuum would *never* run (outside of antiwraparound autovacuums)
when it was set just a little higher (perhaps 0.03 or 0.04). So there
was some inflection point at which its behavior totally changed.

> And I'm not sure why we

Why wouldn't it be both things at the same time, for the same table?

Why not also avoid setting pages all-visible? The WAL records aren't
too much smaller than most freeze records these days -- 64 bytes on
most systems. I realize that the rules for FPIs are a bit different
when page-level checksums aren't enabled, but fundamentally it's the
same situation. No?

--
Peter Geoghegan

---

From: Andres Freund <[email-masked]>
Date: Tue, 5 Sep 2023 22:09:00 -0700

Hi,

> goes wrong. At the same time, it doesn't exactly feel like a

I'm not certain either. This is generally something that's not satisfying
right now - although IMO not necessarily for the reason you mention. Given
that we already store, e.g., the time of the last autovacuum in the stats, I
don't see a problem also storing a corresponding LSN. My issue is more that
this kind of information not being crashsafe is really problematic - it's a
well known issue that autovacuum just doesn't do anything for a while after a
crash-restart (or pitr restore or ...), for example.

Given that all the other datapoints are stored in the stats, I think just
storing the LSNs alongside is reasonable.

> really depend on wall-clock time.

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.

> Certainly, that's desirable from the point of view of not wanting to have to
> measure wall-clock time in places where we otherwise wouldn't have to, which
> tends to end up being expensive.

IMO the bigger issue is that we don't want to store a timestamp on each page.

> >            Page Freezes/Page Frozen (less is better)

As, I think, Robert mentioned downthread, I'm not sure this is a useful thing
to judge the different heuristics by. If the number of pages frozen is small,
the ratio quickly can be very large, without the freezing having a negative
effect.

I suspect interesting top-level figures to compare would be:

1) WAL volume (to judge the amount of unnecessary FPIs)

2) data reads + writes (to see the effect of repeated vacuuming of the same
   blocks)

3) number of vacuums and/or time spent vacuuming (freezing less aggressively
   might increase the number of vacuums due to anti-wrap vacuums, at the same
   time, freezing too aggressively could lead to vacuums taking too long)

4) throughput of the workload (to see potential regressions due to vacuuming
   overhead)

5) for transactional workloads: p99 latency (to see if vacuuming increases
   commit latency and such, just using average tends to hide too much)

Greetings,

Andres Freund

---

From: Robert Haas <[email-masked]>
Date: Wed, 6 Sep 2023 10:35:17 -0400

> 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.

---

From: Robert Haas <[email-masked]>
Date: Wed, 6 Sep 2023 10:46:03 -0400

> was some inflection point at which its behavior totally changed.

I think that tables have a "natural" amount of bloat that is a
function of the workload. If you remove all the bloat by, say, running
VACUUM FULL, they quickly bloat again to that point, and then
stabilize (hopefully). It seems like if you set the scale factor to a
value below that "natural" amount of bloat, you might well spin,
especially on a very write-heavy workload like pgbench, because you're
basically trying to squeeze water from a stone on that point.

It's interesting that when you raised the threshold the rate of
vacuuming dropped to zero. I haven't seen that behavior. pgbench does
rely very, very heavily on HOT-pruning, so VACUUM only cleans up a
small percentage of the dead tuples that get created. However, it's
still needed for index vacuuming, and I'm not sure what would cause it
not to trigger for that purpose.

> Why wouldn't it be both things at the same time, for the same table?

Both of what things?

> when page-level checksums aren't enabled, but fundamentally it's the

It's an interesting point. AFAIK, whether or not page-level checksums
are enabled doesn't really matter here. But it seems fair to ask - if
freezing is too aggressive, why is setting all-visible not also too
aggressive? I don't have a good answer to that question right now.

---

From: Peter Geoghegan <[email-masked]>
Date: Wed, 6 Sep 2023 09:20:23 -0700

> not to trigger for that purpose.

This was a case where index vacuuming was never required. It's just a
simple and easy to recreate example of what I think of as a more
general problem.

> > Why wouldn't it be both things at the same time, for the same table?

Why wouldn't we expect a table to have some pages that ought to be
frozen right away, and others where freezing should in theory be put
off indefinitely? I think that that's very common.

> aggressive? I don't have a good answer to that question right now.

As you know, I am particularly concerned about the tendency of
unfrozen all-visible pages to accumulate without bound (at least
without bound expressed in physical units such as pages). The very
fact that pages are being set all-visible by VACUUM can be seen as a
part of a high-level systemic problem -- a problem that plays out over
time, across multiple VACUUM operations. So even if the cost of
setting pages all-visible happened to be much lower than the cost of
freezing (which it isn't), setting pages all-visible without freezing
has unique downsides.

If VACUUM freezes too aggressively, then (pretty much by definition)
we can be sure that the next VACUUM will scan the same pages -- there
may be some scope for VACUUM to "learn from its mistake" when we err
in the direction of over-freezing. But when VACUUM makes the opposite
mistake (doesn't freeze when it should have), it won't scan those same
pages again for a long time, by design. It therefore has no plausible
way of "learning from its mistakes" before it becomes an extremely
expensive and painful lesson (which happens whenever the next
aggressive VACUUM takes place). This is in large part a consequence of
the way that VACUUM dutifully sets pages all-visible whenever
possible. That behavior interacts badly with many workloads, over
time.

VACUUM simply ignores such second-order effects. Perhaps it would be
practical to address some of the issues in this area by avoiding
setting pages all-visible without freezing them, in some general
sense. That at least creates a kind of symmetry between mistakes in
the direction of under-freezing and mistakes in the direction of
over-freezing. That might enable VACUUM to course-correct in either
direction.

Melanie is already planning on combining the WAL records (PRUNE,
FREEZE_PAGE, and VISIBLE). Perhaps that'll weaken the argument for
setting unfrozen pages all-visible even further.

-- 
Peter Geoghegan

---

From: Robert Haas <[email-masked]>
Date: Wed, 6 Sep 2023 16:21:31 -0400

> simple and easy to recreate example of what I think of as a more

OK.

> off indefinitely? I think that that's very common.

Oh, I see. I agree that that's pretty common. I think what that means
in practice is that we need to avoid relying too much on
relation-level statistics to guide behavior with respect to individual
relation pages. On the other hand, I don't think it means that we
can't look at relation-wide or even system-wide statistics at all.
Sure, those statistics may not be perfect, but some things are not
practical to track on a page granularity, and having some
course-grained information can, I think, be better than having nothing
at all, if you're careful about how much and in what way you rely on
it.

> freezing (which it isn't), setting pages all-visible without freezing

I generally agree with all of that.

> possible. That behavior interacts badly with many workloads, over

I think this is an insightful commentary with which I partially agree.
As I see it, the difference is that when you make the mistake of
marking something all-visible or freezing it too aggressively, you
incur a price that you pay almost immediately. When you make the
mistake of not marking something all-visible when it would have been
best to do so, you incur a price that you pay later, when the next
VACUUM happens. When you make the mistake of not marking something
all-frozen when it would have been best to do so, you incur a price
that you pay even later, not at the next VACUUM but at some VACUUM
further off. So there are different trade-offs. When you pay the price
for a mistake immediately or nearly immediately, it can potentially
harm the performance of the foreground workload, if you're making a
lot of mistakes. That sucks. On the other hand, when you defer paying
the price until some later bulk operation, the costs of all of your
mistakes get added up and then you pay the whole price all at once,
which means you can be suddenly slapped with an enormous bill that you
weren't expecting. That sucks, too, just in a different way.

> setting unfrozen pages all-visible even further.

Yeah, so I think the question here is whether it's ever a good idea to
mark a page all-visible without also freezing it. If it's not, then we
should either mark fewer pages all-visible, or freeze more of them.
Maybe I'm all wet here, but I think it depends on the situation. If a
page is already dirty and has had an FPI since the last checkpoint,
then it's pretty appealing to freeze whenever we mark all-visible. We
still have to consider whether the incremental CPU cost and WAL volume
are worth it, but assuming those costs are small enough not to be a
big problem, it seems like a pretty good bet. Making a page
un-all-visible has some cost, but making a page un-all-frozen really
doesn't, so cool. On the other hand, if we have a page that isn't
dirty, hasn't had a recent FPI, and doesn't need pruning, but which
can be marked all-visible, freezing it is a potentially more
significant cost, because marking the buffer all-visible doesn't force
a new FPI, and freezing does.

---

From: Andres Freund <[email-masked]>
Date: Thu, 7 Sep 2023 21:07:13 -0700

Hi,

> rather than the LSN of the previou vacuum. I still like that idea.

Assuming that "downthread" references
https://postgr.es/m/CA%2BTgmoYb670VcDFbekjn2YQOKF9a7e-kBFoj2WJF1HtH7YPaWQ%40mail.gmail.com
could you sketch out the logic you're imagining a bit more?

> the high end, hours at the very most. That's quite appealing.

The reason I was thinking of using the "lsn at the end of the last vacuum", is
that it seems to be more adapative to the frequency of vacuuming.

One the one hand, if a table is rarely autovacuumed because it is huge,
(InsertLSN-RedoRecPtr) might or might not be representative of the workload
over a longer time. On the other hand, if a page in a frequently vacuumed
table has an LSN from around the last vacuum (or even before), it should be
frozen, but will appear to be recent in RedoRecPtr based heuristics?

Perhaps we can mix both approaches. We can use the LSN and time of the last
vacuum to establish an LSN->time mapping that's reasonably accurate for a
relation. For infrequently vacuumed tables we can use the time between
checkpoints to establish a *more aggressive* cutoff for freezing then what a
percent-of-time-since-last-vacuum appach would provide. If e.g. a table gets
vacuumed every 100 hours and checkpoint timeout is 1 hour, no realistic
percent-of-time-since-last-vacuum setting will allow freezing, as all dirty
pages will be too new. To allow freezing a decent proportion of those, we
could allow freezing pages that lived longer than ~20%
time-between-recent-checkpoints.

Hm, possibly stupid idea: What about using shared_buffers residency as a
factor? If vacuum had to read in a page to vacuum it, a) we would need read IO
to freeze it later, as we'll soon evict the page via the ringbuffer b)
non-residency indicates the page isn't constantly being modified?

> 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.

Intuitively it seems easier to take care of that by checking if an FPI is
needed or not?

I guess we, eventually, might want to also freeze if an FPI would be
generated, if we are reasonably certain that the page isn't going to be
modified again soon (e.g. when table stats indicate effectively aren't any
updates / deletes). Although perhaps it's better to take care of that via
freeze-on-writeout style logic.

I suspect than even for freeze-on-writeout we might end up needing some
heuristics

Greetings,

Andres Freund

---

From: Melanie Plageman <[email-masked]>
Date: Sat, 23 Sep 2023 15:53:33 -0400

...

Hi,

I have edited and rerun all of the benchmarks for a subset of the
algorithms. I ran workloads A-I, except for G (G is not an interesting
workload), on Postgres master as well as Postgres with freeze heuristic
algorithms 4 and 5.

As a refresher, algorithms 4 and 5 are as follows:

Freeze tuples on a page opportunistically if the page would be totally
frozen and:

 4. Buffer is already dirty and no FPI is required OR page LSN is older
 than 33% of the LSNs since the last vacuum of the table.

 5. Buffer is already dirty and no FPI is required AND page LSN is older
 than 33% of the LSNs since the last vacuum of the table.

On master, the heuristic is to freeze a page opportunistically if it
would be totally frozen and if pruning emitted an FPI.

I made a few configuration changes for all benchmarks -- most notably
autovacuum is on for all workloads and autovacuum_naptime is decreased
to 10 seconds. The runtime of all time-based workloads was changed to 40
minutes. All workloads were run for a fixed duration except for the COPY
workload (workload F).

It is also worth noting that data loaded into the standard pgbench
tables (with pgbench -i) was loaded with COPY and not COPY FREEZE. Also,
pgbench -i and the pgbench runs were passed the --no-vacuum option.

The updated workloads are as follows:

A. Gaussian TPC-B like + select-only:
   pgbench scale 450 (DB < SB) with indexes on updated columns
   WL 1: 16 clients doing TPC-B like pgbench but with Gaussian access
   distribution (with parameter 6) for updated tables
   WL 2: 2 clients, rate-limited to 1000 TPS, doing select-only pgbench

B. TPC-B like
   pgbench scale 450
   16 clients doing built-in pgbench TPC-C like script

C. Shifting hot set:
   16 clients inserting a single row then updating an indexed column in
   that row

D. Shifting hot set, delete old data
   WL 1: 16 clients inserting one row then updating an indexed column in
   that row
   WL 2: 1 client, rate limited to 10 TPS, deleting data more than 1
   minute old

E. Shifting hot set, delete new data, access new data
   WL 1: 16 clients cycling through 2 single row inserts, an update of
   an indexed column in a single recently inserted row, and a delete of
   a single recently inserted row
   WL 2: 1 client, rate-limited to 0.2 TPS, selecting data from the last
   5 minutes

F. Many COPYs
   1 client, copying a total of 90 GB of data in 70 individual COPYs

G. N/A

H. Append only table
   16 clients, inserting a single row at a time

I. Work queue
   WL 1: 16 clients inserting a single row, updating a non-indexed
   column in that row twice, then deleting that row
   WL 2: 1 client, rate-limited to 0.02 TPS, COPYing data into another
   table

Below are some of the data I collected, including how much WAL was
generated, how much IO various backends did, TPS, and P99 latency.
These were collected at the end of the pgbench run.

Most numbers were rounded to the nearest whole number to make the chart
easier to see. So, for example, a 0 in WAL GB does not mean that no WAL
was emitted. P99 latency below 0 was rounded to a single decimal place.

All IO time is in milliseconds.

"P99 lat" was calculated using the "time" field from pgbench's
"per-transaction logging" option [1]. It is the "transaction's elapsed time"
or duration. I converted it to milliseconds.

"pages frozen" here refers to the number of pages marked frozen in the
visibility map at the end of the run. "page freezes" refers to the
number of times vacuum froze tuples in lazy_scan_prune(). This does not
include cases in which the page was found to be all frozen but no tuples
were explicitly frozen.

"AVs" is the number of autovacuums for the table or tables specified.
"M" (under algo) indicates unpatched Postgres master.

pgbench_accounts is abbreviated as "Acct", pgbench_history as "Hist",
and pgbench_branches + pgbench_tellers as B+T.

Workload A:

+------+--------+---------------------+---------------------+------------------+
| algo | WAL GB | cptr bgwriter writes | other reads/writes | IO time AV worker|
+------+--------+---------------------+---------------------+------------------+
|    M |     37 |           3,064,470 |             119,021 |              199 |
|    4 |     41 |           3,071,229 |             119,010 |              199 |
|    5 |     37 |           3,067,462 |             119,043 |              198 |
+------+--------+---------------------+---------------------+------------------+

+------+---------+-----------+----------------+----------------------+
| algo | TPS read | TPS write | P99 latency read | P99 latency write |
+------+---------+-----------+----------------+----------------------+
|    M |    1000 |     5,391 |            0.1    |             4.0   |
|    4 |    1000 |     5,391 |            0.1    |             3.9   |
|    5 |    1000 |     5,430 |            0.1    |             3.9   |
+------+---------+-----------+----------------+----------------------+

+------+---------+---------+---------+
| algo | Acct AVs | B+T AVs| Hist AVs|
+------+---------+---------+---------+
|    M |       2 |     459 |      21 |
|    4 |       2 |     467 |      20 |
|    5 |       2 |     469 |      21 |
+------+---------+---------+---------+

+------+------------------+------------------+---------------+
| algo | Acct Page Freezes| Acct Pages Frozen| Acct % Frozen |
+------+------------------+--------------------+-------------+
|    M |            2,555 |              646 |           0%  |
|    4 |          866,141 |          385,039 |          52%  |
|    5 |                4 |                0 |           0%  |
+------+------------------+--------------------+-------------+

+------+-------------------+-------------------+---------------+
| algo | Hist Page Freezes | Hist Pages Frozen | Hist % Frozen |
+------+-------------------+-------------------+---------------+
|    M |                0  |                 0 |          0%   |
|    4 |           70,227  |            69,835 |         85%   |
|    5 |           19,204  |            19,024 |         23%   |
+------+-------------------+-------------------+---------------+

+------+-------------------+-------------------+---------------+
| algo | B+T Page Freezes  | B+T Pages Frozen  | B+T % Frozen  |
+------+-------------------+-------------------+---------------+
|    M |              152  |               297 |         33%   |
|    4 |           74,323  |               809 |        100%   |
|    5 |            6,215  |               384 |         48%   |
+------+-------------------+-------------------+---------------+

Algorithm 4 leads to a substantial increase in the amount of WAL (due to
additional FPIs). However, the impact on P99 latency and TPS is minimal.
The relatively low number of page freezes on pgbench_accounts is likely
due to the fact that freezing only happens in lazy_scan_prune(), before
index vacuuming. pgbench_accounts' updated column was indexed, so many
tuples could not be removed before freezing, making pages ineligible for
opportunistic freezing.

This does not explain why algorithm 5 froze fewer pages of
pgbench_accounts than even master. I plan to investigate this further. I
also noticed that the overall write TPS seems low for my system. It is
likely due to row-level contention because of the Gaussian access
distribution, but it merits further analysis.

Additionally, I plan to redesign this benchmark. I will run a single
pgbench instance and use pgbench's script weight feature to issue more
write script executions than select-only script executions. Using two
pgbench instances and rate-limiting the select-only script makes it
difficult to tell if there is a potential impact to concurrent read
throughput.

Workload B:

+------+--------+---------------------+-------------------+------------------+
| algo | WAL GB | cptr bgwriter writes| other reads/writes| IO time AV worker|
+------+--------+---------------------+---------------------+----------------+
|    M |     73 |           5,972,992 |           647,342 |              75  |
|    4 |     74 |           5,976,167 |           640,682 |              66  |
|    5 |     73 |           5,962,465 |           644,060 |              70  |
+------+--------+---------------------+---------------------+----------------+

+------+--------+-------------+
| algo |   TPS  | P99 latency |
+------+--------+-------------+
|    M | 21,336 |         1.5 |
|    4 | 21,458 |         1.5 |
|    5 | 21,384 |         1.5 |
+------+--------+-------------+

+------+---------+---------+---------+
| algo | Acct AVs| B+T AVs | Hist AVs|
+------+---------+---------+---------+
|    M |       1 |     472 |      22 |
|    4 |       1 |     470 |      21 |
|    5 |       1 |     469 |      19 |
+------+---------+---------+---------+

+------+------------------+--------------------+---------------+
| algo | Acct Page Freezes| Acct Pages Frozen | Acct % Frozen  |
+------+------------------+--------------------+---------------+
|    M |                0 |                0  |             0% |
|    4 |          269,850 |                0  |             0% |
|    5 |              194 |                0  |             0% |
+------+------------------+-------------------+----------------+

+------+-------------------+---------------------+---------------+
| algo | B+T Page Freezes  | B+T Pages Frozen    | B+T % Frozen  |
+------+-------------------+---------------------+---------------+
|    M |                 0 |               123   |          34%  |
|    4 |            34,268 |               148   |          44%  |
|    5 |                25 |               114   |          33%  |
+------+-------------------+---------------------+---------------+

+------+-------------------+---------------------+---------------+
| algo | Hist Page Freezes | Hist Pages Frozen   | Hist % Frozen |
+------+-------------------+---------------------+---------------+
|    M |                 0  |                 0  |          0%   |
|    4 |           297,083  |           296,688  |         90%   |
|    5 |            85,573  |            85,353  |         26%   |
+------+-------------------+---------------------+---------------+

Algorithm 4 is more aggressive and does much more freezing. Since there
are no indexes, more page freezing can happen for updated tables. These
pages, however, are uniformly updated and do not stay frozen, so the
freezing is pointless. This freezing doesn't seem to substantially
impact P99 latency, TPS, or volume of WAL emitted.

Workload C:

+------+--------+---------------------+---------------------+------------------+
| algo | WAL GB | cptr bgwriter writes| other reads/writes  | IO time AV worker|
+------+--------+---------------------+---------------------+------------------+
|    M |     16 |             910,583 |              61,124 |              75  |
|    4 |     17 |             896,213 |              61,124 |              75  |
|    5 |     15 |             900,068 |              61,124 |              76  |
+------+--------+---------------------+---------------------+------------------+

+------+-------+--------------+
| algo |  TPS  | P99 latency  |
+------+-------+--------------+
|    M | 10,653|           2  |
|    4 | 10,620|           2  |
|    5 | 10,591|           2  |
+------+-------+--------------+

+------+-----+--------------+-------------+----------+
| algo | AVs | Page Freezes | Pages Frozen | % Frozen|
+------+-----+--------------+-------------+----------+
|    M |   4 |      130,600 |           0 |      0%  |
|    4 |   4 |      364,781 |     197,135 |     60%  |
|    5 |   4 |            0 |           0 |      0%  |
+------+-----+--------------+-------------+----------+

It is notable that algorithm 5 again did not freeze any pages.

Workload D:

+------+--------+---------------------+---------------------+------------------+
| algo | WAL GB | cptr bgwriter writes| other reads/writes  | IO time AV worker|
+------+--------+---------------------+---------------------+------------------+
|    M |     12 |              50,745 |                 481 |             1.02 |
|    4 |     12 |              49,590 |                 481 |             0.99 |
|    5 |     12 |              51,044 |                 481 |             0.98 |
+------+--------+---------------------+---------------------+------------------+

+------+----------+------------+-------------------+--------------------+
| algo | TPS write | TPS delete| P99 latency write | P99 latency delete |
+------+----------+------------+--------------------+-------------------+
|    M |   11,034 |        10  |              1.7  |              107   |
|    4 |   10,992 |        10  |              1.7  |               93   |
|    5 |   11,014 |        10  |              1.7  |               95   |
+------+----------+------------+-------------------+--------------------+

+------+-----+--------------+-------------+-----------+
| algo | AVs | Page Freezes | Pages Frozen | % Frozen |
+------+-----+--------------+-------------+-----------+
|    M |  230|        2,066 |         332  |      6%  |
|    4 |  233|      631,220 |       1,404  |     25%  |
|    5 |  231|      456,066 |       2,352  |     44%  |
+------+-----+--------------+--------------+----------+

The P99 delete latency is higher for master. However write TPS is also
higher, so it is possible that the decreased P99 latency for the delete
is unrelated to freezing. Notably algorithm 4 froze least effectively --
it did the most page freezes per page frozen at the end of the run.

Workload E:

+------+--------+---------------------+---------------------+------------------+
| algo | WAL GB | cptr bgwriter writes | other reads/writes | IO time AV worker|
+------+--------+---------------------+---------------------+------------------+
|    M |     15 |              738,854 |                 482 |            1.0  |
|    4 |     15 |              745,182 |                 482 |            0.8  |
|    5 |     15 |              733,916 |                 482 |            0.9  |
+------+--------+---------------------+---------------------+------------------+

+------+---------+-----------+--------------------+------------------+
| algo | TPS read | TPS write| P99 latency read ms| P99 latency write|
+------+---------+-----------+--------------------+------------------+
|    M |     0.2 |    20,171 |              1,655 |              2   |
|    4 |     0.2 |    20,230 |              1,611 |              2   |
|    5 |     0.2 |    20,165 |              1,579 |              2   |
+------+---------+-----------+--------------------+------------------+

+------+-----+--------------+--------------+----------+
| algo | AVs | Page Freezes | Pages Frozen | % Frozen |
+------+-----+--------------+--------------+----------+
|    M |  23 |        2,396 |            0 |      0%  |
|    4 |  23 |      231,816 |      121,373 |     71%  |
|    5 |  23 |       68,302 |       36,294 |     21%  |
+------+-----+--------------+--------------+----------+

The additional page freezes done by algorithm 4 seem somewhat effective
and do not appear to have an impact on throughput, latency, or a large
impact on WAL volume.

Workload F:

+------+--------+---------------------+--------------------+------------------+
| algo | WAL GB | cptr bgwriter writes| other reads/writes | IO time AV worker|
+------+--------+---------------------+---------------------+-----------------+
|    M |    173 |           1,202,231 |         53,957,448 |           12,389 |
|    4 |    189 |           1,212,521 |         55,589,140 |           13,084 |
|    5 |    173 |           1,194,242 |         54,260,118 |           13,407 |
+------+--------+---------------------+--------------------+------------------+

+------+--------------+
| algo | P99 latency  |
+------+--------------+
|    M |       19875  |
|    4 |       19314  |
|    5 |       19701  |
+------+--------------+

+------+-----+--------------+--------------+----------+
| algo | AVs | Page Freezes | Pages Frozen | % Frozen |
+------+-----+--------------+--------------+----------+
|    M |   3 |            0 |             0 |      0% |
|    4 |   3 |    2,598,727 |     2,598,725 |     24% |
|    5 |   3 |      475,063 |       475,064 |      4% |
+------+-----+--------------+--------------+----------+

Algorithm 4 produced a notably higher volume of WAL but also managed to
freeze a decent portion of the table. P99 latency is higher -- meaning
each COPY did take longer with algorithm 4.

Workload H:

+------+--------+----------------------+---------------------+------------------+
| algo | WAL GB | cptr bgwriter writes | other reads/writes | IO time
AV worker |
+------+--------+----------------------+---------------------+------------------+
|    M |     12 |              922,121 |                 318 |
    1.03 |
|    4 |     16 |              921,543 |                 318 |
    0.90 |
|    5 |     12 |              921,309 |                 318 |
    0.74 |
+------+--------+----------------------+---------------------+------------------+

+------+--------+--------------+
| algo |   TPS  | P99 latency  |
+------+--------+--------------+
|    M | 21,926 |         0.98 |
|    4 | 22,028 |         0.97 |
|    5 | 21,919 |         0.99 |
+------+--------+--------------+

+------+-----+--------------+-------------+-----------+
| algo | AVs | Page Freezes | Pages Frozen | % Frozen |
+------+-----+--------------+--------------+----------+
|    M |   6 |            0 |            0 |      0%  |
|    4 |   6 |      560,317 |      560,198 |     94%  |
|    5 |   6 |       11,921 |       11,921 |      2%  |
+------+-----+--------------+--------------+----------+

Algorithm 4 produced a much higher volume of WAL and froze much more of
the table. This did not negatively impact throughput or latency.
Workload 5 froze dramatically few pages as compared to algorithm 4.

Workload I:

+------+--------+---------------------+---------------------+-------------------+
| algo | WAL GB | cptr bgwriter writes | other reads/writes | IO time
AV worker |
+------+--------+---------------------+---------------------+-------------------+
|    M |     32 |             123,947 |          32,733,204 |
 16,803  |
|    4 |     59 |             124,233 |          32,734,188 |
 17,151  |
|    5 |     32 |             124,324 |          32,733,166 |
 17,122  |
+------+--------+---------------------+---------------------+-------------------+

+------+-----------+---------+------------------+------------------+
| algo | TPS queue | TPS copy| P99 latency queue| P99 latency copy |
+------+-----------+---------+------------------+------------------+
|    M |     5,407 |    0.02 |               4  |          8,386   |
|    4 |     5,277 |    0.02 |               5  |          8,520   |
|    5 |     5,401 |    0.02 |               4  |          8,399   |
+------+-----------+---------+------------------+------------------+

+------+-----------+----------+
| algo | Queue AVs | Copy AVs |
+------+-----------+----------+
|    M |      236 |       5   |
|    4 |      236 |       5   |
|    5 |      235 |       5   |
+------+----------+-----------+

+------+-------------------+--------------------+---------------+
| algo | Queue Page Freezes| Queue Pages Frozen | Queue % Frozen|
+------+-------------------+--------------------+---------------+
|    M |               1   |              1     |        100%   |
|    4 |               1   |              1     |        100%   |
|    5 |               1   |              1     |        100%   |
+------+-------------------+--------------------+---------------+

+------+-------------------+-------------------+---------------+
| algo | Copy Page Freezes | Copy Pages Frozen | Copy % Frozen |
+------+-------------------+-------------------+---------------+
|    M |                 0 |                 0 |          0%   |
|    4 |         3,821,658 |         3,821,655 |         64%   |
|    5 |           431,044 |           431,043 |          7%   |
+------+-------------------+-------------------+---------------+

This is a case in which algorithm 4 seemed to have a measurable negative
performance impact. It emitted twice the WAL and had substantially worse
P99 latency and throughput.

-------

My takeaways from all of the workload results are as follows:

Algorithm 4 is too aggressive and regresses performance compared to
master.

Algorithm 5 freezes surprisingly few pages, especially in workloads
where only the most recent data is being accessed or modified
(append-only, update recent data).

A work queue-like workload with other concurrent workloads is a
particular challenge for the freeze heuristic, and we should think more
about how to handle this.

We should consider freezing again after index vacuuming and unused tuple
reaping.

In many cases a moderate amount of additional freezing (like algorithm
5) does not impact P99 latency and throughput in a meaningful way.

Next steps:
I plan to make some changes to the benchmarks and rerun them with the
new algorithms suggested on the thread.

- Melanie

[1] https://www.postgresql.org/docs/current/pgbench.html

---

From: Melanie Plageman <[email-masked]>
Date: Sun, 24 Sep 2023 12:45:21 -0400

> Workload F:

Andres mentioned that the P99 latency for the COPY workload (workload F)
might not be meaningful, so I have calculated the duration total, mean,
median, min, max and standard deviation in milliseconds.

Workload F:
+------+------------+-------+--------+--------+--------+---------+
| algo |      Total |   Mean| Median |    Min |    Max |  Stddev |
+------+------------+-------+--------+--------+--------+---------+
|    M |  1,270,903 | 18,155| 17,755 | 17,090 | 19,994 |     869 |
|    4 |  1,167,135 | 16,673| 16,421 | 15,585 | 19,485 |     811 |
|    5 |  1,250,145 | 17,859| 17,704 | 15,763 | 19,871 |   1,009 |
+------+------------+-------+--------+--------+--------+---------+

Interestingly, algorithm 4 had the lowest total duration for all COPYs.
Some investigation of other data collected during the runs led us to
believe this may be due to autovacuum workers doing more IO with
algorithm 4 and thus generating more WAL and ending up initializing more
WAL files themselves. Whereas on master and with algorithm 5, client
backends had to initialize WAL files themselves, leading COPYs to take
longer. This was supported by the presence of more WALInit wait events
for client backends on master and with algorithm 5.

Calculating these made me realize that my conclusions about the work
queue workload (workload I) didn't make much sense. Because this
workload updated a non-indexed column, most pruning was HOT pruning done
on access and basically no page freezing was done by vacuum. This means
we weren't seeing negative performance effects of freezing related to
the work queue table.

The difference in this benchmark came from the relatively poor
performance of the concurrent COPYs when that table was frozen more
aggressively. I plan to run a new version of this workload which updates
an indexed column for comparison and does not use a concurrent COPY.

This is the duration total, mean, median, min, max, and standard
deviation in milliseconds of the COPYs which ran concurrently with the
work queue pgbench.

Workload I COPYs:
+------+--------+-------+--------+--------+--------+---------+
| algo |  Total |  Mean | Median |   Min  |   Max  |  Stddev |
+------+--------+-------+--------+--------+--------+---------+
|    M | 191,032|  4,898|  4,726 |  4,486 |  9,353 |     800 |
|    4 | 193,534|  4,962|  4,793 |  4,533 |  9,381 |     812 |
|    5 | 194,351|  4,983|  4,771 |  4,617 |  9,159 |     783 |
+------+--------+-------+--------+--------+--------+---------+

I think this shows that algorithm 4 COPYs performed the worst. This is
in contrast to the COPY-only workload (F) which did not show worse
performance for algorithm 4. I think this means I should modify the work
queue example and use something other than concurrent COPYs to avoid
obscuring characteristics of the work queue example.

- Melanie

---

From: Robert Haas <[email-masked]>
Date: Mon, 25 Sep 2023 14:45:07 -0400

> could you sketch out the logic you're imagining a bit more?

I'm not exactly sure what the question is here. I mean, it doesn't
quite make sense to just ask whether the page LSN is newer than the
last checkpoint's REDO record, because I think that's basically just
asking whether or not we would need an FPI, and the freeze criteria
that Melanie has been considering incorporate that check in some other
way already. But maybe some variant on that idea is useful - the
distance to the second-most-recent checkpoint, or a multiple or
percentage of the distance to the most-recent checkpoint, or whatever.

> that it seems to be more adapative to the frequency of vacuuming.

Yes, but I think it's *too* adaptive. The frequency of vacuuming can
plausibly be multiple times per minute or not even annually. That's
too big a range of variation. The threshold for freezing can vary by
how actively the table or the page is updated, but I don't think it
should vary by six orders of magnitude. Under what theory does it make
sense to say that say "this row in table hasn't been modified in 20
seconds, so let's freeze it, but this row in table B hasn't been
modified in 8 months, so let's not freeze it because it might be
modified again soon"? If you use the LSN at the end of the last
vacuum, you're going to end up making decisions exactly like that,
which seems wrong to me.

> time-between-recent-checkpoints.

Yeah, I don't know if that's exactly the right idea, but I think it's
in the direction that I was thinking about. I'd even be happy with
100% of the time-between-recent checkpoints, maybe even 200% of
time-between-recent checkpoints. But I think there probably should be
some threshold beyond which we say "look, this doesn't look like it
gets touched that much, let's just freeze it so we don't have to come
back to it again later."

I think part of the calculus here should probably be that when the
freeze threshold is long, the potential gains from making it even
longer are not that much. If I change the freeze threshold on a table
from 1 minute to 1 hour, I can potentially save uselessly freezing
that page 59 times per hour, every hour, forever, if the page always
gets modified right after I touch it. If I change the freeze threshold
on a table from 1 hour to 1 day, I can only save 23 unnecessary
freezes per day. Percentage-wise, the overhead of being wrong is the
same in both cases: I can have as many extra freeze operations as I
have page modifications, if I pick the worst possible times to freeze
in every case. But in absolute terms, the savings in the second
scenario are a lot less. I think if a user is accessing a table
frequently, the overhead of jamming a useless freeze in between every
table access is going to be a lot more noticeable then when the table
is only accessed every once in a while. And I also think it's a lot
less likely that we'll reliably get it wrong. Workloads that touch a
page and then touch it again ~N seconds later can exist for all values
of N, but I bet they're way more common for small values of N than
large ones.

Is there also a need for a similar guard in the other direction? Let's
say that autovacuum_naptime=15s and on some particular table it
triggers every time. I've actually seen this on small queue tables. Do
you think that, in such tables, we should freeze pages that haven't
been modified in 15s?

> non-residency indicates the page isn't constantly being modified?

This doesn't seem completely stupid, but I fear it would behave
dramatically differently on a workload a little smaller than s_b vs.
one a little larger than s_b, and that doesn't seem good.

---

From: Robert Haas <[email-masked]>
Date: Mon, 25 Sep 2023 16:19:22 -0400

> about how to handle this.

I feel like we have a sort of Goldilocks problem here: the porridge is
either too hot or too cold, never just right. Say we just look at
workload B, looking at the difference between pgbench_accounts (which
is randomly and frequently updated and thus shouldn't be
opportunistically frozen) and pgbench_history (which is append-only
and should thus be frozen aggressively). Algorithm 4 gets
pgbench_history right and pgbench_accounts wrong, and master does the
opposite. In a perfect world, we'd have an algorithm which could
distinguish sharply between those cases, ramping up to maximum
aggressiveness on pgbench_history while doing nothing at all to
pgbench_accounts.

Algorithm 5 partially accomplishes this, but the results aren't
super-inspiring either. It doesn't add many page freezes in the case
where freezing is bad, but it also only manages to freeze a quarter of
pgbench_history, where algorithm 4 manages to freeze basically all of
it. That's a pretty stark difference. Given that algorithm 5 seems to
make some mistakes on some of the other workloads, I don't think it's
entirely clear that it's an improvement over master, at least in
practical terms.

It might be worth thinking harder about what it takes specifically to
get the pgbench_history case, aka the append-only table case, correct.
One thing that probably doesn't work very well is to freeze pages that
are more than X minutes old. Algorithm 5 uses an LSN threshold instead
of a wall-clock based threshold, but the effect is the same. I think
the problem here is that the vacuum operation essentially happens in
an instant. At the instant that it happens, some fraction of the data
added since the last vacuum is older than whatever threshold you pick,
and the rest is newer. If data is added at a constant rate and you
want to freeze at least 90% of the data, your recency threshold has to
be no more than 10% of the time since the last vacuum. But with
autovacuum_naptimes=60s, that's like 6 seconds, and that's way too
aggressive for a table like pgbench_accounts. It seems to me that it's
not possible to get both cases right by twiddling the threshold,
because pgbench_history wants the threshold to be 0, and
pgbench_accounts wants it to be ... perhaps not infinity, because
maybe the distribution is Gaussian or Zipfian or something rather than
uniform, but probably a couple of minutes.

So I feel like if we want to get both pgbench_history and
pgbench_accounts right, we need to consider some additional piece of
information that makes those cases distinguishable. Either of those
tables can contain a page that hasn't been accessed in 20 seconds, but
the correct behavior for such a page differs between one case and the
other. One random idea that I had was to refuse to opportunistically
freeze a page more than once while it remains resident in
shared_buffers. The first time we do it, we set a bit in the buffer
header or something that suppresses further opportunistic freezing.
When the buffer is evicted the bit is cleared. So we can still be
wrong on a heavily updated table like pgbench_acccounts, but if the
table fits in shared_buffers, we'll soon realize that we're getting it
wrong a lot and will stop making the same mistake over and over. But
this kind of idea only works if the working set is small enough to fit
in shared_buffers, so I don't think it's actually a great plan, unless
we only care about suppressing excess freezing on workloads that fit
in shared_buffers.

A variant on the same theme could be to keep some table-level counters
and use them to assess how often we're getting it wrong. If we're
often thawing recently-frozen pages, don't freeze so aggressively. But
this will not work if different parts of the same table behave
differently.

If we don't want to do something like this that somehow responds to
the characteristics of a particular page or table, then it seems we
either have to freeze quite aggressively to pick up insert-only cases
and accept that this will lead to some wasted effort in heavy-update
cases, or else freeze less aggressively and accept that we're going
not going to freeze insert-only pages consistently.

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 25 Sep 2023 14:16:46 -0700

On Mon, Sep 25, 2023 at 11:45 AM Robert Haas <[email-masked]> wrote:
> too big a range of variation.

+1. The risk of VACUUM chasing its own tail seems very real. We want
VACUUM to be adaptive to the workload, not adaptive to itself.

> gets touched that much, let's just freeze it so we don't have to come

The sole justification for any strategy that freezes lazily is that it
can avoid useless freezing when freezing turns out to be unnecessary
-- that's it. So I find it more natural to think of freezing as the
default action, and *not freezing* as the thing that requires
justification. Thinking about it "backwards" like that just seems
simpler to me. There is only one possible reason to not freeze, but
several reasons to freeze.

> freezes per day.

I totally agree with you on this point. It seems related to my point
about "freezing being the conceptual default action" in VACUUM.

Generally speaking, over-freezing is a problem when we reach the same
wrong conclusion (freeze the page) about the same relatively few pages
over and over -- senselessly repeating those mistakes really adds up
when you're vacuuming the same table very frequently. On the other
hand, under-freezing is typically a problem when we reach the same
wrong conclusion (don't freeze the page) about lots of pages only once
in a very long while. I strongly suspect that there is very little
gray area between the two, across the full spectrum of application
characteristics.

Most individual pages have very little chance of being modified in the
short to medium term. In a perfect world, with a perfect algorithm,
we'd almost certainly be freezing most pages at the earliest
opportunity. It is nevertheless also true that a freezing policy that
is only somewhat more aggressive than this ideal oracle algorithm will
freeze way too aggressive (by at least some measures). There isn't
much of a paradox to resolve here: it's all down to the cadence of
vacuuming, and of rows subject to constant churn.

As you point out, the "same policy" can produce dramatically different
outcomes when you actually consider what the consequences of the
policy are over time, when applied by VACUUM under a variety of
different workload conditions. So any freezing policy must be designed
with due consideration for those sorts of things. If VACUUM doesn't
freeze the page now, then when will it freeze it? For most individual
pages, that time will come (again, pages that benefit from lazy
vacuuming are the exception rather than the rule). Right now, VACUUM
almost behaves as if it thought "that's not my problem, it's a problem
for future me!".

Trying to differentiate between pages that we must not over freeze and
pages that we must not under freeze seems important. Generational
garbage collection (as used by managed VM runtimes) does something
that seems a little like this. It's based on the empirical observation
that "most objects die young". What the precise definition of "young"
really is varies significantly, but that turns out to be less of a
problem than you might think -- it can be derived through feedback
cycles. If you look at memory lifetimes on a logarithmic scale, very
different sorts of applications tend to look like they have remarkably
similar memory allocation characteristics.

> Percentage-wise, the overhead of being wrong is the
> scenario are a lot less.

Very true.

I'm surprised that there hasn't been any discussion of the absolute
amount of system-wide freeze debt on this thread. If 90% of the pages
in the entire database are frozen, it'll generally be okay if we make
the wrong call by freezing lazily when we shouldn't. This is doubly
true within small to medium sized tables, where the cost of catching
up on freezing cannot ever be too bad (concentrations of unfrozen
pages in one big table are what really hurt users).

-- 
Peter Geoghegan

---

From: Andres Freund <[email-masked]>
Date: Tue, 26 Sep 2023 08:19:43 -0700

Hi,

> pages in one big table are what really hurt users).

I generally agree with the idea of using "freeze debt" as an input - IIRC I
proposed using the number of frozen pages vs number of pages as the input to
the heuristic in one of the other threads about the topic a while back. I also
think we should read a portion of all-visible-but-not-frozen pages during
non-aggressive vacuums to manage the freeze debt.

However, I'm not at all convinced doing this on a system wide level is a good
idea. Databases do often contain multiple types of workloads at the same
time. E.g., we want to freeze aggressively in a database that has the bulk of
its size in archival partitions but has lots of unfrozen data in an active
partition. And databases have often loads of data that's going to change
frequently / isn't long lived, and we don't want to super aggressively freeze
that, just because it's a large portion of the data.

Greetings,

Andres Freund

---

From: Peter Geoghegan <[email-masked]>
Date: Tue, 26 Sep 2023 09:07:13 -0700

> that, just because it's a large portion of the data.

I didn't say that we should always have most of the data in the
database frozen, though. Just that we can reasonably be more lazy
about freezing the remainder of pages if we observe that most pages
are already frozen. How they got that way is another discussion.

I also think that the absolute amount of debt (measured in physical
units such as unfrozen pages) should be kept under control. But that
isn't something that can ever be expected to work on the basis of a
simple threshold -- if only because autovacuum scheduling just doesn't
work that way, and can't really be adapted to work that way.

-- 
Peter Geoghegan

---

From: Melanie Plageman <[email-masked]>
Date: Wed, 27 Sep 2023 14:36:23 -0400

> time-between-recent-checkpoints.

One big sticking point for me (brought up elsewhere in this thread, but,
AFAICT never resolved) is that it seems like the fact that we mark pages
all-visible even when not freezing them means that no matter what
heuristic we use, we won't have the opportunity to freeze the pages we
most want to freeze.

Getting to the specific proposal here -- having two+ heuristics and
using them based on table characteristics:

Take the append-only case. Let's say that we had a way to determine if
an insert-only table should use the vacuum LSN heuristic or older than X
checkpoints heuristic. Given the default
autovacuum_vacuum_insert_threshold, every 1000 tuples inserted,
autovacuum will vacuum the table. If you insert to the table often, then
it is a frequently vacuumed table and, if I'm understanding the proposal
correctly, you would want to use a vacuum LSN-based threshold to
determine which pages are old enough to freeze. Using only the
page-older-than-X%-of-LSNs-since-last-vacuum heuristic and assuming no
concurrent workloads, each autovacuum will leave X% of the pages
unfrozen. However, those pages will likely be marked all visible. That
means that the next non-aggressive autovacuum will not even scan those
pages (assuming that run of pages exceeds the skip pages threshold). So
some chunk of pages will get left behind on every autovacuum.

On the other hand, let's say that the table is not frequently inserted
to, so we consider it an infrequently updated table and want to use the
checkpoint-based heuristic. If the page hasn't been modified for
multiple checkpoints, then the next modification will require an FPI.
Because there aren't dead tuples on the page, we can't expect pruning to
emit the FPI. So, freezing the page will always emit an FPI in this
case.

It seems like the ideal freeze pattern for an insert-only table would be
to freeze as soon as the page is full before any checkpoints which could
force you to emit an FPI.

- Melanie

---

From: Peter Geoghegan <[email-masked]>
Date: Wed, 27 Sep 2023 11:57:46 -0700

> heuristic we use, we won't have the opportunity to freeze the pages we

I think of it as being a bit like an absorbing state from a Markov
chain. It's a perfect recipe for weird path dependent behavior, which
seems to make your task just about impossible. It literally makes
vacuuming more frequently lead to less useful work being performed! It
does so *reliably*, in the simplest of cases.

Ideally, you'd be designing an algorithm for a system where we could
expect to get approximately the desired outcome whenever we have
approximately the right idea. A system where mistakes tend to "cancel
each other out" over time. As long as you have "set all-visible but
don't freeze" behavior as your starting point, then ISTM that your
algorithm almost has to make no mistakes at all to really improve on
what we have right now. As things stand, when your algorithm decides
to not freeze (for pages that are still set all-visible in the VM),
you really can't afford to be wrong. (I think that you get this
already, but it's a point worth emphasizing.)

-- 
Peter Geoghegan

---

From: Robert Haas <[email-masked]>
Date: Wed, 27 Sep 2023 15:45:06 -0400

> force you to emit an FPI.

Yes. So imagine we have two freeze criteria:

1. Do not ever opportunistically freeze pages.
2. Opportunistically freeze pages whenever we can completely freeze the page.

For a table where the same rows are frequently updated or where the
table is frequently truncated, we want to apply (1). For an
insert-only table, we want to apply (2). Maybe I'm all wet here, but
it's starting to seem to me that in almost all other cases, we also
want to apply (2). If that's true, then the only thing we need to do
here is identify the special cases where (1) is correct.

It'd be even better if we could adapt this behavior down to the page
level - imagine a large mostly cold table with a hot tail. In a
perfect world we apply (1) to the tail and (2) to the rest. But this
might be too much of a stretch to actually get right.

> heuristic we use, we won't have the opportunity to freeze the pages we

The only solution to this problem that I can see is what Peter
proposed earlier: if we're not prepared to freeze the page, then don't
mark it all-visible either. This might be the right thing to do, and
if it is, we could even go further and get rid of the two as separate
concepts completely. However, I think it would be OK to leave all of
that to one side for the moment, *especially* if we adopt some
proposal that does a lot more opportunistic freezing than we do
currently. Because then the problem just wouldn't come up nearly as
much as it does now. One patch can't fix everything, and shouldn't
try.

---

From: Andres Freund <[email-masked]>
Date: Wed, 27 Sep 2023 13:03:12 -0700

Hi,

On 2023-09-27 15:45:06 -0400, Robert Haas wrote:
> concepts completely.

I suspect that medium term the better approach would be to be much more
aggressive about setting all-visible, including as part of page-level
visibility checks, and to deal with the concern of vacuum not processing such
pages soon by not just looking at unmarked pages, but also a portion of the
all-visible-but-not-frozen pages (the more all-visible-but-not-frozen pages
there are, the more of them each vacuum should process). I think all-visible
is too important for index only scans, for us to be able to remove it, or
delay setting it until freezing makes sense.

My confidence in my gut feeling isn't all that high here, though.

> However, I think it would be OK to leave all of

Agreed.

Greetings,

Andres Freund

---

From: Peter Geoghegan <[email-masked]>
Date: Wed, 27 Sep 2023 13:14:41 -0700

> My confidence in my gut feeling isn't all that high here, though.

I think that this is a bad idea. I see two main problems with
"splitting the difference" like this:

1. If we randomly scan some all-visible pages in non-aggressive
VACUUMs, then we're sure to get FPIs in order to be able to freeze.

As a general rule, I think that we're better of gambling against
future FPIs, and then pulling back if we go too far. The fact that we
went one VACUUM operation without the workload unsetting an
all-visible page isn't that much of a signal about what might happen
to that page.

2. Large tables (i.e. the tables where it really matters) just don't
have that many VACUUM operations, relative to everything else. Who
says we'll get more than one opportunity per page with these tables,
even with this behavior of scanning all-visible pages in
non-aggressive VACUUMs?

Big append-only tables simply won't get the opportunity to catch up in
the next non-aggressive VACUUM if there simply isn't one.

-- 
Peter Geoghegan

---

From: Andres Freund <[email-masked]>
Date: Wed, 11 Oct 2023 17:43:46 -0700

Hi,

Robert, Melanie and I spent an evening discussing this topic around
pgconf.nyc. Here are, mildly revised, notes from that:

First a few random points that didn't fit with the sketch of an approach
below:

- Are unlogged tables a problem for using LSN based heuristics for freezing?

  We concluded, no, not a problem, because aggressively freezing does not
  increase overhead meaningfully, as we would already dirty both the heap and VM
  page to set the all-visible flag.

- "Unfreezing" pages that were frozen hours / days ago aren't too bad and can
  be desirable.

  The main thing we are worried about is repeated freezing / unfreezing of
  pages within a relatively short time period.

- Computing an average "modification distance" as I (Andres) proposed efor
  each page is complicated / "fuzzy"

  The main problem is that it's not clear how to come up with a good number
  for workloads that have many more inserts into new pages than modifications
  of existing pages.

  It's also hard to use average for this kind of thing, e.g. in cases where
  new pages are frequently updated, but also some old data is updated, it's
  easy for the updates to the old data to completely skew the average, even
  though that shouldn't prevent us from freezing.

- We also discussed an idea by Robert to track the number of times we need to
  dirty a page when unfreezing and to compare that to the number of pages
  dirtied overall (IIRC), but I don't think we really came to a conclusion
  around that - and I didn't write down anything so this is purely from
  memory.

A rough sketch of a freezing heuristic:

- We concluded that to intelligently control opportunistic freezing we need
  statistics about the number of freezes and unfreezes

  - We should track page freezes / unfreezes in shared memory stats on a
    per-relation basis

  - To use such statistics to control heuristics, we need to turn them into
    rates. For that we need to keep snapshots of absolute values at certain
    times (when vacuuming), allowing us to compute a rate.

  - If we snapshot some stats, we need to limit the amount of data that occupies

    - evict based on wall clock time (we don't care about unfreezing pages
      frozen a month ago)

    - "thin out" data when exceeding limited amount of stats per relation
      using random sampling or such

    - need a smarter approach than just keeping N last vacuums, as there are
      situations where a table is (auto-) vacuumed at a high frequency

  - only looking at recent-ish table stats is fine, because we
     - a) don't want to look at too old data, as we need to deal with changing
       workloads

     - b) if there aren't recent vacuums, falsely freezing is of bounded cost

  - shared memory stats being lost on crash-restart/failover might be a problem

    - we certainly don't want to immediate store these stats in a table, due
      to the xid consumption that'd imply

- Attributing "unfreezes" to specific vacuums would be powerful:

  - "Number of pages frozen during vacuum" and "Number of pages unfrozen that
    were frozen during the same vacuum" provides numerator / denominator for
    an "error rate"

  - We can perform this attribution by comparing the page LSN with recorded
    start/end LSNs of recent vacuums

  - If the freezing error rate of recent vacuums is low, freeze more
    aggressively. This is important to deal with insert mostly workloads.

  - If old data is "unfrozen", that's fine, we can ignore such unfreezes when
    controlling "freezing aggressiveness"

    - Ignoring unfreezing of old pages is important to e.g. deal with
      workloads that delete old data

  - This approach could provide "goals" for opportunistic freezing in a
    somewhat understandable way. E.g. aiming to rarely unfreeze data that has
    been frozen within 1h/1d/...

Around this point my laptop unfortunately ran out of battery. Possibly the
attendees of this mini summit also ran out of steam (and tea).

We had a few "disagreements" or "unresolved issues":

- How aggressive should we be when we have no stats?

- Should the freezing heuristic take into account whether freezing would
  require an FPI? Or whether page was not in s_b, or ...

I likely mangled this substantially, both when taking notes during the lively
discussion, and when revising them to make them a bit more readable.

Greetings,

Andres Freund

---

From: Robert Haas <[email-masked]>
Date: Thu, 12 Oct 2023 10:26:28 -0400

Thanks for these notes.

>   around that - and I didn't write down anything so this is purely from

See http://postgr.es/m/[email-masked]
for my on-list discussion of this.

>     were frozen during the same vacuum" provides numerator / denominator for

I want to highlight the denominator issue here. I think we all have
the intuition that if we count the number of times that a
recently-frozen page gets unfrozen, and that's a big number, that's
bad, and a sign that we need to freeze less aggressively. But a lot of
the struggle has been around answering the question "big compared to
what". A lot of the obvious candidates fail to behave nicely in corner
cases, as discussed in the above email. I think this is one of the
better candidates so far proposed, possibly the best.

>     been frozen within 1h/1d/...

This strikes me as another important point. Making the behavior
understandable to users is going to be important, because sometimes
whatever system we might craft will misbehave, and then people are
going to need to be able to understand why it's misbehaving and how to
tune/fix it so it works.

> attendees of this mini summit also ran out of steam (and tea).

When the tea is gone, there's little point in continuing.

> discussion, and when revising them to make them a bit more readable.

I think it's quite a good summary, actually. Thanks!