Re: Disabling Heap-Only Tuples

On 2023-Sep-19, Robert Haas wrote:

> On Tue, Sep 19, 2023 at 6:26 AM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> > Second, I think we should make it auto-reset. That is, have the user
> > set some value; later, when some condition triggers (say, the table size
> > is 1.2x the limit value you configured), then the local_update_limit is
> > automatically removed from the table options. From that point onwards,
> > the table is operated normally.
>
> That's an interesting idea. It would require taking AEL on the table.
> And also, what do you mean by 1.2x the limit value? Is that supposed
> to be a >= condition or a <= condition? It can't really be a >=
> condition, but you wouldn't set it in the first place unless the table
> were significantly bigger than it could be. But if it's a <= condition
> it doesn't really protect you from hosing yourself. You just have to
> insert a bit more data before enough of the bloat gets removed, and
> now the table just bloats infinitely and probably rather quickly. The
> correct value of the setting depends on the amount of real data
> (non-bloat) in the table, not the actual table size.

I was thinking something vaguely like "a table size that's roughly what
an optimal autovacuuming schedule would leave the table at" assuming 0.2
vacuum_scale_factor. You would determine the absolute minimum size for
the table given the current live tuples in the table, then add 20% to
account for a steady state of dead tuples and vacuumed space. So it's
not 1.2x of the "current" table size at the time the local_update_limit
feature is installed, but 1.2x of the optimal table size.

This makes me think that maybe the logic needs to be a little more
complex to avoid the problem you describe: if an UPDATE is prevented
from being HOT because of this setting, but then it goes and consults
FSM and it gives the update a higher block number than the tuple's
current block (or it fails to give a block number at all so it is forced
to extend the relation), then the update should give up on that strategy
and use a HOT update after all. (I have not read the actual patch;
maybe it already does this? It sounds kinda obvious.)

Having to set AEL is not nice for sure, but wouldn't
ShareUpdateExclusiveLock be sufficient? We have a bunch of reloptions
for which that is sufficient.

> But without any kind of auto-tuning, in my opinion, it's a fairly poor
> feature. Sure, some people will get use out of it, if they're
> sufficiently knowledgeable and sufficiently determined. But I think
> for most people in most situations, it will be a struggle.
>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Tiene valor aquel que admite que es un cobarde" (Fernandel)