Re: Disabling Heap-Only Tuples
От | Alvaro Herrera |
---|---|
Тема | Re: Disabling Heap-Only Tuples |
Дата | |
Msg-id | 202309191630.xlaw4uzoxsw5@alvherre.pgsql обсуждение исходный текст |
Ответ на | Re: Disabling Heap-Only Tuples (Robert Haas <robertmhaas@gmail.com>) |
Ответы |
Re: Disabling Heap-Only Tuples
(Robert Haas <robertmhaas@gmail.com>)
Re: Disabling Heap-Only Tuples (Andres Freund <andres@anarazel.de>) |
Список | pgsql-hackers |
On 2023-Sep-19, Robert Haas wrote: > On Tue, Sep 19, 2023 at 6:26 AM Alvaro Herrera <alvherre@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)
В списке pgsql-hackers по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: dikkop seems unhappy because of openssl stuff (FreeBSD 14-BETA1)