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. > > > > 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.
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". Thom