On 2023-Sep-19, Robert Haas wrote: > On Tue, Sep 19, 2023 at 6:26 AM Alvaro Herrera <alvhe...@alvh.no-ip.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)