Re: Disabling Heap-Only Tuples

On Wed, 5 Jul 2023 at 12:45, Thom Brown <thom(at)linux(dot)com> wrote:
> Heap-Only Tuple (HOT) updates are a significant performance
> enhancement, as they prevent unnecessary page writes. However, HOT
> comes with a caveat: it means that if we have lots of available space
> earlier on in the relation, it can only be used for new tuples or in
> cases where there's insufficient space on a page for an UPDATE to use
> HOT.
>
> This mechanism limits our options for condensing tables, forcing us to
> resort to methods like running VACUUM FULL/CLUSTER or using external
> tools like pg_repack. These either require exclusive locks (which will
> be a deal-breaker on large tables on a production system), or there's
> risks involved. Of course we can always flood pages with new versions
> of a row until it's forced onto an early page, but that shouldn't be
> necessary.
>
> Considering these trade-offs, I'd like to propose an option to allow
> superusers to disable HOT on tables. The intent is to trade some
> performance benefits for the ability to reduce the size of a table
> without the typical locking associated with it.

Interesting use case, but I think that disabling HOT would be missing
the forest for the trees. I think that a feature that disables
block-local updates for pages > some offset would be a better solution
to your issue: Normal updates also prefer the new tuple to be stored
in the same pages as the old tuple if at all possible, so disabling
HOT wouldn't solve the issue of tuples residing in the tail of your
table - at least not while there is still empty space in those pages.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech/)