Hi,
On 2023-09-19 18:30:44 +0200, Alvaro Herrera wrote:
> 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.)
Yea, a setting like what's discussed here seems, uh, not particularly useful
for achieving the goal of compacting tables. I don't think guiding this
through SQL makes a lot of sense. For decent compaction you'd want to scan the
table backwards, and move rows from the end to earlier, but stop once
everything is filled up. You can somewhat do that from SQL, but it's going to
be awkward and slow. I doubt you even want to use the normal UPDATE WAL
logging.
I think having explicit compaction support in VACUUM or somewhere similar
would make sense, but I don't think the proposed GUC is a useful stepping
stone.
> > 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.
Indeed. I think it'd often just explode table and index sizes, because HOT
pruning won't be able to make usable space in pages anymore (due to dead
items).
Greetings,
Andres Freund