public inbox for [email protected]  

help / color / mirror / Atom feed

From: Thom Brown <[email protected]>
To: Matthias van de Meent <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Disabling Heap-Only Tuples
Date: Wed, 5 Jul 2023 12:02:55 +0100
Message-ID: <CAA-aLv5B2reEQdOgXY5aE9szef1F7s-RQ23Oacb1R6reMPu07w@mail.gmail.com> (raw)
In-Reply-To: <CAEze2WjpvSoT+Ybp2jpBfPMWSpsdq_2heAmvbJVUcgZ+QfrXoA@mail.gmail.com>
References: <CAA-aLv6sYZ5XnuYrytTjxZumBh3KrdyMRmasxHfgaKf-HJrNpw@mail.gmail.com>
	<CAEze2WjpvSoT+Ybp2jpBfPMWSpsdq_2heAmvbJVUcgZ+QfrXoA@mail.gmail.com>

On Wed, 5 Jul 2023 at 11:57, Matthias van de Meent
<[email protected]> wrote:
>
> On Wed, 5 Jul 2023 at 12:45, Thom Brown <[email protected]> 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

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Disabling Heap-Only Tuples
  In-Reply-To: <CAA-aLv5B2reEQdOgXY5aE9szef1F7s-RQ23Oacb1R6reMPu07w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox