On Wed, 2023-07-05 at 12:02 +0100, Thom Brown wrote: > On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent > <boekewurm+postg...@gmail.com> wrote: > > On Wed, 5 Jul 2023 at 12:45, Thom Brown <t...@linux.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. > > > > > > 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. > > Hmm... I see your point. It's when an UPDATE isn't going to land on > the same page that it relocates to the earlier available page. So I > guess I'm after whatever mechanism would allow that to happen reliably > and predictably. > > So $subject should really be "Allow forcing UPDATEs off the same page".
I've been thinking about the same thing - an option that changes the update strategy to always use the lowest block with enough free space. That would allow to consolidate bloated tables with no down time. Yours, Laurenz Albe
