MIME-Version: 1.0
References: <CAA-aLv6sYZ5XnuYrytTjxZumBh3KrdyMRmasxHfgaKf-HJrNpw@mail.gmail.com>
 <CAEze2WjpvSoT+Ybp2jpBfPMWSpsdq_2heAmvbJVUcgZ+QfrXoA@mail.gmail.com>
In-Reply-To: <CAEze2WjpvSoT+Ybp2jpBfPMWSpsdq_2heAmvbJVUcgZ+QfrXoA@mail.gmail.com>
From: Thom Brown <thom@linux.com>
Date: Wed, 5 Jul 2023 12:02:55 +0100
Message-ID: <CAA-aLv5B2reEQdOgXY5aE9szef1F7s-RQ23Oacb1R6reMPu07w@mail.gmail.com>
Subject: Re: Disabling Heap-Only Tuples
To: Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAA-aLv5B2reEQdOgXY5aE9szef1F7s-RQ23Oacb1R6reMPu07w%40mail.gmail.com>
Precedence: bulk

On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
>
> On Wed, 5 Jul 2023 at 12:45, Thom Brown <thom@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