Re: Disabling Heap-Only Tuples - Mailing list pgsql-hackers

On Tue, Sep 19, 2023 at 2:20 PM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
> Mostly agreed, but I think there's a pitfall here. You seem to assume
> we have a perfect oracle that knows the optimal data size, but we
> already know that our estimates can be significantly off. I don't
> quite trust the statistics enough to do any calculations based on the
> number of tuples in the relation. That also ignores the fact that we
> don't actually have any good information about the average size of the
> tuples in the table. So with current statistics, any automated "this
> is how large the table should be" decisions would result in an
> automated footgun, instead of the current patch's where the user has
> to decide to configure it to an explicit value.

I'm not assuming that there's an oracle here. I'm hoping that there's
some way that we can construct one. If we can't, then I think we're
asking the user to figure out a value that we don't have any idea how
to compute ourselves. And I think that kind of thing is usually a bad
idea. It's reasonable to ask the user for input when they know
something relevant that we can't know, like how large they think their
database will get, or what hardware they're using. But it's not
reasonable to essentially hope that the user is smarter than we are.
That's leaving our job half-undone and forcing the user into coping
with the result. And note that the value we need here is largely about
the present, not the future. The question is "how small can the table
be practically made right now?". And there is no reason at all to
suppose that the user is better-placed to answer that question than
the database itself.

> But about that: I'm not sure what the "footgun" is that you've
> mentioned recently?
> The issue with excessive bloat (when the local_update_limit is set too
> small and fillfactor is low) was fixed in the latest patch nearly
> three weeks ago, so the only remaining issue with misconfiguration is
> slower updates. Sure, that's not great, but in my opinion not a
> "footgun": performance returns immediately after resetting
> local_update_limit, and no space was lost.

That does seem like a very good change, but I'm not convinced that it
solves the whole problem. I would agree with your argument if the only
downside of enabling the feature were searching the FSM, failing to
find a suitable free page, and falling back to a HOT update. Such a
thing might be slow, but it won't cause any bloat, and as you say, if
the feature doesn't do what you want, don't use it. But I think the
feature can still cause bloat.

If we're using this feature on a reasonably heavily-updated table,
then sometimes when we check whether any low-numbered pages have free
space, it will turn out that one of them does. This will happen even
if local_update_limit is set far too low, because the table is
heavily-updated, and sometimes that means tuples are moving around,
leaving holes. So when there is a hole, i.e. just by luck we happen to
find some space on a low-numbered page, we'll suffer the cost of a
non-HOT update to move that tuple to an earlier page of the relation.
However, there's a good chance that the next time we update that
tuple, the page will have become completely full, because everybody's
furiously trying to jam as many tuples as possible into those
low-numbered pages, so now the tuple will have to bounce to some
higher-numbered page.

So I think what will happen if the local update limit is set too low,
and the table is actually being updated a lot, is that we'll just
uselessly do a bunch of HOT updates on high-numbered pages as non-HOT,
which will fill up low-numbered pages turning even potentially HOT
updates on those pages to non-HOT as well. Doing a bunch of updates
that could have been HOT as non-HOT can for sure cause index bloat. It
could maybe also cause table bloat, because if we'd done the updates
as HOT, we would have been able to recover the line pointers via
HOT-pruning, but since we turned them into non-HOT updates, we have to
wait for vacuum, which is comparatively much less frequent.

I'm not quite sure how bad this residual problem is. It's certainly a
lot better if a failed attempt to move a tuple earlier can turn into a
normal HOT update instead of a non-HOT update. But I don't think it
completely eliminates the problem of useless tuple movement either.

As Andres points out, I think rightly, we should really be thinking
about ways to guide this behavior other than a page number. As you
point out, there's no guarantee that we can know the right page
number. If we can, cool. But there are other approaches too. He
mentions looking at how full the FSM is, which seems like an
interesting idea although surely we don't want every backend
repeatedly iterating over the FSM to recompute statistics. I wonder if
there are other good ideas we haven't thought of yet. Certainly, if
you found that you were frequently being forced to move tuples to
higher-numbered pages for lack of space anywhere else, that would be a
good sign that you were trying to squeeze the relation into too few
pages. But ideally you'd like to realize that you have a problem
before things get to that point.

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